Question

Assignment Chapter 6 Part - Assignment Chapter 6 Part 2 - Write Queries that: 1. List all Staff first and last names that have a branch number listed the Branch table. 2. Sort the query results from the query above is descending order 3. Join Branch and Staff tables where branch No are equal 4. Execute Left Outer Join for query 3 5. Execute Right Outer Join for query 3. 6. Insert record - 'CR76', 'PL94', '2018-02-28', 'To Big' into the Viewing table 7. Delete the record insert in Item 6

Answer 1

1. List all Staff first and last names that have a branch number listed the Branch Table:

$\rightarrow$ SELECT s.fName, s.lName FROM Staff s INNER JOIN Branch b ON s.branchNo = b.branchNo;

$\rightarrow$ (Alternative Query) SELECT fName, lName FROM Staff WHERE branchNo IN (SELECT branchNo FROM Branch);

2. Sort the query results from the query above in descending order:

$\rightarrow$SELECT s.fName, s.lName FROM Staff s INNER JOIN Branch b ON s.branchNo = b.branchNo ORDER BY fName DESC;

3. Join Branch and Staff tables where branchNo are equal:

$\rightarrow$SELECT * FROM Branch b INNER JOIN Staff s ON b.branchNo = s.branchNo;

4. Execute Left Outer Join for query 3:

$\rightarrow$SELECT * FROM Branch b LEFT JOIN Staff s ON b.branchNo = s.branchNo;

5. Execute Right Outer Join for query 3:

$\rightarrow$SELECT * FROM Branch b RIGHT JOIN Staff s ON b.branchNo = s.branchNo;

6. Insert record - 'CR76', 'PL94', '2018-02-28', 'To Big' into the viewing table:

$\rightarrow$ INSERT INTO Viewing (clientNo, propertyNo, ViewDate, comment) VALUES ('CR76', 'PL94', '2018-02-28', 'To Big');

7. Delete the record insert in Item 6:

$\rightarrow$DELETE FROM Viewing WHERE clientNo = 'CR76' AND propertyNo = 'PL94' AND viewDate = '2018-02-28' AND comment = 'To Big';

*** I hope the answer is sufficient for the given question. Thank you !