4 - The One-to-Many Relationship
Skill Builders

CREATE TABLE building (
bldnum    INTEGER,
bldname    CHAR(20),
   PRIMARY KEY(bldnum));

CREATE TABLE ROOM (
roomnum     INTEGER,
roomsize    INTEGER,
roomname    CHAR(20),
roomtype    CHAR(20),
bldnum      INTEGER,
   PRIMARY KEY (roomnum, bldnum),
   CONSTRAINT fkbuilding FOREIGN KEY(bldnum) REFERENCES building);

SELECT natname, SUM(stkqty*stkdiv) FROM nation JOIN stock
   ON stock.natcode = nation.natcode
   GROUP BY natname; 

There is only one Indian stock and the query specifies "greater than the average," and thus the average for Indian is the holding of that stock, which is equal to the average. By changing the query to "greater than or equal to the average," the Indian stock will be reported.

SELECT natname, stkfirm, stkqty from stock JOIN nation
ON stock.natcode = nation.natcode
WHERE stkqty >= 
  (SELECT AVG(stkqty) FROM stock
	   	WHERE stock.natcode = nation.natcode);

SELECT stkfirm, stkqty FROM stock stk1 WHERE 
   (SELECT COUNT(*) FROM stock stk2 WHERE stk2.stkqty >= stk1.stkqty) < 4; 

This is a challenging correlated query to understand, as most correlated queries are. First, you create two temporary copies of stock , which are called stk1 and stk2 (stock stk1 specifies creation of a temporary copy of stock that is called stk1). The inner part of the correlated query takes one row of stk1 at a time and counts how many rows in stk2 have a value of stkqty greater than or equal to the value of stkqty for the current stk1 row. If the count is less than 4, then the row is reported. In other words, if for any row of stock there are fewer than four other rows in stock with a greater or equal value of stkqty, then this row must have one of the three largest values of stkqty.

A student contributed the following solution. The solution shows mastery of SQL, but it is not as generalized as the previous answer. For example, what if the request is for the five stocks with the largest quantities?

SELECT * FROM stock WHERE
   stkqty >=  (SELECT MAX(stkqty) FROM stock 
      WHERE stkqty <  (SELECT MAX(stkqty) FROM stock
         WHERE stkqty <  (SELECT MAX(stkqty) FROM stock))) 
            ORDER BY stkqty DESC;

CREATE VIEW natavg
   AS (SELECT natcode, AVG(stkqty)as avgstkqty FROM stock GROUP BY natcode);

SELECT stkfirm, stock.natcode FROM stock JOIN natavg
ON stock.natcode = natavg.natcode
WHERE stock.stkqty > natavg.avgstkqty;

SELECT cdtitle, trktitle, trklength FROM cd JOIN track
   ON track.cdid = cd.cdid
      ORDER BY cdtitle, trklength;

SELECT track.cdid,trktitle, trklength FROM cd JOIN track
   ON trklength =
      (SELECT MAX(trklength) FROM track
         WHERE track.cdid = cd.cdid);

There is still some important information missing:

• No information on the artist(s)
• No information on the composer and producers of the music
• No information on the different recordings of a piece of music

Since every label has its own system of assigning IDs to their own CDs, there is some possibility that two labels will use the same ID for two different CDs.