Jump to content

fsmo

New Members
  • Posts

    1
  • Joined

  • Last visited

fsmo's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. 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);
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.