Looking over an old exam and a question I missed.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Looking over an old exam and a question I missed.

Author

Topic

dlmagers10
Starting Member

48 Posts

Posted - 2010-10-24 : 21:01:06

Ok, I am stuck on a problem and I am looking for help. NOT AN ANSWER. I need to figure it out on my own.

Here is what I have:

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;

Tables included:

BOOK {BOOK_CODE, TITLE, PUBLISHER_CODE, TYPE, PRICE, PAPERBACK}

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.

Very frustrated.

Sachin.Nand

2937 Posts

Posted - 2010-10-25 : 00:34:58

Can you please post some sample data & output expected?

PBUH

dlmagers10
Starting Member

48 Posts

Posted - 2010-10-25 : 07:51:09

This is the BOOK Table:

Going across 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

Sachin.Nand

2937 Posts

Posted - 2010-10-25 : 08:51:41

quote:

Ok, I am stuck on a problem and I am looking for help. NOT AN ANSWER. I need to figure it out on my own.

You need to use cross apply selecting top 2 books having the same price ordered by the sortkey descending.

PBUH

dlmagers10
Starting Member

48 Posts

Posted - 2010-10-25 : 09:00:49

Thank you.

dlmagers10
Starting Member

48 Posts

Posted - 2010-10-25 : 14:25:48

Actually I found the answer. Thank you to everyone the considered helping me.

SELECT A.BOOK_CODE, B.BOOK_CODE, A.PRICE
FROM BOOK A, BOOK B
WHERE A.PRICE = B.PRICE AND A.BOOK_CODE < B.BOOK_CODE
ORDER BY A.BOOK_CODE, B.BOOK_CODE;

And Yes, this was from a past test a problem that I missed but I wanted to ask it because I know I will see the same content again in my final exam.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-25 : 17:30:06

I used to own bookstores, so this looks awful to me. We have an ISBN and not a book code, which includes the publisher code, that vague "type" is called a shop category and we have codes for bindery. Paperback is a value and not an attribute. I am sorry you have only one book, as shown by the singular table name. Here is what the skeleton should look like if you were in the trade:

Books {isbn, book_title, shop_category, retail_price, bindery_code}

>> 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. <<

SELECT B1.isbn AS first book, B2.isbn AS second_book, B1.retail_price
FROM Books AS B1, Books AS B2
WHERE B1.retail_price = B2.retail_price
AND B1.isbn <= B2.isbn;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-25 : 21:22:17

quote:
Originally posted by jcelko

I used to own bookstores, so this looks awful to me. We have an ISBN and not a book code, which includes the publisher code, that vague "type" is called a shop category and we have codes for bindery.

It's a freakin' test question -- an imaginary sample.

quote:
Originally posted by jcelko

I am sorry you have only one book, as shown by the singular table name.

Please get over that. It's your opinion. I'll argue with you that it is a good idea to name tables in the singular. I prefer that actually. Guess what? Neither one of us are right or wrong. There is no "standard" convention or right and wrong way in this case.

How would you name a table to store the names of folks who can't seem to step down off their high horse(s?) long enough to quit cluttering up forums with self serving rhetoric?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources