Consider the following database. Employee has a unique eid, name, age and salary. Department has a unique did, name and an employee as a manager. A department may or may not have a manager assigned. An employee can work for multiple departments. The pct_time in Works table is the percentage of time an employee works for a department. The salary of the employees is fixed and is not based on pct_time or the deparments they work in.

Emp (eid: INT(11), ename: VARCHAR(50), age: INT(11), salary: FLOAT)

Works (eid: INT(11), did: INT(11), pct_time: INT(11))

Dept (did: INT(11), dname: VARCHAR(50, managerid: INT(11))

Constraints

eid is primary key for Emp table and cannot be NULL

did is primary key for Dept table and cannot be NULL

eid and did are foreign keys referencing to Emp and Dept tables respectively in Works table. combination of eid and did is the primary key or works table.

managerid is foreign key referencing to Emp table and can have NULL values if the department doesnt have a manager assigned.

Sample values.

Emp

eid	ename	age	salary
1001	Georgi Facello	34	45000.00
1002	Parto Bamford	28	34000.00
1003	Christian Koblick	42	55000.00
1004	Saniya Peac	24	28000.00

Dept

did	dname	managerid
d001	Marketing	1003
d002	Finance	1002
d003	Information Security	NULL

Works

eid	did	pct_time
1001	d001	60
1002	d002	100
1003	d001	100
1001	d003	40
1004	d003	50
1004	d002	50

1. Write SQL queries for the following. Submit all queries. (5 points each)

A) Select the employee name and no of departments each employee works for.

B) Select the employee names with salary greater than average salary of all employees.

C) Select the department names with no manager assigned yet.

D) Select the employee names who work for more than 1 department.

E) Select the employee names and their manager names (keep in mind, one employee works for multiple departments so they will have multiple managers).

F) Select the name and salary of the employee with the second highest salary.