CS4750 - Database Systems

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.

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.

Find the makers of PC's with a speed of at least 3.0
Find the printers with the highest price
Find the laptops whose speed is slower than that of any PC
Find the maker of the color printer with the lowest price
Consider the manufacturers that make printers, find the average hard disk size of a PC these manufacturers make
List all laptops with price greater than the average price of PC's
Find the average price of PC's and laptops made by manufacturer "EEE"

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.

CC-BY-NC-SA 4.0 license.
Last updated 2024-02-24 21:47