前端之家收集整理的这篇文章主要介绍了
ORACLE_SQL 练手 part4，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
更多参考和数据脚本下载：
1.万事俱备之ORACLE_SQL 练手 part1
@H_
404_4@
select * from regions;
select * from countries;
select * from locations;
select * from departments;
select * from jobs;
update countries set country_name = @H_404_42@'Others' where country_id=@H_
404_42@'ZZ';
select department_id,count(*) nums,max(salary) maxsal,min(salary) minsal from employees group by department_id order by department_id ASC;
SELECT department_id,COUNT(*) nums_good from employees where salary>5000 group by department_id order by department_id;
select department_name,empp.department_id,round(avg(salary)) avgsal,count(*) nums from employees empp left join departments d on empp.department_id = d.department_id group by empp.department_id,department_name order by department_name;
SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM (SELECT (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME,EMP.SALARY FROM EMPLOYEES EMP) GROUP BY DPTNAME ORDER BY DPTNAME;
select a.department_id,a.salary,count(*) nums_same from employees a,employees b where a.salary =b.salary and a.employee_id!=b.employee_id and a.department_id=b.department_id group by a.department_id,a.salary;
select a.department_id,count(*) nums_same from employees a join employees b on a.salary =b.salary and a.employee_id!=b.employee_id and a.department_id=b.department_id group by a.department_id,a.salary;
select d.department_name,l.city from departments d left join locations l on d.location_id=l.location_id where d.department_id in (select department_id from employees where salary>1000 group by department_id having count(*)>2);
SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*) FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND D.LOCATION_ID = L.LOCATION_ID AND E.SALARY > 1000 GROUP BY D.DEPARTMENT_NAME,L.CITY HAVING COUNT(*) > 2;
select first_name||@H_404_42@' '||last_name ename,salary
from employees
where salary>(
select round(
avg(salary))
from employees)
order by salary
desc;
select first_name||@H_404_42@' '||last_name ename,salary
from employees
where salary between (
select round(
avg(salary))
from employees
where department_id=
50)
and (
select round(
avg(salary))
from employees
where department_id=
80);
select first_name||@H_404_42@' '||last_name ename,department_id，salary
from employees
where department_id
in (
select department_id
from employees
group by department_id
having avg(salary)>
5000 );
select employee_id from employees group by department_id,employee_id where salary =(select max(salary) from employees);
select employee_id from employees group by department_id,employee_id having salary =(select max(salary) from employees);
select DEPARTMENT_ID,employee_id,FIRST_NAME || @H_404_42@' ' || LAST_NAME ename
from employees
group by department_id,salary
having (salary,department_Id)
in(
select max(salary),department_Id
from employees
group by department_id );
SELECT FIRST_NAME || @H_404_42@' ' || LAST_NAME
AS NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES
WHERE (DEPARTMENT_ID,SALARY)
IN (
SELECT DEPARTMENT_ID,
MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);
select max(avsal)maxsal from (select round(avg(salary)) avsal,department_id from employees group by department_id);
select department_id,max(avsal) from (select round(avg(salary)) avsal,department_id from employees group by department_id );
select * from (select round(avg(salary)) avsal,department_id from employees group by department_id order by avsal desc) where rownum =1;
