CodeDrive

문제:

풀이방법:

SELECT를 사용하는 기본적인 방법이다. FROM 절에서 출력하기 원하는 컬럼명을 SELECT에 명시해주면 그 컬럼 값들만 출력해준다.

문제링크:

https://programmers.co.kr/learn/courses/30/lessons/59403

문제:

풀이방법:

아픈 동물 찾기 문제와 유사한 문제이다. 문제의 조건에서 젊은 동물은 INTAKE_CONDITION이 Aged가 아닌 경우를 뜻하기 때문에 WHERE INTAKE_CONDITION != 'Aged' 와 같이 조건문을 넣어주었고, ANIMAL_ID로 정렬을 하라고 했기 때문에 ORDER BY를 넣어주었다.

문제링크:

https://programmers.co.kr/learn/courses/30/lessons/59037

문제:

풀이방법:

정해진 조건에 해당하는 값들을 찾아야 하는 문제이다. 정해진 조건에 해당하는 부분은 WHERE절로 만족시켜야 한다. 

따라서 WHERE INTAKE_CONDITION='SICK' 은 INTAKE_CONDITION이 SICK인 값들, 즉 아픈 동물들을 뜻하게 된다. 

일반 SELECT~FROM 절에 이 WHERE 조건을 추가하면 아픈 동물의 값들만 얻을 수 있다.

문제링크:

https://programmers.co.kr/learn/courses/30/lessons/59036

문제:

풀이방법:

ORDER BY에는 옵션이 존재한다. 아무 옵션도 주지 않으면 자동으로 ASC로 되어 있다. 하지만 이 문제와 같이 역순으로 정렬을 해야 한다는 조건이 있으면 DESC를 사용하면 된다.

문제링크:

https://programmers.co.kr/learn/courses/30/lessons/59035

문제:

풀이 방법:

최댓값을 구하는 함수인 MAX를 사용하면 된다. 또한 이 값을 시간이라고 컬럼명을 정했기 때문에 AS로 시간으로 지정했다.

문제 링크:

https://programmers.co.kr/learn/courses/30/lessons/59415

문제:

풀이방법:

 모든 정보를 조회하는 문제이므로 전체를 뜻하는 *를 SELECT 부분에 사용하면 된다. 다만 ANIMAL_ID순으로 조회하라는 조건이 있었으므로 ORDER BY ANIMAL_ID를 추가해주었다.

문제링크:

https://programmers.co.kr/learn/courses/30/lessons/59034

1. 다음 테이블 인스턴트 차트를 기반으로 DEPT 테이블을 생성하십시오.

CREATE TABLE dept(ID Number(7),NAME VARCHAR2(25));
describe dept;

2.DEPARTMENT 테이블의 데이터를 DEPT 테이블에 추가하십시오.

insert into dept(id , name)
select department_id,department_name from departments;

3. 다음 테이블 인스턴스 차트를 기반으로 emp 테이블을 생성하십시오.

Create table emp(ID Number(7),LAST_NAME VARCHAR2(25),FIRST_NAME VARCHAR2(25),DEPT_ID NUMBER(7));

4.EMP 테이블의 LAST_NAME 열의 최대 길이를 50으로 수정하십시오.

ALTER TABLE emp
modify (last_name varchar2(50));

5.EMP 테이블을 삭제하십시오.

drop table emp;

6. EMPLOYEES 테이블 구조를 기반으로 EMPLOYEES2 테이블을 생성하십시오.  EMPLOYEE_ID,first_name,last_name,salary 및  department_id 열만 포함시키고 새 테이블의 열 이름을 각각 ID,First_Name,Last_name,salary 및 dept_id로 지정하십시오.

 
create table EMPLOYEES2(ID number(6),FIRST_NAME varchar(20),LAST_NAME varchar(25),SALARY number(8,2),DEPT_ID number(4));

7. employees2 테이블의 이름을 emp로 변경하십시오.

RENAME employees2 to emp;

8.EMP 테이블에서 FIRST_NAME 열을 삭제하십시오.

ALTER TABLE emp
drop (first_name);

1. EMP 테이블의 ID 열에 테이블 레벨의 PRIMARY KEY 제약 조건을 추가. 제약 조건 이름은 my_emp_id_pk로 지정하시오.

 
alter table emp
add constraint "my_emp_id_pk" primary key(id);

2. ID 열을 사용하여 DEPT 테이블에 PRIMARY_key 제약 조 건을 생성. 제약 조건 이름은 my_dept_id_pk로 지정하시오.

alter table dept
Add (constraint "my_dept_id_pk" primary key(id));

3.EMP 테이블에 DEPT_ID 열을 추가. 존재하지 않는 부서 에 사원이 배정되지 않도록 외래 키 참조를 EMP 테이블에 추가. 제약 조건 이름은 my_emp_dept_id_fk로 지정하시오.

alter table emp
add (constraint "my_emp_dept_id_fk" foreign key(dept_id) references dept(id));

4.User_constaints 뷰를 질의하여 제약 조건이 추가되었는지 확인하시오. (EMP, DEPT 테이블만 질의)

select constraint_type,constraint_name, search_condition
from user_constraints
where table_name= 'emp' or table_name= 'dept';

5.EMP 테이블을 수정하여 십진 자릿수 2, 소수점 이하 자릿수 2인 NUMBER 데이터 유형의 COMMISSION 열을 추가하시오. 커미션 값이 0보다 크도록 커미션 열에 제약 조건을 추가하시오.

alter table emp
add (COMMISSION number(2,2));
alter table emp
add (constraint "commission_ck" check (commission>0));

1.현재 날짜를 표시하는 질의를 작성하고 열 레이블을 Data로지정하십시오.

SELECT sysdate as "DATA" FROM dual;

-현재 날짜는 dual의 sysdate에 있다.

2.각 사원에 대해 사원 번호,이름, 급여및15% 인상된급여를정수로표시하십시오. 인상된 급여열의 레이블을 New Salary로 지정하십시오.

SELECT employee_id, last_name, salary*1.15 as "New Salary" FROM employees;

-애트리뷰트들을 나열하는 부분에 곱하기와 같은 사칙연산자가 들어갈 수 있다.

3.이름이 J, A 또는 M으로 시작하는 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 및 이름 길이를표시하는 질의를 작성하고 각 열에 적합한 레이블을 지정하십시오. 결과를 사원의 이름에 따라 정렬하십시오.

 
SELECT INITCAP(last_name) as "LAST_NAME",length(last_name) as "LENGTH OF LAST NAME" FROM employees WHERE last_name like 'J%' or last_name like 'A%' or last_name like 'M%' order by last_name;

-INITCAP은 첫글자만 대문자, 나머지는 소문자로 표시해주는 함수이고, length는 길이를 표시해주는 함수이다.

4.각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달 수)를 계산하여 열 레이블을 MONTHS_WORKED로 지정하십시오. 결과는 정수로 반올림하여 표시하고 근무 달 수를 기준으로 정렬하십시오

SELECT last_name, ROUND(MONTHS_BETWEEN(sysdate,hire_date)) as "MONTHS_WORKED" FROM employees ORDER BY MONTHS_WORKED;

-MONTHS_BETWEEN은 두 날짜 사이에 차이를 나타내주며 ROUND로 정수로 반올림이 가능하다.

5.각 사원에 대해 다음 항목을 생성하는 질의를 작성하십시오. earn monthly but wants <3 times salary> 열레이블을Dream Salaries로 지정하십시오 

SELECT last_name ||' earn ' || salary || ' monthly but wants '|| 3*salary as "Dream Salaries" FROM employees;

-특정한 문장으로 질의를 만들어야 하므로 이는 || 를 사용해서 이을 수 있다.

6.모든 사원의 이름과 급여를 표시하는 질의를 작성하십시오. 급여는 15자 길이로 왼쪽에 $기호가 채워진 형식으로 표기하고 열레이블을 SALARY로 지정하십시오. 

SELECT last_name,LPAD(salary,15,'$') as "SALARY" FROM employees;

-글자의 길이를 설정해주고 남은 글자들을 원하는 것으로 채워주는 함수가 LPAD이다. LPAD는 왼쪽에 빈 공간을 채우고 RPAD는 오른쪽에 빈 공간을 채워준다.

7.사원의 이름, 입사일 및 급여 검토일을 표시하십시오. 급여 검토일은 여섯달이 경과한 후 첫번째 월요일입니다. 열레이블을 REVIEW로 지정하고 날짜는 “Monday, the Thirty-First of July, 2000”과 같은 형식으로 표시되도록 지정하십시오.

SELECT last_name,hire_date, To_CHAR(NEXT_DAY(ADD_MONTHS(hire_date,6),'월요일'),'DAY,DD "of the" MONTH,YYYY') as "REVIEW" FROM employees;

-ADD_MONTHS를 통해서 6달이 지난 것을 알 수 있고, NEXT_DAY로 그 다음 첫번째 월요일을 얻을 수 있다. 또한 날짜를 특정 형식으로 만들어 줘야 하므로 이를 위해 TO_CHAR로 변환한다.

8. 이름, 입사일 및 업무 시작 요일을 표시하고 열레이블을 DAY로 지정하십시오. Monday를 시작으로 해서 요일을 기준으로 결과를 정렬하십시오. 

SELECT last_name,hire_date,To_CHAR(hire_date,'DY') as "DAY"  FROM employees ORDER BY TO_CHAR(hire_date-1,'D');

-윗 문제처럼 날짜에서 특정 시간만 가져오려 하면 TO_CHAR를 사용하도록 한다. 또한 요일을 정렬할 때 ORDER BY를 사용하는데 이 때 월요일을 기준으로 맞춰주려면 hire-date에서 하루를 빼야한다.

9.사원의 이름과 커미션을 표시하는 질의를 작성하십시오. 커미션을 받지 않는 사원일 경우 “No Commission”을 표시하십시오. 열레이블은 COMM으로 지정하십시오.

SELECT last_name,NVL(To_CHAR(commission_pct,'0.99'),'No Comission') as "COMM" FROM employees;

-커미션을 받지 않는 사원은 null일 것 이므로 NVL를 통해서 바꿔주도록 한다.

10.사원의 이름을 표시하고 급여 총액을 별표(*)로 나타내는 질의를 작성하십시오. 각 별표는 1,000달러를 나타냅니다. 급여를 기준으로 데이터를내림차순으로정렬하고열레이블을EMPLOYEES_AND_THEIR_SALARIES로지정하십시오.*/

SELECT last_name, salary, RPAD(' ',salary/1000+1,'*') as "EMPLOYEES_AND_THEIR_SALARIES" FROM employees ORDER BY salary DESC;

-RPAD를 사용해서 급여를 별표로 바꾸었다.

11. DECODE 함수를 사용하여 다음 데이터에 따라 JOB_ID 열의 값을 기준으로 모든 사원의 등급을 표시하는 질의를 작성하십시오. 업무등급 AD_PRES A, ST_MAN B, IT_PROG C, SA_REP D, ST_CLERK E, 기타0 

SELECT last_name,DECODE (job_id,'AD_PRES','A','ST_MAN','B','IT_PROG','C','SA_REP','D','ST_CLERK','E',0) as "GRADE" FROM employees;

-일정 기준으로 값을 부여하는 방법에는 크게 두 가지가 있는데 이 방법은 DECODE를 사용한 예시이다.

12. 11번 문제의 명령문을 CASE 구문을 사용하여 재작성하십시오.

SELECT last_name, 
  CASE job_id
   WHEN 'AD_PRES' THEN 'A'
   WHEN 'ST_MAN' THEN 'B'
   WHEN 'IT_PROG' THEN 'C'
   WHEN 'SA_REP' THEN 'D'
   WHEN 'ST_CLERK' THEN 'E'
  ELSE '0' END AS GRADE FROM employees;

-일정 기준으로 값을 부여하는 방법에는 크게 두 가지가 있는데 이 방법은 CASE를 사용한 예시이다.

1. 모든사원의이름, 부서번호, 부서이름을표시하는질의를작성하십시오. 

select e.last_name,d.department_id,d.department_name from employees e,departments d;

- 두 개의 릴레이션에 정보를 가져오고자 할 때에는 릴레이션에도 별칭을 부여한다.

 2. 부서80에속하는모든업무의고유목록을작성하고출력결과에부서의위치를포함시키십시오. (중복제거)

select distinct e.job_id , d.location_id from employees e INNER JOIN departments d ON e.department_id=80;

- 조인을 사용하였고, 중복을 제거하기 위해서 distinct를 사용하였다.

3. 커미션을 받는 모든 사원의 이름, 부서이름, 위치 ID 및 도시를 표시하는 질의를 작성하십시오.

select e.last_name,d.department_name,d.location_id,l.city from employees e, departments d, locations l where e.department_id=d.department_id and d.location_id=l.location_id and e.commission_pct is not null;

-JOIN 연산자가 아닌 where 조건으로 조인을 하였다.

4. 이름에 a(소문자)가 포함된 모든 사원의 이름과 부서 이름을 표시하는 질의를 작성하십시오.

select e.last_name,d.department_name from employees e,departments d where e.last_name like '%a%' and e.department_id=d.department_id;

5.Toronto에서 근무하는 모든 사원의 이름,업무,부서 번호 및 부서 이름을 표시하는 질의를 작성하십시오. (join,on 키워드 사용)

 
select e.last_name,e.job_id,d.department_id,d.department_name
from employees e Inner Join departments d on (e.department_id=d.department_id) Inner Join locations l on (d.location_id=l.location_id) where l.city= 'Toronto';

6.사원의 이름 및 사원 번호를 관리자의 이름 및 관리자 번호와 함께 표시하고, 각각의 열 레이블을 Employee,Emp#, Manager,Mgr#로 지정하십시오.(관리자가 없는 사원도 포함)

select e1.last_name as "Employee", e1.employee_id as "Emp#", e2.last_name as "Manager", e2.employee_id as "Mgr#"
from employees e1 left outer join employees e2 on e1.manager_id = e2.employee_id;

7.지정한 사원의 이름, 부서 번호 및 지정한 사원과 동일한 부서에서 근무하는 모든 사원을 표시하도록 질의를 작성하고, 각 열에 적합한 레이블을 지정하십시오. 또한 부서번호, 사원 이름, 동일한 부서에서 근무하는 사원의 이름으로 오름 차순 정렬하시오.

 
select e1.department_id as "DEPARTMENT", e1.last_name as "EMPLOYEE", e2.last_name as "COLLEAGUE"
from employees e1,employees e2 where e1.department_id=e2.department_id and not e1.last_name=e2.last_name
Order by DEPARTMENT, EMPLOYEE, COLLEAGUE;

8.Davies라는 사원보다 늦게 입사한 사원의 이름과 입사일을 표시하는 질의를 작성하십시오.

select last_name,hire_date from employees where hire_date >=(select hire_date from employees where last_name='Davies');

9.관리자보다 먼저 입사한 모든 사원의 이름 및 입사일을 관리자의 이름 및 입사일과 함께 표시하는 질의를 작성하십시오.

select e1.last_name,e1.hire_date,e2.last_name,e2.hire_date from employees e1 Inner Join employees e2 on (e1.manager_id=e2.employee_id)
where e1.hire_date < e2.hire_date;

INSERT, DELETE, UPDATE문

INSERT문은 기존의 릴레이션에 투플을 삽입하는 질의이다. 참조되는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건의 위배가 발생하지 않으나 참조하는 릴레이션에 투플이 삽입되는 경우에는 참조 무결성 제약조건을 위배할 수 있다. 

INSERT

INTO 릴레이션(애트리뷰트1, ...., 애트리뷰트n)

VALUES (값1, ... , 값n);

DELETE문은 한 릴레이션으로부터 한 개 이상의 투플들을 삭제한다. 참조되는 릴레이션의 삭제 연산의 결과로 참조 무결성 제약조건이 위배될 수 있으나, 참조하는 릴레이션에서 투플을 삭제하면 참조 무결성 제약조건을 위배하지 않음

DELETE FROM 릴레이션 WHERE 조건;

UPDATE문은 한 릴레이션에 들어 있는 투플들의 애트리뷰트 값들을 수정한다. 기본 키나 외래 키에 속하는 애트리뷰트의 값이 수정되면 참조 무결성 제약조건을 위배할 수 있다.

UPDATE 릴레이션

SET 애트리뷰트 = 값 또는 식[ , ...]

WHERE 조건;

트리거와 주장

트리거란 명시된 이벤트가 발생할 때마다 DBMS가 자동적으로 수행하는, 사용자가 정의하는 문이다. 데이터베이스의 무결성을 유지하기 위한 도구이며, 트리거를 이벤트-조건-동작(Event-Condition, Action) 규칙이라고도 부른다.

CREATE TRIGER <트리거 이름>

AFTER <트리거를 유발하는 이벤트> ON <릴레이션> [WHEN <조건>]

BEGIN <SQL문(들)> END;

이벤트의 가능한 예는 테이블에 삽입, 삭제, 수정등이 있다.

주장이란 제약조건을 위반하는 연산이 수행되지 않도록 하는 것이다.

CREATE ASSERTION 이름 CHECK 조건;

일반적으로 두 개 이상의 테이블에 영향을 미치는 제약조건을 명시하기 위해 사용된다.

SQL문 실습

1. 급여가 $12,000를 넘는 사원의 이름과 급여를 표시

2. 사원 번호가 176인 사원의 이름과 부서 번호를 표시

3.급여가 $5,000에서 $12,000 사이에 포함되지 않는 모든 사원의 이름과 급여를 표시

4. 2007년 2월 20일과 2007년 5월 1일 사이에 입사한 사원의 이름, 업무 ID 및 시작일을 표시하되, 시작일을 기준으로 오름차순으로 정렬하는 질의

5. 부서 20 및 50에 속하는 모든 사원의 이름과 부서 번호를 이름을 기준으로 영문자순으로 표시

6.급여가 $5,000와 $12,000 사이이고 부서 번호가 20 또는 50인 사원의 이름과 급여를 나열하고, 열 레이블을 Employee와 Monthly Salary로 각각 지정

7.1994년에 입사한 모든 사원의 이름과 입사일을 표시

8.관리자가 없는 모든 사원의 이름과 업무 ID를 표시

9.커미션을 받는 모든 사원의 이름,급여 및 커미션을 급여 및 커미션을 기준으로 내림차순으로 정렬하여 표시

10.이름의 세 번째 문자가 a인모든사원의이름을표시

11.이름에 a와 e가 있는 모든 사원의 이름을 표시

12. 업무가 영업 사원(SA_REP)또는 사무원(ST_CLERK)이면서급여가 $2,500, $3,500 또는$7,000가 아닌 모든 사원의 이름, 업무 및 급여를 표시

13.커미션 비율이 20%인 모든 사원의 이름, 급여 및 커미션을 표시

SQL은 비절차적 언어이므로 사용자는 자신이 원하는(what)만 명시하며, 원하는 것을 처리하는 방법(how)은 명시할 수 없다. 또한 SQL은 대화식 SQL , 내포된 SQL 두 가지의 인터페이스를 가지고 있으며, 데이터 검색, 데이터 조작어, 데이터 정의어, 트랜잭션 제어, 데이터 제어어로 구성되어 있다.

데이터 정의어

스키마의 생성과 제거의 기능을 제공하며 다음과 같이 정의한다.

CREATE TABLE STUDENTS

(STUNO NUMBER NOT NULL,

STUNAME CHAR(10),

GRADE NUMBER,

CLASS NUMBER,

PRIMARY KEY(STUNO), [ 제약조건 ] )

STUDENTS의 스키마를 만드는데 그 안에 STUNO, STUNAME, GRADE, CLASS 와 같은 애트리뷰트를 가지는 것을 의미한다. 각 애트리뷰트명 옆에 써져 있는 것은 데이터 타입이고 다음 표와 같은 것들이 있다. 그리고 마지막에 PRIMARY KEY(STUNO)와 같이 기본키를 명시하고 제약 조건들을 명시해줄 수 있다.

이 밖에도 릴레이션 제거(DROP TABLE CLASSROOM), ALTER TABLE, 인덱스 생성 (CREATE INDEX)과 같은 기능들을 지원한다.

SELECT문

관계 데이터베이스에서 정보를 검색하는 SQL문으로 관계 대수의 실렉션과 의미가 완전히 다르다.

관계 대수의 실렉션, 프로젝션, 조인, 카티션 곱 등을 결합한 것과 같다고 볼 수 있으며 가장 많이 사용된다.

기본적인 SQL 질의에서 SELECT 절과 FROM절만 필수적인 절이고, 나머지는 선택 사항이다.

SELECT  [DISTINCT] 애트리뷰트(들)

FROM 릴레이션(들)

[WHERE 조건 [중첩 질의]] [GROUP BY 애트리뷰트(들)] [HAVING 조건] [ORDER BY 애트리뷰트(들) [ASC|DESC]];

다음은 SELECT문에서 사용할 수 있는 기능들이다.

별칭(alias)

서로 다른 릴레이션에 동일한 이름을 가진 애트리뷰트가 속해 있을 때 애트리뷰트의 이름을 구분하는 방법

FROM EMPLOYEE AS E, DEPARTMENT AS D

모든 애트리뷰트 검색

SELECT  * FROM DEPARTMENT;

DISTINCT

모든 상이한 값들만 검색하고자 할 때 사용한다. SELECT DISTINCT TITLE FROM EMPLOYEE;

WHERE

특정한 조건의 투플들만 검색하고자 할 때 사용한다.

2번 부서에 근무하는 사원들의 정보를 검색해라 == SELECT * FROM EMPLOYEE WHERE DNO=2;

문자열 비교

이씨 성을 가진 사원들의 이름, 직급, 소속 부서 번호를 검색하라.

SELECT EMPNAME, TITLE, DNO FROM EMPLOYEE WHERE EMPNAME LIKE '이%' ;

ORDER BY절

사용자가 SELECT문에서 질의 결과의 순서를 명시하지 않으면 릴레이션에 투플들이 삽입된 순서대로 사용자에게 제시된다. ORDER BY 절을 명시하면 해당 애트리뷰트를 기준으로 검색 결과를 정렬한다. SELECT문에 가장 마지막에 사용되며 디폴트 정렬 순서를 오름차순(ASC)이며, DESC를 지정하며 내림차순으로 정렬할 수 있다.

단, SELECT절에 명시한 애트리뷰트들을 사용해서 정렬해야 한다.

그룹화

GROUP BY절에 사용된 애트리뷰트에 동일한 값을 갖는 투플들이 하나의 그룹으로 묶임 이 애트리뷰트를 그룹화 애트리뷰트라고 한다. SELECT 절에는 집단 함수, 그룹화 애트리뷰트들만 나타날 수 있다.

HAVING절

어떤 조건을 만족하는 그룹들에 대해서만 집단 함수를 적용할 수 있다. 따라서 각 그룹마다 하나의 값을 갖는 애트리뷰트를 사용해서 각 그룹이 만족해야 하는 조건을 명시한다. 그룹화 애트리뷰트에 같은 갖는 투플들의 그룹에 대한 조건을 나타내고, 이 조건을 만족하는 그룹들만 질의 결과에 나타난다.

조인

두 개 이상의 릴레이션으로부터 연관된 투플들을 결합한다. 일반적인 형식은 FROM절에 두 개 이상의 릴레이션들이 열거되고, 두 릴레이션에 속하는 애트리뷰트들을 비교하는 조인 조건이 WHERE절에 포함된다. 흔히 =(비교 연산자)로 연결한다.

이 밖에도 많은 기능이 있으나 실습편에서 소개하겠습니다.

중첩 질의

질의의 where 또는 FROM절에 다시 SELECT문이 포함되는 것이며 부 질의라고 한다.

부 질의에서 한 개의 스칼라 값이 반환되는 경우 where절에서 상수 또는 애트리뷰트가 사용될 위치에 나타날 수 있다.

만약 릴레이션이 반환되는 경우에는 IN,  ANY, ALL, EXISTS와 같은 연산자를 사용해야 한다.