Consider the following relational schema. An employee can work in more than one department; also, the percentTime field of the Works relations shows the percentage of time that a given employee works in a given department.

Write the following queries in SQL statements. The query answers must not contain duplicates, but you should use the SQL keyword DISTINCT only when necessary. When your SQL query statement is not accepted by MySQL, it will display error messages. You need to read that message and fix all problems accordingly. Note that MySQL does not support INTERSECT and EXCEPT currently. When you need to find the intersection and difference of two result sets, you can use IN and NOT IN instead. In addition, for this assignment, creation of temporary tables is not allowed, i.e., for each question you must write exactly one SQL statement.

(20 Points) Find the department name and the number of its employees who are making higher than the departments average salary. Answer:

(20 Points) Retrieve the name and salary of the two highest salaried employees for the department Research. Hints: The DENSE_RANK() function may use to assign a rank for each row within a partition or result set with no gaps in ranking values. Answer:

HERE IS THE LINK FOR SQL SCRIPT FILE CODE

https://docs.google.com/document/d/1KTrujzboh3IUCHV6LrM-XavjyIat4nXSYoTvGUrx6p8/edit?usp=sharing

if link does not work see script below

DROP DATABASE IF EXISTS HW3;

CREATE DATABASE HW3;

Use HW3;

CREATE TABLE Employees(

eid int,

fName VARCHAR(30),

lName VARCHAR(30),

age SMALLINT,

salary FLOAT(10,2),

PRIMARY KEY (eid));

CREATE TABLE Locations(

lID INT,

address VARCHAR(40),

city VARCHAR(30),

state CHAR(2),

zip CHAR(5),

PRIMARY KEY (lID));

CREATE TABLE Departments(

did int,

dName VARCHAR(50),

budget int,

managerEID int,

locationID int,

PRIMARY KEY (did),

FOREIGN KEY(managerEID) REFERENCES Employees(eID),

FOREIGN KEY(locationID) REFERENCES Locations(lID));

CREATE TABLE WorksIn(

eID int,

did int,

percentTime SMALLINT,

startDate DATE,

endDate DATE,

PRIMARY KEY (eid, did),

FOREIGN KEY(eid) REFERENCES Employees(eid),

FOREIGN KEY(did) REFERENCES Departments(did));

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (1, 'John','Smith', 39, 70000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (2, 'Franklin','Wong', 20, 40000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (3, 'Alicia','Zelaya', 25, 67000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (4, 'Jennifer','Wallace', 19, 45000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (5, 'John','Smith', 30, 60000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (6, 'Joy','Reddy', 20, 65000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (7, 'ANN','Chen', 22, 50000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (8, 'Ron','Williamson', 32, 80000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (9, 'Jeffrey','Long', 24, 55800);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (10, 'Kate','Greens', 32, 98000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (11, 'Kate','Greens', 20, 38000);

INSERT INTO Employees(eid, fName, lName, age, salary)

VALUES (12, 'Ann','Lee', 20, 38000);

INSERT INTO Locations(lid, address, city, state, zip)

VALUES (1001, '203 Main St.','Greesboro', 'NC', '27411');

INSERT INTO Locations(lid, address, city, state, zip)

VALUES (1002, '12 University Blv.','Charlotte', 'NC', '28213');

INSERT INTO Departments(did,dName, budget, managerEID,locationID)VALUES (911,'Research', 10000000, 3, 1001);

INSERT INTO Departments(did,dName, budget, managerEID)VALUES (922,'Human Resource', 10000000, 2);

INSERT INTO Departments(did,dName, budget, locationID)VALUES (933,'Information Technology', 5, 1002);

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (1,911, 60, '2012-11-16', '2013-12-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (1,933, 40, '2012-10-16','2013-12-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (2,922, 100, '2012-01-16', '2015-12-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (3,922, 100, '2014-01-16', '2014-12-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (4,911, 30, '2013-01-16', '2013-12-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (4,933, 70, '2013-01-16', '2013-12-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (5,933, 100, '2012-01-16', '2012-12-31');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (6,911, 80, '2012-11-16', '2014-12-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (6,933, 20, '2012-10-16','2013-12-31');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (7,911, 100, '2013-01-01', '2015-12-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (8,922, 100, '2013-05-16', '2014-12-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (9,911, 20, '2013-01-16', '2013-03-16');

INSERT INTO WorksIn(eid,did,percentTime, startDate, endDate)

values (10,911, 100, '2012-01-16', '2014-12-16');

/**************** Task **********************************/

# 1

# 2