Programming Help

Web Development



What's Here?

  • Members: 136,576
  • Replies: 479,705
  • Topics: 74,760
  • Snippets: 2,567
  • Tutorials: 677
  • Total Online: 1,869
  • Members: 90
  • Guests: 1,779

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

Join 136,576 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,869 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
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
+Quote Post

William_Wilson
RE: Stumped On A Question
4 Oct, 2008 - 03:25 PM
Post #2

lost in compilation
Group Icon

Joined: 23 Dec, 2005
Posts: 3,995



Thanked: 16 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
+Quote Post

tet
RE: Stumped On A Question
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
+Quote Post

baavgai
RE: Stumped On A Question
4 Oct, 2008 - 07:07 PM
Post #4

Dreaming Coder
Group Icon

Joined: 16 Oct, 2007
Posts: 2,031



Thanked: 105 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
+Quote Post

tet
RE: Stumped On A Question
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
+Quote Post

Hary
RE: Stumped On A Question
5 Oct, 2008 - 02:06 AM
Post #6

D.I.C Head
**

Joined: 23 Sep, 2008
Posts: 205



Thanked: 15 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
+Quote Post

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

Live Help!

Tutorials

Programming

Web Development

Reference Sheets

Code Snippets

DIC Chatroom

Bye Bye Ads

Monthly Drawing

Thumb Drive

Top Contributors

Top 10 Kudos This Month