订单数据表SQL语句练习

1、建立相关的表

# 职工表
CREATE TABLE `employee` (
  `houseId` VARCHAR (36),
  `empId` VARCHAR (36),
  `salary` DOUBLE
) ;

# 供应商表
CREATE TABLE `supplier` (
  `supplierId` VARCHAR (36),
  `supplierName` VARCHAR (36),
  `address` VARCHAR (36)
) ;

# 订单表
CREATE TABLE `order` (
  `orderId` VARCHAR (36),
  `empId` VARCHAR (36),
  `supplierId` VARCHAR (36),
  `time` DATETIME
) ;

# 仓库表
CREATE TABLE `house` (
  `houseId` VARCHAR (36),
  `city` VARCHAR (36),
  `area` DOUBLE
) ;

2、插入数据

INSERT INTO employee 
VALUES
  ('WH2', 'E1', 1220),
  ('WH1', 'E3', 1210),
  ('WH2', 'E4', 1250),
  ('WH3', 'E6', 1230),
  ('WH1', 'E7', 1250) ;

INSERT INTO supplier 
VALUES
  ('S3', '振华电子厂', '西安'),
  ('S4', '华通电子公司', '北京'),
  ('S6', '607厂', '郑州'),
  ('S7', '爱华电子厂', '北京') ;

INSERT INTO `order` 
VALUES
  ('OR67', 'E3', 'S7', '2002/06/23'),
  ('OR73', 'E1', 'S4', '2002/07/28'),
  ('OR76', 'E7', 'S4', '2002/05/25'),
  ('OR77', 'E6', NULL, NULL),
  ('OR79', 'E3', 'S4', '2002/06/13'),
  ('OR80', 'E1', NULL, NULL),
  ('OR90', 'E3', NULL, NULL),
  ('OR91', 'E3', 'S3', '2002/07/13') ;

INSERT INTO `house` 
VALUES
  ('WH1', '北京', 370),
  ('WH2', '上海', 500),
  ('WH3', '广州', 200),
  ('WH4', '武汉', 400) ;

3、相关的练习题

• 检索在北京的供应商的名称；

SELECT supplierName FROM supplier WHERE address = '北京';

• 检索发给供应商S6的订购单号；

SELECT orderId FROM `order` WHERE supplierId = 'S6';

• 检索出职工E6发给供应商S6的订购单信息；

SELECT o.* FROM `order` o WHERE empId = 'E6' AND supplierId = 'S6'; 

• 检索出向供应商S3发过订购单的职工的职工号和仓库号；

SELECT 
  o.`empId`,
  houseId 
FROM
  `order` o,
  employee e 
WHERE e.`empId` = o.`empId` 
  AND o.`supplierId` = 'S3' ;

• 检索出目前与S3供应商没有联系的职工信息；

SELECT 
  e.* 
FROM
  employee e 
WHERE e.`empId` NOT IN 
  (SELECT 
    o.`empId` 
  FROM
    `order` o 
  WHERE o.`supplierId` = 'S3') ;

• 检索出目前没有任何订购单的供应商信息；

SELECT 
  s.* 
FROM
  supplier s 
WHERE s.`supplierId` NOT IN 
  (SELECT DISTINCT 
    (supplierId) 
  FROM
    `order` 
  WHERE supplierId IS NOT NULL) ;

• 检索出和职工E1、E3都有联系的北京的供应商信息；

SELECT 
  * 
FROM
  supplier 
WHERE address = '北京' 
  AND supplierId IN 
  (SELECT 
    supplierId 
  FROM
    `order` 
  WHERE empId = 'E1') 
  AND supplierId IN 
  (SELECT 
    supplierId 
  FROM
    `order` 
  WHERE empId = 'E3') ;

• 检索出目前和华通电子公司有业务联系的每个职工的工资；

SELECT 
  salary 
FROM
  employee e,
  supplier s,
  `order` o 
WHERE e.`empId` = o.`empId` 
  AND o.`supplierId` = s.`supplierId` 
  AND s.`supplierName` = '华通电子公司' ;

• 检索出与工资在1220元以下的职工没有联系的供应商的名称；

SELECT 
  s.`supplierName` 
FROM
  supplier s 
WHERE s.`supplierId` NOT IN 
  (SELECT 
    o.`supplierId` 
  FROM
    employee e,
    `order` o 
  WHERE e.`empId` = o.`empId` 
    AND salary < 1220 
    AND o.`supplierId` IS NOT NULL) ;

• 检索出向S4供应商发出订购单的仓库所在的城市；

SELECT DISTINCT 
  (city) 
FROM
  house h,
  employee e,
  `order` o 
WHERE h.`houseId` = e.`houseId` 
  AND e.`empId` = o.`empId` 
  AND o.`supplierId` = 'S4' ;

• 检索出在上海工作并且向S6供应商发出了订购单的职工号；

SELECT 
  e.`empId` 
FROM
  house h,
  employee e,
  `order` o 
WHERE h.`houseId` = e.`houseId` 
  AND e.`empId` = o.`empId` 
  AND o.`supplierId` = 'S6' 
  AND h.`city` = '上海' ;

• 检索出在广州工作并且只向S4供应商发出了订购单的职工号；

SELECT 
  empId 
FROM
  employee 
WHERE houseId IN 
  (SELECT 
    houseId 
  FROM
    house 
  WHERE city = '广州') 
  AND empId IN 
  (SELECT 
    empId 
  FROM
    `order` 
  WHERE supplierId = 'S6') 
  AND empId NOT IN 
  (SELECT 
    empId 
  FROM
    `order` 
  WHERE supplierId != 'S6') ;

• 检索出由工资多于1230元的职工向北京的供应商发出的订购单号；

SELECT 
  orderId 
FROM
  `order` o,
  supplier s 
WHERE o.`empId` IN 
  (SELECT 
    empId 
  FROM
    employee e 
  WHERE e.salary > 1230) 
  AND o.supplierId = s.supplierId 
  AND address = '北京' ;

• 检索出仓库的个数；

SELECT COUNT(houseId) FROM house;

• 检索出有最大面积的仓库信息；

SELECT  * FROM house WHERE `area` = (SELECT MAX(`area`) FROM house) ;

• 检索出所有仓库的平均面积；

SELECT AVG(`area`) '平均面积' FROM house;

• 检索出向S4供应商发出订购单的那些仓库的平均面积；

SELECT 
  AVG(DISTINCT (`area`)) 
FROM
  house h,
  employee e,
  `order` o 
WHERE h.`houseId` = e.`houseId` 
  AND e.`empId` = o.`empId` 
  AND o.`supplierId` = 'S4' ;

• 检索出每个城市的供应商个数；

SELECT 
  address,
  COUNT(*) '供应商个数' 
FROM
  supplier 
GROUP BY address ;

• 检索出每个仓库中工资多于1220元的职工个数；

SELECT 
  houseId,
  COUNT(*) 
FROM
  employee 
WHERE salary > 1220 
GROUP BY houseId ;

• 检索出和面积最小的仓库有联系的供应商的个数；

SELECT 
  COUNT(*) 
FROM
  employee e,
  `order` o,
  supplier s 
WHERE e.`empId` = o.`empId` 
  AND s.`supplierId` = o.`supplierId` 
  AND houseId = 
  (SELECT 
    MIN(salary) 
  FROM
    house) ;

• 检索出工资低于本仓库平均工资的职工信息；

SELECT 
  e1.*,
  e2.sal '该仓库的平均工资'
FROM
  employee e1,
  (SELECT 
    houseId,
    AVG(salary) sal 
  FROM
    employee 
  GROUP BY houseId) e2 
WHERE e1.`salary` < e2.`sal` 
  AND e1.houseId = e2.houseId ;

• 插入一个新的供应商元组（S9，智通公司，沈阳）；

INSERT INTO supplier VALUE('S9','智通公司','沈阳');

• 删除目前没有任何订购单的供应商；

DELETE 
FROM
  supplier 
WHERE supplierId NOT IN 
  (SELECT 
    supplierId 
  FROM
    `order` 
  WHERE supplierId IS NOT NULL) ;

• 删除由在上海仓库工作的职工发出的所有订购单；

DELETE 
FROM
  `order` o 
WHERE o.empId IN 
  (SELECT DISTINCT 
    (e.`empId`) 
  FROM
    house h,
    employee e 
  WHERE h.houseId = e.houseId 
    AND city = '上海') ;

• 北京的所有仓库增加100m2的面积；

UPDATE house SET `area` = `area` + 100;

• 给低于所有职工平均工资的职工提高5%的工资；

UPDATE 
  employee 
SET
  salary = salary * 1.05 
WHERE salary < 
  (SELECT 
    minSal 
  FROM
    (SELECT 
      AVG(salary) minSal
    FROM
      employee) t) ;