SQL Advanced Concepts Overview
This content covers various advanced SQL concepts such as Grouping and Aggregation, Execution Order, Joins, Maximum Finding, and Line Formatting. It provides examples and explanations on how to use these concepts effectively in SQL queries. The visual aids help in understanding the concepts better.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
1 SQL Additional Notes
SQL Additional Notes 2 1 Group and Aggregation* 2 Execution Order* 3 Join* 4 Find the maximum 5 Line Format *partially from University of Washington CSE 554
Grouping and Aggregation Purchase(product, date, price, quantity) Find total sales after 10/1/2005 per product. SELECT FROM Purchase WHERE date > 10/1/2005 GROUP BY product product, Sum(price*quantity) AS TotalSales Let s see what this means
1&2. FROM-WHERE-GROUPBY Product Bagel Bagel Banana Banana Date 10/21 10/25 10/3 10/10 Price 1 1.50 0.5 1 Quantity 20 20 10 10
3. SELECT Product TotalSales Product Bagel Bagel Banana Banana Date 10/21 10/25 10/3 10/10 Price 1 1.50 0.5 1 Quantity 20 20 10 10 Bagel 50 Banana 15 SELECT FROM Purchase WHERE date > 10/1/2005 GROUP BY product product, Sum(price*quantity) AS TotalSales
GROUP BY v.s. Nested Quereis SELECT FROM Purchase WHERE date > 10/1/2005 GROUP BY product product, Sum(price*quantity) AS TotalSales SELECT DISTINCT x.product, (SELECT Sum(y.price*y.quantity) FROM Purchase y WHERE x.product = y.product AND y.date > 10/1/2005 ) AS TotalSales FROM Purchase x WHERE x.date > 10/1/2005
HAVING Clause Same query, except that we consider only products that had at least 30 quantity sale SELECT product, Sum(price * quantity) FROM Purchase WHERE date > 10/1/2005 GROUP BY product HAVING Sum(quantity) > 30 HAVING clause contains conditions on aggregates.
General form of Grouping and Aggregation SELECT S FROM R1, ,Rn WHERE C1 GROUP BY a1, ,ak HAVING C2 S = may contain attributes a1, ,ak and/or any aggregates but NO OTHER ATTRIBUTES C1 = is any condition on the attributes in R1, ,Rn C2 = is any condition on aggregate expressions
Execution Order SELECT S FROM R1, ,Rn WHERE C1 GROUP BY a1, ,ak HAVING C2 Evaluation steps: Evaluate FROM-WHERE, apply condition C1 1. Group by the attributes a1, ,ak Apply condition C2 to each group (may have aggregates) Compute aggregates in S and return the result 2. 3. 4.
Join Explicit joins in SQL = inner joins : Product(name, category) Purchase(prodName, store) SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodName Same as: SELECT Product.name, Purchase.store FROM Product, Purchase WHERE Product.name = Purchase.prodName But Products that never sold will be lost !
Outerjoins Left outer joins in SQL: Product(name, category) Purchase(prodName, store) SELECT Product.name, Purchase.store FROM Product LEFT OUTER JOIN Purchase ON Product.name = Purchase.prodName
Product Purchase Name Category ProdName Store Gizmo gadget Gizmo Wiz Camera Photo Camera Ritz OneClick Photo Camera Wiz Name Store Gizmo Wiz Camera Ritz Camera Wiz OneClick NULL
Outer Joins Left outer join: Include the left tuple even if there s no match Right outer join: Include the right tuple even if there s no match Full outer join: Include the both left and right tuples even if there s no match
Find the maximum Find student with highest grade Student Grade Method 1 select student, grade from tableA where grade = (select max(grade) from tableA) A 90 select student, grade from tableA where grade = (select max(grade) from tableA) B 100 C 80 Method 2 tableA select * from( select student, grade from tableA order by Grade DESC ) where ROWNUM <=1
Find the maximum Student Grade What if they have a tie Method 2 will only give the first row which is (A,100) Method 3 A 100 B 100 C 80 select * from( select student, rank() over (order by grade desc) as RNK from tableA ) where RNK <=1 *We will not have ties in grading of Project 2
Format You will not lose points because of format the tabs, spaces, center or left alignment. But a good format is preferred. Easier to grade and good for further development To avoid line warp When creating table, use appropriate column size. For instance, sname varchar(50) instead of varchar(255) SET LINESIZE 300
17 Questions?
