question

dlmagers avatar image
dlmagers asked

Need help with a past Exam question

Looking over an old Exam on a question that I missed. Here's what I have thus far:

SELECT B.BOOK_CODE, B.PRICE
FROM BOOK B
WHERE TYPE = 'FIC'
OR TYPE = 'MYS'
OR TYPE IN
(SELECT B.BOOK_CODE
FROM BOOK B
WHERE TYPE = 'ART')
ORDER BY B.BOOK_CODE DESC, B.BOOK_CODE;

Problem being I need to find the BOOK_CODE for each pair of books that have the same price. The first book code listed should be the major sort key and the second book code should be the minor sort key. Table and sample data: DECLARE @TestTable AS TABLE (Book_Code char(4), Title varchar(255), Publisher_Code char(2), [Type] char(3), Price decimal(10, 2), Paperback bit) ; INSERT @TestTable (Book_Code, Title, Publisher_Code, [Type], Price, Paperback) VALUES ('0180' ,'A Deepness in the Sky ', 'TB' , 'SFI' ,7.19 , 1), ('0189' ,'Magic Terror ', 'FA' , 'HOR' ,7.99 , 1), ('0200' ,'The Stranger ', 'VB' , 'FIC' ,8.00 , 1), ('0378' ,'Venice ', 'SS' , 'ART' ,24.50 , 0), ('079X' ,'Second Wind ', 'PU' , 'MYS' ,24.95 , 0), ('0808' ,'The Edge ', 'JP' , 'MYS' ,6.99 , 1), ('1351' ,'Dreamcatcher: A Novel ', 'SC' , 'HOR' ,19.60 , 0), ('1382' ,'Treasure Chests ', 'TA' , 'ART' ,24.46 , 0), ('138X' ,'Beloved ', 'PL' , 'FIC' ,12.95 , 1), ('2226' ,'Harry Potter and the Prisoner of Azkaban ', 'ST' , 'SFI' ,13.96 , 0), ('2281' ,'Van Gogh and Gauguin ', 'WP' , 'ART' ,21.00 , 0), ('2766' ,'Of Mice and Men ', 'PE' , 'FIC' ,6.95 , 1), ('2908' ,'Electric Light ', 'FS' , 'POE' ,14.00 , 0), ('3350' ,'Group: Six People in Search of a Life ', 'BP' , 'PSY' ,10.40 , 1), ('3743' ,'Nine Stories ', 'LB' , 'FIC' ,5.99 , 1), ('3906' ,'The Soul of a New Machine ', 'BY' , 'SCI' ,11.16 , 1), ('5163' ,'Travels with Charley ', 'PE' , 'TRA' ,7.95 , 1), ('5790' ,'Catch-22 ', 'SC' , 'FIC' ,12.00 , 1), ('6128' ,'Jazz ', 'PL' , 'FIC' ,12.95 , 1), ('6328' ,'Band of Brothers ', 'TO' , 'HIS' ,9.60 , 1), ('669X' ,'A Guide to SQL ', 'CT' , 'CMP' ,37.95 , 1), ('6908' ,'Franny and Zooey ', 'LB' , 'FIC' ,5.99 , 1), ('7405' ,'East of Eden ', 'PE' , 'FIC' ,12.95 , 1), ('7443' ,'Harry Potter and the Goblet of Fire ', 'ST' , 'SFI' ,18.16 , 0), ('7559' ,'The Fall ', 'VB' , 'FIC' ,8.00 , 1), ('8092' ,'Godel, Escher, Bach ', 'BA' , 'PHI' ,14.00 , 1), ('8720' ,'When Rabbit Howls ', 'JP' , 'PSY' ,6.29 , 1), ('9611' ,'Black House ', 'RH' , 'HOR' ,18.81 , 0), ('9627' ,'Song of Solomon ', 'PL' , 'FIC' ,14.00 , 1), ('9701' ,'The Grapes of Wrath ', 'PE' , 'FIC' ,13.00 , 1), ('9882' ,'Slay Ride ', 'JP' , 'MYS' ,6.99 , 1), ('9883' ,'The Catcher in the Rye ', 'LB' , 'FIC' ,5.99 , 1), ('9931' ,'To Kill a Mockingbird ', 'HC' , 'FIC' ,18.00 , 0)
t-sqlexam-question
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hmm we might get some use out of a [past exam] tag...
1 Like 1 ·
@Scot - I don't know - maybe [past-exam-honest]? :)
1 Like 1 ·
Can you post any sample data and your required output?
0 Likes 0 ·
You should have a look at self joins, and figure out on what column you would like to make the join on.
0 Likes 0 ·
Also, you're saying that the "first" book code is the major sort key and the "second" book code is the minor key... well, what determines which of those as first & second? Also, what if there are three of them? At this point, the question seems woefully incomplete.
0 Likes 0 ·
Show more comments

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.