数据：
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
