前端之家收集整理的这篇文章主要介绍了
ORACLE_SQL 练手 part4,
前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
更多参考和数据脚本下载:
1.万事俱备之ORACLE_SQL 练手 part1
select * from regions;
select * from countries;
select * from locations;
select * from departments;
select * from jobs;
update countries set country_name = 'Others' where country_id='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||' '||last_name ename,salary from employees where salary>(select round(avg(salary)) from employees) order by salary desc;
select first_name||' '||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||' '||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 || ' ' || 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 || ' ' || 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;