Using the NEW database provided(“Employee.accdb” à See Blackboard to download th

Question

Using the NEW database provided(“Employee.accdb” à See Blackboard to download this DB), write SELECT statements to get the following information(most answers should use Sub-Queries):

Sub Queries

1. Who(give their name) makes the most money in each building?

2. List all the employees, fname, lname and dept who make more money than the avg?

3. Give the person, lname and fname, and their account number, who has the most money in their Retirement Account.

4. Give a list of all employees(fname, lname and dept) who make the least amount of money in their building, but only if they work in building 400, 402 or 405.

5. Give a list of all the managers(fname, lname and dept). [Hint: Self Join]

6. Give a list of all employees(fname, lname and eid) who have their RetirementAccounts in Stocks.

7. Give me the person(fname and lname) that makes the most $$ and the person that makes the least. Use only one query(2 sub-queries)!

8. Give the employee(fname and lname) that make the most money in each dept.

Table Tools Employees (1): Database- CUsers A Loner DownloadsEmployees (1).accdb (Access 2007 - 2016 file forma... carlos rosas ? File Home Create External Dato Database Tools Fields abe Tell me what you want to do Employees All Tables Employees EID FNAME LNAME DEPTM ANAGE SALARY BUIL DRETIREACCTR RETIREACCTNUM RETIREAMOU Click to Add Employees Table $14,000.00 mutualfunds mutualfunds mutualfunds Davis $42,000.00 WolfGuy. (#botcom mands) topinvites Record: ? ? 7 of 16 ? ?! No Filter Search Datasheet View 10:06 AM O Type here to search 3/29/2018

Explanation / Answer

Query 1:

Subquery is used to identify the max salary

SELECT Emp.FName, Emp.LName

FROM Employees Emp

WHERE Salary = (SELECT MAX(Salary) Salary FROM Employees)

Query 2:

Subquery is used to get average salary

SELECT Emp.FName, Emp.LName, Emp.Dept

FROM Employees Emp

WHERE Salary > (SELECT AVG(Salary) Salary FROM Employees)

Query 3:

Subquery is used to get maximum Retirement amount

SELECT Emp.FName, Emp.LName, Emp.RETIREACCTNUMBER

FROM Employees Emp

WHERE RETIREAMOUNT = (SELECT MAX(RETIREAMOUNT) RETIREAMOUNT FROM Employees)

Query 4:

Subquery is used to identify the min salary

SELECT Emp.FName, Emp.LName, Emp.Dept

FROM Employees Emp

WHERE Salary = (SELECT MIN(Salary) Salary FROM Employees)

AND Emp.Build IN (400, 402 , 405)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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