数据库基础的一些题型

   在从事开发，关于数据库开发面试的常考的题型

题型

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
6、查询"李"姓老师的数量
7、查询学过"张三"老师授课的同学的信息
8、查询没学过"张三"老师授课的同学的信息
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
11、查询没有学全所有课程的同学的信息
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
16、检索"01"课程分数小于60，按分数降序排列的学生信息
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
18、查询每门课程被选修的学生数
19、查询出只有两门课程的全部学生的学号和姓名
20、查询男生、女生人数
21、查询名字中含有"风"字的学生信息
22、查询同名同姓学生名单，并统计同名人数
23、查询1990年出生的学生名单
24、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
25、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
26、查询课程名称为"数学"，且分数低于60的学生姓名和分数
27、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
28、查询不及格的课程
29、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
30、求每门课程的学生人数
31、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
32、查询每门功成绩最好的前两名
33、检索至少选修两门课程的学生学号
34、查询选修了全部课程的学生信息

 

建表语句

create database school;

use school;

学生表

create table student(
    s_id varchar(10),
    s_name varchar(20),
    s_age date,
    s_sex varchar(10)
);

课程表

create table course(
    c_id varchar(10),
    c_name varchar(20),
    t_id varchar(10)
);

老师表
create table teacher (
t_id varchar(10),
t_name varchar(20)
);

成绩表

create table score (
    s_id varchar(10),
    c_id varchar(10),
    score varchar(10)
);

insert into student (s_id, s_name, s_age, s_sex)
values  ('01' , '赵雷' , '1990-01-01' , '男'),
        ('02' , '钱电' , '1990-12-21' , '男'),
        ('03' , '孙风' , '1990-05-20' , '男'),
        ('04' , '李云' , '1990-08-06' , '男'),
        ('05' , '周梅' , '1991-12-01' , '女'),
        ('06' , '吴兰' , '1992-03-01' , '女'),
        ('07' , '郑竹' , '1989-07-01' , '女'),
        ('08' , '王菊' ' '1990-01-20' , '女');

insert into course (c_id, c_name, t_id)
values  ('01' , '语文' , '02'),
        ('02' , '数学' , '01'),
        ('03' , '英语' , '03');

insert into teacher (t_id, t_name)
values  ('01' , '张三'),
        ('02' , '李四'),
        ('03' , '王五');

insert into score (s_id, c_id, score)
values  ('01' , '01' , 80),
        ('01' , '02' , 90),
        ('01' , '03' , 99),
        ('02' , '01' , 70),
        ('02' , '02' , 60),
        ('02' , '03' , 80),
        ('03' , '01' , 80),
        ('03' , '02' , 80),
        ('03' , '03' , 80),
        ('04' , '01' , 50),
        ('04' , '02' , 30),
        ('04' , '03' , 20),
        ('05' , '01' , 76),
        ('05' , '02' , 87),
        ('06' , '01' , 31),
        ('06' , '03' , 34),
        ('07' , '02' , 89),
        ('07' , '03' , 98);

#1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
#1
select * from student RIGHT JOIN
 (SELECT a.s_id,a.score 01_score,b.score 02_score
  from (select * from score where score.c_id='01') as a,
 (SELECT * from score where score.c_id='02') as b where a.s_id=b.s_id and a.score>b.score) r
 on student.s_id=r.s_id

#2
select a.s_id,a.score from
 (select * from score where c_id = '01') as a,
 (select * from score where c_id='02') as b
 where a.s_id = b.s_id and a.score > b.score;

#2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.s_id,a.score from
 (select * from score where c_id = '01') as a,
 (select * from score where c_id='02') as b
 where a.s_id = b.s_id and a.score < b.score;

 

#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select stu.s_id as '编号',stu.s_name as '姓名',avg(sc.score) as '平均成绩'
from student stu
inner join score sc
on stu.s_id = sc.s_id
group by stu.s_id having avg(sc.score) > 60;

#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select stu.s_id as '编号',stu.s_name as '姓名',avg(sc.score) as '平均成绩'
from student stu
inner join score sc
on stu.s_id = sc.s_id
group by stu.s_id having avg(sc.score) < 60;

 

#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select
   stu.s_id,stu.s_name,
   count(c.c_id) as sun_course,
   sum(sco.score) as sum_score
from
   student stu
inner join score sco on
   sco.s_id = stu.s_id
inner join course c on
   c.c_id = stu.s_id
group by
   sco.s_id;

 

#6、查询"李"姓老师的数量
select count(*) from teacher where t_name like "李%"

 

#7、查询学过"张三"老师授课的同学的信息
select s.* from
teacher t
inner join course c on t.t_id = c.t_id
inner join score sc on sc.c_id = c.c_id
inner join student s on s.s_id = sc.s_id
where t.t_name = '张三';

select * from student s
where s.s_id in(
 select sc.s_id from score sc
 where sc.c_id in(
  select c.c_id from course c
  where c.t_id = (
   select t.t_id from teacher t
   where t.t_name = '张三'
  )
 )
)
  

#8、查询没学过"张三"老师授课的同学的信息
select * from student s
where s.s_id not in(
 select sc.s_id from score sc
 where sc.c_id in(
  select c.c_id from course c
  where c.t_id = (
   select t.t_id from teacher t
   where t.t_name = '张三'
  )
 )
)

#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student
where s_id in(
  select sc1.s_id
  from score sc1,
    score sc2
  where
    sc1.s_id = sc2.s_id
    and sc1.c_id = '01'
    and sc2.c_id = '02'
);

#10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select s.*,sc1.score,sc2.score
from student s
left join (select * from score where c_id = '01') sc1 on s.s_id = sc1.s_id
left join (select * from score where c_id = '02') sc2 on s.s_id = sc2.s_id
where sc1.c_id = '01' and sc2.c_id is null;

 

#11、查询没有学全所有课程的同学的信息
select s.* from student s
left join Score s1 on s1.s_id=s.s_id
group by s.s_id
having count(s1.c_id)<(select count(*) from course) 

 

#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from student where s_id in(
  select distinct a.s_id from score a where a.c_id
    in(select a.c_id from score a where a.s_id='01')
  );

 

#13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student
where s_id in (select s_id from score t1 group by s_id
having group_concat(c_id) =
( select group_concat(c_id) as str2 from score
where s_id = '01')
and s_id != '01');

 

#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student s
where s.s_id not in(
 select sc.s_id from score sc
 where sc.c_id in(
  select c.c_id from course c
  where c.t_id = (
   select t.t_id from teacher t
   where t.t_name = '张三'
  )
 )
)

 

#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.s_id, student.s_name, AVG(score.score)
from student,score
where student.id = score.s_id and score.score<60
group by score.s_id
having count(*) > 1;

 

#16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.s_id,stu.s_name,stu.s_age,stu.s_sex,a.score
from
(select s_id,score
from score
where c_id='01' and score < 60 order by score desc) a
left join student stu on a.s_id=stu.s_id;

#17、information_schema按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select stu.* a from student stu
left join (select stu. s_id,avg(score) as a
from student group by s_id) r
on stu.s_id = r.s_id order by a desc;

 

#18、查询每门课程被选修的学生数
select c_id,count(s_id)'人数' from score
group by c_id

 

#19、查询出只有两门课程的全部学生的学号和姓名
 select stu.s_id 学号,stu.s_name 姓名
   from student stu
   inner join score sc
   on stu.s_id = sc.s_id
   group by sc.s_id
   having count(sc.c_id) = 2;

select * from student stu
where stu.s_id in(
  select sc.s_id from score sc
group by score.s_id
having count(score.c_id) = 2

#20、查询男生、女生人数
select a.s1,b.s2 FROM
(select count(*) as s1  from student where s_sex like '女')a,
(select count(*) as s2 from student where s_sex like '男')b

select s.s_sex,count(*) AS '人数' from student s
group by s.s_sex

#21、查询名字中含有"风"字的学生信息
select * from student where s_name like '%风%'

#22、查询同名同姓学生名单，并统计同名人数
Select s_name,count(*)  from Student
 group by s_name having count(*)>1

#23、查询1990年出生的学生名单
select s_name from student
 where year(s_age) = 1990;

 

#24、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号
select c.c_id,AVG(s.score) as a
from score s,course c
where s.c_id=c.c_id
group by c_id
order by a desc,c.c_id asc

 

#25、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select * from student stu
inner join score sc on stu.s_id = sc.s_id
group by stu.s_id having
 avg(sc.score) >= 85

 

#26、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select * from course c
inner join score sc on c.c_id = sc.c_id
inner join student stu on sc.s_id = stu.s_id
where c.c_name = '数学'

#27、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select stu.s_name,c.c_name,s.score
  from student stu
  left join score s on stu.s_id = s.s_id
  left join course c on s.c_id = c.c_id
  where s.score > 70
  order by s_name,c_name

#28、查询不及格的课程
select stu.s_name,c.c_name,s.score
 from student stu
 left join score s on stu.s_id = s.s_id
 left join course c on s.c_id = c.c_id
 where s.score < 60
 order by s_name,c_name;

 

#29、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select s.s_id,stu.s_name,s.score
from student stu
inner join score s using(s_id)
where s.s_id = '01' and s.score >= 80
 
#30、求每门课程的学生人数
select s1.c_id,count(*)'学生人数' from score s1
 GROUP BY s1.c_id

select cou.c_id,cou.c_name,count(*)'每科学生人数'
from course cou, score sc
where cou.c_id = sc.c_id
group by cou.c_id,cou.c_name;

#31、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select sc.*
from
score sc,
(select c_id,score from score group by c_id,score having count(1)>1) n
where
sc.c_id = n.c_id
and sc.score = n.score
order by sc.c_id,sc.score,sc.s_id;

#32、查询每门功成绩最好的前两名
select *
from score s
 where (select count(*) from score sc
        where sc.c_id = s.c_id
        and sc.score > s.score) < 2
 order by s.c_id

#33、检索至少选修两门课程的学生学号
select s_id from score group by s_id having count(*)>=2

#34、查询选修了全部课程的学生信息
select stu.* from student stu
 inner join score s using(s_id)
 group by 1,2,3,4
 having count(*)=(select count(*) from course);