0221 sql exercises
docx
keyboard_arrow_up
School
Indiana Wesleyan University, Indianapolis *
*We aren’t endorsed by this school
Course
697
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
docx
Pages
5
Uploaded by CaptainMuleMaster789
SQL Exercise
Write the following queries, based on the database schema:
Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)
a) Find the makers of PC's with a speed of at least 3.0.
SELECT Product.maker
FROM Product
WHERE Product.model
IN (SELECT PC.model FROM PC WHERE PC.speed >=3.0);
SELECT Product.maker
FROM Product, PC
WHERE Product.model = PC.model AND PC.speed >=3.0;
SELECT Product.maker
FROM Product
INNER JOIN PC
ON Product.model = PC.model
WHERE PC.speed >=3.0;
b) Find the printer model with the highest price.
(Correct)
SELECT Printer.model
FROM Printer
WHERE Printer.price = (SELECT MAX(price) from Printer);
(alt.) WHERE Printer.price IN (SELECT MAX(price) from Printer);
SELECT Printer.model
FROM Printer
WHERE Printer.price >= ALL (SELECT price FROM Printer);
(
INCORRECT
. CANNOT USE IT THIS WAY. )
SELECT Printer.model
FROM Printer
WHERE price = MAX (Price);
(INCORRECT, may work on some systems but not recommended. )
SELECT model, MAX(price)
FROM Printer;
c) Find the laptop models whose speed is slower than the speed of any PC.
SELECT Laptop.model
FROM Laptop
WHERE speed < (select min(speed) from PC);
SELECT Laptop.model
FROM Laptop
WHERE speed < ALL (select speed from PC);
d) 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
) AS M1
WHERE M1.price >=
ALL (SELECT price FROM PC
UNION
SELECT price FROM Laptop
UNION
SELECT price FROM Printer
) ;
e) Find the maker of the color printer with the lowest price.
SELECT product.maker
From product inner join
Printer ON Product.model = Printer.model
WHERE Printer.color = True
AND Printer.price in (SELECT MIN (Price) from Printer where Printer.color = True);
f) 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
R1.maker
FROM
Product R1, PC P1
WHERE
R1.model=P1.model AND P1.ram IN
(SELECT MIN(ram)
FROM
PC)
AND P1.speed >=
ALL (SELECT P1.speed
FROM
Product R1, PC P1
WHERE
R1.model=P1.model
AND P1.ram IN (SELECT MIN(ram) FROM PC)
);
Extra questions
Product(maker, model, type)
PC(model, speed, ram, hd, price)
Laptop(model, speed, ram, hd, screen, price)
Printer(model, color, type, price)
a) What PC models have a speed of at least 3.00?
SELECT PC.model
FROM PC
WHERE PC.speed >= 3.0;
b) Which manufacturers make laptops with a hard disk of at least 100GB?
SELECT product.maker
FROM product, laptop
WHERE product.model = laptop.model AND laptop.hd >= 100;
SELECT product.maker
FROM product INNER JOIN laptop ON product.model = laptop.model
WHERE laptop.hd >= 100;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
c) Find the model number and price of all products (of any type) made by manufacturer B.
SELECT PLP.model, PLP.price
FROM (
SELECT model, price FROM PC
UNION
SELECT model, price from Laptop
UNION
SELECT model, price from Printer
) AS PLP INNER JOIN Product ON Product.model = PLP.model
WHERE Product.maker = ‘B’;
d) Find the model numbers of all color laser printers.
SELECT printer.model
FROM printer
WHERE printer.color = ‘TRUE’ AND printer.type = ‘laser’;
e) Find those manufacturers that sell Laptops, but not PC's.
SELECT product.maker
FROM Product
Where product.type = ‘laptop’ AND
Product.maker NOT IN (SELECT product.maker FROM product WHERE product.type = ‘pc’ );
f) ! Find those hard-disk sizes that occur in two or more PC's.
SELECT PC.hd
FROM PC
GROUP BY PC.hd
HAVING count(pc.hd) >=2;
g) Find those pairs of PC models that have both the same speed and RAM. A pair should be listed only
once; e.g., list (i, j) but not (j, i).
SELECT p1.model, p2.model
FROM PC as p1, PC as p2
WHERE p1.speed = p2.speed AND p1.ram = p2.ram AND p1.model < p2.model;
h) !! Find those manufacturers of at least two different computers (PC's or laptops) with speeds of at
least 2.80.
SELECT Product.maker
FROM Product
WHERE product.model IN (SELECT PC.model FROM PC WHERE PC.speed >=2.8)
OR
Product.model IN (SELECT laptop.model FROM Laptop WHERE laptop.speed >= 2.8)
GROUP by Product.maker
HAVING COUNT(Product.model) >= 2;
i) !! Find the manufacturer(s) of the computer (PC or laptop) with the highest available speed.
Select product.maker
FROM product, (select model, speed from pc UNION select model, speed from laptop) as MS
Where product.model = ms.model
AND MS.speed in (select max(ms.speed) from (select model, speed from pc UNION select model, speed
from laptop) as MS);
j) !! Find the manufacturers of PC's with at least three different speeds.
Select product.maker
From product, pc
Where product.model = pc.model
Group by product. maker
Having count (distinct speed) >= 3.0;