《数据库原理与应用》(第三版)书后上机练习题——第六章

• 所有关于VB课的题目，这里可以选择把表中的Java课改成VB课，或者把题目中的VB课改为Java课
  • 我这里是把题目中的VB课改为Java课来写的
• 18题: 查询选课人数最多的前两名学生的学号和选课门数，包括并列的情况。
  • 这里不通顺应该是: 选课门数最多的前两名学生

SELECT * FROM SC

SELECT Sname,Sage FROM Student WHERE Sdept='计算机系'

SELECT Sno,Cno,Grade FROM SC WHERE Grade BETWEEN 70 AND 80

SELECT Sname,Sage FROM Student 
	WHERE Sdept='计算机系' AND Sage BETWEEN 18 AND 20 AND Ssex='男'

SELECT MAX(Grade) AS c01的最高分 FROM SC WHERE Cno = 'c01'

SELECT MAX(Sage) AS 计算机系的最大年龄,MIN(Sage) AS 计算机系的最小年龄 FROM Student
	WHERE Sdept = '计算机系'

SELECT Sdept,COUNT(*) AS 学生人数 FROM Student GROUP BY Sdept

SELECT Sno,COUNT(*) AS 选课门数,SUM(Grade) AS 总成绩 FROM sc GROUP BY Sno
	ORDER BY COUNT(*) ASC

SELECT Sno,SUM(Grade) AS 总成绩 FROM SC
	GROUP BY Sno HAVING SUM(Grade)>200

SELECT Sname,Sdept FROM Student S JOIN SC ON S.Sno = SC.Sno
	WHERE Cno = 'c02'

SELECT Sname,Cno,Grade FROM Student S JOIN SC ON S.sno = SC.sno
	WHERE Grade>80 ORDER BY Grade DESC

SELECT S.Sno,Sname,Sdept FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno
	WHERE SC.Cno is NULL

SELECT C.Cno,COUNT(SC.Sno) AS 选课人数 FROM Course C LEFT JOIN SC
	ON C.Cno = SC.Cno GROUP BY C.Cno

/*题目应该是 查询与“Java"在同一学期开设的课程的课程名和开课学期。*/
SELECT Cname,Semester FROM Course
	WHERE Semester IN (
		SELECT Semester FROM Course WHERE Cname='Java')

SELECT Sname,Sdept,Sage FROM Student
	WHERE Sage IN (
		SELECT Sage FROM Student WHERE Sname='李勇')

SELECT TOP 2 WITH TIES Sname,Sage FROM Student
	WHERE Sdept='计算机系'
	ORDER BY Sage ASC

/*题目应该是 查询Java成绩最高的前两名学生的姓名，所在系和Java成绩，包括并列情况*/
SELECT TOP 2 WITH TIES Sname,Sdept,Grade
	FROM Student S JOIN	SC ON	S.Sno=SC.Sno
	JOIN Course C ON C.Cno=SC.Cno
	WHERE Cname = 'Java'
	ORDER BY Grade DESC

/*题目应该是 查询选课门数组多的前两名学生的学号和选课门数，包括并列情况*/
SELECT TOP 2 WITH TIES Sno,COUNT(*) AS 选课门数
	FROM SC GROUP BY Sno ORDER BY COUNT(*) DESC

SELECT TOP 1 Sdept,COUNT(*) AS 人数 FROM Student
	GROUP BY Sdept ORDER BY COUNT(*) DESC

SELECT Sname,Sdept FROM Student
	WHERE Sno IN (SELECT Sno FROM SC WHERE Cno = 'c01')

SELECT Student.Sno,Sname,Cno,Grade FROM Student JOIN SC ON Student.Sno=SC.Sno
	WHERE Student.Sno IN(SELECT Sno FROM Student WHERE Sdept = '数学系') 
	AND SC.Sno IN(SELECT Sno FROM SC WHERE Grade > 80)

SELECT Sname FROM Student JOIN SC ON Student.Sno=SC.Sno 
	WHERE Sdept = '计算机系' AND Grade = (SELECT MAX(Grade) FROM
		SC JOIN Student ON SC.Sno=Student.Sno WHERE Sdept = '计算机系') 

SELECT Sname,Sdept,Ssex,Grade FROM Student
	JOIN SC ON Student.Sno=SC.Sno
	JOIN Course ON SC.Cno=Course.Cno
	WHERE Cname='数据结构' AND Grade=(SELECT MAX(Grade)
		FROM SC JOIN Course ON SC.Cno=Course.Cno WHERE Cname='数据结构')

/*题目应该是 查询没选“Java*课程的学生姓名和所在系*/
SELECT Sname,Sdept FROM Student 
	WHERE Sno IN(SELECT Sno FROM SC JOIN Course ON SC.Cno = Course.Cno
		WHERE Cname != 'Java')

SELECT Sname,Ssex FROM Student
 LEFT JOIN SC ON Student.Sno=SC.Sno
 WHERE Sdept='计算机系' AND SC.Sno IS NULL

SELECT Sname,Cname FROM Student
	JOIN SC ON Student.Sno=SC.Sno
	JOIN Course ON SC.Cno=Course.Cno
	WHERE  Student.Sno IN
	(SELECT TOP 1 WITH TIES Student.Sno 
		FROM Student JOIN SC ON Student.Sno=SC.Sno
		WHERE Sdept='计算机系'
		GROUP BY Student.Sno ORDER BY AVG(Grade) ASC)

SELECT Cname,Semester,Credit FROM Course 
	WHERE Cno IN 
	(SELECT TOP 1 WITH TIES Cno FROM SC 
		WHERE Cno IN
		(SELECT Cno FROM Course
		 WHERE Semester
		 BETWEEN 1 AND 5)
		GROUP BY Cno ORDER BY COUNT(Sno) 
	)
	/*对1到5学期的课程号进行分组，根据学生数量排序（就是选课人数）*/

/*查询计算机系每个学生的考试情况，列出姓名、课程名和考试成绩，并将查询结果保存到一个新表中。新表名为: Computer_Dept*/
SELECT Sname,Cname,Grade INTO Computer_Dept
	FROM Student 
	LEFT JOIN SC ON Student.Sno=SC.Sno
	LEFT JOIN Course ON SC.Cno=Course.Cno
	WHERE Sdept='计算机系'

CREATE TABLE test_t(
	COL1 int,
	COL2 char(10) not NULL,
	COL3 char(10)
)
INSERT INTO test_t(COL2) VALUES('B1')
INSERT INTO test_t(COL1,COL2,COL3) VALUES(1,'B2','C2')
INSERT INTO test_t(COL1,COL2) VALUES(2,'B3')

DELETE FROM SC WHERE Grade < 50

没有低于50分的

DELETE FROM Course
	WHERE Cno NOT IN(SELECT Cno FROM SC)

删了两个课程 c03计算机网路，c08离散数学

DELETE FROM SC
	FROM SC JOIN Student ON Student.Sno=SC.Sno
	JOIN Course ON Course.Cno=SC.Cno
	WHERE Cname='Java' AND Grade < 60 AND Sdept='计算机系'

没有不及格的

DELETE FROM SC
	FROM SC JOIN Course ON Course.Cno=SC.Cno
	WHERE Cname='Java' AND Grade=(
		SELECT MIN(Grade) FROM SC
		JOIN Course ON Course.Cno=SC.Cno
		WHERE Cname='Java')

删除了9521103 c02 68 这条记录

UPDATE Course SET Credit=Credit+2 
	WHERE Semester=2

高等数学学分从8变为10

UPDATE Course SET Credit=3
	WHERE Cname='Java'

UPDATE Student SET Sage=Sage+1
	WHERE Sdept='计算机系'

UPDATE SC SET Grade=Grade+5
	FROM SC JOIN Course ON Course.Cno=SC.Cno
	JOIN Student ON SC.Sno=Student.Sno
	WHERE Sdept = '信息系' AND Cname='计算机文化学'

9521102 c01 分数从82变为87

UPDATE Course SET Credit=Credit-1
	WHERE Cno IN (SELECT TOP 1 WITH TIES Course.cno
		FROM Course LEFT JOIN SC ON Course.cno=Sc.cno
		GROUP BY Course.cno
		ORDER BY COUNT(Sno))

图中的代码是错误的！看上面的代码
数据库基础、数据结构、操作系统的学分从4、5、4变为3、4、3


如有大佬发现错误，还请指正，感谢！