Homework 07: SQL Data Manipulation Submission Deadline: Wednesday 03/28/2018 1:4

Question

Homework 07: SQL Data Manipulation Submission Deadline: Wednesday 03/28/2018 1:45 PM. 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, phonel, 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? 5- Using a join query, list the agentNo, first and last names, the city and the state of agents living and working in the same city.

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 H
• Subjects

---

---

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