데베 쪽지시험 문제
create database company;
use company;
create table department(
deptno int not null primary key,
deptname varchar(20) unique,
floor int
);
create table employee(
empno int not null primary key,
empname varchar(20) not null,
title varchar(20) default '사원',
manager int,
salary int,
dno int,
foreign key(dno) references department(deptno) on update cascade on delete cascade
);
INSERT INTO DEPARTMENT VALUES(1, '영업', 8);
INSERT INTO DEPARTMENT VALUES(2, '기획', 10);
INSERT INTO DEPARTMENT VALUES(3, '개발', 9);
INSERT INTO DEPARTMENT VALUES(4, '총무', 7);
INSERT INTO EMPLOYEE VALUES(2106, '김창섭', '대리', 1003, 2500000, 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(3427, '최종철', '사원', 3011, 1500000, 3);
INSERT INTO EMPLOYEE VALUES(1365, '김상원', '사원', 3426, 1500000, 1);
INSERT INTO EMPLOYEE VALUES(4377, '이성래', '이사', NULL, 5000000, 2);
-- 1번 이씨 성 을 가진 사원들의 이름 ,직급, 소속부서명을 검색하라.
select e.empname, e.title, d.deptname from employee e, department d
where e.dno = d.deptno and substr(e.empname,1,1) = '이';
-- 2번 모든 사원에 대해 부서명 별로 급여의 평균과
-- 급여의 최대 값을 구하되 급여의 평균이 2500000 이상인 부서명, 급여의 평균, 급여의 최대값을 검색하시오.
select d.deptname, avg(e.salary), max(e.salary) from employee e, department d
where e.dno = d.deptno group by d.deptname having avg(e.salary) >= 2500000;
-- 3번 개발부서에 근무하는 사원의 이름과, 부서명, 봉급을 검색하라
select e.empname, d.deptname, e.salary from employee e, department d
where e.dno = d.deptno and d.deptname = '개발';
-- 4번 김창섭 또는 최종철이 속한 부서이고 부서명이 기획부서인 사원명, 부서명을 검색
-- 4번 정답 ..
select empname, deptname from employee e, department d
where e.dno = d.deptno and (empname = '김창섭' or empname = '최종철')
and d.deptname = '기획';
-- 5번 소속된 직원이 한명도 없는 부서의 번호를 검색하라
select d.deptno
from department d
where d.deptno not in (select e.dno from employee e);
-- 6번 최종철과 같은 직급을 가진 모든 사원의 이름과 직급을 검색하라
select e.empname, e.title from employee e
where e.title like (select emp.title from employee emp where emp.empname = '최종철');
-- 7번 모든 사원에 대해서 소속부서의 이름, 사원의 이름, 직급, 급여를 검색하라
-- 부서 이름에대해서 오름차순 부서이름이 같은경우에는 SALARY 에 대해서 내림차순으로 정렬하시오
select d.deptname, e.empname, e.title, e.salary
from employee e, department d where e.dno = d.deptno
order by d.deptname, e.salary desc;
-- 8번 자신이 속한 부서의 사원들의 평균 급여보다 많은 급여를 받는 사원들에 대해서 이름 부서번호 급여를 검색하라
-- 자체조인하는 문제.. group by로 가져오는게 아닌 자체조인을 서브쿼리 내부에서 가져온다
select e.empname, e.dno, e.salary from employee e
where salary > (select avg(salary) from employee b where e.dno = b.dno);
select * from employee;
select * from department;
-- 9번 부서명별(부성번호 아님)로 급여의 평균을 구하는 뷰 를 작성하시오
-- ( 단 부서별 급여의 평균이 3000000원 이상인 뷰를 정의 하고 필드가 부서명 급여평균으로 정의한다.)
create view avgsalary(부서명,급여평균) as
select d.deptname, avg(e.salary) from department d, employee e
where d.deptno = e.dno
group by d.deptname
having avg(salary) >= 3000000;
select * from avgsalary;
-- 10번 사원의 이름과 그 사원의 직속상사 이름을 검색하는 뷰를 작성하시오
-- 10번 정답.. 자체조인 한 값이다
create view sawon(사원이름,직속상사이름) as
select a.empname, b.empname from employee a, employee b where a.manager = b.empno;
수업 시간 연습문제
-- 부서 테이블을 기준으로 소속된 사원이 없어도 사원의 이름은 모두 나오고 소속된 사원이 없는
-- 부서의 이름은 널로 채워지도록 뷰를 작성하고 한명도 소속되지 않은 부서의 이름을 검색
-- 사원이 한명도 소속 되지 않은 부서의 이름과 사원의 이름을 검색
select * from employee;
select * from department;
alter view v3
as
select deptname,empname
from department d left outer join employee e
on d.deptno = e.dno;
select * from v3 where empname is null;
-- 사원테이블 앞에 열번호 붙여서 번호, 사원명, 부서명을 검색
set @num := 0;
select (@num := @num+1) as '번호', empname, deptname
from employee e, department d
where e.dno = d.deptno;
-- 사원의 성씨별로 몇명인지 개수를 출력
select left(empname,1), count(*) from employee group by left(empname,1);
트리거
create database trigetDB;
use trigerDB;
create table triaa(
id int primary key,
name char(15)
);
create table tribb(
id_1 int primary key,
name_1 char(15)
);
select * from triaa;
select * from tribb;
-- triaa에 삽입시 tribb에도 삽입되는 트리거 만들기, delimiter는 블록을 만드는 것
delimiter //
create trigger triin
after insert on triaa for each row
begin
insert into tribb values(new.id, new.name);
end; //
insert into triaa values (1,'홍길동');
insert into triaa values (2,'김철수');
select * from triaa;
select * from tribb;
-- triaa를 수정하면 tribb도 수정되는 트리거 정의하기
-- update는 변경된게 새롭게 들어간다, old가 아니라 new를 써야한다
delimiter //
create trigger triup
after update on triaa for each row
begin
update tribb
set name_1 = new.name
where id_1 = new.id;
end; //
update triaa set name = '홍홓홓' where id = 1;
select * from tribb;
-- 삭제 연동되는 트리거 정의하기
delimiter //
create trigger tridel
after delete on triaa for each row
begin
delete from tribb where id_1 = old.id;
end; //