SQL 쿼리생성과정

--테이블에서 전체 컬럼을 출력

SELECT * FROM EMPLOYEES;

--테이블에서 특정 컬럼을 출력

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES;

-- 컬럼이름 바꾸고 싶을 때

SELECT FIRST_NAME FIRST, LAST_NAME LAST, DEPARTMENT_ID DEPT FROM EMPLOYEES;

--새로운 컬럼 이름에 띄어쓰기가 되어 있는 경우

SELECT FIRST_NAME "GIVEN NAME", LAST_NAME "FAMILY NAME" FROM EMPLOYEES;

--조건을 주고 검색 하는 경우

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 90;


SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IN (100, 110, 120);

--패턴을 조건으로 주고 검색

SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE 'Ma%';--Ma로 시작하는 이름 검색 
SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE '%ma%';--중간에 ma가 들어가는 이름 검색 

--컴바인하기
--SELECT *FROM TABLE1
--UNION
--SELCET *FROM TABLE2;

SELECT * FROM A
UNION
SELECT * FROM B
UNION
SELECT * FROM C
UNION
SELECT * FROM D
UNION
SELECT * FROM E
UNION
SELECT * FROM F
UNION
SELECT * FROM G
UNION
SELECT * FROM H
UNION
SELECT * FROM Y
UNION
SELECT * FROM J
UNION 
SELECT *FROM K;

--

select TRANSACTION_YEAR "년도", b.GENRE "장르" , count(*)"장르 카운트" from books b, transaction t
where t.book_id = b.book_id
group by TRANSACTION_YEAR, b.GENRE  order by TRANSACTION_YEAR, count(*) desc;