1 / 9

ITE 370: Nested Queries

ITE 370: Nested Queries. Jeffrey P. Landry University of South Alabama. Nested Query-Defined. A nested query is a query within a query A complete SELECT statement embedded within another SELECT statement Nested queries are also called subqueries. Query Results.

kanan
Download Presentation

ITE 370: Nested Queries

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. ITE 370: Nested Queries Jeffrey P. Landry University of South Alabama University of South Alabama School of CIS

  2. Nested Query-Defined • A nested query is a query within a query • A complete SELECT statement embedded within another SELECT statement • Nested queries are also called subqueries University of South Alabama School of CIS

  3. Query Results • The results of various queries can be thought of as: • a table (multiple rows and columns) • a list of values (a single column) • or a single value (one row, one column, i.e. a cell) • Subqueries may be placed wherever you can place a table, list of values, or a single value University of South Alabama School of CIS

  4. Subqueries that Return a Single Value • Queries that return a single value (cell) can be used wherever a literal value is allowed • Example: List all baseball players who hit more home runs than the average SELECT Round(Avg(HR)) FROM Player  SELECT First, Last, HR FROM PLAYER WHERE HR > (SELECT Avg(HR) FROM Player)  University of South Alabama School of CIS

  5. Subqueries that Return a List of Values • Queries that return a list of values (from one column) can be used wherever a value list is required (eg. the IN predicate) • Example: List all National League players SELECT TeamId FROM Team WHERE League = ‘NL’  { Same as (2, 3, 4, 8, 9, …) } SELECT Last, First FROM Player WHERE TeamId IN (SELECT TeamId FROM Team WHERE League = ‘NL’) University of South Alabama School of CIS

  6. Subqueries that Return a Table • Queries that return a table (multiple rows and columns) can be used wherever a table is required • For example, in the FROM statement • Use aliasing (AS <tablename> • Example: Players that lead their team in home runs University of South Alabama School of CIS

  7. Example of Nested Query that Return a Table SELECT TeamId, Max(HR) -- set of team-leading FROM Player -- HR quantities GROUP BY Team -- find out who the team leaders are SELECT Last, First, Player.TeamId, HR FROM Player, (SELECT TeamId, Max(HR) AS TopHR FROM Player GROUP BY TeamId) As TopTeamHR WHERE (Player.TeamId = TopTeamHR.TeamId) AND (Player.HR = TopTeamHR.TopHR) University of South Alabama School of CIS

  8. Nested Queries and EXISTS • EXISTS asks “does at least one row exist?” • Accepts a SELECT query as its lone parameter • Interprets SELECT in boolean terms • True means there is at least one row that satisfies the query • False means empty set is returned • The nested SELECT query must refer to the outer query (correlated query), and is executed for each row in the outer query University of South Alabama School of CIS

  9. EXISTS example Problem: List all the teams that have no players Solution: SELECT * FROM Team WHERE NOT EXISTS (SELECT * FROM Player WHERE Player.TeamId=Team.TeamId); University of South Alabama School of CIS

More Related