The
computer store
Relational Schema
Table creation code
CREATE
TABLE Manufacturers (
Code INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL
CREATE
TABLE Products (
Code INTEGER PRIMARY KEY NOT NULL,
Name TEXT NOT NULL ,
Price REAL NOT NULL ,
Manufacturer INTEGER NOT NULL
CONSTRAINT
fk_Manufacturers_Code REFERENCES MANUFACTURERS(Code)
Sample
Dataset
INSERT INTO Manufacturers(Code,Name) VALUES(1,'Sony');
INSERT INTO Manufacturers(Code,Name) VALUES(2,'Creative Labs');
INSERT INTO Manufacturers(Code,Name) VALUES(3,'Hewlett-Packard');
INSERT INTO Manufacturers(Code,Name) VALUES(4,'Iomega');
INSERT INTO Manufacturers(Code,Name) VALUES(5,'Fujitsu');
INSERT INTO Manufacturers(Code,Name) VALUES(6,'Winchester');
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(1,'Hard drive',240,5);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(2,'Memory',120,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(3,'ZIP drive',150,4);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(4,'Floppy disk',5,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(5,'Monitor',240,1);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(6,'DVD drive',180,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(7,'CD drive',90,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(8,'Printer',270,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(9,'Toner cartridge',66,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(10,'DVD burner',180,2);
Exercises
1. Select the names of all the products in the
store.
Solution
SELECT Name FROM Products;
2. Select the names and the prices of all the products in the store.
Solution
SELECT Name, Price FROM Products;
3. Select the name of the products with a price less than or equal to $200.
Solution
SELECT Name FROM Products WHERE Price <= 200;
4. Select all the products with a price between $60 and $120.
Solution
SELECT * FROM Products
WHERE Price >= 60 AND Price <= 120;
SELECT * FROM Products
WHERE Price BETWEEN 60 AND 120;
5. Select the name and price in cents (i.e., the price must be multiplied by 100).
Solution
SELECT Name, Price * 100 FROM Products;
SELECT Name, Price * 100 AS PriceCents FROM Products;
6. Compute the average price of all the products.
Solution
SELECT AVG(Price) FROM Products;
7. Compute the average price of all products with manufacturer code equal to 2.
Solution
SELECT AVG(Price) FROM Products WHERE Manufacturer=2;
8. Compute the number of products with a price larger than or equal to $180.
Solution
SELECT COUNT(*) FROM Products WHERE Price >= 180;
9. Select the name and price of all products with a price larger than or equal to
$180, and sort first by price (in descending order), and then by name (in
ascending order).
Solution
SELECT Name, Price
FROM Products
WHERE Price >= 180
ORDER BY Price DESC, Name;
10. Select all the data from the products, including all the data for each
product's manufacturer.
Solution
SELECT * FROM Products, Manufacturers
WHERE Products.Manufacturer = Manufacturers.Code;
SELECT *
FROM Products INNER JOIN Manufacturers
ON Products.Manufacturer = Manufacturers.Code;
11. Select the product name, price, and manufacturer name of all the products.
Solution
SELECT Products.Name, Price, Manufacturers.Name
FROM Products, Manufacturers
WHERE Products.Manufacturer = Manufacturers.Code;
SELECT Products.Name, Price, Manufacturers.Name
FROM Products INNER JOIN Manufacturers
ON Products.Manufacturer = Manufacturers.Code;
12. Select the average price of each manufacturer's
products, showing only the manufacturer's code.
Solution
SELECT AVG(Price), Manufacturer
FROM Products
GROUP BY Manufacturer;
13. Select the average price of each manufacturer's products, showing the
manufacturer's name.
Solution
SELECT AVG(Price), Manufacturers.Name
FROM Products, Manufacturers
WHERE Products.Manufacturer = Manufacturers.Code
GROUP BY Manufacturers.Name;
SELECT AVG(Price), Manufacturers.Name
FROM Products INNER JOIN Manufacturers
ON Products.Manufacturer = Manufacturers.Code
GROUP BY Manufacturers.Name;
14. Select the names of manufacturer whose products have an average price larger
than or equal to $150.
Solution
SELECT AVG(Price), Manufacturers.Name
FROM Products, Manufacturers
WHERE Products.Manufacturer = Manufacturers.Code
GROUP BY Manufacturers.Name
HAVING AVG(Price) >= 150;
SELECT AVG(Price), Manufacturers.Name
FROM Products INNER JOIN Manufacturers
ON Products.Manufacturer = Manufacturers.Code
GROUP BY Manufacturers.Name
HAVING AVG(Price) >= 150;
15. Select the name and price of the cheapest product.
Solution
SELECT Name, Price
FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products);
16. Select the name of each manufacturer along with the name and price of its most
expensive product.
Solution
SELECT A.Name, A.Price, F.Name
FROM Products A, Manufacturers F
WHERE A.Manufacturer = F.Code
AND A.Price =
(
SELECT MAX(A.Price)
FROM Products A
WHERE A.Manufacturer = F.Code
);
SELECT A.Name, A.Price, F.Name
FROM Products A INNER JOIN Manufacturers F
ON A.Manufacturer = F.Code
AND A.Price =
(
SELECT MAX(A.Price)
FROM Products A
WHERE A.Manufacturer = F.Code
);
17. Add a new product: Loudspeakers, $70, manufacturer 2.
Solution
INSERT INTO Products( Name , Price , Manufacturer)
VALUES ( 'Loudspeakers' , 70 , 2 );
18. Update the name of product 8 to "Laser Printer".
Solution
UPDATE Products
SET Name = 'Laser Printer'
WHERE Code = 8;
19. Apply a 10% discount to all products.
Solution
UPDATE Products
SET Price = Price * 0.9;
20. Apply a 10% discount to all products with a price larger than or equal to $120.
Solution
UPDATE Products
SET Price = Price * 0.9
WHERE Price >= 120;
