数据库原理实验测试1

目录

R10-1 查询仓库号为'A01'的所有员工信息，并按照工资降序排列  

   R10-2 查找所有的仓库信息

 R10-3 查询姓名为’刘勇’的员工信息及所销售的订单编号，单价和数量信息

    R10-4 查询每个员工编号和姓名及其工作仓库的信息

   R10-5 查询’A02’仓库中薪水最高的员工编号与姓名       

    R10-6 建立产品的销售记录视图vProductStatics

        R10-7 删除没有确定工作仓库的员工信息

         R10-8 将’长城机电’的所在城市修改为’绍兴’

R10-9 增加一个供货商，编号为’S05’，名称为’长城机电’，城市为’杭州’  

 R10-10 查询订单量最多的产品编号

R10-1 查询仓库号为'A01'的所有员工信息，并按照工资降序排列  

SELECT *
FROM employee
WHERE Wno = 'A01'
ORDER BY Salary DESC;

   R10-2 查找所有的仓库信息

select Wno,City,Size
from warehouse

 R10-3 查询姓名为’刘勇’的员工信息及所销售的订单编号，单价和数量信息

SELECT e.Eid ,
       e.EName,
       e.Wno,
       e.Salary,
       o.OrdNo,
       o.Price,
       o.QTY
FROM employee e
JOIN orders o ON e.Eid = o.Eid
WHERE e.EName = '刘勇';

    R10-4 查询每个员工编号和姓名及其工作仓库的信息

SELECT e.Eid,
       e.EName,
       w.Wno,
       w.City,
       w.Size
FROM employee e
JOIN warehouse w ON e.Wno = w.Wno;

   R10-5 查询’A02’仓库中薪水最高的员工编号与姓名       

SELECT e.Eid ,
       e.EName
FROM employee e
WHERE e.Wno = 'A02'
AND e.Salary = (SELECT MAX(Salary) FROM employee WHERE Wno = 'A02');

    R10-6 建立产品的销售记录视图vProductStatics

CREATE VIEW vProductStatics AS
SELECT
    Pid ,
    SUM(QTY) AS totalQTY,
    MAX(Price) AS maxPrice,
    MIN(Price) AS minPrice
FROM orders
GROUP BY Pid;

        R10-7 删除没有确定工作仓库的员工信息

DELETE FROM employee
WHERE Wno IS NULL OR Wno = '';

         R10-8 将’长城机电’的所在城市修改为’绍兴’

UPDATE supplier
SET City = '绍兴'
WHERE SName = '长城机电';

R10-9 增加一个供货商，编号为’S05’，名称为’长城机电’，城市为’杭州’  

INSERT INTO supplier (Sid, SName, City)
VALUES ('S05', '长城机电', '杭州');

 R10-10 查询订单量最多的产品编号

SELECT Pid
FROM orders
GROUP BY Pid
HAVING COUNT(*) = (SELECT MAX(ordcount) FROM (SELECT COUNT(*) AS ordcount From orders GROUP BY Pid ) AS t)