复试_Ex2018_

复试__Ex2018

  • part 2 SQL编程题

复试_Ex2018_

  1. 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` = '硬件');
  2. 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`;
  3. SELECT *
    FROM dept d
    ORDER BY d.`budget` DESC
    LIMIT 0,5;
  4. 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
)

复试_Ex2018_

  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;

复试_Ex2018_

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);
上一篇:MyBatis——使用@Param注解实现多参数入参


下一篇:差分约束系统