1 / 72

Advanced SQL

Advanced SQL. SMSU Computer Services Short Course. Contact Information. Greg Snider – MIS Database Analyst Ext. 6-4397 Email – SGS345B Don’t hesitate to email or call if you have any questions after you start using QM and SQL. Agenda. SQL Limits FETCH FIRST n ROWS ONLY

abner
Download Presentation

Advanced SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced SQL SMSU Computer Services Short Course

  2. Contact Information • Greg Snider – MIS Database Analyst • Ext. 6-4397 • Email – SGS345B Don’t hesitate to email or call if you have any questions after you start using QM and SQL.

  3. Agenda • SQL Limits • FETCH FIRST n ROWS ONLY • Quantified predicates • Named expressions • Scalar Subselect

  4. Agenda • Case • Multiple Join Types • Common Table Expressions • In-line Views/Table Expressions/Nested Table Expressions / Derived Tables

  5. SQL Limits • SQL statements can now be 65, 535 characters long • 256 tables can be referenced in a single SQL statement • Approximately 8000 elements in a SELECT list

  6. FETCH FIRST n ROWS ONLY • Sets the maximum number of rows to be returned • Follows the ORDER BY clause if it is present • If the ORDER BY clause is present, then the FETCH FIRST operation is performed on the sorted data

  7. FETCH FIRST n ROWS ONLY Example

  8. FETCH FIRST n ROWS ONLY Example Results

  9. Quantified Predicates • Compares a value with a set of values • Expression [=, <>, <, >, <=, >=] [Some, Any, All] (subselect)

  10. Quantified Predicates • Expression > ALL (subselect) • The predicate is true if the expression is greater than each individual value returned by the subselect. If the subselect returns no values, the predicate is true. The result is false if the specified relationship is false for at least one value. • The <> All predicate is equivalent to the NOT IN predicate.

  11. Quantified Predicates • Expression > ANY (subselect • The predicate is true if the expression is greater than at least on of the values returned by the subselect. If the subselect returns no values, the predicate is false. • The = ANY quantified operator is equivalent to the IN predicate • SOME is synonymous with ANY

  12. Named Expressions • The AS clause lets you assign a meaningful name to an expression, which makes it easier to refer back to that expression. • SELECT YEAR(CURRENT DATE) – BIRTH_DATE) AS AGE • The named column cannot be used in a WHERE clause • The named column can be used in a ORDER BY clause or GROUP BY clause

  13. Named Expressions

  14. Named Expressions

  15. Scalar Subselect • A scalar subselectis a subselect, enclosed in parentheses, placed in the SELECT list that returns a single result row and a single result column. If the result of the subselect is no rows, then the null value is returned. An error is returned if there is more than one row in the result.

  16. Scalar Subselect Example

  17. Scalar Subselect Example – Results

  18. CASE • The CASE expression can be used to easily manipulate the data in a column • It can be used to change codes in to meaningful words • It can be used to protect against exceptions such as division by zero

  19. CASE Syntax

  20. CASE Example 1

  21. CASE Example 2

  22. CASE Example 3

  23. Can I use more than one column in a CASE expression? • Yes, any number of WHEN conditions can be used in the CASE expression, and each WHEN can reference a different column or expression. CASE ( WHEN COL7 > 15 THEN COL7 * .0133 WHEN COL3 = 34 THEN COL3 / .0133 WHEN COL8 < 98 THEN 999 ELSE NULL END) AS MULTICALC

  24. Where can I use CASE expressions? • Anywhere an expression is allowed in a SQL statement • SELECT clause • WHERE clause • HAVING clause • Built-in Function • IN (list)

  25. Can I use a subselect inside a CASE expression?

  26. CASE Subselect Results

  27. Can I use an IN list in a CASE expression?

  28. CASE Example 4

  29. CASE Example 4 Results

  30. CASE Example 5 (join ORing) SELECT columns FROM T1, T2 WHERE (CASE WHEN T1.ID1 IS NULL THEN T1.ID2 END) = T2.COLX

  31. CASE Example 6 (table pivoting) • The table looks like this:

  32. CASE Example 6

  33. CASE Example 6 – Results

  34. Multiple Join types • Inner Join • Left Outer Join • Right Outer Join • Left Exception Join • Right Exception Join • Full Outer Join

  35. Inner Join • An Inner Join returns only the rows from each table that have matching values in the join columns. Any rows that do not have a match between the tables will not appear in the result table. • This is the join you’re used to using. FROM T1 INNER JOIN T2 ON T1.COLA = T2.COL1

  36. Left Outer Join • A Left Outer Join returns values for all of the rows from the first table (the table on the left) and the values from the second table for the rows that match. Any rows that do not have a match in the second table will return the null value for all columns from the second table. FROM T1 LEFT OUTER JOIN T2 ON T1.COLA = T2.COL1

  37. Left Outer Join – Example • List students and the classes they are taking, if any.

  38. Left Outer Join – Example

  39. Left Outer Join – Results

  40. Right Outer Join • A Right Outer Join return values for all of the rows from the second table (the table on the right) and the values from the first table for the rows that match. Any rows that do not have a match in the first table will return the null value for all columns from the first table. FROM T1 RIGHT OUTER JOIN T2 ON T1.COLA = T2.COL1

  41. Left Exception Join • A Left Exception Join returns only the rows from the left table that do not have a match in the right table. Columns in the result table that come from the right table have the null value. FROM T1 LEFT EXCEPTION JOIN T2 ON T1.COLA = T2.COL1

  42. Left Exception Join – Example • List students who are not taking classes

  43. Left Exception Join – Example

  44. Left Exception Join – Results

  45. Right Exception Join • A Right Exception Join returns only the rows from the right table that do not have a match in the left table. Columns in the result table that come from the left table have the null value. FROM T1 RIGHT EXCEPTION JOIN T2 ON T1.COLA = T2.COL1

  46. Full Outer Join • The Full Outer Joinincludes the inner join (the rows that match) and the rows from both the left and right tables that do not match. • The Full Outer Join is not included in this version of SQL, but it can be simulated. • You simulate a Full Outer Join by using a Left Outer Join union’ed with a Right Exception Join

  47. Full Outer Join – Example

  48. Full Outer Join – Results

  49. Full Outer Join - Results

  50. How to know which join to use? • If the request contains one of the key condition phrases: “whether or not”, “regardless of”, “no matter what”, “if any”. • If any of these phrases fit in the generic information request of “get all information from this table—condition phrase—other information is present in the other table”, then a left outer join will be necessary.

More Related