1 / 36

Single Table Queries Part 2

Single Table Queries Part 2. Using Functions. Tourism_Revenue Table. Select min(Revenues) as ‘Minimum Revenues’ from Tourism_Revenue. Result Set. Tourism_Revenue Table. Select count(*) as ‘Record Count’ from Tourism_Revenue. Result Set. NFL_Fans Table. select NFL_Team,

ciqala
Download Presentation

Single Table Queries 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. Single Table QueriesPart 2

  2. Using Functions A Guide to MySQL

  3. Tourism_Revenue Table Select min(Revenues) as ‘Minimum Revenues’ from Tourism_Revenue Result Set

  4. Tourism_Revenue Table Select count(*) as ‘Record Count’ from Tourism_Revenue Result Set

  5. NFL_Fans Table select NFL_Team, avg(Age) as 'Avg Fan Age' from NFL_Fans group by NFL_Team Result Set

  6. NFL_Fans Table select NFL_Team, count(*) as 'Fan Count' from NFL_Fans group by NFL_Team Result Set

  7. NFL_Fans Table select NFL_Team, count(*) as 'Fan Count' from NFL_Fans group by NFL_Team having count(*) > 2 Result Set

  8. where vs. having • where clause • filters out data not meeting a certain criteria • data that is not filtered out can then be placed into groups • having clause • filters out groups not meeting a certain criteria

  9. select Political_Party, count(*) as 'Member count' from USA where State = 'WI' group by Political_Party having count(*) > 10000 order by 1

  10. U.S. Population: 305,709,250 select Political_Party, count(*) as 'Member count' from USA where State = 'WI' group by Political_Party having count(*) > 10000 order by 1 WI Population: 5,363,675 Would it make a whole lot of sense for us to have everyone in the U.S. get into groups based on Political Party if we really only want to put Wisconsin people into groups? No. We only care about 5.3 million people, so there’s no reason to have the other 300 million + people get into groups.

  11. U.S. Population: 305,709,250 select Political_Party, count(*) as 'Member count' from USA where State = 'WI' group by Political_Party having count(*) > 10000 order by 1 WI Population: 5,363,675 Would it make a whole lot of sense for us to have everyone in the U.S. get into groups based on Political Party if we really only want to put Wisconsin people into groups? No. We only care about 5.3 million people, so there’s no reason to have the other 300 million + people get into groups. So we filter out people from the other 49 states before we even create groups (where clause)

  12. U.S. Population: 305,709,250 select Political_Party, count(*) as 'Member count' from USA where State = 'WI' group by Political_Party having count(*) > 10000 order by 1 WI Population: 5,363,675 Can we have Political Party groups with 10,000 or less members go home before we even place the people of Wisconsin into groups? No. We need to put people into groups so that we can count how many are in each group.

  13. U.S. Population: 305,709,250 select Political_Party, count(*) as 'Member count' from USA where State = 'WI' group by Political_Party having count(*) > 10000 order by 1 WI Population: 5,363,675 Can we have Political Party groups with 10,000 or less members go home before we even place the people of Wisconsin into groups? No. We need to put people into groups so that we can count how many are in each group. So we place the people from Wisconsin into groups based on Political Party (group by clause)

  14. U.S. Population: 305,709,250 select Political_Party, count(*) as 'Member count' from USA where State = 'WI' group by Political_Party having count(*) > 10000 order by 1 WI Population: 5,363,675 Let’s say we have 20 political parties in Wisconsin, but only 2 have member counts greater then 10,000. Would it make sense to have all 20 parties line up in alphabetical order if we only care about seeing in order the 2 that have more then 10,000 members? No. I struggle enough with the alphabet, so I’d much rather order just the 2 groups instead of all 20.

  15. U.S. Population: 305,709,250 select Political_Party, count(*) as 'Member count' from USA where State = 'WI' group by Political_Party having count(*) > 10000 order by 1 WI Population: 5,363,675 Let’s say we have 20 political parties in Wisconsin, but only 2 have member counts greater then 10,000. Would it make sense to have all 20 parties line up in alphabetical order if we only care about seeing in order the 2 that have more then 10,000 members? No. I struggle enough with the alphabet, so I’d much rather order just the 2 groups instead of all 20. So we get rid of the groups that do not have more then 10,000 members (having clause)

  16. U.S. Population: 305,709,250 select Political_Party, count(*) as 'Member count' from USA where State = 'WI' group by Political_Party having count(*) > 10000 order by 1 WI Population: 5,363,675 Now that we only have Wisconsin people in our groups, and we only have the groups with more then 10,000 members being returned, let’s sort the data!

  17. U.S. Population: 305,709,250 select Political_Party, count(*) as 'Member count' from USA where State = 'WI' group by Political_Party having count(*) > 10000 order by 1 WI Population: 5,363,675 Now that we only have Wisconsin people in our groups, and we only have the groups with more then 10,000 members being returned, let’s sort the data! (Order by clause)

  18. Instructor Table What will the following query return? select dept_id, count(*) from instructor Error Query is missing a “group by” clause

  19. Instructor Table What will the following query return? select dept_id, count(*) from instructor group by dept_id Results

  20. Nesting Queries • Query results require two or more steps • Subquery: an inner query placed inside another query • Outer query uses subquery results A Guide to MySQL

  21. A_Owner Table Let’s say we want to find slip information for all boats owned by people with a first name of Mary We know first name information does not exist in the Marina Slip table, which is where the slip information exists, but it does exist in the Owner table A_Marina_Slip Table We also know the Owner table and the Marina Slip table are related to each other by OWNER_NUM

  22. A_Owner Table We can write a query against the Owner table using the filter information provided (First_Name = ‘Mary’) to bring back Owner_Num select owner_num from a_owner where first_name = 'Mary' A_Marina_Slip Table

  23. A_Owner Table We can write a query against the Owner table using the filter information provided (First_Name = ‘Mary’) to bring back Owner_Num select owner_num from a_owner where first_name = 'Mary' A_Marina_Slip Table

  24. A_Owner Table We can use the result set from the query against the Owner table in a query against the Marina Slip table select owner_num from a_owner where first_name = 'Mary' Select * from a_marina_slip where owner_num in ( A_Marina_Slip Table )

  25. A_Owner Table We can use the result set from the query against the Owner table in a query against the Marina Slip table select owner_num from a_owner where first_name = 'Mary' Select * from a_marina_slip where owner_num in ( A_Marina_Slip Table Result Set )

  26. Select * from a_marina_slip where owner_num in ( select owner_num from a_owner where first_name = 'Mary' ) Outer query Subquery The result of the subquery is used by the outer query

  27. A_Owner Table Let’s say we want to find owner information for people who own a Sprite 4000 A_Marina_Slip Table

  28. A_Owner Table A_Marina_Slip Table

  29. A_Owner Table Select * from a_owner where owner_num = ( select owner_num from a_marina_slip where boat_type = 'Sprite 4000' ) A_Marina_Slip Table Query will error because the subquery returns more then one record.

  30. A_Owner Table Select * from a_owner where owner_num in ( select owner_num from a_marina_slip where boat_type = 'Sprite 4000' ) A_Marina_Slip Table Result Set

  31. Null • A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

  32. select slip_id, next_service_date from a_service_request

  33. select slip_id, next_service_date from a_service_request where next_service_date is null

  34. select slip_id, next_service_date from a_service_request where next_service_date is not null

  35. Using the tables in Alexamara Marina Return the marina number, slip number, and boat name of every slip who’s rental fee is greater then average of all rental fees.

  36. What does ETL stand for? • Extract, Transform, and Load • ETL is a term/concept frequently associated with Data Warehousing • Examples of ETL Tools • InformaticaPowerCenter • SSIS (SQL Server Integration Services; MS SQL Server 2005 and above) • DTS (Data Transformation Services; MS SQL Server 2000) • IBM CognosDecisionStream

More Related