Advanced querying

      SELECT ItemNo, ItemDesc, ItemPrice
      FROM ITEM
      WHERE ItemPrice > (SELECT AVG(ItemPrice)
                         FROM ITEM);

The inner query is executed first and returns a single row:

The outer query is then executed, the value returned is placed in each row of the table created by the outer query and evaluated (as we know, SQL can only compare a single row at a time). The rows that match the criteria are returned.

      SELECT init.ItemNo, ItemDesc
      FROM InvoiceItem init, Item it
      WHERE init.ItemNo = it.ItemNo
      GROUP BY init.ItemNo, ItemDesc
      HAVING SUM(Quantity) >= ALL (SELECT SUM(Quantity)
                              FROM InvoiceItem
                              GROUP BY ItemNo);

If we used the ANY operator in the above query, all of the values from the inner query will be evaluated as true in the outer query and displayed.  Try it and see.

The inner query returns:

This is then evaluated by the outer query.

This is the opposite of an earlier query that finds all the customers that have an invoice against them.

      SELECT CustABN AS ABN, CustName AS Name
      FROM CUSTOMER
      WHERE CustABN NOT IN (SELECT c.CustABN
                            FROM CUSTOMER c, INVOICE i
                            WHERE c.CustABN = i.CustABN);

Here the logical NOT operator is used with the IN operator. The inner query is the earlier query that found all customers with an invoice against them. Using NOT IN will now finds all the records that weren’t retuned by the inner query.

      SELECT i.ItemNo, ItemDesc
      FROM ITEM i
      WHERE NOT EXISTS (SELECT *
                        FROM INVOICEITEM ii
                        WHERE i.ItemNo = ii.ItemNo);

      SELECT ItemDesc, ItemPrice, AvgPrice
      FROM (SELECT AVG(ItemPrice) AS AvgPrice FROM ITEM), ITEM
      WHERE ItemPrice < AvgPrice;

In an earlier exercise we had found the most expensive and least expensive items stocked using:

      SELECT MAX(ItemPrice) AS Dearest, MIN(ItemPrice) AS Cheapest
      FROM ITEM;

To now include the product descriptions:

     (SELECT ItemDesc AS Item, ItemPrice
      FROM ITEM
      WHERE ItemPrice IN (SELECT MAX(ItemPrice)
                          FROM ITEM))
      UNION

     (SELECT ItemDesc AS Item, ItemPrice
      FROM ITEM
      WHERE ItemPrice IN (SELECT MIN(ItemPrice)
                          FROM ITEM));

      SELECT itemdesc
      FROM item
      WHERE itemprice > ALL (SELECT itemprice
                             FROM item
                             WHERE itemdesc LIKE '%Freeze%');

      SELECT itemdesc
      FROM item
      WHERE itemprice > ANY (SELECT itemprice
                             FROM item
                             WHERE itemdesc LIKE '%Freeze%');

      SELECT DISTINCT i.invoiceno
      FROM invoice i, invoiceitem ii
      WHERE i.INvoiceNo= ii.InvoiceNo
      AND ii.itemno IN (SELECT Itemno
                        FROM invoiceitem
                        WHERE invoiceno = 3);

      SELECT *
      FROM item
      WHERE itemno <> ALL (SELECT itemno
                           FROM invoiceitem
                           WHERE quantity = 1)
      ORDER BY itemno;

OR alternatively

      SELECT *
      FROM item
      WHERE itemno NOT IN (SELECT itemno
                           FROM invoiceitem
                           WHERE quantity = 1)
      ORDER BY itemno;

      SELECT g1.student_No, g1.Course_No, g1.Grade, g2.Course_No, g2.Grade
      FROM Grade g1, Grade g2
      WHERE g1.student_No = g2.student_No
      AND g1.Course_No = 'HIT232'
      AND g2.Course_No = 'HIT999';

      SELECT Name
      FROM Students
      WHERE Coordinator_No = (SELECT Coordinator_No
                              FROM Students
                              WHERE Name = 'Donald Duck');

In this query the subquery only returned a single value and so the = operator could be used. Had the subquery returned more than one row or no rows and = still be used there would have been a runtime error.

In cases where a subquery could return no value, one value or many values, ANY or ALL should be used. This query could be rewritten as:

      SELECT Name
      FROM Students
      WHERE Coordinator_No = ANY (SELECT Coordinator_No
                                  FROM Students
                                  WHERE Name = 'Donald Duck');

      SELECT name
      FROM student s1
      WHERE NOT EXISTS ( SELECT *
                        FROM student s2
                        WHERE s1. name <> s2.name
                        AND s1.coordinator_no = s2.coordinator_no);

      (SELECT Student_No
      FROM student;
      WHERE Coord_No = 23)
      UNION
      (SELECT Student_No
      FROM Grade
      WHERE Course_No='HIT999' );

      SELECT Name
      FROM Coordinator
      WHERE (SELECT Count(*)
            FROM Student
            WHERE Student.coord_no=Coordinator.coord_no) >10;

      SELECT coord_no, Count(*)
      FROM Student
      GROUP BY coord_no
      HAVING max(total_units) > 160
      OR coord_no IN (SELECT coord_no
                     FROM grade, student
                     WHERE course_no = 'HIT232'
                     AND student.student_no = grade.student_no);

      (SELECT maker
      FROM Product p, printer pr
      WHERE p.model=pr.model)
      MINUS
      (SELECT maker
      FROM Product p, pc
      WHERE p.model=pc.model);

      SELECT hd
      FROM pc
      GROUP by hd
      HAVING count(*) >=2;

      SELECT DISTINCT pc1.model
      FROM pc pc1, pc pc2
      WHERE pc1.speed = pc2.speed
      AND pc1.ram = pc2.ram
      AND pc1.model <> pc2.model;

      SELECT maker
      FROM (SELECT maker, p.model, pc.speed
            FROM product p, pc
            WHERE p.model=pc.model
            AND pc.speed >= 1000
            UNION
            SELECT maker, p.model, l.speed
            FROM product p, laptop l
            WHERE p.model=l.model
            AND l.speed >= 1000)
      GROUP BY maker
      HAVING count(*) >= 2;

      SELECT *
      FROM pc
      WHERE speed <= ALL (select speed from laptop);

OR

      SELECT *
      FROM pc
      WHERE speed < (SELECT min(speed) FROM laptop);

      SELECT model
      FROM
            (SELECT model, price FROM pc
            UNION
            SELECT model, price FROM laptop
            UNION
            SELECT model, price FROM printer)
      WHERE price = (SELECT max(price)
                  FROM
                  (SELECT model, price FROM pc
                  UNION
                  SELECT model, price FROM laptop
                  UNION
                  SELECT model, price FROM printer));

      SELECT p.maker
      FROM pc pc1, product p
      WHERE pc1.model = p.model
      AND ram = (SELECT min(ram) FROM pc)
      AND speed >= ALL( SELECT speed
                        FROM pc
                        WHERE ram=pc1.ram);

      SELECT p.model, p.maker
      FROM pc, product p
      WHERE pc.model = p.model
      AND (ram, speed) IN (SELECT ram, max(speed)
                        FROM pc
                              GROUP BY ram
                              HAVING ram = (SELECT min(ram) FROM pc) );