1. 首页
  2. 文章中心
  3. # SQL Sever

数据库-SQL Sever(练习检测)0. 目录2. SQL Sever Test

71 阅读 0 评论
我是靠谱客的博主 顺利火，最近开发中收集的这篇文章主要介绍数据库-SQL Sever(练习检测)0. 目录2. SQL Sever Test，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

数据库.SQL Sever.练习检测.职业技能

  • 0. 目录
  • 2. SQL Sever Test
    • 2.1 测试题目
    • 2.2 试题解答

0. 目录

2. SQL Sever Test

2.1 测试题目

使用SQL进行创建数据库和表

  1. 创建数据库Student
  2. 创建数据表：
    学生表studentinfo（学生id、学号、姓名、电话、性别、班级、年龄、创建日期）
    在这里插入图片描述
  3. 插入学生表数据（单条/批量插入）
    在这里插入图片描述
  4. 插入学生成绩数据
    在这里插入图片描述
  5. 删除学号为005的学生和学生成绩
  6. 更新学号为001语文的成绩为78
  7. 查询一班男生的成绩
  8. 查询一班语文科目成绩，并通过成绩降序
  9. 通过班级和科目分组，查询平均分和最高分，按最高分排序（序号、班级、科目、最高分、平均分）【除了SQL外，在EXCEL中也要模拟出逻辑查询处理的步骤】
  10. 查询学号001学生的各科目成绩在一行中显示
    在这里插入图片描述

2.2 试题解答

  • 创建数据库Student
CREATE DATABASE Student;

在这里插入图片描述

  • 创建数据库表studentinfo、studentscore
CREATE TABLE studentinfo(
	studentid UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	studentno NVARCHAR(20) NOT NULL,
	studengtname NVARCHAR(20) NOT NULL,
	mobile NVARCHAR(20) NOT NULL,
	gender NVARCHAR(20) NOT NULL,
	classname NVARCHAR(20) NOT NULL,
	age INT NOT NULL,
	createdon DATETIME NOT NULL
);

CREATE TABLE studentscore(
	studentscoreid UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	studentid UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES studentinfo(studentid),
	subjectname NVARCHAR(20) NOT NULL,
	score INT NOT NULL,
	createdon DATETIME NOT NULL

在这里插入图片描述
在这里插入图片描述

  • studentinfo、studentscore表插入数据
  1. 方法1
INSERT INTO studentinfo(studentid,studentno,studentname,mobile,gender,classname,age,createdon)
SELECT '61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','001','张静','13811920091','女','一班',20,'2020/4/1 10:30'
UNION
SELECT '959FA07B-AAE2-469B-A2BE-7BDE1048A02C','002','王伟','13811920092','男','二班',19,'2020/4/1 10:30'
UNION
SELECT '7BF5489C-CA9E-46CD-B095-4C6FC6797F15','003','张三','13811920093','男','二班',18,'2020/4/1 10:30'
UNION
SELECT 'C281947B-1ABB-4D26-B880-FF227105C8D4','004','王武','13811920094','男','一班',19,'2020/4/1 10:30'
UNION
SELECT '8AC42551-5E05-4DA1-8329-8A896BBD0ABE','005','谢文','13811920095','男','三班',21,'2020/4/1 10:30'
UNION
SELECT 'FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','006','叶问','13811920096','男','一班',22,'2020/4/1 10:30'
UNION
SELECT '03FC4DF9-AABA-4B6A-9565-705275E322B0','007','张强','13811920097','男','三班',20,'2020/4/1 10:30'
UNION
SELECT 'B32F30B6-5477-4462-BBC8-9FE700AF4252','008','李丽','13811920098','女','四班',18,'2020/4/1 10:30'

INSERT INTO studentscore(studentscoreid,studentid,subjectname,score,createdon)
SELECT 'CF8811B3-E3D2-401C-96F7-54EDCC77B023','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','语文',70,'2020/4/1 10:30'
UNION
SELECT '51169677-5509-43C2-A798-DEC19EEA8767','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','语文',84,'2020/4/1 10:30'
UNION
SELECT 'BFB715EA-49FC-4898-8525-5838EDD34A8E','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','语文',88,'2020/4/1 10:30'
UNION
SELECT 'E1F3452F-1204-42F8-9C27-EF37AE341FFC','C281947B-1ABB-4D26-B880-FF227105C8D4','语文',92,'2020/4/1 10:30'
UNION
SELECT 'B4112535-7002-4E7B-8EA4-75A88CD68348','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','语文',98,'2020/4/1 10:30'
UNION
SELECT 'DA67328A-FB51-4731-9A12-1D3F43E9B722','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','语文',86,'2020/4/1 10:30'
UNION
SELECT '46CDCAD8-B358-4D6C-B43F-C21FBA68875F','03FC4DF9-AABA-4B6A-9565-705275E322B0','语文',67,'2020/4/1 10:30'
UNION
SELECT '3C242280-2FA4-44D5-8FE4-A24FE30E1E17','B32F30B6-5477-4462-BBC8-9FE700AF4252','语文',89,'2020/4/1 10:30'
UNION
SELECT 'B2C8AA87-29BE-4C2A-99FA-207BF7D91202','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','数学',94,'2020/4/1 10:30'
UNION
SELECT '2E3A23D0-0F76-43A1-95DA-3356748FE3A9','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','数学',93,'2020/4/1 10:30'
UNION
SELECT '2615B55C-513D-4846-A5E7-E8345E4AF1B1','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','数学',78,'2020/4/1 10:30'
UNION
SELECT '6CF39B27-50EE-47A1-87EB-18137D73928B','C281947B-1ABB-4D26-B880-FF227105C8D4','数学',69,'2020/4/1 10:30'
UNION
SELECT 'F41DE0FD-E4AB-4708-9F6E-4D5C78BFCE3B','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','数学',100,'2020/4/1 10:30'
UNION
SELECT '51EF0AD8-604E-4058-BD5B-FCC75F3EB492','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','数学',99,'2020/4/1 10:30'
  1. 方法2
INSERT INTO studentinfo VALUES ('61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','001','张静','13811920091','女','一班',20,'2020/4/1 10:30');
INSERT INTO studentinfo VALUES ('959FA07B-AAE2-469B-A2BE-7BDE1048A02C','002','王伟','13811920092','男','二班',19,'2020/4/1 10:30');
INSERT INTO studentinfo VALUES ('7BF5489C-CA9E-46CD-B095-4C6FC6797F15','003','张三','13811920093','男','二班',18,'2020/4/1 10:30');
INSERT INTO studentinfo VALUES ('C281947B-1ABB-4D26-B880-FF227105C8D4','004','王武','13811920094','男','一班',19,'2020/4/1 10:30');
INSERT INTO studentinfo VALUES ('8AC42551-5E05-4DA1-8329-8A896BBD0ABE','005','谢文','13811920095','男','三班',21,'2020/4/1 10:30');
INSERT INTO studentinfo VALUES ('FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','006','叶问','13811920096','男','一班',22,'2020/4/1 10:30');
INSERT INTO studentinfo VALUES ('03FC4DF9-AABA-4B6A-9565-705275E322B0','007','张强','13811920097','男','三班',20,'2020/4/1 10:30');
INSERT INTO studentinfo VALUES ('B32F30B6-5477-4462-BBC8-9FE700AF4252','008','李丽','13811920098','女','四班',18,'2020/4/1 10:30');

INSERT INTO studentscore VALUES ( 'CF8811B3-E3D2-401C-96F7-54EDCC77B023','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','语文',70,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( '51169677-5509-43C2-A798-DEC19EEA8767','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','语文',84,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( 'BFB715EA-49FC-4898-8525-5838EDD34A8E','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','语文',88,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( 'E1F3452F-1204-42F8-9C27-EF37AE341FFC','C281947B-1ABB-4D26-B880-FF227105C8D4','语文',92,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( 'B4112535-7002-4E7B-8EA4-75A88CD68348','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','语文',98,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( 'DA67328A-FB51-4731-9A12-1D3F43E9B722','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','语文',86,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( '46CDCAD8-B358-4D6C-B43F-C21FBA68875F','03FC4DF9-AABA-4B6A-9565-705275E322B0','语文',67,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( '3C242280-2FA4-44D5-8FE4-A24FE30E1E17','B32F30B6-5477-4462-BBC8-9FE700AF4252','语文',89,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( 'B2C8AA87-29BE-4C2A-99FA-207BF7D91202','61ACAC24-5C09-47D2-A7E9-71A4FF9ECBAD','数学',94,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( '2E3A23D0-0F76-43A1-95DA-3356748FE3A9','959FA07B-AAE2-469B-A2BE-7BDE1048A02C','数学',93,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( '2615B55C-513D-4846-A5E7-E8345E4AF1B1','7BF5489C-CA9E-46CD-B095-4C6FC6797F15','数学',78,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( '6CF39B27-50EE-47A1-87EB-18137D73928B','C281947B-1ABB-4D26-B880-FF227105C8D4','数学',69,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( 'F41DE0FD-E4AB-4708-9F6E-4D5C78BFCE3B','8AC42551-5E05-4DA1-8329-8A896BBD0ABE','数学',100,'2020/4/1 10:30');
INSERT INTO studentscore VALUES ( '51EF0AD8-604E-4058-BD5B-FCC75F3EB492','FEA2D7F4-BA8A-4574-AE64-46C20A5689E0','数学',99,'2020/4/1 10:30');

在这里插入图片描述
在这里插入图片描述

  • 删除学号为005的学生和学生成绩
DELETE FROM studentscore WHERE studentscore.studentid = (SELECT studentid FROM studentinfo WHERE studentinfo.studentno = '005');
DELETE FROM studentinfo WHERE studentinfo.studentno = '005';

在这里插入图片描述

  • 更新学号为001语文的成绩为78
UPDATE studentscore SET score = 78 WHERE studentscore.subjectname = '语文' AND studentscore.studentid = (SELECT studentid FROM studentinfo WHERE studentinfo.studentno = '001');

在这里插入图片描述

  • 查询一班男生的成绩
SELECT studengtname,subjectname,score FROM studentscore sco
	LEFT JOIN studentinfo info ON sco.studentid = info.studentid
	WHERE info.classname = '一班' AND info.gender = '男'
	ORDER BY info.studentno;

在这里插入图片描述

  • 查询一班语文科目成绩，并通过成绩降序
SELECT studengtname,score FROM studentscore sco
	LEFT JOIN studentinfo info ON sco.studentid = info.studentid
	WHERE info.classname = '一班' AND sco.subjectname = '语文'
	ORDER BY score DESC;

在这里插入图片描述

  • 通过班级和科目分组，查询平均分和最高分，按最高分排序（序号、班级、科目、最高分、平均分）
SELECT classname,subjectname,MAX(score) AS '最高分',AVG(score) AS '平均分' FROM studentscore sco
	LEFT JOIN studentinfo info ON sco.studentid = info.studentid
	GROUP BY classname,subjectname
	ORDER BY MAX(score);

在这里插入图片描述
VT1-VT5的计算方法

--VT1
SELECT * FROM studentscore INNER JOIN studentinfo ON 1 = 1;
--VT2
SELECT * FROM studentscore sco INNER JOIN studentinfo info ON sco.studentid = info.studentid;
--VT3
SELECT * FROM studentscore sco INNER JOIN studentinfo info ON sco.studentid = info.studentid GROUP BY classname,subjectname;
--VT4
SELECT classname,subjectname,MAX(score) AS '最高分',AVG(score) AS '平均分' FROM studentscore sco INNER JOIN studentinfo info ON sco.studentid = info.studentid GROUP BY classname,subjectname;
--VT5
SELECT classname,subjectname,MAX(score) AS '最高分',AVG(score) AS '平均分' FROM studentscore sco INNER JOIN studentinfo info ON sco.studentid = info.studentid GROUP BY classname,subjectname ORDER BY MAX(score);

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述在这里插入图片描述

  • 查询学号001学生的各科目成绩在一行中显示
    聚合方法:
SELECT classname,studentno,studentname,
	MAX(CASE subjectname WHEN '语文' THEN score ELSE 0 END)语文,
	MAX(CASE subjectname WHEN '数学' THEN score ELSE 0 END)数学
	FROM studentscore sco
	LEFT JOIN studentinfo info ON sco.studentid = info.studentid
	WHERE studentno = '001'
	GROUP BY info.classname,studentno,studentname;

pivot方法1:

WITH q AS (
	SELECT classname,studentno,studentname,score,subjectname FROM studentinfo 
	JOIN studentscore 
	ON studentinfo.studentid=studentscore.studentid 
	WHERE studentno='001') 
	SELECT * FROM q
	PIVOT (max(score) FOR [subjectname] in(数学,语文)) AS t;

pivot方法2:

SELECT * FROM (
	SELECT info.studentno, info.classname,info.studentname,sco.subjectname,sco.score
	FROM studentinfo info
	INNER JOIN studentscore sco ON info.studentid = sco.studentid
	WHERE studentno = '001'
)tmp
PIVOT
(
	SUM(tmp.score) FOR tmp.subjectname in ([语文],[数学])
)sco;

在这里插入图片描述

最后

以上就是顺利火为你收集整理的数据库-SQL Sever(练习检测)0. 目录2. SQL Sever Test的全部内容，希望文章能够帮你解决数据库-SQL Sever(练习检测)0. 目录2. SQL Sever Test所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(47)

相关文章

SQL相关知识复习总结
SQL相关知识复习总结
MYSQL数据库语句复习一、数据库相关语句二、比较经典的数据库语句实例
MYSQL数据库语句复习一、数据库相关语句二、比较经典的数据库语句实例
数据库的复习--3.SQL语言
数据库的复习--3.SQL语言
数据库的复习总结1.操作数据库2.操作表3.操作表中的记录
数据库的复习总结1.操作数据库2.操作表3.操作表中的记录
数据库-SQL Sever(练习检测)0. 目录2. SQL Sever Test
数据库-SQL Sever(练习检测)0. 目录2. SQL Sever Test
数据库期末sql基础语句的复习
数据库期末sql基础语句的复习
数据库期末总结——查询语句SQL
数据库期末总结——查询语句SQL
数据库SQL复习要点
数据库SQL复习要点

评论列表共有 0 条评论

立即
投稿
返回
顶部