SELECT 문 실습

• 테이블

-- 1 emp 테이블에서 사원번호(EMPNO), 사원이름(ENAME), 월급(SAL)을 출력하시오 .
DESC EMP;
SELECT * FROM EMP;
SELECT EMPNO, ENAME, SAL FROM EMP;

-- 2 emp 테이블에서 사원이름과 월급을 출력하는데 컬럼명(AS)은 이름, 월급으로 변경하여 출력하시오. 
SELECT ENAME AS 이름, SAL AS 월급 FROM EMP;
SELECT ENAME "이름", SAL "월급" FROM EMP;

-- 3 emp 테이블에서 사원번호 사원이름 월급 연봉(SAL*12)을 구하고 각각 컬럼명을 사원번호, 사원이름, 월급으로 변경하여 출력하시오.
SELECT EMPNO AS 사원번호, ENAME AS 사원이름, SAL AS 월급, SAL*12 AS 연봉 FROM EMP;

-- 4 emp 테이블에서 업무(job)를 중복(DISTINCT)되지 않게 표시하시오.
SELECT DISTINCT JOB FROM EMP; 

-- 5 emp Table의 사원명과 업무로 연결 해서 표시하고 컬러명은 Employee and Job로 표시하시오.
--'('문자열 ||은 연결한다는 의미
SELECT '(' || ename ||','||job||')' AS "Employee and Job" FROM EMP;

--6) emp 테이블에서 사원번호가 7698 인 사원의 이름, 업무, 급여를 출력하시오.
SELECT ENAME, JOB, SAL FROM EMP WHERE EMPNO=7698;

-- 7) emp 테이블에서 사원이름이 SMITH인 사람의 이름과 월급, 부서번호를 구하시오.
SELECT ENAME, SAL, DEPTNO FROM EMP WHERE ENAME='SMITH';

-- 8) 월급이 2500이상 3500미만인(BETWEEN AND) 사원의 이름, 입사일, 월급을 구하시오.
SELECT ENAME, HIREDATE, SAL FROM EMP WHERE SAL>=2500 AND SAL<3500;
SELECT ENAME, HIREDATE, SAL FROM EMP WHERE SAL BETWEEN 2500 AND 3500;

-- 9) 급여가 2000에서 3000사이에 포함되지 않는(NOT) 사원의 이름, 업무, 급여를 출력하시오.
SELECT ENAME, HIREDATE, SAL FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3000;

-- 10) 81년 05월 01일과 81년 12월 03일 사이에 입사한 사원의 이름, 급여, 입사일을 출력하시오.
SELECT ENAME, SAL, HIREDATE FROM EMP WHERE HIREDATE BETWEEN '81/05/01' AND '81/12/03';

-- 11) emp테이블에서 사원번호가 7566,7782,7934인 사원을 제외한 사람들의 사원번호,이름,  월급을 출력하시오.
SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO NOT IN(7566, 7782, 7934);

-- 11-1) emp테이블에서 사원번호가 7566,7782,7934인 사원을 사람들의 사원번호,이름,  월급을 출력하시오.
SELECT EMPNO, ENAME, SAL FROM EMP WHERE EMPNO IN(7566, 7782, 7934);

-- 12) 부서번호 30(deptno)에서 근무하며 월 2,000달러 이하를 받는 81년05월01일 이전에 입사한 사원의 이름, 급여, 부서번호, 입사일을 출력하시오.
SELECT ENAME, SAL, EMPNO ,HIREDATE 
FROM EMP 
WHERE DEPTNO = 30 AND SAL <= 2000 AND HIREDATE <= TO_DATE('1981-05-01', 'yyyy-mm-dd');

-- 13) emp테이블에서 급여가 2,000와 5,000 사이고 부서번호가 10 또는 30인 사원의 이름과 급여,부서번호를 나열하시오.
SELECT ENAME, SAL, EMPNO 
FROM EMP 
WHERE SAL BETWEEN 2000 AND 5000 OR EMPNO IN(10, 30);

-- 14) 업무가 SALESMAN 또는 MANAGER이면서 급여가 1,600, 2,975 또는 2,850이 아닌 모든 사원의 이름, 업무 및 급여를 표시하시오.
SELECT ENAME, JOB, SAL 
FROM EMP 
WHERE JOB IN('SALESMAN', 'MANAGER') AND SAL NOT IN(1600,2975,2850); --영문 데이터는 대 소문자의 값이 다르므로 구분

-- 15) emp테이블에서 사원이름 중 S가 포함되지 않은 사람들 중 부서번호가 20인 사원들의 이름과 부서번호를 출력하시오.
SELECT ENAME, DEPTNO
FROM EMP
WHERE ENAME NOT LIKE '%S%' AND DEPTNO = 20;

-- 16) emp테이블에서 이름에 A와 E가 있는 모든 사원의 이름을 표시하시오.
SELECT ENAME 
FROM EMP 
WHERE ENAME LIKE '%A%' AND ENAME LIKE '%E%';

-- 17) emp테이블에서 관리자가 없는 모든 사원의 이름과 업무를 표시하시오.
-- 상관(MGR) 속성의 값이 NULL인 사원의 정보를 조회(널(NULL) 조회시 IS NULL)
SELECT ENAME,JOB 
FROM EMP 
WHERE MGR IS NULL;

-- 18) emp테이블에서 커미션 항목이 입력된 사원들의 이름과 급여, 커미션을 구하시오.
SELECT ENAME, SAL, COMM 
FROM EMP 
WHERE COMM IS NOT NULL;

-- 19) emp 테이블에서 사원번호, 급여, 부서번호를 출력하시오. 단, 급여가 많은 순서 대로 = 정렬
SELECT EMPNO, SAL, DEPTNO FROM EMP ORDER BY SAL DESC;

-- 20) emp 테이블에서 사원번호, 급여, 부서번호를 출력하시오. 단, 급여가 적은 순서 대로 = 정렬
SELECT EMPNO, SAL, DEPTNO FROM EMP ORDER BY SAL ASC;

-- 21) emp 테이블에서 사원번호, 급여, 직급, 입사일 출력하시오. 단, 직급으로 오름차순, 급여로 내림차순
SELECT EMPNO, SAL, JOB, HIREDATE FROM EMP ORDER BY JOB ASC,SAL DESC;

-- 22) emp 테이블에서 급여가 2000이상인 사원의 사원번호, 사원이름, 급여 출력
SELECT EMPNO, ENAME, SAL FROM EMP WHERE SAL >=2000;

-- 23) emp 테이블에서 부서번호가 10번인 사원들의 모든 정보 출력
SELECT * FROM EMP WHERE DEPTNO = 10;

-- 24) emp 테이블에서 입사일이 '81/02/20'인 사원의 사원번호, 이름, 입사일 출력
SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE='81/02/20';

--25)emp 테이블에서 직업이 'SALESMAN'인 사람들의 이름,직업,급여를 출력해보세요.단, 급여가 높은 순서대로
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB='SALESMAN';

--26)부서번호가 10번인 사원들의 급여를 출력하되 10% 인상 된 금액으로 출력
SELECT SAL+(SAL*0.1) FROM EMP WHERE DEPTNO=10;

--27) 급여가 3000이상인 사원들의 모든 정보를 출력하세요
SELECT * FROM EMP WHERE SAL >=3000;

--28)부서번호가 30번이 아닌 사람들의 이름과 부서번호를 출력해보세요.
SELECT ENAME, DEPTNO FROM EMP WHERE NOT DEPTNO=30;

--29)부서번호가 10번이고 급여가 3000 이상인 사원들의 이름과 급여를 출력하세요
SELECT ENAME, SAL FROM EMP WHERE DEPTNO=10 AND SAL>=3000;

--30)직업이 SALESMAN 이거나 MANAGER인 사원의 사원번호와 부서번호를 출력하세요
SELECT EMPNO, DEPTNO FROM EMP WHERE JOB IN('SALESMAN', 'MANAGER');

실습 2

--3-1) 모든 도서의 이름과 가격 검색
SELECT BOOKNAME, PRICE 
FROM BOOK;
-- 내용을 보고 싶다면,
desc book;

--3-1) 두 개 위치 변경 변형
SELECT PRICE, BOOKNAME 
FROM BOOK;

--3-2) 모든 도서의 도서번호, 도서이름, 출판사, 가격 검색
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK;

--3-3) 도서 테이블에 있는 모든 출판사 검색(중복제거)
SELECT DISTINCT PUBLISHER 
FROM BOOK;

--3-4)가격이 20,000원 미만인 도서를 검색하시오.
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK 
WHERE PRICE <20000;

--3-5)가격이 10,000원 이상 20,000 이하인 도서를 검색하시오.
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK 
WHERE PRICE BETWEEN 10000 AND 20000;

--3-6)출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’인 도서를 검색하시오.
-- IN이나 NOT IN 사용 가능
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK 
WHERE PUBLISHER='굿스포츠' OR PUBLISHER='대한미디어';

SELECT * 
FROM BOOK 
WHERE PUBLISHER IN('굿스포츠', '대한미디어');

--3-6 변형)출판사가 ‘굿스포츠’ 혹은 ‘대한미디어’이 아닌 도서를 검색하시오.
SELECT * 
FROM BOOK 
WHERE PUBLISHER NOT IN('굿스포츠', '대한미디어');

--3-7)‘축구의 역사’를 출간한 출판사를 검색하시오.
-- 문자열의 패턴을 비교 LIKE 연산자
SELECT BOOKNAME, PUBLISHER 
FROM BOOK 
WHERE BOOKNAME='축구의 역사';

SELECT BOOKNAME, PUBLISHER 
FROM BOOK 
WHERE BOOKNAME LIKE '축구의 역사';

--3-8)도서이름에 ‘축구’가 포함된 출판사를 검색하시오.
-- 와일드 문자 : %(모두) - 아무 문자열
SELECT BOOKNAME, PUBLISHER 
FROM BOOK 
WHERE BOOKNAME LIKE '%축구%';

--3-9) 도서이름의 왼쪽 두 번째 위치에 ‘구’라는 문자열을 갖는 도서를 검색하시오.
-- 문자열 패턴 _(언더바) : 특정 위치에 한 문자만 대치할 때 사용
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK 
WHERE BOOKNAME LIKE '_구%';

--3-10) 축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하시오.
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK 
WHERE BOOKNAME LIKE '%축구%' AND PRICE >=20000;

-- 문자열을 검색할 때, LIKE와 같이 사용하는 와일드 문자
-- +(문자열을 연결) '골프'+'바이블' : '골프 바이블'
-- % : 0개 이상의 문자열과 일치
-- '[0-5]%' : 0~5 사이의 숫자로 시작하는 문자열
-- [^] 한 개의 문자와 불일치 '[^0-5]%' : 0-5 사이의 숫자로 시작하지 않는 문자열
-- _(언더바) : 특정 위치의 한 개의 문자와 일치

--3-11) 위와 동일
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK 
WHERE PUBLISHER='굿스포츠' OR PUBLISHER='대한미디어';

--3-12) 도서를 이름순으로 검색하시오.
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK 
ORDER BY BOOKNAME ASC;

--3-13) 도서를 가격순으로 검색하고, 가격이 같으면 이름순으로 검색하시오.
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK 
ORDER BY PRICE ASC ,BOOKNAME ASC;

--3-14) 도서를 가격의 내림차순으로 검색하시오. 만약 가격이 같다면 출판사의 오름차순으로 검색하시오
SELECT BOOKID, BOOKNAME, PUBLISHER, PRICE 
FROM BOOK 
ORDER BY PRICE DESC ,PUBLISHER ASC;

새로운 테이블 만들어서 접속하기 위한 방법

system에 계정 만들기 & 권한주기

• DBA 접속 : 새로운 계정 만들기

-- 새로운 batman 계정 만들기(계정과 비번)
CREATE USER batman IDENTIFIED BY godam;

GRANT CONNECT, RESOURCE TO batman;

-- 새로운 계정 만들기 위해 system 로그인(DBA) 

-- 새로운 사용자(user : madang pwd : madang)
-- madang 데이터베이스에서 사용 할 테이블 공간 지정
CREATE TABLESPACE md_tbs
     DATAFILE 'D:\madang\oradata\md_tbs_data01.dbf'
     SIZE 10M;

-- 마당이라는 사용자가 사용할 패스워드 지정 하고 테이블을 만들어줌      
CREATE USER madang IDENTIFIED BY madang
    DEFAULT TABLESPACE md_tbs;
    
-- Session 권한, 접속 권한=> CONNECT, 객체 접근 권한 => RESOURECE grant 주어야 한다.
GRANT CONNECT, RESOURCE TO madang;
-- view 생성, 동의어 (시노임) 생성 권한 획득
GRANT CREATE VIEW, CREATE SYNONYM TO madang;

-- 테이블 스페이스에 대한 제한 없이 사용하도록 권한
GRANT UNLIMITED TABLESPACE TO madang;

-- 잠기지 않도록 함
ALTER USER madang ACCOUNT UNLOCK;

-- 새로운 batman 계정 만들기
CREATE USER batman IDENTIFIED BY godam;

GRANT CONNECT, RESOURCE TO batman;

퀴즈

-- P.92 #2.
SELECT DISTINCT JOB FROM emp;

-- #3.
SELECT EMPNO AS EMPLOYEE_NO, ENAME AS EMPLOYEE_NAME, MGR AS MANAGER, SAL AS SALARY, 
COMM AS COMMISSION, DEPTNO AS DEPARTMENT_NO
FROM EMP
ORDER BY EMPNO DESC, ENAME ASC;

-- P.125 #1
SELECT * 
FROM EMP 
WHERE ENAME LIKE '%S';

-- # 2.
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO=30 AND JOB='SALESMAN';

-- #3.
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN(20, 30) AND SAL > 2000;

-- INTERSECT사용한 방식
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN(20, 30)
INTERSECT
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE SAL > 2000;

-- # 4.
SELECT *
FROM EMP
WHERE SAL > 3000 OR SAL < 2000;

-- #5.
SELECT ENAME, EMPNO, SAL, DEPTNO
FROM EMP
WHERE ENAME LIKE '%E%' AND DEPTNO = 30 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%';

회고

SELECT문의 기초를 배운 하루 확실히 SELECT 기본은 자주 봐서 엄청 어렵거나 하지않다. 이거 배우면서 겸사겸사 정보처리기사 같이 병행해서 준비하면 될 듯하다.