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 DRETIREACCTRETIREACCTNUM RETIREAMOUN- 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

1. select FNAME,LNAME from employees where SALARY = (select max(SALARY) from Employees) group by BUILD;

The above query will retrieve the employee information whose is making most salary in each building.

2. select FNAME,LNAME,DEPT from Employees where SALARY > (select avg(SALARY) from Employees);

The above query will retrieve the employee information whose salary is having greater than average salary.

3. select FNAME,LNAME,RETIREACCTNUM from Employees where RETIREAMOUNT = (select max(RETIREAMOUNT) from Employees);

The above query will retrieve the employee information who is having maximum amount in retirement account.

4. select FNAME,LNAME,DEPT from Employees where SALARY = (select min(SALARY) from Employees) and BUILD IN (400,402,405);

The above query will retrieve the employee information whose salary is least who are working for building specified.

As per the chegg policy I am not able to answer more than 4 parts. Please post the remaining as new post.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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