数据库练习

--表（一）Student (学生表)
--属性名 数据类型 可否为空 含 义
--Sno Char(3) 否 学号（主码）
--Sname Char(8) 否 学生姓名
--Ssex Char(2) 否 学生性别
--Sbirthday datetime 可 学生出生年月
--Class Char(5) 可 学生所在班级
create database ceshi
go
use ceshi
go
create table Student
(
Sno Char(3) primary key not null, -- 学号
Sname Char(8) not null,--学生姓名
Ssex Char(2)not null,--性别
Sbirthday datetime ,--出生年月日
Class Char(5)--所在班级
)
go

--表（二）Course（课程表）
--属性名 数据类型 可否为空 含 义
--Cno Char(5) 否 课程号（主码）
--Cname Varchar(10) 否 课程名称
--Tno Char(3) 否 教工编号（外码）
create table Course
(
Cno Char(5) primary key not null,--课程号
Cname Varchar(10 )not null,--课程名称
Tno Char(3)not null--教工编号 外
)
go

--表（三）Score(成绩表)
--属性名 数据类型 可否为空 含 义
--Sno Char(3) 否 学号（外码）
--Cno Char(5) 否 课程号（外码）
--Degree Decimal(4,1) 可 成绩
--主码：Sno+ Cno
create table Score
(
Sno Char(3)not null,--学号 外
Cno Char(5)not null,--课程号 外
Degree Decimal(4,1)--成绩

)
go

--表（四）Teacher(教师表)
--属性名 数据类型 可否为空 含 义
--Tno Char(3) 否 教工编号（主码）
--Tname Char(4) 否 教工姓名
--Tsex Char(2) 否 教工性别
--Tbirthday datetime 可 教工出生年月
--Prof Char(6) 可 职称
--Depart Varchar(10) 否 教工所在部门

create table Teacher
(Tno Char(3)primary key not null,--主 教师编号
Tname Char(4)not null,--教师姓名
Tsex Char(2)not null,--教师性别
Tbirthday datetime,--生日
Prof Char(6),--职称
Depart Varchar(10)--所在部门

)
go
--表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

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

--表（二）Course
--Cno Cname Tno
--3-105 计算机导论 825
--3-245 操作系统 804
--6-166 数字电路 856
--9-888 高等数学 831
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')
go

--表（三）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
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)
go
--表（四）Teacher
--Tno Tname Tsex Tbirthday Prof Depart
--804 李诚 男 1958-12-02 副教授 计算机系
--856 张旭 男 1969-03-12 讲师 电子工程系
--825 王萍 女 1972-05-05 助教 计算机系
--831 刘冰 女 1977-08-14 助教 电子工程系
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','刘冰','女','1977-08-14','助教','电子工程系')
go

--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表中成绩在60到80之间的所有记录。
select *from Score where degree between 60 and 80
--5、 查询Score表中成绩为85，86或88的记录。
select *from Score where degree in (85,86,88)
--6、 查询Student表中“95031”班或性别为“女”的同学记录。
select *from student where class='95031' or ssex='女'
--7、 以Class降序查询Student表的所有记录。
select *From student order by class desc
--8、 以Cno升序、Degree降序查询Score表的所有记录。
select *from score order by Cno,Degree desc
--9、 查询“95031”班的学生人数。--聚合函数：针对数据列，计算求和或者计数等一系列算术性操作
select count(*) from student where class='95031'
--sum(),avg(),max(),min()
select MAX(Degree) as maxfen,MIN(Degree) minfen from Score where Cno='3-105'
--10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
select sno ,cno from score where Degree = (select MAX(degree) from score)
select top 1 sno ,cno from Score order by Degree desc
--11、 查询每门课的平均成绩。--当分组和聚合结合的时候,先分组,然后对每一组分别进行聚合
select AVG(degree) as 平均成绩,cno from Score group by cno
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno,avg(degree) as degreess from score where Cno like '3%'
group by cno having COUNT(*)>=5
--13、查询分数大于70，小于90的Sno列。
select sno from Score where degree>70 and degree <90
--14、查询所有学生的Sname、Cno和Degree列。
select sname,cno,degree from student,score where student.Sno=Score.Sno
--
select sname,cno,DEGREE from score
inner join student on Score.Sno=student.Sno
--15、查询所有学生的Sno、Cname和Degree列。
select sno,cname ,degree from score join course on score.cno=course.cno
--
select sno,(select cname from course where score.cno=course.cno) as 课程名,DEGREE from score
--16、查询所有学生的Sname、Cname和Degree列
select sname,cname,DEGREE from Score
join student on Student.Sno=Score.Sno
join Course on Course.Cno=Score.Cno

--17、 查询“95033”班学生的平均分。
select avg(DEGREE) from Score where Sno in(select sno from Student where class='95033')

--18、 假设使用如下命令建立了一个grade表：
create table grade(low int,upp int,rank char(1))
insert into grade values(90,100,'A')
insert into grade values(80,89,'B')
insert into grade values(70,79,'C')
insert into grade values(60,69,'D')
insert into grade values(0,59,'E')
--现查询所有同学的Sno、Cno和rank列。
select sno,cno,rank from Score join grade on Degree between low and upp--自带排序功能

select sno,cno, (select RANK from grade where score.degree between low and upp) as 级别 from score

--19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from Student where Sno in (select Sno from Score where Cno='3-105' and Degree >
(select Degree from Score where Cno='3-105' and Sno='109'))

--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
--1.查询学号出现两次及以上的学号出来
select sno from Score group by Sno having COUNT(*)>=2
--2.查询步骤1中的学号的人的分数
select *from Score where Sno in (select sno from Score group by Sno having COUNT(*)>=2)
--3.查询所有人的分数最高分出来
select max(degree) from Score where Sno in (select sno from Score group by Sno having COUNT(*)>=2)
--4.从步骤2的结果中剔除最高分即步骤3的
select *from Score where Sno in
(select sno from Score group by Sno having COUNT(*)>=2)
and Degree !=(select max(degree) from Score )

--21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select *from Score where Degree > (select Degree from Score where Cno='3-105' and Sno='109')

--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno, sname, sbirthday from student where YEAR(Sbirthday) =
(select year(Sbirthday) from Student where Sno='108')

--时间日期函数：
YEAR(时间日期列) ： 取年份
month():取月份值
day():取日期值

--23、查询“张旭“教师任课的学生成绩。
select *from score where Cno in
(select Cno from Course where Tno in
(select Tno from Teacher where Tname='张旭'))

--24、查询选修某课程的同学人数多于5人的教师姓名。
select tname from Teacher where Tno in
(select Tno from Course where Cno in
(select Cno from Score group by Cno having COUNT(*)>5))

--25、查询95033班和95031班全体学生的记录。
select *from student where class in ('95033','95031')
select *from Score where Sno in (select sno from student where class in ('95033','95031'))
select * from Student,Score where class in ('95033','95031') and Student.Sno=Score.sno
--26、 查询存在有85分以上成绩的课程Cno.
select cno from Score where Degree>=85 group by Cno

--27、查询出“计算机系“教师所教课程的成绩表。
select *from score where cno in (
select cno from course where tno in (
select tno from teacher where depart='计算机系'))

--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select tname,prof from teacher where prof not in
(select prof from teacher where depart = '电子工程系'and prof in(select prof from teacher where depart = '计算机系') )
and depart in ('计算机系','电子工程系')

--查询两个系中相同职称的职称名称
select prof from teacher where depart = '电子工程系' and prof in(select prof from teacher where depart = '计算机系')

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select *from score where cno='3-105' and degree>(select MIN(degree) from score where cno='3-245') order by degree desc
select *from score where cno='3-105' and degree >any(select degree from score where cno='3-245')order by degree desc

--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select *from score where cno='3-105' and degree >all(select degree from score where cno='3-245')

--31、 查询所有教师和同学的name、sex和birthday.
select sname,ssex,sbirthday from student
union
select tname,tsex,tbirthday from teacher
--注意:列数要对应,列的类型要按先后顺序对应

--32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname,ssex,sbirthday from student where ssex='女'
union
select tname,tsex,tbirthday from teacher where tsex='女'

--33、 查询成绩比该课程平均成绩低的同学的成绩表。--相关子查询
select * from score a where a.Degree<
(select AVG(degree) from score b where b.cno=a.Cno)

select * from Score where
(Degree<(select top 1 AVG(Degree) from Score group by Cno) and cno=(select top 1 Cno from Score group by Cno))
or (Degree<(select top 1 AVG(Degree) from Score where Cno =(select top 1 Cno from Score where Cno not IN(select top 1 Cno from Score group by Cno) group by cno) group by Cno) and cno=(select top 1 Cno from Score where Cno =(select top 1 Cno from Score where Cno not in(select top 1 Cno from Score group by Cno) group by cno) group by Cno))
or (Degree<(select top 1 AVG(Degree) from Score where Cno =(select top 1 Cno from Score where Cno not IN(select top 2 Cno from Score group by Cno) group by cno) group by Cno) and cno=(select top 1 Cno from Score where Cno =(select top 1 Cno from Score where Cno not in(select top 2 Cno from Score group by Cno) group by cno) group by Cno))

--34、 查询所有任课教师的Tname和Depart.
select tname,depart from teacher where tno in (select distinct tno from course)

--35 、 查询所有未讲课的教师的Tname和Depart.
select tname,depart from teacher where tno not in (select distinct tno from course)

--36、查询至少有2名男生的班号。
select class From student where ssex='男' group by class having COUNT(*)>=2

--37、查询Student表中不姓“王”的同学记录。
select *from student where sname not like '王%'

--38、查询Student表中每个学生的姓名和年龄。
select sname,YEAR(GETDATE())-year(sbirthday) as 年龄 from student

--39、查询Student表中最大和最小的Sbirthday日期值。
select MAX(sbirthday) ,MIN(sbirthday) from student

--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select *from student order by class desc,sbirthday asc
--41、查询“男”教师及其所上的课程。
select *from course join teacher on course.tno=teacher.tno where tsex='男'

--42、查询最高分同学的Sno、Cno和Degree列。
select *from score where degree = (select MAX(degree) from score)

--43、查询和“李军”同性别的所有同学的Sname.
select sname from student where ssex in (select ssex from student
where sname='李军')

--44、查询和“李军”同性别并同班的同学Sname.
select sname from student where ssex in (select ssex from student
where sname='李军') and class in (select class from student
where sname='李军')
--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select *from score where sno in (select sno from student where ssex = '男') and
cno in (select cno from course where cname='计算机基础')

select *From student
select *From teacher
select *From score
select *From course
update teacher set depart='计算机系' where tno='888'