数据库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 )=3

GROUP BY a.CustomerNo ,a.CustomerName ,orderDate

结果：

(20) 使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。

命令：

SELECT a.CustomerNo ,a.CustomerName ,ISNULL(convert(char(10),orderDate ,120),'') Date

FROM Customer a LEFT JOIN OrderMaster b ON a.CustomerNo =b.CustomerNo

ORDER BY a.CustomerNo ,orderSum

结果：

(21) 查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。

命令：

select employeeName ,

case sex when 'F' then '女'

when 'M' then '男'

end as 性别, orderDate ,quantity ,sum(quantity *price )as 金额

from Employee a,OrderMaster b,OrderDetail c

where employeeNo =salerNo and b.orderNo =c.orderNo and ProductNo in

(select ProductNo from Product where ProductName ='16M DRAM')

group by employeeName ,sex , orderDate ,quantity

结果：

(22) 查找每个人的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。

命令：

select employeeNo ,employeeName ,sex ,ProductName ,quantity ,price ,sum(quantity *price )

as ordersum, orderDate

from Employee a,OrderMaster b,OrderDetail c,Product d

where employeeNo =salerNo and b.orderNo =c.orderNo and c.ProductNo =d.ProductNo

group by employeeNo ,employeeName ,sex ,ProductName ,quantity ,price , orderDate

结果：

(23) 查询客户姓名为“客户丙”所购货物的“客户名称”、“定单金额”、“定货日期”和“电话号码”。

命令：

SELECT a.CustomerName ,quantity *price 订单金额,orderDate ,telephone

FROM Customer a,OrderMaster b,OrderDetail c

WHERE CustomerName ='客户丙' and a.CustomerNo =b.CustomerNo

and b.orderNo =c.orderNo

结果：

(24) 找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。

命令：

select b.orderNo ,sum(quantity *price ) ordersum

from Employee a,OrderMaster b,OrderDetail c

where sex ='M' and a.employeeNo =b.salerNo and b.orderNo =c.orderNo

group by b.orderNo

having sum(quantity *price )>2000

结果：

(25) 查询来自上海市的客户的姓名、电话、订单号及订单金额。

命令：

select CustomerName ,telephone ,b.orderNo ,sum(quantity *price ) as ordersum

from Customer a,OrderMaster b,OrderDetail c

where address ='上海市' and a.CustomerNo =b.CustomerNo and b.orderNo =c.orderNo

group by CustomerName ,telephone ,b.orderNo

结果：

实验二题目

首先使用命令：

UPDATE OrderMaster SET orderSum =total

FROM OrderMaster a,

(SELECT orderNo ,sum(quantity *price ) as total

FROM OrderDetail

GROUP BY orderNo ) b

WHERE a.orderNo =b.orderNo

更新表OrderMaster 中 orderSum 的值。

（1）查找有销售记录的客户编号、名称和订单总额。

命令：

SELECT a.CustomerNo ,CustomerName ,sum(orderSum ) Sum

FROM Customer a,OrderMaster b

WHERE a.CustomerNo =b.CustomerNo and orderSum !=0

GROUP BY a.CustomerNo ,CustomerName

结果：

(2) 在订单明细表中查询订单金额最高的订单。

命令：

SELECT *

FROM OrderMaster

WHERE orderSum =(SELECT max(orderSum ) FROM OrderMaster )

结果：

(3) 查询没有订购商品的客户编号和客户名称。

命令：

SELECT a.CustomerNo ,CustomerName ,

FROM Customer a

WHERE NOT EXISTS (

SELECT *

FROM OrderMaster b,OrderDetail c

WHERE a.CustomerNo =b.CustomerNo and b.orderNo =c.orderNo )

GROUP BY a.CustomerNo ,CustomerName

结果：

(4) 找出至少被订购3次的商品编号、订单编号、订货数量和订货金额，并按订货数量的降序排序输出。

命令：

SELECT productNo ,orderNo ,quantity ,quantity *price 订货金额

FROM OrderDetail

WHERE productNO IN(

SELECT productNo

FROM OrderDetail

GROUP BY productNo

HAVING count(*)>=3)

ORDER BY productNo desc

结果：

(5) 使用子查询查找16M DRAM的销售情况，要求显示相应的销售员的姓名、性别，销售日期、销售数量和金额，其中性别用“男”、“女”表示。

命令：

SELECT employeeName ,

CASE sex WHEN'F' THEN '女'

WHEN 'M' THEN '男'

END AS 性别, orderDate ,quantity ,sum(quantity *price )as 金额

FROM Employee a,OrderMaster b,OrderDetail c

WHERE employeeNo =salerNo and b.orderNo =c.orderNo and ProductNo in

(SELECT ProductNo FROM Product WHERE ProductName ='16M DRAM')

结果：

(6) 查询sales表中订单金额最高的订单号及订单金额。

命令：

SELECT orderNo ,orderSum

FROM OrderMaster

WHERE orderSum =(SELECT max(orderSum ) FROM OrderMaster )

结果：

(7) 计算出一共销售了几种商品。

命令：

SELECT count(distinct productNo ) 商品种类

FROM OrderDetail

结果：

(8) 显示OrderDetail表中每种商品的订购金额总和，并且依据销售金额由大到小排序输出。

命令：

SELECT productNo ,sum(quantity *price ) 订购金额

FROM OrderDetail

GROUP BY productNo

ORDER BY 订购金额 DESC

结果：

(9) 查找销售总额少于1000元的销售员编号、姓名和销售额。

命令：

SELECT employeeNo ,employeeName ,sum(orderSum ) 销售额

FROM OrderMaster a RIGHT OUTER JOIN Employee b ON salerNo =employeeNo

WHERE employeeNo NOT IN (

SELECT salerNo

FROM OrderMaster

WHERE salerNo NOT IN(

SELECT salerNo

FROM OrderMaster

GROUP BY salerNo

HAVING sum(orderSum )<1000))

GROUP BY employeeNo ,employeeName

结果：

(10) 找出目前业绩未超过5000元的员工，并按销售业绩的降序排序输出。

命令：

SELECT employeeNo ,employeeName ,sum(orderSum ) 销售额

FROM OrderMaster a RIGHT OUTER JOIN Employee b ON salerNo =employeeNo

WHERE salerNo IN(

SELECT salerNo

FROM OrderMaster

GROUP BY salerNo

HAVING sum(orderSum )<5000)

GROUP BY employeeNo ,employeeName

ORDER BY 销售额 DESC

结果：

(11) 在Employee表中查询薪水超过员工平均薪水的员工信息。

命令：

SELECT employeeNo ,employeeName ,salary

FROM Employee

WHERE salary >(

SELECT avg(salary )

FROM Employee )

GROUP BY employeeNo ,employeeName ,salary

结果：

(12) 计算每一种商品的销售数量、平均销售单价和总销售金额。

命令：

SELECT productNo ,count(quantity ) 销售数量,avg(price ) 平均销售单价,sum(quantity *price ) 总销售额

FROM OrderDetail

GROUP BY productNo

ORDER BY productNo

结果：

(13) 查找至少有3次销售的业务员名单和销售日期。

命令：

SELECT employeeName ,orderDate

FROM Employee a,OrderMaster b,(

SELECT salerNo

FROM OrderMaster

GROUP BY salerNo

HAVING count(*)>=3) c

WHERE employeeNo =b.salerNo and b.salerNo =c.salerNo

GROUP BY employeeName ,orderDate

结果：

(14) 用存在量词查找没有订货记录的客户名称。

命令：

SELECT CustomerName

FROM Customer a

WHERE NOT EXISTS (

SELECT CustomerNo

FROM OrderMaster b

WHERE a.CustomerNo =b.CustomerNo )

结果：

(15) 查询订单中所订购的商品数量没有超过10个的客户编号和客户名称。

命令：

SELECT a.CustomerNo ,a.CustomerName

FROM Customer a

WHERE EXISTS (

SELECT *

FROM OrderDetail b,OrderMaster c

WHERE a.CustomerNo =c.CustomerNo and b.orderNo =c.orderNo

GROUP BY a.CustomerNo

HAVING sum(quantity )<10)

结果：

(16) 在销售明细表中按商品编号进行汇总，统计每种商品的销售数量和金额。

命令：

SELECT productNo ,sum(quantity ) 销售数量,sum(quantity *price ) 金额

FROM OrderDetail

GROUP BY productNo

结果：

(17) 按客户编号统计每个客户2008年2月的订单总金额。

命令：

SELECT a.CustomerNo ,orderSum ,orderDate

FROM Customer a LEFT OUTER JOIN OrderMaster b ON a.CustomerNO =b.CustomerNo

WHERE year(orderDate )='2008' and month(orderDate )='2'

结果：

(18) 查找定单金额高于8000的所有客户编号。

命令：

SELECT CustomerNo

FROM OrderDetail as a,OrderMaster as b

WHERE a.orderNo =b.orderNo

GROUP BY CustomerNo

HAVING sum(quantity *price )>8000

结果：

(19) 显示每种商品的销售金额总和，并依销售金额由大到小输出。

命令：

SELECT productNo ,sum(price *quantity ) 销售金额

FROM OrderDetail

GROUP BY productNo

ORDER BY 销售金额 desc

结果：

(20) 查找销售金额最大的客户名称和总货款。

命令：

SELECT top 1 CustomerName ,max(总货款) 总货款

FROM (SELECT CustomerNo ,sum(orderSum ) 总货款

FROM OrderMaster

GROUP BY CustomerNo ) a,OrderMaster b,Customer c

WHERE a.CustomerNo =b.CustomerNo and b.CustomerNo =c.CustomerNo

GROUP BY CustomerName

ORDER BY 总货款 desc

结果：

(21) 查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。

命令：

SELECT a.CustomerNo ,a.CustomerName , c.productNo ,productName ,quantity ,quantity *price 金额,orderDate

FROM Customer a,OrderMaster b,OrderDetail c,Product d

WHERE b.orderNo =c.orderNO and c.productNo =d.productNo and a.CustomerNo IN(

SELECT CustomerNo

FROM OrderMaster

WHERE orderNo IN (

SELECT orderNO

FROM OrderDetail

GROUP BY orderNo

HAVING count(*)>=3))

ORDER BY a.CustomerNo , c.productNo

结果：

(22) 找出目前业绩超过232000元的员工编号和姓名。

命令：

SELECT employeeNo ,employeeName

FROM OrderMaster ,Employee

WHERE salerNo =employeeNo

GROUP BY employeeNo ,employeeName

HAVING sum(orderSum )>232000

结果：

(23) 找出目前销售业绩超过40000元的业务员编号及销售业绩，并按销售业绩从大到小排序。

命令：

SELECT employeeNo ,sum(orderSum ) 销售额

FROM OrderMaster ,Employee

WHERE salerNo =employeeNo

GROUP BY employeeNo ,employeeName

HAVING sum(orderSum )>40000

ORDER BY 销售额 desc

结果：

(24) 求出每位客户的总订购金额，显示出客户号及总订购金额，并按总订购金额降序排列。

命令：

SELECT CustomerNo ,sum(orderSum ) 总订购金额

FROM OrderMaster

GROUP BY CustomerNo

ORDER BY 总订购金额 desc

结果：

(25) 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。

命令：

SELECT CustomerNo ,sum(quantity ) 每种商品总数量,avg(price ) 平均单价

FROM OrderDetail a,OrderMaster b

WHERE a.orderNo =b.orderNo

GROUP BY a.productNo ,CustomerNo

ORDER BY CustomerNo ,productNo

结果：

(26) 查询业绩最好的的业务员号、业务员名及其总销售金额。

命令：

SELECT top 1 employeeNo ,employeeName ,sum(orderSum ) 销售额

FROM OrderMaster ,Employee

WHERE salerNo =employeeNo

GROUP BY employeeNo ,employeeName

ORDER BY 销售额 desc

结果：

(27) 查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。

命令：

SELECT a.*

FROM OrderMaster a,OrderDetail b

WHERE a.orderNo =b.orderNo and productNo IN(

SELECT productNo

FROM OrderDetail

WHERE orderNo ='200803010001')

结果：

(28) 求每种商品的总销售数量及总销售金额，要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列。

命令：

SELECT a.productNo ,productName ,sum(quantity ) 总数量,sum(price *quantity ) 销售金额

FROM OrderDetail a,Product b

WHERE a.productNo =b.productNo

GROUP BY a.productNo ,productName

ORDER BY a.productNo desc

结果：

(29) 查询总订购金额超过“C20070002”客户的总订购金额的客户号、客户名及其住址。

命令：

SELECT a.CustomerNo ,CustomerName ,address

FROM OrderMaster a,Customer b

WHERE a.CustomerNo =b.CustomerNo

GROUP BY a.CustomerNo ,CustomerName ,address

HAVING sum(orderSum )>(

SELECT sum(orderSum )

FROM OrderMaster

WHERE CustomerNo ='C20070002')

结果：

本文来源：https://www.2haoxitong.net/k/doc/d6f06a20aaea998fcc220e2b.html