A2sol - e) (SELECT emp_id, emp_lname FROM employee,...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
University of Waterloo School of Computer Science CS338 Winter 2009 Solution of Assignment 2 Question 1 a) π emp_fname,emp_lname (Employee dept_id=dept_id ( σ dept_name=sales Department)) b) π emp_fname,emp_lname (( σ sex=M bene_day_care=Y Employee) dept_id=dept_id ( σ dept_name=sales Department)) c) dept_head_id AS emp_id dept_head_id (Department))) ∪ (ρ sales_rep AS emp_id sales_rep (Sales_order))) d) emp_id (Employee dept_id=dept_id ( σ dept_name=sales Department))) - sales_rep AS emp_id sales_rep (Sales_order))) Question 2 a) SELECT emp_fname, emp_lname FROM employee WHERE start_date > ‘01/01/1993’ b) SELECT emp_fname, emp_lname FROM employee E, department D WHERE E.dept_id=D.dept_id AND dept_name=’sales’ c) SELECT emp_lname, dept_name FROM employee, department WHERE dept_head_id = emp_id d) SELECT emp_id, emp_lname FROM employee E, department D WHERE E.dept_id=D.dept_id AND dept_name=’sales’ AND bene_day_care=’Y’ AND sex=’M’ and emp_lname like ‘S%’
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: e) (SELECT emp_id, emp_lname FROM employee, department WHERE dept_head_id = emp_id ) UNION (SELECT emp_id, emp_lname FROM employee, sales_order WHERE sales_rep= emp_id) f) SELECT DISTINCT emp_id, emp_lname FROM employee, department, sales_order WHERE dept_head_id = emp_id AND emp_id = sales_rep g) SELECT emp_id, emp_lname FROM employee E, department D WHERE E.dept_id=D.dept_id AND dept_name=sales AND emp_id NOT IN (SELECT sales_rep FROM sales_order) h) SELECT dept_name, count(*) FROM employee E, department D WHERE E.dept_id=D.dept_id GROUP BY dept_name ORDER BY dept_name i) SELECT dept_name, H.emp_lname, count(*) as emp_count FROM employee E, department D, employee H WHERE E.dept_id=D.dept_id AND dept_head_id=H.emp_id GROUP BY dept_name,H.emp_lname ORDER BY emp_count DESC j) SELECT dept_name, count(*) FROM employee E, department D WHERE E.dept_id=D.dept_id GROUP BY dept_name HAVING count(*) > 10...
View Full Document

Page1 / 2

A2sol - e) (SELECT emp_id, emp_lname FROM employee,...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online