sql面试题2

– 查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部
select c.name,count(fc.film_id)
from film f inner join film_category fc on f.film_id = fc.film_id
inner join category c on fc.category_id = c.category_id
where f.description like “%robot%” and c.category_id in (select category_id
from film_category fc
group by category_id
having count(fc.film_id) >=5);

SELECT c.name, COUNT(fc.film_id) FROM
(select category_id, COUNT(film_id) AS category_num FROM
film_category GROUP BY category_id HAVING count(film_id)>=5) AS cc,
film AS f, film_category AS fc, category AS c
WHERE f.description LIKE ‘%robot%’
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id=cc.category_id;

select c.name,count(fc.film_id) from
(select category_id,count(film_id) as category_num from
film_category group by category_id having count(film_id)>=5) as cc,
film as f,film_category as fc,category as c
where f.description like “%robot%”
and f.film_id=fc.film_id
and c.category_id=fc.category_id
and c.category_id=cc.category_id;

– 不同客群的占比
create table customer
(id int,
Gp varchar(2),
age int(3));
insert into customer
values
(1,“A”,29),
(2,“B”,29),
(3,“A”,29),
(4,“B”,29),
(5,“C”,29),
(6,“D”,29);
select * from customer;
select count(GP)/(select count(*) from customer) “占比” from customer group by GP;

– 每天的审批通过及审批通过的平均申请金额
create table app
(apply_date date ,
loan_no int,
app_prin int(10),
result char(2));

insert into app
values
(“2018-2-5”,111,1000,“Y”),
(“2018-4-5”,112,1000,“N”),
(“2018-5-5”,113,2000,“N”),
(“2018-6-5”,114,3000,“Y”),
(“2018-3-5”,115,1000,“N”),
(“2018-9-5”,116,5000,“Y”),
(“2018-9-5”,117,3000,“Y”);

insert into app
values
(“2018-2-5”,118,3000,“N”),
(“2018-4-5”,119,1000,“Y”);
select * from app;

select apply_date,count(result) c1, avg(app_prin) “平均金额” from app
where result=“Y”
group by apply_date;

select b.apply_date,ifnull(c1/c2,0) “每天通过率” ,ifnull(平均金额,0) “通过平均金额”
from (select apply_date,count(result) c1, avg(app_prin) “平均金额” from app
where result=“Y”
group by apply_date) a
right join
(select apply_date,count(result) c2 from app
group by apply_date) b on b.apply_date=a.apply_date
order by b.apply_date;