Add a fine column in the transaction table and add some values in it.

1. Write the SQL queries and execute to verify the outputs. (8x2)

1. List the patron_id of all patrons who did a transaction of a specific date (you may define any date)

2. List the patron_id for all who have placed a hold today

3. List the details of the books that are issued

4. List all the books issued in the month of September 2014

5. Provide a detailed list of all the patrons with proper headings

6. You may have issued and returned many books in this month. Generate the report of total fine paid by every patron.

7. List the fine paid by the eldest patron

8. List the books details of the book having the highest rating (max number of transactions)

2. Create a view to print the number of books issued, returned or on hold in a one full year. Write a query to show results (4)

3. Write the following regular expressions[8 marks]

a. List all the employees in the department that starts with A and ends with s and anything in between

b. List all the employees having the desig that starts with M and finishes either at r or s (Manager or Managers)

c. List the names of the patrons in the state (province) BC (may use Zip) (V2)

d. List all the employees that may have any character coming twice consequently in their first names

books ( book_id int, title varchar(20), author_last_namevarchar(20), author_first_name varchar(20), ratingchar(1))

patrons (patron_id int, last_name varchar(20), first_name varchar(20), street_address varchar(30), cityvarchar(10), zip char(7), DOB date, Last_modified date, Modified_By varchar2(15))

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