17_Subqueries.pdf - CITS1402 Relational Database Management...

This preview shows page 1 - 6 out of 18 pages.

CITS1402 Relational Database Management Systems Video 17 — Subqueries I Gordon Royle Department of Mathematics & Statistics
When once is not enough The basic SELECT command really only allows one of everything: I Creation of one large input table ( FROM ) I One selection from the rows of the input table ( WHERE ) I One organisation of the rows into groups ( GROUP BY ) I One summary/aggregation of each group ( COUNT , SUM etc) I One final pass through the summary rows ( HAVING ) In general, when one row is being processed, it is hard to use information about the other rows in the same table.
A two-step process Previously we found the longest match in two queries I Find length of longest match SELECT MAX (minutes) FROM MatchResult; I Use the value in a second query SELECT tournYear, winnerName, loserName, score FROM MatchResult WHERE minutes = 353;
Nested Queries A nested query is a query that involves another query as one of its component parts. SELECT tournYear, winnerName, loserName, score FROM MatchResult WHERE minutes = ( SELECT MAX (minutes) FROM MatchResult); This query involves a subquery to find the details of the longest men’s singles match in the Australian Open 2000-2019 database.
Inner query SELECT

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture