1 / 14

Ch. 4 Relational Algebra (2)

Ch. 4 Relational Algebra (2). Exercises. Queries in Ch.4 (p.161, p.166). Queries in Ch.4 (p.161, p.166). EMPL ( EmpNo , EmpName , Title, Manager, Salary, DNO) (p.161) DEPT ( DeptNo , DeptName , Floor) (p.166)

adila
Download Presentation

Ch. 4 Relational Algebra (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. Ch. 4Relational Algebra (2) Exercises

  2. Queries in Ch.4 (p.161, p.166) 데이터베이스시스템

  3. Queries in Ch.4 (p.161, p.166) EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO) (p.161) DEPT (DeptNo, DeptName, Floor) (p.166) Q (p.160) List all the information of employees who work for the department numbered 3 EMPL where DNO = 3 Q (p.162) List all the titles of employees. EMPL [Title] 데이터베이스시스템

  4. EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO)DEPT (DeptNo, DeptName, Floor) Q (p.164) List DeptNo of the department for which '김창섭'works or that of the department '개발' (EMPL where EmpName= '김창섭') [DNO] Union (DEPT where DeptName= '개발') [DeptNo] Q (p.165) List DeptNos of departments for which '김창섭'or '최종철'works and that of the department '기획' (EMPL where EmpName= '김창섭'or EmpName= '최종철') [DNO] Intersect (DEPT where DeptName= '기획') [DeptNo] 데이터베이스시스템

  5. EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO)DEPT (DeptNo, DeptName, Floor) Q (p.166) List DeptNos of departments without any employees in it. DEPT [DeptNo] – EMPL [DNO] Q (p.168) Get the Cartesian product of EMPL and DEPT. EMPL X DEPT (degree: 9, cardinality: 28) Q (p.169) Get the equi-Join of Employee relation and Department relation (EMPL X DEPT) where DNO = DeptNo 데이터베이스시스템

  6. EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO)DEPT (DeptNo, DeptName, Floor) Q (p.171) Get the natural join of EMPL and DEPT (EMPL rename DNO as DeptNo) join DEPT (degree: 8, cardinality: 7) Q (p.172) List the names and salaries of employees who work for departments of DeptNo 2 or 3. (EMPL where DNO=2 or DNO=3) [EmpName, Salary] Q (p.173) List the names of employees who work for the department '개발' ( (EMPL rename DNO as DeptNo) join (DEPT where DeptName= '개발') ) [EmpName] 데이터베이스시스템

  7. EMPL (EmpNo, EmpName, Title, Manager, Salary, DNO)DEPT (DeptNo, DeptName, Floor) 집단함수 : SUM, AVG, MAX, MIN, COUNT (p.174) Q (p.174) Get the average salary of all the employees summarize EMPL per{ } add Avg(Salary) (degree: 1, cardinality: 1) summarize EMPL per{ } add Avg(Salary) as Avg_Sal Q (p.175) List the average salary of the employees for each department. summarize EMPL per{DNO} add Avg(Salary) as Avg_Sal (degree: 2, cardinality: #of departments=3) 데이터베이스시스템

  8. 데이터베이스시스템

  9. Query set A (pp.526–534) EMPL (EmpNo, EmpName, Title, Manager,Salary, HireDate, DNO) DEPT (DeptNo, DeptName, Floor) Q1 : List the department names and DeptNos of all the departments. DEPT [DeptName, DeptNo] Q2 : Get all the detail of EMPL relation EMPL Q4 : List names and titles of employees who work for the department with number 3. (EMPL where DNO=3) [EmpName, Title] 데이터베이스시스템

  10. Query set A (pp.526–534)EMPL (EmpNo, EmpName, Title, Manager, Salary, HireDate, DNO)DEPT (DeptNo, DeptName, Floor) Q6 : List names and HireDates of employees who joined the company since Feb. 1, 2000. (EMPL where HireDate >= ‘2000-2-1’) [EmpName, HireDate] Q7 : Get all the names and titles of employees who are not '과장' (EMPL where Title <> '과장') [EmpName, Title] Q9 : List names, titles, and salaries of employees whose salaries are between 2,000,000 and 3,000,000 (EMPL where Salary>=2000000 and Salary <= 3000000) [EmpName, Title, Salary] 데이터베이스시스템

  11. Query set A (pp.526–534)EMPL (EmpNo, EmpName, Title, Manager, Salary, HireDate, DNO)DEPT (DeptNo, DeptName, Floor) Q12 : List names, titles, and DNO’s of employees who work for department 2 as either a '대리'or a ‘과장'. (EMPL where DNO=2 and (Title= ‘대리'or Title= '과장') ) [EmpName, Title, DNO] Q13 : Get all names and titles of employees whose name starts with '김' (EMPL where EmpName like '김%’) [EmpName, Title] Q14 : Get the name and the title of the highest manager (EMPL where Manager is NULL) [EmpName, Title] Q15, Q16, Q17: EMPL [EmpName, Title] 데이터베이스시스템

  12. Query set A (pp.526–534)EMPL (EmpNo, EmpName, Title, Manager, Salary, HireDate, DNO)DEPT (DeptNo, DeptName, Floor) Q18 : Get the number of employees who work in the department 3 and their average salary summarize (EMPL where DNO=3) per{ } add (Count(*) as Num_Emp, Avg(Salary) as Avg_Sal) Q20 : List names and Titles of employees who work in the department '개발' ( (EMPL rename DNO as DeptNo) join (DEPT where DeptName= '개발') ) [EmpName, Title] 데이터베이스시스템

  13. Query set A (pp.526–534)EMPL (EmpNo, EmpName, Title, Manager, Salary, HireDate, DNO)DEPT (DeptNo, DeptName, Floor) Q23 : Get the names of employees who earn more than the average salary of all the employees ( (EMPL X (summarize EMPL per{ } add Avg(Salary) as Avg_Sal) ) where Salary > Avg_Sal) [EmpName] Q27 : List the DeptNo, department_names, and floors of the departments in which nobody works (DEPT[DeptNo] – (EMPL rename DNO as DeptNo) ) Join DEPT DEPT – (DEPT join (EMPL rename DNO as DeptNo) ) [DeptNo, DeptName, Floor] 데이터베이스시스템

  14. 데이터베이스시스템

More Related