SQL Exercises/Pieces and providers

From Wikibooks, the open-content textbooks collection

[edit] Relational Schema

[edit] Exercises

Select the name of all the pieces.

SELECT Name FROM Pieces;

Select all the providers' data.

SELECT * FROM Providers;

Obtain the average price of each piece (show only the piece code and the average price).

SELECT Piece, AVG(Price)
  FROM Provides
  GROUP BY Piece;

Obtain the names of all providers who supply piece 1.

/* Without subquery */
SELECT Providers.Name
  FROM Providers INNER JOIN Provides
  ON Providers.Code = Provides.Provider
    AND Provides.Piece = 1;

/* With subquery */
SELECT Name
  FROM Providers
  WHERE Code IN
  (SELECT Provider FROM Provides WHERE Piece = 1);

Select the name of pieces provided by provider with code "HAL".

/* Without subquery */
SELECT Pieces.Name
  FROM Pieces INNER JOIN Provides
  ON Pieces.Code = Provides.Piece
    AND Provides.Provider = 'HAL';

/* With IN subquery */
SELECT Name
  FROM Pieces
  WHERE Code IN
    (SELECT Piece FROM Provides WHERE Provider = 'HAL');

/* With EXISTS subquery */
SELECT Name
  FROM Pieces
  WHERE EXISTS
  (
    SELECT * FROM Provides
      WHERE Provider = 'HAL'
        AND Piece = Pieces.Code
  );

For each piece, find the most expensive offering of that piece and include the piece name, provider name, and price (note that there could be two providers who supply the same piece at the most expensive price).

SELECT Pieces.Name, Providers.Name, Price
  FROM Pieces INNER JOIN Provides ON Pieces.Code = Piece
              INNER JOIN Providers ON Providers.Code = Provider
  WHERE Price =
  (
    SELECT MAX(Price) FROM Provides
    WHERE Piece = Pieces.Code
  );

Add an entry to the database to indicate that "Skellington Supplies" (code "TNBC") will provide sprockets (code "1") for 7 cents each.

INSERT INTO Provides
  VALUES ( 'TNBC' , 1 , 7 );

Increase all prices by one cent.

UPDATE Provides SET Price = Price + 1;

Update the database to reflect that "Susan Calvin Corp." (code "RBT") will not supply any pieces (the provider should still remain in the database).

DELETE FROM Provides
  WHERE Provider = 'RBT';

Update the database to reflect that "Susan Calvin Corp." (code "RBT") will not supply bolts (code 4).

DELETE FROM Provides
  WHERE Provider = 'RBT'
    AND Piece = 4;

[edit] Table creation code

CREATE TABLE Pieces (
  Code INTEGER PRIMARY KEY NOT NULL,
  Name TEXT NOT NULL
);

CREATE TABLE Providers (
 Code TEXT PRIMARY KEY NOT NULL,
 Name TEXT NOT NULL
);

CREATE TABLE Provides (
  Piece INTEGER  
    CONSTRAINT fk_Pieces_Code REFERENCES Pieces(Code),
  Provider TEXT  
    CONSTRAINT fk_Providers_Code REFERENCES Providers(Code),
  Price INTEGER NOT NULL,
  PRIMARY KEY(Piece, Provider)
);

[edit] Sample dataset

INSERT INTO Providers(Code, Name) VALUES('HAL','Clarke Enterprises');
INSERT INTO Providers(Code, Name) VALUES('RBT','Susan Calvin Corp.');
INSERT INTO Providers(Code, Name) VALUES('TNBC','Skellington Supplies');

INSERT INTO Pieces(Code, Name) VALUES(1,'Sprocket');
INSERT INTO Pieces(Code, Name) VALUES(2,'Screw');
INSERT INTO Pieces(Code, Name) VALUES(3,'Nut');
INSERT INTO Pieces(Code, Name) VALUES(4,'Bolt');

INSERT INTO Provides(Piece, Provider, Price) VALUES(1,'HAL',10);
INSERT INTO Provides(Piece, Provider, Price) VALUES(1,'RBT',15);
INSERT INTO Provides(Piece, Provider, Price) VALUES(2,'HAL',20);
INSERT INTO Provides(Piece, Provider, Price) VALUES(2,'RBT',15);
INSERT INTO Provides(Piece, Provider, Price) VALUES(2,'TNBC',14);
INSERT INTO Provides(Piece, Provider, Price) VALUES(3,'RBT',50);
INSERT INTO Provides(Piece, Provider, Price) VALUES(3,'TNBC',45);
INSERT INTO Provides(Piece, Provider, Price) VALUES(4,'HAL',5);
INSERT INTO Provides(Piece, Provider, Price) VALUES(4,'RBT',7);