天津理工大学数据库期末复习——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 o=o
AND Student.Sname='李勇'
AND 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 o=o
AND ame='数据库');
（8）求至少选修了学号为20152201所选修的全部课程的学生学号。

SELECT Student.*,SC.* FROM Student,SC WHERE 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 o=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。