实验四 数据查询——简单查询 Sql Server数据库实验

实验四  数据查询——简单查询

 

一、实验目的

1．掌握SQL查询语句的基本概念 

2．掌握SQLServer查询语句的基本语法

3．熟练使用SQL的Select语句对单表进行查询

4．熟练掌握并运用SQLServer所提供的函数

5．熟练使用SQL语句进行连接操作 

 

二、实验环境（实验的软件、硬件环境）

硬件：PC机 软件：SQL2000

 

三、实验说明

请复习相关的单表查询及select语句的语法知识点，并完成如下内容。

 

四、实验内容

1．在订单数据库orderDB中，完成如下的查询：

（1）查询所有业务部门的员工姓名，职称，薪水

（2）查询名字中含有“有限”的客户姓名和所在地

（3）查询姓“王”并且姓名的最后一个字为“成”的员工

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

（5）查询订单金额高于8000的所有客户编号

（6）选取编号界于C0001~C0004的客户编号，客户名称，客户地址

（7）找出同一天进入公司服务的员工

（8）在订单主表中查询订单金额大于“E2005002”业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。

（9）查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。

（10）查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。

（11）查询单价高于400元的商品编号、商品名称、订货数量和订货单价。

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

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

（14）查找在2008年3月中有销售记录的客户编号、名称和订单总额。

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

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

（17）查找公司男业务员所接且订单金额超过2000元的订单号及订单金额。

（18）查找来自上海市的客户的姓名，电话，订单号及订单金额。

五、实验步骤

请完成实验内容，并写出具体的实验步骤

 

六、思考题：

1．连接操作类型有哪些？分析外连接在现实应用中的意义？

2．查询表可以用在什么地方？使用查询表要注意哪些地方？

3．分析between…and、and、or等关键字的使用方法？

4．分析哪些情况需要使用自身连接？

5．总结SQL语句中单表查询语句的使用方法？

 

七、总结（实验过程的体会、心得和实验教与学之间还需改进的内容）

 

use OrderDB

/* 简单查询都比较简单，注意下case 语句使用，以及第7题  ，12题的连接写法 */

/* 做实验的时候最好都运行一下，看看结果对不对，没有这种数据的话就自己设置几组符合情况的数据*/
--（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,Sex =  (case Sex when 'M' then '男'
when 'F' then '女' end) 
 from Employee where Address in ('上海市','南昌市') and Sex='F'

--（5）查询订单金额高于8000的所有客户编号
select CustomerNo from OrderMaster where Ordersum > 8000


--（6）选取编号界于C0001~C0004的客户编号，客户名称，客户地址
select CustomerNo,CustomerName,Address from Customer where CustomerNo >= 'C0001' and CustomerNo <= 'C0004'


--（7）找出同一天进入公司服务的员工（注意一下）
select a.EmployeeNo,a.EmployeeName,a.Hiredate,b.EmployeeNo,b.EmployeeName,a.Hiredate
from Employee a,Employee b where CONVERT(date,a.hiredate)=CONVERT(date,b.hiredate)
and a.EmployeeNo>b.EmployeeNo


--（8）在订单主表中查询订单金额大于“E2005002”业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。
select * from OrderMaster where Ordersum >(select min(Ordersum) from OrderMaster where SaleNo='E2005002' and year(Orderdate) = '2008' and month(Orderdate) ='1' and day(Orderdate) ='9')


--（9）查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。  商品信息表，订单主表，订单明细表

select CustomerNo,b.OrderNo,Ordersum from Product a,OrderMaster b,OrderDetail c where a.ProductNo=c.ProductNo and b.OrderNo = c.OrderNo and ProductName = '52倍速光驱' and
b.OrderNo in(
select b.OrderNo from Product a,OrderMaster b,OrderDetail c where a.ProductNo=c.ProductNo and b.OrderNo = c.OrderNo and ProductName = '17寸显示器'
)


--（10）查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。
select EmployeeName,Sex,Department,Headship from Employee
 where Department in (select Department from Employee where EmployeeName ='陈诗杰')


--（11）查询单价高于400元的商品编号、商品名称、订货数量和订货单价。    订货单价 是price 还是 ProductPrice
select a.ProductNo,ProductName,Qty,Price  from Product a,OrderDetail b
 where ProductPrice > 400 and a.ProductNo = b.ProductNo 


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

select a.ProductNo,ProductName,qty,price from Product a left outer join 
OrderDetail b on(a.ProductNo=b.ProductNo) and ProductPrice > 400


select a.ProductNo,ProductName,qty,price from Product a right outer join 
OrderDetail b on(a.ProductNo=b.ProductNo) and ProductPrice > 400


select a.ProductNo,ProductName,qty,price from Product a full join 
OrderDetail b on(a.ProductNo=b.ProductNo) and ProductPrice > 400




--（13）查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，
--其中性别使用“男”和“女”表示，日期使用yyyy-mm-dd格式显示。
select SaleNo,EmployeeName,Sex=case sex when 'M' then '男' when 'F' then '女' end ,ProductName,Qty,
ProductPrice,Ordersum,Orderdate=convert(date,Orderdate) from Employee a,OrderMaster b,OrderDetail c,Product d  
where a.EmployeeNo=b.SaleNo and b.OrderNo=c.OrderNo and c.ProductNo=d.ProductNo


--（14）查找在2008年3月中有销售记录的客户编号、名称和订单总额。
select a.customerno,CustomerName,Ordersum 
from OrderMaster a,Customer b where a.CustomerNo=b.CustomerNo and year(Orderdate)='2008' and month(Orderdate)='3'


--（15）使用左外连接查找每个客户的客户编号、名称、订单日期、订货金额，其中订货日期不要显示时间，
--日期格式为yyyy-mm-dd，按客户编号排序，同一客户再按订单金额降序排序输出。
select a.CustomerNo,CustomerName,Orderdate=CONVERT(date,Orderdate),Ordersum from Customer a left outer join OrderMaster b on(a.CustomerNo=b.CustomerNo)
order by a.CustomerNo asc,Ordersum desc


select a.CustomerNo from Customer a left outer join OrderMaster b on (a.CustomerNo=b.CustomerNo) order by a.CustomerNo,Ordersum desc
--（16）查找16M DRAM的销售情况，要求显示相应的销售员的姓名，性别，销售日期、销售数量和金额，其中性别用“男”，“女”表示。
select EmployeeName,Sex=(case sex when 'M' then '男' when 'F' then '女' end),Orderdate,Qty,Ordersum
 from Product a,OrderMaster b,OrderDetail c,Employee d 
where ProductName='16M DRAM' and a.ProductNo = c.ProductNo and b.OrderNo = c.OrderNo and b.SaleNo = d.EmployeeNo


--（17）查找公司男业务员所接且订单金额超过2000元的订单号及订单金额。注：员工表和订单主表 注意性别数据是用  M 和 F表示
select OrderNo,Ordersum from Employee a,OrderMaster b where a.EmployeeNo = b.SaleNo and Sex ='M' and Ordersum > 2000


--（18）查找来自上海市的客户的姓名，电话，订单号及订单金额。   注：客户表与订单主表
select CustomerName,Telephone,OrderNo,Ordersum  from Customer a,OrderMaster b where a.CustomerNo = b.CustomerNo and Address = '上海市'