{"id":582,"date":"2021-11-17T09:11:18","date_gmt":"2021-11-17T09:11:18","guid":{"rendered":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/?page_id=582"},"modified":"2021-11-19T09:21:54","modified_gmt":"2021-11-19T09:21:54","slug":"subqueries","status":"publish","type":"page","link":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/advanced-higher\/subqueries\/","title":{"rendered":"Subqueries"},"content":{"rendered":"<p><strong>Examples are based on the <a href=\"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/reference\/sample-databases\/\">Travel Agency<\/a> database<\/strong><\/p>\n<p>At Higher level, subqueries were created by saving them as SQL <strong>views<\/strong>, and then including the view in the FROM clause of the outer query.<\/p>\n<p>However, modifying the full query would involve editing both the main query and the view separately.<\/p>\n<p>Including subqueries in the WHERE clause of a query keeps everything is in one place.<\/p>\n<h1>Subquery in the WHERE clause of a SELECT query<\/h1>\n<p>A subquery is a query embedded within the WHERE clause of another SQL query. A subquery is sometimes referred to as an inner query or a nested query, and an SQL query is sometimes referred to as the outer query or the parent query.<\/p>\n<p>The subquery executes before the main query, so the results can be passed to the main query as a condition to further restrict the data to be retrieved.<\/p>\n<p>There are a few rules that subqueries must follow:<\/p>\n<ul>\n<li>Subqueries must be enclosed within brackets.<\/li>\n<li>Unless the main query has multiple fields in its SELECT clause, a subquery can have only one field in its SELECT clause.<\/li>\n<li>The BETWEEN operator can be used within a subquery but cannot be applied to the results of a subquery returned to the main query.<\/li>\n<li>Although an ORDER BY clause can be used with the main query, an ORDER BY clause cannot be used in a subquery; if it is needed, the GROUP BY clause can be used to perform the same function as the ORDER BY within a subquery.<\/li>\n<\/ul>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">SELECT field names\r\nFROM table names\r\nWHERE fieldName OPERATOR\r\n    (SELECT field names\r\n    FROM table names\r\n    WHERE condition)\r\nORDER BY field names;\r\n<\/pre>\n<h1>Single-Value subqueries<\/h1>\n<p>Many subqueries return exactly one record (called single-value subqueries).\u00a0The developer must check that this is the case, because an error will be generated if a subquery returns more results than expected.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">-- Display details of most expensive hotel\r\nSELECT hotelName, starRating, pricePersonNight\r\nFROM Hotel\r\nWHERE pricePersonNight = (SELECT MAX(pricePersonNight)\r\n                          FROM Hotel );\r\n<\/pre>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">-- Display details all hotels that have a below-average star rating.\r\nSELECT resortName, hotelName, starRating\r\nFROM Resort, Hotel\r\nWHERE Resort.resortID = Hotel.resortID\r\nAND starRating &lt; (SELECT AVG(starRating) \r\n                  FROM Hotel);<\/pre>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\/* Display details of the customer who booked the same hotel \r\nas the customer with ID 111. *\/\r\nSELECT firstName, surname, postcode\r\nFROM Customer, Booking\r\nWHERE Customer.customerNo = Booking.customerNo\r\n   AND NOT Customer.customerNo = 111\r\n   AND hotelRef = (SELECT hotelRef \r\n                   FROM Booking\r\n                   WHERE customerNo = 111\r\n                  );\r\n<\/pre>\n<h1>Multiple-Value subqueries<\/h1>\n<p>Subqueries that return more than one row (called multiple-value subqueries), can only be used with multiple-value operators such as EXISTS, IN and ANY.<\/p>\n<h2>IN operator<\/h2>\n<p>This allows multiple values to be specified as an alternative to multiple OR conditions.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\/* display data on all hotels booked by customer ID 315.\r\nThe subquery generate list of all hotelRefs that customer 315 has booked.\r\nThe outer query looks for hotels that have a hotelref in the subquery. *\/\r\nSELECT hotelName, starRating\r\nFROM Hotel\r\nWHERE hotelRef IN (SELECT hotelRef FROM Hotel, Booking\r\n                    WHERE Hotel.hotelRef = Booking.hotelRef\r\n                    AND customerNo = 315);\r\n<\/pre>\n<h2>ANY operator<\/h2>\n<p>This returns true if any of the subquery values meet the condition specified in the main query.<\/p>\n<p><em>Note: SQLite does not support the ANY operator. However you can still be required to complete design and coding questions that use this.<\/em><\/p>\n<ul>\n<li><em><strong>WHERE fieldname &gt; ANY (subquery)<br \/>\n<\/strong>is equivalent to greater than the minimum value in the subquery&#8217;s list<\/em><\/li>\n<li><em><strong>WHERE fieldname &lt; ANY (subquery)<br \/>\n<\/strong><\/em><em> is equivalent to less than the maximum value in the subquery&#8217;s list<br \/>\n<\/em><\/li>\n<li><em><strong>WHERE fieldname = ANY (subquery)<br \/>\n<\/strong><\/em><em> is equivalent to IN subquery&#8217;s list<br \/>\n<\/em><\/li>\n<\/ul>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\/*Display the customer number, hotel reference, and booking cost for any booking \r\nthat costs more than any bookings made by customers with surnames Lowden, Shawfair or \r\nSheriffhall.\r\nThe subquery returns list of costs for all booking made by the three specified customers\r\nThe outer query finds all bookings that cost more than any of the subquery cost *\/\r\nSELECT customerNo, \r\n       Hotel.hotelRef, \r\n       pricePersonNight*numberNights*numberInParty AS &#x5B;Booking Cost]\r\nFROM Booking, Hotel\r\nWHERE Booking.hotelRef = Hotel.hotelRef\r\n      AND pricePersonNight*numberNights*numberInParty &gt; ANY\r\n          (SELECT pricePersonNight*numberNights*numberInParty\r\n           FROM Booking, Hotel, Customer\r\n           WHERE Booking.hotelRef = Hotel.hotelRef\r\n           AND Booking.customerNo = Customer.customerNo\r\n           AND surname IN (&quot;Danderhall&quot;, &quot;Lowden&quot;, &quot;Shawfair&quot;)\r\n          );\r\n<\/pre>\n<h2>EXISTS<\/h2>\n<p>This tests for the existence of records within the subquery and returns true when the subquery returns one or more records (this is very useful to obtain records that do not meet a certain condition).<\/p>\n<p>Notes:<\/p>\n<ul>\n<li>EXISTS only checks for presence of any records. The SELECT clause of the subquery could return any field, * is returned to keep the SQL simpler\/readable.<\/li>\n<li>The subquery&#8217;s WHERE clause links to a table that is not in its own FROM clause, but to a table in outer query<\/li>\n<\/ul>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\/* Display details of all 3-star hotels that have bookings. *\/ \r\nSELECT hotelname, mealPlan, starRating, resortName\r\nFROM Hotel, Resort\r\nWHERE Hotel.resortID = Resort.resortID\r\n      AND starRating = 3\r\n      AND EXISTS (SELECT * FROM Booking\r\n                  WHERE Booking.hotelRef = Hotel.hotelRef)\r\n<\/pre>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">-- Display details of customers who have never made a booking.\r\nSELECT firstName, surname, address\r\nFROM Customer\r\nWHERE NOT EXISTS (SELECT * FROM Booking\r\n                  WHERE Customer.customerNo = Booking.customerNo);\r\n<\/pre>\n<h1>Multiple Subqueries<\/h1>\n<p>Two independent subqueries:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\/* Display the name, star rating, and total of nights booked for hotels that have:\r\n- a total number of customer nights booked that is more than the total number of nights\r\n  booked by the customer with ID 290 (number of nights booked multiplied by number in party)\r\n- and a star rating which is less than that of the hotel with the highest star rating\r\nThe query should list the hotels from lowest star rating to the highest.*\/\r\nSELECT hotelName, starRating, \r\n       SUM(numberNights*numberInParty) AS &#x5B;Customers Nights]\r\nFROM Hotel, Booking\r\nWHERE Hotel.hotelRef= Booking.HotelRef\r\n      AND numberNights*numberInParty &gt; ( SELECT SUM(numberNights*numberInParty) \r\n                                         FROM Booking\r\n                                         WHERE customerNo =290)\r\n      AND starRating &lt; (SELECT MAX(starRating) FROM Hotel)\r\nGROUP BY hotelName, starRating\r\nORDER BY starRating;\r\n<\/pre>\n<p>A subquery inside a subquery:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n\/* Display details of the second most expensive hotels *\/\r\n-- innermost subquery finds most of expensive price of all hotels\r\n-- subquery then finds largest price that is less than the most expensive price\r\nSELECT * \r\nFROM hotel\r\nWHERE pricePersonNight = ( SELECT MAX(pricePersonNight)\r\n                           FROM hotel\r\n                           WHERE pricePersonNight &lt; ( SELECT MAX(pricePersonNight)\r\n                                                      FROM hotel )\r\n                         )\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Examples are based on the Travel Agency database At Higher level, subqueries were created by saving them as SQL views, and then including the view in the FROM clause of the outer query. However, modifying the full query would involve editing both the main query and the view separately. Including subqueries in the WHERE clause of a query keeps everything is in one place. Subquery in the WHERE clause of<\/p>\n<p><a class=\"more-link\" href=\"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/advanced-higher\/subqueries\/\">Read More<\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"parent":533,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-582","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/582","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/comments?post=582"}],"version-history":[{"count":29,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/582\/revisions"}],"predecessor-version":[{"id":629,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/582\/revisions\/629"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/533"}],"wp:attachment":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/media?parent=582"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}