Preliminary Tasks: Task 1 In this Lab, it\'s assumed that you have already creat
ID: 3739432 • Letter: P
Question
Preliminary Tasks: Task 1 In this Lab, it's assumed that you have already created the Agents table and inserted sample data into it. If you don't have it, or if you are using Oracle Live SQL, you will need to create the table by copying and pasting the content of the file "Lab02_Data.txt" into an SQL Editor, then execute it. Task 2 Create the Branches table and insert sample data into it by executing the following code from your SQL Editor. CREATE TABLE Branches ( branchNo VARCHAR2(4), address VARCHAR2(50), city VARCHAR2(30), county VARCHAR2(30), state VARCHAR2(2), PRIMARY KEY (branchNo)) INSERT INTO Branches VALUES( BO02','366 Tiger Ln','Los Angeles','Los Angeles','CA'); INSERT INTO Branches VALUES('B005','55 Waydell St','Fairfield','Essex','NJ'); INSERT INTO Branches VALUES('B007,'1362 Alabama Rd','Riverside','Riverside','CA'); INSERT INTO Branches VALUES('B010','18 Harrison Rd','New York','New York','NY');Explanation / Answer
Answer(3):
SELECT fName, lName, salary, b.branchNo AS bBranchNo, b.address AS bAddress
FROM Agents a, Branches b
WHERE a.branchNo = b.branchNo /* Join condition is branchNo column of both table must be same*/
ORDER BY salary DESC; /* to sort in descending order use DESC, for ascending ASC, by default it sorts in ascending order*/
Answer(4):
SELECT a.state AS State, count(agentNo) AS TotalAgents, sum(salary) AS SumSalary
FROM Agents a, Branches b
WHERE a.branchNo = b.branchNo AND a.state = b.state /* join condition(branchNo to be equal) and living state & working state are required to be same */
GROUP BY a.state; /* GROUP BY clause aggregates the result based on particular column, In this case for each state, total agents and sum of salaries of all agents of each state is displayed */
Answer(5):
SELECT agentNo, fName, lName, a.city AS aCity, a.state AS aState
FROM Agents a, Branches b
WHERE a.branchNo = b.branchNo AND a.city = b.city; /* join condition(branchNo to be equal) and living city & working city are required to be same */
Answer(6):
DELETE FROM Agents
WHERE phone1 IS NULL; /* in case of NULL values don't use phone1 = NULL , use IS clause */
Answer(7):
SELECT b.branchNo AS bBranchNo, b.address As bAddress, b.city AS bCity, b.state AS bState, agentNo, fName, lName
FROM Agents a LEFT OUTER JOIN Branches b /*The LEFT JOIN keyword returns all records from the left table, and the matched records from the right table. The result is NULL from the right side, if there is no match. */
ON a.branchNo = b.branchNo /* join condition */
ORDER BY b.state, b.address; /* order first by state and then by addrres(when state is same) */
Answer(8):
UPDATE Agents
SET salary = 1.1 * salary /* salary = salary + 0.1*salary */
WHERE state = 'New Jersy' ;
Answer(9):
INSERT INTO Branches VALUES('B012', '2854 Steinway St', 'Astoria', 'Queens', 'NY');