SQL mission 2

JOIN / 서브쿼리(sub-query) / DDL / DML / DQL

-- 1. 
-- 성이 'King' 사용자의 정보를 출력하시오.
-- last_name
--1
select * from EMPLOYEES where last_name = 'King';
--2
select * from emp_details_view where last_name = 'King';

-- 2. 
-- 부서가 80, 90인 사용자의 부서정보를 출력하시오.
select DEPARTMENT_id
from EMPLOYEES
where DEPARTMENT_ID = 80 or DEPARTMENT_ID = 90 
order by DEPARTMENT_ID
;

select DISTINCT e.DEPARTMENT_ID, d.DEPARTMENT_NAME
from EMPLOYEES e, DEPARTMENTS d  
where e.DEPARTMENT_ID = 80 or e.DEPARTMENT_ID = 90 
and e.DEPARTMENT_ID = d.DEPARTMENT_ID
order by e.DEPARTMENT_ID
;

-- 3. 
-- 성이 'King'인 사용자의 부서명 정보를 출력하시오.
--1
SELECT LAST_NAME, DEPARTMENT_NAME 
from EMPLOYEES e, DEPARTMENTS d
where last_name = 'King'
and e.DEPARTMENT_ID = d.DEPARTMENT_ID
;

--2
SELECT DEPARTMENT_ID, DEPARTMENT_NAME 
FROM emp_details_view 
where last_name = 'King'
;

-- 4. 
-- 성이 'kING'인 사용자의 사번(employees), 부서명(departments), 직무(job)를 출력하시오.
SELECT e.last_name, e.EMPLOYEE_ID, d.DEPARTMENT_NAME, j.JOB_TITLE
FROM employees e, departments d, jobs j
where lower(e.last_name) = 'king'
and e.JOB_ID = j.JOB_ID
;
-- 결과 중복 : 마지막줄 삭제시 부서명 전체 수 만큼 반복된 결과 출력
--1
SELECT e.last_name, e.EMPLOYEE_ID, d.DEPARTMENT_NAME, j.JOB_TITLE
FROM employees e, departments d, jobs j
where lower(e.last_name) = 'king'
and e.JOB_ID = j.JOB_ID
and e.DEPARTMENT_ID = d.DEPARTMENT_ID
;
--2
SELECT last_name, EMPLOYEE_ID, DEPARTMENT_NAME, JOB_TITLE
FROM emp_details_view 
where lower(last_name) = 'king'
;

-- 5. 
-- employees 테이블명을 줄여서(alias) 표기하여 직원의 사번(employees), 부서명(departments), 직무(job)를 출력하시오. 
SELECT e.EMPLOYEE_ID 사번, d.DEPARTMENT_NAME 부서명, j.JOB_TITLE 직무
FROM employees e, departments d, jobs j
where e.JOB_ID = j.JOB_ID
and e.DEPARTMENT_ID = d.DEPARTMENT_ID
;

-- 6. 
-- employees 테이블에서 성이 'King'인 사원의 메니저의 이름과 부서명을 출력하라.
select e.last_name "직원 성", m.last_name "매니저 이름", d.DEPARTMENT_NAME 부서명
from EMPLOYEES e, EMPLOYEES m, DEPARTMENTS d
where e.last_name = 'King'
and e.MANAGER_ID = m.EMPLOYEE_ID
and e.DEPARTMENT_ID = d.DEPARTMENT_ID
;

-- 7. 
-- employees 테이블에서 성이 'Taylor'인 사원의 매니저의 이름과 직업을 출력하라.
select e.last_name "직원 성", m.last_name "매니저 이름", j.JOB_TITLE 직업
from EMPLOYEES e, EMPLOYEES m, JOBS j
where e.last_name = 'Taylor'
and e.MANAGER_ID = m.EMPLOYEE_ID
and e.JOB_ID = j.JOB_ID
;

-- 8. 
-- 사원 중 평균 급여보다 큰 급여를 받는 사원정보를 출력하라.
select round(avg(SALARY)) 
from EMPLOYEES;

select *
from EMPLOYEES
where SALARY > (select round(avg(SALARY)) from EMPLOYEES) 
order by SALARY desc
; 

-- 9. 
-- 직무별 가장 많은 급여를 받는 사원 정보 출력하라.
--1
select job_id, max(SALARY)
from EMPLOYEES 
group by job_id
;

select e.EMPLOYEE_ID , e.last_name , e.job_id, e.SALARY
from EMPLOYEES e, (select job_id, max(SALARY) max_SALARY from EMPLOYEES group by job_id) m
where e.SALARY = m.max_SALARY
and e.job_id = m.job_id
;

--2
select job_id, max(SALARY) 
from emp_details_view
group by job_id
;

-- 10. 
-- 성이 'King'인 사원과 같은 job의 사원을 출력하라.
-- 단, 'King'은 제외 한다.
select JOB_ID, EMPLOYEE_ID, LAST_NAME
from EMPLOYEES where LAST_NAME = 'King';

select * from EMPLOYEES where JOB_ID = 'SA_REP' or JOB_ID = 'AD_PRES';

select e.EMPLOYEE_ID, e.LAST_NAME, s.JOB_ID
from EMPLOYEES e, (select JOB_ID from EMPLOYEES where LAST_NAME = 'King' group by job_id) s
where e.JOB_ID = s.JOB_ID
and LAST_NAME != 'King' 
;
--JOB_ID가 AD_PRES인 사원은 'King' 한명이기 때문에 결과에 JOB_ID가 SA_REP 사원들만 출력된다.

-- 11. 
-- 성이 'King'인 사원중 많은 급여를 받는 사원보다 더 많은 급여를 받는 사원을 출력하라.

select LAST_NAME, SALARY from EMPLOYEES where LAST_NAME = 'King';

select max(SALARY) max from EMPLOYEES where LAST_NAME = 'King';

select e.LAST_NAME, EMPLOYEE_ID
from EMPLOYEES e
where e.SALARY > (select max(SALARY) max from EMPLOYEES where LAST_NAME = 'King')
;
-- 급여 24000은 전직원 중 가장 높은 급여. 때문에 값이 안나옴.... 

-- 12. 
-- 부서가 'Sales'인 부서에 근무하는 직원정보 출력하라.
select d.DEPARTMENT_ID 부서번호, d.DEPARTMENT_NAME 부서명, e.EMPLOYEE_ID 사번, e.last_name 직원이름
from EMPLOYEES e, DEPARTMENTS d
where d.DEPARTMENT_NAME = 'Sales'
;

-- 13. 
-- 최소 급여를 받는 사람의 이름과 급여 정보를 출력하라.
select min(SALARY) from EMPLOYEES;

select e.last_name, e.SALARY
from EMPLOYEES e, (select min(SALARY) min_SALARY from EMPLOYEES) m
where e.SALARY = m.min_SALARY
;


-- 14. 
-- 평균 급여보다 많은 급여를 받는 직원의 정보를 출력하라.
SELECT * 
FROM EMPLOYEES
where SALARY > (select round(avg(SALARY)) from EMPLOYEES)
order by SALARY desc
;

/*
 15. ~  20. 미션 관련 참고 데이터

## 회원관리 초기화데이터								
아이디	비밀번호		이름		연락처			이메일			가입일		등급	마일리지	담당자
user01	password01	홍길동	010-1234-1111	user01@work.com	2017.05.05	G	75000	
user02	password02	강감찬	010-1234-1112	user02@work.com	2017.05.06	G	95000	
user03	password03	이순신	010-1234-1113	user03@work.com	2017.05.07	G	3000	
user04	password04	김유신	010-1234-1114	user04@work.com	2017.05.08	S			송중기
user05	password05	유관순	010-1234-1115	user05@work.com	2017.05.09	A		

## 게시글 관리 초기화데이터						
게시번호	제목		내용						작성자	작성날짜			조회수	
1		주말과제	회원도서관리DB설계			user05	2020.11.11		0	
2		형상관리 	형상관리 소개				user04	2020.12.25		5	
3		주말과제	화면정의서					user05	2021.02.14		0	
4		과제제출	시간엄수					user05	2021.03.01		15	
5		WEB참고	www.w3schools.com		user01	2021.05.26		5	
*/

-- 15. 제공된 회원 및 게시글 초기레코드 참고 데이터를 참고로하여 테이블을 생성하시오.
create table member (
    member_id varchar2(15),
    member_pw varchar2(20) not null,
    name varchar2(20) not null,
    mobile varchar2(13) not null,
    email varchar2(30) not null,
    entry_date varchar2(10) not null,
    grade varchar2(1) not null,
    mileage number(6),
    manager varchar2(20),
    CONSTRAINT PK_MEMBER_ID PRIMARY KEY(MEMBER_ID)
);

create table notice (
    board_no number(6),
    board_title varchar2(30) not null,
    board_contents varchar2(1000),
    MEMBER_ID varchar2(30) ,
    board_date varchar2(10) not null,
    borad_viewcount number(10),	
    CONSTRAINT PK_board_no PRIMARY KEY(board_no),
	constraint FK_MEMBER_ID foreign key(MEMBER_ID) references MEMBER(MEMBER_ID)	
);


-- 16. 15에서 제공된 참고 데이터를 참고로하여 회원 레코드를 추가하시오.
INSERT into member
values ('user01', 'password01', '홍길동', '010-1234-1111', 'user01@work.com',	 '2017.05.05', 'G',	'75000', null);

INSERT into member
values ('user02', 'password02', '강감찬',	'010-1234-1112', 'user02@work.com', '2017.05.06', 'G', '95000', null);

INSERT into member
values ('user03', 'password03',	'이순신',	'010-1234-1113', 'user03@work.com', '2017.05.07', 'G', '3000', null);

INSERT into member
values ('user04', 'password04',	'김유신',	 '010-1234-1114', 'user04@work.com', '2017.05.08', 'S' , null, '송중기');

INSERT into member
values ('user05', 'password05', '유관순', '010-1234-1115', 'user05@work.com',	'2017.05.09', 'A', null, null);


-- 17. 15에서 제공된 참고 데이터를 참고로하여 게시글 레코드를 추가하시오.
INSERT into notice
values (1, '주말과제', '회원도서관리DB설계', 'user05', '2020.11.11', 0);

INSERT into notice
values (2, '형상관리', '형상관리 소개', 'user04', '2020.12.25', 5);

INSERT into notice
values (3, '주말과제', '화면정의서', 'user05', '2021.02.14', 0);

INSERT into notice
values (4, '과제제출', '시간엄수', 'user05', '2021.03.01', 15);	

INSERT into notice
values (5, 'WEB참고', 'www.w3schools.com', 'user01',	'2021.05.26', 5);	
 
 
-- 18. 전체 회원 정보를 조회하시오.
select * from member;


-- 19. 전체 게시글 정보를 조회하시오.
select * from notice;
 
 
-- 20. 회원 아이디 user05 가 작성한 게시글을 조회하시오.
select * from notice
where MEMBER_ID = 'user05';


-- 21. 제약관련 데이터 딕셔너리를 조인을 이용하여 다음의 제약 정보를 조회하시오.
-- 제약 데이터 딕셔너리 : user_constraints, user_cons_columns
-- 단, 테이블명, 제약타입 으로 정렬 조회하시오.
-- 조회항목 : 테이블명, 제약타입, 제약명, 컬럼명
select * from user_constraints;
select * from user_cons_columns;

select con.TABLE_NAME 테이블명, con.CONSTRAINT_TYPE 제약타입, con.CONSTRAINT_NAME 제약명, col.COLUMN_NAME 컬럼명
from user_constraints con, user_cons_columns col
where con.TABLE_NAME = col.TABLE_NAME
order by con.TABLE_NAME, con.CONSTRAINT_TYPE
;