数据库查询1

CREATE TABLE Student

(Sno char(9) not null unique,

 Sname char(20) unique,

 Ssexchar(2),

 Sagesmallint 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,

 Cnochar(4) not null,

 Grade int

);

 

ALTER TABLE SC Alter column Grade float;

ALTER TABLE Student ADD Scome DATETIME;

ALTER TABLE Student drop column Scome;

ALTER TABLE Teacher drop column Email;

ALTER TABLE Student drop UQ__Student__7D78A4E7;

 

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);

 

CREATE NONCLUSTERED INDEX Stusage ON student(SageDESC);

CREATE CLUSTERED INDEX CLU_Stusno ONstudent(Sno);

CREATE CLUSTERED INDEX CLU_Stusname ONstudent(Sname);

--无法对表 'student' 创建多个聚集索引。请在创建新聚集索引前删除现有的聚集索引'CLU_Stusno'。--

CREATE UNIQUE INDEX ASC_CC ON Course(Ccredit);

CREATE UNIQUE INDEX UN_SC ON SC(Sno ASC,CnoDESC);

 

DROP INDEX Student.CLU_Stusno;

DROP INDEX Sc.UN_SC;

DROP TABLE Teacher;

 

--1、简单查询

-- (1)      查询全体学生的姓名、学号、所在系。

select Sname,Sno,Sdept from Student;

-- (2)      查询全体学生的详细记录。

select * from Student;

-- (3)      查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名，并使用列别名改变查询结果的列标题。

selectSname NAME,'Year of Birth:' BIRTH ,2012-Sage BIRTHDAY,LOWER(Sdept)DEPARTMENTfrom Student;

-- (4)      查询选修了课程的学生学号。

select Sno from SC;

-- (5)      查询所有年龄在20岁以下的学生姓名及其年龄。

select Sname,Sage from Student whereSage<20;

-- (6)      查询年龄不在20~23岁之间的学生姓名、系别和年龄。

select Sname,Sdept,Sage from Student WhereSage not between 20 and 23;

-- (7)      查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

select Sname,Ssex from Student where Sdeptnot in ('CS','MA','IS');

-- (8)      查询学号为20080711的学生的详细情况。（具体的学号值根据表中数据确定）

select * from Student where Sno='20080711';

-- (9)      查询姓“刘”且全名为三个汉字的学生姓名。

select Sname from Student where Sname like'刘__';

-- (10)查询名字中第3个字为“阳”字的学生的姓名和学号。

select Sname,Sno from Student where Snamelike '__阳';

-- (11)查询所有不姓刘的学生姓名。

select Sname from Student where Sname notlike '刘%';

-- (12)查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。

select * from Course where Cnamelike'DB\_%i__'escape'\';

-- (13)查所有有成绩的学生学号和课程号。

select Sno,Cno from SC where Grade is notnull;

-- (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,Sagedesc;

-- (17)查询年龄最大的前三个学生的姓名。

select top 3 Sname from Student order bySage 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 (Sno) from SC group byCno;

-- (23)查询选修了3门以上课程的学生学号。

select Sno from SC group by Sno havingcount(*)>3;

-- (24)查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。

select Sno,count(*) from SC whereGrade>=90 group by Sno having count(*)>=3;

-- (25)查询学生20060711选修课程的总学分。

select sum(Ccredit) from SC,Course whereSno='20060711' and SC.Cno=Course.Cno;

-- (26)查询每个学生选修课程的总学分。

select sum(Ccredit) from SC,Course whereSC.Cno=Course.Cno group by Sno;

 

--2、连接查询

-- (1)查询每一门课的间接先修课（即先修课的先修课）

select first.Cno,second.Cpno from Coursefirst,course second where first.Cpno=second.Cno;

-- (2)查询每个学生及其选修课程的情况包括没有选修课程的学生（用外连接）

selectStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student left outer join SCon(Student.Sno=SC.Sno);

-- (3)查询选修2号课程且成绩在90分以上的所有学生的学号、姓名

select Student.Sno,Sname from Student,SCwhere Student.Sno=SC.Sno and SC.Cno='2' and SC.Grade>90;

-- (4)查询每个学生的学号、姓名、选修的课程名及成绩。

select Student.Sno,Sname,Cname,Grade fromStudent,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno;

--3、嵌套查询

-- (5)查询与“刘晨”在同一个系学习的学生（分别用嵌套查询和连接查询）

select Sno,Sname,Sdept from Student whereSdept in (select Sdept from Student where Sname='刘晨');

-- (6)查询选修了课程名为“信息系统”的学生学号和姓名

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 whereSage<ANY (select Sage from Student where Sdept='CS') and Sdept<>'CS';

-- (8)查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。分别用ALL谓词和集函数

select Sname,Sage from Student whereSage<ALL(select Sage from Student where Sdept='CS') and Sdept<>'CS';

-- (9)查询所有选修了1号课程的学生姓名。（分别用嵌套查询和连接查询）

select Sname from Student where  exists (select * from SC whereSno=Student.Sno and Cno='1');

-- (10)查询没有选修1号课程的学生姓名。

select Sname from Student where not exists(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.Snoand Cno=Course.Cno));

-- (12)查询至少选修了学生95002选修的全部课程的学生号码。

select distinct Sno from SC SCX where notexists (select * from SC SCY where SCY.Sno='95002' and not exists (select *from SC SCZ where SCZ.Sno=SCX.Sno and SCZ.Cno=SCY.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 Sdept='CS'intersect select * from student where Sage<=19;

-- (15)查询既选修了课程1又选修了课程2的学生的信息。

select * from SC where Cno='1' intersectselect Sno from SC where Cno='2';

-- (16)查询计算机科学系的学生与年龄不大于19岁的学生的差集。

select * from student where Sdept='CS'ecxept select * from student where Sage<=19;

--5、综合查询

--1、查询所有学生都选修的课程的课程号和课程名

select Cno,Cname from course where notexists(select * from student where not exists(select * from sc wherecno=course.cno and sno=student.sno));

--2、查询与课程名“数据库”先行课相同的课程号和课程名

select Cno,Cname from course where Cpno in(select Cpno from course where Cname like '数据库');

--3、查询成绩在85分以上的学生选课情况，要求显示学生姓名、课程名及成绩

select sname,cname,grade from student,course,scwhere student.sno=sc.sno and course.cno=sc.cno and grade>85;

--4、查询总成绩大于500、总学分大于30的学生学号、总成绩和总学分

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)>500and sum(ccredit)>30;

--5、查询每门课程及其被选修的情况

select course.cno,course.cname,sc.gradefrom course full join sc on (sc.cno=course.cno);

--6、查询至少选修了“刘晨”所选全部课程的学生学号和姓名

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

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

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

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

select sname,student.sno fromcourse,student,sc where

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

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

--8、查询只选修了“数据库”这门课程的学生学号、姓名和成绩

select student.sno,sname,grade fromstudent,sc where student.sno=sc.sno and not exists

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