Plz solve me the queries

Question

Post reply

Plz solve me the queries

Web-382291

SSC Rookie

Points: 35
More actions
November 27, 2006 at 12:14 pm

#121675

Hi anyone pls help to solve the query
Table1: Pubs
Pubid     Pubname
1          PUB1
2          PUB2
3          PUB3
Table2: Drinks
DrinkID     DrinkName
1             Drink1
2             Drink2
3             Drink3
4             Drink4
5             Drink5
6             Drink6
7             Drink7
Table3: PubDrink
PubID  DrinkID
1         1
1         2
1         5
1         6
2         1
2         2
2         3
3         4
          7
Can anyone pls help me out to find the solution. I have three tables i)Pubs ii)Drinks iii)PubDrink . I have listed the table values on top. Now I want to find out
1) which drink is top sold
2) Which drink is not sold in any pubs
3) Which pubs is not selling any drinks
Thanks
WEB

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

1)

SELECT TOP 1 WITH TIES DrinkID FROM PubDrink GROUP BY DrinkID ORDER BY COUNT(*) DESC

2)

SELECT DrinkName FROM Drinks WHERE NOT EXISTS (SELECT * FROM PubDrink WHERE Drinks.DrinkID = PubDrink.DrinkID)

3)

SELECT PubName FROM Pub WHERE NOT EXISTS (SELECT * FROM PubDrink WHERE Pub.PubID = PubDrinks.PubID)

SQLBill SSC Guru Points: 51440 More actions · Answer 2

That's not a very hard set of queries. Why don't you open the BOL (Books OnLine) that comes with SQL Server and at least make an attempt to solve it yourself. Then if you have problems, ask for help.

This looks like a homework question and you are trying to get someone else do the work.

-SQLBill

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

That's why I stayed away from the more obvious left join for all answers .

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

This looks like a homework question and you are trying to get someone else do the work.

I'll drink to that!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Web-382291 SSC Rookie Points: 35 More actions · Answer 5

Hi Ninja's_RGR'us

Thanks a lot.

Actually I am new to sql. I will try to go with the online books. I went for an interview, they gave me this question. The rule is I should not use NOT IN or IN Statment.

Any how thanks a lot.

WEB