SQL学习新的（三） - 简书
SQL学习新的（三）
学生管理数据库
自然链接的说明数据库下载地址为:说明这是我在学习数据库课时，老师给的27道SELECT语句练习题，在写这篇文章时，老师并没有给参考答案，写这篇文章的目的完全是为了加深我对SQL语句的理解和方便我以后复习、查阅，文中难免有错误之处，发现的朋友，望指正一二，感激不尽。数据库中有三张表，分别为student,course,SC（即学生表，课程表，选课表）//三张表截图如下：
--1.分别查询学生表和学生修课表中的全部数据。SELECT * FROM student--查询学生表SELECT * FROM course--查询课程表--2.查询成绩在70到80分之间的学生的学号、课程号和成绩。SELECT Sno 学号,Cno 课程号,Grade 成绩FROM SCWHERE Grade BETWEEN 70 AND 80--3．查询C01号课程成绩最高的分数SELECT TOP 1 GradeFROM SCWHERE Cno='C01'ORDER BY Grade DESC--降序DESC，升序ASC--4.查询学生都选修了哪些课程,要求列出课程号。SELECT Cname AS 学生选修的课程,Cno AS 课程号FROM courseWHERE Cno IN(SELECT DISTINCT Cno FROM SC)--DISTINCT用来去除重复--5.查询Northwind数据库中orders表的OrderID、CustomerID和OrderDate,--并将最新的定购日期(OrderDate)列在前边。USE NorthwindSELECT OrderID,CustomerID,OrderDateFROM ordersORDER BY OrderDate DESC--降序DESC，升序ASC--6.查询Northwind数据库中orders表的ShipCountry列以B,C,D,F --//ShipCountry BCDF--开始且第三个字符为"a"的OrderID、CustomerID和ShipCountry的信息。//OrderID、CustomerID第三个字符为"a"USE NorthwindGOSELECT OrderID,CustomerID,ShipCountry FROM ordersWHERE ShipCountry LIKE '[BCDF]_a%'--_下划线表示任意一个字符，%表示一个或多个字符--7.查询Northwind数据库中orders表的ShipCountry列不以A,B,C,D,E,F开始且最后一个字--母是"a"的OrderID、CustomerID和ShipCountry的信息。USE NorthwindGOSELECT OrderID,CustomerID,ShipCountryFROM ordersWHERE ShipCountry LIKE '[^ABCDEF]%a'--8.查询修了C02号课程的所有学生的平均成绩、最高成绩和最低成绩。SELECT AVG(Grade) 平均成绩,MAX(Grade) 最高成绩,MIN(Grade) 最低成绩FROM SCWHERE Cno='C02'--9.统计每个系的学生人数。SELECT Sdept 系别,COUNT(*) 人数FROM studentWHERE Sdept IN(SELECT DISTINCT Sdept FROM student)GROUP BY Sdept--10.统计每门课程的修课人数和考试最高分。SELECT Cname 课程名,COUNT(*) 修课人数,MAX(Grade) 考试最高分FROM SC,courseo IN(SELECT DISTINCT Cno FROM SC ) oGROUP ame--11.统计每个学生的选课门数,并按选课门数的递增顺序显示结果。SELECT student.Sname 学生姓名,student.Sno 学生学号,COUNT(SC.Sno) 选课门数FROM studentinner join SC ON SC.Sno=student.SnoGROUP BY student.Sname,student.SnoORDER BY COUNT(SC.Sno) ASC--12.统计选修课的学生总数和考试的平均成绩。SELECT COUNT(DISTINCT(Sno)) AS 学生总数,AVG(Grade) AS 平均成绩FROM SC
--//用DISTINCT消除重复的行--13.查询选课门数超过2门的学生的平均成绩和选课门数。SELECT Student.Sname,AVG(Sc.Grade) 平均成绩,COUNT(SC.Sno) 选课门数FROM SC--内联接join或inner join,内联系是比较运算符，只返回符合条件的行JOIN Student ON (SC.Sno = Student.Sno)JOIN Course ON (SC.Cno = o)GROUP BY Student.SnameHAVING COUNT(o) &2--分组条件--14.列出总成绩超过200分的学生,要求列出学号、总成绩。SELECT Sno 学号,SUM(Grade) 总成绩FROM SCGROUP BY SnoHAVING SUM(Grade)&200--15.查询pubs数据库的titles表中每类图书的平均价格超过12.0元的书的类型(Type)、--平均价格和最高价格。USE pubsGOSELECT Type AS 书的类型,AVG(price) AS 平均价格,MAX(price) AS 最高价格FROM titlesGROUP BY TypeHAVING AVG(price)&12.0--16.查询pubs数据库的titles表中每类图书中图书的数目超过3本的图书的总价格。USE pubsGOSELECT 图书类型=Type,图书的数目=count(Type),图书的总价格=SUM(price)FROM titlesGROUP BY TypeHAVING count(Type)&3--17.查询选修了c02号课程的学生的姓名和所在系。SELECT Sname 学生姓名,Sdept 所在系,SC.Cno AS 选修课程FROM studentinner join SC ON student.Sno=SC.Snoo='C02'--18.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果。SELECT Sname 学生姓名,SC.Cno 课程号,SC.Grade 成绩FROM studentinner join SC ON student.Sno=SC.Sno--内连接表SC查询WHERE SC.Grade&80ORDER BY SC.Grade DESC--19.查询计算机系男生修了"数据库基础"的学生的姓名、性别、成绩。SELECT Sname 姓名,Ssex 性别,SC.Grade 成绩FROM studentinner join SC ON Cno IN(SELECT Cno FROM course WHERE Cname='数据库基础') --显示成绩的条件AND student.Sno=SC.Sno --显示成绩的学生的学号WHERE Sdept='计算机系' AND Ssex='男'--20.查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。--这题使用到了表的自连接，所以需要给表取两个别名，如A和BSELECT A.Sname 年龄相同的学生的姓名,A.Sage 年龄FROM student Ainner join student B ON A.Sage IN(SELECT Sage FROM student WHERE A.Sage=B.Sage AND A.Sname!=B.Sname)GROUP BY A.Sname,A.SageORDER BY A.Sage--21.查询哪些课程没有人选,要求列出课程号和课程名。SELECT Cno AS 课程号,Cname AS 课程名FROM courseWHERE Cno NOT IN(SELECT o FROM SC)--22.查询有考试成绩的所有学生的姓名、修课名称及考试成绩--要求将查询结果放在一张新的永久表(假设新表名为new-sc)中。SELECT student.Sname AS 有考试成绩的学生的姓名,ame AS 修课名称,SC.Grade AS 考试成绩INTO [new_sc] --将查询结果放入新表new_sc中FROM student,course,SCWHERE SC.Grade IS NOT NULL AND student.Sno=SC.Sno o--23.分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,--并要求将这两个查询结果合并成一个结果集,--并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。--//此题用到了并union查询SELECT Sdept 系名,Sname 姓名,Ssex 性别,ame 修课名称,SC.Grade 修课成绩FROM studentinner join SC
ON student.Sno=SC.Snoinner join course oWHERE Sdept='信息系'UNIONSELECT Sdept 系名,Sname 姓名,Ssex 性别,ame 修课名称,SC.Grade 修课成绩FROM studentinner join SC
ON student.Sno=SC.Snoinner join course oWHERE Sdept='计算机系'或select sdept, sname,ssex,cname,grade from student,sc,coursewhere student.sno=sc.sno o and (sdept='信息系' or sdept='计算机系')--24.用子查询实现如下查询:--(1)
查询选修了C01号课程的学生的姓名和所在系。SELECT Sname AS 选修了C01号课程的学生的姓名,Sdept AS 所在系FROM studentWHERE Sno IN(SELECT Sno FROM SC WHERE Cno='C01')--(2)
查询数学系成绩80分以上的学生的学号、姓名。SELECT Sno AS 数学系成绩80分以上的学生的学号,Sname AS 姓名FROM studentWHERE Sno IN(SELECT Sno FROM SC WHERE Grade&80)AND
Sno IN(SELECT Sno FROM student WHERE Sdept='数学系')--(3)
查询计算机系学生所选的课程名.SELECT Cname AS 计算机系学生所选的课程名FROM courseWHERE<o IN(SELECT DISTINCT Cno FROM SC WHERE SC.Sno IN(SELECT Sno FROM student WHERE Sdept='计算机系'))--25.将计算机系成绩高于80分的学生的修课情况插入到另一张表中,分两种情况实现：//////////////?????/--(1)
在插入数据过程中建表。--使用SELECT INTO插入数据的方法，是在插入数据的过程中建立新表SELECT student.Sname AS 学生名字,ame AS 选修课程,SC.Cno AS 课程编号INTO [SC_Info1]--将计算机系成绩高于80分的学生的修课情况插入到表SC_Info1中FROM student,course,SCWHERE student.Sdept='计算机系' AND SC.Grade&80 o AND student.Sno=SC.Sno--(2)
先建一个新表,然后再插入数据。--创建表SC_Info2CREATE TABLE SC_Info2(Sname char(7),Cname char(20),Cno
char(10))--往表SC_info2插入查询得到的结果INSERT SC_Info2SELECT student.Sname AS 学生名字,ame AS 选修课程,SC.Cno AS 课程编号FROM student,course,SCWHERE student.Sdept='计算机系' AND SC.Grade&80 o AND student.Sno=SC.Sno--26.删除修课成绩小于50分的学生的修课记录DELETE SC WHERE Grade&50 OR Grade IS NULL--27.将所有选修了"c01"课程的学生的成绩加10分。UPDATE SCSET Grade=Grade+10WHERE Cno='C01'首先来看自然连接的定义：自然连接：是一种特殊的等值连接，它要求两个关系进行比较的分量必须是相同的属性组，并且在结果集中将重复属性列去掉。一个简单的例子，将下列关系R和S进行自然连接：R：A
g首先要对两个关系中相同属性组的分量进行比较，即比较R.A，R.C和S.A,S.C。 显然在R中只有第一行和第二行满足条件，因此进行连接得到结果：A B C Da b c db a d g再来看一道2008年软件设计师考试的题目：若关系R、S如下所示，则R与S自然连接后的属性列数和元组个数分别为（）R：A B C Da b c da c d ca d g fa b g fS:C Dc dg f分析，首先进行比较，容易得出R中有三个元组（即三行，1、3、4行）符合条件，所以元组个数为3，再然后去掉重复的列，显然仍然剩下A、B、C、D四个列。面试题，有三张表，学生表S，课程表C，学生课程表SC，学生可以选修多门课程，一门课程可能被多个学生选修，通过SC表关联。（1）写出建表以及插入语句；（2）写出SQL语句，查询选修了所有选修课程的学生；（3）写出SQL语句，查询选修了至少2门
时间： 01:00:18
&&&& 阅读：1985
&&&& 评论：
&&&& 收藏：0
标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&
1 CREATE TABLE student(
2 id INT PRIMARY KEY AUTO_INCREMENT,
3 stuname VARCHAR(20) NOT NULL
7 CREATE TABLE course(
8 id INT PRIMARY KEY AUTO_INCREMENT,
9 couname VARCHAR(20) NOT NULL
12 CREATE TABLE stucou
13 (sid INT REFERENCES student(id),
14 cid INT REFERENCES course(id)
18 INSERT INTO student VALUES(1,‘zhangsan‘);
19 INSERT INTO student VALUES(2,‘lsii‘);
20 INSERT INTO student VALUES(3,‘wangwu‘);
21 INSERT INTO student VALUES(4,‘zhaoliu‘);
23 INSERT INTO course VALUES(1,‘Java‘);
24 INSERT INTO course VALUES(2,‘C++‘);
25 INSERT INTO course VALUES(3,‘Chinese‘);
26 INSERT INTO course VALUES(4,‘English‘);
28 INSERT INTO stucou VALUES(1,1);
29 INSERT INTO stucou VALUES(1,2);
30 INSERT INTO stucou VALUES(1,3);
31 INSERT INTO stucou VALUES(1,4);
32 INSERT INTO stucou VALUES(2,1);
33 INSERT INTO stucou VALUES(2,2);
34 INSERT INTO stucou VALUES(2,3);
35 INSERT INTO stucou VALUES(3,1);
36 INSERT INTO stucou VALUES(3,2);
37 INSERT INTO stucou VALUES(4,3);
38 INSERT INTO stucou VALUES(4,4);
39 -- 查询出选所有课程的学生--
40 SELECT student.id,student.stuname
41 FROM student
42 WHERE student.id IN(
43 SELECT sid
44 FROM stucou
45 GROUP BY sid
46 HAVING COUNT(*)=
47 (SELECT COUNT(*)
48 FROM course)
50 SELECT COUNT(*)
51 FROM course
53 -- 查询出选课数大于2的学生--
55 SELECT student.id,student.`stuname`
56 FROM student
57 WHERE student.id IN(
58 SELECT sid
59 FROM stucou
60 GROUP BY sid
61 HAVING COUNT(*)&2
&标签：&&&&&&&&&&&&&&&&&&&&&&&&&&&原文：/mr-guan/p/5402726.html
教程昨日排行
&&国之画&&&& &&&&&&
&& &&&&&&&&&&&&&&
鲁ICP备号-4
打开技术之扣，分享程序人生！