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

where a.orderNo2612=b.orderNo2612
group byCustomerNo2612
having sum(quantity2612*price2612)>8000
结果：
(9)选取编号界于“C20050001”和“C20050004”的客户编号、客户名称、客户地址。
命令：
SELECT CustomerNo2612,CustomerName2612,address2612
where exists
(select * from Employee2612 as b
where a.hireDate2612=b.hireDate2612 and a.employeeNo2612!=b.employeeNo2612)
group by hireDate2612,employeeName2612
命令：
select b.ProductName2612,a.ProductNo2612,a.total,a.price2612
from (
select sum(quantity2612) as total,ProductNo2612,price2612
from OrderDetail2612
group byProductNo2612,price2612
FROM Customer2612
WHERE CustomerNo2612 BETWEEN 'C20050001' AND 'C20050004'
结果：
(11)找出同一天进入公司服务的员工。
命令：
select hireDate2612,employeeName2612
from Employee2612 as a
group by a.ProductNo2612,quantity2612,price2612,ProductName2612
having price2612>400
select a.ProductNo2612,ProductName2612,quantity2612,price2612
from Product2612 a RIGHTOUTER JOIN OrderDetail2612 b ON a.ProductNo2612=b.ProductNo2612
from OrderDetail2612
group by ProductNo2612,price2612) as a , Product2612 as b
where a.ProductNo2612=b.ProductNo2612
结果：
(16)查询单价高于400元的商品编号、商品名称、订货数量和订货单价。
having price2612>400) as a , Product2612 as b
where a.ProductNo2612=b.ProductNo2612
结果：
(17)分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价，并分析比较检索的结果。
结果：
(4)查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示。
命令：
select employeeName2612,department2612,headShip2612,address2612,
case sex2612
when 'M' then '男'
(SELECT sum(quantity2612*price2612)
FROM OrderDetail2612 a,OrderMaster2612 b
WHERE a.orderNo2612=b.orderNo2612 and salerNo2612='E2005002' and orderDate2612='2008-01-09 00:00:00.000'
GROUP BY a.orderNo2612 )
结果：
(13)查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。
命令：
select b.CustomerNo2612,a.orderNo2612,sum(quantity2612*price2612) as total
where a.department2612=b.department2612
结果：
(15)查询每种商品的商品编号、商品名称、订货数量和订货单价。
命令：
select b.ProductName2612,a.ProductNo2612,a.total,a.price2612
from (
select sum(quantity2612) as total,ProductNo2612,price2612
SELECT orderNo2612,sum(quantity2612*price2612) as total
FROM OrderDetail2612
GROUP BY orderNo2612
HAVING sum(quantity2612*price2612)>10000
结果：
(6)选取订单金额最高的前10%的订单数据。
when 'F' then '女'
end as性别
from Employee2612
where address2612 like '%上海%' or address2612 like '%南昌%' and sex2612='F'
结果：
(5)在表sales中挑出销售金额大于等于10000元的订单。
命令：
命令：
select a.employeeName2612,a.sex2612,a.department2612,a.headShip2612
from Employee2612 as a,(select * from Employee2612 where employeeName2612='陈诗杰') as b
from OrderDetail2612 as a,OrderMaster2612 as b,Product2612 as c,
(select d.orderNo2612 from OrderDetail2612 as d,Product2612 as e where ProductName2612='17寸显示器'and d.ProductNo2612=e.ProductNo2612) as f
实验一
在订单数据库中完成如下的查询
(1)查询所有业务部门的员工姓名、职称、薪水
命令：
select employeeName2612,headShip2612,salary2612
from Employee2612
where department2612='业务科'
结果：
(2)查询名字中含有“有限”的客户姓名和所在地。
where c.ProductName2612='52倍速光驱' and a.orderNo2612=b.orderNo2612 and a.orderNo2612=f.orderNo2612
group byb.CustomerNo2612,a.orderNo2612
结果：
(14)查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。
FROM Employee2612 a,OrderMaster2612 b,OrderDetail2612 c,Product2612 d
WHERE employeeNo2612=salerNo2612 and b.orderNo2612=c.orderNo2612 and c.ProductNo2612=d.ProductNo2612
命令：
SELECT employeeNo2612,employeeName2612,
case sex2612 when 'F' then '女'
when 'M' then '男'
end as性别, ProductName2612,quantity2612,price2612,quantity2612*price2612 orderSum,ISNULL(convert(char(10),orderDate2612,120),'')日期
WHERE a.CustomerNo2612=b.CustomerNo2612 and b.orderNo2612=c.orderNo2612
and year(orderDate2612)=2008 and month(orderDate2612)=3
group by a.ProductNo2612,quantity2612,price2612,ProductName2612
having price2612>400
结果：
(18)查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。
group by a.ProductNo2612,quantity2612,price2612,ProductName2612
having price2612>400
select a.ProductNo2612,ProductName2612,quantity2612,price2612
from Product2612 a FULL OUTER JOIN OrderDetail2612 b ON a.ProductNo2612=b.ProductNo2612