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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

Scot Hauder avatar image Scot Hauder commented ·
hmm we might get some use out of a [past exam] tag...
1 Like 1 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
@Scot - I don't know - maybe [past-exam-honest]? :)
1 Like 1 ·
Cyborg avatar image Cyborg commented ·
Can you post any sample data and your required output?
0 Likes 0 ·
Daniel Ross avatar image Daniel Ross commented ·
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 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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 ·
dlmagers avatar image dlmagers commented ·
As far as is this a past exam that would be a Yes! I want to try to figure this out and I have no clue. And to Mr. Fritchey, I understand the question seems a little incomplete. Let me see if I can try to re phrase the question. First here is some output that maybe you can get a better sense. 


From Left to Right.  You have BOOK_CODE, TITLE, PUBLISHER_CODE, TYPE, PRICE, PAPERBACK.

0180	A Deepness in the Sky                   	TB 	SFI	7.19	Y
0189	Magic Terror                            	FA 	HOR	7.99	Y
0200	The Stranger                            	VB 	FIC	8.00	Y
0378	Venice                                  	SS 	ART	24.50	N
079X	Second Wind                             	PU 	MYS	24.95	N
0808	The Edge                                	JP 	MYS	6.99	Y
1351	Dreamcatcher: A Novel                   	SC 	HOR	19.60	N
1382	Treasure Chests                         	TA 	ART	24.46	N
138X	Beloved                                 	PL 	FIC	12.95	Y
2226	Harry Potter and the Prisoner of Azkaban	ST 	SFI	13.96	N
2281	Van Gogh and Gauguin                    	WP 	ART	21.00	N
2766	Of Mice and Men                         	PE 	FIC	6.95	Y
2908	Electric Light                          	FS 	POE	14.00	N
3350	Group: Six People in Search of a Life   	BP 	PSY	10.40	Y
3743	Nine Stories                            	LB 	FIC	5.99	Y
3906	The Soul of a New Machine               	BY 	SCI	11.16	Y
5163	Travels with Charley                    	PE 	TRA	7.95	Y
5790	Catch-22                                	SC 	FIC	12.00	Y
6128	Jazz                                    	PL 	FIC	12.95	Y
6328	Band of Brothers                        	TO 	HIS	9.60	Y
669X	A Guide to SQL                          	CT 	CMP	37.95	Y
6908	Franny and Zooey                        	LB 	FIC	5.99	Y
7405	East of Eden                            	PE 	FIC	12.95	Y
7443	Harry Potter and the Goblet of Fire     	ST 	SFI	18.16	N
7559	The Fall                                	VB 	FIC	8.00	Y
8092	Godel, Escher, Bach                     	BA 	PHI	14.00	Y
8720	When Rabbit Howls                       	JP 	PSY	6.29	Y
9611	Black House                             	RH 	HOR	18.81	N
9627	Song of Solomon                         	PL 	FIC	14.00	Y
9701	The Grapes of Wrath                     	PE 	FIC	13.00	Y
9882	Slay Ride                               	JP 	MYS	6.99	Y
9883	The Catcher in the Rye                  	LB 	FIC	5.99	Y
9931	To Kill a Mockingbird                   	HC 	FIC	18.00	N
Not sure if you can get any thing from that. Interested.
0 Likes 0 ·
WilliamD avatar image WilliamD commented ·
You need to divide the list of books into groups by their price. Identify the groupings and sort them into the order you descibed (book_code ascending). I suggest you look into the windowing features of SQL Server to do this, namely ROW_NUMBER().
0 Likes 0 ·

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.