Advanced Applications of SQL

Document Sample
Advanced Applications of SQL Powered By Docstoc
					Advanced Applications of SQL

What You Will Learn...................................................................................................................... 1

Understanding the Principles of Database Design.......................................................................... 2
   Enforcing Uniqueness of Data Table Records.......................................................................... 2
   Eliminating Redundant Data..................................................................................................... 4
   Eliminating Data that Does Not Depend on the Key................................................................ 6
   Eliminating Unrelated Data from the Same Table.................................................................... 6
   Advantages of a Relational Database ....................................................................................... 6
   Disadvantages of a Relational Database ................................................................................... 7

Exercise 1: Database Design........................................................................................................... 8

Defining Primary Keys and Foreign Keys...................................................................................... 9
   Primary Keys ............................................................................................................................ 9
   Foreign Keys........................................................................................................................... 12

Exercise 2: Primary Keys and Foreign Keys ................................................................................ 16

Establishing Referential Integrity ................................................................................................. 17
   Using Primary Keys and Foreign Keys to Enforce Referential Integrity ............................... 17
   Cascading Updates and Deletes .............................................................................................. 19
   Understanding Procedural Referential Integrity (Cascading Triggers) .................................. 22

Exercise 3: Referential Integrity ................................................................................................... 23

Combining the Results of Multiple Queries (UNION)................................................................. 24
  Using the UNION Operator .................................................................................................... 24
  Sorting the Results .................................................................................................................. 25
  Using ALL to Include Duplicates ........................................................................................... 25
  Using WHERE, GROUP BY, and HAVING Clauses............................................................ 26
  Storing the Query Results in a New Table.............................................................................. 27

Comparing the Results of Multiple Queries (EXCEPT and INTERSECT) ................................. 28
  Using the EXCEPT Operator.................................................................................................. 28
  Using the INTERSECT Operator ........................................................................................... 30

Exercise 4: UNION, EXCEPT, and INTERSECT ....................................................................... 31

Summarizing Data Using UNION, ROLLUP, and CUBE ........................................................... 32
   Using UNION to Display Detailed Data and Totals............................................................... 32
   Using ROLLUP to Calculate Subtotals and Grand Totals...................................................... 34
   Using CUBE to Calculate Subtotals and Grand Totals .......................................................... 35

Exercise 5: UNION, ROLLUP, and CUBE.................................................................................. 37


Advanced Applications of SQL
www.micros.umsl.edu
Using PIVOT and UNPIVOT to Transform Your Query Results................................................ 39
   Using the PIVOT Operator ..................................................................................................... 40
   Using the UNPIVOT Operator ............................................................................................... 44

Exercise 6: PIVOT and UNPIVOT............................................................................................... 46

Appendix A: Class Database Tables............................................................................................. 48

Appendix B: Normalization.......................................................................................................... 58
   First Normal Form .................................................................................................................. 58
   Second Normal Form.............................................................................................................. 58
   Third Normal Form................................................................................................................. 58
   Summary of Normalization..................................................................................................... 59

Appendix C: Database Programming Using Transact-SQL ......................................................... 60
   Using Parameters to Supply Criteria to a Query..................................................................... 61
   Using the CASE Function....................................................................................................... 62
   Writing Triggers...................................................................................................................... 64

Appendix D: Solutions to Exercises ............................................................................................. 65




Advanced Applications of SQL
www.micros.umsl.edu