Part 1 Maintaining and redesigning an existing database (2 %) Write SQL statemen
ID: 3587152 • Letter: P
Question
Part 1 Maintaining and redesigning an existing database (2 %)
Write SQL statements for the following
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)
Use the existing database to do the following:
Adding new data (DML):
a.)Add a new book to the BOOKS table: add your favorite book (add a high rating J).
b)Borrow your favorite book. Use yourself as a patron (added in Lab1). Return your favorite book.
c)List the last transaction (all columns) for your favorite book.
Print the SQL queries for a-c and the result for c.
Adding new columns (DDL):
a)Write an SQL statement to add a new column DOB to the PATRONS table. Print the query.
b)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.
Print the DDL statements for a and b.
Changing data (DML):
a)Add DOB to an existing patron (yourself). Use the TO_DATE function for the DOB.
b)Add another patron with all data (including columns added in 2b).
c)List the patrons (Last_modified, patron_id, last_name, first_name, DOB) whose data were added/modified within last 10 days (use SYSDATE as today’s date). Use ISO standard for LAST_MODIFIED including local time. Use only the year for the DOB (YYYY). Sort the results by transaction date (newest transactions first).
Print the SQL queries for a-c and the result for c.
Explanation / Answer
Adding new data (DML):
a)INSERT INTO books(book_id, title, author_last_name, author_first_name, rating)
values('bkid0034','Programming with C','HILLS','TATA','j');
book_id don't know the datatype if book_id datatype is int then make it without quotes 0034 inserted here as book_id is varchar and auto increment so
that the last inserted data of books table can easily fetch.
b)INSERT INTO patrons(patron_id, last_name, first_name, street_address, city_state_zip, location)
values('ptid0034','william','james','Park street',112542,'California');
c)the last transaction can fetch using:
i) SELECT MAX(book_id) FROM books;
ii)SELECT TOP 1 * FROM books ORDER BY page_id DESC
iii)SELECT * FROM books WHERE book_id=(SELECT max(book_id) FROM books);
NOTE: These query will work if the books table of book_id column has auto increment.
Adding new columns (DDL):
a) ALTER TABLE patrons ADD DOB date;
b) ALTER TABLE patrons ADD LAST_MODIFIED date,MODIFIED_BY varchar(30);
Changing data (DML):
a) INSERT INTO patrons values((TO_DATE('2017/05/03', 'yyyy/mm/dd'));
b) INSERT INTO patrons(patron_id, last_name, first_name, street_address, city_state_zip, location,DOB,LAST_MODIFIED,MODIFIED_BY)
values('ptid0035','william','james','Park street',112542,'California','1995/08/09','2017/05/03','Adam');
c) b) SELECT Last_modified, patron_id, last_name, first_name, DOB
FROM patrons
WHERE Last_modified > SYSDATE-10 or DOB > SYSDATE-10
ORDER BY DOB DESC;