|
0．本实验环境 ①SQL Server 2016②SSMS 17
1．创建数据库和数据表（1）利用资源管理器，在 D 盘建立以自己的姓名为名称的文件夹，以便保存数据库。
（2）登录并连接到 SQL Server 2012 服务器。
（3）利用对象资源管理器建立名称为 Study 的数据库文件，主文件名为 Study.mdf，日志文件名为 Study.ldf，它们的保存路径在第（1）步中建立的文件夹。 （4）利用对象资源管理器在已经建立的 Study 数据库中分别建立以下六个数据表。
① 学生基本情况数据表 Student，结构如下：
123456789use Study;CREATE TABLE Student(s_no char(6) primary key,--学号class_no char(6) not null,--班级号s_name varchar(10) not null,--学生姓名s_sex char(2) check(s_sex= '男' or s_sex='女'),--性别s_birthday datetime --出生日期)
② 班级数据表 Class，结构如下：
123456CREATE TABLE Class(class_no char(6) primary key,--班级号class_name char(20) not null,--班级名称class_special varchar(20),--所属专业class_dept char(20)--系别)
③ 课程数据表 Course，结构如下：
12345CREATE TABLE Course(course_no char(5) primary key,--课程号course_name char(20) not null,--课程名称course_score numeric(6, 2)--学分)
④ 选修课程情况数据表 Choice，结构如下：
12345CREATE TABLE Choice(s_no char(6),--学号course_no char(5),--课程号score numeric(6, 2)--成绩)
⑤ 教师数据表 Teacher，结构如下：
1234567CREATE TABLE Teacher(t_no char(6) primary key,--教师号t_name varchar(10) not null,--教师姓名t_sex char(2) check(t_sex = '男'or t_sex='女'),--性别t_birthday datetime,--出生日期t_title char(10)--职称)
⑥ 教师任课情况表 Teaching，结构如下：
1234Create table Teaching(couse_no char(5),--课程号t_no char(6)--教师号)
（5）利用企业管理器，在 Study 数据库中，向以上建立的六个数据表中分别输入以下内容。
① 学生基本情况数据表 Student 的内容如下：
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455insert intoStudent (s_no,class_no,s_name, s_sex,s_birthday)values('991101', 'js9901', '张彬', '男', '1981-10-1')insert intoStudent (s_no,class_no,s_name,s_sex,s_birthday)values('991102', 'js9901' , '王蕾', '女', '1980-8-8')insert intoStudent (s_no,class_no,s_name,s_sex,s_birthday)values('991103', 'js9901' , '李建国', '男', '1981-4-5')insert intoStudent (s_no, class_no, s_name, s_sex,s_birthday)values('991104', 'js9901' , '李平方', '男', '1981-5-12')insert intoStudent (s_no,class_no,s_name,s_sex,s_birthday)values('991201', 'js9902' , '陈东辉', '男', '1980-2-8')insert intoStudent (s_no, class_no, s_name, s_sex,s_birthday)values('991202', 'js9902' , '葛鹏', '男', '1979-12-23')insert intoStudent (s_no,class_no, s_name,s_sex,s_birthday)values('991203', 'js9902' , '潘桃芝', '女', '1980-2-6')insert intoStudent (s_no, class_no, s_name, s_sex, s_birthday)values('991204', 'js9902' , '姚一峰', '男', '1981-5-7')insert intoStudent (s_no,class_no,s_name,s_sex,s_birthday)values('001101', 'js0001' , '宋大方', '男', '1980-4-9')insert intoStudent (s_no,class_no,s_name,s_sex,s_birthday)values('001102', 'js0001' , '许辉', '女','1978-8-1')insert intoStudent (s_no,class_no,s_name,s_sex,s_birthday)values('001201', 'js0002' , '王一山', '男', '1982-12-14')insert intoStudent (s_no,class_no,s_name,s_sex,s_birthday)values('001202', 'js0002' , '牛莉', '女', '1981-6-9')insert intoStudent (s_no, class_no,s_name,s_sex,s_birthday)values('002101', 'xx0001' , '李丽丽', '女', '1981-9-19')insert intoStudent (s_no, class_no, s_name,s_sex,s_birthday)values('002102', 'xx0001', '李王', '男', '1980-9-23')
② 班级数据表 Class 的内容如下：
1234567891011121314151617insert into Class(class_no,class_name,class_special,class_dept)values('js9901','计算机99-1','计算机','计算机系')insert into Class(class_no,class_name,class_special,class_dept)values('js9902','计算机99-2','计算机','计算机系')insert into Class(class_no,class_name,class_special,class_dept)values('js0001','计算机00-1','计算机','计算机系')insert into Class(class_no,class_name,class_special,class_dept)values('js0002','计算机00-2','计算机','计算机系')insert into Class(class_no,class_name,class_special,class_dept)values('xx0001','信息00-1','信息','信息系')insert into Class(class_no,class_name,class_special,class_dept)values('xx0002','信息00-2','信息','信息系')
③ 课程数据表 Course 的内容如下：
1234567891011121314151617insert into Course(course_no,course_name,course_score)values('01001','计算机基础','3')insert into Course(course_no,course_name,course_score)values('01002','程序设计语言','5')insert into Course(course_no,course_name,course_score)values('01003','数据结构','6')insert into Course(course_no,course_name,course_score)values('02001','数据库原理与应用','6')insert into Course(course_no,course_name,course_score)values('02002','计算机网络','6')insert into Course(course_no,course_name,course_score)values('02003','微机原理与应用','8')
④ 选修课程情况数据表 Choice 的内容如下：
123456789101112131415161718192021222324252627insert into Choice(s_no,course_no,score)values('991101','01001','88.0')insert into Choice(s_no,course_no,score)values('991102','01001','0.0')--这里的score书上是空的的，但是前面设置了NOT NYLL，所以给个初始值0.0insert into Choice(s_no,course_no,score)values('991103','01001','91.0')insert into Choice(s_no,course_no,score)values('991104','01001','78.0')insert into Choice(s_no,course_no,score)values('991201','01001','67.0')insert into Choice(s_no,course_no,score)values('991101','01002','90.0')insert into Choice(s_no,course_no,score)values('991102','01002','58.0')insert into Choice(s_no,course_no,score)values('991103','01002','71.0')insert into Choice(s_no,course_no,score)values('991104','01002','85.0')
⑤ 教师数据表 Teacher 的内容如下：
1234567891011121314151617181920212223242526272829insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)values('000001', '李英','女','1964-11-3','讲师')insert into Teacher(t_no,t_name, t_sex,t_birthday,t_title)values('000002', '王大山', '男', '1955-3-7','副教授')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)values('000003','张朋','男','1960-10-5','讲师')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)values('000004', '陈为军', '男', '1970-3-2','助教')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)values('000005', '宋浩然', '男','1966-12-4','讲师')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)values('000006','许红霞','女','1951-5-8','副教授')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)values('000007', '徐永军', '男', '1948-4-8','教授')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)values('000008', '李桂箐', '女','1940-11-3','教授')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)values('000009', '王一凡', '女','1962-5-9','讲师')insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title)values('000010','田峰','男','1972-11-5','助教')
⑥ 教师任课情况表 Teaching 的内容如下：
1234567891011121314151617181920212223242526272829insert into Teaching(couse_no,t_no)values('01001', '000001')insert into Teaching(couse_no,t_no)values('01002', '000002')insert into Teaching(couse_no,t_no)values('01003', '000002')insert into Teaching(couse_no,t_no)values('02001', '000003')insert into Teaching(couse_no,t_no)values('02002', '000004')insert into Teaching(couse_no,t_no)values('01001', '000005')insert into Teaching(couse_no,t_no)values('01002', '000006')insert into Teaching(couse_no,t_no)values('01003', '000007')insert into Teaching(couse_no,t_no)values('02001','000007')insert into Teaching(couse_no,t_no)values('02002', '000008')
（6）利用对象资源管理器的数据库备份功能，将以上建立的数据库 Study 备份到所建立的文件夹中，并将备份文件拷贝到 U 盘中，以备下面的题目使用。
2．简单的数据查询本题中所用的数据库是第 1 题中所建立的 Study 数据库。
（1）查询所有同学的基本信息，包括：学号 s_no、班级号 class_no、姓名 s_name、性别 s_sex、出生日期 s_birthday。
12SELECT s_no,class_no,s_name,s_sex,s_birthdayFROM Student
（2）查询所有同学，要求显示其学号 s_no、姓名 s_name。
12SELECT s_no,s_nameFROM Student
（3）查询所有男同学，要求显示其学号 s_no、姓名 s_name、出生日期 s_birthday。
123SELECT s_no,s_name,s_birthdayFROM StudentWHERE s_sex = '男'
（4）查询所有出生日期在“1980－01－01”前的女同学，要求显示其学号 s_no、姓名 s_name、性别 s_sex、出生日期 s_birthday。
1234SELECT s_no,s_name,s_sex,s_birthdayFROM StudentWHERE s_sex = '女'AND s_birthday = '1960-01-01'AND t_title = '讲师'order by t_birthday asc
3．复杂数据查询本题中所用的数据库是第 1 题中所建立的 Study 数据库。
（1）查询所有同学的选课及成绩情况，要求显示学生的学号 s_no、姓名 s_name、课程号course_no和课程的成绩 score。
12345--有些同学没有选课，在这里列出的是所有同学的，无论选没选课SELECT s.s_no,s.s_name,c.course_no,c.scoreFROM StudentAs s left join Choice As cOn s.s_no = c.s_no
（2）查询所有同学的选课及成绩情况，要求显示学生的姓名 s_name、课程名称 course_ name、课程的成绩 score，并将查询结果存放到一个新的数据表 new_table 中。
123456--在这里把没有选课的也查出来了，如果想查出只选有课的同学，用内连接SELECT s.s_name,c.course_name,ch.scoreinto new_tableFROM(Student As s left join Choice As ch on s.s_no=ch.s_no)left join Course As cOn ch.course_no=c.course_no
（3）查询“计算机 99-1”班的同学的选课及成绩情况，要求显示学生的学号 s_no、姓名 s_name、课程号 course_no、课程名称 course_name、课程的成绩 score。
12345--这里用到的方法是把四个表联立SELECT s.s_no,s.s_name,c.course_no,c.course_name,ch.scoreFROM(Student As s inner join Choice As ch ON s.s_no=ch.s_no)inner join Class As cl ON s.class_no=cl.class_no AND cl.class_name='计算机99-1'inner join Course As c ON ch.course_no=c.course_no
（4）查询所有同学的学分情况（假设课程成绩≥60 分时可获得该门课程的学分），要求显示学生的学号 s_no、姓名 s_name、总学分（将该列定名为：total_score）。（用 JOIN）
12345SELECT s.s_no,s.s_name,SUM(course_score)As total_scoreFROM Student As s inner join Choice As ch ON s.s_no=ch.s_noinner join Course As c ON ch.course_no=c.course_noWHERE ch.score>='60.0'GROUP BY s.s_no,s.s_name
（5）查询所有同学的平均成绩及选课门数，要求显示学生的学号 s_no、姓名 s_name、平均成绩（将该列定名为 average_score）、选课的门数（将该列定名为：choice_num）。
123SELECT s.s_no,s_name,AVG(ch.score)As average_score,COUNT(ch.course_no)As choice_numFROM Student AS s inner join Choice As ch ON s.s_no=ch.s_noGROUP BY s.s_no,s.s_name
（6）查询所有选修了课程但未参加考试的所有同学及相应的课程，要求显示学生的学号 s_no、姓名 s_name、课程号 course_no、课程名称 course_name。
123SELECT s.s_no,s.s_name,c.course_no,c.course_nameFROM Student As s inner join Choice As ch ON s.s_no=ch.s_no AND ch.score='0.0'inner join Course As c ON ch.course_no=c.course_no
（7）查询所有选修了课程但考试不及格（假设＜60 分为不及格）的所有同学及相应的课程，要求显示学生的学号 s_no、姓名 s_name、课程号 course_no、课程名称 course_name、学分course_score。
123456SELECT s.s_no,s.s_name,c.course_name,c.course_scoreFROM Student As s inner join Choice As chON s.s_no=ch.s_noinner join Course As cON ch.course_no=c.course_noWHERE ch.score |