2009 Winter Question 1
University of Waterloo School of Computer Science CS338 Solution of Assignment 2
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 S%
e) like (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