SQL

Modified from Exercise 2.4.1 from the textbook, Database Systems: Complete Book. The products database consists of 4 relational instances with the following schemas

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

The Product relation gives the manufacturer, model number and type ( PC, laptop, or printer ) of various products. We assume for convenience that model numbers are unique over all manufacturers and product types; that assumption is not realistic, and a real database would include a code for the manufacturer as part of the model number.

The PC relation gives for each model number that is a PC the speed ( of the processor, in gigahertz ), the amount of RAM ( in megabytes ), the size of the hard disk ( in gigabytes ), and the price.

The Laptop relation is similar, except that the screen size ( in inches ) is also included.

The Printer relation records for each printer model whether the printer produces color output ( true, if so ), the process type ( laser or ink - jet, typically ), and the price.

Sample data for the tables are given below.

Task 1 : DDLs

Write the following declarations and make sure they execute on sqlite3:

a. A suitable schema for relation Product.

b. A suitable schema for relation PC.

c. A suitable schema for relation Laptop.

d. A suitable schema for relation Printer.

Task 2 : Inserting, loading, and updating data

a. Write insert statements to insert one new PC product into the Product and PC tables. You may give arbitrary values to the field, but make sure the model number is 999..

b. Use the sqlite3 .import command to load csv files into all the tables. product.csv | pc.csv | laptop.csv | printer.csv

c. Write an update statement to increase the price of printer model 3007 to $220

d. Write a delete statement to delete the PC product that you inserted earlier.

Task 3 : Writing SQL queries

Write SQL queries for the following questions. You can do this either using the sqlite3 commandline shell, a sqlite3 GUI, or via Python using this SQLinPython.ipynb file.

a. What PC models have a speed of at least 3.00?

b. Which manufacturers make laptops with a hard disk of at least 100GB?

c. Find the model number and price of all products (of any type) made by manufacturer B.

d. Find the model numbers of all color laser printers.

e. Find those manufacturers that sell Laptops, but not PC’s.

f. Find those hard disk sizes that occur in two or more PC’s.

g. Find the manufacturers of PC’s with at least three different speeds.

h. Find the manufacturers who sell exactly three different models of PC

i. Find the average speed of laptops costing over $1000.

j. Find the average price of PC’s made by manufacturer “A”.

k. Find, for each different speed, the average price of a PC.

l. Find, for each manufacturer, the average screen size of its laptops.

m. Find the manufaturers that make at least three different models of PC.

Sample data (pics)

Product

PC

laptop

printer