A2sol

# A2sol - e(SELECT emp_id emp_lname FROM employee department...

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

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%’

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

View Full Document
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

## This note was uploaded on 05/28/2011 for the course CS 338 taught by Professor I.i during the Winter '09 term at Waterloo.

### Page1 / 2

A2sol - e(SELECT emp_id emp_lname FROM employee department...

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

View Full Document
Ask a homework question - tutors are online