DB Oracle SQL 구문

기본 구문

-- table
select * from department;
select * from employee;
 
-- where
select * from department d, employee e where d.deptno = e.dno;
select distinct e2.empno, e2.empname from employee e1, employee e2 where e1.manager = e2.empno;
 
-- join
select * from department natural join employee ;
 
-- order by
select empname from employee order by empname desc;
select empname from employee order by empname asc;
 
-- group by
select title,count(*),sum(salary) from employee group by title;
select title,count(*),sum(salary) from employee group by title order by count(*) asc, sum(salary) asc;
 
-- sub query
select empname from employee e where exists (select * from department d where e.dno = d.deptno and(d.deptname='영업' or d.deptname='개발'));
select empname from employee e where e.empno in (select manager from employee);
 
-- insert 
insert into employee values(1112, 'fxnnxc', '대리', 1003, 2500000, 2);
 
-- unlock
ALTER USER hr ACCOUNT UNLOCK;
ALTER USER hr IDENTIFIED BY hr;

 

 

연습문제

-- 20 번 및 50 번 부서에서 근무하는 모든 사원들의 LAST_NAME 및 부서 번호를 알파벳순으로 조회한다.
select last_name, department_id from employees where department_id=20 or department_id=50 order by last_name;
 
-- 매니저가 없는 사람들의 LAST_NAME 및 JOB_ID 를 조회한다.
select job_id from employees where manager_id is null;
 
-- LAST_NAME 의 네번째 글자가 a 인 사원들의 LAST_NAME 을 조회한다.
select last_name from employees where last_name like '___a%';
 
-- 시애틀에 사는 모든 사람들의 LAST_NAME, 부서 명, 지역 ID 및 도시 명을 조회한다.
select last_name, department_name, locations.location_id, city from employees, departments, locations where city='Seattle';
    
-- 자신의 매니저보다 먼저 고용된 사원들의 LAST_NAME 및 고용일을 조회한다.
select last_name, e1.hire_date from employees e1 where hire_date < (select e2.hire_date from employees e2 where e2.employee_id= e1.manager_id);