复试_Ex2018_

复试__Ex2018

• part 2 SQL编程题

```mysql
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` = '硬件');
```

```mysql
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`;
```

```mysql
SELECT *
FROM dept d
ORDER BY d.`budget` DESC
LIMIT 0,5;
```

```mysql
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
)

1. 中位数问题

• 方法一：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);