Question

Consider the following database schema:

               Product(maker, model, type)

               PC(model, speed, ram, hd, rd, price)

               Laptop(model, speed, ram, hd, screen, price)

               Printer(model, color, type, price)

Give SQL statement for each of the following:

2. (Subqueries, Join operations) Write the following queries in SQL:

1. Find the makers of PC’s with a speed of at least 1200.
2. Find the printers with the highest price.
3. Find the laptops whose speed is lower than that of any PC.
4. Find the model number of the item (PC, laptop, or printer) with the highest price.
5. Find the maker of the color printer with the lowest price.
6. Find the maker(s) of the PC(s) with the fastest processor among all those PC’s that have the smallest amount of RAM.

Answer 1

Consider the following database schema:

Product(maker, model, type)

PC(model, speed, ram, hd, rd, price)

Laptop(model, speed, ram, hd, screen, price)

Printer(model, color, type, price)

1. Find the makers of PC’s with a speed of at least 1200.

=> Select maker from Product

NATURAL JOIN PC

Where PC.speed >= 1200 And Count (distinct model)>=2

2.Find the printers with the highest price.

=> Select model from Printer

Where Price >= ALL (Select price from Printer)

3.Find the laptops whose speed is lower than that of any PC.

=> Select model from Laptop

Where speed <= ALL (Select speed from PC)

4.Find the model number of the item (PC, laptop, or printer) with the highest price.

=> Select model from

(Select model, price from PC)

UNION

(Select model, price from Laptop)

UNION

(Select model, price from Printer) model_price

Where price >= ALL (Select price from model_price)

5.Find the maker of the color printer with the lowest price.

=> Select distinct maker from Product, Printer

Where color ='true' And Printer.model = Product.model

And price <= ALL (Select price from Printer

Where color ='true')

6.Find the maker(s) of the PC(s) with the fastest processor among all those PC’s that have the smallest amount of RAM.

=> Select distinct maker from Product, PC

Where Product.model = PC.model

And RAM <= ALL( Select ram from PC)

And Speed >= ALL( Select speed from PC

Where ram = ( select min (ram) from PC))