【数据库系统概论】实验二 SQL数据查询

一、实验目的

1．掌握SQL Server数据库系统基本操作
2．掌握SQL Server数据库系统数据查询使用方法

二、实验内容

利用实验一建立的数据库表，用代码完成以下操作：

1. 在学生表中增加一个属性”phone”，类型为字符型，长度为11位。
2. 在学生表中对属性姓名，年龄建立索引，姓名为升序，年龄为降序。
3. 查询考试不及格的学生和没有参加考试的学生名单。
4. 查询选修了“C语言”课程的学生名单。
5. 统计每门课程的平均成绩，按照从高到低排序。
6. 找出平均成绩最高的学生。
7. 找出选修了2门课以上的学生。
8. 查询每位同学高于自己平均分的课程。
9. 查询全部同学都选修了的课程。
10. 查询至少选修了学号为“”同学选修全部课程的学生。

数据表：

DROP TABLE IF EXISTS SC;
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Course;
 
CREATE TABLE Student
 (	
 Sno CHAR(9) PRIMARY KEY,        /* 列级完整性约束条件,Sno是主码*/                  
 Sname CHAR(20) UNIQUE,          /* Sname取唯一值*/
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20)
 ); 
 
CREATE TABLE  Course
 (	
 Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40),            
 Cpno CHAR(4),               	                      
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES  Course(Cno) /* 表级完整性约束条件， Cpno是外码，被参照表是自身*/
 ); 
 
CREATE TABLE  SC
 (
 Sno CHAR(9), 
 Cno CHAR(4),  
 Grade SMALLINT,
 PRIMARY KEY (Sno,Cno),                      /* 主码由两个属性构成，必须作为表级完整性进行定义*/
 FOREIGN KEY (Sno) REFERENCES Student(Sno),  /* 表级完整性约束条件，Sno是外码，被参照表是Student*/
 FOREIGN KEY (Cno)REFERENCES Course(Cno)     /* 表级完整性约束条件，Cno是外码，被参照表是Course*/
 ); 
 
 
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215121','李勇','男','CS',20);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215122','刘晨','女','CS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215123','王敏','女','MA',18);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215125','张立','男','IS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',20);
 

 
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('1','数据库',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('2','数学',NULL,2);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('3','信息系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('4','操作系统',NULL,3);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('5','数据结构',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('6','数据处理',NULL,2);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('7','Pascal语言',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('8','C语言',NULL,4);

UPDATE Course SET Cpno = '5' WHERE Cno = '1' 
UPDATE Course SET Cpno = '1' WHERE Cno = '3' 
UPDATE Course SET Cpno = '6' WHERE Cno = '4' 
UPDATE Course SET Cpno = '7' WHERE Cno = '5' 
UPDATE Course SET Cpno = '6' WHERE Cno = '7' 

 
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','8',50);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','1',50);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215123 ','4',59);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215123 ','1',59);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215125 ','1',50);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215128 ','1',60);
 
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM SC;

三、问题和要求

1.写出你操作并正确执行的代码及结果。

1.在学生表中增加一个属性”phone”，类型为字符型，长度为11位。

ALTER TABLE Student ADD phone CHAR(11);

2.在学生表中对属性姓名，年龄建立索引，姓名为升序，年龄为降序。

CREATE INDEX INDEXS ON Student(Sname ASC,Sage DESC);

3.查询考试不及格的学生和没有参加考试的学生名单。

SELECT DISTINCT S.*,Grade FROM SC,Student S WHERE S.Sno=SC.Sno AND Grade<60;

4.查询选修了“C语言”课程的学生名单。

SELECT S.*,C.Cname FROM SC,Student S,Course C WHERE S.Sno=SC.Sno AND C.Cno=SC.Cno AND Cname='C语言';

5.统计每门课程的平均成绩，按照从高到低排序。

SELECT Cname,AVG(Grade) FROM SC,Course C WHERE C.Cno=SC.Cno GROUP BY Cname ORDER BY AVG(Grade) DESC;

6.找出平均成绩最高的学生。

SELECT TOP 1 Sno 学号, AVG(Grade)平均值 FROM SC GROUP BY Sno

7.找出选修了2门课以上的学生。

SELECT Sno 学号 FROM SC GROUP BY Sno HAVING COUNT(Cno) >= 2

8.查询每位同学高于自己平均分的课程。

SELECT * FROM SC S1 WHERE Grade>=(SELECT AVG(Grade) FROM SC S2 WHERE S1.Sno=S2.Sno);

9.查询全部同学都选修了的课程。

SELECT Cno, Cname FROM Course C WHERE NOT EXISTS
(SELECT * FROM Student S WHERE NOT EXISTS
(SELECT * FROM SC WHERE SC.Cno = C.Cno and SC.Sno = S.Sno));

10.查询至少选修了学号为 “ 201215122 ” 同学选修全部课程的学生。

SELECT DISTINCT Sno 学号 FROM SC scx WHERE NOT EXISTS(SELECT * FROM SC scy WHERE scy.Sno = '201215122' AND NOT EXISTS(SELECT * FROM SC scz WHERE scz.Sno = scx.Sno AND scz.Cno = scy.Cno));