CSE3330 Homework #1
Due on Monday October 11, before midnight
Worth 4% of the final grade

  1. (50 points) Consider the following schema:
    Product ( maker, model, address, phone, email )
    PC ( model, speed, ram, disk, monitor, price )
    Laptop ( model, speed, ram, disk, screen, price )
    Printer ( model, type, price )
    where a product model is a PC model, a laptop model, or a printer model. Note that PC.model, Laptop.model, and Printer.model are foreign keys that reference Product.model. Express the following queries in SQL:
    1. Print the makers of PCs with a speed of at least 3GHz.
    2. Print the printer models with the highest price.
    3. Print the laptop models whose speed is slower than the speed of any PC.
    4. Print the maker of the color printer with the lowest price.
    5. Print the maker of the PC with the fastest processor among all those PC's that have less than 4GB of RAM.
    6. For each different PC maker, print the maker name and the average price of the maker's PCs.

  2. (50 points) Consider the following relational database schema that contains information about Olympic athletes, events, and competition results:
    Athlete ( AName, Age, Country )
    Event ( EName, Type, Location, Date, Time )
    Competes ( AName, EName, Score )
    Express the following queries in SQL:
    1. Print the names of all athletes who are from the USA and are older than 30.
    2. Print the name, location, date, and time of all swimming events.
    3. Print the names of the Chinese athletes who competed in swimming on 08/14/08.
    4. Print the names of all athletes who are not competing in any event.
    5. Print the names of all athletes who are competing in 2 or more events.
    6. For every different event name, print the event name and the number of athletes competing in this event.

How to Submit the Homework

You need to submit your homework solution online using the following web form. Acceptable document formats are: Microsoft word/powerpoint document, PDF, and postscript. You may submit it as many times as you like, but only the most recently submitted file will be retained and evaluated. Not that, this time we will not accept any email or hardcopy submission. If you cannot login or have a problem submitting the homework using this form, ask the GTA for help.

Submit Homework #1:

Last modified: 10/07/10 by Leonidas Fegaras