Write the correct SQL statement according to the below questions:

use the Patrons Schema having following tables to answer this part :

books (book_id, title, author_last_name, author_first_name, rating)

patrons (patron_id, last_name, first_name, street_address, city_state_zip, location)

transactions (transaction_id, patron_id, book_id, transaction_date, transaction_type)

questions :

q1: List the details of the latest book issued to the patron having last name as Sharma.

q2: List all the books issued in the month of October 2018 .

q3: Write a query to display the number of transaction done for each book in the transaction table.

q4: Create a sequence to add the value for the transaction_id in transactions table.

Use the following tables to complete this part.

staff(staffId, lname, fname, dept, city, salary, commission, grossSalary, DOB, DOJ, branch_ID) branch(branch_id, address, city, YearOfOpening, Manager, ManagerSince)

1. List all the branches with their Id, address, city and full name of the manager.

2. Display the branchno, number of employees as well as sum of salaries of the employees for each branch from the staff table.

3. New table creation:

Create a new table ArchiveStaffPostings as

ArchiveStaffPostings(T_id, staff_id, branch_id, Date_from, Date_to).

4. Add a column workingSince (date) in the staff table.

5. List all the staff details with the branch details they are working at. Staff not allotted a branch must show nulls. (Using JOIN)

6. Write a procedure StaffTransfer(staff_id, branchID) that will

Add a tuple to ArchiveManager table with the sequence s, staff_id, branch_id from table, workingSince from table into date_from and todays date as Date_to

Update the staff table and set branchID into branch_ID and workingSince to sysdate

Display a message on Screen stating

(The SG17 works at the branch B001 Now )

7. Now write the statement to test this procedure.

Example test: staffTransfer(a101, b101) -> this will change the branchcode to b101, workingSince to current Date in the staff table and must add a new tuple in archieveStaffPostings with sno, staffId, branchId from table, date_from from table and dateto as sysdate)