select *
from employees e
where e.department_id in
(select d.department_id from departments d
where d.department_name in('Marketing','IT')); --查询不是经理的员工的信息 select *
from employees e
where e.employee_id not in
(select distinct e1.manager_id from employees e1
where e1.manager_id is not null); --查询出所有经理的信息
select e.last_name,e.department_id from employees e
where e.employee_id in
(select distinct e1.manager_id from employees e1
where e1.manager_id is not null); --查询工资比10号部门中其中一个员工低的员工信息 select *
from employees e where e.salary
where e1.department_id=10);
--查询工资比10号部门都要低的员工信息 select *
from employees e where e.salary<
(select min(e1.salary) from employees e1
where e1.department_id=10); --如果要显示这个最低工资 select e.last_name,e.salary,s1.ms from employees e,
(select min(e1.salary) ms from employees e1
where e1.department_id=10) s1 where e.salary
--列出与“Sewall”(指的是last_name)从事相同工作的所有员工及部门名称 select e.last_name,d.department_name from employees e,departments d
where e.department_id=d.department_id
and e.job_id=(select job_id from employees where last_name='Sewall') and e.last_name<>'Swall';
--显示和Austin同部门，工资低于Baer的雇员有哪些 select e.last_name from employees e
where e.department_id=(select department_id from employees where last_name='Austin') and e.salary<(select salary from employees where last_name='Baer');
--找出部门90中所有的经理（MANAGER）和部门20中所有办事员（**_CLERK） select *from employees where department_id=90; select e.last_name,e.job_id from employees e where (
e.department_id=90 and
e.employee_id in
(select distinct e1.manager_id from employees e1
where e1.manager_id is not null) ) or(
e.department_id=20 and e.job_id like '%CLERK%' ) ;
--显示每个部门的名称、地点、员工人数以及该部门所有员工的平均薪资，将平均薪资舍入到小数点后两位。
SELECT d.department_name 部门, d.location_id 地点,
s.empnum 部门员工数, s.avgsal 部门平均工资 FROM departments d,
(SELECT e1.department_id dptid,count(e1.employee_id) empnum ,AVG(e1.salary) avgsal FROM employees e1 GROUP BY e1.department_id) s WHERE d.department_id=s.dptid;
--列出薪金高于公司平均薪金的所有员工,薪资,所在部门名称,上级领导姓名,工资等级 SELECT d.department_name 部门, e.last_name 员工, mgr.last_name 主管, g.grade_level 工资等级 FROM employees e
join employees mgr on e.manager_id=mgr.employee_id join departments d on e.department_id=d.department_id
join job_grades g on e.salary between g.lowest_sal and g.highest_sal WHERE e.salary>(select avg(salary) from employees)
--查询出部门名称，部门员工数，部门平均工资，部门最低工资雇员的姓名，其工资,及工资等级
--方法1
SELECT d.department_name 部门, s.empnum 部门员工数, s.avgsal 部门平均工资, e.salary 最低工资工资,
e.last_name 部门最低工资雇员, g.grade_level 工资等级 FROM employees e, departments d,
(SELECT e1.department_id dptid,count(e1.employee_id) empnum ,AVG(e1.salary) avgsal,MIN(e1.salary) minsal
FROM employees e1 GROUP BY e1.department_id) s, job_grades g
WHERE e.department_id=d.department_id AND d.department_id=s.dptid AND e.salary=s.minsal
AND e.salary between g.lowest_sal AND g.highest_sal;
--方法2:
select d.department_name 部门, d.department_id 部门号, s1.empcount 部门员工数, s1.avgsal 部门平均工资, s1.minsal 部门最低工资,
e.last_name 部门最低工资雇员, g.grade_level 工资等级 from employees e
join departments d on e.department_id=d.department_id
join job_grades g on e.salary between g.lowest_sal and g.highest_sal join
(select e1.department_id dptid,COUNT(*) empcount,AVG(e1.salary) avgsal,MIN(e1.salary) minsal from employees e1
group by department_id) s1
on e.department_id=s1.dptid and e.salary=s1.minsal;
/*------------------英文练习题-------------*/
/*1.The HR department needs a query that prompts the user for an employee last name.
The query then displays the last name and hire date of any employee in the same department as the employee
whose name they supply (excluding that employee).
For example, if the user enters Zlotkey, find all employees who work with Zlotkey (excluding
Zlotkey).*/
select e.last_name,e.hire_date,e.department_id from employees e
where e.department_id=( select e1.department_id from employees e1
where e1.last_name=&last_name) and e.last_name<>&last_name;
/*2.Create a report that displays the employee number, last name, and salary of all employees who earn more than the average salary. Sort the results in order of ascending salary.*/ SELECT e.employee_id, e.last_name, e.salary
FROM employees e
WHERE e.salary>(select avg(salary) from employees) order by e.salary;
/*3.Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains the letter “u.” Save your SQL statement as lab_07_03.sql. Run your query.*/ SELECT e.employee_id, e.last_name
FROM employees e
WHERE e.department_id in(select department_id from employees where last_name like '%u%');
/*4.The HR department needs a report that displays the last name, department number, and job ID of all employees
whose department location ID is 1700.*/ select e.last_name,e.department_id,e.job_id from employees e
where department_id in(select department_id from departments where location_id=1700)
--5.Create a report for HR that displays the last name and salary of every employee who reports to King.
select e.last_name,e.salary from employees e
where e.manager_id in(select employee_id from employees
where last_name='King')
/*6.Create a report for HR that displays the department number, last name, and job ID for every employee
in the Executive department.*/
select e.department_id,e.last_name,e.job_id from employees e
where e.department_id in(select department_id
from departments d
where d.department_name='Executive')
/*7.Modify the query in lab_07_03.sql to display the employee number, last name, and salary of all employees
who earn more than the average salary, and who work in a department with any employee whose last name contains a “u.”
Resave lab_07_03.sql as lab_07_07.sql. Run the statement in lab_07_07.sql. */
SELECT e.employee_id, e.last_name, e.salary
FROM employees e
WHERE e.department_id in(select department_id from employees where last_name like '%u%') and e.salary>(select avg(salary) from employees);
11.创建和管理表
/*
创建和管理表,预习自检: 1.有哪些数据库对象? 表：用于存储数据
视图：一个或者多个表中的数据的子集 序列：数字值生成器
索引：提高某些查询的性能 同义词：给出对象的替代名称 2.建表是要指定哪些内容? 3.如何建表时为列指定默认值? 4.如何使用子查询语法创建表?
5.如何为已有表新增列,删除列,修改列,为新增列定义默认值? 6.如何标记列为.如何批量删除列 8.如何删除表 9.如何更改表名?
10.如何舍去表中的内容? 11.如何为表,列添加注释?
12.oracle有哪些常用的数据类型? */ /*
使用sql语句完成以下练习: */