--1.（3分）查找每个部门的最高工资员工编号及其下属信息。
select e2.empno,e1.* from emp e1 join (
select * from emp where (deptno,sal) in
(select deptno,max(sal) from emp group by deptno)) e2
on
e1.mgr = e2.empno;
/*
2.（5分）
有成绩表如下（使用with子查询）：
准考证号 科目 成绩
2006001 语文 119
2006001 数学 108
2006002 物理 142
2006001 化学 136
2006001 物理 127
2006002 数学 149
2006002 英语 110
2006002 语文 105
2006001 英语 98
2006002 化学 129
……
给出总分在600以上的学生准考证号。*/
with t as (select sid,sum(score) a from stu group by sid)
select sid from t where a > 600;
/*
3.（5分）
新建 stu_dent(学生表)，字段：sno，sname，sex，birth，age
要求如下：
1、学号(sno)不能为空且不可重复；
2、名字(sname)不能为空；
3、性别(sex)的值只能是'男'或'女'；
4、出生日期(birth)为日期格式；
5、年龄(age)为数值类型且在 0 - 100 之间；*/
create table stu_dent(
sno char(50) primary key ,
sname char(20) not null,
sex char(3) check(sex in ('女','男')),
birth date,
age number check(age between 0 and 100)
);
select * from stu_dent;
4.（5分）/*
某cc表数据如下：
c1 c2
--------------
1 西
1 安
1 的
2 天
2 气
3 好
……
转换为
c3 c4
--------------
1 西安的
2 天气
3 好*/
--要求：不能改变表结构及数据内容，仅在最后通过SELECT显示出这个查询结果
select c1 c3,listagg(c2,'') within group (order by r) c4
from (select cc.*,rownum r from cc) t group by c1;
/*5.（5分）
X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号(id)、日期(visit_date)、人流量(people)。
请编写一个查询语句，找出人流量的高峰期。
高峰期时，至少连续三行记录中的人流量不少于100。
例如，表stadium：
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
对于上面的示例数据，输出为：
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
提示：
每天只有一行记录，日期随着 id 的增加而增加。*/
with t as
(select f.*,f.id-row_number()over(order by id) m from f601 f where people > 100)
select t.id,t.visit_date,people from
t where t.m = (select m from
(select m,count(*) from t
group by m having count(*) >2));
/*
6.（5分）
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+*/
/*create table f602(
id number,
numb number
);*/
/*insert into f602 values(1,1);
insert into f602 values(2,1);
insert into f602 values(3,1);
insert into f602 values(4,2);
insert into f602 values(5,1);
insert into f602 values(6,2);
insert into f602 values(7,2);*/
select * from f602;
select numb from
(select t.*,lead(t.lead1)over(order by t.id) lead2 from
(select f.*,lead(f.numb)over(order by f.id) lead1 from f602 f)t)a
where a.lead1 = a.numb and a.lead1 = a.lead2;
/*7.（5分）
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的，小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
示例：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。*/
with seat as
(select 1 ,'Abbot' as student from dual
union all
select 2,'Doris' from dual
union all
select 3,'Emerson' from dual
union all
select 4 ,'Green' from dual
union all
select 5 ,'Jeames' from dual
)
select * from seat
order by
decode(student,'Doris',1,'Abbot',2,'Green',3,'Emerson',4,'Jeames',5)
/*
8.（7分）
Trips 表中保存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是检查类型，成员只有以下三种 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id | Status |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1 | 1 | 10 | 1 | completed |2013-10-01|
| 2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|
| 3 | 3 | 12 | 6 | completed |2013-10-01|
| 4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|
| 5 | 1 | 10 | 1 | completed |2013-10-02|
| 6 | 2 | 11 | 6 | completed |2013-10-02|
| 7 | 3 | 12 | 6 | completed |2013-10-02|
| 8 | 2 | 12 | 12 | completed |2013-10-03|
| 9 | 3 | 10 | 12 | completed |2013-10-03|
| 10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，
Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
+----------+--------+--------+
| Users_Id | Banned | Role |
+----------+--------+--------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
+----------+--------+--------+
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日期间非禁止用户的取消率。
基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+*/
/*create table "Users"(
"Users_Id" number primary key,
"Banned" char(10) check("Banned" in ('Yes','No')),
"Role" char(10) check("Role" in ('client','driver','partner'))
);
insert into "Users" values(1,'No','client');
insert into "Users" values(2,'Yes','client');
insert into "Users" values(3,'No','client');
insert into "Users" values(4,'No','client');
insert into "Users" values(10,'No','driver');
insert into "Users" values(11,'No','driver');
insert into "Users" values(12,'No','driver');
insert into "Users" values(13,'No','driver');*/
/*create table Trips(
"Id" number,
"Client_Id" number references "Users"("Users_Id"),
"Driver_Id" number references "Users"("Users_Id"),
"City_Id" number,
"Status" char(30) check("Status" in ('completed','cancelled_by_driver','cancelled_by_client')),
"Request_at" date
);
insert into Trips values(1,1,10,1,'completed',to_date('2013-10-01','yyyy-MM-dd'));
insert into Trips values(2,2,11,1,'cancelled_by_driver',to_date('2013-10-01','yyyy-MM-dd'));
insert into Trips values(3,3,12,6,'completed',to_date('2013-10-01','yyyy-MM-dd'));
insert into Trips values(4,4,13,6,'cancelled_by_driver',to_date('2013-10-01','yyyy-MM-dd'));
insert into Trips values(5,1,10,1,'completed',to_date('2013-10-02','yyyy-MM-dd'));
insert into Trips values(6,2,11,6,'completed',to_date('2013-10-02','yyyy-MM-dd'));
insert into Trips values(7,3,12,6,'completed',to_date('2013-10-02','yyyy-MM-dd'));
insert into Trips values(8,2,12,12,'completed',to_date('2013-10-03','yyyy-MM-dd'));
insert into Trips values(9,3,12,12,'completed',to_date('2013-10-03','yyyy-MM-dd'));
insert into Trips values(10,4,12,12,'cancelled_by_driver',to_date('2013-10-03','yyyy-MM-dd'));
*/
with t as
(select t.*,u1.*,u2."Banned" Banned2 from Trips t join "Users" u1 on u1."Users_Id" = t."Client_Id"
join "Users" u2 on u2."Users_Id" = t."Driver_Id" )
select t."Request_at" "Day",
round(sum(decode(trim(t."Status"),'cancelled_by_driver',1,0))/count(*),2) "Cancellation Rate"
from t where t."Banned" = 'No ' and t.Banned2 = 'No' group by t."Request_at";
/*9.（10分）
Spending table:
+---------+------------+----------+--------+
| user_id | spend_date | platform | amount |
+---------+------------+----------+--------+
| 1 | 2019-07-01 | mobile | 100 |
| 1 | 2019-07-01 | desktop | 100 |
| 2 | 2019-07-01 | mobile | 100 |
| 2 | 2019-07-02 | mobile | 100 |
| 3 | 2019-07-01 | desktop | 100 |
| 3 | 2019-07-02 | desktop | 100 |
+---------+------------+----------+--------+
这张表记录了用户在一个在线购物网站的支出历史，该在线购物平台同时拥有桌面端（'desktop'）和手机端（'mobile'）的应用程序。
写一段 SQL 来查找每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。
查询结果格式如下例所示：
Result table:
+------------+----------+--------------+-------------+
| spend_date | platform | total_amount | total_users |
+------------+----------+--------------+-------------+
| 2019-07-01 | desktop | 100 | 1 |
| 2019-07-01 | mobile | 100 | 1 |
| 2019-07-01 | both | 200 | 1 |
| 2019-07-02 | desktop | 100 | 1 |
| 2019-07-02 | mobile | 100 | 1 |
| 2019-07-02 | both | 0 | 0 |
+------------+----------+--------------+-------------+*/
/*create table Spending(
user_id number,
spend_date date,
platform char(10),
amount number
);
insert into Spending values(1,to_date('2019-07-01','yyyy-MM-dd'),'mobile',100);
insert into Spending values(1,to_date('2019-07-01','yyyy-MM-dd'),'desktop',100);
insert into Spending values(2,to_date('2019-07-01','yyyy-MM-dd'),'mobile',100);
insert into Spending values(2,to_date('2019-07-02','yyyy-MM-dd'),'mobile',100);
insert into Spending values(3,to_date('2019-07-01','yyyy-MM-dd'),'desktop',100);
insert into Spending values(3,to_date('2019-07-02','yyyy-MM-dd'),'desktop',100);*/
select *from spending
select * from(
select spend_date,decode(s,1,'mobile',3,'desktop','both') platform,
total_amount,count(*) total_users from
(select user_id,spend_date,sum(amount) total_amount,sum(decode(trim(platform),'mobile',1,'desktop',3)) s
from
(select * from spending order by spend_date,user_id)
group by user_id,spend_date
)
group by
spend_date,decode(s,1,'mobile',3,'desktop','both') ,
total_amount order by spend_date,total_amount)
union all
select to_date('2019-07-02','yyyy-MM-dd'),'both',0,0 from dual;
select distinct spend_date,
case when platform=platform1 then platform
else 'both'
end platform,
case when a=1 then sum(amount)over(partition by a order by user_id)
else amount
end total_amount
from
(select s1.user_id user_id,s1.spend_date spend_date,
s1.platform platform,s2.platform platform1,s1.amount amount,
case when s1.platform!=s2.platform then 1
else 0
end a
from spending s1 join spending s2 on s1.user_id=s2.user_id and s1.spend_date=s2.spend_date
) order by spend_date,total_amount;
-------------------------------------------------
select * from spending;
with aa as(select s1.user_id,s1.spend_date,s1.platform,s1.amount,s2.platform as a,s2.amount as b from Spending s1 left join Spending s2
on s1.user_id=s2.user_id and s1.spend_date=s2.spend_date and s1.platform <>s2.platform)
select spend_date,platform,sum(amount),count(*) from aa where a is null group by spend_date,platform
union all
select spend_date,'both',
sum(case when a is not null then amount else 0 end),
sum(case when a is not null then 1 else 0 end)/2 from aa group by spend_date
----------------------------------------------
select t.spend_date,t.platform,ifnull(t3.total_amount,0) as total_amount ,ifnull(t3.total_users,0) as total_users from
(select distinct spend_date,'both' as platform from Spending
union all
select distinct spend_date,'mobile' as platform from Spending
union all
select distinct spend_date,'desktop' as platform from Spending)
t left join
(select spend_date,platform,sum(amount) as `total_amount`,count(*) as `total_users` from (
select spend_date,user_id,sum(amount) `amount`,
case when count(distinct platform) > 1 then 'both'
else platform end as platform
from Spending group by spend_date,user_id
) t2 group by spend_date,platform
) t3 on t.spend_date = t3.spend_date and t.platform = t3.platform;
--------------------------------------------------
select t.spend_date,t.platform,ifnull(t3.total_amount,0) as total_amount ,ifnull(t3.total_users,0) as total_users from
(select distinct spend_date,'both' as platform from Spending
union all
select distinct spend_date,'mobile' as platform from Spending
union all
select distinct spend_date,'desktop' as platform from Spending)
t left join
(select spend_date,platform,sum(amount) as `total_amount`,count(*) as `total_users` from (
select spend_date,user_id,sum(amount) `amount`,
case when count(distinct platform) > 1 then 'both'
else platform end as platform
from Spending group by spend_date,user_id
) t2 group by spend_date,platform
) t3 on t.spend_date = t3.spend_date and t.platform = t3.platform;
--------------------------------------------
with s1 as (select * from Spending where platform='mobile'),--手机端
s2 as (select * from Spending where platform='desktop'), --电脑端
s3 as(select (case when s1.spend_date is not null then s1.spend_date else s2.spend_date end) spend_date,
(case when s1.platform is not null and s2.platform is null then s1.platform
when s2.platform is not null and s1.platform is null then s2.platform
else 'both' end) platform,
nvl(s1.amount,0)+nvl(s2.amount,0) amount
from s1 full join s2 on s1.user_id=s2.user_id and s1.spend_date=s2.spend_date)
select s3.spend_date,
s3.platform,
s3.amount,
s4.total_users
from s3 left join (select spend_date,platform,count(*) total_users from s3 group by spend_date,platform) s4
on s3.spend_date=s4.spend_date and s3.platform=s4.platform
order by s3.spend_date;