Single Table Queries

1. Single Table Queries Week 2, Day 2 (based on Ch 5 of Connolly and Begg) CMPT 355 Sept-Dec 2010 - w2d2

2. Single Table Queries - Outline • INPUT (from the db): • Getting Different Amounts of Data • OUTPUT (to the user): • Presenting Results • PROCESSING: • Summarizing Results CMPT 355 Sept-Dec 2010 - w2d2

3. Getting Different Amounts of Data • SQL SEARCHes are designed for all your data needs • Greater than table level (to be covered next day) • Table level: • Get all data in a table • Record level: • Get a particular record from a table • Slice and Dice: • Get selected records from a table • Get selected attributes for all records from a table • Get selected attributes from selected records from a table CMPT 355 Sept-Dec 2010 - w2d2

4. Getting Different Amounts of Data • Table Level • Get all data in a table (E.g. 5.1) Employee # E-name E-Haddress E-Hphone E-position E-dept E-Salary E-phone CMPT 355 Sept-Dec 2010 - w2d2

5. Getting Different Amounts of Data • Record level: • Get a particular record from a table (E.g. 5.5) EmployeeNO E-name E-Haddress E-Hphone E-position E-dept E-Salary E-phone CMPT 355 Sept-Dec 2010 - w2d2

6. Getting Different Amounts of Data • Slice and Dice: • Get selected records from a table (E.g. 5.5) EmployeeNO E-name E-Haddress E-Hphone E-position E-dept E-Salary E-phone CMPT 355 Sept-Dec 2010 - w2d2

7. Getting Different Amounts of Data • Slice and Dice: • Get selected attributes for all records (E.g. 5.2) EmployeeNO E-name E-Haddress E-Hphone E-position E-dept E-Salary E-phone CMPT 355 Sept-Dec 2010 - w2d2

8. Getting Different Amounts of Data • Slice and Dice: • Get selected attributes from selected records EmployeeNO E-name E-Haddress E-Hphone E-position E-dept E-Salary E-phone CMPT 355 Sept-Dec 2010 - w2d2

9. Getting Different Amounts of Data • Review • SELECT [required] • * or list of attributes • determines output • FROM [required] • list of tables • source of data for the query • WHERE • conditions • determine which records are selected CMPT 355 Sept-Dec 2010 - w2d2

10. Getting Different Amounts of Data • Variations on WHERE • Get selected records (including a particular record) from a table • E.g. 5.5 comparison search condition • E.g. 5.6 compound comparison search • E.g. 5.7 range search condition • E.g. 5.8 membership search condition • E.g. 5.9 pattern match search condition • for really complex text based searches • E.g. 5.10 NULL search condition • to deal with NULLs (as per discussion of NULLs last class) CMPT 355 Sept-Dec 2010 - w2d2

11. Getting Different Amounts of Data • Variations on WHERE • Get selected records (including a particular record) from a table CMPT 355 Sept-Dec 2010 - w2d2

12. Getting Different Amounts of Data • Review exercises • Consider a table Employee # E-name E-Haddress E-Hphone E-position E-dept E-Salary E-phone • What SQL would you use for a query to get • Data for a residential phone book • Data for an office phone book • The names, departments, and salaries of all managers • The names and phone numbers of all programmers and all analysts • The names of people earning less than $10,000 or more than $100,000 CMPT 355 Sept-Dec 2010 - w2d2

13. Presenting Results - outline • Renaming headings • Modifying results • Sorting results CMPT 355 Sept-Dec 2010 - w2d2

14. Presenting Results • Renaming headings • Add AS to each attribute in SELECT clause that is to be renamed • SELECT (xxxx) AS yyy, (zzz) AS aaa • E.g renaming selected headings EmployeeNO E-name E-Haddress E-Hphone • SELECT E-name AS Name, E-Haddress AS HomeAddress, E-Hphone AS HomePhone EmployeeNO Name HomeAddress HomePhone CMPT 355 Sept-Dec 2010 - w2d2

15. Presenting Results • Modifying results (E.g. 5.4) • Add an arithmetic operation on the attribute as the input to the AS clause • (optionally) Add AS to SELECT clause for output • SELECT xxxx(arithmetic operation) AS yyy • E.g present Canadian $ salary as American $ EmployeeNO E-name E-position E-dept E-Salary E-phone • SELECT E-name, E-salary / 1.35 AS USsalary E-name USsalary CMPT 355 Sept-Dec 2010 - w2d2

16. Presenting Results • Modifying results (additional information) • The ISO standard for SQL is written in permissive / generative terms with only the most fundamental of exclusions. • “The syntactic notation used in ISO/IEC 9075 is an extended form of BNF (‘Backus Normal Form’ or ‘Backus Naur Form’).”1 • Wherever an attribute can be used, an expression that evaluates to an attribute can also be used. • Remember: each cell of a table contains exactly one atomic (single) value • Rather than referring to attributes within a SELECT, the text on p. 116-117 specifies: • “columnExpression represents a column name or an expression” 1 ISO/IEC 9075-1 Database Languages – SQL - Framework CMPT 355 Sept-Dec 2010 - w2d2

17. Presenting Results • Modifying results (additional information) • You should be able to use any properly formed expression • that evaluates to a single value as one of the attributes in a SELECT, including: • An attribute • An arithmetic function of one or more attributes • (see next slide for various arithmetic functions) • The same attribute as was already used • A value from a subquery (an embedded query) • A combination of functions and subqueries that evaluates to a single value • Of course, you can expect some differences with different DBMS implementations. CMPT 355 Sept-Dec 2010 - w2d2

18. (This slide is for enrichment only) “4.4.3 Operations involving numbers • As well as the usual arithmetic operators, plus, minus, times, divide, unary plus, and unary minus, there are the following functions that return numbers:”1 • <position expression> determines the first position, if any, at which one string, S1, occurs within another, S2. • <length expression> returns the length of a given character string. • <extract expression> • <cardinality expression> • <absolute value expression> • <modulus expression> • <natural logarithm> • <exponential function> • <power function> • <square root> • <floor function> (truncate) • <ceiling function> • <width bucket function> (you can look this one up if you like) 1 ISO/IEC 9075-2 Database Languages – SQL - Foundation CMPT 355 Sept-Dec 2010 - w2d2

19. (This slide is for enrichment only) Data typing in a SELECT • is based on the most appropriate data type available for the attribute: • The data type of an individual attribute is based on its data definition • The data type of a computed value is established implicitly based on the data types involved in the computation. “4.11 Data Conversion • Implicit type conversion can occur in expressions, fetch operations, single row select operations, inserts, deletes, and updates. Explicit type conversions can be specified by the CAST operator.”1 1 ISO/IEC 9075-2 Database Languages – SQL - Foundation CMPT 355 Sept-Dec 2010 - w2d2

20. Presenting Results • Sorting results • Add ORDER BY clause at end of query • SELECT xxxx AS xyxy • FROM yyy • WHERE zzz • ORDER BY • List of attributes, with most important first • Determines order of records in result • NOTE: to sort in reverse order, add “DESC” after the attribute name CMPT 355 Sept-Dec 2010 - w2d2

21. Presenting Results • Sorting results (cont) • Consider creating an office phone book • You might want a listing of all people organized alphabetically by their names • SELECT E-name, E-dept, E- phone • ORDER BY E-name • (E.g. 5.11 single-column sorting) • You might want a listing of departments and of people within departments • SELECT E-dept, E-name, E- phone • ORDER BY E-dept, E-name • (E.g. 5.12 multiple column sorting) CMPT 355 Sept-Dec 2010 - w2d2

22. Summarizing Results - outline • NOTE: • the DBMS always looks at all records • but sometimes you only want • to output a summary of these records • Alternatives for summarizing results include: • Get a set of different values of an attribute • Using summary functions • Get a set of subtotals CMPT 355 Sept-Dec 2010 - w2d2

23. Summarizing Results • Get a set of different values of an attribute • Use DISTINCT in SELECT clause • E.g. 5.3 distinct values of an attribute CMPT 355 Sept-Dec 2010 - w2d2

24. Summarizing Results • Get a set of different values of an attribute(cont) • “A note on the result of the SELECT statement • “Select statements in SQL do not produce a set of values, but rather produce a list of values. The lack of order and the guarantee of uniqueness of the relational model are not part of SQL. Like many other SQL features, uniqueness is not guaranteed because it is expensive. If a query is known to yield unique results, it doesn’t need to be checked. If it doesn’t matter whether the rows are unique, it also doesn’t need to be checked. Checking for uniqueness requires that the rows be sorted by some value and then compared. The cost of executing this operation is quite high for large tables. SQL leaves it to the developer to decide whether uniqueness is important. Adding the keyword DISTINCT after SELECT forces the SQL processor to produce a table with unique rows.”1 1 Greg Riccardi, Principles of Database Systems with Internet and Java Applications, Addison-Wesley, 2001. CMPT 355 Sept-Dec 2010 - w2d2

25. Summarizing Results • Using summary functions • Applying a summary function instead of an arithmetic operation to an attribute in a SELECT • Using AS in SELECT to rename the resulting attribute • Can have a set of summary results in a single SELECT • E.g. 5.13 COUNT • E.g. 5.14 COUNT (DISTINCT) • E.g. 5.15 COUNT and SUM • E.g. 5.16 MIN, MAX, AVG CMPT 355 Sept-Dec 2010 - w2d2

26. Summarizing Results • Get a set of subtotals • Rather than a single set of summary values, get a set of summary values for each grouping that has a different value for some attribute • GROUP BY clause creates a separate group for each distinct value of some attribute • HAVING clause restricts membership in groups based on some conditions (similar to WHERE) • E.g. 5.17 GROUP BY • E.g. 5.18 HAVING CMPT 355 Sept-Dec 2010 - w2d2

27. Summarizing Results • Our query structure now contains: • SELECT [DISTINCT] xxxx AS xyxy, expression, xyxy,xxyy • FROM yyy • WHERE zzz • GROUP by uuu • HAVING vvv • ORDER BY sss CMPT 355 Sept-Dec 2010 - w2d2