Programming Help

Web Development



What's Here?

  • Members: 131,704
  • Replies: 469,518
  • Topics: 72,771
  • Snippets: 2,534
  • Tutorials: 664
  • Total Online: 2,497
  • Members: 88
  • Guests: 2,409

Who's Online?

Welcome to Dream.In.Code
Getting Help is Easy!

Join 131,704 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 2,497 people online right now. Registration is fast and FREE... Join Now!




Stumped on a question

 
 Reply to this topicStart new topic

Stumped on a question, Oracle SQL
tet
post 4 Oct, 2008 - 03:16 PM
Post #1


New D.I.C Head

*
Joined: 3 Sep, 2007
Posts: 9


My Contributions
Hey all.

I'm wondering about this question cause it has me stumped.

The question is:
"List the ITEM_NO for products that were sold to every customer."

It uses the following tables:
IPB Image

I know the result table has to have a single row with the ITEM_NO = 1 by simply looking at the tables.

So far I've tried queries such as:

CODE
select ITEM_NO
from ITEM_SOLD
group by ITEM_NO
having COUNT(RECEIPT_NO) >= 3


But I'm thinking by doing that query I'm simply manipulating data so I get the answer I'm looking for and not exactly what the query should be at all.

Thanks.
User is offlineProfile CardPM

Go to the top of the page

William_Wilson
post 4 Oct, 2008 - 03:25 PM
Post #2


lost in compilation

Group Icon
Joined: 23 Dec, 2005
Posts: 3,951



Thanked 13 times

Dream Kudos: 3275

Expert In: Java, C, Javascript

My Contributions
sounds to me like it is simply: select DISTINCT ITEM_NO from ITEM_SOLD
This should show a list of all item numbers that were sold, and only display them once, as per distinct.
User is offlineProfile CardPM

Go to the top of the page

tet
post 4 Oct, 2008 - 03:48 PM
Post #3


New D.I.C Head

*
Joined: 3 Sep, 2007
Posts: 9


My Contributions
QUOTE(William_Wilson @ 4 Oct, 2008 - 04:25 PM) *

sounds to me like it is simply: select DISTINCT ITEM_NO from ITEM_SOLD
This should show a list of all item numbers that were sold, and only display them once, as per distinct.


I though that too initially but then I re-read the question. It says "List the ITEM_NO that were sold to every customer".

So I assumed that the result has to be the item(s) that have been sold to ALL of the customers, such as the item with ITEM_NO = 1, which has been sold to all 3 customers.

But perhaps you're right and I'm interpreting it wrong.
User is offlineProfile CardPM

Go to the top of the page

baavgai
post 4 Oct, 2008 - 07:07 PM
Post #4


Dreaming Coder

Group Icon
Joined: 16 Oct, 2007
Posts: 1,948



Thanked 94 times

Dream Kudos: 475

Expert In: C, C++, Java, C#, ASP.NET, PHP, Perl, Python, Oracle, SQL Server, MySql, HTML, JavaScript, Lua

My Contributions
QUOTE(tet @ 4 Oct, 2008 - 07:16 PM) *

List the ITEM_NO for products that were sold to every customer.


Hmm, "every customer" is simple, we can just get a count.

CODE
select count(*) from customer


Then the question becomes, how many unique customers bought each item.
CODE

select a.item_no, count(distinct b.account_no) as acct_count
    from item_sold a
        inner join sales b on a.receipt_no=b.receipt_no
    group by a.item_no


So, final answer.

CODE

select a.item_no
    from item_sold a
        inner join sales b on a.receipt_no=b.receipt_no
    group by a.item_no
    having count(distinct b.account_no) = (select count(*) from customer)


At least, if I'm reading the request right.
User is offlineProfile CardPM

Go to the top of the page

tet
post 4 Oct, 2008 - 08:12 PM
Post #5


New D.I.C Head

*
Joined: 3 Sep, 2007
Posts: 9


My Contributions
QUOTE(baavgai @ 4 Oct, 2008 - 08:07 PM) *

QUOTE(tet @ 4 Oct, 2008 - 07:16 PM) *

List the ITEM_NO for products that were sold to every customer.


Hmm, "every customer" is simple, we can just get a count.

CODE
select count(*) from customer


Then the question becomes, how many unique customers bought each item.
CODE

select a.item_no, count(distinct b.account_no) as acct_count
    from item_sold a
        inner join sales b on a.receipt_no=b.receipt_no
    group by a.item_no


So, final answer.

CODE

select a.item_no
    from item_sold a
        inner join sales b on a.receipt_no=b.receipt_no
    group by a.item_no
    having count(distinct b.account_no) = (select count(*) from customer)


At least, if I'm reading the request right.


I believe that's the answer I'm looking for, thank you! I wasn't that far from the answer, there's still some confusion for me when it comes to using "where" and "having" so I didn't know if they could be used together. Thanks a lot though.
User is offlineProfile CardPM

Go to the top of the page

Hary
post 5 Oct, 2008 - 02:06 AM
Post #6


D.I.C Head

**
Joined: 23 Sep, 2008
Posts: 176



Thanked 12 times
My Contributions
QUOTE(tet @ 4 Oct, 2008 - 09:12 PM) *

I believe that's the answer I'm looking for, thank you! I wasn't that far from the answer, there's still some confusion for me when it comes to using "where" and "having" so I didn't know if they could be used together. Thanks a lot though.


Use where for items that are in columns, and having for properties of grouped items, like averages or max's. It is certainly possible to have a criteria on a property of a single item, as on a group.
User is offlineProfile CardPM

Go to the top of the page

Fast ReplyReply to this topicStart new topic
Time is now: 11/20/08 09:07AM
Invision Power Board v2.1.7 © 2008  IPS, Inc.
Licensed to: MediaGroup1

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

Bye Bye Ads

Free DIC T-Shirt

T-Shirt Example

Related Sites

Monthly Drawing

Thumb Drive

Partners

Top Contributors

Top 10 Kudos This Month