POTD 4: SQL Subqueries

Due 27-Feb-2024, 12pm/noon EST

---

Purpose:

• Be familiar with database queries
• Practice writing and interpreting SQL
• Get ready to work on homework assignment and course project

You may make a copy of a worksheet and complete this activity, or simply type your answers in any text editor.

You may work alone or with at most 2 other students (max size=3) in this course.

---

Expand / Collapse all

Use the following schema.

Product (maker, model, type)     -- maker is foreign key to Manufacturer(maker)
PC (model, speed, ram, hd, price)
Laptop (model, speed, ram, hd, screen, price)
Printer (model, color, type, price)

Alternatively, you may import Product-Laptop-PC-Printer.sql into your database server.

You may find subqueries helpful.

---

Write SQL queries to solve at least 5 of the following problems.

1. Find the makers of PC's with a speed of at least 3.0
   [sample solution]

   SELECT DISTINCT maker
   FROM    S24_Product
   WHERE   model IN
           (SELECT model
           FROM    S24_PC
           WHERE   speed >= 3.0);
   
   -- OR --
        
   SELECT DISTINCT R.maker
   FROM    S24_Product R
   WHERE   EXISTS
           (SELECT P.model
           FROM    S24_PC P
           WHERE   P.speed >= 3.0
               AND P.model = R.model); 

2. Find the printers with the highest price
   [sample solution]

   SELECT  P1.model
   FROM    S24_Printer P1
   WHERE   P1.price >= ALL
           (SELECT P2.price
           FROM    S24_Printer P2);
   
   -- OR --
      
   SELECT  P1.model
   FROM    S24_Printer P1
   WHERE   P1.price IN
           (SELECT MAX(P2.price)
           FROM    S24_Printer P2); 

3. Find the laptops whose speed is slower than that of any PC
   [sample solution]

   SELECT  L.model
   FROM    S24_Laptop L
   WHERE   L.speed < ANY
           (SELECT P.speed
           FROM    S24_PC P);
   
   -- OR --
           
   SELECT  L.model
   FROM    S24_Laptop L
   WHERE   EXISTS
           (SELECT P.speed
           FROM    S24_PC P
           WHERE   P.speed > L.speed); 

4. Find the maker of the color printer with the lowest price
   [sample solution]

   SELECT  R.maker
   FROM    S24_Product R,
           S24_Printer T
   WHERE   R.model = T.model
       AND T.price <= ALL
           (SELECT MIN(price)
           FROM    S24_Printer
           WHERE   color = TRUE);
           
   -- OR --
        
   SELECT  R.maker
   FROM    S24_Product R,
           S24_Printer T1
   WHERE   R.model = T1.model
       AND T1.price IN
           (SELECT MIN(T2.price)
           FROM    S24_Printer T2
           WHERE   color = TRUE); 

5. Consider the manufacturers that make printers, find the average hard disk size of a PC these manufacturers make
   [sample solution]

   SELECT  AVG(P.hd) AS Avg_HD_Size
   FROM    Product R,
           PC P
   WHERE   R.model  = P.model
   AND     R.maker IN
           (SELECT maker
            FROM    Product
            WHERE   type = 'printer'
           ); 

6. List all laptops with price greater than the average price of PC's
   [sample solution]

   SELECT * FROM S24_Laptop 
   WHERE price > (SELECT AVG(price) FROM S24_PC);

7. Find the average price of PC's and laptops made by manufacturer "EEE"
   [sample solution]

   SELECT AVG(T.price) 
   FROM   (SELECT P.price
           FROM    S24_Product R,
                   S24_PC P
           WHERE   R.model = P.model AND R.maker = 'EEE'
           
           UNION
           
           SELECT  L.price
           FROM    S24_Product R,
                   S24_Laptop L
           WHERE   R.model = L.model AND R.maker = 'EEE' 
           ) T;  

Expand / Collapse all

---

Grading rubric

[Total: 10 points]: Done (or provide evidence of your attempt, full or reasonable effort)

• (5 points) — Providing evidence of your attempt, minimal effort

(-2.5 points) for 24 hours late (submitted after 27-Feb-2024 12pm EST, by 28-Feb-2024 12pm EST)
(-5 points) for 48 hours late (submitted after 28-Feb-2024 12pm EST, by 29-Feb-2024 12pm EST)

---

Submission

• [optional] Take a selfie (or picture) of your team and submit it with your POTD
• Submit all your SQL code
• Include all team member names and computingIDs in the report.
• You may do one of the following:
  • take picture(s) of your POTD, or
  • save your POTD as a .txt, .sql, or .pdf file — No Word document.
• Each team submits only one copy.
• Upload your report as a PDF to POTD 4 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.
• Please verify that your POTD is accessible. Making your submission available to instructors and course staff is your responsibility; if we cannot access or open your file(s), we have to assign a zero grade. Be sure to test access to your file(s) before the due date.

---

Copyright © 2024 Upsorn Praphamontripong
Released under the CC-BY-NC-SA 4.0 license.
Last updated 2024-02-24 21:47