Sql Projects Authors Database Need an database to store author information and t
ID: 3718004 • Letter: S
Question
Sql Projects
Authors Database
Need an database to store author information and the books that they have written.
First Solution
Your first solution should allow for any 1 author to be the author of record for many books.
Tables Needed
Authors
Books
Stored Procs Needed
The videos cover stored procedures. If you cannot build stored procedure please write out the SQL statements and save the scripts to a folder on your desktop.
To Select All Authors and return only Author information
To Select All Books and return only book information
To Select all Books written by a given Author (the proc should accept an authorId)
To Select All Authors that have written a book and return both author and book information by row.
Second Solution
Should allow for multiple authors on 1 book.
Explanation / Answer
For the given scenario 3 books are created namely Book, Author and Author_Book. The table book will keep the book information, table Author will keep Author information and table Author_book will keep the information about which book is written bye which author.
The relational schema of the scenario is given below-
Book (BookID, Title, Year, Price, Publisher)
Author (AuthorID, AuthorName)
Author_Book (BookID, AuthorID)
The SQL statement for creating all the 3 tables are given below-
CREATE TABLE Book
(
BookID INT,
Title VARCHAR(100),
Year DATE,
Price DECIMAL(4,2),
PublisherID INT,
PRIMARY KEY (BookID)
);
CREATE TABLE Author
(
AuthorID INT,
AuthorName VARCHAR(20),
PRIMARY KEY (AuthorID)
);
CREATE TABLE Author_Book
(
BookID INT,
AuthorID INT,
PRIMARY KEY (BookID, AuthorID),
FOREIGN KEY (BookID) REFERENCES Book(BookID),
FOREIGN KEY (AuthorID) REFERENCES Author (AuthorID)
);
Query to select all Authors-
SELECT * FROM Author;
Query to return all Books-
SELECT * FROM Book;
Query to return Book detail return by a specific order-
SELECT b.BookID, b.Title, b.Year, b.Publisher
FROM Book b
INNER JOIN Author_Book ab
ON ab.BookID = b.BookID
INNER JOIN Author a
ON a.AuthorID = ab.AuthorID
WHERE AuthorName = 'Glenn';
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.