Sql学习笔记：试题库1

SQL：

1.Mysql综合测评

1.1统计薪资大于薪资最高的员工所在部门的平均工资和薪资最低的员工所在部门的平均工资的平均工资的员工信息。

2.统计薪资和平均薪资最低的部门中薪资最高的员工的薪资相差500之内的全部员工的信息

3.统计出薪资和平均薪资处于正中间的部门中薪资最高的员工的薪资 相差500之内的全部员工的信息

4.查询部门名称不是research,职位是manager,且薪资大于平均薪资的员工(包含ename hiredait loc三个字段)

5.找出5月1号之后入职的并且薪资高于NEWYORK地区的平均薪资的员工的具体信息(部门信息代替部门编号)按薪资降序列出

6查询各个部门{工资小于【纽约员工平均工资】}且{入职时间大于30年}的员工人数（输出字段至少包含[部门地址，部门名称，人数]这三个字段）

7.查询出入职时间最早的员工所在部门的最高薪资的员工名字,工作职位.工作地点

8.统计出薪资位于平均工资最高的部门与平均薪资最低的部门之间 的入职日期最晚的员工的工作城市的所有员工的平均工资

9.查询平均工资最高的地区的最早入职的员工

10 列出各个地区平均薪资处于中等水平的地区，列出该地区人数和平均在职时间

2.Oracle数据库练习

2.1demobld.sql

\--
-- Copyright (c) Oracle Corporation 1988, 2000.  All Rights Reserved.
\--
-- NAME
--  demobld.sql
\--
-- DESCRIPTION
--  This script creates the SQL*Plus demonstration tables in the
--  current schema.  It should be STARTed by each user wishing to
--  access the tables.  To remove the tables use the demodrop.sql
--  script.
\--
--  USAGE
--   From within SQL*Plus, enter:
--     START demobld.sql

SET TERMOUT ON
PROMPT Building demonstration tables.  Please wait.
SET TERMOUT OFF
DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;
CREATE TABLE EMP	
​   (
     -- 雇员编号
    EMPNO NUMBER(4) NOT NULL,
     -- 雇员姓名
​    ENAME VARCHAR2(10),
     -- 雇员职位
​    JOB VARCHAR2(9),
     -- 雇员对应的领导的编号
​    MGR NUMBER(4),
     -- 雇员的雇佣日期
​    HIREDATE DATE,
     -- 雇员的基本工资
​    SAL NUMBER(7, 2),
     -- 奖金
​    COMM NUMBER(7, 2),
     -- 所在部门
​    DEPTNO NUMBER(2),
     foreign key(deptno) references dept(deptno)
 );
INSERT INTO EMP VALUES
​    (7369, 'SMITH',  'CLERK',   7902,
​    TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
​    (7499, 'ALLEN',  'SALESMAN',  7698,
​    TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
​    (7521, 'WARD',  'SALESMAN',  7698,
​    TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
​    (7566, 'JONES',  'MANAGER',  7839,
​    TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
​    (7654, 'MARTIN', 'SALESMAN',  7698,
​    TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
​    (7698, 'BLAKE',  'MANAGER',  7839,
​    TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
​    (7782, 'CLARK',  'MANAGER',  7839,
​    TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
​    (7788, 'SCOTT',  'ANALYST',  7566,
​    TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
​    (7839, 'KING',  'PRESIDENT', NULL,
​    TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
​    (7844, 'TURNER', 'SALESMAN',  7698,
​    TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,   0, 30);
INSERT INTO EMP VALUES
​    (7876, 'ADAMS',  'CLERK',   7788,
​    TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
​    (7900, 'JAMES',  'CLERK',   7698,
​    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  950, NULL, 30);
INSERT INTO EMP VALUES
​    (7902, 'FORD',  'ANALYST',  7566,
​    TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
​    (7934, 'MILLER', 'CLERK',   7782,
​    TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
 

CREATE TABLE DEPT
​    (
     -- 部门编号
     DEPTNO NUMBER(2),
     -- 部门名称
​    DNAME VARCHAR2(14),
     -- 部门所在位置
​    LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',  'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',    'CHICAGO');
INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE BONUS
​    (
      -- 雇员姓名
      ENAME VARCHAR2(10),
      -- 雇员职位
​     JOB  VARCHAR2(9),
      -- 雇员工资
​     SAL  NUMBER,
      -- 雇员资金
​     COMM  NUMBER);
 
CREATE TABLE SALGRADE
​    (GRADE NUMBER,
​     LOSAL NUMBER,
​     HISAL NUMBER);
 
INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
 
CREATE TABLE DUMMY
​    (DUMMY NUMBER);
INSERT INTO DUMMY VALUES (0);
COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.
EXIT

SCOTT用户四张表结构

表一：部门表DEPT（使用DESC DEPT;查询）

NO	名称	类型	描述
1	DEPTNO	NUMBER(2)	表示部门编号有两位数字所组成
2	DNAME	VARCHAR2（14）	表示部门名称最多由14个字符所组成
3	LOC	VARCHAR2（13）	表示部门所在位置

（SELECT * FROM DEPT；）

NO	DEPTNO	DNAME	LOC
1	10	ACCOUNTING（财务部，会计部）	NEWYORK（纽约）
2	20	RESEARCH（调研部）	DALLAS（达拉斯）
3	30	SALES（营业部，市场部）	CHICAGO（芝加哥）
4	40	OPERATIONS（运营部）	BOSTON（波士顿）

表二：雇员表EMP（使用DESC EMP;查询）

名称	类型	描述
EMPNO	NUMBER（4）	表示雇员编号，由四个数字组成
ENAME	VARCHAR2（10）	表示雇员姓名，由10个字符组成
JOB	VARCHAR2（9）	表示雇员的职位，由9个字符组成
MGR	NUMBER（4）	表示雇员对应的领导编号，领导也是雇员
HIREDATE	DATE	表示雇员的雇佣日期
SAL	NUMBER（7,2）	表示雇员的基本工资，由两位小数5位整数和2位小数组成，共7位
COMM	NUMBER（7,2）	表示雇员的奖金
DEPTNO	NUMBER（2）	表示雇员所在部门的编号

（SELECT * FROM EMP;）

NO	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
1	7369	SMITH（史密斯）	CLERK（办事员）	7902	17-12月-80	800		20
2	7499	ALLEN（艾伦）	SALESMAN（销售员）	7698	20-2月-81	1600	300	30
3