Solutions Assignment 1
docx
keyboard_arrow_up
School
Western Michigan University *
*We aren’t endorsed by this school
Course
4430
Subject
Industrial Engineering
Date
Apr 3, 2024
Type
docx
Pages
3
Uploaded by CaptainSeaLion4027
Product (maker, model
, type)
PC (model
, speed, ram, hdisk, price)
Laptop (model
, speed, ram, hdisk, screen, price)
Printer (model
, color, type, price)
1.
Find the model number, speed, and hard-disk size for all PC ’s whose price is under $1000.
Select distinct model, speed, hdisk
From PC
Where price <1000
Order by 1;
2.
List the manufacturers of all printers.
Select distinct maker From Product
Where type = ‘printers’
Order by 1;
3.
Find the model number, memory size, and screen size for laptops costing more than $1500.
Select model, ram, screen
From laptop
Where price>1500
Order by 1;
4.
List all color printers.
Select distinct maker from printer
Where color = ‘1’
Order by 1;
5. Find the model number and hard-disk size for those PC ’s that have a speed of 3.2 and a price less than $2000.
Select distinct model, hdisk
From PC
Where speed = ‘3.2’
And price < 2000
Order by 1;
5.
Give the manufacturer and speed of laptops with a hard disk of at least 30.
Select p.maker, l.speed From product p
Join laptop l on l.model = p.model
Where hdisk>=30;
6.
Find the model number and price of all products (of any type) made by manufacturer B.
Select pr.model
From product pr
Join(
Select model, price from pc where pr.maker = ‘B’
Union all
Select model, price from laptop where pr.maker = ‘B’
Union all
Select model, price from printer where pr.maker = ‘B’
);
7.
Find those manufacturers that sell Laptops, but not PC ’s.
SELECT DISTINCT maker
FROM Product
WHERE type = 'Laptop' AND maker NOT IN (
SELECT maker FROM Product WHERE type = 'PC'
);
8.
Find those hard-disk sizes that occur in two or more PC ’s.
Select distinct hdisk from pc
Group by hdisk
having count(*)>=2;
10. 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 distinct p1.model,p2.model from pc p1, pc p2
where p1.model>p2.model
and p1.speed=p2.speed
and p1.ram = p2.ram;
11. Find those manufacturers of at least two different computers (PC’s or laptops) with speeds of at least 3.0.
12. Find the makers of PC ’s with a speed of at least 3.0.
13. Find the printers with the highest price.
14. Find the laptops whose speed is slower than that of any PC.
15. Find the model number of the item (PC, laptop, or printer) with the highest price.
16. Find the maker of the least expensive color printer.
17. Find the maker(s) of the PC(s) with the fastest processor among all those PC’s that have the smallest amount of RAM.
18. Find the average speed of PC ’s.
19. Find the average speed of laptops costing over $1000.
20. Find the average price of PC’s made by manufacturer “A.”
21. Find the average price of PC’s and laptops made by manufacturer “D.”
22. Find, for each different speed, the average price of a PC.
23. Find for each manufacturer, the average screen size of its laptops.
24. Find the manufacturers that make at least three different models of PC.
25. Find for each manufacturer who sells PC’s the maximum price of a PC.
26. Find, for each speed of PC above 2.0, the average price.
27. For each manufacture that makes printers, find the average hard disk size of PCs it makes
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