【SQL Server】综合练习

齐天大圣12138

已于 2022-04-07 23:10:02 修改

阅读量440

点赞数

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

于 2022-04-07 22:27:00 首次发布

本文链接：https://blog.csdn.net/weixin_52078305/article/details/124028256

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

数据：

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) --系别
)
GO

CREAtE TABLE Course(
	course_no char(5) primary key, --课程号
	course_name char(20) not null, --课程名称
	course_score numeric(6,2) --学分
)

GO

CREATE TABLE Choice(
	s_no char(6), --学号
	course_no char(5), --课程号
	score numeric(6,2) --成绩
)

GO

CREATE 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) --职称
) 

GO

Create table Teaching(
	course_no char(5), --课程号
	t_no char(6) --教师号
)

GO

insert 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')

GO

GO

insert 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', '信息', '信息系')

Go


Go

Go

insert 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')

Go



insert 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')

Go


Go


insert 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', '助教')

Go


Go

insert 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.1

select s_no,s_name from Student
--2.2

select s_no,s_name,s_birthday from Student
where s_sex = '男'
--2.3

select s_no,s_name,s_sex,s_birthday from Student
where s_birthday < '1980/01/01' and s_sex = '女'
--2.4

select s_no,s_name,s_sex,s_birthday from Student
where s_name like '李%'
--2.5

select s_no,s_name from Student
where s_name like '%一%'
--2.6

select t_no,t_name,t_title from Teacher
where t_title <> '讲师'
--2.7

select s_no from Choice
where score = null
--2.8

select s_no,score from Choice
where score < '60.0'
order by score desc
--2.9

select course_no,course_name from Course
where  course_no in ('01001','02001','02003')
--2.10

select t_no,t_name,t_birthday from Teacher
where t_birthday >= '1970/01/01' and t_birthday <= '1970/12/31'
--2.11

select course_no,count(distinct s_no) from Choice
group by  course_no
--2.12



select  AVG(score),min(score),max(score) from Choice
where course_no = '01001'
--2.14

select t_name,t_birthday from Teacher
where t_birthday >'1970/1/1'
order by t_birthday asc
--2.15

齐天大圣12138

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
【SQL Server】综合练习

数据：use test;GOCREATE 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 --出生日期)GOCREATE TABLE Class( clas
复制链接

扫一扫

专栏目录