SQL Select Statement Part 2

1. SQL Select Statement Part 2 IST359 M005 Yang Wang ywang@syr.edu 342 Hinds http://blackboard.syr.edu

2. Acknowledgements and caveat • These slides draw liberally, with permission, from the following sources: • IST359 materials by Prof. Michael Fudge Jr. • Slides from Murach’s SQL Server 2008 book • Caveat (beware): At best, PowerPoint slides are only a pale limitation of the entirety of a class meeting. In IST359 in particular, the lectures will cover topics beyond what appears in these slides. Don’t rely on them as a substitute for attending class.

3. Agenda • Select from, where, order by • Complex joins: recursive and 3 table joins • Aggregates: Group by and having • Views

4. Aggregates • They summarize your data… You no longer get a real row returned, but a summary of real rows. • Aggregate operators: • Count, Count distinct, Sum, Min, Max, Avg • DEMO TIME!

5. Group by and Having • Group by • Structured summarization • Select columns and expressions must appear in group by clause or are aggregate values • Having • Just like “Where” only after the summarization takes place. • DEMO TIME!

6. SELECT – The final version HOW WE SAY IT HOW IT IS PROCESSED FROM WHERE GROUP BY HAVING SELECT (Projection) ORDER BY TOP / DISTINCT • SELECT (Projection) • TOP / DISTINCT • FROM • WHERE • GROUP BY • HAVING • ORDER BY

8. Let’s Try These • What’s the average wage of Customer Service employees? • What’s the average product markup by vendor? • List all vendors that have less than 5 products, show the vendor name, website, how many products, average product retail price

9. Last example • List all vendors that have less than 5 products, show the vendor name, website, how many products, average product retail price selectvendor_name, vendor_website, count(product_id) as product_count, avg(product_retail_price) as product_avg_retail from fudgemart_vendors left join fudgemart_products onvendor_id= product_vendor_id group by vendor_name, vendor_website havingCOUNT(product_id) < 5

10. Last example – step 1 • List all vendors that have less than 5 products, show the vendor name, website, how many products, average product retail price select * from fudgemart_vendors left join fudgemart_products onvendor_id = product_vendor_id

11. Last example – step 2 • List all vendors that have less than 5 products, show the vendor name, website, how many products, average product retail price select * from fudgemart_vendors left join fudgemart_products onvendor_id = product_vendor_id group by vendor_name, vendor_website What’s wrong?

12. Last example – step 3 • List all vendors that have less than 5 products, show the vendor name, website, how many products, average product retail price select vendor_name, vendor_website, count(product_id) as product_count, avg(product_retail_price) as product_avg_retail from fudgemart_vendors left join fudgemart_products onvendor_id = product_vendor_id group by vendor_name, vendor_website

13. Last example – step 4 • List all vendors that have less than 5 products, show the vendor name, website, how many products, average product retail price select vendor_name, vendor_website, count(product_id) as product_count, avg(product_retail_price) as product_avg_retail from fudgemart_vendors left join fudgemart_products onvendor_id = product_vendor_id group by vendor_name, vendor_website havingCOUNT(product_id) < 5

14. Views – The External Model • They’re Metadata. DDL • Allow you to “save” a SELECT statement as a “virtual table” • The View can then be queried with a SELECT. • Simplifies complex queries.

15. Create Views CREATE VIEW viewname AS select-query-statement

16. Create Views create view v_s_vendors_with_avg_pricesas select vendor_name, vendor_website, count(product_id) as product_count, avg(product_retail_price) as product_avg_retail from fudgemart_vendors left join fudgemart_products on vendor_id = product_vendor_id group by vendor_name, vendor_website having COUNT(product_id) < 5