作业题 第三周

非常折磨 还差一半多的样子

我不干了我打原神去了（）

ps:导入备份数据库的方法：

选择database->new db

先新建一个数据库

选择restore database

选右边”…”添加文件，打开的页面里点add添加

options中勾选覆盖

然后ok，等待

–（一）以数据库系统实验1中student数据库为基础，请使用T-SQL 语句实现进行以下操作：

–1. 查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的详细情况；

SELECT *
FROM Course
WHERE Cname LIKE 'DB_%s__';

–2. 查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；

SELECT Sname,Student.Sno,Course.Cno,Cname
FROM Student JOIN SC ON(Student.Sno=SC.Sno) JOIN Course ON(SC.Cno=Course.Cno)
WHERE Sname LIKE '_阳%';

–3. 列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

SELECT Student.Sno,Sname,Sdept,Course.Cno,Grade
FROM Student JOIN SC ON(Student.Sno=SC.Sno) JOIN Course ON(SC.Cno=Course.Cno)
WHERE Cname='数学' OR Cname='大学英语';

–4. 查询缺少成绩的所有学生的详细情况；

SELECT *
FROM Student JOIN SC ON(Student.Sno=SC.Sno)
WHERE Grade IS NULL;

–5. 查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

SELECT *
FROM Student
WHERE Sage NOT IN(SELECT Sage FROM Student WHERE Sname='张力');

–6. 查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；

SELECT Student.Sno,Sname,AVG(Grade) AS '平均成绩'
FROM Student JOIN SC ON(Student.Sno=SC.Sno)
GROUP BY Student.Sno,Sname
HAVING AVG(Grade)>(
	SELECT AVG(Grade) 
	FROM Student JOIN SC ON(Student.Sno=SC.Sno)
	WHERE Sname='张力');

–7. 按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

SELECT Student.Sno,Sname,Sdept,SUM(CCredit) AS '已修学分'
FROM Student JOIN SC ON(Student.Sno=SC.Sno) JOIN Course ON(SC.Cno=Course.Cno)
WHERE Grade>=60
GROUP BY Student.Sno,Sname,Sdept;
--有问题,这样写已修学分为0的同学就不显示了

–8. 列出只选修一门课程的学生的学号、姓名、院系及成绩；

SELECT Student.Sno,Sname,Sdept,SUM(Grade) AS '成绩'
FROM Student JOIN SC ON(Student.Sno=SC.Sno)
GROUP BY Student.Sno,Sname,Sdept
HAVING COUNT(*)=1;

–9. 查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；

SELECT DISTINCT X.Sno,Sname,Z.Cno
FROM Student X JOIN SC Y ON(X.Sno=Y.Sno) JOIN SC Z ON(X.Sno=Z.Sno)
WHERE EXISTS(
	SELECT *
	FROM Student JOIN SC ON(Student.Sno=SC.Sno)
	WHERE Sname='张力' AND Cno=Y.Cno);

–10. 查询至少选修“数据库”和“数据结构”课程的学生的基本信息；

SELECT *
FROM Student X
WHERE EXISTS(
	SELECT Student.Sno
	FROM Student JOIN SC ON(Student.Sno=SC.Sno) JOIN Course ON(SC.Cno=Course.Cno)
	WHERE Cname='数据库' AND Student.Sno=X.Sno
	INTERSECT
	SELECT Student.Sno
	FROM Student JOIN SC ON(Student.Sno=SC.Sno) JOIN Course ON(SC.Cno=Course.Cno)
	WHERE Cname='数据结构' AND Student.Sno=X.Sno);

–11. 查询没有选修张力所选修的全部课程的学生的姓名；

SELECT DISTINCT Sname
FROM Student,SC X
WHERE Student.Sno=X.Sno AND NOT EXISTS(
	SELECT *
	FROM Student JOIN SC Y ON(Student.Sno=Y.Sno)
	WHERE Sname='张力' AND EXISTS(
		SELECT *
		FROM SC Z
		WHERE X.Sno=Z.Sno AND Y.Cno=Z.Cno));

–12. 查询每个专业年龄超过该专业平均年龄的学生的姓名和专业；

SELECT Sname,Sdept
FROM Student
WHERE Sage>(
	SELECT AVG(Sage)
	FROM Student X
	WHERE Student.Sdept=X.Sdept
	GROUP BY Sdept);

–13. 查询选修了张力同学所选修的全部课程的学生的姓名；

SELECT Sname
FROM Student X
WHERE NOT EXISTS(
	SELECT *
	FROM Student JOIN SC Z ON(Student.Sno=Z.Sno)
	WHERE Sname='张力' AND NOT EXISTS(
		SELECT *
		FROM SC Y
		WHERE Y.Sno=X.Sno AND Z.Cno=Y.Cno));

–14. 检索选修了全部课程的学生姓名；

SELECT Sname
FROM Student X
WHERE NOT EXISTS(
	SELECT *
	FROM COURSE Y
	WHERE NOT EXISTS(
		SELECT *
		FROM SC Z
		WHERE X.Sno=Z.Sno AND Y.Cno=Z.Cno));

–15. 列出同时选修“1”号课程和“2”号课程的所有学生的姓名；（使用两种方法实现）

SELECT Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND Cno='1'
INTERSECT
SELECT Sname
FROM Student,SC
WHERE Student.Sno=SC.Sno AND Cno='2';

SELECT Sname
FROM Student X,SC
WHERE X.Sno=SC.Sno AND Cno='1' AND EXISTS(
	SELECT *
	FROM Student,SC
	WHERE Student.Sno=SC.Sno AND X.Sno=Student.Sno AND Cno='2');

–16. 使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；

SELECT Student.Sno,Sname
FROM Student JOIN SC ON(Student.Sno=SC.Sno) JOIN Course Y ON(SC.Cno=Y.Cno)
WHERE EXISTS(
	SELECT *
	FROM Course X
	WHERE Cname='数据结构' AND X.Cno=Y.Cno);

–17. 使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；

SELECT Sname,Sage,Sdept
FROM Student
WHERE Sdept!='CS' AND Sage<ANY(
	SELECT Sage
	FROM Student
	WHERE Sdept='CS');

–18. 查询选课人数最多的课程号和课程名（包含并列）；

SELECT TOP 1 WITH TIES Course.Cno,Cname
FROM SC,Course
WHERE SC.Cno=Course.Cno
GROUP BY Course.Cno,Cname
ORDER BY COUNT(*) DESC;

–19. 使用集合查询列出CS系的学生以及性别为女的学生名单；

SELECT *
FROM Student
WHERE Sdept='CS'
UNION 
SELECT *
FROM Student
WHERE Ssex='女';

–20. 使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集；

SELECT *
FROM Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19;

SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM Student
WHERE Sage<=19;

（二）以数据库原理实验1中SPJ数据库为基础，请使用T-SQL 语句实现教材P130第5题（1）—（7）

–1.找出所有供应商的姓名和所在城市；

SELECT SNAME,CITY
FROM S;

–2.找出所有零件的名称、颜色、重量；

SELECT PNAME,COLOR,WEIGHT
FROM P;

–3.找出使用供应商S1所供应零件的工程号码；

SELECT JNO
FROM SPJ
WHERE SNO='S1';

–4.找出工程项目J2使用的各种零件的名称及其数量；

SELECT PNAME,QTY
FROM SPJ JOIN P ON(SPJ.PNO=P.PNO)
WHERE JNO='J2';

–5.找出上海厂商供应的所有零件号码；

SELECT DISTINCT PNO
FROM S JOIN SPJ ON (S.SNO=SPJ.SNO)
WHERE CITY='上海';

–6.找出使用上海产的零件的工程名称；

SELECT JNAME
FROM S JOIN SPJ ON (S.SNO=SPJ.SNO) JOIN J ON(J.JNO=SPJ.JNO)
WHERE S.CITY='上海';

–7.找出没有使用天津产的零件的工程号码；

SELECT JNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO
EXCEPT
SELECT JNO
FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY='天津';

–（三）对罗斯文（Northwind）数据库完成一下查询

–1.查询每个订单购买产品的数量和总金额，显示订单号，数量，总金额

SELECT OrderID,Quantity,UnitPrice*Quantity*(1-Discount) AS '总金额'
FROM [Order Details];

SELECT OrderID,SUM(Quantity) AS '订单数',SUM(UnitPrice*Quantity*(1-Discount)) AS '总金额'
FROM [Order Details]
GROUP BY OrderID;
--我感觉下面这个版本更对一些
 

–2. 查询每个员工在7月份处理订单的数量

SELECT EmployeeID,COUNT(*) AS '订单数'
FROM Orders
WHERE MONTH(OrderDate)=7
GROUP BY EmployeeID;

–3. 查询每个顾客的订单总数，显示顾客ID，订单总数

SELECT CustomerID,COUNT(*) AS '订单总数'
FROM Orders
GROUP BY CustomerID;

–4. 查询每个顾客的订单总数和订单总金额

SELECT CustomerID,SUM(Quantity) AS '订单数',SUM(UnitPrice*Quantity*(1-Discount)) AS '总金额'
FROM [Order Details] JOIN Orders ON([Order Details].OrderID=Orders.OrderID)
GROUP BY CustomerID;

–5. 查询每种产品的卖出总数和总金额

SELECT ProductID,SUM(Quantity) AS '卖出总数',SUM(UnitPrice*Quantity*(1-Discount)) AS '总金额'
FROM [Order Details]
GROUP BY ProductID;

–6. 查询购买过全部商品的顾客的ID和姓名

SELECT CustomerID,ContactName
FROM Customers X
WHERE NOT EXISTS(
	SELECT *
	FROM Products Y
	WHERE NOT EXISTS(
		SELECT *
		FROM Orders JOIN [Order Details] ON (Orders.OrderID=[Order Details].OrderID) 
		JOIN Products ON(Products.ProductID=[Order Details].ProductID)
		WHERE X.CustomerID=CustomerID AND Y.ProductID=Products.ProductID));