Question: Develop a SQL query that will select all of the books whose RETURNDATE

Develop a SQL query that will select all of the books whose RETURNDATE <= '2012-08-08' and, which have a value greater than $10.00.

Your query must return the name, address, postal code, and phone number of the borrower, the name and phone number of the librarian responsible for the transaction, the date the book was due to be returned, and the name, cost, and ISBN number of the book.

You query should sort the list by the return date in descending order. You must submit both your SQL query and the output of executing your query against your database.

the assignment must include the SQL DML statements required to implement at least the following relations:

Book

Borrower

BookLended

Librarian

the assignment must make appropriate use of Null (and Not Null) parameters to ensure data validity. The minimum standard will be measured against the instructors example and will be based upon rules associated with primary and foreign keys and integrity constraint rules.

the assignment must make appropriate use of constraint clauses to ensure the referential integrity of the relations in the schema. The minimum standard will be measured against the instructors example and will be based upon rules associated with primary and foreign keys and integrity constraint rules.

the assignment must make appropriate use of keys including the automatic generation of key values where appropriate.

Below are the SQL DDL statements for the creation of the Book, Bookcopy, Borrower, BookLended and Librarian tables and SQL DMLfor the pupulating the data into the tables

Create Database Schema

create table book (

title varchar(60) not null,

author varchar(40),

cost decimal(10,2),

isbn_number integer not null);

alter table book add unique (isbn_number);

alter table book add primary key (isbn_number);

create table bookcopy (

Isbn_number integer,

publicationdate date,

sequence integer);

alter table bookcopy add primary key (isbn_number, sequence);

alter table bookcopy add CONSTRAINT bookcopy_uniq UNIQUE (ISBN_number, sequence);

alter table bookcopy add CONSTRAINT book_fkey FOREIGN KEY (isbn_number) REFERENCES book (isbn_number)

ON DELETE CASCADE;

create table borrower (

librarycard integer,

name char(40),

address char(40),

postalcode char(20),

phonenumber char(20),

membershipdate date);

alter table borrower add primary key (librarycard);

create table librarian (

librarianid integer,

name char(40),

phonenumber char(20),

supervisor integer);

alter table librarian add primary key (librarianid);

create table booklended (

librarycard integer not null,

checkoutdate date not null,

returndate date,

ISBN_number integer not null,

sequence integer not null,

librarianid integer not null);

alter table booklended add PRIMARY KEY (ISBN_number, sequence, librarycard, checkoutdate);

alter table booklended add CONSTRAINT booklended_uniq UNIQUE (ISBN_number, sequence, librarycard,

checkoutdate);

alter table booklended add CONSTRAINT borrower_fkey FOREIGN KEY (librarycard) REFERENCES borrower

(librarycard) ON DELETE CASCADE;

alter table booklended add CONSTRAINT librarian_fkey FOREIGN KEY (librarianid) REFERENCES librarian

(librarianid) ON DELETE CASCADE;

alter table booklended add CONSTRAINT bookcopy_fkey FOREIGN KEY (isbn_number, sequence) REFERENCES

bookcopy (isbn_number, sequence);

Populate Book Table

insert into book (isbn_number, title, author, cost) values (1441438, 'Alice in Wonderland','Lewis

Carroll',7.95);

insert into book (isbn_number, title, author, cost) values (6006374, 'A First Course in Database Systems

(3rd ed.) ', 'Jeffrey Ullman',99.49);

insert into book (isbn_number, title, author, cost) values (3523323, 'Database System Concepts ',

'Abraham Silberschatz',119.67);

insert into book (isbn_number, title, author, cost) values (1429477, 'Grimms Fairy Tales', 'Jacob

Grimm',26.99);

insert into book (isbn_number, title, author, cost) values (1486025, 'A Tale of Two Cities' ,'Charles

Dickens',7.95);

insert into book (isbn_number, title, author, cost) values (1853602, 'War and Peace','Leo Tolstoy',7.99);

insert into book (isbn_number, title, author, cost) values (1904129, 'The Scarlet letter','Nathaniel

Hawthorne',7.95);

insert into book (isbn_number, title, author, cost) values (1593832, 'Pride and Prejudice', 'Jane

Austen',7.95);

insert into book (isbn_number, title, author, cost) values (1538243, 'Pride and Prejudice','Jane

Austen',7.95);

Populate Bookcopy Table

insert into bookcopy (isbn_number, sequence, publicationdate) values (1441438 ,1, '1997-05-01');

insert into bookcopy (isbn_number, sequence, publicationdate) values (6006374 ,1, '2007-10-06');

insert into bookcopy (isbn_number, sequence, publicationdate) values (6006374 ,2, '2007-10-06');

insert into bookcopy (isbn_number, sequence, publicationdate) values (3523323 ,1, '2010-01-27');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1429477 ,1, '2004-02-01');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1429477 ,2, '2004-02-01');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1429477 ,3, '2004-02-01');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1429477 ,4, '2004-02-01');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1486025 ,1, '2010-12-01');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1853602 ,1, '2007-09-01');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1853602 ,2, '2010-09-01');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1904129 ,1, '2009-10-01');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1593832 ,1, '2004-09-20');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1538243 ,1, '2004-09-20');

insert into bookcopy (isbn_number, sequence, publicationdate) values (1538243 ,2, '2004-09-20');

Populate LibrarianTable

insert into librarian (librarianid, name, phonenumber, supervisor) values (1,'Gertrude Smith','555-

1212',null);

insert into librarian (librarianid, name, phonenumber, supervisor) values (2,'Mable Markham','555-

1212',1);

insert into librarian (librarianid, name, phonenumber, supervisor) values (3,'Penelope Pretty','555-

1212',1);

insert into librarian (librarianid, name, phonenumber, supervisor) values (4,'Olga Brown','555-2300',1);

Populate Borrower Table