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