Skip to content

Instantly share code, notes, and snippets.

@Gumball12

Gumball12/week-1.md

Last active September 1, 2021 08:38
Show Gist options
  • Save Gumball12/e495eef7fe1614496050bbfc110cab47 to your computer and use it in GitHub Desktop.
Save Gumball12/e495eef7fe1614496050bbfc110cab47 to your computer and use it in GitHub Desktop.
Download ZIP
practice DB
Raw
week-1.md 
/**
 * create slave table, and then insert rows
 */

create table salves (이름 char(10) primary key, 월급 number, 나이 number);

insert into slaves values('규', 400000, 5);
insert into slaves values('설', 5000000, 73);
insert into slaves values('준', 3000000, 53);
insert into slaves values('주', 7000, 7);

select * from slaves;

select concat('slave: ', 이름) from slaves;

/**
 * using Regular expression (REGEXP_LIKE)
 */

select * from slaves where regexp_like (월급, '.*3.*');

select * from slaves where regexp_like (월급, '^고');
Raw
week-2.md 
/**
 * create slave table, and then insert rows
 */

create table salves (이름 char(10) primary key, 월급 number, 나이 number);

insert into slaves values('규', 400000, 5);
insert into slaves values('설', 5000000, 73);
insert into slaves values('준', 3000000, 53);
insert into slaves values('주', 7000, 7);

select * from slaves;

select concat('slave: ', 이름) from slaves;

/**
 * using Regular expression (REGEXP_LIKE)
 */

select * from slaves where regexp_like (월급, '.*3.*');

select * from slaves where regexp_like (월급, '^고');
Raw
week-3.md

https://i.imgur.com/8Ji7Fou.png

다음과 같이 함수를 사용할수도 있다. 어디서든.

select to_chart(sysdate, 'YYYY Month fmDD fmHH : MI : SS AM') from dual;

select ename 이름, hiredate 입사일 from emp where to_char(hiredate, 'YY') = '81';

fm을 붙이면 1996-01-16으로 나오는 것이 1996-1-16이 되어버린다.

다음과 같이 연산을 할 수도 있다.

select ename, 12 * sal + comm from emp;

null 값에 대해 계산을 진행하고 싶을 때는 다음과 같이 nvl() 함수를 이용하여 진행한다. null은 계산에서 제외되기 때문.

select ename, 12 * sal * nvl(comm, 0) from emp;

select ename, mgr from emp where nvl(mgr, -1) = -1;

select avg(comm) from emp;          => 550

select avg(nvl(comm, 0)) from emp;  => 169.230769

regexp 말고도 like를 사용할 수 있다.

select avg(sal), max(sal), min(sal), sum(sal) from emp where job like 'SALES%';

select avg(sal), max(sal), min(sal), sum(sal) from emp where regexp_like(job, '^SALES.*');

count() 함수는 말 그대로 해당 column의 row 갯수 세는건데, null 값은 제외하고 센다. 만약 포함하고 싶으면 *를 적으면 된다.

select count(*) from emp where deptno = 30;       => 6
select count(comm) from emp where deptno = 30;    => 4
select count(sal) from emp where deptno = 30;     => 6

다음과 같이 grouping을 할 수 있다.

select deptno, round(avg(sal), 1) from emp group by deptno;

deptno 기준으로 grouping 되며, 값으로는 deptno와 round(avg(sal), 1)이 들어가겠지.

다음같이 order도 가능하다.

select deptno, round(avg(sal), 1) rounded from emp group by deptno order by rounded desc;

다음과 같이 여러 값을 기준으로도 grouping이 가능하다.

select deptno, job, sum(sal) from emp group by job, deptno order by deptno;
select deptno, job, sum(sal) from emp group by deptno, job order by deptno;

위 둘은 같은 결과를 갖는다. 참고로, 여러 row에 대해 gropuing을 진행하는 경우 당연히 서로의 row 수가 맞아떨어져야 할 것이다.

단, group by를 사용한 경우 where의 사용이 불가능하다. 이 때는 having을 사용하여 비교를 진행한다.

select deptno, avg(sal), max(sal) from emp where max(sal) > 2000 group by deptno;   => ERROR

select deptno, avg(sal), max(sal) from emp having max(sal) > 2000 group by deptno;  => OK!
Raw
week-4.md

Alias

select e.ename 이름, e.sal 급여 from emp e;

이렇게 field 뿐만 아니라, 테이블까지 alias를 지정할 수 있다.

Subquery

https://i.imgur.com/dZNGZMO.png

SQL 문장 안에 nested된 select 구문을 사용할 수 있다.

select ename, job
	from emp
	where job = (select job from emp where empno = 7369);

# OUTPUT #
ENAME      JOB
---------- ---------
SMITH      CLERK
ADAMS      CLERK
JAMES      CLERK

이렇게 괄호를 통해 nested된 select 구문을 사용하였다.

subquery는 사용 시 규칙이 있는데, 각각 다음과 같다.

  • subquery는 괄호로 묶어 사용해야만 한다.
  • subquery는 연산자의 오른쪽에 나타나야만 한다.
  • 다음 연산자들에 대해 subquery를 사용할 수 있다.
    • single-row subquery: =, >, >=, <, <=, <>, !=
    • multiple-row subquery: IN, NOT, IN, ANY, ALL, EXISTS

multiple-row: ANY

subquery 사용 시 사용되는 연산자 중 ANY는 다음과 같은 의미를 갖는다.

select empno, ename, job
	from emp
	where sal < ANY(
		select sal
		from emp
		where job = 'MANAGER'
	) and job <> 'MANAGER';

# OUTPUT #
     EMPNO ENAME      JOB
---------- ---------- ---------
      7369 SMITH      CLERK
      7900 JAMES      CLERK
      7876 ADAMS      CLERK
      7521 WARD       SALESMAN
      7654 MARTIN     SALESMAN
      7844 TURNER     SALESMAN
      7499 ALLEN      SALESMAN

ANY 연산자는 subquery 결과값 중 어느 하나라도 만족이 되면 데이터를 반환하기에, 위와 같은 결과값이 나온다.

즉, job이 'MANAGER'인 데이터 중 가장 작은 sal 값보다 작고, job이 'MANAGER'만 아니면 결과가 출력된다는 말.

multiple-row: ALL

ALL은 이와는 반대로 모든 결과값을 만족해야만이 데이터를 반환하는 연산자이다.

select empno, ename, job
	from emp
	where sal > ALL(
		select sal
		from emp
		where job = 'MANAGER'
	) and job <> 'MANAGER';

# OUTPUT #
     EMPNO ENAME      JOB
---------- ---------- ---------
      7902 FORD       ANALYST
      7788 SCOTT      ANALYST
      7839 KING       PRESIDENT

즉, job이 'MANAGER'인 데이터들 중 가장 큰 sal 값보다 크고, job이 'MANAGER'가 아니면 결과가 출력된다는 말.

multiple-row: EXISTS

데이터가 존재하면 true를 반환한다.

select empno, ename, sal
	from emp e
	where exists(
		select *
		from emp
		where e.empno = mgr
	);

# OUTPUT #
     EMPNO ENAME             SAL
---------- ---------- ----------
      7566 JONES            2975
      7698 BLAKE            2850
      7788 SCOTT            3000
      7839 KING             5000
      7902 FORD             3000

e.empno가 subquery의 mgr과 동일한 row가 있을 때 true를 반환한다는 의미.

참고로 subquery 부분에서 반드시 *가 들어갈 필요는 없다. sal이나 mgr이나 뭐 아무거나 들어가도 됨.

Multiple-col

subquery의 결과값이 두 개 이상의 col을 반환하는 subquery를 의미.

IN

subquery가 먼저 실행되고, 이 결과를 main query로 넘겨준다.

select empno, sal, deptno
	from emp
	where (sal, deptno)
		in (
			select sal, deptno
			from emp
			where deptno = 30
				and comm is not null
			);

# OUTPUT #
     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7499       1600         30
      7654       1250         30
      7521       1250         30
      7844       1500         30

즉, subquery를 통해 반환된 sal과 deptno의 데이터가 존재해야만이 결과가 출력됨을 의미한다.

subquery의 결과가 모두 main query로 들어가는 것이기에, 반드시 selected 된 subquery의 col과 main query의 col들이 서로 짝이 맞아야겠지.

또는 다음과 같이 사용할 수도 있다.

select ename, deptno, job
	from emp
	where
		deptno in (20, 30) and
		job in ('CLEAK', 'SALESMAN');

# OUTPUT #
ENAME          DEPTNO JOB
---------- ---------- ---------
SMITH              20 CLERK
ALLEN              30 SALESMAN
WARD               30 SALESMAN
MARTIN             30 SALESMAN
TURNER             30 SALESMAN
ADAMS              20 CLERK
JAMES              30 CLERK

즉, deptno는 20 또는 30. job은 'CLEAR', 또는 'SALESMAN'인 row만 출력하라는 의미.

또는 다음과 같이도 가능하다.

select ename, deptno, job
	from emp
	where (deptno, job) in (
		(20, 'CLERK'),
		(30, 'SALESMAN')
	));

# OUTPUT #
ENAME          DEPTNO JOB
---------- ---------- ---------
SMITH              20 CLERK
ALLEN              30 SALESMAN
WARD               30 SALESMAN
MARTIN             30 SALESMAN
TURNER             30 SALESMAN
ADAMS              20 CLERK

의미는 알겠지만, deptno와 job이 (20, 'CLERK') 또는 (30, 'SALESMAN') 인 데이터들만 출력하라는 의미.

Assignment

1. 부서별로 가장 급여를 많이 받는 사원의 정보 출력

답:

select empno, ename, sal, deptno from emp where sal in ( select max(sal) from emp group by deptno ) order by sal;

또는

select empno, ename, sal, deptno from emp where sal in ( select max(sal) from emp group by deptno ) order by deptno desc;

2. 급여 등급이 1등급인 직원의 이름과 급여 출력

답:

select ename, sal from emp where sal > ( select losal from salgrade where grade = 1 ) and sal < ( select hisal from salgrade where grade = 1 );

또는

select ename, sal from emp where sal between ( select losal from salgrade where grade = 1 ) and ( select hisal from salgrade where grade = 1 );

3. 평균급여가 최고인 부서의 번호와 평균 급여는?

답:

select 부서번호, 평균급여 from ( select deptno 부서번호, avg(sal) 평균급여 from emp group by deptno ) where 평균급여 = ( select max(avg(sal)) from emp group by deptno );

또는

select deptno 부서번호, avg(sal) 평균급여 from emp group by deptno having avg(sal) = ( select max(avg(sal)) from emp group by deptno );

4. 이름이 'SCOTT'인 사원의 부서명 검색

답:

select ename 사원이름, job 부서명 from emp where ename in ('SCOTT');

Raw
week-5.md

select all from emp

select all from dept

join

using()을 사용해 equal value로 테이블을 합치는 연산.

select e.empno, e.ename, d.dname, e.sal
	from emp e
	join dept d
		using(deptno)
	where sal = (select max(sal) from emp);

물론 다음과 같이 on을 사용하여 조건으로 합칠 수도 있다.

select e.empno, e.ename, d.dname, e.sal
	from emp e
	join dept d
		on e.deptno = d.deptno
	where sal = (select max(sal) from emp);

nested query

저번 주에도 다루었는데, 다음을 잘 이용하도록 한다.

select ename, d.dname
	from emp
	join dept d
		using(deptno)
	where deptno = (
		select deptno
			from emp
			where ename = 'SMITH'
		);

get day

요일을 구할 수도 있다.

select to_char(sysdate, 'd') /* 1: 일, 2: 월, ..., 7: 토 */
	from dual;

문제는 이렇게 하면 숫자가 나온다는 것

decode

이는 decode() 함수를 통해 가능하다.

select decode(
		to_char(sysdate, 'd'),
		1, '일',
		2, '월',
		3, '화',
		4, '수',
		5, '목',
		6, '금',
		7, '토'
	) from dual;

string concatenation

|| 연산자를 통해 가능하다

select decode(
		to_char(sysdate, 'd'),
		1, '일',
		2, '월',
		3, '화',
		4, '수',
		5, '목',
		6, '금',
		7, '토'
	) || '요일' as 오늘은 from dual;
Raw
week-9.md

multiple join

간단히 다음과 같이 가능하다.

select sname
	from sp
	join s on sp.s# = s.s#
	join p on sp.p# = p.p#;

문제풀이

테이블 구성은 다음과 같음

CREATE TABLE S (
       S#     CHAR(3)    PRIMARY KEY,
       SNAME CHAR(5),
       STATUS NUMBER(2),
       CITY  CHAR(7));


CREATE TABLE P (
       P#     CHAR(3)  NOT NULL PRIMARY KEY,
       PNAME  CHAR(5),
       COLOR  CHAR(5),
       WEIGHT NUMBER(3),
       CITY  CHAR(7));

CREATE TABLE SP (
       S#   CHAR(3)    NOT NULL REFERENCES S(S#),
       P#   CHAR(3)    NOT NULL REFERENCES P(P#),
       QTY  NUMBER(5),
       PRIMARY KEY (S#,P#),
       CHECK (QTY>10));

혹은

CREATE TABLE SP (
       S#   CHAR(3),
       P#   CHAR(3),
       QTY  NUMBER(5),
       PRIMARY KEY (S#,P#),
       FOREIGN KEY(S#) REFERENCES S,
       FOREIGN KEY(P#) REFERENCES P,
       CHECK (QTY>10));

INSERT INTO S VALUES ('S1','HONG','20','SEOUL');
INSERT INTO S VALUES ('S2','SON', '10','PUSAN');
INSERT INTO S VALUES ('S3','PARK','30','PUSAN');
INSERT INTO S VALUES ('S4','SHIN','20','SEOUL');
INSERT INTO S VALUES ('S5','BAHN','30','KYUNGJU');

INSERT INTO P VALUES ('P1', 'NUT',   'RED',   12, 'SEOUL');
INSERT INTO P VALUES ('P2', 'BOLT',  'GREEN', 17, 'PUSAN');
INSERT INTO P VALUES ('P3', 'SCREW', 'BLUE',  17, 'KWANGJU');
INSERT INTO P VALUES ('P4', 'SCREW', 'RED',   14, 'SEOUL');
INSERT INTO P VALUES ('P5', 'CAM',   'BLUE',  12, 'PUSAN');
INSERT INTO P VALUES ('P6', 'COCK',  'RED',   19, 'SEOUL');

INSERT INTO SP VALUES ('S1','P1',300);
INSERT INTO SP VALUES ('S1','P2',200);
INSERT INTO SP VALUES ('S1','P3',400);
INSERT INTO SP VALUES ('S1','P4',200);
INSERT INTO SP VALUES ('S1','P5',100);
INSERT INTO SP VALUES ('S1','P6',100);
INSERT INTO SP VALUES ('S2','P1',300);
INSERT INTO SP VALUES ('S2','P2',400);
INSERT INTO SP VALUES ('S3','P2',200);
INSERT INTO SP VALUES ('S4','P2',200);
INSERT INTO SP VALUES ('S4','P4',300);
INSERT INTO SP VALUES ('S4','P5',400);

질의예제 1

너트를 공급하는 공급자 이름

select sname
	from sp
	join s on sp.s# = s.s#
	join p on sp.p# = p.p#
	where pname = 'NUT';

질의예제 2

색깔이 빨간 부품을 공급하는 공급자 이름

select sname
	from sp
	join s on sp.s# = s.s#
	join p on sp.p# = p.p#
	where color = 'RED';

질의예제 3

부품 P2를 공급하지 않는 공급자 이름

select sname
	from s
	where s# not in (select s# from sp where p# = 'P2');

질의예제 4

어떠한 부품도 공급하지 않는 공급자의 이름

select sname
	from s
	where s# not in (select distinct s# from sp);

질의예제5

같은 도시에 사는 공급자 이름

select sname, city
	from s
	where city in (select city from s);

질의예제 6

모든 부품을 공급하는 공급자의 이름

select sname
	from s
	where s# = (
		select s#
			from sp
			group by s#
			having count(p#) = (select count(p#) from p)
	);

질의 예제7

부품별 공급량

select p#, sum(qty)
	from sp
	group by p#;

질의 예제 8

총량이 500보다 큰 부품별 공급량

select p#, sum(qty)
	from sp
	group by p#
	having sum(qty) > 500;
@dev-redo
Copy link

dev-redo commented Sep 1, 2021

good !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment