create table books

(book_id int primary key,

title varchar(20) NOT NULL,

author_last_name varchar(20) NOT NULL,

author_first_name varchar(20) NOT NULL,

rating char(1) check(rating in (1,2,3)));

create table patrons

(patron_id int primary key,

last_name varchar(20),

first_name varchar(20) NOT NULL,

street_address varchar(20),

city varchar(10),

zip char(7)

DOB date,

LAST_MODIFIED SYSDATE

MODIFIED_BY archer(20));

create table transactions

(transaction_id int primary key,

patron_id int,

book_id int,

transaction_date DATE,

transaction_type varchar(1),

CONSTRAINT FK_patrons FOREIGN KEY (patron_id)

REFERENCES patrons(patron_id),

CONSTRAINT FK_books FOREIGN KEY (book_id)

REFERENCES books(book_id));

CREATE SEQUENCE patron_customers_seq

START WITH 1000

INCREMENT BY 1;

CREATE SEQUENCE book_customers_seq

START WITH 1000

INCREMENT BY 1;

CREATE SEQUENCE transaction_customers_seq

START WITH 1000

INCREMENT BY 1;

Solve these queries from above data

1. 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.
2. 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.
3. Write a join command to display all the patrons with at least one book issued to them.
4. Write SQL to list all the books (book ids and first 10 characters of the title) and their total number of transactions in 2020. Include all books even if they have no transactions (count should be 0). Sort the results by the most popular book (the book with the most transactions).