【SQL Server】综合练习

【SQL Server】综合练习

数据：

use test;GO
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 datet --出生日期
)GO
CREATE TABLE Class(class_no char(6) primary key, --班级号class_name char(20) not null, --班级名称class_special varchar(20), --所属专业class_dept char(20) --系别
)
GOCREAtE TABLE Course(course_no char(5) primary key, --课程号course_name char(20) not null, --课程名称course_score numeric(6,2) --学分
)GOCREATE TABLE Choice(s_no char(6), --学号course_no char(5), --课程号score numeric(6,2) --成绩
)GOCREATE 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 date, --出生日期t_title char(10) --职称
) GOCreate table Teaching(course_no char(5), --课程号t_no char(6) --教师号
)GOinsert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('991101', 'js9901' , '张彬', '男', '1981-10-1')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('991102', 'js9901' , '王蕾', '女', '1980-8-8')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('991103', 'js9901' , '李建国', '男', '1981-4-5')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('991104', 'js9901' , '李平方', '男', '1981-5-12')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('991201', 'js9902' , '陈东辉', '男', '1980-2-8')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('991202', 'js9902' , '葛鹏', '男', '1979-12-23')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('991203', 'js9902' , '潘桃芝', '女', '1980-2-6')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('991204', 'js9902' , '姚一峰', '男', '1981-5-7')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('001101', 'js0001' , '宋大方', '男', '1980-4-9')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('001102', 'js0001' , '许辉', '女', '1978-8-1')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('001201', 'js0002' , '王一山', '男', '1982-12-14')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('001202', 'js0002' , '牛莉', '女', '1981-6-9')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('002101', 'xx0001' , '李丽丽', '女', '1981-9-19')insert into Student(s_no, class_no, s_name, s_sex, s_birthday) values('002102', 'xx0001' , '李王', '男', '1980-9-23')GOGOinsert 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', '信息', '信息系')GoGoGoinsert 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')Goinsert into Choice(s_no, course_no, score)values('991101', '01001', '88.0')insert into Choice(s_no, course_no)values('991102', '01001')insert 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')GoGoinsert 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', '助教')GoGoinsert into Teaching(course_no, t_no)values('01001', '000001')insert into Teaching(course_no, t_no)values('01002', '000002')insert into Teaching(course_no, t_no)values('01003', '000002')insert into Teaching(course_no, t_no)values('02001', '000003')insert into Teaching(course_no, t_no)values('02002', '000004')insert into Teaching(course_no, t_no)values('01001', '000005')insert into Teaching(course_no, t_no)values('01002', '000006')insert into Teaching(course_no, t_no)values('01003', '000007')insert into Teaching(course_no, t_no)values('02001', '000007')insert into Teaching(course_no, t_no)values('02002', '000008')Go

实验：

use test
select * from Student
--2.1select s_no,s_name from Student
--2.2select s_no,s_name,s_birthday from Student
where s_sex = '男'
--2.3select s_no,s_name,s_sex,s_birthday from Student
where s_birthday < '1980/01/01' and s_sex = '女'
--2.4select s_no,s_name,s_sex,s_birthday from Student
where s_name like '李%'
--2.5select s_no,s_name from Student
where s_name like '%一%'
--2.6select t_no,t_name,t_title from Teacher
where t_title <> '讲师'
--2.7select s_no from Choice
where score = null
--2.8select s_no,score from Choice
where score < '60.0'
order by score desc
--2.9select course_no,course_name from Course
where  course_no in ('01001','02001','02003')
--2.10select t_no,t_name,t_birthday from Teacher
where t_birthday >= '1970/01/01' and t_birthday <= '1970/12/31'
--2.11select course_no,count(distinct s_no) from Choice
group by  course_no
--2.12select  AVG(score),min(score),max(score) from Choice
where course_no = '01001'
--2.14select t_name,t_birthday from Teacher
where t_birthday >'1970/1/1'
order by t_birthday asc
--2.15