SQL 練習/零件和供應商

關係模式

練習

1. 選擇所有零件的名稱。

點選檢視解決方案

 SELECT Name FROM Pieces;

2. 選擇所有供應商的資料。

點選檢視解決方案

 SELECT * FROM Providers;

3. 獲取每個零件的平均價格（僅顯示零件程式碼和平均價格）。

點選檢視解決方案

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

4. 獲取供應零件 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);

5. 選擇程式碼為“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
   );

6. 對於每個零件，找到該零件最昂貴的報價，幷包括零件名稱、供應商名稱和價格（注意，可能有兩個供應商以最昂貴的價格供應相同的零件）。

點選檢視解決方案

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

7. 向資料庫新增一個條目，以表明“Skellington Supplies”（程式碼“TNBC”）將以每個 7 美分的價格提供鏈輪（程式碼“1”）。

點選檢視解決方案

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

8. 將所有價格提高一美分。

點選檢視解決方案

 UPDATE Provides SET Price = Price + 1;

9. 更新資料庫以反映“Susan Calvin Corp.”（程式碼“RBT”）將不再供應螺栓（程式碼 4）。

點選檢視解決方案

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

10. 更新資料庫以反映“Susan Calvin Corp.”（程式碼“RBT”）將不再供應任何零件（供應商應仍保留在資料庫中）。

點選檢視解決方案

 DELETE FROM Provides
   WHERE Provider = 'RBT';

表建立程式碼

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

點選檢視 MySQL 語法。

 CREATE TABLE Pieces (
  Code INTEGER PRIMARY KEY NOT NULL,
  Name TEXT NOT NULL
  );
 CREATE TABLE Providers (
  Code VARCHAR(40) 
  PRIMARY KEY NOT NULL,  
  Name TEXT NOT NULL 
  );
 CREATE TABLE Provides (
  Piece INTEGER, 
  FOREIGN KEY (Piece) REFERENCES Pieces(Code),
  Provider VARCHAR(40), 
  FOREIGN KEY (Provider) REFERENCES Providers(Code),  
  Price INTEGER NOT NULL,
  PRIMARY KEY(Piece, Provider) 
  );

示例資料集

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