SQL简单习题

CREATE TABLE Student

(Sno char(9) not null unique,

Sname char(20) unique,

Ssex char(2),

Sage smallint check(Sage>16),

Sdept char(20)

);


CREATE TABLE Course

(Cno char(4) primary key,

Cname char(40) unique,

Cpno char(4),

Ccredit smallint,

);

CREATE TABLE Teacher

(

Tno char(9) primary key,

Tname char(20),

Department char(20),

Email char(25),

Salary smallint

);

CREATE TABLE SC

(Sno char(9) not null,

Cno char(4) not null,

Grade int

);

ALTER TABLE SC Alter column Grade float;

ALTER TABLE Student ADD Scome DATETIME;

ALTER TABLE Student drop column Scome;

INSERT INTO Student VALUES ('200215121','李勇','男',20,'CS');

INSERT INTO Student VALUES ('200215122','刘晨','女',19,'CS');

INSERT INTO Student VALUES ('200215123','王敏','女',18,'MA');

INSERT INTO Student VALUES ('200215125','张立','女',19,'IS');

INSERT INTO Course VALUES (1,'数据库',5,4);

INSERT INTO Course(Cno,Cname,Ccredit) VALUES (2,'数学',2);

INSERT INTO Course VALUES (3,'信息系统',1,4);

INSERT INTO Course VALUES (4,'操作系统',6,3);

INSERT INTO Course VALUES (5,'数据结构',7,4);

INSERT INTO Course(Cno,Cname,Ccredit)VALUES (6,'数据处理',2);

INSERT INTO Course VALUES (7,'PASCAL语言',6,4);


INSERT INTO SC VALUES('200215121',1,92);

INSERT INTO SC VALUES('200215121',2,85);

INSERT INTO SC VALUES('200215121',3,88);

INSERT INTO SC VALUES('200215122',2,90);

INSERT INTO SC VALUES('200215122',3,80);

select * from student
select * from sc
select * from course

-- (1)	查询全体学生的姓名、学号、所在系。
select sname,sno,sdept from student 
-- (2)	查询全体学生的详细记录。
select * from student
-- (3)	查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名，并使用列别名改变查询结果的列标题。
-- (4)	查询选修了课程的学生学号。
select sno from sc
-- (5)	查询所有年龄在20岁以下的学生姓名及其年龄。
select sname ,sage from student where sage <20
-- (6)	查询年龄不在20~23岁之间的学生姓名、系别和年龄。
select sname,sdept,sage from student where sage not between 20 and 23
-- (7)	查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
select sname ,ssex from student where sdept not in ('cs','ma','is')
-- (8)	查询学号为200215121的学生的详细情况。（具体的学号值根据表中数据确定）
select * from student where sno = '200215121'
-- (9)	查询姓“刘”且全名为三个汉字的学生姓名。
select sname from student where sname like '刘__'
-- (10)查询名字中第3个字为“阳”字的学生的姓名和学号。
select sname ,sno from student where sname like '__阳'
-- (11)查询所有不姓刘的学生姓名。
select sname from student where sname  not like '刘%'
-- (13)查所有有成绩的学生学号和课程号。
select sno ,cno from sc where grade is not null
-- (14)查询计算机系年龄在20岁以下的学生姓名。
select sname from student where sdept = 'cs' and sage < 20
-- (15)查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
select sno,grade from sc where cno = '3' order by grade desc
-- (16)查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
select * from student order by sdept ,sage desc
-- (17)查询年龄最大的前三个学生的姓名。
select top 3 sname from student order by sage desc
-- (18)查询学生总人数。
select count(*)'总人数' from student
-- (19)查询选修了课程的学生人数。
select count(distinct sno) from sc
-- (20)查询选修2号课程的学生平均成绩。
select avg(grade) from sc where cno='2'
-- (21)查询选修1号课程的学生最高分数。
select max(grade) from sc where cno = '1'
-- (22)求各个课程号及相应的选课人数。
select cno ,count(*) from sc group by cno
-- (23)查询选修了2门以上(含两门)课程的学生学号。
select sno from sc group by sno having count(*)>=2
-- (24)查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。
select sno,count(*) from sc where grade >=90 group by sno having count(*)>=3
-- (25)查询学生200215121选修课程的总学分。
select sum(Ccredit) from sc,course where sno = '200215121' and sc.cno = course.cno
-- (26)查询每个学生选修课程的总学分。
select sum(Ccredit) from sc,course where sc.cno = course.cno group by sno
--2、连接查询
-- (1)查询每一门课的间接先修课（即先修课的先修课）
-- (2)查询每个学生及其程选修课的情况包括没有选修课程的学生（用外连接）
select student.sno,sname,ssex,sage,sdept,cno,grade from student left outer join sc on student.sno = sc.sno
-- (3)查询选修2号课程且成绩在90分以上的所有学生的学号、姓名
select sname , student.sno from student,sc where grade >=90 and cno='2' and student.sno = sc.sno
-- (4)查询每个学生的学号、姓名、选修的课程名及成绩。
select student.sno,sname,cname,grade from student,course ,sc where student.sno = sc.sno and sc.cno = course.cno
--3、嵌套查询
-- (5)查询与“刘晨”在同一个系学习的学生（分别用嵌套查询和连接查询）
select * from student where sdept in(select sdept from student where sname ='刘晨')
-- (6)查询选修了课程名为“信息系统”的学生学号和姓名
--连接查询
select student.sno,sname from student ,sc,course where student.sno = sc.sno and sc.cno = course.cno and cname='信息系统'
--嵌套查询
select sno,sname from student where sno in(select sno from sc where cno in (select cno from course where cname ='信息系统'))
-- (7)查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄
--法一
select sname ,sage from student where sage <(select min(sage) from student where sdept = 'IS') and sdept <> 'IS'
--法二
select Sname,Sage from Student where Sage<ANY (select Sage from Student where Sdept='IS') and Sdept<>'IS'
-- (8)查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。分别用ALL谓词和集函数
select sname,sage from student where sage < ALL (select sage from student where sdept ='IS') and sdept <> 'IS'
-- (9)查询所有选修了1号课程的学生姓名。（分别用嵌套查询和连接查询）
--连接查询
select sname from student ,sc where cno = '1' and student.sno = sc.sno
--嵌套查询
select sname from student where sno in (select sno from sc where cno ='1')
-- (10)查询没有选修1号课程的学生姓名。
select sname from student where not exists (select * from sc where sc.sno = student.sno and cno = '1')
--后面的 (select * from sc where sc.sno = student.sno and cno = '1')也可以写成 (select * from sc where sno = student.sno and cno = '1')
-- (11)查询选修了全部课程的学生姓名。
select Sname from Student where not exists (select * from Course where not exists (select * from SC where Sno=Student.Sno and Cno=Course.Cno))
--4、集合查询
-- (13)查询选修了课程1或者选修了课程2的学生的信息。
SELECT Sno FROM SC WHERE Cno='1' UNION SELECT Sno FROM SC WHERE Cno='2'
-- (14)查询计算机科学系中年龄不大于19岁的学生的信息。
--法一
select * from student where sage <=19 and sdept = 'CS'
--法二
select * from student where Sdept='CS' intersect select * from student where Sage<=19
--5、综合查询
--1、查询所有学生都选修的课程的课程号和课程名
select Cno,Cname from course where not exists(select * from student where not exists(select * from sc where cno=course.cno and sno=student.sno))
--2、查询与课程名“数据库”先行课相同的课程号和课程名
--3、查询成绩在85分以上的学生选课情况，要求显示学生姓名、课程名及成绩
select sname ,cname,grade from student,sc,course where grade>=85 and student.sno = sc.sno and sc.cno = course.cno
--4、查询总成绩大于150、总学分大于8的学生学号、总成绩和总学分
select student.sno,sum(grade),sum(ccredit) from student,course,sc where student.sno=sc.sno and sc.cno=course.cno
group by student.sno having sum(grade)>150 and sum(ccredit)>8;
--6、查询至少选修了“刘晨”所选全部课程的学生学号和姓名

select distinct sname,student.sno from student,sc scx where not exists

(select * from sc scy where student.sno=scx.sno and student.sname='刘晨'and not exists

(select * from sc scz where scz.sno=scx.sno and scz.cno=scy.cno));

--7、查询没有选修“数据库”这门课程的学生学号和姓名

select sname,student.sno from course,student,sc where

student.sno=sc.sno and sc.cno=course.cno and not exists

(select * from course where cname='数据库');

--8、查询只选修了“数据库”这门课程的学生学号、姓名和成绩
select student.sno,sname,grade from student,sc where student.sno=sc.sno and not exists

(select * from sc scx,course where course.cno=scx.cno and course.cname!='数据库');