SQL Unit 4 Built-in Functions and Calculated Fields

1. SQL Unit 4Built-in Functions and Calculated Fields Kirk Scott

2. 4.1 Built-in Functions • 4.2 Calculated Fields • 4.3 The DATE/TIME Data Type and Date Functions • 4.4 The Keyword FORMAT and Using the Date Data Type • 4.5 String Operations • 4.6 The Keywords FIRST, LAST, and TOP

3. 4.1 Built-in Functions

4. 4.1.1 A Function which Applies to Multiple Rows: COUNT • SQL contains several built-in mathematical functions. • What these functions have in common is that they cover or take into account all of the rows in the table. • For this reason they can be called aggregate functions.

5. Syntactically, the usage of these functions is similar. • The keyword for them is followed by a pair of parentheses and a field of interest is put into the parentheses. • The COUNT function, which was introduced earlier, falls into this class. • SELECT COUNT finds the count of all non-null occurrences of a field in a table.

6. COUNT can be applied to a single field, or to *, which signifies all fields. • It can't be applied to a list of fields. • If it's applied to a single field, that field can be either numeric or non-numeric. • This is an example of COUNT applied to a single field: • SELECT COUNT(year) • FROM Car

7. 4.1.2 Other Aggregate Functions: MAX, MIN, SUM, AVG, VAR, STDEV • More typical examples of aggregate functions are purely mathematical. • They can only be applied to a single numeric field. • These functions have in common with the COUNT function the fact that null values are ignored when computing them and they produce a single value as a result.

8. Here is a list of the aggregate functions which will be covered, along with a brief description of their meaning. • Various implementations of SQL may support other mathematical functions, but these are sufficient to introduce the topic:

9. MAX Find the maximum value of a field in a table. • MIN Find the minimum value of a field in a table. • SUM Find the sum of the values of a field in a table. • AVG Find the average of the values of a field in a table. • VAR Find the variance of the values of a field in a table. • STDEV Find the standard deviation of the values of a field in a table.

10. 4.1.3 Examples of Using Aggregate Functions • Some examples of the use of the functions follow. • All of these queries could be written with a WHERE clause that restricted the set of rows under consideration. • For MAX and MIN, more than one record may contain the maximum or minimum value, but a MAX or MIN query will only produce a single value as a result: • SELECT MAX(commrate) • FROM Salesperson

11. For MIN, it is important to note that even though the value NULL is considered to come before the lowest value when doing ORDER BY, NULL is not considered a value, so MIN will pull out the lowest actual value, not NULL, if NULL is present in the field. • SELECT MIN(commrate) • FROM Salesperson

12. The SUM function adds up the values in a column in a table. • It ignores null values. • SELECT SUM(salesprice) • FROM Carsale

13. The three statistical functions all depend in one way or another on dividing a sum by the number of occurrences. • Null values are ignored when forming the sum, and any records with null in the field of interest are not included in the count which the sum is divided by.

14. The critical observation is this: • Do not operate under the assumption that null numeric fields are treated as if they contain 0's. • They are not. • They are simply ignored in these functions.

15. SELECT AVG(salesprice) • FROM Carsale • SELECT VAR(stickerprice) • FROM Car • SELECT STDEV(dealercost) • FROM Car

16. 4.1.4 Row Runctions: Functions which do not Aggregate • There is another kind of function in SQL with a use which is syntactically similar to the aggregate functions, namely a keyword followed by a set of parentheses containing a field name or some sort of arithmetic expression. • These functions are known as row functions because they apply to the value of the field of each row individually. • They do not combine the values in different rows together.

17. Note that the examples given are simple queries, not update queries and they only change how the data is displayed in the query results. • These functions could have such an effect on the stored data if they were used in update queries. • Here are three functions that follow this model:

18. ABS Find the absolute value of a field in a table. • ROUND Round off a numeric value to a specified number of decimal places. • NZ I call this the New Zealand function. It is actually the "null to zero" function and is named after the initials of the words null and zero.

19. The example database doesn't have a field which illustrates the use of ABS very well, because all of the monetary and numeric fields contain only positive values. • However, this example illustrates its syntax: • SELECT ABS(commrate) AS [Absolute value of commrate] • FROM Salesperson

20. The example database also doesn't have a field where the use of ROUND can be illustrated very practically. • All of the monetary fields will be formatted with two decimal places by default, and the commrate field only holds values of this form: 0.0n.

21. If you wanted to make sure that commrates were only displayed with two decimal places, no matter what kinds of values existed, you could do as shown below. • The ROUND function takes two parameters, the field it is to be applied to and the number of decimal places to be displayed: • SELECT ROUND(commrate, 2) • FROM Salesperson

22. In use, it appears that strictly speaking, ROUND doesn’t affect output; • It just affects numerical computation. • In other words, if you had an integer and applied ROUND with two decimal places to it, it would just display the integer value. • It would not display the value in the form n.00.

23. The data in the Salesperson table do allow for a use of the NZ function which would make a difference. • There is one salesperson with a null value for commrate. • In the query shown below you specify in the parentheses that null values of commrate should be replaced with the value 0. • All other commrates are unchanged by the use of the function: • SELECT spno, name, NZ(commrate, 0) • FROM Salesperson

24. 4.2 Calculated Fields

25. 4.2.1 Calculated Fields Using Arithmetic Operators • In SELECT statements it is possible to create mathematical expressions which involve numeric fields, numeric constants, arithmetic operators like +, -, *, and /, and parentheses for grouping. • Such expressions are row level functions defined by the user. • The expressions apply to the values in a single row and do not aggregate.

26. Expressions of this kind can include the built-in row level functions. • If any of the fields in the expression are null, the result of the expression is null. • Here is a self-explanatory example of a calculated field: • SELECT vin, stickerprice – dealercost AS [markup] • FROM Car

27. 4.2.2 Division in Calculated Fields and the Keyword ROUND • Here is an example of a calculated field containing division: • SELECT (stickerprice – dealercost) / dealercost AS [Percent markup as decimal] • FROM Car

28. Because this example involves division, the results might have many decimal places. • This allows for a practical use of the ROUND function. • Notice how the function is called on an arithmetic expression rather than just a single numeric field. • SELECT ROUND((stickerprice – dealercost) / dealercost, 2) • FROM Car

29. Division could also be a problem if the dealercost were 0 in any record. • In this case the system will return an "#error" message in the results. • One approach to this problem would be to have a WHERE clause which eliminated from consideration records with null dealercosts.

30. 4.2.3 Examples with a Join and NZ • Suppose you would like to find the commission earned on every carsale. • The salesprice field is in the Carsale table and the commrate field is in the Salesperson table. • There is no problem doing a calculated field involving the fields from the different tables in a join query: • SELECT salesprice * commrate • FROM Carsale, Salesperson • WHERE Carsale.spno = Salesperson.spno

31. Because one of the salespeople has null for a commrate value, if that salesperson sold a car, that carsale would produce a null value as a result for this query. • Rather than showing null, since the expected value for a commission is numeric, it may be preferable to explicitly show 0.0. • This can be accomplished by using the NZ function: • SELECT salesprice * NZ(commrate, 0) • FROM Carsale, Salesperson • WHERE Carsale.spno = Salesperson.spno

32. It is also possible that all salespeople should have commrates and that an oversight has been made on data entry. • In the long run, the data in the Salesperson table should be corrected. • In the short run, you may want to produce query results that show that in all cases, at the very least, a commrate of 0.03 applies to a sale.

33. This provides an example of using NZ with a value other than zero: • SELECT salesprice * NZ(commrate, 0.03) • FROM Carsale, Salesperson • WHERE Carsale.spno = Salesperson.spno

34. 4.2.4 Row Level Functions and Aggregate Functions Don't Mix • Row level functions and aggregate functions don't mix. • Recall that by definition, row level functions apply to single rows in a table, while aggregate functions are based on all of the rows in a table. • However, the following query is not syntactically possible in Access. • THIS DOES NOT WORK: • SELECT stickerprice – AVG(stickerprice) • FROM Car

35. Reading the query, it's clear what the person writing it would like to find. • Some systems may support this syntax. • In Access, other techniques will have to be used to obtain this result. • They will be explained later.

36. On the other hand, here is a query where a calculated field, a row level function does mix successfully with an aggregate function. • SELECT AVG(stickerprice – dealercost) • FROM Car

37. It is possible to speculate about why one works and the other one doesn't based on the steps that would have to be taken to calculate them. • For the first example, it would be necessary to scan the whole car table, finding the average, and then go back and find the differences. • For the second example, row-by-row it would be possible to find the differences, saving those value for use when calculating the average.

38. In other words, for the second example it would only be necessary to scan the table once. • It is not so important to grasp this difference • It's worthwhile to understand though, that as a general rule you can't assume that you can mix row level functions and aggregate functions.

39. 4.3 The DATE/TIME Data Type and Date Functions

40. 4.3.1 Important Note Regarding Versions of MS Access • Depending on the installation of Microsoft Access on your machine, you may have problems where some of the date related functions don't work. • There is one general thing that can be done to at least make sure there are no background problems, and a specific thing that can be done to try and solve the problems with the date functions.

41. The general thing to do is to make sure that the version of the example database that you're using agrees with the version of Microsoft Access that you're using. • The example database for this course as posted is in the Microsoft Access 2000 version in case there are people who only have an older version of Access available to them. • If you are using a version of Access later than 2000, then it would be useful to save the example database in that version and use it.

42. Whether you change versions of the example database or not, when you try to run queries with date functions you may get several different types of error messages. • If one of these error messages appears, try the steps given on the following overheads and see if that resolves the problem.

43. Note, the steps are extremely inconvenient. • This problem has cropped up in the lab and the lab tech found the steps given to be a solution. • No one in their right mind would anticipate this. • Your goal is not to understand any of it, but just to try it if you have problems and see if the steps solve the problem

44. A. Press CTRL+g. This will open the Microsoft Visual Basic development environment (assuming that it is installed on your system). • B. Take the Tools option in the menu bar. • C. In the drop down menu, select References. • D. A box will pop up containing a list of things. • It may contain a checked box next to this text: • "MISSING: utility.mda".

45. E. Click on the checked box, removing the check mark. • F. Then click OK. • G. Close the Microsoft Visual Basic development environment by clicking on the X in the upper right hand corner of the window.

46. H. Then try to run your query. Hopefully, the date functions will now work. • I. If this solution works, it should be in effect for the duration of the current session using the database. • It will have to be repeated every time the database is opened.

47. If you still can't get all of the date features to work—just fake it. • In other words, save what you can in the assignments and don't worry about the rest. • When submitting your assignment, along with the attachment, you can put a note in the email message letting me know that the system wouldn’t let you do some of the queries. • Still learn the stuff so that you can do it with paper and pencil on the test.

48. 4.3.2 The DATE/TIME Data Type • If you were to look at the choices of data types in Microsoft Access's graphical user interface for designing tables, you would discover that there is a unified data type, named date/time, for fields that will contain dates, times, or both. • The technical details for this type are that it is 8 bytes long and it can hold time information ranging from years down to seconds.

49. In the example database the Carsale.salesdate field is an example of this type. • It illustrates the idea that you can limit your attention to the date only and ignore the time component if you want to. • The DATE type has several different aspects.

50. By nature, dates and times can be added or subtracted in order to arrive at lengths of time. • However, dates are not generally represented as discrete numbers on a timeline. • They are displayed as alphanumeric text, where characters, digits, and punctuation are interpreted in a special way.