❤️SQL语句案例
SQL语句案例
案例
--5. 显示出表employees中的全部job_id（不能重复）
SELECT DISTINCT
JOB_ID
FROM
EMPLOYEES;
--6. 显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
SELECT
EMPLOYEES.EMPLOYEE_ID || ',' || EMPLOYEES.FIRST_NAME || ',' || EMPLOYEES.LAST_NAME AS OUT_PUT
FROM
EMPLOYEES;
--3. 选择工资不在5000到12000的员工的姓名和工资
SELECT
FIRST_NAME,
SALARY
FROM
EMPLOYEES
WHERE
SALARY > 12000
OR SALARY < 5000;
SELECT
FIRST_NAME,
SALARY
FROM
EMPLOYEES
WHERE
SALARY NOT BETWEEN 5000
AND 12000;
--4. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间
SELECT
FIRST_NAME,
JOB_ID,
HIRE_DATE
FROM
EMPLOYEES
WHERE
HIRE_DATE BETWEEN '01-2月-98'
AND '01-5月-98';
SELECT
FIRST_NAME,
JOB_ID,
HIRE_DATE
FROM
EMPLOYEES
WHERE
HIRE_DATE BETWEEN TO_DATE( '1998-02-01', 'yyyy-mm-dd' )
AND TO_DATE( '1998-05-01', 'yyyy-mm-dd' );
--5. 选择在20或50号部门工作的员工姓名和部门号
SELECT
FIRST_NAME,
DEPARTMENT_ID
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID IN ( 20, 50 );
--6. 选择在1994年雇用的员工的姓名和雇用时间
SELECT
FIRST_NAME,
HIRE_DATE
FROM
EMPLOYEES
WHERE
HIRE_DATE BETWEEN '01-1月-94'
AND '01-1月-95';
SELECT
FIRST_NAME,
HIRE_DATE
FROM
EMPLOYEES
WHERE
to_char( HIRE_DATE, 'yyyy' ) = '1994';
SELECT
hire_date
FROM
employees;
--7. 选择公司中没有管理者的员工姓名及job_id
SELECT
FIRST_NAME,
manager_id,
JOB_ID
FROM
EMPLOYEES
WHERE
MANAGER_ID IS NULL;
--8. 选择公司中有奖金的员工姓名，工资和奖金级别
SELECT
*
FROM
employees e
WHERE
e.commission_pct IS NOT NULL;
--9. 选择员工姓名的第三个字母是a的员工姓名
SELECT
FIRST_NAME
FROM
EMPLOYEES
WHERE
FIRST_NAME LIKE '__a%';
--10. 选择姓名中有字母a和e的员工姓名
SELECT
FIRST_NAME
FROM
EMPLOYEES
WHERE
FIRST_NAME LIKE '%a%'
AND FIRST_NAME LIKE '%e%' SELECT
FIRST_NAME
FROM
EMPLOYEES
WHERE
FIRST_NAME LIKE '%a%e%'
OR FIRST_NAME LIKE '%e%a%';
--0927；
--mysql,小型数据库，增删改查，适合小型的项目；
--oracle大型项目，数据量大；
--查询部门表中的所有列数据,select 后面是要查询的列名，from后面是要查的是哪一张表；
--SELECT * FROM departments;
--查询员工的id和员工的first_name；
--SELECT employee_id,first_name FROM employees;
--查询所有员工的年薪,as实现重命名或者是空格，两个以上单词组成的列名中间用_分割；
--select employee_id,salary*12 yearly_salary from employees;
SELECT
EMPLOYEES.EMPLOYEE_ID
FROM
EMPLOYEES;
--查询所有员工的实发工资
SELECT
EMPLOYEES.EMPLOYEE_ID,
SALARY + SALARY * NVL( COMMISSION_PCT, 0 )
FROM
EMPLOYEES;
--查询所有员工的名字定义别名为emp name
SELECT
FIRST_NAME "emp namE"
FROM
EMPLOYEES;
--将员工的first_name和last_name组合成emp_name显示
SELECT
FIRST_NAME || ' ' || LAST_NAME EMP_NAME
FROM
EMPLOYEES;
--获得在员工表中出现的所有部门id(去重)
SELECT DISTINCT
DEPARTMENT_ID
FROM
EMPLOYEES;
--查询90号部门的员工id，first_name,工资和部门号
SELECT
EMPLOYEE_ID,
FIRST_NAME,
SALARY,
DEPARTMENT_ID
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID = 90;
--查询工资大于5000的所有员工的信息
SELECT
*
FROM
EMPLOYEES
WHERE
SALARY > 10000;
--查询first_name是john的人的信息
SELECT
*
FROM
EMPLOYEES
WHERE
FIRST_NAME = 'John';
--查询工资大于5000并且他在50号部门工作
SELECT
*
FROM
EMPLOYEES
WHERE
SALARY > 5000
AND DEPARTMENT_ID = 50;
--查询工资大于8000或者他在20号部门工作的员工信息
SELECT
*
FROM
EMPLOYEES
WHERE
SALARY > 8000
OR DEPARTMENT_ID = 20;
--查询不在50号部门工作的人的员工id和部门id
SELECT
EMPLOYEE_ID,
DEPARTMENT_ID
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID <> 50;
--查询工资在5000-8000之间的员工的信息
--select * from employees where salary>=5000 and salary<=8000;
SELECT
*
FROM
EMPLOYEES
WHERE
SALARY BETWEEN 5000
AND 8000;
--查询在20,50,60号部门工作的员工的信息
--select * from employees where department_id=20 or department_id=50 or department_id=60;
SELECT
*
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID IN ( 20, 50, 60 );
--查询没有奖金的员工的id，工资和奖金
SELECT
E.EMPLOYEE_ID,
E.SALARY,
E.COMMISSION_PCT
FROM
EMPLOYEES E
WHERE
E.COMMISSION_PCT IS NULL;
--查询有奖金的员工的id，工资和奖金
SELECT
E.EMPLOYEE_ID,
E.SALARY,
E.COMMISSION_PCT
FROM
EMPLOYEES E
WHERE
E.COMMISSION_PCT IS NOT NULL;
--查询不在20,50,60号部门工作的员工的信息
SELECT
*
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID NOT IN ( 20, 50, 60 );
--查询first_name首字符为S的员工的first_name,salary,department_id
SELECT
FIRST_NAME,
SALARY,
DEPARTMENT_ID
FROM
EMPLOYEES
WHERE
FIRST_NAME LIKE 'S%';
--查询first_name第三个字符为a的员工的first_name,salary,department_id
SELECT
FIRST_NAME,
SALARY,
DEPARTMENT_ID
FROM
EMPLOYEES
WHERE
FIRST_NAME LIKE '__a%';
--查询96年5月1日以前入职的员工信息
SELECT
*
FROM
EMPLOYEES
WHERE
HIRE_DATE < '01-5月-96';
--查询在20号部门工作或者job_id含有VP字样的人的信息
SELECT
*
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID = 20
OR JOB_ID LIKE '%VP%';
--按照工资升序排序查询所有员工信息
SELECT
*
FROM
EMPLOYEES
ORDER BY
SALARY;
--按照部门降序排序查询所有员工信息
SELECT
*
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID IS NOT NULL
ORDER BY
DEPARTMENT_ID DESC;
--按照年薪进行排序
SELECT
EMPLOYEE_ID,
SALARY * 12 年薪
FROM
EMPLOYEES
ORDER BY
年薪;
--在部门排序的基础上进行工资排序
SELECT
*
FROM
EMPLOYEES
ORDER BY
DEPARTMENT_ID DESC,
SALARY DESC;
--实现查询的所有员工名字大写
SELECT
LOWER( FIRST_NAME )
FROM
EMPLOYEES;
--查询虚表
SELECT
INITCAP( 'hello world' )
FROM
DUAL;
--在不区分大小写的情况下查询first_name为john的人的信息
SELECT
*
FROM
EMPLOYEES
WHERE
UPPER( FIRST_NAME ) = 'JOHN';
--使用虚表实现函数
SELECT
CONCAT( 'hello', 'world' ),
SUBSTR( 'abcdefg', 3, 4 )
FROM
DUAL;
SELECT
CONCAT( FIRST_NAME, LAST_NAME )
FROM
EMPLOYEES;
--查询工资
SELECT
LPAD( SALARY, 8, '!' )
FROM
EMPLOYEES;
SELECT
RPAD( SALARY, 8, '@' )
FROM
EMPLOYEES;
SELECT
TRIM( ' ' FROM ' Hello World ' )
FROM
DUAL;
--round练习
SELECT
ROUND( 55.5555, 0 )
FROM
DUAL;
--trunc练习
SELECT
TRUNC( 155.555, - 1 )
FROM
DUAL;
--sysdate练习
SELECT SYSDATE
FROM
DUAL;
--查询所有员工来公司了多少周（不能有小数）,周数降序排列
SELECT
EMPLOYEE_ID,
TRUNC( ( SYSDATE - HIRE_DATE ) / 7 ) WEEK
FROM
EMPLOYEES
ORDER BY
WEEK DESC;
--next_day练习
SELECT
NEXT_DAY( SYSDATE, '星期日' )
FROM
DUAL;
--last_day练习
SELECT
LAST_DAY( SYSDATE )
FROM
DUAL;
--日期的round练习
SELECT
TRUNC( SYSDATE )
FROM
DUAL;
--当前日期转成字符型
SELECT SYSDATE
FROM
dual;
SELECT
TO_CHAR( SYSDATE, 'yyyy/mm/dd hh24:mi:ss' )
FROM
DUAL;
--员工表中所有入职时间变为：年/月/日的样式显示
SELECT
TO_CHAR( HIRE_DATE, 'yyyy/mm/dd' )
FROM
EMPLOYEES;
--工资数字格式转换
SELECT
TO_CHAR( SALARY, 'L999,999.99' )
FROM
EMPLOYEES;
--将字符类型转成date
SELECT
TO_DATE( '2005-05-05', 'yyyy-mm-dd' )
FROM
DUAL;
--10号部门的员工工资提升10%显示，20号提升20%，30号部门提升30%，其余部门不提升，工资重命名为
--update_salary
SELECT
EMPLOYEE_ID,
DEPARTMENT_ID,
CASE
DEPARTMENT_ID
WHEN 10 THEN
1.1 * SALARY
WHEN 20 THEN
1.2 * SALARY
WHEN 30 THEN
1.3 * SALARY ELSE SALARY
END "update_salary"
FROM
EMPLOYEES SELECT
EMPLOYEE_ID,
DEPARTMENT_ID,
DECODE( DEPARTMENT_ID, 10, 1.1 * SALARY, 20, 1.2 * SALARY, 30, 1.3 * SALARY, SALARY ) UPDATE_SALARY
FROM
EMPLOYEES --1. 显示系统时间
SELECT SYSDATE
FROM
DUAL;
--2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT
E.EMPLOYEE_ID,
E.FIRST_NAME,
E.SALARY,
E.SALARY * 1.2 "new salary"
FROM
EMPLOYEES E;
--3. 查询员工的姓名和工资，按下面的形式显示
SELECT
E.LAST_NAME,
LPAD( E.SALARY, 15, '$' )
FROM
EMPLOYEES E;
--做一个查询，产生下面的结果 king***********
SELECT
EMPLOYEE_ID,
SALARY,
RPAD( LAST_NAME, LENGTH( LAST_NAME ) + ( SALARY / 1000 ), '*' )
FROM
EMPLOYEES E;
--使用decode函数，按照下面的条件
--AD_PRES A
--ST_MAN B
--IT_PROG C
--SA_REP D
--ST_CLERK E
SELECT
LAST_NAME,
JOB_ID,
DECODE( JOB_ID, 'AD_PRES', 'A', 'ST_MAN', 'B', 'IT_PROG', 'C', 'SA_REP', 'D', 'ST_CLERK', 'E', NULL ) GRADE
FROM
EMPLOYEES;
SELECT
LAST_NAME,
JOB_ID,
CASE
JOB_ID
WHEN 'AD_PRES' THEN
'A'
WHEN 'ST_MAN' THEN
'B'
WHEN 'IT_PROG' THEN
'C' ELSE NULL
END GRADE
FROM
EMPLOYEES;
--1. 显示所有员工的姓名，部门号和部门名称。
SELECT
E.FIRST_NAME,
E.DEPARTMENT_ID,
D.DEPARTMENT_NAME
FROM
EMPLOYEES E,
DEPARTMENTS D
WHERE
E.DEPARTMENT_ID = D.DEPARTMENT_ID ( + );
--2. 查询90号部门员工的job_id和90号部门的location_id
SELECT
E.EMPLOYEE_ID,
E.JOB_ID,
D.LOCATION_ID
FROM
EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.DEPARTMENT_ID = 90;
--3. 选择所有有奖金的员工的
--last_name , department_name , location_id , city
SELECT
E.LAST_NAME,
D.DEPARTMENT_NAME,
L.LOCATION_ID,
L.CITY,
E.COMMISSION_PCT
FROM
EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN LOCATIONS L ON D.LOCATION_ID = L.LOCATION_ID
AND E.COMMISSION_PCT IS NOT NULL;
--4. 选择在Toronto工作的员工的
--last_name , job_id , department_id , department_name
SELECT
E.LAST_NAME,
E.JOB_ID,
D.DEPARTMENT_ID,
D.DEPARTMENT_NAME
FROM
EMPLOYEES E,
DEPARTMENTS D,
LOCATIONS L
WHERE
E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND L.CITY = 'Toronto';
--5. 选择所有员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
--employees Emp# manager Mgr#
--kochhar 101 king 100
SELECT
EMP.FIRST_NAME EMPLOYEES,
EMP.EMPLOYEE_ID "Emp#",
MGR.FIRST_NAME MANAGER,
MGR.EMPLOYEE_ID "Mgr#"
FROM
EMPLOYEES EMP,
EMPLOYEES MGR
WHERE
EMP.MANAGER_ID = MGR.EMPLOYEE_ID;
--6. 查询各部门员工姓名和他们的同事姓名，结果类似于下面的格式
--Department_id Last_name colleague
--20 fay hartstein
SELECT
E.DEPARTMENT_ID,
E.LAST_NAME,
C.LAST_NAME COLLEAGUE
FROM
EMPLOYEES E,
EMPLOYEES C
WHERE
E.DEPARTMENT_ID = C.DEPARTMENT_ID
AND E.EMPLOYEE_ID <> C.EMPLOYEE_ID --求出工资的平均值
SELECT
AVG( SALARY ) 平均值,
MAX( SALARY ) 最大值
FROM
EMPLOYEES --求出工资的平均值，最大值，最小值，总和
SELECT
AVG( SALARY ) 平均值,
MAX( SALARY ) 最大值,
MIN( SALARY ),
SUM( SALARY )
FROM
EMPLOYEES;
--求50号部门有多少人
SELECT
COUNT( MANAGER_ID )
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID = 50;
--查询有多少个部门
SELECT
COUNT( DEPARTMENT_ID )
FROM
DEPARTMENTS;
--查询各个部门的平均工资和最大工资
SELECT
DEPARTMENT_ID,
AVG( SALARY ),
MAX( SALARY )
FROM
EMPLOYEES
GROUP BY
DEPARTMENT_ID;
--查询各个job的工资总和
SELECT
JOB_ID,
SUM( SALARY )
FROM
EMPLOYEES
GROUP BY
JOB_ID;
--查询各个部门的各个job的平均工资和最大工资
SELECT
DEPARTMENT_ID,
JOB_ID,
AVG( SALARY ),
MAX( SALARY )
FROM
EMPLOYEES
GROUP BY
DEPARTMENT_ID,
JOB_ID;
SELECT
EMPLOYEE_ID,
MAX( SALARY )
FROM
EMPLOYEES
GROUP BY
EMPLOYEE_ID;
SELECT
JOB_ID,
COUNT( * )
FROM
EMPLOYEES
GROUP BY
JOB_ID;
--查询平均工资大于5000的部门的id和平均工资,并且部门号要求大于50
SELECT
DEPARTMENT_ID,
AVG( SALARY )
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID > 50
GROUP BY
DEPARTMENT_ID
HAVING
AVG( SALARY ) > 5000
ORDER BY
DEPARTMENT_ID DESC;
--查询最大工资大于10000的job_id和最大工资，要求job_id包含a字母
SELECT
JOB_ID,
MAX( SALARY )
FROM
EMPLOYEES
WHERE
LOWER( JOB_ID ) LIKE '%a%'
GROUP BY
JOB_ID
HAVING
MAX( SALARY ) > 10000
ORDER BY
MAX( SALARY );
--查询最大工资的人的id
SELECT
MAX( SALARY )
FROM
EMPLOYEES;
--4. 查询公司员工工资的最大值，最小值，平均值，总和
SELECT
MAX( E.SALARY ),
MIN( E.SALARY ),
AVG( E.SALARY ),
SUM( E.SALARY )
FROM
EMPLOYEES E;
--5. 查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT
JOB_ID,
MAX( E.SALARY ),
MIN( E.SALARY ),
AVG( E.SALARY ),
SUM( E.SALARY )
FROM
EMPLOYEES E
GROUP BY
JOB_ID;
--6. 选择具有各个job_id的员工人数
SELECT
JOB_ID,
COUNT( EMPLOYEE_ID )
FROM
EMPLOYEES
GROUP BY
JOB_ID;
--7. 查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT
MAX( SALARY ) - MIN( SALARY ) AS "DIFFERENCE"
FROM
EMPLOYEES;
--8. 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT
E.MANAGER_ID,
MIN( E.SALARY )
FROM
EMPLOYEES E
WHERE
E.MANAGER_ID IS NOT NULL
GROUP BY
E.MANAGER_ID
HAVING
MIN( E.SALARY ) >= 6000;
--9. 查询所有部门的名字，location_id，员工数量和工资平均值
SELECT
D.DEPARTMENT_NAME,
D.LOCATION_ID,
COUNT( E.EMPLOYEE_ID ),
AVG( E.SALARY )
FROM
EMPLOYEES E,
DEPARTMENTS D
WHERE
E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY
D.DEPARTMENT_NAME,
D.LOCATION_ID;
--10. 查询公司的人数，以及在1995-1998年之间，每年雇用的人数，结果类似下面的格式
--total 1995 1996 1997 1998
--30 3 4 6 7
SELECT
COUNT( EMPLOYEE_ID ) TOTAL,
SUM( DECODE( TO_CHAR( HIRE_DATE, 'yyyy' ), '1995', 1, 0 ) ) "1995",
SUM( DECODE( TO_CHAR( HIRE_DATE, 'yyyy' ), '1996', 1, 0 ) ) "1996",
SUM( DECODE( TO_CHAR( HIRE_DATE, 'yyyy' ), '1997', 1, 0 ) ) "1997",
SUM( DECODE( TO_CHAR( HIRE_DATE, 'yyyy' ), '1998', 1, 0 ) ) "1998"
FROM
EMPLOYEES;
SELECT
COUNT( EMPLOYEE_ID ) TOTAL,
COUNT( DECODE( TO_CHAR( HIRE_DATE, 'yyyy' ), '1995', 1, NULL ) ) "1995",
COUNT( DECODE( TO_CHAR( HIRE_DATE, 'yyyy' ), '1996', 1, NULL ) ) "1996",
COUNT( DECODE( TO_CHAR( HIRE_DATE, 'yyyy' ), '1997', 1, NULL ) ) "1997",
COUNT( DECODE( TO_CHAR( HIRE_DATE, 'yyyy' ), '1998', 1, NULL ) ) "1998"
FROM
EMPLOYEES;
--select to_char(sysdate,'yyyy') from dual;
--查询工资比id为200号员工高的人的所有信息
SELECT
*
FROM
EMPLOYEES
WHERE
SALARY > ( SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 200 );
--查询和199号员工干同一工作的人的所有信息
SELECT
*
FROM
EMPLOYEES
WHERE
JOB_ID = ( SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 199 )
AND EMPLOYEE_ID <> 199;
--查询和126号员工同一个部门的人的所有信息
SELECT
*
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID = ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 126 )
AND EMPLOYEE_ID <> 126;
--查询最大工资的人的名字和工资
SELECT
LAST_NAME,
SALARY
FROM
EMPLOYEES
WHERE
SALARY = ( SELECT MAX( SALARY ) FROM EMPLOYEES );
--查询平均工资比200号员工工资高的部门id和平均工资
SELECT
DEPARTMENT_ID,
AVG( SALARY )
FROM
EMPLOYEES
GROUP BY
DEPARTMENT_ID
HAVING
AVG( SALARY ) > ( SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 200 );
--选择在Toronto工作的员工的信息
--1，查询location表中的location_id
SELECT
LOCATION_ID
FROM
LOCATIONS L
WHERE
L.CITY = 'Toronto' --2,查询该location_id对应的部门id
SELECT
DEPARTMENT_ID
FROM
DEPARTMENTS
WHERE
LOCATION_ID = ( SELECT LOCATION_ID FROM LOCATIONS L WHERE L.CITY = 'Toronto' ) --3,根据部门id查询员工信息
SELECT
*
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID = ( SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE LOCATION_ID = ( SELECT LOCATION_ID FROM LOCATIONS L WHERE L.CITY = 'Toronto' ) );
--查询工资比John高的人的信息
SELECT
*
FROM
EMPLOYEES
WHERE
SALARY < ALL ( SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME = 'John' );
SELECT
COUNT( EMPLOYEE_ID ) TOTAL,
COUNT( DECODE( TO_CHAR( HIRE_DATE, 'yyyy' ), '1995', 1, NULL ) ) AS "1995"
FROM
EMPLOYEES;
SELECT
TO_CHAR( HIRE_DATE, 'yyyy' ),
COUNT( * )
FROM
EMPLOYEES
WHERE
TO_CHAR( HIRE_DATE, 'yyyy' ) BETWEEN 1995
AND 1998
GROUP BY
TO_CHAR( HIRE_DATE, 'yyyy' );
--1. 查询和John相同部门的员工姓名和雇用日期
SELECT
FIRST_NAME,
HIRE_DATE
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES E WHERE FIRST_NAME = 'John' );
--2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT
EMPLOYEE_ID,
FIRST_NAME,
SALARY
FROM
EMPLOYEES
WHERE
SALARY > ( SELECT AVG( SALARY ) FROM EMPLOYEES );
--3. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT
EMPLOYEE_ID,
FIRST_NAME
FROM
EMPLOYEES
WHERE
DEPARTMENT_ID IN ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FIRST_NAME LIKE '%u%' )
AND FIRST_NAME NOT LIKE '%u%';
--4. 查询在部门编号为20部门员工的员工号，和job_id
--5. 查询管理者是king的员工姓名和工资
SELECT
FIRST_NAME || LAST_NAME ENAME,
SALARY
FROM
EMPLOYEES
WHERE
MANAGER_ID IN ( SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE LOWER( LAST_NAME ) = 'king' );
SELECT
*
FROM
EMPLOYEE1;
--为jobs1表插入一条数据
INSERT INTO JOBS1 ( JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY )
VALUES
( 'sal_man1', 'xxoo', 500, 5000 );
--向员工表中插入数据
INSERT INTO EMPLOYEES
VALUES
( 997, 'xx', 'oo', 'xxxx2', '12456', TO_DATE( '1999-09-09', 'yyyy-mm-dd' ), 'HR_REP', 12222, NULL, 124, NULL );
INSERT INTO JOBS1 ( JOB_ID, JOB_TITLE )
VALUES
( 'sal_man2', 'xxoo' );
INSERT INTO JOBS1 ( JOB_ID, MIN_SALARY, MAX_SALARY )
VALUES
( 'sal_man1', 500, 5000 );
--把jobs中的所有数据插入到jobs1中
INSERT INTO JOBS1 ( JOB_ID, JOB_TITLE ) SELECT
FIRST_NAME,
LAST_NAME
FROM
EMPLOYEES;
INSERT INTO EMPLOYEE1 SELECT
*
FROM
EMPLOYEES;
--将30号部门的所有员工工资改为10000
UPDATE EMPLOYEE1
SET SALARY = 10000
WHERE
DEPARTMENT_ID = 30;
--将和106号员工同一部门的人员的工资提升10%
UPDATE EMPLOYEE1
SET SALARY = 1.1 * SALARY
WHERE
DEPARTMENT_ID = ( SELECT DEPARTMENT_ID FROM EMPLOYEE1 WHERE EMPLOYEE_ID = 106 ) UPDATE EMPLOYEE1
SET SALARY = 0;
--将所有工资低于108员工的人的部门全部调整到和108号员工同一部门
UPDATE EMPLOYEE1
SET DEPARTMENT_ID = ( SELECT DEPARTMENT_ID FROM EMPLOYEE1 WHERE EMPLOYEE_ID = 108 )
WHERE
SALARY < ( SELECT SALARY FROM EMPLOYEE1 WHERE EMPLOYEE_ID = 108 );
--将100号员工的部门改为12号
UPDATE EMPLOYEES
SET DEPARTMENT_ID = 120
WHERE
EMPLOYEE_ID = 100;
--将100号部门的所有员工删除
DELETE
FROM
EMPLOYEE1
WHERE
DEPARTMENT_ID = 100;
--将所有工资小于101号员工的人员信息删除
DELETE
FROM
EMPLOYEE1
WHERE
SALARY < ( SELECT SALARY FROM EMPLOYEE1 WHERE EMPLOYEE_ID = 101 );
DELETE
FROM
EMPLOYEE1;
--删除部门表中部门id为30的记录
DELETE
FROM
DEPARTMENTS
WHERE
DEPARTMENT_ID = 30;
INSERT INTO JOBS1
VALUES
( 'zzz', DEFAULT, 20, 200 );
SELECT
*
FROM
JOBS1;
INSERT INTO emp
VALUES
( 7369, '任盈盈', '职员', 7902, to_date( '1980-12-17', 'yyyy-mm-dd' ), 800, NULL, 20 );
INSERT INTO emp
VALUES
( 7499, '杨逍', '销售人员', 7698, to_date( '1981-2-20', 'yyyy-mm-dd' ), 1600, 300, 30 );
INSERT INTO emp
VALUES
( 7521, '范遥', '销售人员', 7698, to_date( '1981-2-22', 'yyyy-mm-dd' ), 1250, 500, 30 );
INSERT INTO emp
VALUES
( 7566, '任我行', '经理', 7839, to_date( '1981-4-2', 'yyyy-mm-dd' ), 2975, NULL, 20 );
INSERT INTO emp
VALUES
( 7654, '金毛狮王', '销售人员', 7698, to_date( '1981-9-28', 'yyyy-mm-dd' ), 1250, 1400, 30 );
INSERT INTO emp
VALUES
( 7698, '张无忌', '经理', 7839, to_date( '1981-5-1', 'yyyy-mm-dd' ), 2850, NULL, 30 );
INSERT INTO emp
VALUES
( 7782, '苏荃', '经理', 7839, to_date( '1981-6-9', 'yyyy-mm-dd' ), 2450, NULL, 10 );
INSERT INTO emp
VALUES
( 7788, '东方不败', '分析员', 7566, to_date( '1982-12-9', 'yyyy-mm-dd' ), 3000, NULL, 20 );
INSERT INTO emp
VALUES
( 7839, '韦小宝', '总裁', NULL, to_date( '1981-11-17', 'yyyy-mm-dd' ), 5000, NULL, 10 );
INSERT INTO emp
VALUES
( 7844, '紫衫龙王', '销售人员', 7698, to_date( '1981-9-8', 'yyyy-mm-dd' ), 1500, 0, 30 );
INSERT INTO emp
VALUES
( 7876, '向问天', '职员', 7788, to_date( '1983-1-12', 'yyyy-mm-dd' ), 1100, NULL, 20 );
INSERT INTO emp
VALUES
( 7900, '小昭', '职员', 7698, to_date( '1981-12-3', 'yyyy-mm-dd' ), 950, NULL, 30 );
INSERT INTO emp
VALUES
( 7902, '令狐冲', '分析员', 7566, to_date( '1981-12-3', 'yyyy-mm-dd' ), 3000, NULL, 20 );
INSERT INTO emp
VALUES
( 7934, '双儿', '职员', 7782, to_date( '1982-1-23', 'yyyy-mm-dd' ), 1300, NULL, 10 );
UPDATE dept
SET dname = '总部',
loc = '神龙岛'
WHERE
deptno = 10;
UPDATE dept
SET dname = '技术部',
loc = '黑木崖'
WHERE
deptno = 20;
UPDATE dept
SET dname = '市场部',
loc = '光明顶'
WHERE
deptno = 30;
UPDATE dept
SET dname = '行政部',
loc = '嵩山'
WHERE
deptno = 40;
-----------------------------------4---------------------------------------------
--1、查询员工表所有数据
SELECT
*
FROM
emp;
--2、查询总裁的基本工资
SELECT
sal,
job
FROM
emp
WHERE
job = '总裁';
--3、所有奖金为空的员工
SELECT
*
FROM
emp
WHERE
comm IS NULL;
--4、查询基本工资最高的三个人
SELECT
*
FROM
( SELECT * FROM emp ORDER BY sal DESC )
WHERE
ROWNUM <= 3 --5、查询技术部的所在地
SELECT loc FROM dept WHERE dname = '技术部' --6、查询部门编号为30且奖金大于300元的员工信息
SELECT * FROM emp WHERE deptno = 30 AND comm > 300 --7、查询部门编号为20的员工中基本工资最高的员工姓名和工资
SELECT
ename,
sal
FROM
( SELECT * FROM emp WHERE deptno = 20 ORDER BY sal DESC )
WHERE
ROWNUM = 1 SELECT
ename,
sal,
deptno
FROM
emp
WHERE
sal = ( SELECT max( sal ) FROM emp WHERE deptno = 20 )
AND deptno = 20 --8、查询位于'嵩山'、'黑木崖'、'南海神宫'的部门信息
SELECT
*
FROM
dept
WHERE
loc = '嵩山'
OR loc = '黑木崖'
OR loc = '南海神宫';
SELECT
*
FROM
dept
WHERE
loc IN ( '嵩山', '黑木崖', '南海神宫' );
--9、查询入职日期在1981-5-1到1981-12-31之间的所有员工
SELECT
*
FROM
emp
WHERE
hiredate >= to_date( '1981-5-1', 'yyyy-mm-dd' )
AND hiredate <= to_date( '1981-12-31', 'yyyy-mm-dd' );
SELECT
*
FROM
emp hiredate BETWEEN to_date( '1981-5-1', 'yyyy-mm-dd' )
AND to_date( '1981-12-31', 'yyyy-mm-dd' );
--10、查询所有名字为三个字的员工的员工编号,姓名
SELECT
empno,
ename
FROM
emp
WHERE
length( ename ) = 3 --11、查询10号部门的所有经理和20号部门的所有职员的详细信息
SELECT
*
FROM
emp
WHERE
deptno = 10
AND job = '经理'
OR deptno = 20
AND job = '职员';
--12、查询姓名中没有‘王’字的员工的详细信息
SELECT
*
FROM
emp
WHERE
instr( ename, '王' ) = 0;
SELECT
*
FROM
emp
WHERE
ename NOT LIKE '%王%';
SELECT
*
FROM
emp
WHERE
NOT ename LIKE '%王%';
--13、查询员工姓名，将工作年限最长的员工排在最前面
SELECT
ename,
hiredate
FROM
emp
ORDER BY
hiredate ASC;
--14、查询'任我行'的基本工资
SELECT
sal,
ename
FROM
emp
WHERE
ename = '任我行';
--15、查询基本工资比'任我行'多的所有员工的姓名和基本工资
SELECT
sal,
ename
FROM
emp
WHERE
sal > ( SELECT sal FROM emp WHERE ename = '任我行' );
--16、查询各个部门经理的基本工资
SELECT
sal
FROM
emp
WHERE
job = '经理';
--17、查询与'东方不败'从事相同工作的员工的详细信息
SELECT
*
FROM
emp
WHERE
job IN ( SELECT job FROM emp WHERE ename = '东方不败' )
AND ename <> '东方不败';
--18、查询市场部员工的姓名
SELECT
ename
FROM
emp
WHERE
deptno = ( SELECT deptno FROM dept WHERE dname = '市场部' );
--19、查询某些员工的姓名和基本工资，
--条件是他们的基本工资与部门30中某一
--个员工的基本工资相同
SELECT
ename,
sal
FROM
emp
WHERE
sal IN ( SELECT DISTINCT sal FROM emp WHERE deptno = 30 )
AND deptno <> 30;
--20、查询奖金收入比基本工资高的员工的详细信息
SELECT
*
FROM
emp
WHERE
comm > sal;
-----------------------------------5---------------------------------------------
--21、查询不同部门的平均基本工资
SELECT
avg( sal ),
deptno
FROM
emp
GROUP BY
deptno;
--22、查询所有基本工资高于平均基本工资（平均基本工资为所有部门员工的基本工资平均数）的销售人员
SELECT
*
FROM
emp
WHERE
job = '销售人员'
AND sal > ( SELECT avg( sal ) FROM emp );
--23、显示各种职位的最低基本工资
SELECT
min( sal ),
job
FROM
emp
GROUP BY
job;
--24、查询每个部门的人数
SELECT
count( * ),
deptno
FROM
emp
GROUP BY
deptno;
--25、查询每个部门入职最早的员工的入职时间和部门编号
SELECT
min( hiredate ),
deptno
FROM
emp
GROUP BY
deptno;
--26、显示所有职员的姓名及其所在部门的名称
SELECT
e.ename,
d.dname
FROM
emp e,
dept d
WHERE
e.deptno = d.deptno;
--27、显示所有员工的姓名、所在部门名称和基本工资
SELECT
e.ename,
d.dname,
e.sal
FROM
emp e,
dept d
WHERE
e.deptno = d.deptno;
--28、显示不同部门不同职位的平均基本工资,部门名称，职位
SELECT
avg( sal ),
d.dname,
e.job
FROM
emp e,
dept d
WHERE
d.deptno = e.deptno
GROUP BY
d.dname,
e.job
ORDER BY
d.dname,
avg( sal ) DESC;
--29、查询部门平均工资大于员工平均工资（全体员工平均工资）的部门编号和平均工资
SELECT
deptno,
avg( sal )
FROM
emp
GROUP BY
deptno
HAVING
avg( sal ) > ( SELECT avg( sal ) FROM emp ) --30、查询没有员工的部门名称
SELECT
dname
FROM
dept
WHERE
deptno NOT IN ( SELECT DISTINCT deptno FROM emp );
SELECT
count( e.empno ),
d.dname
FROM
emp e RIGHT outer
JOIN dept d ON e.deptno = d.deptno
GROUP BY
d.dname
HAVING
count( e.empno ) = 0;
--31、查询 部门当中每个员工基本工资都大于1200的部门名称
SELECT
d.dname
FROM
emp e,
dept d
WHERE
e.deptno = d.deptno
GROUP BY
d.dname
HAVING
min( sal ) > 1200;