Homework Assignment 6

More SQL Programming

CS 3140
Database Systems
Fall Semester 2020

Due: Monday, November 2, at 11:59 PM

Introduction

The Problems

These problems give you a chance to practice writing basic SQL queries using techniques that you have learned, especially from Week 10.

The Deliverable

Create a single plaintext file named homework06.sql that contains your queries. You should use plaintext editor to create and edit your file. SQL code does not contain any Greek letters, subscripts, or non-standard characters.

I should be able to read your file into SQLite using the .read command and see the results of your queries.

Use comments and blank lines to identify and separate the answers to the different problems. In SQL...

• Single-line comments begin with -- (two hyphens).
• Multi-line comments begin with /* and end with */ similar to Java and C.

Format your code in a reasonable fashion. You can mimic the style you've seen in class notes: Short commands can appear on a single line. Break longer commands into meaningful phrases across multiple lines. Indentation can help us read subqueries. lines

The Database

This assignment uses the computer product database from Homework 4. It consists of four relations, with these schema:

    Product(maker, model, type)
    PC(model, speed, ram, hd, price)
    Laptop(model, speed, ram, hd, screen, price)
    Printer(model, color, type, price)

You may use this file, homework04.db, to test and debug your queries.

Problems

1. For each different laptop speed, find the average price of the laptops in that group. Display (a) the speed and (b) the average price as 'avg price'.


2. Find the average price of the PCs made by manufacturer A.
   
   Hint: A natural join is helpful here.


3. For each manufacturer, find the average screen size of its laptops.
   
   Hint: A natural join is helpful here.


4. Which laptops are faster than at least one PC that has a smaller hard drive than the laptop? Display the model, speed, and hard drive size of these laptops.
   
   Hint: A correlated subquery is helpful here. The shape of the solution is similar to that for Problem 6 on Homework 5.


5. Which manufacturer makes the color printer with the lowest price? Remember: the color attribute is a boolean value.
   
   Hint: Correlated subqueries are helpful here, too. Try breaking the problem down into steps:
   • find the price of the cheapest color printer
   • find the model for that printer
   • find the maker of that model
   Use these small pieces as subqueries to build your solution.

Deliverables

By the due time and date, submit your homework06.sql file using the electronic submission system.

Be sure to use the exact name specified for the file you submit! Otherwise, the autograder cannot find and execute your code.

If you need help or have any any questions, please ask promptly.