4.22 数据库 课堂作业 老师，学生，成绩，课程

create database s000
create table student
(
sno int  primary key  not null,--学生的学号 number的简称
sname varchar(50) not null,--学生的名字
ssex varchar(50) not null,--学生的性别
sbirthday date ,--学生的出生年月
class varchar(50), --学生所在的班级
)

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);

go
select*from student


create table course--创建一个课程表
(
cno varchar(50)primary key  not null,--课程号
cname varchar(50) not null,--课程的名字
tno varchar(50) references teacher(tno) --教工编号 外键受主键约束
)
go
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
select *from course


create table score--创建一个成绩表
(
sno int references student (sno) not null,--学号 
cno varchar(50) references course(cno) not null,--课程号
degiee decimal(4,1)--成绩

)
go
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);
alter table score add code int identity(1,1)
alter table score drop column code

go
select *from score
create table teacher
(
tno varchar(50) primary key not null,--创建一个老师的主码
tname varchar(50) not null,--名字
tsex varchar(50) not null,--性别
tbirthday date ,--老师的生日
Prof varchar(50),--教师的职称
depart varchar(50)--教师所在的部门
)
go
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
select * from teacher
select * from teacher
select * from course
select * from student
select * from score

--1、查询学生表中的名字,性别和班级
select sname ,ssex,class from student 
--2.--把教师表中部门不重复的表现出来
select distinct depart from teacher
--4、 查询Score表中成绩在60到80之间的所有记录
select *from score where degiee between 60 and 80
--5、 查询Score表中成绩为85，86或88的记录。
select *from score where degiee 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 ,degiee desc
--9、 查询“95031”班的学生人数。  聚合函数;针对数据列,计算求和,或者计算等一系列算数型
select *from student where class=95031
--10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序）☆

--select *from score where degiee=92 and cno='3-105'
select top 1 *from score order by degiee desc
--11、 查询每门课的平均成绩。
select AVG(degiee) from score group by cno--group by 分组 avg 求平均分


--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
 
select cno, avg(degiee) as xxx from score where cno like'3%' group by cno having count(*)>=3 order by xxx
--13、查询分数大于70，小于90的Sno列。
select sno from score where degiee between 70 and 90--找出四个表中分数大于70,小于90的分数

--14、查询所有学生的Sname、Cno和Degree列。
--select *from score
--select sname ,cno,degiee from student ,score where student.sno=score.sno--笛卡尔积方法,一定要是主外键
--用主外键关系进行筛选,用逗号分开两个表,形成笛卡尔积方法,进行where筛选 
--笛卡尔积  用两个新表合成一个表.   十条以上不要用笛卡尔积
select sname,cno,degiee from score
join student on score.sno=student.sno
--join ...on...

--15、查询所有学生的Sno、Cname和Degree列。
select *from score
select *from course
select sno,cname,degiee from score
join course  on score.cno=course.cno


--16、查询所有学生的Sname、Cname和Degree列。
select *from score--找出列的表
select *from student
select *from course
select sname,cname,degiee from score
join student on student.sno=score.sno
join course on course.cno=score.cno




--17、 查询“95033”班学生的平均分。
select avg(degiee)as '平均分' from student,score 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')
select *from grade
--现查询所有同学的Sno、Cno和rank列。
select *from score--成绩
select *from grade--等级
select sno,cno,RANK from score
 join grade on degiee between low and upp order by rank

--把分数变成等级  分数在什么什么之间为A，B，C,D,在low和upp中间 排序 rank

--（2）select sno,cno,(select RANK from grade where score,degiee between low and upp)as order by rank

 

--19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。


select *from score where degiee >(select degiee from score where sno=109 and cno ='3-105') and cno='3-105'




--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

select *from score where sno in (select sno from score group by sno having COUNT(*)>1)
 --count(*)>1 里面的数量有两个以上的符合条件，重复
and degiee not in
(select max(degiee) from score where sno in (select sno from score group by sno having count(*)>1))
--查询最高分学生之外的其他学生的信息
select *from score a where degiee not in
(select MAX(degiee) from score b where b.cno=a.cno)

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



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



--23、查询“张旭“教师任课的学生成绩。
select * from teacher where tname='张旭'
select *from course
select *from score
select  degiee from score
join course on course.cno=score.cno
join teacher on course.tno=teacher.tno where teacher.tname='张旭'

--24、查询选修某课程的同学人数多于5人的教师姓名。
select *from course
select *from score
select *from teacher
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
select *from student where class in ('95033,95031' )





--26、  查询存在有85分以上成绩的课程Cno.
select distinct cno from score where degiee>=85

--distinct--去重


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




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

--查出职称和系来自于老师的表职称不在老师表中的职称是电子工程系和是计算机系的老师的名字和职称。

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select *from score
select *from student
select cno,sno,degiee from score where degiee>(select min(degiee) from score where cno='3-245')and cno='3-105'order by degiee desc
--至少成绩高于--寻找里面最小的,有一个高于就可以 用 min()
--all--大于所有的值
--any--大于某一个值
--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

select *from score
select *from student
select cno,sno,degiee from score where degiee>(select MAX(degiee) from score where cno='3-245')and cno='3-105'

---成绩高于--寻找里面最大的,寻找最高分才可以 用 max()

--31、 查询所有教师和同学的name、sex和birthday.

select *from teacher
select *from student

select tname,tsex,tbirthday from teacher 
union--吧两个相同数据的类型的表联接起来
select sname,ssex,sbirthday from student



--32、查询所有“女”教师和“女”同学的name、sex和birthday.

select *from teacher
select *from student
select tname,tsex,tbirthday from teacher  where tsex='女'
union
select sname,ssex,sbirthday from student where ssex='女'
--33、 查询成绩比该课程平均成绩低的同学的成绩表。
select *from score
select *from course
select cno,degiee from score a where degiee <(select AVG(degiee) from score b where b.cno= a.cno) 
--34、 查询所有任课教师的Tname和Depart.
select *from teacher
select *from course
select *from score

select tname,depart from teacher where tno  in(select tno from course where cno not in(select distinct Cno from course))--所有任课老师的

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

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


--37、查询Student表中不姓“王”的同学记录。

select *from student where sname not in(select sname from student where sname like'王%')



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