数据库原理及应用教程第四版实验一到四sql整理

2023-04-02T23:50:00

数据库原理及应用教程第四版实验一到四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
当前页面是本站的「Baidu MIP」版。发表评论请点击:完整版 »