数据库原理及应用教程第四版实验一到四sql整理
数据库原理及应用教程第四版实验一到四sql整理
为了方便自己日后回顾复习sql语句，把自己写的发布到这里，如有错误望多多指正(部分参考书本例题sql语句)
拒绝直接抄袭！希望你可以动动手自己敲一敲
实验一sql语句
SQL命令创建数据库
CREATE DATEBASE DateBaseTest数据表的创建
CREATE TABLE Table_学生表(
学号 char(10) NOT NULL,
姓名 nvarchar(50) NOT NULL,
性别 nchar(10) NOT NULL,
专业班级 nvarchar(50) NOT NULL,
出生日期 date NOT NULL,
联系电话 numeric(18, 0) NULL
) ON PRIMARY
CREATE TABLE Table_课程表(
课程号 nchar(10) NOT NULL,
课程名 nchar(10) NOT NULL,
学分数 int NOT NULL,
学时数 numeric(18, 1) NOT NULL,
任课教师 nchar(10) NOT NULL
) ON PRIMARY
CREATE TABLE Table_学生作业表(
课程号 nchar(10) NOT NULL,
学号 nchar(10) NOT NULL,
作业1成绩 numeric(4, 1) NOT NULL,
作业2成绩 numeric(4, 1) NULL,
作业3成绩 numeric(4, 1) NOT NULL
) ON PRIMARY向表中插入数据
INSERT Table_学生表
(学号,姓名,性别,专业班级,出生日期,联系电话)
VALUES
('0433','张艳','女','生物04',1986-9-13,NULL),
('0496','李越','男','电子04',1984-2-23,13819208888),
('0529','赵欣','女','会计05',1984-1-27,13502228888),
('0531','张志国','男','生物05',1986-9-10,13312568888),
('0538','于兰兰','女','生物05',1984-2-20,13312008888),
('0591','王丽丽','女','电子05',1984-3-20,13320808888),
('0592','王海强','男','电子05',1986-11-1,NULL)
INSERT INTO Table_课程表
(课程号 ,课程名 ,学分数,学时数,任课教师)
VALUES
('K001','计算机图形学','2','40.0','胡晶晶'),
('K002','计算机应用基础','3','48.0','任泉'),
('K006','数据结构','4','64.0','马跃先'),
('M001','政治经济学',' 4','64.0','孔繁新'),
('S001','高等数学','3','48.0','赵晓尘')
INSERT INTO Table_学生作业表
(课程号,学号,作业1成绩,作业2成绩,作业3成绩)
VALUES
('K001','0433',60.0,75.0,75.0),
('K001','0529',70.0,70.0,60.0),
('K001','0531',70.0,80.0,80.0),
('K001','0591',80.0,90.0,90.0),
('K002','0496',80.0,80.0,90.0),
('K002','0529',70.0,70.0,85.0),
('K002','0531',80.0,80.0,80.0),
('K002','0538',65.0,75.0,85.0),
('K002','0592',75.0,85.0,85.0),
('K006','0531',80.0,80.0,90.0),
('K006','0591',80.0,80.0,80.0),
('M001','0496',70.0,70.0,80.0),
('M001','0591',65.0,75.0,75.0),
('S001','0531',80.0,80.0,80.0),
('S001','0538',60.0,NULL,80.0)
实验二sql语句
（1）查询各位学生的学号、专业班级和姓名。
SELECT 学号,姓名,专业班级
FROM Table_学生表（2）查询课程的全部信息。
SELECT *
FROM Table_课程表（3）查询数据库中有哪些专业班级。
SELECT 专业班级
FROM Table_学生表（4）查询学时数大于60的课程信息。
SELECT *
FROM Table_课程表
WHERE 学时数 > 60（5）查询在1986年出生的学生的学号、姓名和出生日期。
SELECT 学号,姓名,出生日期
FROM Table_学生表
WHERE YEAR(出生日期)=1986（6）查询三次作业的成绩都在80分以上的学号、课程号。
SELECT 课程号,学号
FROM Table_学生作业表
WHERE 作业1成绩>=80 AND 作业2成绩>=80 AND 作业3成绩>=80（7）查询姓张的学生的学号、姓名和专业班级。
SELECT 学号,姓名,专业班级
FROM Table_学生表
WHERE 姓名 LIKE '张%'（8）查询05级的男生信息。
SELECT *
FROM Table_学生表
WHERE 学号 LIKE '05%' AND 性别 = '男'（9）查询没有作业成绩的学号和课程号。
SELECT 课程号,学号
FROM Table_学生作业表
WHERE 作业1成绩 is NULL or 作业2成绩 is NULL or 作业3成绩 is NULL（10）查询学号为0538的学生的作业1总分。
SELECT SUM(作业1成绩)
FROM Table_学生作业表
WHERE 学号=0538（11）查询选修了K001课程的学生人数。（两种方法）
SELECT SUM(1) AS 学生人数
FROM Table_学生作业表
WHERE 课程号='K001'
SELECT count(课程号) AS 学生人数
FROM Table_学生作业表
WHERE 课程号='K001'（12）查询数据库中共有多少个班级。
SELECT count(distinct 专业班级)as 总班级数
FROM Table_学生表（13）查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。
SELECT 学号,avg(作业1成绩) as 作业1成绩平均分, avg(作业2成绩) as 作业2成绩平均分 , avg(作业3成绩) as 作业3成绩平均分
FROM [dbo].[Table_学生作业表]
GROUP BY 学号
HAVING(count(*)>=3)（14）查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。
SELECT distinct Table_学生表.学号,姓名,Table_课程表.课程号
FROM Table_学生表,Table_课程表,Table_学生作业表
WHERE Table_学生表.学号=Table_学生作业表.学号 and 姓名='于兰兰'
SELECT distinct Table_学生表.学号,姓名,Table_课程表.课程号
FROM Table_学生表 inner join Table_学生作业表
ON Table_学生表.学号=Table_学生作业表.学号
inner join Table_课程表
ON 姓名='于兰兰'实验三sql语句
1.使用子查询语句完成以下任务
（1）查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）
连接查询
SELECT Stu1.*
FROM Table_学生表 Stu1,Table_学生表 Stu2
WHERE Stu1.专业班级=Stu2.专业班级
AND Stu2.姓名='张志国'
子查询
SELECT *
FROM Table_学生表
WHERE(专业班级=all(SELECT 专业班级
FROM Table_学生表
WHERE姓名='张志国'))（2）查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）
连接查询
SELECT Course1.*
FROM Table_课程表 Course1,Table_课程表 Course2
WHERE Course1.学时数>Course2.学时数
AND Course2.课程名='计算机应用基础'
子查询
SELECT *
FROM Table_课程表
WHERE(学时数>all(SELECT 学时数
FROM Table_课程表
WHERE 课程名='计算机应用基础'))（3）查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、（使用exists关键字的相关子查询））
连接查询
SELECT Table_学生表.学号,姓名
FROM Table_学生表,Table_学生作业表
WHERE Table_学生表.学号=Table_学生作业表.学号
AND 课程号 = 'k002'
普通子查询
SELECT 学号,姓名
FROM Table_学生表
WHERE (学号 = any (SELECT 学号
FROM Table_学生作业表
WHERE 课程号='K002'))
使用exists关键字的相关子查询
SELECT 学号,姓名
FROM Table_学生表
WHERE EXISTS (SELECT *
FROM Table_学生作业表
WHERE Table_学生表.学号=Table_学生作业表.学号
AND Table_学生作业表.课程号 = 'k002')（4）查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）
SELECT *
FROM Table_学生作业表
WHERE Table_学生作业表.课程号!= ALL(SELECT 课程号
FROM Table_学生作业表
WHERE Table_学生作业表.课程号 = 'K001'
OR Table_学生作业表.课程号 = 'M001')2.用数据操纵完成以下任务
（1）在学生表中添加一条学生记录，其中，学号为你本人学号后四位例如’8501’，姓名为你本人姓名，性别为你本人性别，专业班级为软工22
INSERT INTO Table_学生表
(学号,姓名,性别,专业班级,出生日期,联系电话)
VALUES ('6705','王海洋','男','软工22','2001-10-1',18211154878)（2）将所有课程的学分数变为原来的两倍。
UPDATE Table_课程表
SET 学分数 = 学分数*2（3）删除（1）插入的信息
DELETE FROM Table_学生表
WHERE 学号=6705实验四sql语句
（1）创建一个电子05的学生视图（包括学号、姓名、性别、专业班级、出生日期）
CREATE VIEW View_1
AS
SELECT 学号 AS Expr1, 姓名, 性别, 专业班级, 出生日期
FROM Table_学生表
WHERE (专业班级 = '电子05')（2）创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）
CREATE VIEW View_2
AS
SELECT Table_学生表.学号, Table_学生表.姓名, Table_课程表.课程名, Table_学生作业表.作业1成绩,
Table_学生作业表.作业2成绩, Table_学生作业表.作业3成绩
FROM Table_学生表,Table_学生作业表,Table_课程表
WHERE Table_学生表.学号=Table_学生作业表.学号 and Table_课程表.课程号 = Table_学生作业表.课程号 and Table_学生表.专业班级='生物05'（3）创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）
CREATE VIEW View_3
AS
SELECT Table_学生作业表.学号,AVG(作业1成绩) AS 作业1成绩, AVG(作业2成绩)AS 作业2成绩, AVG(作业3成绩) AS 作业3成绩
FROM Table_学生作业表
GROUP BY 学号（4）修改第2题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉。
ALTER VIEW View_2
AS
SELECT Table_学生表.学号, Table_学生表.姓名, Table_课程表.课程名, Table_学生作业表.作业1成绩
FROM Table_学生表,Table_学生作业表,Table_课程表
WHERE Table_学生表.学号=Table_学生作业表.学号 and Table_课程表.课程号 = Table_学生作业表.课程号 and Table_学生表.专业班级='生物05'（5）向电子05的学生视图中添加一条记录，其中学号为0596，姓名为赵亦，性别为男，专业班级为电子05，出生日期为1986-6-8（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）
INSERT INTO View_1
VALUES('0596' ,'赵亦', '男' ,'电子05' ,'1986-6-8')（6）将电子05的学生视图中赵亦的性别改为“女”（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）
UPDATE View_1
SET 性别 = '女'
WHERE 姓名 = '赵亦'（7）删除电子05的学生视图中赵亦的记录。
DELETE FROM [dbo].[View_1]
WHERE 姓名 = '赵亦'（8）删除电子05的学生视图（给出SQL语句即可）。
DROP VIEW View_1