Consider the Branches and Agents tables from Lab 04: Branches (branchNo, address

Question

Consider the Branches and Agents tables from Lab 04: Branches (branchNo, address, city, county, state) Agents (agentNo, fName. IName, branchNo, dob, address, city, county, state, zip, phone phone2, email) Provide the SQL statements that perform the following tasks: 1- Using a subquery, list the agents who work in the branch located at 18 Harrison Rd. 2- List the agents with salaries greater than the average salary of all agents. 3- List the first and last names, the salary, the branch number, and branch address of all agents sorted in the descending order of the salary. 4- What is the SQL query that provides the number and the sum of salaries of agents living and working in the same state?

Explanation / Answer

Solution:

The first four subparts have been answered as per Chegg guidelines, please repost others.

1)

SELECT agentNo FROM Agents WHERE branchNo= (SELECT branchNo FROM Branches WHERE address= '18 Harison Rd.')

2)

SELECT agentNo FROM Agents WHERE salary> AVG(salary)

3)

SELECT fName, lName, salary, branchNo, address FROM Agents INNER JOIN Branches ON Branches.branchNo= Agents.branchNo ORDER BY salary

4)

SELECT COUNT(agentNo), SUM(salary) FROM Agents GROUP BY state.

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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