Question

Hw problems help thanks

The following questions are based on the tables

Product (maker, model, type)

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

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

Printer(model, color, type, price)

14. Find those manufacturers that sell laptops but not pc’s.

15. Find those hard-drive sizes that occur in two or more PC’s. [hint; use group and having]

16. Find those pairs of PC models that have both the same speed and RAM. A pair should be listed only once: e.g. list (f, g) but not (g,f)

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

Answer 1

SOLUTION 14:

select maker from Product p,Laptop L
where p.model==L.model and maker not in (select maker from Product p1,PC where p1.model==PC.model )

EXPLANATION:

STEP 1: We need to find the laptop makers first

STEP 2: We need to eliminate the makers of PC from it

STEP 3: Project those makers

SOLUTION 15:

SELECT hd FROM PC
GROUP BY hd
HAVING count(hd)>=2

EXPLANATION:

STEP 1: Group the hard disks which have the same size by using group by

STEP 2: Check their Count by using having clause and project them

SOLUTION 16:

SELECT p1.model AS MODEL1, p2.model MODEL2
FROM PC p1 JOIN PC p2 ON p1.model!=p2.model AND p1.speed==p2.speed AND p1.ram==p2.ram
WHERE p1.model>p2.model

EXPLANATION:

STEP 1: We need to do a self join based on the condition of the equal speed and ram

STEP 2: We need to eliminate matching of same rows in both tables by matching with the model (p1.model!=p2.model)

STEP 3: To eliminate the duplicate rows of matching(i,j) and (j,i) we do it by (p1.model>p2.model )

STEP 4: Project the fields

SOLUTION 17:

SELECT DISTINCT maker
FROM Product p, Printer pr
WHERE color=="Y" AND price==(SELECT MIN(price) FROM Printer) AND p.model=pr.model

EXPLANATION :

STEP 1: We need to find the color printer by checking the color attribute whether it is Y or N

STEP 2: Select the min Price of the Printer

STEP 3: Map That with the products model

STEP 4: We need to find the distinct of those Makers to eliminate duplicate makers