Please get the answers for the bolded question only given at the last

Maintaining and redesigning an existing database

Create the following schema for maintaining the books issued to the patrons.

books ( book_id int, title varchar(20), author_last_name varchar(20), author_first_name varchar(20), rating char(1))

patrons (patron_id int, last_name varchar(20), first_name varchar(20), street_address varchar(30), city varchar(10), zip char(7))

transactions (transaction_id int, patron_id int, book_id int, transaction_date date, transaction_type char(1))

Book_Id, Patron_id and Transaction_id are primary keys. Patron_id and book_id in transactions table are foreign keys.

Possible values for the transaction_type are 1 = checking out , 2 = returning, 3 = placing a hold.

Possible values for the rating are 1 = text book, 2 = reference book, 3 = others

Use the existing database to do the following:

1. Adding values in the primary key: (4 marks)

CREATE SEQUENCE is a SQL statement to create a sequence, which can be used to generate unique integers. The CURRVAL pseudocolumn, returns the current value of the sequence, and the NEXTVAL pseudocolumn, increments the sequence and returns the new value. You can use sequences to automatically generate primary key values.

CREATE SEQUENCE customers_seq

START WITH 1000

INCREMENT BY 1

Use customer_seq to insert data as primary key as:

Insert into books values(books_seq.nextval, , , )

2. Adding new columns:
   1. Write an SQL statement to add a new column DOB to the PATRONS table. Print the query and the results.
   2. Write an SQL statement to add LAST_MODIFIED and MODIFIED_BY columns to the PATRONS table. The LAST_MODIFIED column will have the server date and time and the MODIFIED_BY will have the USER name.
3. Add patrons (3 marks)
   1. Add 2 patrons with their DOB, LAST_MODIFIED, and MODIFIED_BY.
   2. Write an SQL statement to add two new patrons to the table. Use the TO_DATE function for their DOBs. Use the existing sequence for the patron id. Print the query and the results.

ANSWER THESE ONLY NOT ABOVE:

1. Write and SQL query to list the patrons (patron_id, last_name, first_name, DOB) whose data were added/modified today (use SYSDATE as todays date). The first name and last name should have the following format: Upper case Initial, period, comma, and Last name in a mixed case. Use the ISO standard for DOB (YYYY-MM-DD). Sort the results by last name. Print the query and the results.
2. Write SQL to list all patrons with their ids and age in years (rounded up). Age is calculated based on the server date/time. Sort the results by age in an ascending order.
3. Write SQL to list all patrons and their number of transactions for each transaction type. Include all patrons even if they do not have transactions.
4. Write SQL to list the books with the word database or data base or databases in their title. Make your search case insensitive. Print the authors last name (mixed case) and the title. Sort the results by the rating.