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 (assntNe, IName, IName, branchNo, dob, address, city, county, state, zip, phone1, phone2, email) Provide the SQL statements that perform the following tasks: Se, ec 1 Using a subquery list the agents who work in the branch located at ?8 Harrison Rd. FRom vonc he n c 2- List the agents with salaries greater than the average salary of all age nts.de??218 wwwanKÓ 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. What is the SQL query that provides the number and the sum of salaries of agents living and working in the same state? 4- 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. 5-

Explanation / Answer

If you need any help, give me comment....

CREATE TABLE Physician(
Physician_ID INTEGER NOT NULL PRIMARY KEY,
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Mobile_Number INTEGER,
Pager_Number INTEGER
);

CREATE TABLE Alert_Schedule(
Alert_ID INTEGER NOT NULL PRIMARY KEY,
Alert_Times INTEGER,
Alert_Status TINYINT,
Alter_Count INTEGER,
Response VARCHAR(50),
Physician_ID INTEGER,
FOREIGN KEY(Physician_ID) REFERENCES Physician(Physician_ID)
);

CREATE TABLE App_Audit_tables(
Table_ID INTEGER NOT NULL PRIMARY KEY,
Table_Name VARCHAR(100)
);

CREATE TABLE App_Audit_User(
User_ID INTEGER NOT NULL PRIMARY KEY,
User_name VARCHAR(50)
);

CREATE TABLE App_Audit_Action_Type(
Action_Type_ID INTEGER NOT NULL PRIMARY KEY,
Action_Type_Desc VARCHAR(255)
);

CREATE TABLE App_Audit_Action(
Audit_Action_ID INTEGER NOT NULL PRIMARY KEY,
Table_ID INTEGER,
User_ID INTEGER,
Action_Type_ID INTEGER,
Aud_Start_Date DATE,
Aud_Expiration_Date DATE,
Aud_Ins_DTTM DATETIME,
Aud_Upd_DTTM DATETIME,
Aud_Upd_User INTEGER
FOREIGN KEY(Table_ID) REFERENCES App_Audit_tables(Table_ID),
FOREIGN KEY(User_ID) REFERENCES App_Audit_User(User_ID),
FOREIGN KEY(Action_Type_ID) REFERENCES App_Audit_Action_Type(Action_Type_ID)
);

CREATE TABLE App_Audit_Data(
Audit_Data_ID INTEGER NOT NULL PRIMARY KEY,
Aud_Ins_DTTM DATETIME,
Aud_Upd_DTTM DATETIME,
App_Upd_User INTEGER,
Audit_Action_ID INTEGER,
FOREIGN KEY(Action_Type_ID) REFERENCES App_Audit_Action_Type(Action_Type_ID)  
);

1)
SELECT agentNo, fName, lName
FROM Agents
WHERE branchNo = (
SELECT branchNo
FROM Branches
WHERE address = '18 Harrison Rd'
);

2)
SELECT agentNo, fName, lName
FROM Agents
WHERE salary > (
SELECT AVG(salary)
FROM Agents
);

3)
SELECT agentNo, fName, lName, salary, A.branchNo, B.address
FROM Agents A, Branches B
WHERE A.branchNo = B.branchNo
ORDER BY salary DESC;

4)
SELECT A.state, SUM(salary)
FROM Agents A, Branches B
WHERE A.branchNo = b.branchNo AND A.state = B.state
GROUP BY A.state;

5)
SELECT A.agentNo, A.fName, A.lName, A.city, A.state
FROM Agents A JOIN Branches B ON A.branchNo = B.branchNo AND A.state = B.state;

6)
DELETE FROM Agents WHERE phone1 IS NULL;

7)
SELECT B.branchNo, B.address, B.city, B.state, A.agentNo, A.fName, B.lName
FROM Branches B FULL OUTER JOIN Agents A ON B.branchNo = A.branchNo AND B.state = A.state;

8)
SELECT salary*110 AS incr_salary
FROM Agents
WHERE state = 'New Jersey';

9)
INSERT INTO Branches VALUES('B012', '2854 Steinway St.', 'Astoria', 'Queens', 'NY');

10)
SELECT B.country
FROM Branches B, Agents A
WHERE A.branchNo = B.branchNo AND A.state = B.state;

11)
SELECT B.country
FROM Branches B
MINUS
SELECT B1.country
FROM Branches B1, Agents A
WHERE A.branchNo = B1.branchNo;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

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