Homework Assignment 5

SQL Programming

CS 3140
Database Systems
Fall Semester 2020

Due: Thursday, October 22, 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 9.

The Deliverable

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

Use comment 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. Find the model number, speed, and hard-disk size for all PCs priced under $1000.


2. Do the same as #1, but
   • rename the speed column "speed in GHz" and the hd column "RAM in GB"
   • display the results in descending order of speed


3. Find the model number and type of all color printers. Remember that the color attribute is a boolean value.


4. Find the manufacturer and speed of all laptops with a hard disk of at least 30 gigabytes.


5. Find the manufacturers of the PCs with a speed of at least 3.0 gigahertz.
   Use at least one subquery in your answer.


6. Find the model and RAM size of the PCs that are slower than every laptop.
   Use at least one subquery in your answer.

Deliverables

By the due time and date, submit your homework05.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.