常用数据库检索语句

create database DBMonth1
use DBMonth1
create table Student
(
    SID int primary key identity,
    Sname varchaR(50),
    Sage datetime,
    Ssex varchar(50)
)
create table Teacher
(
    TID int primary key identity,
    Tname varchar(50),
)
create table Course
(
    CID int primary key identity,
    Cname varchar(50),
    TID int foreign key references Teacher(TID)
)
create table Score
(
    ID int primary key identity,
    SID int foreign key references Student(SID),
    CID int foreign key references Course(CID),
    Scores int
)
insert into Student values( '赵雷' , '1990-01-01' , '男');
insert into Student values( '钱电' , '1990-12-21' , '男');
insert into Student values( '孙风' , '1990-05-20' , '男');
insert into Student values( '李云' , '1990-08-06' , '男');
insert into Student values( '周梅' , '1991-12-01' , '女');
insert into Student values( '吴兰' , '1992-03-01' , '女');
insert into Student values( '郑竹' , '1989-07-01' , '女');
insert into Student values( '王菊' , '1990-01-20' , '女');
select * from Student

insert into Teacher values( '秦红山');
insert into Teacher values( '李凡');
insert into Teacher values( '于彦强');

select * from Teacher

insert into Course values( '语文' , 2);
insert into Course values( '数学' , 1);
insert into Course values( '英语' , 3);

insert into Score values(1,1,80);
insert into Score values(1,2,56);
insert into Score values(1,3,88);
insert into Score values(2,1,60);
insert into Score values(2,2,70);
insert into Score values(2,3,90);
insert into Score values(3,1,95);
insert into Score values(3,2,86);
insert into Score values(3,3,99);
insert into Score values(4,1,77);
insert into Score values(4,2,44);
insert into Score values(4,3,88);
insert into Score values(5,1,66);
insert into Score values(5,2,85);
insert into Score values(5,3,88);
insert into Score values(6,1,83);
insert into Score values(6,2,88);
insert into Score values(6,3,83);
insert into Score values(7,2,40);
insert into Score values(8,3,90);
insert into Score values(8,1,30);

--1、查询"1"课程比"2"课程成绩高的学生的信息及课程分数
--答：
select a.*,b.Scores[课程1的分数], c.Scores[课程2的分数] from Student a,Score b,Score c
where a.SID=b.SID and b.SID=c.SID and b.CID=1 and c.CID =2 and b.Scores>c.Scores 
--.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况
--答
--1

--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
--答：
select a.Sname[学生姓名],cast(avg(b.Scores) AS decimal(18,2))[平均分] from Student a , Score b
where a.SID=b.SID
group by a.Sname having cast(avg(b.Scores) AS decimal(18,2))<60
--4、查询在sc表中不存在成绩的学生信息的SQL语句。
--答：
select a.Sname[学生姓名],cast(avg(b.Scores) AS decimal(18,2))[平均分] from Student a left join Score b
on a.SID=b.SID
group by a.Sname having cast(avg(b.Scores) AS decimal(18,2))<60
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
--答：
select a.Sname[姓名],count(b.CID)[选课总数],SUM(Scores)[总成绩] from Student a ,Score b
where a.SID=b.SID 
group by a.Sname

--6、查询"李"姓老师的数量
--答
select  count(Tname) [李姓老师的数量] from Teacher where Teacher.Tname like '李%'
--7、查询学过"秦红山"老师授课的同学的信息
--答：
select distinct Student.* from Student ,Score,Course,Teacher
where Student.SID=Score.SID and Score.CID=Course.CID and Course.TID=Teacher.TID and Teacher.Tname='秦红山'

--8、查询没学过"秦红山"老师授课的同学的信息
--答：
select Student.Sname[学生姓名] from Student where SID not in(
select distinct SID from Score,Course,Teacher 
where Score.CID=Course.CID and Course.TID=Teacher.TID and Teacher.Tname='秦红山')

--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
--答：
select Sname[学生姓名] from Student where SID IN(
select SID from (
select SID from Score where Score.CID=1
union all
select SID from Score where Score.CID=2) t group by SID having COUNT(*)=2
) 
--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
--答：正确的SID是8   这个不会

create view Score2 
as
select * from Score

select a.Sname[学生姓名] from Student a ,Score b where a.SID=b.SID and b.CID=1 and 
 not exists(
select distinct Score.SID from Score ,Score2   where Score.SID=Score2.SID and Score2.CID=2
)





--11、查询没有学全所有课程的同学的信息、
--答：
select Student.Sname[学生姓名] from Student ,Score
where Student.SID=Score.SID
group by Sname having count(CID)<(select count(CID) FROM Course)

--12、查询和"01"号的同学学习的课程完全相同的其他同学的信息
--答：错误
select Student.Sname[学生姓名] from Student where SID IN (
select * from Score where SID=1 and Score.CID in (select CID from Score where SID=1)
group by Score.SID having count(1)=(select count(*) from Score where SID=1))

--14、查询没学过"秦红山"老师讲授的任一门课程的学生姓名

select Student .* from Student where SID not in(
select Score.SID from Score ,Course,Teacher
where Score.CID=Course.CID and Course.TID=Teacher.TID and Teacher.Tname='秦红山')

--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select Student.Sname[学生姓名],cast(AVG(Score.Scores) as decimal(18,2))[平均成绩] from Student ,Score where Student.SID=Score.SID
and Student.SID in(
select SID from Score where Scores >60 group by Score.SID having
COUNT(1)>=2
) group by Student.Sname

--16、检索"01"课程分数小于60，按分数降序排列的学生信息

select a.* from Student a ,Score b where a.SID=b.SID and b.Scores<60 and b.CID=1
order by Scores DESC

--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select Student .Sname[学生姓名] ,
max(case Course.Cname when '语文' then Score.Scores else null end)[语文],
max(case Course.Cname when '数学' then Score.Scores else null end)[数学],
max(case Course.Cname when '英语' then Score.Scores else null end)[英语],
cast(avg(Score.Scores) as decimal(18,2)) [平均分]
from Student 
left join Score on Student.SID=Score.SID
left join Course on Score.CID=Course.CID
group by Student.Sname
order by [平均分] desc
--18、查询学生平均成绩及其名次

select  a.Sname[姓名] , cast(avg(b.Scores) as decimal(18,2))[平均分] ,ROW_NUMBER() over (order by cast(avg(b.Scores) as decimal(18,2)) desc ) as [名次]
 from Student a join Score b on a.SID=b.SID group by a.Sname

 select a.Sname[姓名],cast(avg(b.Scores) as decimal(18,2))[平均分],ROW_NUMBER() over(order by cast(avg(b.Scores) as decimal(18,2)) desc ) as [名次] from Student a , Score b where  a.SID=b.SID
 group by a.Sname
 --19、查询各科成绩前三名的记录

 --20、查询每门课程被选修的学生数
 select CID ,count(SID)[选择的人数] from Score group by CID
 --21、查询出只有两门课程的全部学生的学号和姓名
 select a.Sname[姓名] from Student a ,Score b where a.SID=b.SID 
 group by a.Sname 
 having count(b.CID)=2

 --28、查询男生、女生人数

 select count(Ssex) [男生人数] from Student where Ssex='男'
 select count(Ssex) [女生人数] from Student where Ssex='女'

 select 
 sum(case when Ssex='男' then 1 else 0 end )[男生人数] ,
  sum(case when Ssex='女' then 1 else 0 end )[女生人数] 
 from Student

 select case when Ssex='男' then '男生人数' else '女生人数' end [男女情况] ,count (1) [人数] from Student group by case when Ssex='男' then '男生人数' else '女生人数' end

 --29、查询名字中含有"风"字的学生信息
 select * from Student where Sname like '%'+'风'+'%'

 --30、查询同名同性学生名单，并统计同名人数
 select Sname[学生姓名],count(*) [人数] from Student group by Sname 
 having count(*)>1 

 --31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

 select *from Student where year(Sage)=1990

 select * from Student where datediff(yy,Sage,'1990-01-01')=0

 --32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

 select a.CID[课程编号],a.Cname[课程名称] ,cast(avg(b.scores) as decimal(18,2))[平均分] from Course a ,Score b
 where a.CID=b.CID
 group by a.CID,a.Cname
 order by [平均分] desc,a.CID asc


 --33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

 select a.SID [学生编号],a.Sname[学生姓名],cast(avg(b.Scores)as decimal(18,2))[平均分] from Student a,Score b
 where a.SID=b.SID
 group by a.SID,a.Sname
 having cast(avg(b.Scores)as decimal(18,2))>85

 --34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
 select a.Sname[学生姓名],b.Scores[分数] from Student a,Score b,Course c
 where a.SID=b.SID and b.CID=c.CID and c.Cname='数学' group by a.Sname,b.Scores  having b.Scores<60


 --36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

 select a.Sname[学生姓名],b.Scores[分数],c.Cname[课程名称] from Student a ,Score b,Course c
 where a.SID=b.SID and b.CID=c.CID
 group by a.Sname,b.Scores,c.Cname
 having b.Scores>70
 --37、查询不及格的课程
  select a.Sname[学生姓名],b.Scores[分数],c.Cname[课程名称] from Student a ,Score b,Course c
 where a.SID=b.SID and b.CID=c.CID
 group by a.Sname,b.Scores,c.Cname
 having b.Scores<60

 ----38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
 select a.Sname[学生姓名],b.Scores[分数] from Student a ,Score b
 where a.SID=b.SID and b.CID=1
 group by a.Sname,b.Scores
 having b.Scores>80

 --39、求每门课程的学生人数
 select a.CID[课程编号],a.Cname[课程名称] ,count(*)[学生人数] from Course a,Score b
 where a.CID=b.CID
 group by a.CID,a.Cname

--40、查询选修"秦红山"老师所授课程的学生中，成绩最高的学生信息及其成绩
select top 1 a.Sname [姓名],b.Scores[分数] from Student a,Score b,Course c ,Teacher d
where a.SID=b.SID and b.CID=c.CID and c.TID=d.TID and d.Tname='秦红山'
group by a.Sname,b.Scores
order by b.Scores desc


 --41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩



 --42、查询每门功成绩最好的前两名
 select a.CID,a.Scores from Score a where a.Scores in (select top 2 Scores from Score where Score.CID=a.CID order by Scores desc )  order by a.Scores,a.CID desc


 --44、检索至少选修两门课程的学生学号
 select a.SID,a.Sname from Student a ,Score b 
 where a.SID=b.SID
 group by a.SID,a.Sname
 having COUNT(b.CID)>=2

--45、查询选修了全部课程的学生信息