(一）查询
1、查询秋季学期有一门以上课程获90分以上成绩的学生名。
SELECT SNAME
FROM STUDENT
WHERE SNO IN (SELECT SNO
FROM SC
WHERE GRADE >=90.0 AND CNO IN (SELECT CNO
FROM COURSE
WHERE SEMESTER=‘秋’;););
2、查询只有一人选修的课程号。
方法一：SELECT CNO
FROM SC AS SCX
WHERE CNO NOT IN ( SELECT CNO
FROM SC
WHERE SNO=SCX.SNO );
方法二：SELECT CNO
FROM SC AS SCX
GROUP BY CNO
HAVING COUNT (DISTINCT SNO)=1;
3、查询选修CS-110课程的学生名。
SELECT SNAME
FROM STUDENT
WHERE EXISTS (SELECT *
FROM SC
WHERE SNO=STUDENT.SNO AND CNO=‘CS-110’;);
4、查询缺成绩的学生名及课程号。
SELECT SNAME
CNO FROM STUDENT,SC
WHERE STUDENT.SNO=SC.SNO AND GRADE IS NULL;
5、检索男学生所学课程的任课教师的工号和姓名
方法一：SELECT T#,TNAME
FROM T
WHERE T# IN (SELECT T#
FROM C
WHERE C# IN(SELECT C#
FROM SC
WHERE S# IN (SELECT S#
FROM S
WHERE SEX=’M’;);););
方法二：
SELECT DISTINCT T.T#,TNAME
FROM T,C,S,SC
WHERE S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T# AND SEX=’M’;
6、检索WANG同学不学的课程的课程号。
方法一：SELECT C#
FROM C
WHERE NOT EXISTS
(SELECT *
FROM S, SC
WHERE S.S#=SC.S# AND SC.C#=C.C# AND SNAME=’WANG’);
方法二、SELECT C#
FROM C
WHERE C# NOT IN (SELECT C#
FROM S, SC
WHERE S.S#=SC.S# AND SNAME=’WANG’);
7、检索全部学生都选修的课程的课程号与课程名。
SELECT C#, CNAME
FROM C
WHERE NOT EXISTS(SELECT *
FROM S
WHERE NOT EXISTS(SELECT *
FROM SC
WHERE S#=S.S# AND C#=C.C#));
(二）创建和插入和删除和更改
1、生成一个女生成绩临时表FGRADE，表中包括SNAME， CNO和GRADE三个属性，再插入有关女生的数据。
CREATE TABLE FGRADE (SNAME VARCHAR(8) NOT NULL,
CNO CHAR(6) NOT NULL,
GRADE DEC(4,1) DEFAULT NULL);
2、从基表SC中删去C4号课程成绩<该课程平均成绩的元组
DELETE FROM SC
WHERE C# =‘C4’ AND GRADE<(SELECT AVG(GRADE)
FROM SC
WHERE C#=‘C4’ ) ;
3、把课程名为MATHS的课程从基表SC中删去
DELETE FROM SC
WHERE C# IN (SELECT C#
FROM C
WHERE CNAME=‘MATHS’ ) ；
4、在SC,STUDENT中删去SNO以‘91‘开头的记录
DELETE FROM STUDENT
WHERE SNO LIKE ‘91%’；//这里仅需要删除SC中，在SC中对Foreign 的说明是 ON DELETE CASCADE
5、将CS-110号课程的元组改为（CS-110，DB，3，春）
UPDATE COURSE
SET ROW=（’CS-110’，’DB’，3，’春’）
WHERE CNO=‘CS-110’;
6、把女生的成绩提高10%
UPDATE SC
SET GRADE=GRADE * 1.1
WHERE S# IN (SELECT S#
FROM S
WHERE SEX=‘F’ ) ;
Leave Your Comment Here