一、 设有一数据库,包括四个表:学生表(Student)、课程表(Course)、成绩表(Score)以及教师信息表(Teacher)。四个表的结构分别如表1-1的表(一)~表(四)所示,数据如表1-2的表(一)~表(四)所示。用SQL语句创建四个表并完成相关题目。
表1-1数据库的表结构
表(一)Student
属性名
数据类型
可否为空
含 义
Sno
Char(3)
否
学号(主码)
Sname
Char(4)
否
学生姓名
Ssex
Char(2)
否
学生性别
Sbirthday
datetime
可
学生出生年月
Class
Char(5)
可
学生所在班级
表(二)Course
属性名
数据类型
可否为空
含 义
Cno
Char(5)
否
课程号(主码)
Cname
Varchar(10)
否
课程名称
Tno
Char(3)
否
教工编号(外码)
表(三)Score
属性名
数据类型
可否为空
含 义
Sno
Char(3)
否
学号(外码)
Cno
Char(5)
否
课程号(外码)
Degree
Decimal(4,1)
可
成绩
主码:Sno+ Cno
表(四)Teacher
属性名
数据类型
可否为空
含 义
Tno
Char(3)
否
教工编号(主码)
Tname
Char(4)
否
教工姓名
Tsex
Char(2)
否
教工性别
Tbirthday
datetime
可
教工出生年月
Prof
Char(6)
可
职称
Depart
Varchar(10)
否
教工所在部门
表1-2数据库中的数据
表(一)Student
Sno
Sname
Ssex
Sbirthday
class
108
曾华
男
1977-09-01
95033
105
匡明
男
1975-10-02
95031
107
王丽
女
1976-01-23
95033
101
李军
男
1976-02-20
95033
109
王芳
女
1975-02-10
95031
103
陆君
男
1974-06-03
95031
表(二)Course
Cno
Cname
Tno
3-105
计算机导论
825
3-245
操作系统
804
6-166
数字电路
856
9-888
高等数学
831
表(三)Score
Sno
Cno
Degree
103
3-245
86
105
3-245
75
109
3-245
68
103
3-105
92
105
3-105
88
109
3-105
76
101
3-105
64
107
3-105
91
108
3-105
78
101
6-166
85
107
6-166
79
108
6-166
81
表(四)Teacher
Tno
Tname
Tsex
Tbirthday
Prof
Depart
804
李诚
男
1958-12-02
副教授
计算机系
856
张旭
男
1969-03-12
讲师
电子工程系
825
王萍
女
1972-05-05
助教
计算机系
831
刘冰
女
1977-08-14
助教
电子工程系
==================================================
create database test
go
use test
go
create table student
(
--学号
sno
varchar(3) not null primary key,
--姓名
sname
varchar(4) not null,
--性别
ssex
varchar(2) not null,
--出生年月
sbirthday
datetime,
--所在班级
class
varchar(5)
)
go
create table teacher
(
--教工编号
tno
varchar(3) not null primary key,
--教工姓名
tname
varchar(4) not null,
--教工性别
tsex
varchar(2) not null,
--教工出生日期
tbirthday
datetime,
--职称
prof
varchar(6),
--所在部门
depart
varchar(10)
)
go
create table course
(
--课程号
cno
varchar(5) not null primary key,
--课程名称
cname
varchar(10) not null,
--教工编号
tno
varchar(3) references teacher(tno)
)
go
create table score
(
--学号
sno
varchar(3) not null references student(sno),
--课程号
cno
varchar(5) not null references course(cno),
--成绩
degree
decimal(4,1)
)
go
insert into student
values('108','曾华','男','1977-09-01','95033')
insert into student
values('105','匡明','男','1975-10-02','95031')
insert into student
values('107','王丽','女','1976-01-23','95033')
insert into student
values('101','李军','男','1976-02-20','95033')
insert into student
values('109','王芳','女','1975-02-10','95031')
insert into student
values('103','陆君','男','1974-06-03','95031')
insert into teacher
values('804','李诚','男','1958-12-02','副教授','计算机系')
insert into teacher
values('856','张旭','男','1969-03-12','讲师','电子工程系')
insert into teacher
values('825','王萍','女','1972-05-05','助教','计算机系')
insert into teacher
values('831','刘冰','女','1958-08-14','助教','电子工程系')
insert into course
values('3-105','计算机导论','825')
insert into course
values('3-245','操作系统','804')
insert into course
values('6-166','数字电路','856')
insert into course
values('9-888','高等数学','831')
insert into score
values('103','3-245','86')
insert into score
values('105','3-245','75')
insert into score
values('109','3-245','68')
insert into score
values('103','3-105','92')
insert into score
values('105','3-105','88')
insert into score
values('109','3-105','76')
insert into score
values('101','3-105','64')
insert into score
values('107','3-105','91')
insert into score
values('108','3-105','78')
insert into score
values('101','6-166','85')
insert into score
values('107','6-166','79')
insert into score
values('108','6-166','81')
--1、查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname,Ssex,Class from student
--2、查询教师所有的单位即不重复的Depart列。
select distinct depart from
teacher
--3、查询Student表的所有记录。
select *from student
--4、查询Score表中成绩在到之间的所有记录。
select degree from
score where degree >60 and
degree<80
--5、查询Score表中成绩为,或的记录。
select
degree from score where degree=85 or degree=86 or degree=88
--6、查询Student表中“”班或性别为“女”的同学记录。
select
ssex,class from student where ssex='女' or
class='95031'
--7、以Class降序查询Student表的所有记录。
select class from student order
by class desc
--8、以Cno升序、Degree降序查询Score表的所有记录。
select
cno,degree from score order by cno asc , degree desc
--9、查询“”班的学生人数。
select sname from student where
class='95031'
--10、查询Score表中的最高分的学生学号和课程号。
select sno,cno from score where degree='92'
(
select max(degree) from score
)
--11、查询‘-105’号课程的平均分。
select avg(degree) from score where cno='3-105'
--12、查询Score表中至少有名学生选修的并以开头的课程的平均分数。
select avg(degree)from score where cno=
(
select cno from score group by cno having
count(cno)>=5 and cno like '3%'
)
--13、查询最低分大于,最高分小于的Sno列。
select sno from score where degree >
70 and degree< 90
--14、查询所有学生的Sname、Cno和Degree列。
select student.sname,score.degree,score.cno from
score,student where score.sno=student.sno
,