fsmo Posted October 13, 2015 Share Posted October 13, 2015 (edited) 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); Edited October 13, 2015 by Zane Quote Link to comment Share on other sites More sharing options...
Barand Posted October 13, 2015 Share Posted October 13, 2015 Please use tags in future posts. I added them this time. (Or you can use the <> button in the toolbar) Quote Link to comment Share on other sites More sharing options...
Zane Posted October 14, 2015 Share Posted October 14, 2015 SELECT Book.Title, Publisher.PublisherCode, Book.Type, t3.AuthorLast, t3.AuthorFirst, t4.AuthorLast, t4.AuthorFirst The t1,t2,etc.. are variables (so to speak) referring to a table. You can see from the pattern above that the notation for a field name is[tableName].[fieldName] The database name can also be prepended, but it's not necessary unless you're doing queries with several databases. [databaseName].[tableName].[fieldName] FROM Book, Publisher, Wrote t1, Wrote t2, Author t3, Author t4 This is where those variables or aliases have been declared. t1 is an alias for the Wrote table. For some reason your query queries from two different tables twice, (Wrote and Author), then Publisher, and Book Notice how there are not t's after Publisher and Book. It only means that they have not been aliased. If the t1 and t2 were not present in the above code, you would have to change your reference t1.BookCode to Wrote.BookCode 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; Quote Link to comment Share on other sites More sharing options...
benanamen Posted October 14, 2015 Share Posted October 14, 2015 (edited) The t1, t2 etc are specifically ALIASES. Say your name is mike(table_name) but your wife calls you bigdaddy(alias). It is a shortcut so you dont have repeatedly type out the whole table name. Example: SELECT shortname.id, shortname.firstname FROM mysuperlongtable AS shortname Instead of SELECT mysuperlongtable.id, mysuperlongtable.firstname FROM mysuperlongtable And in practice SELECT s.id, s.firstname FROM mysuperlongtable AS s The ALIAS can be anything you want it to be and is generally only one or two characters I have always found the t1, t2 way to generic and undescript. If the table is users, I would use the ALIAS u, if the table is books. I would use the ALIAS b Alias used to confuse me too, especially because of t1, t2 Edited October 14, 2015 by benanamen Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted October 14, 2015 Share Posted October 14, 2015 The query is nonsensical and very poorly written, so I'd consider just ignoring it and moving to a better learning resource. In any case, don't adopt this style. The result set isn't even deterministic, because the query groups by the book title and then selects columns which aren't functionally dependend on the title (like the publisher). For example: If two books have the same title but different publishers, who is “the” publisher of the title? This obviously makes no sense, and a properly configured MySQL server will actually throw an error. If you do not get an error, that's because MySQL ignores your mistake and selects any publisher. Nondeterministic results are generally a bad thing when dealing with data. The GROUP BY clause is meant for aggregate functions (like SUM or AVG). Since there are no aggregate functions anywhere in the query, the clause simply doesn't belong there. It's appearently being abused to hide the problem just mentioned. Do not use FaNcY identifiers with both lowercase and uppercase letters. They may be pretty, but they're extremely confusing and error-prone, because different operating systems handle them differently. While I tested the query, I got this wrong all the time. Make your identifiers all-lowercase and separate them with underscores. The database name should be all-lowercase as well. Never, ever use cryptic aliases like “t1” and “t2” (like benanamen already said). Once the query becomes more complex, you'll hate yourself for it. And should you ever work in a team, everybody else will hate you as well. Using comma-separated tables in the FROM clause is obsolete and comes with a lot of limitations. Always use explicit joins like “book JOIN publisher ON book.publisher_code = publisher.publisher_code”. A sanitized version of the query might look like this: SELECT book.code, book.title, book.type, publisher.code, author.last_name, author.first_name FROM book JOIN publisher ON book.publisher_code = publisher.code JOIN wrote ON book.code = wrote.book_code JOIN author ON wrote.author_number = author.number ; This would select all books together with the publisher and the authors. Of course you need to fix your column names before you can actually run the query, but you get the idea. 1 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 15, 2015 Share Posted October 15, 2015 Agreed, the solution is poorly written and does not address the final part of the question (order listed on the cover) My solution would be SELECT b.Title , b.PublisherCode , b.Type , GROUP_CONCAT(a.AuthorFirst,' ',a.AuthorLast ORDER BY w.Sequence SEPARATOR ', ') as WrittenBy FROM book b INNER JOIN wrote w ON b.BookCode = w.BookCode INNER JOIN author a ON w.AuthorNum = a.AuthorNum GROUP BY b.BookCode HAVING COUNT(a.AuthorNum) > 1; +----------------------+---------------+------+---------------------------------------+ | Title | PublisherCode | Type | WrittenBy | +----------------------+---------------+------+---------------------------------------+ | Treasure Chests | TA | ART | Lon Schleining, Randy O'Rourke | | Van Gogh and Gauguin | WP | ART | Bradley Collins, Jr., Bradley Collins | | Black House | RH | HOR | Stephen King, Peter Straub | +----------------------+---------------+------+---------------------------------------+ Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.