【Sql Server】实验报告三：SQL的数据查询

一、实验目的

1．掌握单表查询。

2．掌握多表连接查询。

3．掌握子查询、集合查询。

4. 掌握派生表查询

5. 掌握聚合函数使用方法

二、实验内容

操作系统：Windows 10

数据库管理系统：SQL Server 2017

SQL Server数据库创建

参考的是教材P84页的“实验3-2数据查询”的内容

• 实验过程

1.查询商品种类信息

select * from Category

2.查询IT专业所有学生信息

select * from Student where Major='IT'

3.查询MIS专业年龄大于20岁的学生信息。并为MIS列取别名为”信息管理系统“

select SNO,SName,BirthYear,Ssex,college,Major as "信息管理系统",WeiXin from Student 
where Major='MIS'and YEAR(GETDATE())-BirthYear>20

4.查询利润率大于30%的商品编号与商品名

select GoodsNO,GoodsName from Goods 
where ((SalePrice-InPrice)/InPrice)>0.3

5.查询广州佛山供应的商品信息

select * from Goods 
where SupplierNO=(select SupplierNo from Supplier where Address='广州佛山')

6.查询购买了商品种类为咖啡的MIS专业的学生信息

--先筛选出咖啡的商品编号

select GoodsNO from Goods 
join Category on Goods.CategoryNO=Category.CategoryNO 
where CategoryName='咖啡'

--查找MIS学生的消费记录

select * from Student join SaleBill on Student.SNO=SaleBill.SNO

where Major='MIS'

--消费记录中选择咖啡类商品编号

select DISTINCT Student.SNO,SName,BirthYear,Ssex,college,Major,WeiXin from Student

join SaleBill on Student.SNO=SaleBill.SNO

where Major='MIS'

and GoodsNO in

(select GoodsNO from Goods join Category on Goods.CategoryNO=Category.CategoryNO 
where CategoryName='咖啡')

7.查询购买了商品种类为咖啡的各专业的学生人数

select Major,count(*) as 人数 from Student
join SaleBill on Student.SNO=SaleBill.SNO
where GoodsNO in
(select GoodsNO from Goods join Category on Goods.CategoryNO=Category.CategoryNO where CategoryName='咖啡')
group by Major

8.查询购买各商品种类的各专业的学生人数

select Major,count(*) as 人数 from Student 
join SaleBill on Student.SNO=SaleBill.SNO
where GoodsNO in
(select GoodsNO from Goods join Category on Goods.CategoryNO=Category.CategoryNO)
group by Major

9.查询从未购买过商品的学生信息

select * from Student where SNO not in(select SNO from SaleBill)

10.查询与商品编号GN0005相同产地的商品编号、商品名

select GoodsNO, GoodsName from Goods

where SupplierNO in (select SupplierNO from Goods where GoodsNO='GN0005')

11.使用派生表查询各供应商的存货量

select Supplier.SupplierNO,SupplierName,kc.Number
from Supplier join
(select SupplierNO,sum(Number) from Goods group by SupplierNO) 
as kc(SupplierNO,Number)
on Supplier.SupplierNO=kc.SupplierNO

12.查询售价大于该种类商品售价均值的商品号、商品名

--得到商品种类及平均售价

select CategoryNO,avg(SalePrice) as AvgPrice from Goods group by CategoryNO

--完整代码
select GoodsNO,GoodsName from Goods

join (select CategoryNO,avg(SalePrice) as AvgPrice from Goods group by CategoryNO) 

as ag

on Goods.CategoryNO=ag.CategoryNO

where Goods.SalePrice>ag.AvgPrice

13.分别用子查询与连接查询查询购买了商品编号为”GN0003“和”GN0007“的学生学号与姓名

--子查询

select SNO,SName from Student where SNO in

(select SNO from SaleBill where GoodsNO='GN0003'or GoodsNO='GN0007')

--连接查询

select Student.SNO,Student.SName from Student

join(select DISTINCT SNO from SaleBill where GoodsNO='GN0003'or GoodsNO='GN0007') g

on Student.SNO=g.SNO

order by Student.SNO

14.查询各校销售额

--每个学校的学生

select SNO,college from Student

--每个学生的销售额

select SNO,sum(s.Number*g.SalePrice) sumprice from SaleBill s 
join (select GoodsNO,SalePrice from Goods) g on s.GoodsNO=g.GoodsNO 
group by SNO

--各校的销售额

select college,sum(sumprice) 销售额 from Student st
join(select SNO,sum(s.Number*g.SalePrice) sumprice from SaleBill s 
join (select GoodsNO,SalePrice from Goods) g on s.GoodsNO=g.GoodsNO group by SNO) p
on st.SNO=p.SNO
group by college

15.查询购买额前三的校名、专业名

select college,Major, sum(sumprice) 销售额 from Student st
join(select SNO,sum(s.Number*g.SalePrice) sumprice from SaleBill s 
join (select GoodsNO,SalePrice from Goods) g on s.GoodsNO=g.GoodsNO group by SNO) p
on st.SNO=p.SNO
group by college,Major
order by Major desc

16.使用集合查询方式查询生产日期早于2018-1-1或库存量小于30的商品信息

select* from Goods where ProductTime < '2018-1-1'

union

select * from Goods where Number<30

• 实验结果

1：

2：

3：

4：

5：

6：

7：

8：

9：

10：

11：

12：

13：

14：

15：

16：

• 实验出错问题和解决
• 实验心得