数据库SQL查询语句实验报告

纾

实验一 简单查询

在订单数据库中完成如下的查询

(1) 查询所有业务部门的员工姓名、职称、薪水 命令：

select employeeName ,headShip ,salary from Employee

where department ='业务科' 结果：

(2) 查询名字中含有“有限”的客户姓名和所在地。 命令：

select CustomerName ,address from Customer

where CustomerName like '%有限%' 结果：

(3) 查询出姓“王”并且姓名的最后一个字为“成”的员工。 命令： select *

from Employee

where employeeName like '王%成' 结果：

(4) 查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。 命令：

纾

select employeeName ,department ,headShip ,address , case sex when 'M' then '男' when 'F' then '女' end as 性别 from Employee

where address like '%上海%' or address like '%南昌%' and sex ='F' 结果：

(5) 在表sales中挑出销售金额大于等于10000元的订单。 命令：

SELECT orderNo ,sum(quantity *price ) as total FROM OrderDetail GROUP BY orderNo

HAVING sum(quantity *price )>10000 结果：

(6) 选取订单金额最高的前10%的订单数据。 命令：

select TOP 10 PERCENT orderNo , sum(quantity *price ) as total from OrderDetail group by orderNo ORDER BY total desc 结果：

(7) 查询出职务为“职员”或职务为“科长”的女员工的信息。 命令： select *

from Employee

where headShip in ('科长','职员') and sex ='F' 结果：

纾

(8) 查找定单金额高于8000的所有客户编号。 命令：

select CustomerNo

from OrderDetail as a,OrderMaster as b where a.orderNo =b.orderNo group by CustomerNo

having sum(quantity *price )>8000 结果：

(9) 选取编号界于“C20050001”和“C20050004”的客户编号、客户名称、客户地址。 命令：

SELECT CustomerNo ,CustomerName ,address FROM Customer

WHERE CustomerNo BETWEEN 'C20050001' AND 'C20050004' 结果：

(11) 找出同一天进入公司服务的员工。 命令：

select hireDate ,employeeName from Employee as a where exists

(select * from Employee as b

where a.hireDate =b.hireDate and a.employeeNo !=b.employeeNo ) group by hireDate ,employeeName 结果：

纾

(12) 在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。 命令：

SELECT a.orderNo ,CustomerNO ,salerNo ,orderDate ,invoiceNo ,sum(quantity *price ) orderSum

FROM OrderDetail a,OrderMaster b WHERE a.orderNo =b.orderNo

GROUP BY a.orderNo ,CustomerNO ,salerNo ,orderDate ,invoiceNo HAVING sum(quantity *price ) > ALL ( SELECT sum(quantity *price ) FROM OrderDetail a,OrderMaster b WHERE a.orderNo =b.orderNo and salerNo ='E2005002' and orderDate ='2008-01-09 00:00:00.000' GROUP BY a.orderNo ) 结果：

(13) 查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。 命令：

select b.CustomerNo ,a.orderNo ,sum(quantity *price ) as total from OrderDetail as a,OrderMaster as b,Product as c, (select d.orderNo from OrderDetail as d,Product as e where ProductName ='17寸显示器'and d.ProductNo =e.ProductNo ) as f

where c.ProductName ='52倍速光驱' and a.orderNo =b.orderNo and a.orderNo =f.orderNo group by b.CustomerNo ,a.orderNo 结果：

(14) 查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。 命令：

select a.employeeName ,a.sex ,a.department ,a.headShip

from Employee as a,(select * from Employee where employeeName ='陈诗杰') as b

纾

where a.department =b.department 结果：

(15) 查询每种商品的商品编号、商品名称、订货数量和订货单价。 命令：

select b.ProductName ,a.ProductNo ,a.total,a.price from ( select sum(quantity ) as total,ProductNo ,price from OrderDetail group by ProductNo ,price ) as a , Product as b where a.ProductNo =b.ProductNo 结果：

纾

(16) 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。 命令：

select b.ProductName ,a.ProductNo ,a.total,a.price from ( select sum(quantity ) as total,ProductNo ,price from OrderDetail group by ProductNo ,price having price >400) as a , Product as b where a.ProductNo =b.ProductNo

结果：

(17) 分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。 命令：

select a.ProductNo ,ProductName ,quantity ,price

from Product a LEFT OUTER JOIN OrderDetail b ON a.ProductNo =b.ProductNo group by a.ProductNo ,quantity ,price ,ProductName having price >400

select a.ProductNo ,ProductName ,quantity ,price

from Product a RIGHT OUTER JOIN OrderDetail b ON a.ProductNo =b.ProductNo group by a.ProductNo ,quantity ,price ,ProductName having price >400

纾

select a.ProductNo ,ProductName ,quantity ,price

from Product a FULL OUTER JOIN OrderDetail b ON a.ProductNo =b.ProductNo group by a.ProductNo ,quantity ,price ,ProductName having price >400 结果：

(18) 查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。 命令：

SELECT employeeNo ,employeeName , case sex when 'F' then '女' when 'M' then '男' end as 性别, ProductName ,quantity ,price ,quantity *price orderSum,ISNULL(convert(char(10),orderDate ,120),'') 日期 FROM Employee a,OrderMaster b,OrderDetail c,Product d

WHERE employeeNo =salerNo and b.orderNo =c.orderNo and c.ProductNo =d.ProductNo 结果：

纾

(19) 查找在2008年3月中有销售记录的客户编号、名称和订单总额。 命令：

SELECT a.CustomerNo ,a.CustomerName ,orderDate ,sum(quantity *price )as 金额 FROM Customer a,OrderMaster b,OrderDetail c

WHERE a.CustomerNo =b.CustomerNo and b.orderNo =c.orderNo and year(orderDate )=2008 and month(orderDate ) …… 此处隐藏：7858字，全部文档内容请下载后查看。喜欢就下载吧 ……