数据库实验三

--（一）以数据库系统实验1中student数据库为基础，请使用T-SQL 语句实现进行以下操作：
--1.	查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的详细情况
select *
from course
where Cname like 'DB\_%s__'

--2.	查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名
select Sname,student.Sno,sc.Cno,Cname
from student,sc,course
where Sname like '_阳%' and student.Sno=sc.Sno and sc.Cno=course.Cno

--3.	列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩
select student.Sno,Sname,Sdept,sc.Cno,Grade
from student,sc,course
where student.Sno=sc.Sno and sc.Cno=course.Cno and Cname in ('数学','大学英语')

--4.	查询缺少成绩的所有学生的详细情况
select *
from student,sc,course
where student.Sno=sc.Sno and sc.Cno=course.Cno and Grade is null

--5.	查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；
select *
from student
where Sage<>(
	select Sage
	from student
	where Sname='张力'
)

--6.	查询所选课程的平均成绩大于张力的平均成绩的学生学、姓名及平均成绩
select student.Sno,Sname,AVG(Grade)
from student,sc
where student.Sno=sc.Sno
group by student.Sno,Sname
having AVG(Grade)>(
	select AVG(Grade)
	from student,sc
	where student.Sname='张力' and student.Sno=sc.Sno
	group by student.Sno,Sname
)


--7.	按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和
select student.Sno,Sname,Sdept,sum(Ccredit) 已修学分
from student,course,sc
where student.Sno=sc.Sno and sc.Cno=course.Cno
group by student.Sno,Sname,Sdept

--8.	列出只选修一门课程的学生的学号、姓名、院系及成绩
select student.Sno,Sname,Sdept,Grade
from sc,student
where student.Sno=sc.Sno and student.Sno in(
	select student.Sno
	from sc,student
	where student.Sno=sc.Sno
	group by student.Sno
	having count(sc.Cno)=1)

--9.	查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号
select student.Sno,Sname,Cno
from student,sc
where student.Sno=sc.Sno and Cno in (
	select Cno
	from sc,student
	where student.Sno=sc.Sno and student.Sname='张力'
)

--10.	查询至少选修“数据库”和“数据结构”课程的学生的基本信息
select student.Sno,Sname,Ssex,Sage,Sdept --同时选修这两门及以上的学生
from student
where student.Sno in (
	select sc.Sno
	from sc,course
	where sc.Cno=course.Cno and Cname='数据库' and  sc.Sno in(
		select sc.Sno
		from sc,course
		where sc.Cno=course.Cno and Cname='数据结构' 
	)
)


--11.	查询没有选修张力所选修的全部课程的学生的姓名
select s1.Sname
from student s1
where  not exists(
	select *
	from sc sc2,student
	where student.Sno=sc2.Sno and student.Sname='张力' and exists(
		select *
		from sc sc3
		where sc3.Cno=sc2.Cno and sc3.Sno=s1.Sno
		)
	)

--12.	查询每个专业年龄超过该专业平均年龄的学生的姓名和专业
select Sname,Sdept
from student s1
where exists(
	select *
	from student s2
	where s1.Sdept=s2.Sdept
	group by s2.Sdept
	having s1.Sage>avg(s2.Sage)
	)

--13.	查询选修了张力同学所选修的全部课程的学生的姓名
select s1.Sname
from student s1
where  not exists(
	select *
	from sc sc2,student
	where student.Sno=sc2.Sno and student.Sname='张力' and not exists(
		select *
		from sc sc3
		where sc3.Cno=sc2.Cno and sc3.Sno=s1.Sno
		)
	)


--14.	检索选修了全部课程的学生姓名
select Sname
from student
where not exists(
	select *
	from course
	where not exists(
		select *
		from sc
		where sc.Cno=course.Cno and sc.Sno=student.Sno
		)
	)

--15.	列出同时选修“1”号课程和“2”号课程的所有学生的姓名（使用两种方法实现）
select Sname
from student
where Sno in (
	select Sno
	from sc
	where sc.Cno=1 and Sno=sc.Sno and sc.Sno in (
			select Sno
			from sc
			where sc.Cno=2 and Sno=sc.Sno 
		)
	)

select Sname
from student
where exists (
	select *
	from sc
	where sc.Sno=student.Sno and sc.Cno=1 and exists(
		select *
		from sc
		where sc.Sno=student.Sno and sc.Cno=2 
		)
	)

--16.	使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名
select Sno,Sname
from student
where student.Sno in (
	select Sno
	from sc
	where sc.Sno in (
		select Sno
		from course
		where course.Cname='数据结构'
		)
	)

--17.	使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系
select Sname,Sage,Sdept
from student
where Sdept<>'CS' and Sage<ANY(
	select Sage
	from student
	where Sdept='CS'
	)


--18.	查询选课人数最多的课程号和课程名（包含并列）
select Cno,Cname
from course
where Cno in(
	select Cno
	from sc
	group by sc.Cno
	having count(sc.Sno)=(
		select top 1 count(sc.Cno)
		from sc
		group by sc.Cno
		order by count(sc.Cno) desc
		)
	)

--19.	使用集合查询列出CS系的学生以及性别为女的学生名单
select *
from student
where Sdept='CS'
intersect
select *
from student
where Ssex='女'

--20.	查询选修了所有男生所选的全部课程的女生的学号和姓名。
select Sno,Sname
from student s1
where not exists(
	select *
	from sc sc1,student s2
	where s2.Sno=sc1.Sno and s2.Ssex='男' and not exists(
		select *
		from sc sc2
		where s1.Sno=sc2.Sno and s1.Ssex='女' and sc1.Cno=sc2.Cno
		)
	)


--（二）对罗斯文（Northwind）数据库完成一下查询
--1.查询每个订单购买产品的数量和总金额，显示订单号，数量，总金额
select OrderID,count(Quantity),sum((UnitPrice*Quantity)*(1-Discount)) 总金额
from [Order Details]
group by OrderID

--2. 查询每个员工在7月份处理订单的数量
select Orders.EmployeeID,count(Orders.OrderID) 订单数
from Orders
where DATENAME(mm,OrderDate)=7
group by EmployeeID

--3. 查询每个顾客的订单总数，显示顾客ID，订单总数
select CustomerID,count(distinct Orders.OrderID)
from Orders
group by CustomerID

--4. 查询每个顾客的订单总数和订单总金额
select CustomerID,count(distinct Orders.OrderID) 订单总数,sum((UnitPrice*Quantity)*(1-Discount)) 总金额
from Orders,[Order Details]
where Orders.OrderID=[Order Details].OrderID
group by CustomerID

--5. 查询每种产品的卖出总数和总金额
select Products.ProductID,sum(Quantity) 总数,sum(([Order Details].UnitPrice*Quantity)*(1-Discount)) 总金额
from Products,[Order Details]
where [Order Details].ProductID=Products.ProductID
group by Products.ProductID

--6. 查询购买过全部商品的顾客的ID和姓名
select CustomerID,ContactName
from Customers
where not exists(
	select *
	from Products
	where not exists(
		select *
		from [Order Details],Orders
		where [Order Details].ProductID=Products.ProductID and [Order Details].OrderID=Orders.OrderID 
		and Customers.CustomerID=Orders.CustomerID
		)
	)

--（三）	对books数据库完成以下操作
--1.	查询各种类别的图书的类别和数量（包含目前没有图书的类别）
select BookType.TypeID,count(BookInfo.TypeID) 数量
from BookType
left join BookInfo 
on BookType.TypeID=BookInfo.TypeID
group by BookType.TypeID

--2.	查询借阅了‘数据库基础’的读者的卡编号和姓名
select CardInfo.CardNo,CardInfo.Reader
from BorrowInfo,CardInfo,BookInfo
where BookInfo.BookNo=BorrowInfo.BookNo and BorrowInfo.CardNo=CardInfo.CardNo and BookInfo.BookName='数据库基础' 

--3.	查询各个出版社的图书价格超过这个出版社图书的平均价格的图书的编号和名称
select b1.BookNo,b1.BookName
from BookInfo b1
where b1.Price>(
	select avg(b2.Price)
	from BookInfo b2
	where b2.Publisher=b1.Publisher
	group by b2.Publisher
)

--4.	查询借阅过了全部图书的读者的编号和姓名
select CardInfo.CardNo,CardInfo.Reader
from CardInfo
where not exists(
	select *
	from BookInfo b1
	where not exists(
		select *
		from BorrowInfo b2
		where b1.BookNo=b2.BookNo and  CardInfo.CardNo=b2.CardNo
	)
)

--5.	查询借阅图书包含李明所借的全部图书的读者的编号和姓名
select car1.CardNo,car1.Reader
from CardInfo car1
where not exists(
	select *
	from CardInfo car2,BorrowInfo bor1
	where car2.Reader='李明' and car2.CardNo=bor1.CardNo and not exists (
		select *
		from BorrowInfo bor2
		where bor2.CardNo=car1.CardNo and bor1.BookNo=bor2.BookNo
	)
)

--6.	查询借阅次数超过2次的读者的编号和姓名
select CardInfo.CardNo,CardInfo.Reader
from CardInfo,BorrowInfo
where CardInfo.CardNo=BorrowInfo.CardNo
group by CardInfo.CardNo,CardInfo.Reader
having count(*)>2


--7.	查询借阅卡的类型为老师和研究生的读者人数
select count(*) 人数
from CardInfo,CardType
where CardInfo.CTypeID=CardType.CTypeID and CardType.TypeName='教师'
union
select count(*) 人数
from CardInfo,CardType
where CardInfo.CTypeID=CardType.CTypeID and CardType.TypeName='研究生'

select '教师数'=count(case when TypeName ='教师'THEN 1 END), 
'研究生数'=count(case when TypeName ='研究生'THEN 1 END)
from CardInfo,CardType
where CardInfo.CTypeID=CardType.CTypeID

--8.	查询没有被借过的图书的编号和名称
select BookInfo.BookNo,BookInfo.BookName
from BookInfo
where not exists(
	select *
	from BorrowInfo
	where BookInfo.BookNo=BorrowInfo.BookNo
)

--9.	查询没有借阅过英语类型的图书的学生的编号和姓名
select CardInfo.CardNo,CardInfo.Reader
from CardInfo
where not exists (
	select *
	from BorrowInfo,BookType,BookInfo
	where BorrowInfo.CardNo=CardInfo.CardNo and BookInfo.BookNo=BorrowInfo.BookNo and BookInfo.TypeID=BookType.TypeID
		and BookType.TypeName='英语'
)


--10.	查询借阅了‘计算机应用’类别的‘数据库基础’图书的读者的编号读者以及该读者的借阅卡的类型
select CardInfo.CardNo,CardInfo.Reader,CardType.TypeName
from CardInfo,CardType
where CardInfo.CTypeID=CardType.CTypeID and CardInfo.CardNo in (
	select CardInfo.CardNo
	from CardInfo,BorrowInfo,BookType,BookInfo
	where BookInfo.TypeID=BookType.TypeID and BookInfo.BookName='数据库基础' and BookType.TypeName='计算机应用'
	 and BorrowInfo.CardNo=CardInfo.CardNo  and BorrowInfo.BookNo=BookInfo.BookNo
	)

--（四）	对商场数据库完成以下操作
--1.	列出北京各个商场都销售，且售价均超过10000 元的商品的商品号和商品名
select item.ino,item.iname
from item
where not exists (
	select *
	from market
	where market.city='北京' and not exists (
		select *
		from sales
		where  market.mno=sales.mno and  item.ino=sales.ino and sales.price>10000
		)
	)

--2.	列出在不同商场中最高售价和最低售价之差超过100 元的商品的商品号、最高售价和最低售价
select sales.ino,max(sales.price) 最高售价,min(sales.price) 最低售价
from sales
group by sales.ino
having max(sales.price)-min(sales.price)>100

--3.	列出售价超过该商品的平均售价的各个商品的商品号和售价
select s1.ino,s1.price
from sales s1
where s1.price>(
		select avg(s2.price)
		from sales s2
		where s1.ino=s2.ino
		group by s2.ino
	)

--4.	查询销售了全部商品的商场号，商场名和城市
select mno,mname,city
from market m1
where not exists(
	select *
	from item
	where not exists(
		select *
		from sales
		where sales.mno=m1.mno and sales.ino=item.ino
	)
)


--5.	查询所有商场都销售了的商品的商品号和商品名。（用两种方法实现）
select item.ino,item.iname
from item
where not exists(
	select *
	from market
	where not exists(
		select *
		from sales
		where sales.ino=item.ino and market.mno=sales.mno
	)
)

select item.ino,item.iname
from sales,item
where sales.ino=item.ino
group by item.ino,item.iname
having count(*)=(
	select count(*)
	from market
	)



--6.	查询每个商场里价格最高的商品的名称（用两种方法做）
select s1.mno,item.iname
from item,sales s1
where s1.ino=item.ino and s1.price>=all(
	select s2.price
	from sales s2
	where s2.mno=s1.mno
)

select s1.mno,item.iname
from item,sales s1
where s1.ino=item.ino and s1.price=(
	select max(s2.price)
	from sales s2
	where s1.mno=s2.mno
	group by s2.mno
)