SQL语句习题总练习

SQL语句习题总练习

SQL语句总结详解点这里

接下来为练习：

预建表

------------------------------------------------------------------------
--先建表
CREATE TABLE 学生
(
Sno varchar(20),
Sname varchar(20),
Ssex char(2),
Sage smallint,
Sdept varchar(20)
);
CREATE TABLE 课程
(
Cno varchar(20),
Cname varchar(20),
Ccredit smallint,
);
CREATE TABLE 选课
(
Sno varchar(20),
Cno varchar(20),
Grade smallint
);
------------------------------------------------------------------------

第一大题：采用SQL语句修改表的结构

------------------------------------------------------------------------
--第一大题：采用SQL语句修改表的结构，并要求记录系统的相关提示界面，题目如下：
--题目1：修改学生表的结构
--（1）增加“专业”字段，默认为“计算机科学与应用”
ALTER TABLE 学生
ADD 专业 char(20) DEFAULT '计算机科学与技术'
--（2）增加约束：定义性别只能是”男”或”女”的检查约束
ALTER TABLE 学生
ADD CONSTRAINT CK_Ssex CHECK(Ssex IN ('男','女'))
--（3）为学生表创建主键约束
ALTER TABLE 学生
ALTER COLUMN Sno VARCHAR(20) NOT NULL
ALTER TABLE 学生
ADD CONSTRAINT PK_Sno PRIMARY KEY(Sno)
--（4）删除“姓名（sname）”字段
ALTER TABLE 学生
DROP COLUMN Sname

--题目2：修改课程表的结构
--（1）增加“课程类别”字段，默认为“软件系统类”
ALTER TABLE 课程
ADD 课程类别 char(20) DEFAULT '软件系统类'
--（2）增加约束：定义“Ccredit ”字段取值范围在1到10之间
ALTER TABLE 课程
ADD CONSTRAINT CK_Credit CHECK ( Ccredit >= 1 AND Ccredit <= 10)
--（3）为课程表创建主键约束
ALTER TABLE 课程
ALTER COLUMN Cno VARCHAR(20) NOT NULL
ALTER TABLE 课程
ADD CONSTRAINT PK_Cno PRIMARY KEY(Cno)
--（4）删除“课程名（Cname）”字段
ALTER TABLE 课程
DROP COLUMN Cname

--题目3：修改选课表的结构
--（1）增加“任课教师级别”字段，默认为“讲师以上”
ALTER TABLE 选课
ADD 任课教师级别 VARCHAR(20) DEFAULT '讲师以上'
--（2）增加约束：定义“Grade”字段取值范围在0到100之间
ALTER TABLE 选课
ADD CONSTRAINT CK_Grade CHECK(Grade <=100 AND Grade >=0)
--（3）为选课表创建外键约束
ALTER TABLE 选课
ADD CONSTRAINT FK_Sno FOREIGN KEY(Sno) REFERENCES 学生(Sno)
ALTER TABLE 选课
ADD CONSTRAINT FK_Cno FOREIGN KEY(Cno) REFERENCES 课程(Cno)
--（4）删除“成绩（grade）”字段
ALTER TABLE 选课
DROP CONSTRAINT CK_GRADE
ALTER TABLE 选课
DROP COLUMN Grade 
------------------------------------------------------------------------

第二大题：采用SQL语句实现数据的维护操作

------------------------------------------------------------------------
--第二大题：采用SQL语句实现数据的维护操作
--题目1：学生表数据的操作
--（1）	添加以下数据
/*
Sno	    专业	  Ssex	Sage Sdept
99001	软件工程   男	 20	   CS 
*/
INSERT 
INTO 学生(Sno,专业,Ssex,Sage,Sdept) 
VALUES('99001','软件工程','男','20','Cs')=
--（2）将所有学生的年龄减少1岁
UPDATE 学生
SET Sage = Sage - 1
--（3）将学号为95003的学生删除
DELETE 
FROM 学生
WHERE Sno='95003'

--题目2：课程表数据的操作
--（1）	添加以下数据
--Cno	课程类别	Ccredit
--001	软件系统      4
INSERT 
INTO 课程(Cno,课程类别,Ccredit) 
VALUES('001','软件系统','4')
--（2）将课程号为002的课程学分（Ccredit）减少1分
UPDATE 课程
SET Ccredit = Ccredit - 1
WHERE Cno = '002'
--（3）将课程号为002的课程删除
DELETE
FROM 课程
WHERE Cno = '002'

--题目3：选课表数据的操作
--（1）	添加以下数据
--Sno	Cno	Grade	任课教师级别
--95001	001	87	副教授
INSERT 
INTO 选课(Sno,Cno,Grade,任课教师级别) 
VALUES('95001','001','87','副教授')
--（2）将选了课程号为002的学生成绩加5%
UPDATE 选课
SET Grade = Grade + Grade * 0.05
WHERE Cno = '002'
--（3）将学号为95001的选课信息删除
DELETE 
FROM 选课
WHERE Sno = '95001'
------------------------------------------------------------------------

第三大题：采用SQL语句实现查询统计功能

------------------------------------------------------------------------
--第三大题：采用SQL语句实现查询统计功能
--题目1：查询各学院各门课程的平均成绩和选修人数，输出平均成绩在60以上的课程，选课人数和平均成绩，并按平均成绩排序
SELECT Sdept,Cname,COUNT(*)选课人数,AVG(Grade)平均成绩
FROM 学生,课程,选课 
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno
GROUP BY Sdept,Cname,选课.Cno having AVG(Grade) > 60
ORDER BY 平均成绩 DESC

--题目2：统计各学院学生选课总学分和平均成绩，输出选课总学分在20分以上学生的选课数量和平均成绩，并按平均成绩排序
SELECT Sdept,SUM(Ccredit)选课总学分,AVG(Grade)平均成绩
FROM 学生,课程,选课 
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno 
GROUP BY Sdept HAVING SUM(Ccredit)>20
ORDER BY 平均成绩 DESC

--题目3：统计各门课程各分数段的分布情况。 
SELECT 选课.Cno,COUNT(CASE WHEN 选课.Grade>80 THEN 1 ELSE NULL END)成绩大于80的人数
FROM 学生,课程,选课 
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno 
GROUP BY 选课.Cno
------------------------------------------------------------------------

第四大题：采用SQL语句实现查询功能

------------------------------------------------------------------------
--第四大题：采用SQL语句实现查询功能:
--题目1：创建“物电学院”所有学生的成绩视图，包括：学号，姓名，选修课程名，成绩，并对视图作查询。
CREATE VIEW 物电学院学生成绩
AS 
SELECT 学生.Sno 学号,Sname 姓名,Cname 选修课程名,Grade 成绩
FROM 学生,课程,选课 
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno AND Sdept = '物电学院'
WITH CHECK OPTION;

SELECT *
FROM 物电学院学生成绩

--题目2：创建“学生选课”视图，包括：学号，姓名，选修课程名，成绩，要求没有选修任何课程的学生也在内，并对视图作查询。
CREATE VIEW 学生选课
AS 
SELECT 学生.Sno 学号,Sname 姓名,Cname 选修课程名,Grade 成绩
FROM 学生  
LEFT OUTER JOIN 选课
ON 学生.Sno=选课.Sno  
LEFT OUTER JOIN 课程
ON 选课.Cno=课程.Cno
WITH CHECK OPTION;

SELECT *
FROM 学生选课

--题目3：创建“课程选修”视图，包括课程号、课程名、学号、姓名、成绩，要求没被任何学生选修的课程也在内，并对视图作查询。
CREATE VIEW 课程选修
AS 
SELECT 课程.Cno 课程号,Cname 课程名,学生.Sno 学号,Sname 姓名,Grade 成绩
FROM 课程  
LEFT OUTER JOIN 选课
ON 选课.Cno=课程.Cno
LEFT OUTER JOIN 学生
ON 学生.Sno=选课.Sno  
WITH CHECK OPTION;

SELECT *
FROM 课程选修
------------------------------------------------------------------------

第五大题：采用SQL语句实现存储过程:

------------------------------------------------------------------------
--第五大题：采用SQL语句实现存储过程:
--题目1：设计并调用存储过程，输出学生选修总学分 
CREATE PROC pro1
AS
SELECT 学生.Sno 学生学号,SUM(课程.Ccredit) 总学分
FROM 学生,课程,选课 
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno
GROUP BY 学生.Sno

EXEC pro1

--题目2：设计并调用存储过程，输出课程选修总人数。 
CREATE PROC pro2
AS 
SELECT 课程.Cno 课程号,COUNT(*) 选修人数
FROM 学生,课程,选课 
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno 
GROUP BY 课程.Cno

EXEC pro2

--题目3：设计并调用存储过程，输出课程成绩平均分、最高分和最低分
CREATE PROC pro3
AS 
SELECT 课程.Cno 课程号,AVG(Grade) 成绩平均分 ,MAX(Grade)最高分,MIN(Grade) 最低分
FROM 学生,课程,选课 
WHERE 学生.Sno=选课.Sno AND 选课.Cno=课程.Cno 
GROUP BY 课程.Cno
EXEC pro3
------------------------------------------------------------------------