SQL-Server练习题

最新推荐文章于 2024-05-03 07:27:17 发布

itl-coder.github.io

最新推荐文章于 2024-05-03 07:27:17 发布

阅读量1k

点赞数 1

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_43340420/article/details/120733143

版权

SQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

创建表命令使用

CREATE DATABASE stsc
ON PRIMARY (
    /*
    NAME is filespec: filespec 指定的文件的逻辑文件名
    逻辑文件名--数据库文件在数据库中显示的名字
    */
    name=coderitl_datafile,
    /* 主数据文件存储路径 name.mdf */
    filename='G:\SQLServer\Data\database-20210928\stsc.mdf',
    /* SIZE: 指定 filespec 定义的文件的初始大小 */
    size=10mb,
    /* MAXSIZE：指定 filespec 定义文件的最大大小 */
    maxsize = 50mb, filegrowth = 5mb )

log ON (
   name=coderit_log,
   filename='G:\SQLServer\Data\database-20210928\stsc.ldf',
   SIZE=5mb,
   maxsize=25mb,
   filegrowth=5mb
) ;

使用T-SQL命令，为stsc数据库创建教材P388所示的student，course，score，teacher数据表结构。

use stsc;

-- 创建表 student 
create table student(
	/* 学号 */
	sno char(6) not null primary key,
	/* 姓名 */
	sname char(8)  not null,
	/* 性别  */
	stsex char(2) not null,
	/* 出生日期 */
	stbirthday date not null,
	/* 专业 */
	speciality char(12),
	/* 总学分 */
	tc int 
);

-- 创建表 course
create table course(
	-- 课程号
	cno char(3) not null primary key,
	-- 课程名
	cname char(16)  not null,
	-- 学分
	credit int ,
	-- 教师号
	tno char(6)
);


-- 创建表 score
create table score(
	-- 学号
	stno char(6) not null,
	-- 课程号
	cno char(3)  ,
	-- 成绩
	grade int ,
	-- 添加联合主键
	primary key(stno,cno)
);

-- 创建表 teacher 

create table teacher(
	-- 教师号
	tno char(6) not null,
	-- 姓名
	tname char(8)  not null,
	-- 性别 
	tsex char(2) not null,
	-- 出生日期
	tbirthday date not null,
	-- 职称
	title char(12) ,
	-- 学院名
	school char(12),
);

使用T-SQL命令，为stsc数据库的student表添加Email字段，类型为char(20)
```
use stsc
go
ALTER TABLE student
	ADD Email char(20) NOT NULL;
```
使用T-SQL命令，在stsc数据库中修改course表的credit字段类型，改为float
```
USE stsc
GO
ALTER TABLE course
	ALTER COLUMN credit float;
```
使用T-SQL命令，在stsc数据库中删除student表的email字段
```
USE stsc
GO
ALTER TABLE student
	DROP COLUMN email;
```

用SSMS图形界面向stsc数据库中的student，course，score表插入数据，（表中数据如P389）

student:
    INSERT INTO student VALUES ('121001', '李贤友', '男', '1991-12-30', '通信', 52);
    INSERT INTO student VALUES ('121002', '周映雪', '女', '1993-01-12', '通信', 49);
    INSERT INTO student VALUES ('121005', '刘刚', '男', '1992-07-05', '通信', 50);
    INSERT INTO student VALUES ('122001', '郭德纲', '男', '1991-10-23', '计算机', 48);
    INSERT INTO student VALUES ('122002', '谢暄', '女', '1992-09-11', '计算机', 52);
    INSERT INTO student VALUES ('122004', '孙婷', '女', '1991-02-24', '计算机', 50);

course:
    INSERT INTO course VALUES ('102', '数字电路', 3, '102101');
    INSERT INTO course VALUES ('203', '数据库系统', 3, '204101');
    INSERT INTO course VALUES ('205', '微机原理', 4, '204107');
    INSERT INTO course VALUES ('208', '计算机原理', 4, NULL);
    INSERT INTO course VALUES ('801', '高等数学', 4, '801102');

score:
	INSERT INTO score VALUES ('121001', '102', 92);
	INSERT INTO score VALUES ('121002', '102', 72);
	INSERT INTO score VALUES ('121005', '102', 87);
	INSERT INTO score VALUES ('122002', '203', 94);
	INSERT INTO score VALUES ('122004', '203', 81);
	INSERT INTO score VALUES ('121001', '205', 91);
	INSERT INTO score VALUES ('121002', '205', 65);
    INSERT INTO score VALUES ('121005', '205', 85);
    INSERT INTO score VALUES ('121001', '801', 94);
    INSERT INTO score VALUES ('121002', '801', 73);
    INSERT INTO score VALUES ('121005', '801', 82);
    INSERT INTO score VALUES ('122001', '801', NULL);
    INSERT INTO score VALUES ('122002', '801', 95);
    INSERT INTO score VALUES ('122004', '801', 86);

用Insert语句向stsc数据库中的teacher数据表插入数据（表中数据如P389）

Teacher:  	
	INSERT INTO teacher VALUES ('102101', '刘林卓', '男', '1962-03-21', '教授', '通信学院');	
	INSERT INTO teacher VALUES ('102105', '周学莉', '女', '1977-10-05', '讲师', '通信学院');	
	INSERT INTO teacher VALUES ('204101', '吴波', '男', '1978-04-26', '教授', '计算机学院');	
	INSERT INTO teacher VALUES ('204107', '王冬琴', '女', '1968-11-18', '副教授', '计算机学院');	
	INSERT INTO teacher VALUES ('801102', '李伟', '男', '1975-08-19', '副教授', '数学学院');

新增练习

	/* 1. 将李贤友同学的出生日期改为‘1995-4-5’。*/
	UPDATE student
	SET stbirthday = '1995-4-5'
	WHERE sname = '李贤友';

输出:

	-- 2. 在teacher表添加一名老师的信息: 通信学院，张丽，女，教授，教师号：102103，出生日期：1977-8-7。
	INSERT INTO teacher ( school, tname, tsex, title, tno, tbirthday)
	VALUES ( '通信学院', '张丽', '女', '教授', '102103', '1977-8-7');

输出:

	-- 3.   删除张丽老师的信息。
	DELETE
	FROM teacher
	WHERE tname = '张丽';

输出:

	-- 4.   查询student表中总分大于或等于50分的学生情况。
	SELECT *
	FROM student
	WHERE tc >= 50;

输出:

	-- 5. 查询谢暄的“高等数学”成绩。
	SELECT sname, grade
	FROM student stu
	        INNER JOIN score ON stu.sno = score.stno
	        INNER JOIN course ON score.cno = course.cno
	WHERE stu.sname = '谢暄'
	 AND cname = '高等数学';

输出:

	-- 6. 查找选修了“数字电路”的学生的姓名及成绩，并按成绩降序排列。
	SELECT sname, grade
	FROM student stu
	        INNER JOIN score ON stu.sno = score.stno
	        INNER JOIN course ON score.cno = course.cno
	WHERE cname = '数字电路'
	ORDER BY grade DESC;

输出:

-- 7.   查询学号为121001的学生的所有课程的平均成绩。
	use stsc
	go
	SELECT count(*) AS "leanCourse",avg(grade) AS "allCourseAvgScore", stu.stno
	FROM student stu
	       INNER JOIN score ON stu.stno = score.stno
	WHERE stu.stno = '121001'
	GROUP BY stu.stno;

输出:

-- 8.   查找“数据库系统”和“微机原理”的平均成绩。
	SELECT cname, avg(grade) AS "Avg"
	FROM score
	        INNER JOIN course ON score.cno = course.cno
	WHERE cname in ('数据库系统','微机原理')
	GROUP BY cname;

输出:

	9. 查询每个专业最高分的课程名和分数。
	-- 查询课程名
	SELECT max(grade) AS "grade",cname
	FROM course
	INNER JOIN score
	ON course.cno = score.cno
	INNER JOIN student stu
	ON stu.sno = score.stno
	WHERE speciality IN
	-- 查询专业
	(SELECT DISTINCT student.speciality
	FROM student)
	AND grade IN
	-- 查询最大成绩
	(SELECT max(grade) AS "grade"
	FROM student
	INNER JOIN score
	ON student.sno = score.stno
	GROUP BY speciality)
	GROUP BY cname;

输出:

	-- 10. 查询通信专业最高分的学生的学号、姓名、课程号和分数。
	
	select sno, sname, c.cno, grade,speciality
	from student
	        inner join score sc on sno = stno
	        inner join
	    course c on c.cno = sc.cno
	where grade = (select max(grade)
	              from student
	                       inner join score sc on sno = stno
	                       inner join
	                   course c on c.cno = sc.cno
	              where speciality = '通信')
	  and  speciality = '通信';
	-------------------------------------------------------------------------------
	/* 临时解决方法: 创建一个视图 */
	create view getMaxGradeInfo as
	SELECT sno
	    , sname
	    , score.cno
	    , grade
	    , cname
	    , speciality
	FROM student stu
	        INNER JOIN score ON stu.sno = score.stno
	        INNER JOIN course ON score.cno = course.cno
	WHERE speciality = '通信'
	GROUP BY sno, sname, score.cno, cname, grade, speciality;
	
	/* 临时解决方案 */
	select top 1 max(grade) as 'MaxGrade',sno, sname, cno, grade
	from getMaxGradeInfo
	group by sno, sname, cno, grade;

输出:

	-- 11. 查询有两门以上（含两门）课程超过 80 分的学生的姓名及其平均成绩。
	SELECT count(*) as 'TCourse', avg(grade) as 'Avg', sname
	FROM student stu
	        inner join score on
	   score.stno = stu.sno
	        inner join course on score.cno = course.cno
	where grade > 80
	group by sname
	having count(*) > 2;

输出:

	-- 12.查询选修了所有已安排任课教师的课程的学生的姓名。
	use stsc
	go
	SELECT sname
	FROM student
	WHERE NOT EXISTS(SELECT *
                FROM course
                WHERE NOT EXISTS(
                        SELECT *
                        FROM score sc
                        WHERE sc.stno = Student.sno
                          AND sc.Cno = course.cno));

itl-coder.github.io

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
SQL-Server练习题

创建表命令使用CREATE DATABASE stscON PRIMARY ( /* NAME is filespec: filespec 指定的文件的逻辑文件名逻辑文件名--数据库文件在数据库中显示的名字 */ name=coderitl_datafile, /* 主数据文件存储路径 name.mdf */ filename='G:\SQLServer\Data\database-20210928\stsc.mdf', /* S...
复制链接

扫一扫