实验四 SQL语言 SELECT查询操作.doc

实验四 SQL语言 习题一 习题二 USE DATABASE jxgl USE jxgl Create Table Student (Sno CHAR(5) NOT NULL PRIMARY KEY(Sno), Sname VARCHAR(20), Sage SMALLINT CHECK(Sage=15 AND Sage=45), Ssex CHAR(2) DEFAULT 男 CHECK (Ssex=男 OR Ssex=女), Sdept CHAR(2)); Create Table Course (Cno CHAR(2) NOT NULL PRIMARY KEY(Cno), Cname VARCHAR(20), Cpno CHAR(2), Ccredit SMALLINT); Create Table SC (Sno CHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES Student(Sno), Cno CHAR(2) NOT NULL, Grade SMALLINT CHECK((Grade IS NULL)OR (Grade BETWEEN 0 AND 100)), PRIMARY KEY(Sno,Cno), CONSTRAINT C_F FOREIGN KEY(Cno) REFERENCES Course(Cno)); INSERT INTO Student VALUES(98001,钱横,18,男,CS); INSERT INTO Student VALUES(98002,王林,19,女,CS); INSERT INTO Student VALUES(98003,李民,20,男,IS); INSERT INTO Student VALUES(98004,赵三,16,女,MA); INSERT INTO Course VALUES(1,数据库系统,5,4); INSERT INTO Course VALUES(2,数学分析,null,2); INSERT INTO Course VALUES(3,信息系统导论,1,3); INSERT INTO Course VALUES(4,操作系统原理,6,3); INSERT INTO Course VALUES(5,数据结构,7,4); INSERT INTO Course VALUES(6,数据处理基础,null,4); INSERT INTO Course VALUES(7,C语言,6,3); INSERT INTO SC VALUES(98001,1,87); INSERT INTO SC VALUES(98001,2,67); INSERT INTO SC VALUES(98001,3,90); INSERT INTO SC VALUES(98002,2,95); INSERT INTO SC VALUES(98002,3,88); 课程表Course（课程号Cno,课程名Cname,先修课号Cpno,学分Ccredit） 学生表Student（学号Sno,姓名Sname,年龄Sage,性别Ssex,所在系Sdept） 学生选课表SC（学号Sno,课程号Cno,成绩Grade) （1）基于“教学管理”数据库jxgl，使用SQL的查询语句表示下列查询： ①检索年龄大于23岁的男学生的学号和姓名； select Sno,Sname from Student where Sage23 AND Ssex=男; ②检索至少选修一门课程的女学生的姓名； select Sname from Student,SC where Ssex=女 AND Student.Sno=SC.Sno group by Student.Sname having count(*)=1; 或者 Select Sname From Student Where Ssex=女 AND Sno in (select sno from SC group by sno having count(*)=1); ③检索王同学不学的课程的课程号； select Cno from Course where Course.Cno not in (select Cno from SC,Student where SC.Sno=Student.Sno AND Sname LIKE 王%); ④检索至少选修两门课程的学生学号； select DISTINCT Student.Sno from Student,SC WHERE Student.Sno=SC.Sno GROUP BY Student.Sno HAVING COUNT(*)=2; ⑤检索全部学生都