Exercises
uses Hardware Database defined in Exercises 2.3.1 (pages 36-37) and 2.4.1 (pages 53-54) in the textbook. Write, run, and save the following SQL queries and operations in this database.
Queries (2 pts each)
Query 1. Find the makers that produce PCs or laptops, but do not produce printers.
Query 2. For each maker that produces laptops, find the number of laptop models produced by this maker. Output a table consisting of two columns: (maker, number of models). Sort the result by numbers of models in descending order.
Query 3. For each maker that produces laptops, find the average price of the models of laptops produced by this maker. Round the price up to the nearest integer. Output a table consisting of two columns: (maker, average price).
Query 4. Find the laptop models with the maximum ram and their makers. Output a table consisting of three columns: (model, ram, maker).
Query 5. Find the makers that produce laptops with at least three different speeds.
Query 6. Find the models of laptops whose prices are lower than the average price of laptop models. Output a table consisting of two columns: (model, price). Sort the result by prices in ascending order.
Query 7. Find the makers that produce only laptops.
Data Definition and Data Manipulation Operations (2 pts each)
Operation 1. Use a CREATE TABLE statement to create a suitable schema for relation FlashDrive with three attributes: model (SMALLINT), storage (NUMBER), and price (SMALLINT). Declare the model attribute to be a primary key and a foreign key that refers to model in the Product table. Run this operation and save it in the database.
Operations 2 and 3. Use INSERT INTO statements to insert the following information into the database: maker C produces a flash drive (model is 4001, type is `flash’) whose storage is 4 (GB) and price is $15.
How to submit
Prepare two files:
an accdb file that contains Hardware Database with your saved queries and operations;
a docx/doc/pdf file that contains
copies of your queries (SQL text) and copies of the resulting tables (use “copy and paste” to copy them from your accdb file to your docx/doc/pdf file);
copies of your operations (SQL text) and a copy of the resulting FlashDrive table (use “copy and paste” to copy them from your accdb file to your docx/doc/pdf file).
