sql-(test)

CREATE TABLE EMP
       (EMPNO NUMERIC(4) NOT NULL PRIMARY KEY,
        ENAME VARCHAR(10),
        JOB VARCHAR(9),
        MGR NUMERIC(4),
        HIREDATE DATE,
        SAL NUMERIC(7, 2),
        COMM NUMERIC(7, 2),
        DEPTNO NUMERIC(2),
        VERSIONNO NUMERIC(8));
INSERT INTO EMP VALUES (75616, '张经',  'MANAGER',   7839,
        '1981-04-02',  8975, NULL, 20, 0);
INSERT INTO EMP VALUES (7369, '张三',  'CLERK',     7902,
        '1980-12-17',  8100, NULL, 20, 0);
INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698,
        '1981-02-20', 1600,  300, 30, 0);
INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698,
        '1981-02-22', 7250,  500, 30, 0);
INSERT INTO EMP VALUES (7566, '张亿',  'MANAGER',   7839,
        '1981-04-02',  8975, NULL, 20, 0);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698,
        '1981-09-28', 1250, 1400, 30, 0);
INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839,
        '1981-05-01',  2850, NULL, 30, 0);
INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839,
        '1981-06-09',  2450, NULL, 10, 0);
INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566,
        '1982-12-09', 3000, NULL, 20, 0);
INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL,
        '1981-11-17', 5000, NULL, 10, 0);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698,
        '1981-09-08',  1500,    0, 30, 0);
INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788,
        '1983-01-12', 9100, NULL, 20, 0);
INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698,
        '1981-12-03',   3950, NULL, 30, 0);
INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566,
        '1981-12-03',  3000, NULL, 20, 0);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782,
        '1982-01-23', 1300, NULL, 10, 0);
insert  into emp values(7955, 'SMITH',  'CLERK',     7902,
        SYSDATE,  6800, 55, 20, 0);
insert  into emp values(7755, 'SMITH',  'CLERK',     7902,
        SYSDATE,  7750, 55, 40, 0);
insert  into emp values(7951, 'SMITH',  'CLERK',     7902,
        SYSDATE,  8190, 55, 40, 0);
COMMIT;
CREATE TABLE DEPT
       (DEPTNO NUMERIC(2) NOT NULL PRIMARY KEY,
        DNAME VARCHAR(14),
        LOC VARCHAR(13),
        VERSIONNO NUMERIC(8));

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK', 0);
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS', 0);
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO', 0);
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON', 0);
COMMIT;

分别在ORACLE和MYSQL下查出第二名工资高的全部信息？
select   *  from emp
       where sal in (select max(sal) sal from emp
where sal < (select max(sal) from emp)
) ;
比如查询scott.emp表的用户SAL排序信息每个部门的第二名工资
SELECT deptno, ename,
           ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) seq
      FROM emp
          where seq=2;
/**
SQL> SELECT deptno, ename,
           ROW_NUMBER () OVER(PARTITION BY deptno ORDER BY sal DESC) seq
      FROM emp;

    DEPTNO ENAME             SEQ
---------- ---------- ----------
        10 KING                1
        10 CLARK               2
        10 MILLER              3
        20 SCOTT               1
        20 FORD                2
        20 JONES               3
        20 ADAMS               4
        20 SMITH               5
        30 BLAKE               1
        30 ALLEN               2
        30 TURNER              3
        30 WARD                4
        30 MARTIN              5
        30 JAMES               6

14 rows selected.

再结合其他函数进行一下行列转换:

SQL> select deptno,
    max(decode(seq,1,ename,null)) highest,
    max(decode(seq,2,ename,null)) second,
    max(decode(seq,3,ename,null)) third
    from (
    select deptno,ename,
    row_number() over(partition by deptno order by sal desc) seq
    from emp)
   where seq <=3 group by deptno
   /

    DEPTNO HIGHEST    SECOND     THIRD
---------- ---------- ---------- ----------
        10 KING       CLARK      MILLER
        20 SCOTT      FORD       JONES
        30 BLAKE      ALLEN      TURNER

*/




CREATE TABLE DEP
       (DEPID VARCHAR(8) NOT NULL PRIMARY KEY,
         DEPNAME VARCHAR(60),
         PID VARCHAR(8),
        VERSIONNO NUMERIC(8));


INSERT INTO DEP VALUES ('101510', '赤壁赤马县港营业所', '1015', 0);
INSERT INTO DEP VALUES ('10151011', '赤湖镇港营业所', '101510', 0);
INSERT INTO DEP VALUES ('1015', '赤壁市供电公司',   '10', 0);
INSERT INTO DEP VALUES ('10', '省电力',NULL, 0);
INSERT INTO DEP VALUES ('101511', '赤壁赤兔港营业所', '1015', 0);
INSERT INTO DEP VALUES ('101010', '崇观营业所', '1010', 0);
INSERT INTO DEP VALUES ('1010', '崇观供电公司',   '10', 0);
COMMIT;
select d1.DEPID AS 部门编号,
d1.depname  AS 部门名,
d2.depname  AS 上级部门名
from dep AS d1 left join dep AS d2 on d2.depid =d1.pid

select d1.depid , d1.depname AS 部门名,d2.depname AS上级部门名
      from dep AS d1 left join dep AS d2 on d2.depid =d1.pid
select depname 部门名,(select depname from dep AS inDep WHERE inDep.depid=outDep.pid) 上级部门名 from dep AS outDep
select * from dep

例如设备有 A1,A2,A3,A4  其中A4没加油

现有一表数据如下
设备  加油量    加油日期
CREATE TABLE MACHINERY
       (MACHINE_NO VARCHAR(20) NOT NULL ,
        NAME VARCHAR(20) NOT NULL ,
        OIL NUMERIC(5,2),
        OP_DATE DATE,
        VERSIONNO NUMERIC(8));

insert into MACHINERY values('AA','AAcar',10,'2009-10-01',0);
insert into MACHINERY values('BB','BBcar',20,'2009-10-02',0);
insert into MACHINERY values('CC','CCcar',30,'2009-10-03',0);

insert into MACHINERY values('AA','AAcar',10,'2009-10-05',0);
insert into MACHINERY values('BB','BBcar',20,'2009-10-07',0);
insert into MACHINERY values('CC','CCcar',30,'2009-10-08',0);

insert into MACHINERY values('AA','AAcar',10,'2009-10-11',0);
insert into MACHINERY values('BB','BBcar',20,'2009-10-22',0);
insert into MACHINERY values('CC','CCcar',30,'2009-10-13',0);

insert into MACHINERY values('AA','AAcar',10,'2009-10-25',0);
insert into MACHINERY values('BB','BBcar',20,'2009-10-27',0);
insert into MACHINERY values('CC','CCcar',30,'2009-10-18',0);



insert into MACHINERY values('DD','DDcar',0,'2009-10-25',0);
insert into MACHINERY values('DD','EEcar',0,'2009-10-27',0);
insert into MACHINERY values('DD','FFcar',0,'2009-10-18',0);
        COMMIT;

ALTER TABLE MACHINERY ADD NAME VARCHAR(20) 添加名称列
*********************************************************
请写出将表MACHINERY中NAME存在重复的记录都列出来的SQL语句（按NAME排序）

select *
from MACHINERY
where name in (select name from MACHINERY group by name having count(*)>1)
order by name



****************************************************



%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
根据emp 和dept 拿到员工个数大于3的部门编号和该部门员工个数 以及该部门所有的员工信息 还有部门的信息
select a.*,b.*
from dept b, emp a, (select DEPTNO ,count(*) cnt from emp group by deptno having count(*)>3) c
where a.deptno = b.deptno
   and a.deptno = c.deptno
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


region  地区   achievement  业绩

CREATE TABLE REGION_ACH
       (ID INT NOT NULL PRIMARY KEY , --合同
        NAME VARCHAR(20) NOT NULL ,--姓名
        ACHIEVEMENT NUMERIC(5,2),-- 业绩
        REGION  CHAR(1), --地区
        VERSIONNO NUMERIC(8));
insert into REGION_ACH values(1,'DDcar',3650,'A',0);
INSERT INTO REGION_ACH VALUES (2, 'ACCOUNTING',4566, 'B', 0);

insert into REGION_ACH values(3,'fD',3450,'A',0);
INSERT INTO REGION_ACH VALUES (4, 'GOG',4866, 'B', 0);
INSERT INTO REGION_ACH VALUES (5, 'HIR',4566, 'C', 0);
INSERT INTO REGION_ACH VALUES (6, 'TG',4266, 'D', 0);
INSERT INTO REGION_ACH VALUES (7, 'BOR',4166, 'D', 0);
INSERT INTO REGION_ACH VALUES (8, 'NIM',4766, 'C', 0);
COMMIT;


第一道：显示出  业绩 大于同一地区平均值的 合同id  姓名 地区 业绩
select T1.* from REGION_ACH T1, ( select avg(ACHIEVEMENT) ACHI ,REGION from REGION_ACH
group by REGION)T2 where T1.ACHIEVEMENT  > T2.ACHI and
T1.REGION = T2.REGION;

第二道：把同一地区的  平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）
CREATE TABLE REGION_ACH_NWES AS (SELECT avg(R.ACHIEVEMENT) AVG_ACHIEVEMENT,REGION from REGION_ACH group by REGION);

CREATE TABLE REGION_ACH_NWES AS SELECT AVG_ACHIEVEMENT,REGION FROM (SELECT avg(ACHIEVEMENT) AVG_ACHIEVEMENT,REGION FROM REGION_ACH group by REGION);
CREATE TABLE REGION_ACH_NWE (AVG_ACHIEVEMENT INT NOT NULL,REGION CHAR(1));
COMMIT;
这种插入数字和下面一样的只是没有列名：
INSERT INTO REGION_ACH_NWE (AVG_ACHIEVEMENT,REGION) SELECT avg(ACHIEVEMENT) AVG_ACHIEVEMENTI ,REGION
from REGION_ACH group by REGION;
COMMIT;

INSERT INTO REGION_ACH_NWE SELECT avg(ACHIEVEMENT) AVG_ACHIEVEMENTI ,REGION
from REGION_ACH group by REGION;
COMMIT;
SELECT * FROM REGION_ACH_NWE

LEFT JOIN 多表关联用法：
select * from biao1 as a left join biao2 as b on a.字段=b.字段 left join biao3 as c on a.ziduan=c.ziduan where 条件



**********************************************
一张表包括出库和入库状态：
货品名字 出入库 数量
A 出库 100
A 入库 200
A 出库 null


求商品A的库存总数 入库减去出库
我当时是在MYSQL环境下写的
不知道怎么处理空值


mysql> select * from t_bllizard;
+-------+------+------+
| marno | mflg | qty |
+-------+------+------+
| A | I | 200 |

CREATE TABLE BLLIZARD (MARNO VARCHAR(10),
MFLG CHAR(1),
QTY  NUMERIC(6,2)

);
INSERT INTO BLLIZARD VALUES('A','O',100);
INSERT INTO BLLIZARD VALUES('A','I',300);
INSERT INTO BLLIZARD VALUES('A','O',NULL);
COMMIT;
oracle   select sum(decode(mflg,'I',qty))-sum(decode(mflg,'o',nvl(qty,0)))totalqty from BLLIZARD
oracle   select sum(if(mflg='I',qty,-qty))
    -> from t_bllizard
    -> where marno='A'

**********************************************
面试题：
一个表student中有班级classid,学号id,成绩grade
1.计算各个班的平均成绩
2.查找比该班平均成绩高的学生的班级classid,学号id,成绩grade

CREATE TABLE STUDENTCL ( CLASSID VARCHAR(2),
ID VARCHAR(7),
GRADE NUMERIC(7,1)
           
);
INSERT INTO STUDENTCL VALUES('01','9501001',550);
INSERT INTO STUDENTCL VALUES('01','9501002',590);
INSERT INTO STUDENTCL VALUES('01','9501003',575);
INSERT INTO STUDENTCL VALUES('01','9501004',570);
INSERT INTO STUDENTCL VALUES('01','9501005',599);
INSERT INTO STUDENTCL VALUES('02','9502001',585);
INSERT INTO STUDENTCL VALUES('02','9502002',590);
INSERT INTO STUDENTCL VALUES('02','9502003',577);
INSERT INTO STUDENTCL VALUES('02','9502004',579);
INSERT INTO STUDENTCL VALUES('03','9503001',610);
INSERT INTO STUDENTCL VALUES('03','9503002',643);
INSERT INTO STUDENTCL VALUES('03','9503003',615);
INSERT INTO STUDENTCL VALUES('03','9503004',570);
INSERT INTO STUDENTCL VALUES('03','9503005',559);
INSERT INTO STUDENTCL VALUES('04','9504001',530);
INSERT INTO STUDENTCL VALUES('04','9504002',523);
INSERT INTO STUDENTCL VALUES('04','9504003',615);
INSERT INTO STUDENTCL VALUES('04','9504004',580);
INSERT INTO STUDENTCL VALUES('04','9504005',569);
commit;

CREATE TABLE AT
       (A_ID VARCHAR(8) NOT NULL PRIMARY KEY,
         A_NAME VARCHAR(20));

INSERT INTO AT VALUES('101','HAO1O1');
INSERT INTO AT VALUES('102','HAO1O2');         
INSERT INTO AT VALUES('103','HAO1O3');
INSERT INTO AT VALUES('104','HAO1O4');
 INSERT INTO AT VALUES('105','HAO1O5');
INSERT INTO AT VALUES('106','HAO1O6');        
CREATE TABLE BT
       (B_ID VARCHAR(8) NOT NULL PRIMARY KEY,
         A_ID VARCHAR(8),
         B_TEXT VARCHAR(60));    
         
         
INSERT INTO BT VALUES('1','101','HAO1O11');
INSERT INTO BT VALUES('2','101','HAO1O11');
INSERT INTO BT VALUES('3','103','HAO1O33');
INSERT INTO BT VALUES('4','104','HAO1O41');
INSERT INTO BT VALUES('5','104','HAO1O41');
INSERT INTO BT VALUES('6','103','HAO1O33');
INSERT INTO BT VALUES('7','101','HAO1O11');
INSERT INTO BT VALUES('8','101','HAO1O11');
INSERT INTO BT VALUES('9','103','HAO1O33');
INSERT INTO BT VALUES('11','104','HAO1O41');
INSERT INTO BT VALUES('12','101','HAO1O11');
INSERT INTO BT VALUES('13','103','HAO1O33');
INSERT INTO BT VALUES('14','102','HAO1O21');
INSERT INTO BT VALUES('15','102','HAO1O22');
INSERT INTO BT VALUES('16','102','HAO1O22');
INSERT INTO BT VALUES('17','104','HAO1O42');


INSERT INTO BT VALUES('18','104','HAO1O42');
INSERT INTO BT VALUES('19','104','HAO1O42');
INSERT INTO BT VALUES('20','104','HAO1O42');
INSERT INTO BT VALUES('21','105','HAO1O51');
INSERT INTO BT VALUES('22','105','HAO1O52');
INSERT INTO BT VALUES('23','106','HAO1O61');
commit;

select a_id,count(a_id) from bt group by a_id

select a.a_id,a.a_name,co from at a,
(select a_id,count(a_id) co from bt group by a_id) t
 where a.a_id = t.a_id
  order by t.co desc;
 
 select * from
 (select a.a_id, count(b.b_id) m_count
  from at a, bt b
  where A.a_id = B.a_id
  group by a_id) t

  order by t.m_count desc;

/// 航空网的几个航班查询题：

表结构如下：
CREATE TABLE city(id int NOT NULL auto_increment PRIMARY KEY,
city_name VARCHAR(20) );

CREATE TABLE flight(id int NOT NULL auto_increment PRIMARY KEY,
start_cityId int REFERENCES city(id),
end_cityId int references city(id),
start_time TIMESTAMP
 );
 
 insert into city values(null,'北京'),(null,'上海'),(null,'广州'),(NULL,'重庆');
 insert into flight values
 (null,1,2,'2014-09-04 9:17:23'),(null,1,3,'2014-09-04 09:37:23'),(null,1,2,'2014-09-04 10:15:14'),(null,2,3,'2014-09-04 12:11:14'),(null,3,4,'2014-09-04 08:11:14');
 
 //1、查询起飞城市是北京的所有航班，按到达城市的名字排序
 
 // sql -1
  SELECT f.id , f.start_cityId,f.end_cityId,c.city_name from flight f, city c
 WHERE f.start_cityId= (select c1.id from city c1 where c1.city_name = '北京')
AND f.end_cityId= c.id
ORDER BY c.city_name asc
//sql -2
select flight.id,'北京' start_cityid, e.city_name from
                  flight,city e WHERE flight.end_cityid=e.id and flight.start_cityid=(select
                  id from city WHERE city_name='北京');
    //sql -3              
 select flight.id,s.city_name,e.city_name from
                  flight,city s,city e
                  where flight.start_cityid=s.id and s.city_name='北京' and
                  flight.end_cityId=e.id
                  order by e.city_name desc;
2、查询北京到上海的所有航班纪录（起飞城市，到达城市，起飞时间，航班号）
// 方法 1
select flight.id,s.city_name,e.city_name,flight.start_time  from
                  flight,city s,city e
                  where flight.start_cityid=s.id and s.city_name='北京' and
                  flight.end_cityId= e.id
                  and e.city_name='上海'
                  
        // 方法2          
SELECT  f.id, '北京'start_cityId,'上海'end_cityId, f.start_time FROM flight f
WHERE f.start_cityId= (select c1.id from city c1 where c1.city_name = '北京')
 and f.end_cityId= (select c2.id from city c2 where c2.city_name = '上海')
 
  3、查询具体某一天（2005-5-8）的北京到上海的的航班次数
                  select count(*) from
                  (select c1.CityName,c2.CityName,f.StartTime,f.flightID
                  from city c1,city c2,flight f
                  where f.StartCityID=c1.cityID
                  and f.endCityID=c2.cityID
                  and c1.cityName='北京'
                  and c2.cityName='上海'
                  and 查帮助获得的某个日期处理函数(startTime) like '2005-5-8%'
                  mysql中提取日期部分进行比较的示例代码如下：
                  select * from flight where
                  date_format(starttime,'%Y-%m-%d')='1998-01-02'