sql-(test)
来源：互联网 发布：免费手机相册制作软件 编辑：程序博客网 时间：2024/05/17 06:01
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
(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'
- test sql
- Test SQL
- sql-(test)
- SQL JOIN TEST
- sql test（1）
- Spark sql test
- sql 50 test
- SQL For Test
- 导入测试数据test.sql
- Unit Test for T-SQL(ZT)
- A Simple C# And SQL Test
- SQL data reader reading data performance test
- test
- test
- test
- test
- test
- test
- HDU 1212求余
- hdu 1116 Play on Words
- .NET中通过域验证用户名和密码
- HTML学习笔记(五) HTML 标题
- system string to std string
- sql-(test)
- 我的第一篇
- HDU 动态规划（46道题目）倾情奉献~ 【只提供思路与状态转移方程】
- 字体和文字
- 友善之臂视频监控方案源码学习(2) - 主程序实现细节
- 一个屌丝脱屌的经历【转载】
- strcmp和stricmp、strcmpi三者之间的区别（C++）
- HTML学习笔记（六）HTML 段落
- const 指针与指向const的指针
