天津理工大学数据库期末复习——SQL语句汇总

1. 建表：

（1）创建学生表Student:

CREATE TABLE Student

(Sno CHAR(9) PRIMARY KEY,

Sname CHAR(20) UNIQUE,

Ssex CHAR(2),

Ssage SMALLINT,

Sdept CHAR(20));

（2）创建课程表:COURSE

CREATE TABLE COURSE

(Cno CHAR(4) PRIMARY KEY,

Cname CHAR(40) NOT NULL,

Cpno CHAR(4),

Ccredit SMALLINT,

FOREIGN KEY(Cpno) REFERENCES Course(Cno) );

（3）创建学生-选课表SC

CREATE TABLE SC

(Sno CHAR(9),

Cno CHAR(4),

Grade SMALLINT,

PRIMARY KEY(Sno,Cno),

FOREIGN KEY(Cno) REFERENCES COURSE(Cno) );

2．基本SQL数据操作：

（1）查询信息系和计算机系的学生，并按学生所在系和学号排序。

SELECT *

FROM Student

WHERE Sdept='IS' OR Sdept='CS'

ORDER BY Sdept,Sno;

（2）查询学生表中最小的年龄。

SELECT MIN(Ssage)

FROM Student;

（3）查询课程名中包含“设计”的课程名。SELECT Cname

FROM COURSE

WHERE Cname LIKE '%设计%';

（4）查询先行课程为空值的课程号、课程名及学分。SELECT Cno,Cname,Ccredit

FROM COURSE WHERE Cpno is NULL;

（5）查询李勇选修的数据库课程的成绩。SELECT Grade

FROM Student,COURSE,SC

WHERE Student.Sno=SC.Sno

AND https://www.360docs.net/doc/3114609290.html,o=https://www.360docs.net/doc/3114609290.html,o

AND Student.Sname='李勇'

AND https://www.360docs.net/doc/3114609290.html,ame='数据库';

（6）查询平均成绩80分以上的学生的学号。SELECT Sno

FROM SC

GROUP BY Sno

HA VING A VG(Grade)>80;

（7）求计算机系没有选修数据库课程的学生姓名。SELECT Sname

FROM Student

WHERE Sdept='CS'

AND NOT EXISTS

(SELECT *

FROM COURSE,SC

WHERE Student.sno=SC.Sno

AND https://www.360docs.net/doc/3114609290.html,o=https://www.360docs.net/doc/3114609290.html,o

AND https://www.360docs.net/doc/3114609290.html,ame='数据库');

（8）求至少选修了学号为20152201所选修的全部课程的学生学号。

SELECT Student.*,SC.* FROM Student,SC WHERE https://www.360docs.net/doc/3114609290.html,o=

(SELECT Cno

FROM SC

WHERE Sno='20152201'

)

AND SC.Sno=Student.Sno

（9）求各系的系的学生人数的，并将结果按学生人数的降序排序。

SELECT Sdept,COUNT(Sno)

FROM Student

GROUP BY Sdept

ORDER BY COUNT(Sno)DESC;

（10）查询选修了数据库课程并且成绩高于该门课程平均分的学生学号和成绩。

SELECT Sno,Grade FROM SC

WHERE GRADE>( SELECT A VG(Grade) FROM SC WHERE Cno=(

SELECT Cno

FROM COURSE

WHERE Cname='数据库')) AND Cno=(

SELECT Cno

FROM COURSE

WHERE Cname='数据库');

3. 复杂SQL数据操作：

(1) 将学习了数据库课程的学生成绩加5分。UPDA TE SC

SET Grade=Grade+5

WHERE Cno=

(SELECT Cno

FROM COURSE

WHERE Cname='数据库');

(2) 将计算机系学习了2号课程的学生成绩置0。UPDA TE SC

SET Grade=0

WHERE Sno IN

(SELECT Sno

FROM Student

WHERE Sdept='CS'AND Cno='2');

(3) 将李勇的数据库成绩改为85。

UPDA TE SC

SET Grade=85

WHERE Sno IN

(SELECT Sno

FROM Student

WHERE Sname='李勇') AND Cno IN

(SELECT Cno

FROM COURSE

WHERE Cname='数据库');

(4) 将选修了2号课程且成绩为空的选课记录删除。DELETE

FROM SC

WHERE Cno='2'AND GRADE=0;

(5) 从课程表中删除在选课表中没有选课记录的课程记录。

DELETE

FROM COURSE

WHERE Cno NOT IN (SELECT Cno FROM SC);

(6) 删除计算机系学生选修了数据库课程的选课记录。

DELETE

FROM SC

WHERE Cno IN

(SELECT Cno

FROM COURSE

WHERE Cname='数据库') AND Sno IN (SELECT Sno

FROM Student

WHERE Sdept='CS');

(7) 求各系的系名及男女生人数并将结果保存到另一个表中。

CREATE VIEW DEPT(Sdept,Ssex,Num)

AS SELECT Sdept,Ssex,COUNT(Ssex)

FROM Student

GROUP BY Sdept,Ssex;

(8) 将平均成绩80分以上的学生的学号，选学的课程数和平均成绩保存到另一个表中。

CREATE VIEW Grade(Sno,Num,Ave)

AS SELECT Sno,COUNT(Cno),A VG(Grade)

FROM SC

GROUP BY Sno

HA VING A VG(Grade)>=80;

(9) 创建一个视图，查询没有选修课程的学生学号、姓名和所在系，并利用该视图查询所有没有选修课程的学生信息。

CREATE VIEW S(Sno,Sname,Sdept)

AS SELECT Sno,Sname,Sdept

FROM Student

WHERE Sno NOT IN

(SELECT Sno FROM SC);

(10) 创建一个给出学生的姓名、课程名和成绩的视图，并利用该视图查询某个学生学习的课程名和成绩。

CREATE VIEW STD

AS SELECT Sname,Cname,Grade

FROM Student,COURSE,SC

WHERE Student.Sno=SC.Sno

AND https://www.360docs.net/doc/3114609290.html,o=https://www.360docs.net/doc/3114609290.html,o

SELECT Cname,Grade FROM STD

WHERE Sname='李力';

(11) 创建一个视图，求各门课程的课程号、选课人数、平均分、最高分，并利用该视图查询1号课程的选课人数、平均分、最高分。

CREATE VIEW C

AS SELECT Cno,COUNT(Sno) countc,A VG(Grade) avec,MAX(Grade) maxc

FROM SC

GROUP BY Cno;

SELECT countc,avec,maxc FROM C

WHERE Cno='1';

(12) 创建一个视图，求选修了2号课程且成绩高于该门课程平均分的学生学号和成绩。

CREATE VIEW C1(Sno,Grade)

AS SELECT Sno,Grade

FROM SC

WHERE Cno='2'

AND Grade>

(SELECT A VG(Grade)

FROM SC

WHERE Cno='2');

(13) 创建用户user1，将学生表的select权限赋给用户user1，然后以user1登录，检查权限情况。CREATE ROLE USE1

GRANT SELECT ON Student TO USE1;

(14) 收回用户user1的对学生表的select权限，然后以user1登录，检查权限情况。

REVOKE SELECT ON Student

FROM USE1