SQL Library Database

# calculates overdueCost

SELECT transactionID, bookID, memberID, loanDate, returnDate,

	IF(DATEDIFF(returnDate, loanDate) > 7, DATEDIFF(returnDate, loanDate)  * 0.25,0)  AS overdueCost

FROM BooksLoaned;

#updates overdueCost

UPDATE `library`.`booksloaned` SET `overdueCost` = '8.75' WHERE (`transactionID` = '107428');

UPDATE `library`.`booksloaned` SET `overdueCost` = '3.00' WHERE (`transactionID` = '412024');

UPDATE `library`.`membercontact` SET `overdueCost` = '8.75' WHERE (`memberID` = '6338');

UPDATE `library`.`membercontact` SET `overdueCost` = '3.00' WHERE (`memberID` = '7555');

#Simple Querys per Table

#BookStock

SELECT bookID, title, author

FROM bookstock

WHERE genre = 'Fiction';

#MemberContact

SELECT fName, lName, memberID

FROM membercontact

WHERE memberType = 'child';

#BooksLoaned

SELECT transactionID, memberID, loanDate

FROM booksloaned

WHERE status = 'Loaned';

#multi table join 1

SELECT b.title,b.author, m.fname, m.lname, l.status

FROM bookstock b

JOIN booksloaned l ON b.bookID = l.bookID

JOIN membercontact m ON m.memberID = l.memberID;

#multi table join 2

SELECT b.title, b.author, b.genre, m.fName, m.lName, l.loanDate, l.returnDate, l.status

FROM bookstock b

JOIN booksloaned l ON b.bookID = l.bookID

JOIN membercontact m ON m.memberID = l.memberID

WHERE b.genre = 'History';

#multi table join 3

SELECT b.author, b.title, m.fName, m.lName, m.telNumber, l.loanDate, l.returnDate

FROM booksloaned l

JOIN bookstock b ON l.bookID = b.bookID

JOIN membercontact m ON l.memberID = m.memberID

WHERE l.status = 'Loaned';

#multi table join 4

SELECT m.fName, m.lName, m.memberType, b.title, b.author, l.loanDate, l.returnDate

FROM booksloaned l

JOIN membercontact m ON l.memberID = m.memberID

JOIN bookstock b ON l.bookID = b.bookID

WHERE m.memberType = 'child';