SQL练习题<2>

首先是建立练习使用各种表格（这里使用的是SQL server建立的）：

use school;
-- 创建学生表
create table Student(
    Sno varchar(20),
    Sname varchar(20) not null,
    Ssex varchar(20) not null,
    Sbirthday datetime,
    Class varchar(20)    
);
-- 创建教师表
create table Teacher(
    Tno varchar(20),
    Tname varchar(20) not null,
    Tsex varchar(20) not null,
    Tbrithday datetime,
    Prof varchar(20),
    Depart varchar(20) not null

);
-- 创建课程表
create table Course(
    Cno varchar(20),
    Cname varchar(20) not null,
    Tno varchar(20) not null,
);
-- 创建成绩表
create table Score(
    Sno varchar(20) not null,
    Cno varchar(20) not null,
    -- 成绩
    Degree Decimal
);

--添加约束
--为学生表添加约束,把列Sno作为主键约束,把列Ssex添加检查约束
alter table Student
add constraint CK_Sno check(Sno is not null),  --为列Sno添加非空约束
    constraint PK_Sno primary key(Sno),        --为列Sno添加主键约束
    constraint CK_Ssex check(Ssex='男' or Ssex='女')  --为列Ssex添加检查约束，令列Ssex列只能输入男或女
--为课程表添加约束
alter table Course 
add constraint CK_Cno check(Cno is not null),
    constraint PK_Cno primary key(Cno), --为列Cno添加主键约束
    constraint UQ_Cname unique(Cname)  --为列Cname添加唯一约束


--为教师表格添加约束，为列Tno添加主键约束，为列Tsex添加检查约束,为列Depart添加外键约束
alter table Teacher 
add constraint CK_Tno check(Tno is not null),
    constraint PK_Tno primary key(Tno),  --为列Tno添加主键约束
    constraint CK_Tsex check(Tsex='男' or Tsex='女')  --为列Tsex添加检查约束

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','刘冰','女','1977-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('103','3-105','64');
-- insert into score values('105','3-105','91');
-- insert into score values('109','3-105','78');
-- 这三行数据在样例里面给出，但是主键重复了。- -，报错吓了我一跳
insert into score values('103','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81')
go

接下来是练习题及答案：

--1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname, Ssex, Class from Student
--2、 查询教师所有的单位即不重复的Depart列。
select distinct Depart,Tname 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 Ssex='女' or Class='95031'

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

--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、查询每门课的平均成绩，要按照课程分组group by，然后求没门课平均avg
select Cno, convert(numeric(18,2),avg(Degree)) 平均成绩 from Score
group by Cno
--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select Cno,avg(Degree) from Score
group by Cno
having  Cno like '3%' and Cno in (select Cno from Score group by Cno having count(Cno) > 2)
--Like模糊查询     3%以3开头    having只能跟在group by 后面
--13、查询分数大于70，小于90的Sno列。

select Sno from Score
where Degree between 70 and 90
--14、查询所有学生的Sname、Cno和Degree列。
select A.Sno,A.Degree,B.Sname from Score as A 
inner join Student as B
on A.Sno = B.Sno
--15、查询所有学生的Sno、Cname和Degree列。
select A.Sno,A.Degree,C.Cname from Score as A 
inner join Course as C
on A.Cno = C.Cno
go
--16、查询所有学生的Sname、Cname和Degree列。
select S.Sname,A.Degree,C.Cname from Score as A 
inner join Course as C
on A.Cno = C.Cno
inner join Student as S
on S.Sno=A.Sno
go
--17、 查询“95033”班学生的平均分。
select S.Class as 班级 ,avg(A.Degree) as 评分分 from Score as A
inner join Student as S
on S.Sno = A.Sno
group by S.Class

--建立新表grade表
create table grade 
(low  int,upp  int,ranks  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 * , (select ranks from grade where low < Degree and Upp >= Degree) as 评级
from Score
方法二
select A.* , G.ranks from Score as A 
inner join grade as G
on A.Degree between G.low and G.upp

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

--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
Select * from score a 
where degree <(select max(degree) from Score b where b.Cno=a.Cno) 
and Sno in (select Sno from Score group by Sno having count(*)>1)

--结合条件一定要写在子查询中,子查询内部设定的关联名称,只能在该子查询内部使用,也就是说内部可以看到外部,而
--外部看不到内部
--SQL是按照先内层子查询后补外层查询的顺序来执行的,这样,子查询执行结束后只会留下执行结果.

--22、查询和学号为105的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select * from Score AS A
inner join Student AS S
on A.Sno=S.Sno
where year(S.Sbirthday) = (select year(Sbirthday) from Student where Sno='105')
--23、查询“张旭“教师任的学生成绩。
select A.* from Teacher as T
inner join Course as C
on T.Tno=C.Tno
inner join Score AS A
on A.Cno=C.Cno
where T.Tname='张旭'

--24、查询选修某课程的同学人数多于5人的教师姓名。
select A.Cno,T.Tname,Count(A.Cno) from Teacher as T
inner join Course as C
on T.Tno=C.Tno
inner join Score AS A
on A.Cno=C.Cno
group by A.Cno,T.Tname

--25、查询95033班和95031班全体学生的记录。
select * from Student
where Class='95033' or Class='95031'
--26、  查询存在有85分以上成绩的课程Cno.
select Cno from Score 
where Degree > 85
group by Cno

--27、查询出“计算机系“教师所教课程的成绩表。
select A.* from Teacher as T
inner join Course as C
on T.Tno=C.Tno
inner join Score AS A
on A.Cno=C.Cno
where T.Depart='计算机系'


--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。?
select Prof,Depart from Teacher as a
where Prof not in (select Prof from Teacher as b where b.Depart <> a.Depart)

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的记录
select * from Score as A
inner join Student as S
on A.Sno = S.Sno
where Cno = '3-105' and Degree > (select Degree from Score as B where B.Sno=A.Sno and B.Cno='3-245')

--31、 查询所有教师和同学的name、sex和birthday.
select Sname, Ssex, convert(date,Sbirthday) from Student
union (select Tname,Tsex,convert(date,Tbrithday) from Teacher)

--32、查询所有“女”教师和“女”同学的name、sex和birthday.
select Sname as name, Ssex as sex, convert(date,Sbirthday) as birthday from Student
where Ssex='女'
union (select Tname,Tsex,convert(date,Tbrithday) from Teacher where Tsex='女')

--33、 查询成绩比该课程平均成绩低的同学的成绩表。
select * from Score as a
where Degree < (select avg(Degree) from Score as b where b.Cno=a.Cno group by b.Cno)

--34、查询所有任课教师的Tname和Depart.
select Tname,Depart from Teacher 
--35 、查询所有未讲课的教师的Tname和Depart.
select * from Teacher 
where Tno not in (select Tno from Course where Cno in (select Cno from Score))
--36、查询至少有2名男生的班号。
select Class from Student
where Ssex='男'
group by Class
having Count(Class)>=2
--37.查询不姓王的同学信息
select * from Student
where Sname not like '王%'
--38、查询Student表中每个学生的姓名和年龄。
select Sname,Ssex from Student
--39、查询Student表中最大和最小的Sbirthday日期值。
select max(Sbirthday) as 最大日期值,min(Sbirthday) as 最小日期值 from Student
--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from Student
order by Class,Sbirthday desc
--41、查询“男”教师及其所上的课程。
select T.Tname, C.Cno from Teacher as T
inner join Course as C
on T.Tno=C.Tno
where T.Tsex='男'
--42、查询最高分同学的Sno、Cno和Degree列。
select * from Score
where Degree =(select max(Degree) from Score )
--43、查询和“李军”同性别的所有同学的Sname.
select Sname from Student
where Ssex=(select Ssex from Student where Sname='李军') and Sname <> '李军'
--44、查询和“李军”同性别并同班的同学Sname.

select Sname from Student
where Ssex=(select Ssex from Student where Sname='李军') and Sname <> '李军' 
and Class = (select Class from Student where Sname='李军')
--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。SQL语句
select * from Score
where Sno in (select Sno from Student where Ssex='男') and Cno in (select Cno from Course where Cname='计算机导论')

--46,查询" 3-245 "课程比" 3-105 "课程成绩高的学生的信息及课程分数
select * from Score as A
inner join Score as B
on A.Sno = B.Sno
where A.Cno='3-245' and B.Cno='3-105' and A.Degree < B.Degree
--47.查询各科成绩的最高分及最低分及格率，中等率，优良率，优秀率

--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

--要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select Cno as 科目, Max(Degree) as 最高分, MIn(Degree) as 最低分, convert(int,avg(Degree)) as 平均分 ,
convert(varchar(20),convert(numeric(10,2),convert(numeric(10,2),sum(case when Degree >= 60 then 1 else 0 end))/Count(Cno)*100)) + '%' as 及格率,
convert(varchar(20),convert(numeric(10,2),convert(numeric(10,2),sum(case when Degree >= 70 and Degree <80 then 1 else 0 end))/Count(Cno)*100)) + '%' as 中等率,
convert(varchar(20),convert(numeric(10,2),convert(numeric(10,2),sum(case when Degree >=80 and Degree < 90 then 1 else 0 end))/Count(Cno)*100)) + '%' as 优良率,
convert(varchar(20),convert(numeric(10,2),convert(numeric(10,2),sum(case when Degree >= 60 then 1 else 0 end))/Count(Cno)*100) )+ '%' as 优秀率
from Score 
group by Cno
order by count(Cno),Cno
--48.按各科成绩进行排序，并显示排名， degree 重复时保留名次空缺
select * , rank() over(partition by Cno order by Degree) as 排名 from Score

--49查询各科成绩前两名的记录
select * from(select * , rank() over(partition by Cno order by Degree) as 排名 from Score) as A
where A.排名 <=2
--50查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select S.Sno,A.* from Student as S
left join Score as A
on S.Sno=A.Sno
--51.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select * from Score as A
inner join Score as B
on A.Sno = B.Sno
where A.Cno <> B.Cno and A.Degree = B.Degree