Transcript EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)

SQL 연습 2
pp.194-227
Data (EMPL & DEPT) p.194
EmpNo EmpName
Title
Manager Salary
DNO
1
1003
조민희
과장
4377
3000000
2
2
1365
김상원
사원
3426
1500000
1
3
2106
김창섭
대리
1003
2500000
2
4
3011
이수민
부장
4377
4000000
3
5
3426
박영권
과장
4377
3000000
1
6
3427
최종철
사원
3011
1500000
3
7
4377
이성래
사장
NULL
5000000
2
1
2
3
4
SQL 연습
DeptNo
DeptName
FloorNo
1
2
3
4
영업
기획
개발
총무
8
10
9
7
데이터베이스시스템
2
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO)
create table DEPT
(DeptNo int,
DeptName varchar(4) not null,
Floor int,
primary key (DeptNo) );
create table EMPL
(EmpNo varchar(4),
EmpName varchar(8) not null,
Title varchar(4),
Manager varchar(4) references EMPL(EmpNo),
Salary int,
DNO int,
constraint PK_EMPL primary key (EmpNo),
constraint FK_EMP_DNO foreign key (DNO) references
DEPT(DeptNo) );
SQL 연습
데이터베이스시스템
3
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO)
Insert
insert
insert
insert
into
into
into
into
DEPT
DEPT
DEPT
DEPT
values
values
values
values
(1,
(2,
(3,
(4,
‘영업’ , 8);
'기획', 10);
'개발', 9);
'총무', 7);
insert into EMPL (EmpNo, EmpName, Title, Salary, DNO)
values ('2106', '김창섭', '대리', 2500000, 2);
insert into EMPL (EmpNo, EmpName, Title, Salary, DNO)
values ('3426', '박영권', '과장', 3000000, 1);
insert
insert
insert
insert
insert
into
into
into
into
into
SQL 연습
EMPL
EMPL
EMPL
EMPL
EMPL
values
values
values
values
values
('3011',
('1003',
('3427',
('1365',
('4377',
'이수민',
'조민희',
'최종철',
'김상원',
'이성래',
'부장',
'과장',
'사원',
'사원',
'사장',
데이터베이스시스템
null,
null,
null,
null,
null,
4000000,
3000000,
1500000,
1500000,
5000000,
3);
2);
3);
1);
2);
4
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO)
update
update
update
update
update
update
EMPL
EMPL
EMPL
EMPL
EMPL
EMPL
SQL 연습
set
set
set
set
set
set
Manager='1003'
Manager='4377'
Manager='4377'
Manager='4377'
Manager='3011'
Manager='3426'
where
where
where
where
where
where
데이터베이스시스템
EmpNo='2106';
EmpNo='3426';
EmpNo='3011';
EmpNo='1003';
EmpNo='3427';
EmpNo='1365';
5
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO)
Q1 (p.198) List full details of all departments.
select * from DEPT;
Q2 (p.199) List department_numbers and
department_names of all departments.
select DeptNo, DeptName from DEPT;
Q3 (p.200) List all the titles of employees.
select distinct Title from EMPL;
Q4 (p.201) List full details of employees who work
for department 2
select * from EMPL
where DNO=2;
SQL 연습
데이터베이스시스템
6
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO)
Q1 (p.198) List full details of all departments.
select * from DEPT;
Q2 (p.198) List department_numbers and
department_names of all departments.
select DeptNo, DeptName from DEPT;
Q3 (p.199) List all the titles of employees.
select distinct Title from EMPL;
Q4 (p.200) List full details of employees who work
for department 2
select * from EMPL
where DNO=2;
SQL 연습
데이터베이스시스템
7
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q5 (p.202) List names, titles, and department_numbers of
employees whose names start with ‘이’
select EmpName, Title, DNO from EMPL
where EmpName like ‘이%’;
Q6 (p.203) List names and salaries of employees whose
title is ‘과장’ and who work for department 1.
select EmpName, Salary from EMPL
where Title= ‘과장’ and DNO=1;
Q7 (p.204) List names and salaries of employees whose
title is ‘과장’ but who do not work for department 1.
select EmpName, Salary from EMPL
where Title= ‘과장’ and DNO <> 1;
SQL 연습
데이터베이스시스템
8
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q8 (p.204) List names, titles, and the salaries of
employees whose salaries are greater than or equal
to 3000000 and less than or equal to 4500000.
select EmpName, Title, Salary from EMPL
where Salary >= 3000000 and Salary <= 4500000;
Q9 (p.205) List full details of employees who work for
department 1 or 3.
select * from EMPL
where DNO=1 or DNO=3;
select * from EMPL where DNO In (1, 3);
Q10 (p.206) List names, salaries, and 10% above the
salaries of employees whose title is ‘과장’.
select EmpName, Salary, Salary*1.1 as NewSalary
from EMPL
where Title= ‘과장’;
SQL 연습
데이터베이스시스템
9
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q11 (p.209) List salaries, titles, and names of employees
who work for dept 2 in the increasing order of salaries.
select Salary, Title, EmpName from EMPL
where DNO=2
order by Salary ASC;
Q12 (p.210) Get the average salary and the maximum salary
of employees.
select AVG(Salary) as AvgSal, MAX(Salary) as MaxSal
from EMPL;
Q13 (p.211) For each department_number, list the
department_number, average salary, and maximum salary.
select DNO, AVG(Salary) as AvgSal, MAX(Salary) as MaxSal
from EMPL
group by DNO;
SQL 연습
데이터베이스시스템
10
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q14 (p.213) For each department_number whose average
salary is greater than or equal to 2500000, list the
department_number, average salary, and maximum salary.
select DNO, AVG(Salary) as AvgSal, MAX(Salary) as MaxSal
from EMPL
group by DNO
having AVG(Salary) >= 2500000;
Q15 (p.214) Get the department_number where 김창섭 works
or the name of which is 개발.
select DNO from EMPL
where EmpName= ‘김창섭’
UNION
select DeptNo from DEPT
where DeptName= ‘개발’;
SQL 연습
데이터베이스시스템
11
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q16 (p.216) List all employee names and the
department names the employees work for.
select EmpName, DeptName
from EMPL, DEPT
where EMPL.DNO = DEPT.DeptNo;
select EmpName, DeptName
from EMPL inner join DEPT on (EMPL.DNO = DEPT.DeptNo);
Q17 (p.217) List all emplployee names and their
manager names.
select e.EmpName, m.EmpName
from EMPL e, EMPL m
where e.Manager = m.EmpNo;
SQL 연습
데이터베이스시스템
12
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q18 (p.218) List department names, employee names,
titles, and salaries of all the employees in the
increasing order of department_number and then in
the decreasing order of salary.
select DeptName, EmpName, Title, Salary
from EMPL, DEPT
where DNO = DeptNo
order by DeptNo, Salary DESC;
SQL 연습
데이터베이스시스템
13
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q19 (p.220) List names and titles of employees with the
same title as that of 박영권.
select a.EmpName, b.Title
from EMPL a, EMPL b
where a.Title = b.Title and b.EmpName = ‘박영권’;
select EmpName from EMPL
where Title = (select Title from EMPL
where EmpName= ‘박영권’);
select EmpName from EMPL
where Title In (select Title from EMPL
where EmpName=‘박영권’)
select EmpName from EMPL a
where Exists (select * from EMPL
where Title=a.Title and EmpName=‘박영권’)
SQL 연습
데이터베이스시스템
14
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q20 (p.223) List names of employees who work for 영
업부 or 개발부.
select EmpName
from EMPL, DEPT
where DNO = DeptNo and
(DeptName= ‘영업’ or DeptName=‘개발’);
select EmpName from EMPL, DEPT
where DNO = DeptNo and DeptName In ('영업', '개발');
select EmpName from EMPL
where DNO In (select DeptNo from DEPT
where DeptName= ‘영업’ or DeptName= ‘개발’);
select EmpName from EMPL e
where Exists (select * from DEPT d
where d.DeptNo = e.DNO and
(DeptName= ‘영업’ or DeptName= ‘개발’) );
SQL 연습
데이터베이스시스템
15
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q21 (p.225) List names, department_numbers and the
salaris of employees earning more salaries than the
average salary of the department they work for.
select EmpName, DNO, Salary
from EMPL a
where Salary > (select AVG(Salary) from EMPL b
where a.DNO = b.DNO);
How to check whether the output is right?
select EmpName, a.DNO, a.Salary, b.AvgSal
from EMPL a, (select DNO, AVG(Salary) as AvgSal
from EMPL group by DNO) b
where a.DNO = b.DNO and a.Salary > b.AvgSal;
SQL 연습
데이터베이스시스템
16
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q22 (p.226) Insert (5, '연구', null) into department table.
insert into DEPT
values (5, '연구', null);
Q23 (p.227) select names, titles, and salaries of
employees whose salary is more than or equal to
3,000,000 and insert into HIGHSAL (assuming that
HIGHSAL table has been already created )
insert into HIGHSAL (EmpName, Title, Salary)
select EmpName, Title, Salary
from EMPL
where Salary >= 3000000;
SQL 연습
데이터베이스시스템
17
DEPT(DeptNo, DeptName, FloorNo)
EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO)
Q24 (p.228) Delete the department 4 from the DEPT
table.
delete from DEPT
where DeptNo= 4;
Q25 (p.229) Change the department of the employee
with employee_number 2106 to department 3 and
increase his salary by 5%.
update EMPL
set DNO=3, Salary = Salary * 1.05
where EmpNo= '2106';
SQL 연습
데이터베이스시스템
18