오라클로 배우는 데이터베이스 입문 - 01~02 문제풀이

01. 데이터 베이스

문제

정답

02. 관계형 DB, 오라클 DB

문제

정답

3. SQL 쿼리

Q1.

1. SELECT

2. FROM

3. 별칭

 

Q2.

--Q2
SELECT DISTINCT JOB FROM EMP;

Q3.

SELECT EMPNO AS EMPLOYEE_NO
       ,ENAME AS EMPLOYEE_NAME
       ,JOB
       ,MGR AS MANAGER1
       ,HIREDATE
       ,SAL AS SALARY
       ,COMM AS COMMISSION
       ,DEPTNO AS DEPARTMENT_NO
       FROM EMP ORDER BY DEPTNO DESC, ENAME ASC ;

Q4. 125P 문제

1)

--문제 1번 125P
SELECT * FROM EMP WHERE ENAME LIKE '%S';
--문제 2번
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP WHERE DEPTNO = 30 AND JOB = 'SALESMAN';
--문제 3번
--집합연산자 사용 X
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO 
FROM EMP WHERE DEPTNO IN (20,30)
AND SAL > 2000;

--집합연산자 사용 O (UNION)
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO 
FROM EMP WHERE DEPTNO = 20 AND SAL > 2000
UNION
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO 
FROM EMP WHERE DEPTNO = 30
AND SAL > 2000;

--문제 4번
SELECT * FROM EMP WHERE SAL < 2000 OR SAL > 3000;
SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3000;

--문제 5번
SELECT ENAME, EMPNO, SAL, DEPTNO FROM EMP WHERE DEPTNO = 30 AND ENAME LIKE '%E%' 
AND SAL NOT BETWEEN 1000 AND 2000 ;

--문제 6번
SELECT * FROM EMP WHERE COMM IS NULL 
                    AND MGR IS NOT NULL
                    AND JOB IN ('MANAGER', 'CLERK')
                    AND ENAME NOT LIKE '_L%';