❤️SQL语句案例

小明的学习圈子2023-08-12数据库总结

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;
Last Updated 2024/4/6 10:47:15