pta mysql 训练题集 (201-220)

10-201 在顾客表中查询青岛的顾客编号，公司名称和电话，结果按顾客编号升序排列

select 顾客编号,公司名称,电话
from 顾客
where 城市 = '青岛';

10-202 在顾客表中查询各个城市的顾客数目

select 城市,count(*) as 顾客数
from 顾客
group by 城市;

10-203 在订单表中查询运费的平均值

select avg(运费) as 平均运费 from 订单;

10-204 通过订单表和顾客表，查询订单编号，顾客编号，公司 名称和订单日期

select 订单编号,a.顾客编号,公司名称,订单日期
from 顾客 as a,订单 as b
where a.顾客编号=b.顾客编号;

10-205 在顾客表中查询出现了那些城市，要求不显示重复值

select distinct 城市 from 顾客;

10-206 在员工表中查询入职最晚的员工的编号，姓名和入职日期

select 员工编号,姓名,入职日期
from 员工
where 入职日期 = (select max(入职日期) from 员工);

10-207 在员工表中查询每年入职的员工人数

select 年份,count(*) as 入职人数
from
(
    select 姓名,year(入职日期) as 年份 from 员工
) as a
group by 年份

10-208 在订单表中查询011号员工和121号员工承办的订单信息

select * from 订单
where 员工编号 in ('011','121');

10-209 查询比“网络工程”专业所有学生年龄都小的学生姓名

select sname
from stu
where birdate >
(
    select max(birdate) from stu where mno=(select mno from major where  mname='网络工程')
);

10-210 查询软件工程专业中年龄最大的同学姓名

select sname
from stu
where birdate = (select min(birdate) from stu where mno = (select mno from major where mname = '软件工程'))
and mno = (select mno from major where mname = '软件工程');

10-211 查找课程选修的情况

select cou.cno as 课程号,cname as 课程名,count(sno) as 选课人数,ifnull(max(grade),0)  as 最高成绩,ifnull(min(grade),0) as 最低成绩,ifnull(avg(grade),0) as 平均成绩 
from cou
left join sc on sc.cno = cou.cno
group by cou.cno;

10-212 查询各专业的学生人数

select major.mno as 专业号,mname as 专业,count(sno) as 人数
from major
left join stu on major.mno=stu.mno
group by major.mno;

10-213 统计每个专业的男生与女生人数

select stu.mno as 专业号,mname as 专业名,(case sex when '0' then '女' when '1' then '男' end) as 性别,count(sno) as 人数
from major,stu
where major.mno=stu.mno
group by stu.mno,major.mname,sex
order by stu.mno

10-214 查询选修了“C语言”课程，但是没有选修“数据结构”课程的学生

select sname
from stu
where sno in (
    select sno
    from sc
    where cno =(select cno from cou where cname = 'C语言')
) and sno not in(
    select sno
    from sc
    where cno =(select cno from cou where cname = '数据结构')
)

10-215 查询计算机工程专业学生选修但软件工程专业学生没有选修的课程。

select cname
from cou
where cno in
(
select cno
from sc
where sc.cno in
    (
        select cno
        from stu,major
        where stu.mno = major.mno and major.mname = '计算机工程'
    )
and sc.cno not in 
    (
         select cno 
         from sc
         where sno in
        (
            select sno
            from stu,major
            where stu.mno = major.mno and major.mname = '软件工程'
        )
    )
)

10-216 查询选修课程超过2门且成绩都在80分以上的学生

select sname as 姓名,mno as 专业,学分 as 总学分
from stu,
(
    select sno,count(*),sum(credit) as 学分 
    from (select sno,sc.cno,credit from sc,cou where grade >= 80 and sc.cno = cou.cno ) as a
    group by sno
    having count(*) > 1
) as b
where stu.sno = b.sno;
--  题目要求有问题,这里要大于等于80

10-217 查询选修人数超过2人且成绩都在60分以上的课程

select cou.cno as 课程号,cname as 课程名,score_max as 最高成绩,score_min as 最低成绩,score_avg as 平均成绩
from cou,
(
    select cno , max(grade) as score_max,min(grade) as score_min,avg(grade) as score_avg
    from sc 
    group by cno
) as a
where cou.cno = a.cno
having 平均成绩 > 80

10-218 ignore-order-sample

select *
from Student
where id < 50
order by id asc

10-219 按城市所属的省份统计省份下所有城市的人口

select name,sum(population) as population
from(
     select ( case
              when name in ('杭州','宁波','温州')
              then '浙江' 
              else '江苏'
              end )as name,population
     from city
) as a
group by name;

10-220 查看所有学生的基本信息。 没有！