데이터 베이스 배움터 4장 select 예제들

아래 문장들은 SQL문이다. 부서, 직원, 프로젝트 테이블을 생성하고, 각 테이블에 투플을 삽입한다.

CREATE TABLE department(

deptno int,

    deptname varchar(32),

    floor int,

    PRIMARY KEY(deptno)

);

CREATE TABLE employee(

empno int NOT NULL,

    empname varchar(32) UNIQUE,

    title varchar(32) DEFAULT '사원',

    manager int,

    salary int CHECK(salary < 6000000),

    dno int DEFAULT 1 CHECK(dno IN (1,2,3,4,5,6)),

    PRIMARY KEY(empno),

    CONSTRAINT fk1 FOREIGN KEY(manager) REFERENCES employee(empno),

    CONSTRAINT fk2 FOREIGN KEY(dno) REFERENCES department(deptno)

);

CREATE TABLE PROJEMP(

projno int not null,

    projname varchar(30) unique,

    pmanager int,

    budget int

);

INSERT INTO DEPARTMENT VALUES (1, "영업", 8) ; 

INSERT INTO DEPARTMENT (FLOOR, DEPTNAME, DEPTNO) VALUES (10, "기획", 2) ; 

INSERT INTO DEPARTMENT VALUES (3, "개발", 9) ; 

INSERT INTO DEPARTMENT VALUES (4, "총무", 7) ;

INSERT INTO EMPLOYEE VALUES (4377, "이성래", "사장", NULL, 5000000, 2) ;

INSERT INTO EMPLOYEE VALUES (3426, "박영권", "과장", 4377, 3000000, 1) ;

INSERT INTO EMPLOYEE VALUES (3011, "이수민", "부장", 4377, 4000000, 3) ;

INSERT INTO EMPLOYEE VALUES (1003, "조민희", "과장", 4377, 3000000, 2) ;

INSERT INTO EMPLOYEE VALUES (2106, "김창섭", "대리", 1003, 2500000, 2) ;

INSERT INTO EMPLOYEE VALUES (3427, "최종철", "사원", 3011, 1500000, 3) ;

INSERT INTO EMPLOYEE VALUES (1365, "김상원", "사원", 3426, 1500000, 1) ;

INSERT INTO PROJEMP VALUES (100, "날씨예보", 4377, 80000000);

INSERT INTO PROJEMP VALUES (101, "기숙사 배달", 3427, 60000000);

INSERT INTO PROJEMP VALUES (102, "홍보 앱", 2106, 100000000);

INSERT INTO PROJEMP VALUES (100, "오목 게임", 4377, 90000000);

198p

질의: 전체 부서의 모든 애트리뷰트를 검색하라.

select *

from department;

199p

질의: 모든 부서의 부서번호와 부서이름을 검색하라.

select deptno, deptname

from department;

200p

질의: 모든 사원들의 직급을 검색하라.

(1) distinct를 사용하지 않을 때

select title

from employee;

(2) distinct를 사용할 때

select distinct title

from employee;

201p

질의: 2번 부서에 근무하는 사원들에 관한 모든 정보를 검색하라

select *

from employee

where dno = 2;

202p

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

select empname, title, dno

from employee

where empname like '이%';

202p

질의: 직급이 과장이면서 1번 부서에서 근무하는 사원들의 이름과 급여를 검색하라.

select empname, salary

from employee

where title='과장' and dno = 1;

204p

질의: 직급이 과장이면서 1번 부서에 속하지 않은 사원들의 이름과 급여를 검색하라.

select empname, salary

from employee

where title = '과장' and dno <> 1;

질의: 급여가 3000000원 이상이고, 4500000원 이하인 사원들의 이름, 직급, 급여를 검색하라.

(1)

select empname, title, salary

from employee

where salary >= 3000000 and salary <= 4500000;

(2)

select empname, title, salary

from employee

where salary between 3000000 and 4500000; 

205p

질의: 1번 부서나 3번 부서에 소속된 사원들에 관한 모든 정보를 검색하라.

select *

from employee

where dno in (1, 3);

206p

질의: 직급이 과장인 사원들에 대해 이름과, 현재의 급여, 급여가 10% 인상됐을 때의 값을 검색하라.

select empname, salary, salary * 1.1 as newsalary

from employee

where title = '과장';

209p

질의: 2번 부서에 근무하는 사원들의 급여, 직급, 이름을 검색하여 급여의 오름차순으로 정렬하라.

select salary, title, empname

from employee

where dno = 2 

order by salary asc;

210p

질의: 모든 사원들의 평균 급여와 최대 급여를 검색하라.

select avg(salary), max(salary)

from employee;

211p

질의: 모든 사원들에 대해서 사원들이 속한 부서번호별로 그룹화하고, 각 부서마다 부서번호, 평균급여, 최대 급여를 검색하라.

select dno, avg(salary), max(salary)

from employee

group by dno;

213p

질의: 모든 사원들에 대해서 사원들이 속한 부서번호별로 그룹화하고, 평균 급여가 2500000원 이상인 부서에 대해서 부서번호, 평균 급여, 최대 급여를 검색하라.

select dno, avg(salary), max(salary)

from employee

group by dno

having avg(salary) > 2500000;

#group으로 묶어줬으니 조건은 where절이 아닌 having 절에 써준다.

214p

질의: 김창섭이 속한 부서이거나 개발 부서의 부서번호를 검색하라.

(select dno

from employee

where empname = '김창섭'

)

union

(select depno

from department

where deptname = '개발'

);

216p

질의: 모든 사원의 이름과 이 사원이 속한 부서의 이름을 검색하라.

select empname, deptname

from employee as E, department as D

where E.dno = D.deptno;

217p

질의: 모든 사원에 대해서 사원의 이름과 직속 상사의 이름을 검색하라.

select E.empname as 사원 이름, M.empname as 직속 상사

from employee as E, employee as M

where E.manager = M.empno;

#사원에 대한 별칭 E, 매니저에 대한 별칭 M. 이때 사원의 매니저 번호와 매니저의 사원의 번호로 조인한다.

중첩질의 한 개의 스칼라 값이 반환되는 경우

220p

¿?질의: 박영권과 같은 직급을 갖는 모든 사원들의 이름과 직급을 검색하라.

select empname, title

from employee

where title = (

select title

from employee

where empname = '박영권'

);

# 중첩 질의이다. 괄호 안에선 박영권이라는 사원을 찾고 그의 직급을 찾는다. 괄호 밖의 where 절에서 title로 그의 직급을 받아 그의 직급과 같은 직원의 이름과 직급을 검색한다.

223p

¿?질의: 영업부나 개발부에 근무하는 사원들의 이름을 검색하라.

(1) in을 사용한 질의

select empname

from employee

where dno in (

select deptno

from department

where deptname = '개발' or deptname = '영업'

);

(2) exists를 사용한 질의

select empname

from employee as E

where exists (

select deptname

    from department as D

    where E.dno = D.deptno and deptname = '영업' or deptname = '개발'

);

225p

¿?질의: 자신이 속한 부서의 사원들의 평균 급여보다 많은 급여를 받는 사원들에 대해서 이름, 부서번호, 급여를 검색하라.

(1) 교재에 나온 방법 - 상관 중첩 질의 : 중첩 질의의 dno는 외부 질의의 별칭 E로 구분된다. 좀 헷갈린다.

select empname, dno, salary

from employee as E

where salary > (

select avg(salary)

from employee

where E.dno = dno

);

(2)다르게 검색하는 방법 - 중첩 질의 안에서 같은 부서끼리 조인하고 그 부서의 평균 급여를 검색한다.

select empname, dno, salary

from employee as E1

where salary > (

select avg(salary)

    from employee as E2

    where E1.dno = E2.dno

);