I am using the Henry Database schema which the code is down below. But in the query I do not understand the t values(t1,t2,t3 and t4). I have also included the question to the query. I need someone to dumb it down for me.
Question - . For each book with coauthors, list the title, publisher code, type, and author names (in the order listed on the cover).
Answer:
SELECT
Book.Title,
Publisher.PublisherCode,
Book.Type,
t3.AuthorLast,
t3.AuthorFirst,
t4.AuthorLast,
t4.AuthorFirst
FROM Book,
Publisher,
Wrote t1,
Wrote t2,
Author t3,
Author t4
WHERE
Book.BookCode=t1.BookCode AND
Book.BookCode=t2.BookCode AND
t1.AuthorNum=t3.AuthorNum AND
t2.AuthorNum=t4.AuthorNum AND
Book.PublisherCode=Publisher.PublisherCode
AND t3.AuthorNum != t4.AuthorNum
GROUP BY Book.Title;
Code to make schema:
CREATE DATABASE HENRY;
USE HENRY;
CREATE TABLE Author
(AuthorNum DECIMAL(2,0) PRIMARY KEY,
AuthorLast CHAR(12),
AuthorFirst CHAR(10) )
;
CREATE TABLE Book
(BookCode CHAR(4) PRIMARY KEY,
Title CHAR(40),
PublisherCode CHAR(3),
Type CHAR(3),
Paperback CHAR(1) )
;
CREATE TABLE Branch
(BranchNum DECIMAL(2,0) PRIMARY KEY,
BranchName CHAR(50),
BranchLocation CHAR(50) )
;
CREATE TABLE Copy
(BookCode CHAR(4),
BranchNum DECIMAL(2,0),
CopyNum DECIMAL(2,0),
Quality CHAR(20),
Price DECIMAL(8,2),
PRIMARY KEY (BookCode, BranchNum, CopyNum) )
;
CREATE TABLE Publisher
(PublisherCode CHAR(3) PRIMARY KEY,
PublisherName CHAR(25),
City CHAR(20) )
;
CREATE TABLE Wrote
(BookCode CHAR(4),
AuthorNum DECIMAL(2,0),
Sequence DECIMAL(2,0),
PRIMARY KEY (BookCode, AuthorNum) )
;
INSERT INTO Author
VALUES
(1,'Morrison','Toni');
INSERT INTO Author
VALUES
(2,'Solotaroff','Paul');
INSERT INTO Author
VALUES
(3,'Vintage','Vernor');
INSERT INTO Author
VALUES
(4,'Francis','Dick');
INSERT INTO Author
VALUES
(5,'Straub','Peter');
INSERT INTO Author
VALUES
(6,'King','Stephen');
INSERT INTO Author
VALUES
(7,'Pratt','Philip');
INSERT INTO Author
VALUES
(8,'Chase','Truddi');
INSERT INTO Author
VALUES
(9,'Collins','Bradley');
INSERT INTO Author
VALUES
(10,'Heller','Joseph');
INSERT INTO Author
VALUES
(11,'Wills','Gary');
INSERT INTO Author
VALUES
(12,'Hofstadter','Douglas R.');
INSERT INTO Author
VALUES
(13,'Lee','Harper');
INSERT INTO Author
VALUES
(14,'Ambrose','Stephen E.');
INSERT INTO Author
VALUES
(15,'Rowling','J.K.');
INSERT INTO Author
VALUES
(16,'Salinger','J.D.');
INSERT INTO Author
VALUES
(17,'Heaney','Seamus');
INSERT INTO Author
VALUES
(18,'Camus','Albert');
INSERT INTO Author
VALUES
(19,'Collins, Jr.','Bradley');
INSERT INTO Author
VALUES
(20,'Steinbeck','John');
INSERT INTO Author
VALUES
(21,'Castelman','Riva');
INSERT INTO Author
VALUES
(22,'Owen','Barbara');
INSERT INTO Author
VALUES
(23,'O''Rourke','Randy');
INSERT INTO Author
VALUES
(24,'Kidder','Tracy');
INSERT INTO Author
VALUES
(25,'Schleining','Lon');
INSERT INTO Book
VALUES
('0180','A Deepness in the Sky','TB','SFI','Y');
INSERT INTO Book
VALUES
('0189','Magic Terror','FA','HOR','Y');
INSERT INTO Book
VALUES
('0200','The Stranger','VB','FIC','Y');
INSERT INTO Book
VALUES
('0378','Venice','SS','ART','N');
INSERT INTO Book
VALUES
('079X','Second Wind','PU','MYS','N');
INSERT INTO Book
VALUES
('0808','The Edge','JP','MYS','Y');
INSERT INTO Book
VALUES
('1351','Dreamcatcher: A Novel','SC','HOR','N');
INSERT INTO Book
VALUES
('1382','Treasure Chests','TA','ART','N');
INSERT INTO Book
VALUES
('138X','Beloved','PL','FIC','Y');
INSERT INTO Book
VALUES
('2226','Harry Potter and the Prisoner of Azkaban','ST','SFI','N');
INSERT INTO Book
VALUES
('2281','Van Gogh and Gauguin','WP','ART','N');
INSERT INTO Book
VALUES
('2766','Of Mice and Men','PE','FIC','Y');
INSERT INTO Book
VALUES
('2908','Electric Light','FS','POE','N');
INSERT INTO Book
VALUES
('3350','Group: Six People in Search of a Life','BP','PSY','Y');
INSERT INTO Book
VALUES
('3743','Nine Stories','LB','FIC','Y');
INSERT INTO Book
VALUES
('3906','The Soul of a New Machine','BY','SCI','Y');
INSERT INTO Book
VALUES
('5163','Travels with Charley','PE','TRA','Y');
INSERT INTO Book
VALUES
('5790','Catch-22','SC','FIC','Y');
INSERT INTO Book
VALUES
('6128','Jazz','PL','FIC','Y');
INSERT INTO Book
VALUES
('6328','Band of Brothers','TO','HIS','Y');
INSERT INTO Book
VALUES
('669X','A Guide to SQL','CT','CMP','Y');
INSERT INTO Book
VALUES
('6908','Franny and Zooey','LB','FIC','Y');
INSERT INTO Book
VALUES
('7405','East of Eden','PE','FIC','Y');
INSERT INTO Book
VALUES
('7443','Harry Potter and the Goblet of Fire','ST','SFI','N');
INSERT INTO Book
VALUES
('7559','The Fall','VB','FIC','Y');
INSERT INTO Book
VALUES
('8092','Godel, Escher, Bach','BA','PHI','Y');
INSERT INTO Book
VALUES
('8720','When Rabbit Howls','JP','PSY','Y');
INSERT INTO Book
VALUES
('9611','Black House','RH','HOR','N');
INSERT INTO Book
VALUES
('9627','Song of Solomon','PL','FIC','Y');
INSERT INTO Book
VALUES
('9701','The Grapes of Wrath','PE','FIC','Y');
INSERT INTO Book
VALUES
('9882','Slay Ride','JP','MYS','Y');
INSERT INTO Book
VALUES
('9883','The Catcher in the Rye','LB','FIC','Y');
INSERT INTO Book
VALUES
('9931','To Kill a Mockingbird','HC','FIC','N');
INSERT INTO Branch
VALUES
(1,'Henry Downtown','16 Riverview');
INSERT INTO Branch
VALUES
(2,'Henry on the Hill','1289 Bedford');
INSERT INTO Branch
VALUES
(3,'Henry Brentwood','Brentwood Mall');
INSERT INTO Branch
VALUES
(4,'Henry Eastshore','Eastshore Mall');
INSERT INTO Copy
VALUES
('0180',1,1,'Excellent',7.19);
INSERT INTO Copy
VALUES
('0180',1,2,'Excellent',7.19);
INSERT INTO Copy
VALUES
('0189',2,1,'Excellent',7.99);
INSERT INTO Copy
VALUES
('0189',2,2,'Good',5.99);
INSERT INTO Copy
VALUES
('0200',1,1,'Excellent',8.00);
INSERT INTO Copy
VALUES
('0200',2,1,'Excellent',8.00);
INSERT INTO Copy
VALUES
('0200',2,2,'Fair',3.50);
INSERT INTO Copy
VALUES
('0200',2,3,'Poor',2.25);
INSERT INTO Copy
VALUES
('0378',3,1,'Excellent',24.50);
INSERT INTO Copy
VALUES
('0378',3,2,'Excellent',24.50);
INSERT INTO Copy
VALUES
('079X',2,1,'Excellent',25.95);
INSERT INTO Copy
VALUES
('079X',3,1,'Excellent',25.95);
INSERT INTO Copy
VALUES
('079X',3,2,'Good',19.95);
INSERT INTO Copy
VALUES
('079X',4,1,'Excellent',25.95);
INSERT INTO Copy
VALUES
('079X',4,2,'Excellent',25.95);
INSERT INTO Copy
VALUES
('079X',4,3,'Good',19.95);
INSERT INTO Copy
VALUES
('0808',2,1,'Excellent',7.99);
INSERT INTO Copy
VALUES
('1351',2,1,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',2,2,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',2,3,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',2,4,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',3,1,'Excellent',21.95);
INSERT INTO Copy
VALUES
('1351',3,2,'Good',13.95);
INSERT INTO Copy
VALUES
('1382',2,1,'Good',34.50);
INSERT INTO Copy
VALUES
('138X',2,1,'Excellent',12.95);
INSERT INTO Copy
VALUES
('138X',2,2,'Excellent',12.95);
INSERT INTO Copy
VALUES
('138X',2,3,'Good',6.95);
INSERT INTO Copy
VALUES
('2226',1,1,'Excellent',14.96);
INSERT INTO Copy
VALUES
('2226',1,2,'Excellent',14.96);
INSERT INTO Copy
VALUES
('2226',1,3,'Good',8.95);
INSERT INTO Copy
VALUES
('2226',3,1,'Excellent',14.95);
INSERT INTO Copy
VALUES
('2226',3,2,'Excellent',14.95);
INSERT INTO Copy
VALUES
('2226',4,1,'Fair',3.95);
INSERT INTO Copy
VALUES
('2281',4,1,'Excellent',21.00);
INSERT INTO Copy
VALUES
('2766',3,1,'Excellent',7.95);
INSERT INTO Copy
VALUES
('2766',3,2,'Good',3.95);
INSERT INTO Copy
VALUES
('2908',1,1,'Excellent',14.95);
INSERT INTO Copy
VALUES
('2908',1,2,'Excellent',14.95);
INSERT INTO Copy
VALUES
('2908',1,3,'Good',8.50);
INSERT INTO Copy
VALUES
('2908',4,1,'Good',8.50);
INSERT INTO Copy
VALUES
('3350',1,1,'Excellent',10.40);
INSERT INTO Copy
VALUES
('3350',1,2,'Excellent',10.40);
INSERT INTO Copy
VALUES
('3743',2,1,'Excellent',5.99);
INSERT INTO Copy
VALUES
('3906',2,1,'Excellent',12.16);
INSERT INTO Copy
VALUES
('3906',3,1,'Excellent',12.16);
INSERT INTO Copy
VALUES
('3906',3,2,'Good',4.50);
INSERT INTO Copy
VALUES
('5163',1,1,'Excellent',7.95);
INSERT INTO Copy
VALUES
('5790',4,1,'Excellent',12.00);
INSERT INTO Copy
VALUES
('5790',4,2,'Good',5.95);
INSERT INTO Copy
VALUES
('6128',2,1,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',2,2,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',2,3,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',2,4,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',3,1,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',3,2,'Excellent',12.95);
INSERT INTO Copy
VALUES
('6128',3,3,'Good',4.75);
INSERT INTO Copy
VALUES
('6328',2,1,'Excellent',9.95);
INSERT INTO Copy
VALUES
('6328',2,2,'Excellent',9.95);
INSERT INTO Copy
VALUES
('669X',1,1,'Excellent',39.95);
INSERT INTO Copy
VALUES
('669X',2,1,'Excellent',39.95);
INSERT INTO Copy
VALUES
('6908',2,1,'Excellent',5.99);
INSERT INTO Copy
VALUES
('6908',2,2,'Excellent',5.99);
INSERT INTO Copy
VALUES
('7405',3,1,'Good',5.00);
INSERT INTO Copy
VALUES
('7405',3,2,'Fair',2.95);
INSERT INTO Copy
VALUES
('7443',4,1,'Good',9.25);
INSERT INTO Copy
VALUES
('7559',2,1,'Fair',3.65);
INSERT INTO Copy
VALUES
('7559',2,2,'Good',8.00);
INSERT INTO Copy
VALUES
('8092',3,1,'Good',9.50);
INSERT INTO Copy
VALUES
('8720',1,1,'Excellent',6.29);
INSERT INTO Copy
VALUES
('8720',1,2,'Excellent',6.29);
INSERT INTO Copy
VALUES
('8720',1,3,'Good',3.95);
INSERT INTO Copy
VALUES
('9611',1,1,'Excellent',18.81);
INSERT INTO Copy
VALUES
('9611',1,2,'Good',8.25);
INSERT INTO Copy
VALUES
('9627',3,1,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',3,2,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',3,3,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',3,4,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',3,5,'Good',6.50);
INSERT INTO Copy
VALUES
('9627',4,1,'Excellent',14.00);
INSERT INTO Copy
VALUES
('9627',4,2,'Good',6.50);
INSERT INTO Copy
VALUES
('9701',1,1,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9701',1,2,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9701',2,1,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9701',3,1,'Fair',4.00);
INSERT INTO Copy
VALUES
('9701',3,2,'Fair',4.00);
INSERT INTO Copy
VALUES
('9701',3,3,'Good',7.25);
INSERT INTO Copy
VALUES
('9701',4,1,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9701',4,2,'Poor',1.55);
INSERT INTO Copy
VALUES
('9882',3,1,'Excellent',6.99);
INSERT INTO Copy
VALUES
('9882',3,2,'Good',3.75);
INSERT INTO Copy
VALUES
('9882',3,3,'Excellent',6.99);
INSERT INTO Copy
VALUES
('9883',2,1,'Excellent',5.99);
INSERT INTO Copy
VALUES
('9883',2,2,'Excellent',5.99);
INSERT INTO Copy
VALUES
('9883',2,3,'Fair',1.95);
INSERT INTO Copy
VALUES
('9883',4,1,'Good',3.99);
INSERT INTO Copy
VALUES
('9883',4,2,'Excellent',5.99);
INSERT INTO Copy
VALUES
('9931',1,1,'Excellent',13.00);
INSERT INTO Copy
VALUES
('9931',1,2,'Excellent',13.00);
INSERT INTO Publisher
VALUES
('AH','Arkham House','Sauk City WI');
INSERT INTO Publisher
VALUES
('AP','Arcade Publishing','New York');
INSERT INTO Publisher
VALUES
('BA','Basic Books','Boulder CO');
INSERT INTO Publisher
VALUES
('BP','Berkley Publishing','Boston');
INSERT INTO Publisher
VALUES
('BY','Back Bay Books','New York');
INSERT INTO Publisher
VALUES
('CT','Course Technology','Boston');
INSERT INTO Publisher
VALUES
('FA','Fawcett Books','New York');
INSERT INTO Publisher
VALUES
('FS','Farrar Straus and Giroux','New York');
INSERT INTO Publisher
VALUES
('HC','HarperCollins Publishers','New York');
INSERT INTO Publisher
VALUES
('JP','Jove Publications','New York');
INSERT INTO Publisher
VALUES
('JT','Jeremy P. Tarcher','Los Angeles');
INSERT INTO Publisher
VALUES
('LB','Lb Books','New York');
INSERT INTO Publisher
VALUES
('MP','McPherson and Co.','Kingston');
INSERT INTO Publisher
VALUES
('PE','Penguin USA','New York');
INSERT INTO Publisher
VALUES
('PL','Plume','New York');
INSERT INTO Publisher
VALUES
('PU','Putnam Publishing Group','New York');
INSERT INTO Publisher
VALUES
('RH','Random House','New York');
INSERT INTO Publisher
VALUES
('SB','Schoken Books','New York');
INSERT INTO Publisher
VALUES
('SC','Scribner','New York');
INSERT INTO Publisher
VALUES
('SS','Simon and Schuster','New York');
INSERT INTO Publisher
VALUES
('ST','Scholastic Trade','New York');
INSERT INTO Publisher
VALUES
('TA','Taunton Press','Newtown CT');
INSERT INTO Publisher
VALUES
('TB','Tor Books','New York');
INSERT INTO Publisher
VALUES
('TH','Thames and Hudson','New York');
INSERT INTO Publisher
VALUES
('TO','Touchstone Books','Westport CT');
INSERT INTO Publisher
VALUES
('VB','Vintage Books','New York');
INSERT INTO Publisher
VALUES
('WN','W.W. Norton','New York');
INSERT INTO Publisher
VALUES
('WP','Westview Press','Boulder CO');
INSERT INTO Wrote
VALUES
('0180',3,1);
INSERT INTO Wrote
VALUES
('0189',5,1);
INSERT INTO Wrote
VALUES
('0200',18,1);
INSERT INTO Wrote
VALUES
('0378',11,1);
INSERT INTO Wrote
VALUES
('079X',4,1);
INSERT INTO Wrote
VALUES
('0808',4,1);
INSERT INTO Wrote
VALUES
('1351',6,1);
INSERT INTO Wrote
VALUES
('1382',23,2);
INSERT INTO Wrote
VALUES
('1382',25,1);
INSERT INTO Wrote
VALUES
('138X',1,1);
INSERT INTO Wrote
VALUES
('2226',15,1);
INSERT INTO Wrote
VALUES
('2281',9,2);
INSERT INTO Wrote
VALUES
('2281',19,1);
INSERT INTO Wrote
VALUES
('2766',20,1);
INSERT INTO Wrote
VALUES
('2908',17,1);
INSERT INTO Wrote
VALUES
('3350',2,1);
INSERT INTO Wrote
VALUES
('3743',16,1);
INSERT INTO Wrote
VALUES
('3906',24,1);
INSERT INTO Wrote
VALUES
('5163',20,1);
INSERT INTO Wrote
VALUES
('5790',10,1);
INSERT INTO Wrote
VALUES
('6128',1,1);
INSERT INTO Wrote
VALUES
('6328',14,1);
INSERT INTO Wrote
VALUES
('669X',7,1);
INSERT INTO Wrote
VALUES
('6908',16,1);
INSERT INTO Wrote
VALUES
('7405',20,1);
INSERT INTO Wrote
VALUES
('7443',15,1);
INSERT INTO Wrote
VALUES
('7559',18,1);
INSERT INTO Wrote
VALUES
('8092',12,1);
INSERT INTO Wrote
VALUES
('8720',8,1);
INSERT INTO Wrote
VALUES
('9611',5,2);
INSERT INTO Wrote
VALUES
('9611',6,1);
INSERT INTO Wrote
VALUES
('9627',1,1);
INSERT INTO Wrote
VALUES
('9701',20,1);
INSERT INTO Wrote
VALUES
('9882',4,1);
INSERT INTO Wrote
VALUES
('9883',16,1);
INSERT INTO Wrote
VALUES
('9931',13,1);