1 / 16

SQL Select Statement Part 2

SQL Select Statement Part 2. IST359 M005. Yang Wang ywang@syr.edu 342 Hinds http://blackboard.syr.edu. Acknowledgements and caveat. These slides draw liberally, with permission, from the following sources: IST359 materials by Prof. Michael Fudge Jr.

ledell
Télécharger la présentation

SQL Select Statement Part 2

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. 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

  7. 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

  8. 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

  9. 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

  10. 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?

  11. 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

  12. 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

  13. 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.

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

  15. 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

More Related