sql실습

SELECT employee_id, department_id FROM TBL_EMP


SELECT 1 a, 2 b, 3 c FROM dual
UNION ALL
SELECT 4 a, 5 b, 6 c FROM dual


SELECT SUM(d10), SUM(d20), SUM(d30)
FROM
(SELECT COUNT(employee_id) d10 , 0 d20, 0 d30 FROM TBL_EMP WHERE department_id = 10
UNION ALL
SELECT 0 d10, COUNT(employee_id) d20, 0 d30 FROM TBL_EMP WHERE department_id = 20
UNION ALL
SELECT 0 d10, 0 d20, COUNT(employee_id) d30 FROM TBL_EMP WHERE department_id = 30 ) dept



SELECT '10' d10, '20' d20, '30' d30, '40' d40, '50' d50 FROM dual
UNION ALL
SELECT
SUM( DECODE(department_id, 10, 1, 0)) d10 ,
SUM( DECODE(department_id, 20, 1, 0)) d20 ,
SUM( DECODE(department_id, 30, 1, 0)) d30 ,
SUM( DECODE(department_id, 40, 1, 0)) d40 ,
SUM( DECODE(department_id, 50, 1, 0)) d50
FROM TBL_EMP




SELECT * FROM hr.EMPLOYEES, hr.DEPARTMENTS WHERE employee_id = 100


SELECT 2889/107 FROM dual

SELECT *
FROM
(SELECT * FROM hr.EMPLOYEES WHERE employee_id = 100 ) emp, hr.DEPARTMENTS



SELECT
*
FROM
(
SELECT ROWNUM rn, seqno, title, writer, regdate, updatedate
FROM
TBL_BOOK
WHERE seqno > 0
AND ROWNUM > 0 AND ROWNUM <= (3 * 10) ORDER BY seqno desc ) WHERE rn > ( (3-1) * 10)



SELECT * FROM hr.DEPARTMENTS

SELECT employee_id, dept.department_id
FROM hr.EMPLOYEES emp, hr.DEPARTMENTS dept
WHERE emp.DEPARTMENT_ID(+) = dept.DEPARTMENT_ID