[SOLVED] SQL 1

1

CS430/630 Practice Midterm

For both questions, you are given the following relational schema.

Emp (eid:integer, ename:string, age:integer, salary:real)

Works (eid:integer, did:integer, pct_time:integer)

Dept(did:integer, dname:string, budget:real, managerid:integer)

managerid values in table Dept correspond to eid values in table Emp.

Question 1 (10 points)

Write relational algebra expressions for the following queries:

(a) Find the ages of employees who work in a department named Catering.
(b) Find the salaries of employees that work at least 30% of their time in departments that have

budget at least $50,000.
(c) Find the salaries of department managers.
(d) Find the names of employees who work in the Marketing department or who spend more than

half of their time in a single department.

(e) [630 students only] Find the eid of managers who manage exactly one department.

Note: for this question, you are NOT ALLOWED to use SQL, answers in SQL will not receive any marks.
Derive relational algebra expressions only.

Question 2 (20 points)

Write SQL queries for the following:

(a) Write a statement to create the table Works. You do NOT need to provide create table
statements for the other tables. You may choose at your discretion the details of the exact SQL
type for columns, as long as the category matches (e.g., integer, fractional or character type).

(b) Find the names of employees who work under the supervision of a manager named Steve
Smith.

(c) Find the ages of employees who do not work in any department with budget below $20,000.
(d) Find the age(s) of the employee(s) with the highest salary.

(e) Find the did and average salary over employees younger than 45 years old for each
department with at least 10 employees of any age.

(f) Find the names of employees who work in all departments.

(g)[630 students only] Find the name(s) of the department(s) with the highest average salary.