MySQL面试题 数据库MySQL经典面试题之SQL语句

1.根据emp数据表编写SQL查出所有name重复的记录且按照age降序。

+--------+--------+-----+

| emp_no | name   | age |

+--------+--------+-----+

|   1001 | 小明   |  18 |

|   1002 | 王强   |  20 |

|   1003 | 小雪   |  20 |

|   1004 | 小明   |  19 |

+--------+--------+-----+

 

select emp_no, name,age 
from emp 
where name in (
    select t.name from
     (    select name,count(name) as num 
         from emp 
         group by name 
         having num > 1
     )t
) 
order by age desc;

 （测试数据添加SQL见文章末尾）

1.学生表
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

2.课程表
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
3.教师表
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
4.成绩表
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数

1. 平均成绩及格了的学生

select avg(score) avg_score,SID 
from SC 
group by SID 
having avg_score >= 60;

2. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.SID,a.Sname,b.avg_score from Student a join  
(
    select avg(score) avg_score,SID 
    from SC 
    group by SID 
    having avg_score >= 60
) b on a.SID = b.SID;

3. 查询学过"张三"老师授课的同学的信息

select distinct a.SId,a.Sname,a.Sage,a.Ssex
from Student a
join SC b on a.SID = b.SID
join Course c on c.CID = b.CID
join Teacher d on d.TID = c.TID
where d.Tname = '张三';

4. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 

 --方法1

select m.* 
from Student m 
where SID in
(
    select SID from
    (
        select distinct SID from SC where CID = '01'
        union all
        select distinct SID from SC where CID = '02'
    ) t 
    group by SID 
    having count(1) = 2
)
order by m.SID

  --方法2

select Student.* 
from Student 
join SC on Student.SID = SC.SID
where CID = '02' 
and exists (select 1 from SC sc_2 where sc_2.SID = SC.SID and sc_2.CID='01');

  --方法3

select Student.* 
from Student 
join SC on Student.SID = SC.SID
where CID = '01' 
and exists (select 1 from SC sc_2 where sc_2.SID = SC.SID and sc_2.CID='02');

5. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select Student.* from Student 
join SC on Student.SID = SC.SID where CID = '01' 
and not exists (select 1 from SC sc_2 where sc_2.SID = SC.SID and sc_2.CID='02');

6. 查询两门及其以上不及格课程的同学的学号

select SID from SC where score < 60 group by SID having count(1) >= 2;

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

select Student.SID,Student.sname,avg(SC.score) as avg_score
from student join SC on student.SID = SC.SID
where Student.SID in(select SID from SC where score < 60 group by SID having count(1) >= 2) 
group by Student.SID,Student.sname;

查询没有学全所有课程的同学的信息

select Student.*
from Student left join SC
on Student.SID = SC.SID
group by Student.SID , Student.Sname , Student.Sage , Student.Ssex having count(CID) < (select count(CID) from Course)

附录》添加测试数据

1.学生表
create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

2.课程表
create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

3.教师表
create table Teacher(TID varchar(10),Tname nvarchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

4.成绩表
create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);