复试__Ex2018
- part 2 SQL编程题
SELECT e.`ename`,e.`age` FROM emp e WHERE e.`eid` IN (SELECT w.`eid` FROM works w JOIN works w2 ON w.`eid` = w2.`eid` WHERE w.`did`='软件' AND w2.`did` = '硬件');
SELECT e.`eid`,e.`ename`,e.`salary`,tmp.`did` FROM emp e,(SELECT w.`eid`,w.`did`,d.`budget`*0.2 AS tmp_sal FROM works w JOIN dept d ON w.`did` = d.`did`) tmp WHERE e.`eid` = tmp.`eid` AND e.`salary`>=tmp.`tmp_sal`;
SELECT * FROM dept d ORDER BY d.`budget` DESC LIMIT 0,5;
SELECT SUM(d.`budget`),d.`managerid` FROM dept d GROUP BY d.`managerid` HAVING SUM(d.`budget`)>=500000;
数据库测试:
- 建立数据库
Create Table CREATE TABLE `dept` ( `did` varchar(20) NOT NULL, `budget` decimal(10,0) DEFAULT NULL, `managerid` int(11) DEFAULT NULL, PRIMARY KEY (`did`) ) Create Table CREATE TABLE `emp` ( `eid` int(11) NOT NULL, `ename` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `salary` decimal(10,0) DEFAULT NULL, PRIMARY KEY (`eid`) ) Create Table CREATE TABLE `works` ( `eid` int(11) DEFAULT NULL, `did` varchar(20) DEFAULT NULL, `pct_time` int(11) DEFAULT NULL, KEY `works_ibfk_1` (`eid`), KEY `works_ibfk_2` (`did`), CONSTRAINT `works_ibfk_1` FOREIGN KEY (`eid`) REFERENCES `emp` (`eid`) ON UPDATE CASCADE )
- 中位数问题
- 方法一:Oracle使用median函数
select median(e.salary)
from employee e join department d on e.dno = d.id
where d.name = 'IT';
-- 上面只是对一个部门进行求中位数,通过使用group by,可以实现对多个部门求平均
select d.name,median(e.salary)
from employee e join department d on e.dno = d.id
group by d.name;
- 方法二:使用思想:两个表连接之后,两个薪水相等的次数大于等于,e1 大于等于e2的次数
select tmp.dno,avg(tmp.salary)
from (select e1.dno,e1.salary
from employee e1,employee e2,department d
where e1.dno = d.id
and e1.dno = e2.dno
group by e1.salary,e1.dno
having sum(case when e1.salary = e2.salary then 1 else 0 end )
>= abs(sum(sign(e1.salary-e2.salary)))) tmp
group by tmp.dno;
2.月份之间的差---Oracle使用months_between(date1,date2)函数
select months_between(tmp.ma,tmp.mi) as mons,months_between(tmp.ma,tmp.mi)/12 as yeas
from (select max(e.hire) as ma,min(e.hire) as mi
from employee e) tmp;
3.一年内的一周每日的次数
- 方法一:使用oracle 中的connect by 中的level,因为level是递增的,那么用2017-1-1 + level就等于当前的天数,再通过trunc函数,就可以的得到这一天是星期几。
with x as(
select level lvl
from dual
connect by level <= add_months(TO_DATE('2017-1-1','YYYY-MM-DD'),12)-TO_DATE('2017-1-1','YYYY-MM-DD'))
select to_char(TO_DATE('2017-1-1','YYYY-MM-DD')+lvl-1,'DAY'),count(*)
from x
group by to_char(TO_DATE('2017-1-1','YYYY-MM-DD')+lvl-1,'DAY');
- 方法二:使用WITH...AS 以及t500表来完成
t500表就是一个数字,里面的id是1-500一个数一个数递增的,相当于上面的level
--为了方便,这里使用的是sysdate,同理这里的sysdate可以使用to_date来进行替换,得到想要年分的数字
select to_char(trunc(sysdate,'y')+t500.id-1,'DAY'),COUNT(*)
from t500
where t500.id<=add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
--这是指从1到这一年的最后一天的天数,然后将这个天数转化为星期几,再count(*)
group by to_char(trunc(sysdate,'y')+t500.id-1,'DAY');
4.计算当前员工以及同部门员工内紧随其后的员工的薪水差
- 这里涉及的函数是lead()over()开窗函数,这里的lead就是一种错位,将下一行的移到这一行
select e2.dno,e2.name,e2.salary,e2.hire,e2.salary-x.tmp as diff
from (select e.name,e.dno,e.hire,e.salary,lead(e.salary)over(partition by e.dno order by e.hire) as tmp
from employee e) x,employee e2
where x.name=e2.name;
select o.oid,c.cname,
from Customers c,Orderstatus os,Orders o
where c.cid = o.cid
and o.oid = os.oid
and oc.status<>'完成'
and os.statusdate in (select max(os2.statusdate)
from Orderstatus os2
group by os2.oid);