-- CSE670 10:30am
-- Jason Kim

-- 
-- Homework #3
-- SQL File
-- 1 Declares the tables of the LIBRARY schema of Problem 6.18 in the textbook (pages 186–187).
-- 2 Populates the tables with entries which produce non-empty answers for the queries below
-- 3 Issues queries (a)–(g) of Problem 6.18
-- 

-- --------------------------------------------------------

-- 1+2 Creation of Database 'Library'

-- 
-- Table structure for table `BOOK`
-- 

CREATE TABLE `BOOK` (
  `book_id` int(10) unsigned NOT NULL auto_increment,
  `book_title` varchar(250) NOT NULL default '',
  `publisher_name` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`book_id`),
  FOREIGN KEY  (`publisher_name`) REFERENCES PUBLISHER('Name')
	ON DELETE SET DEFAULT ON UPDATE CASCADE
);

-- 
-- populate table `BOOK`
-- 

INSERT INTO `BOOK` VALUES (1, 'The Lost Tribe', 'Random House');
INSERT INTO `BOOK` VALUES (2, 'Farook', 'Random House');
INSERT INTO `BOOK` VALUES (3, 'Carrie', 'Plume');
INSERT INTO `BOOK` VALUES (4, 'The Dark Tower VII', 'Plume');
INSERT INTO `BOOK` VALUES (5, 'Red Dawn', 'Amazing Books');
INSERT INTO `BOOK` VALUES (6, 'Zepplin', 'Amazing Books');


--
-- Table structure for table 'PUBLISHER'
--
CREATE TABLE `PUBLISHER` (
  `Name` varchar(250) NOT NULL default '',
  `Address` varchar(250) NOT NULL default '',
  `Phone` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`Name`)
);

-- 
-- populate table `PUBLISHER`
-- 

INSERT INTO `PUBLISHER` VALUES ('Plume','1234 Cool Ave', '555-5555');
INSERT INTO `PUBLISHER` VALUES ('Random House', '5678 Neat St', '444-4444');
INSERT INTO `PUBLISHER` VALUES ('Amazing Books','9012 Great Rd', '333-3333');

--
--Table structure for table 'BOOK_AUTHORS'
--

CREATE TABLE `BOOK_AUTHORS` (
  `book_id` int(10) unsigned NOT NULL,
  `author_name` varchar(250) NOT NULL default '',
  FOREIGN KEY  (`book_id`) REFERENCES BOOK('book_id')
);


-- 
-- populate table `BOOK_AUTHORS`
-- 

INSERT INTO `BOOK_AUTHORS` VALUES (1, 'Master Nook');
INSERT INTO `BOOK_AUTHORS` VALUES (2, 'Jardumba Nartangula');
INSERT INTO `BOOK_AUTHORS` VALUES (3, 'Stephen King');
INSERT INTO `BOOK_AUTHORS` VALUES (4, 'Stephen King');
INSERT INTO `BOOK_AUTHORS` VALUES (5, 'John Smith');
INSERT INTO `BOOK_AUTHORS` VALUES (6, 'Yasunori Mitsugi');

--
--Table structure for table 'BOOK_COPIES'
--

CREATE TABLE `BOOK_COPIES` (
   `book_id` int(10) unsigned NOT NULL,
   `branch_id` int(10) unsigned NOT NULL,
   `no_of_copies` int(10) unsigned NOT NULL,
  FOREIGN KEY (`book_id`) REFERENCES BOOK('book_id'),
  FOREIGN KEY (`branch_id`) REFERENCES LIBRARY_BRANCH('branch_id')
);


-- 
-- populate table `BOOK_COPIES`
-- 

INSERT INTO `BOOK_COPIES` VALUES (1, 1, 10);
INSERT INTO `BOOK_COPIES` VALUES (1, 2, 10);
INSERT INTO `BOOK_COPIES` VALUES (2, 1, 10);
INSERT INTO `BOOK_COPIES` VALUES (2, 2, 10);
INSERT INTO `BOOK_COPIES` VALUES (3, 1, 10);
INSERT INTO `BOOK_COPIES` VALUES (3, 2, 10);
INSERT INTO `BOOK_COPIES` VALUES (4, 1, 10);
INSERT INTO `BOOK_COPIES` VALUES (4, 2, 10);
INSERT INTO `BOOK_COPIES` VALUES (5, 1, 10);
INSERT INTO `BOOK_COPIES` VALUES (5, 2, 10);
INSERT INTO `BOOK_COPIES` VALUES (6, 2, 10);

--
--Table structure for table 'BOOK_LOANS'
--

CREATE TABLE `BOOK_LOANS` (
   `book_id` int(10) unsigned NOT NULL,
   `branch_id` int(10) unsigned NOT NULL,
   `card_no` int(10) unsigned NOT NULL,
   `date_out` date,
   `due_date` date,
  FOREIGN KEY (`book_id`) REFERENCES BOOK('book_id'),
  FOREIGN KEY (`branch_id`) REFERENCES LIBRARY_BRANCH('branch_id'),
  FOREIGN KEY (`card_no`) REFERENCES BORROWER('card_no')
);

-- 
-- populate table `BOOK_LOANS`
-- 

INSERT INTO `BOOK_LOANS` VALUES (1,1,123,12/15/05)
INSERT INTO `BOOK_LOANS` VALUES (2,1,123,12/15/05)
INSERT INTO `BOOK_LOANS` VALUES (3,1,123,12/15/05)
INSERT INTO `BOOK_LOANS` VALUES (4,1,123,12/15/05)
INSERT INTO `BOOK_LOANS` VALUES (5,1,123,12/15/05)
INSERT INTO `BOOK_LOANS` VALUES (6,1,123,12/15/05)
INSERT INTO `BOOK_LOANS` VALUES (1,2,456,11/24/05)
INSERT INTO `BOOK_LOANS` VALUES (2,2,456,11/24/05)
INSERT INTO `BOOK_LOANS` VALUES (3,2,456,11/24/05)
INSERT INTO `BOOK_LOANS` VALUES (4,2,456,11/24/05)
INSERT INTO `BOOK_LOANS` VALUES (5,2,456,11/24/05)
INSERT INTO `BOOK_LOANS` VALUES (6,2,456,today)

--
--Table structure for table `Library_Branch`
--

CREATE TABLE `LIBRARY_BRANCH`(
	`branch_id` int(10) unsigned NOT NULL auto_increment,
	`branch_name` varchar(250) NOT NULL default '',
	`address` varchar(250) NOT NULL default '',
	PRIMARY KEY (`branch_id`)
);

--
-- populate table `Library_Branch`
--

INSERT INTO `LIBRARY_BRANCH` VALUES (1, 'Sharpstown', '4321 Wonder Road');
INSERT INTO `LIBRARY_BRANCH` VALUES (2, 'Central', '9876 Flower St');

--
--Table structure for table `BORROWER`
--

CREATE TABLE `BORROWER`(
	`card_no` int(10) unsigned NOT NULL auto_increment,
	`name` varchar(250) NOT NULL default '',
	`address` varchar(250) NOT NULL default '',
	`phone` varchar(250) NOT NULL default '',
	PRIMARY KEY (`card_no`)
);

---
--- populate table `
---

INSERT INTO `BORROWER` VALUES (1,'John Doe','1234 Plain St','532-2432');
INSERT INTO `BORROWER` VALUES (2,'Jane Doe','1234 Plain St','532-2432');
INSERT INTO `BORROWER` VALUES (3,'Mike Jackson','1111 nowhere','222-2222');



---
--- Run Queries (a)–(g) of Problem 6.18
---
--(a) how many copies of the book titled The Lost Tribe are owned by each library branch whos name is Sharpstown?
SELECT `no_of_copies` 
FROM `BOOK_COPIES`
WHERE (SELECT `book_id`
	   FROM `BOOK`
	   WHERE `book_title` = `The Lost Tribe`) = `book_id` AND (SELECT `branch_id`
															   FROM `LIBRARY_BRANCH`
															   WHERE `branch_name` = `Sharpstown`)= `branch_id`;
															   
--(b)how many copies of the book titled The Lost Tribe are owned by each library branch?
SELECT SUM(`no_of_copies`)
FROM `BOOK_COPIES`
WHERE (SELECT `book_id`
	   FROM `BOOK`
	   WHERE `book_title` = `The Lost Tribe`) = `book_id`;



--(c) names of all borrowers who do not have any books checked out
SELECT `name`
FROM `BORROWER`
WHERE (SELECT `card_no`
	   FROM `BOOK_LOANS`
	   WHERE DueDate NOT EXISTS) = `card_no`;
	   

--(d) retrieve book title, borrower name, and borrower address for duedate that is today

SELECT (`book_title`, `name`,`address`)
FROM (`BOOK`,`BORROWER`)
WHERE (SELECT(`book_id`)
	   FROM `BOOK_LOANS`) = BOOK(`book_id`) AND (SELECT `due_date`
												 FROM `BOOK_LOANS`) = today AND (SELECT `card_no`
																				 FROM `BOOK_LOANS`) = BORROWER(`card_no`);
																				
--(e) For each library branch retrieve branch name and total number of books loaned out.
SELECT(`branch_name`, `date_due` COUNT(*))
FROM(`LIBRARY_BRANCH`, `BOOK_LOANS`)
WHERE LIBRARY_BRANCH(`branch_id`) = BOOK_LOANS(`branch_id`);

--(f) retrieve names addresses and number of books checked out for all borrowers who have more than 5 books checked out

SELECT(`name`,`address`,date_due COUNT(*))
FROM `BORROWER`,`BOOK_LOAN`
WHERE (SELECT `date_due` COUNT(*)
	   FROM `BOOK_LOAN`) > 5 AND (SELECT `card_no`
								  FROM `BOOK_LOAN`) = BORROWER(`card_no`);

--(g) for each book authored by stephen king, get title and number of copies owned by library branch central.

SELECT (`book_title`, `no_of_copies`)
FROM (`BOOK`,`BOOK_COPIES`)
WHERE (SELECT `book_id`
	   FROM `BOOK_AUTHORS`
	   WHERE `author_name` = 'Stephen King') = BOOK(`book_id`) AND (SELECT `branch_id`
																    FROM `LIBRARY_BRANCH`
																	WHERE `branch_name` = 'Central') = BOOK(`book_id`);