SQL Exercises/The computer store

Relational Schema

Please note the datatypes given are SQLite datatypes.

PK and FK stand for primary key and foreign key respectively.

Exercises

Select the names of all the products in the store.

Click to see solution

SELECT Name FROM Products;

Select the names and the prices of all the products in the store.

Click to see solution

SELECT Name, Price FROM Products;

Select the name of the products with a price less than or equal to $200.

Click to see solution

SELECT Name FROM Products WHERE Price <= 200;

Select all the products with a price between $60 and $120.

Click to see solution

 /* With AND */
 SELECT * FROM Products
   WHERE Price >= 60 AND Price <= 120;
 
 /* With BETWEEN */
 SELECT * FROM Products
   WHERE Price BETWEEN 60 AND 120;

Select the name and price in cents (i.e., the price must be multiplied by 100).

Click to see solution

 /* Without AS */
 SELECT Name, Price * 100 FROM Products;
 
 /* With AS */
 SELECT Name, Price * 100 AS PriceCents FROM Products;

Compute the average price of all the products.

Click to see solution

 SELECT AVG(Price) FROM Products;

Compute the average price of all products with manufacturer code equal to 2.

Click to see solution

 SELECT AVG(Price) FROM Products WHERE Manufacturer=2;

Compute the number of products with a price larger than or equal to $180.

Click to see solution

 SELECT COUNT(*) FROM Products WHERE Price >= 180;

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).

Click to see solution

   SELECT Name, Price 
     FROM Products
    WHERE Price >= 180
 ORDER BY Price DESC, Name;

Select all the data from the products, including all the data for each product's manufacturer.

Click to see solution

 /* Without INNER JOIN */
 SELECT * FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code;
 
 /* With INNER JOIN */
 SELECT *
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code;

Select the product name, price, and manufacturer name of all the products.

Click to see solution

 /* Without INNER JOIN */
 SELECT Products.Name, Price, Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code;
 
 /* With INNER JOIN */
 SELECT Products.Name, Price, Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code;

Select the average price of each manufacturer's products, showing only the manufacturer's code.

Click to see solution

  SELECT AVG(Price), Manufacturer
    FROM Products
GROUP BY Manufacturer;

Select the average price of each manufacturer's products, showing the manufacturer's name.

Click to see solution

 /* Without INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name;
 
 /* With INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name;

Select the names of manufacturer whose products have an average price larger than or equal to $150.

Click to see solution

 /* Without INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products, Manufacturers
   WHERE Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name
   HAVING AVG(Price) >= 150;
 
 /* With INNER JOIN */
 SELECT AVG(Price), Manufacturers.Name
   FROM Products INNER JOIN Manufacturers
   ON Products.Manufacturer = Manufacturers.Code
   GROUP BY Manufacturers.Name
   HAVING AVG(Price) >= 150;

Select the name and price of the cheapest product.

Click to see solution

 SELECT Name, Price
   FROM Products
   WHERE Price = (SELECT MIN(Price) FROM Products);

Select the name of each manufacturer along with the name and price of its most expensive product.

Click to see solution

 /* Without INNER JOIN */
   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
     );
 
 /* With INNER JOIN */
   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
     );

Add a new product: Loudspeakers, $70, manufacturer 2.

Click to see solution

 INSERT INTO Products( Name , Price , Manufacturer)
  VALUES ( 'Loudspeakers' , 70 , 2 );

Update the name of product 8 to "Laser Printer".

Click to see solution

 UPDATE Products
   SET Name = 'Laser Printer'
   WHERE Code = 8;

Apply a 10% discount to all products.

Click to see solution

 UPDATE Products
   SET Price = Price * 0.9;

Apply a 10% discount to all products with a price larger than or equal to $120.

Click to see solution

 UPDATE Products
   SET Price = Price * 0.9
   WHERE Price >= 120;

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)
);

Please note the syntax presented here is for the SQLite system. It has been tested by the authors on sqlite3 specifically.

Also note that the NOT NULL constraint on the primary key fields is semantically redundant, but a syntactic necessity in SQLite.

Click to see MySQL syntax.

CREATE TABLE Manufacturers (
  Code INTEGER,
  Name VARCHAR(255) NOT NULL,
  PRIMARY KEY (Code)   
);

CREATE TABLE Products (
  Code INTEGER,
  Name VARCHAR(255) NOT NULL ,
  Price DECIMAL NOT NULL ,
  Manufacturer INTEGER NOT NULL,
  PRIMARY KEY (Code), 
  FOREIGN KEY (Manufacturer) REFERENCES Manufacturers(Code)
) ENGINE=INNODB;

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);