SQL basic

2018-05-11

SQL 기본 문제 & 답

table

SQL문에 대해 공부하시는 분은 질문을 보고 저와 같이 답을 생각해보시길 바랍니다!

테이블을 생성하고 제약조건 생성 및 연결 스크립트를 작성하세요.

create table product (

    product_id number(6) constraint pk_product primary key,

    description varchar2(30));



create table job (

    job_id number(3) constraint pk_job primary key,

    function varchar2(30));



create table location (

    location_id number(3) constraint pk_location primary key,

    regional_group varchar2(20));



create table department (

    department_id number(3) constraint pk_department primary key,

    name varchar2(14),

    location_id number(3) constraint department_location_id_fk references location);


create table employee (

    employee_id number(4) constraint pk_employee primary key,

    last_name varchar2(15),

    first_name varchar2(15),

    middle_initial varchar2 (1),

    job_id number(3) constraint employee_job_id_fk references job,

    manager_id number(4),

    hire_date date,

    salary number(7,2),

    commission number(7,2),

    department_id number(3) constraint employee_department_id_fk references department

    );


create table customer (

    customer_id number(6) constraint pk_customer primary key,

    name varchar2(45),

    address varchar2(40),

    city varchar2 (30),

    state varchar2 (2),

    zip_code varchar2 (9),

    area_code number (3),

    phone_number number (7),

    salesperson_id number(4) references employee(employee_id),

    credit_limit number(3,2),

    hire_date date,

    comments long

    );



create table sales_order (

    order_id number(4) constraint pk_sales_order primary key,

    order_date date,

    name varchar2(14),

    customer_id number(3) references customer(customer_id),

    ship_date date,

    total number(8,2)

    );


create table price (

    product_id number(6) constraint price_product_id_fk references product,

    list_price number(8,2),

    min_price number(8,2),

    start_date date,

    constraint pk_price primary key(start_date, product_id),

    end_date date

    );



create table item (

    order_id number(4) constraint item_order_id_fk references sales_order,

    item_id number(4),

    product_id number(6) constraint item_id_fk references product,

    actual_price number(8,2),

    quantity number(8),

    total number(8,2),

    constraint pk_item primary key(order_id, item_id)

    );

1번에서 생성한 테이블중 EMPLOYEES 테이블에 INSERT, UPDATE,DELETE, SELECT 하는 SQL문장과 실행결과를 작성하세요.

insert into

location(location_id, regional_group)

values(01, 'seoul');



insert into

location(location_id, regional_group)

values(02, 'busan');




insert into

department(department_id, name, location_id)

values(1, 'accounting', 02);



insert into

department(department_id, name, location_id)

values(2, 'management', 01);



select * from department;


insert into job(job_id, function) values(1,'계산');

insert into job(job_id, function) values(2,'연산');

select * from job;

select * from department;

select * from employee;



insert into

employee(employee_id, last_name, first_name, middle_initial, job_id, manager_id, hire_date, salary, commission, department_id)

values(0001, 'park', 'smith', 'p', 1, 0003, sysdate, 10000, 100, 1);





update employee set commission = 3000 where employee_id =1;



delete from employee where employee_id = 1;

TCL을 구별하여 설명하고 EMPLOYEES 테이블에 TCL 명령어의 SQL문장과 실행 결과를 작성하세요.
- TCL이란 ?
  TCL(transaction control language)이란 DML(INSERT,UPDATE, DELETE, MERGE)에 의해 변화되는 데이터 관리를 위한 기능
- TCL종류 ?
  COMMIT, ROLLBACK
- TCL종류 각각 사용 예제

1
2
3

INSERT INTO EMPLOYEE (	EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISION, DEPARTMENT_ID)

VALUES (1000, 'LASTNM', 'FIRSTNM', 'FL', 1, 100, TO_DATE(20180308), 5000000, 500, 10);

1번에서 생성한 제약조건을 조회하는 SQL문장과 실행결과를 작성하세요. (테이블명, 제약조건명, 제약조건 타입 조회할 것)

select table_name, constraint_name, constraint_type

from user_constraints

where table_name = 'EMPLOYEE';

DEPARTMENT 테이블에 부서번호, 부서명을 수강생이름, 주소를 추가하세요. EMPLOYEES 테이블에 위에 입력한 부서번호에 해당하는 사원정보를 INSERT, UPDATE, DELETE, SELECT 하는 SQL문장과 실행결과를 작성하세요.

ALTER TABLE department ADD (department_no VARCHAR2(14));

ALTER TABLE department ADD (address VARCHAR2(20));

INSERT INTO department (DEPARTMENT_ID, NAME, LOCATION_ID) VALUES (3,'총무부',1);



INSERT INTO EMPLOYEE (	EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID)

              VALUES (2000, 'BBBBBNM', 'AAAAANM', 'A', 1, 100, TO_DATE(20180308), 3000, 300, 1);

INSERT INTO EMPLOYEE (	EMPLOYEE_ID, LAST_NAME, FIRST_NAME, MIDDLE_INITIAL, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION, DEPARTMENT_ID)

              VALUES (2, 'BBB', 'AAA', 'A', 2, 2000, TO_DATE(20180503), 3000, 300, 1);



UPDATE EMPLOYEE SET HIRE_DATE  = TO_DATE(20180309) WHERE EMPLOYEE_ID = 2000;



DELETE FROM EMPLOYEE WHERE EMPLOYEE_ID = 2000;



SELECT * FROM job;

SELECT * FROM EMPLOYEE;

아래의 질문에 SQL문을 작성하세요.

EMPLOYEES 테이블에서 수당을 받지 않는 사원들의 부서를 중복값은 제거해서 출력하세요.

1
2
3

select distinct(department_id) from employee

where COMMISSION is null;

EMPLOYEES 테이블에서 DEPTNO 별로 최대급여를 출력하는데, 최대급여가 높은 것부터 출력하시오

select department_id, max(salary)

from employee

group by department_id

order by max(salary) desc;

EMPLOYEES 테이블에서 사원이 2명 이상인 부서에 대해 부서별 평균급여를 계산하시오.

select department_id, avg(salary)

from employee

group by department_id

having count(*) >=2;

EMPLOYEES 테이블에서 입사년도가 1981년보다 큰 사원의 수는?

1	select * from employee where hire_date > '1981/01/01';

IT blog / Thanks to everyone!

ProgrammingSQL

SQL basic

SQL 기본 문제 & 답