SQL_学生-课程数据库(上)

 

第一部分

单表查询

例一：查询全体学生的学号与姓名

SELECT Sno,Sname

FROM Student;

例二：查询全体学生的姓名、学号、所在系

SELECT Sname,Sno,Sdept

FROM Student;

例三：查询全体学生的详细记录

SELECT *

FROM Student;

等价于：

SELECT *

FROM Student;

例四：查询全体学生的姓名及其出生年份

SELECT Sname,2009-Sage

FROM Student;

例五：查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示所有的系名

SELECT Sname,'Year of Birth:',2004-Sage,LOWER(Sdept)

FROM Student;

指定列别名如下方法：

SELECT Sname NAME,'Year of Birth:' BIRTH,2009-Sage BIRTHDAY,

LOWER(Sdept) DEPARTMENT

FROM Student;

例六：查询选修了课程的学生的学号

SELECT Sno //等价于SELECT ALL Sno

FROM SC;

去掉重复行方法：

SELECT DISTINCT Sno

FROM SC;

例七：查询计算机科学系全体学生的名单

SELECT Sname

FROM Student

WHERE Sdept='CS';

例八：查询所有年龄在20岁以下的学生的姓名及其年龄

SELECT Sname,Sage

FROM Student

WHERE Sage<20;

例九：查询考试成绩有不及格的学生的学号

SELECT DISTINCT Sno

FROM SC

WHERE Grade<60;

例十：查询年龄在20-23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄

SELECT Sname,Sdept,Sage

FROM Student

WHERE Sage BETWEEN 20 AND 23;

例十一：查询年龄不在20-23岁之间的学生的姓名、系别、和年龄

SELECT Sname,Sdept,Sage

FROM Student

WHERE Sage NOT BETWEEN 20 AND 23;

例十二：查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别

SELECT Sname,Ssex

FROM Student

WHERE Sdept IN('CS','MA','IS');

等价于：

SELECT Sname,Ssex

FROM Student

WHERE Sdept='CS'OR Sdept='MA' OR Sdept='IS';

例十三：查询既不是计算机科学系、数学系，也不是信息系的姓名和性别

SELECT Sname,Ssex

FROM Student

WHERE Sdept NOT IN('IS','MA','CS');

例十四：查询学号为200215121的学生的详细情况

SELECT *

FROM Student

WHERE Sno LIKE '200215126 ';//输入数字6后空一格后输入单引号，否则查询不显示

等价于：

SELECT *

FROM Student

WHERE Sno='200215121';

例十五：查询所有姓刘的学生的姓名、学号和性别

SELECT Sname,Sno,Ssex

FROM Student

WHERE Sname LIKE '王%';

例十六：查询姓”欧阳”且全名为3个汉字的学生的姓名

SELECT Sname

FROM Student

WHERE Sname LIKE '欧阳__%';

例十七：查询名字中第2个字为“阳”字的学生的姓名和学号

SELECT Sname,Sno

FROM Student

WHERE Sname LIKE '_阳%';

例十八：查询所有不姓刘的学生的姓名

SELECT Sname,Sno,Ssex

FROM Student

WHERE Sname NOT LIKE '刘%';

例十九：查询 DB_Design课程的课程号和学分

SELECT Cno,Ccredit

FROM Course

WHERE Cname LIKE 'DB/_Design%' ESCAPE'/';

例二十：查询以“DB_”开头，且倒数第三个字符为i的课程的详细情况

SELECT *

FROM Course

WHERE Cname LIKE 'DB/_%i__%'ESCAPE'/';

例二十一：查询缺少成绩的学生的学号和相应的课程号

SELECT Sno,Cno

FROM SC

WHERE Grade IS NULL;

例二十二：查询所有有成绩的学生学号和课程号

SELECT DISTINCT Sno,Cno

FROM SC

WHERE Grade IS NOT NULL;

例二十三：查询计算机科学系年龄在20岁以下的学生的姓名

SELECT Sname

FROM Student

WHERE Sdept='CS' AND Sage<20;

例二十四：查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

SELECT Sno,Grade

FROM SC

WHERE Cno='3'

ORDER BY Grade DESC

例二十五：查询全体学生的情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列

SELECT *

FROM Student

ORDER BY Sdept,Sage DESC;

例二十六：查询学生的总人数

SELECT COUNT(*)

FROM Student;

例二十七：查询选修了课程的学生人数

SELECT COUNT(DISTINCT Sno)

FROM SC

例二十八：计算1号课程的学生平均成绩

SELECT AVG(Grade)

FROM SC

WHERE Cno='1';

例二十九：查询选修1号课程的学生最高分数

SELECT MAX(Grade)

FROM SC

WHERE Cno='1';

例三十：查询学生200215012选修课程的总学分数

SELECT SUM(Ccredit)

FROM SC,Course

WHERE Sno='200215122'AND SC.Cno=Course.Cno;

例33:查询每一个学生及其选修课程的情况

SELECT Student.*,SC.*

FROM Student,SC

WHERE Student.Sno=SC.Sno;

例34:针对33用自然连接完成

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROM Student,SC

WHERE Student.Sno=SC.Sno;

例32：查询选修了3门以上课程的学生的学号

SELECT Sno

FROM SC

GROUP BY Sno

HAVING COUNT(*) >3;

例31：求各个课程号及相应的选课人数

SELECT Cno,COUNT(Sno) AS 'COUNT(Sno)'

FROM SC

GROUP BY Cno;

例35查询每一门课的间接先修课（即先修课的先修课）

SELECT FIRST.Cno,SECOND.Cpno

FROM Course FIRST,Course SECOND

WHERE FIRST.Cpno=SECOND.Cno;

?例36：外连接

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

From Student LEFT OUT JOIN SC ON(Student.Sno=SC.Sno);

也可以使用USING来去掉结果中地重复值

FROM Student LEFT OUT JOIN SC USING(Sno);

例37:查询选修了2号课程且成绩在90分以上地所有学生

SELECT Student.Sno,Sname

FROM Student SC

WHERE Student.Sno=SC.Sno AND

SC.Cno='2' AND SC.Grade>90;

例38：查询每个学生地学号、姓名、选修课地课程名及成绩

SELECT Student.Sno,Sname,Cname,Grade

FROM Student,SC,Course

WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno

嵌套查询：

SELECT Sname

FROM Student

WHERE Sno IN

(SELECT Sno

FROM SC

WHERE Cno='2');

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept IN

(SELECT Sdept

FROM Student

WHERE Sname='刘晨');

例40：查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno,Sname

FROM Student

WHERE Sno IN

(SELECT Sno

FROM SC

WHERE Cno IN

(SELECT Cno

FROM Course

WHERE Cname='信息系统'

)

)

带有比较运算符的子查询

SELECT Sno,Sname,Sdept

FROM Student

WHERE Sdept=

(SELECT Sdept

FROM Student

WHERE Sname='刘晨')

 

例41：找出每一个学生超过他选修课程平均成绩的课程号

SELECT Sno,Cno

FROM SC x

WHERE Grade >=(SELECT AVG(Grade)

FROM SC y

WHERE y.Sno=x.Sno);

SELECT AVG(Grade)

FROM SC y

WHERE y.Sno='200215121';

SELECT Sno,Cno

FROM SC x

WHERE Grade>=80;

例42：查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄

SELECT Sname,Sage

FROM Student

WHERE Sage<ANY(SELECT Sage

FROM Student

WHERE Sdept='CS')

AND Sdept<>'CS'

SELECT Sname,Sage

FROM Student

WHERE Sage<

(SELECT MAX(Sage)

FROM Student

WHERE Sdept='CS')

AND Sdept<>'CS';

例43：查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄

SELECT Sname,Sage

FROM Student

WHERE Sage<ALL

(SELECT Sage

FROM Student

WHERE Sdept='CS')

AND Sdept<>'CS';

SELECT Sname,Sage

FROM Student

WHERE Sage<

(SELECT MIN(Sage)

FROM Student

WHERE Sdept='CS')

AND Sdept<>'CS';

例44：查询所有选修了1号课程的学生的姓名

SELECT Sname

FROM Student

WHERE EXISTS

(SELECT *

FROM SC

WHERE Sno=Student.Sno AND Cno='1');

例45：查询没有选修1号课程的学生的姓名

SELECT Sname

FROM Student

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE Sno=Student.Sno AND Cno='1');

SELECT Sno,Sname,Sdept

FROM Student S1

WHERE EXISTS

(SELECT *

FROM Student S2

WHERE S2.Sdept=S1.Sdept AND S2.Sname='刘晨');

|例39用EXISTS实现查询代码：

SELECT Sno,Sname,Sdept

FROM Student S1

WHERE EXISTS

(SELECT *

FROM Student S2

WHERE S2.Sdept=S1.Sdept AND

S2.Sname='刘晨');

？例46：查询选修了全部课程的学生的姓名

SELECT Sname

FROM Student

WHERE NOT EXISTS

(SELECT *

FROM Course

WHERE NOT EXISTS

(SELECT *

WHERE Sno=Student.Sno

AND Cno=Course.Cno));

？例47查询至少选修了学生200215122选修的全部课程的学生号码

SELECT DISTINCT Sno

FROM SC SCX

WHERE NOT EXISTS

(SELECT *

FROM SC SCY

WHERE SCY.Sno='200215122' AND

NOT EXISTS

(SELECT *

FROM SC SCZ

WHERE SCZ.Sno=SCX.Sno AND

SCZ.Cno=SCY.Cno));