SQL练习

1. 表的某一列的值与此列平均值进行对比，使用case when进行条件判断，赋值新字段；

--新建表servers
create table servers(
	id integer not null,
	server_name varchar(100)  not null,
	connections integer not null
	);
	
insert into servers values (105,'HAWK',3);
insert into servers values (104,'BUZZARD',0);
insert into servers values (100,'FALCON',6);
insert into servers values (103,'HARRIER',3);
insert into servers values (107,'HAAA',1);

select * from servers 

---根据表servers中的connections字段>此列的平均值，则status置为'overload'，否则置为'OK'，结果输出id，server_name，status三个字段，同时按id进行排序；

select id,server_name,
case when connections > (select avg(connections) from servers) 
then 'overload' 
else 'OK' end as status 
from servers order by id 

 运行结果：

2 给出表tasks和参赛人员关于tasks的得分情况表resports，

要求输出结果字段：task_id,task_name,difficulty;

difficulty 由不同参赛人员针对同一任务的平均分得出；

score>=60 视为‘easy'; 60>=score>=20视为medium;   score<=20视为'hard';

---1 创表tasks
create table tasks(
  id integer not null,
	name varchar(100) not null
	);
	
	insert into tasks values (3,'Cake')
	insert into tasks values (6,'GameOFNUTS')
	insert into tasks values (7,'CircleIntersectionArea')
	insert into tasks values (9,'JessicaAndBrian')
	
---2 创表resports
 create table resports(
    id integer not null,
	task_id integer  not null,
	candidate varchar(100) not null,
	score int not null
	);
	
insert into  resports values (2,6,'Paul Sat',0)
insert into  resports values (3,3,'Karen M',30)
insert into  resports values (5,3,'Oscar Glad',10)	
insert into  resports values (7,9,'Karen M',60)
insert into  resports values (11,6,'Paul Sat',81)
insert into  resports values (13,6,'Paul Sat',100)


----分解题目：
--先找出每个tasks的难易程度,得到'task_id'、'difficulty'；
--再去关联tasks表、resports表，得到'task_id'、'task_name'、'difficulty'；

---1 先找出每个tasks的难易程度,得到'task_id'、'difficulty'
select task_id, 
case 
when avg(score) <=20  then 'hard'
when avg(score) > 20 and  avg(score) <=  60  then 'Medium'
when avg(score) > 60  then 'hard'
end  as difficulty  
from resports 
group by task_id 

----2 输出最终的结果：

select distinct  b.task_id , a.name  as task_name, c.difficulty 
from tasks a 
join resports b 
on a.id =b.task_id 
left join 
(select task_id, 
case 
when avg(score) <=20  then 'hard'
when avg(score) > 20 and  avg(score) <=  60  then 'Medium'
when avg(score) > 60  then 'hard'
end  as difficulty  
from resports 
group by task_id) c
on b.task_id  = c.task_id 

最终结果图：

 3. 表 transaction1 对于每月支出少于三笔且低于100元的，需要每月付费5元；否则，无需付费；

balance = sum(amount)-fee ,输出balance的值；

​
create table transaction1 (
  amount integer not null,
	date date  not null
	);
	
	select * from  transaction1
	
insert into  transaction1 values (1000,'2020-01-06')	
insert into  transaction1 values (-10,'2020-01-14')	
insert into  transaction1 values (-75,'2020-01-20')	
insert into  transaction1 values (-5,'2020-01-25')	
insert into  transaction1 values (-4,'2020-01-29') 
insert into  transaction1 values (2000,'2020-03-10') 
insert into  transaction1 values (-75,'2020-03-12')	
insert into  transaction1 values (-20,'2020-03-15')	
insert into  transaction1 values (40,'2020-03-15')	
insert into  transaction1 values (-50,'2020-03-17')	
insert into  transaction1 values (200,'2020-10-10') 
insert into  transaction1 values (-200,'2020-10-10') 
 

---a 表找出无需付税的月份
---b 表计算付税的金额
---c 计算balance

select c.total - b.fee as balance 
from 
(
select (12-count(a.mon))*5 as fee 
from 
(select month(date)  as mon
from transaction1  
where amount < 0
group by  month(date)
having count(*) >= 3 and sum(amount) <= -100 )  as a) 
as b 
,
(select sum(amount)  as total from  transaction1 )as c

​

 只有三月符合上述条件，无需付税；sum(amount)-11*5=2746