Answer these questions using subqueries if needed. A table must prompt in my SQL

Question

Answer these questions using subqueries if needed. A table must prompt in my SQL workbench with no errors, thanks!

4. Find the department name and the number of its employees who are making higher than average salary of all employees.

Query:

DROP DATABASE IF EXISTS HW1;
CREATE DATABASE HW1;

Use HW1;

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');

Consider the following relational schema. An employee can work in more than one departmer also, the percestline field of the Works relations shows the percentage of time that a given employee works in a given department. Employees ell integer, Aaue string, Name string, age integer, salary real) Locations lD integer, address string, city string, state string. zip string) Departments(dlD integer, dNancstring, budget real, anagcrEID integer, locationlD integer) Worksin(elID integer, dIDinteger, perscntTiwe integer, startDate date, codDate date) The primary key fields are underlined, and the domain of each field is listed after the field name. Thus elR is the key for Employees, UDis the key for Locations, glD is th key for Departments, and eRand diD together form the key for WorksIn. These tables have been created and the data have been populated using MySQL server Download HW1sql to your computer and run it on MySQL. Execute your SOL statements based on these tables. Turnin of these queries to the assignment link. your SQL queries statements and the resul Some notes to keep in mind as you go: While we have tried to limit the possible number of correct solutions to e question, keep in mind that there still may be multiple correct solutions. For e of the question, there is a relatively short solution, which is preferred. Unless specified in the question, your queries should produce the correct answ for any instance of the relations. We have provided a script that will contain D for creating a sample database with some test data, but your queries should vw over any legitimate instance of the relation. Unless specified in the question, return the entire tuple. E.g. "retrieve students . Return the columns in the order we specify. E.g. question 6 would require you . Note that the dataset would mean return the entire students tuples. output the fields in name, salary order is minimal. We will be testing your queriesa much larger dataset, so be sure to add to the one provided to make sure y queries are in fact correct Write the following queries in 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 SQL statements. The q uery answers must not contain

Explanation / Answer

If you have any doubts, please give me comment..

SELECT dName, COUNT(W.eID) AS no_of_employees

FROM Employees E, WorksIn W, Departments D

WHERE W.dID = D.dID AND E.eID = W.eID AND salary > (

SELECT AVG(salary)

FROM Employees

)

GROUP BY D.dID, dName;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.