1 / 18

SQL 연습 2

SQL 연습 2. pp.194-227. Data (EMPL & DEPT) p.194. DEPT( DeptNo , DeptName , FloorNo ) EMPL ( EmpNo , EmpName , Title, Manager, Salary, DNO). c reate table DEPT ( DeptNo int , DeptName varchar (4) not null, Floor int , primary key ( DeptNo ) ); c reate table EMPL ( EmpNo varchar (4),

aaron
Download Presentation

SQL 연습 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL 연습 2 pp.194-227

  2. Data (EMPL & DEPT) p.194 데이터베이스시스템

  3. DEPT(DeptNo, DeptName, FloorNo)EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO) create table DEPT (DeptNoint, DeptNamevarchar(4) not null, Floor int, primary key (DeptNo) ); create table EMPL (EmpNovarchar(4), EmpNamevarchar(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) ); 데이터베이스시스템

  4. DEPT(DeptNo, DeptName, FloorNo)EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO) Insert into DEPT values (1, ‘영업’ , 8); insert into DEPT values (2, '기획', 10); insert into DEPT values (3, '개발', 9); insert into DEPT values (4, '총무', 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 into EMPL values ('3011', '이수민', '부장', null, 4000000, 3); insert into EMPL values ('1003', '조민희', '과장', null, 3000000, 2); insert into EMPL values ('3427', '최종철', '사원', null, 1500000, 3); insert into EMPL values ('1365', '김상원', '사원', null, 1500000, 1); insert into EMPL values ('4377', '이성래', '사장', null, 5000000, 2); 데이터베이스시스템

  5. DEPT(DeptNo, DeptName, FloorNo)EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO) update EMPL set Manager='1003' where EmpNo='2106'; update EMPL set Manager='4377' where EmpNo='3426'; update EMPL set Manager='4377' where EmpNo='3011'; update EMPL set Manager='4377' where EmpNo='1003'; update EMPL set Manager='3011' where EmpNo='3427'; update EMPL set Manager='3426' where EmpNo='1365'; 데이터베이스시스템

  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.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 EMPLwhere DNO=2; 데이터베이스시스템

  7. 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 EMPLwhere DNO=2; 데이터베이스시스템

  8. 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 EMPLwhere EmpName like ‘이%’; Q6 (p.203) List names and salaries of employees whose title is‘과장’and who work for department 1. select EmpName, Salary from EMPLwhere 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 EMPLwhere Title= ‘과장’and DNO <> 1; 데이터베이스시스템

  9. 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 EMPLwhere Salary >= 3000000 and Salary <= 4500000; Q9 (p.205) List full details of employees who work for department 1 or 3. select * from EMPLwhere 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 NewSalaryfrom EMPLwhere Title= ‘과장’; 데이터베이스시스템

  10. 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 EMPLwhere DNO=2order by Salary ASC; Q12 (p.210) Get the average salary and the maximum salary of employees. select AVG(Salary) as AvgSal, MAX(Salary) as MaxSalfrom 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 MaxSalfrom EMPLgroup by DNO; 데이터베이스시스템

  11. 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 MaxSalfrom EMPLgroup by DNOhaving AVG(Salary) >= 2500000; Q15 (p.214) Get the department_number where 김창섭 works or the name of which is 개발. select DNO from EMPL where EmpName= ‘김창섭’UNIONselect DeptNo from DEPT where DeptName= ‘개발’; 데이터베이스시스템

  12. 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, DeptNamefrom EMPL, DEPTwhere EMPL.DNO = DEPT.DeptNo; select EmpName, DeptNamefrom EMPL inner join DEPT on (EMPL.DNO = DEPT.DeptNo); Q17 (p.217) List all emplployee names and their manager names. select e.EmpName, m.EmpNamefrom EMPL e, EMPL mwhere e.Manager = m.EmpNo; 데이터베이스시스템

  13. 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, Salaryfrom EMPL, DEPTwhere DNO = DeptNoorder by DeptNo, Salary DESC; 데이터베이스시스템

  14. 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.Titlefrom EMPL a, EMPL bwhere a.Title = b.Title and b.EmpName = ‘박영권’; select EmpName from EMPLwhere Title = (select Title from EMPL where EmpName= ‘박영권’); select EmpName from EMPL where Title In (select Title from EMPL where EmpName=‘박영권’) select EmpName from EMPL awhere Exists (select * from EMPL where Title=a.Title and EmpName=‘박영권’) 데이터베이스시스템

  15. DEPT(DeptNo, DeptName, FloorNo)EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO) Q20 (p.223) List names of employees who work for 영업부 or 개발부. select EmpNamefrom EMPL, DEPTwhere DNO = DeptNo and (DeptName= ‘영업’or DeptName=‘개발’); select EmpNamefrom EMPL, DEPT where DNO = DeptNo and DeptName In ('영업', '개발'); select EmpName from EMPLwhere DNO In (select DeptNo from DEPT where DeptName= ‘영업’or DeptName= ‘개발’); select EmpName from EMPL ewhere Exists (select * from DEPT d where d.DeptNo = e.DNO and (DeptName= ‘영업’or DeptName= ‘개발’) ); 데이터베이스시스템

  16. 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, Salaryfrom EMPL awhere 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.AvgSalfrom EMPL a, (select DNO, AVG(Salary) as AvgSal from EMPL group by DNO) bwhere a.DNO = b.DNO and a.Salary > b.AvgSal; 데이터베이스시스템

  17. DEPT(DeptNo, DeptName, FloorNo)EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO) Q22 (p.226) Insert (5, '연구', null) into department table. insert into DEPTvalues (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, Salaryfrom EMPLwhere Salary >= 3000000; 데이터베이스시스템

  18. DEPT(DeptNo, DeptName, FloorNo)EMPL (EmpNo , EmpName, Title, Manager, Salary, DNO) Q24 (p.228) Delete the department 4 from the DEPT table. delete from DEPTwhere 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 EMPLset DNO=3, Salary = Salary * 1.05where EmpNo= '2106'; 데이터베이스시스템

More Related