Solved

Find cid of customers who have ordered exactly the same set of products which are ordered by a customer with cid 'c0001'.

Posted on 2008-10-12
8
345 Views
Last Modified: 2013-12-07
I am doing an assignment for school which involves writing queries. I am done except for I stumped on one question:

Find cid of customers who have ordered exactly the same set of products which are ordered by a customer with cid 'c0001'.

I can't seem to think of anyway to determine that the sets are the same. I will post the table definitions for the assignment and what I have so far. It is due on Tuesday so a fast response would be appreciated.
SELECT c.cid

FROM customers c, orders o, orderline ol

WHERE c.cid = o.cid AND o.oid = ol.oid AND ol.pid IN(SELECT DISTINCT ol.pid FROM customers c, orders o, orderline ol WHERE c.cid = 'c0001' AND c.cid = o.cid AND o.oid = ol.oid);
 

-- We will drop the tables in case they exist from previous runs

drop table orderline;

drop table orders;

drop table products;

drop table categories; 

drop table customers;

  

CREATE TABLE customers (

  cid           char(5),

  cname         char(15),

  phone         char(12),

  address       char(20),

  PRIMARY KEY (cid)

);

CREATE TABLE categories (

  catid         char(2),

  catname       char(12),

  PRIMARY KEY (catid)

);

CREATE TABLE products (

  pid           char(5),

  pname         char(15),

  unit          char(5),

  price         float,

  stock_qty     integer,

  catid         char(2),

  PRIMARY KEY (pid),

  FOREIGN KEY (catid) REFERENCES categories

);

CREATE TABLE orders (

  oid           char(5),

  cid           char(5),

  odate         date,

  charge_amount float,

  PRIMARY KEY (oid),

  FOREIGN KEY (cid) REFERENCES customers

);

CREATE TABLE orderline (

  lno           integer,

  oid           char(5),

  pid           char(5),

  qty           integer, 

  price         float, 

  PRIMARY KEY (oid, lno),

  FOREIGN KEY (oid) REFERENCES orders ON DELETE CASCADE,

  FOREIGN KEY (pid) REFERENCES products

);

Open in new window

0
Comment
Question by:bejhan
  • 5
  • 2
8 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 22700711
Guidelines for solving this problem.

1. Get the count at customer level.
2. Join this results with another subquery which gets the count of orders for customer 'c0001'.
3. filter results with the count from step 1 matching with step 2.
0
 
LVL 32

Expert Comment

by:awking00
ID: 22704138
Answering homework questions can be tricky because Oracle often offers multiple ways of finding a solution and the responders may not be aware of the class topic. In your case, you need to identify two sets of results - those whose customer is c0001 and those who are not c0001 - and then compare them for equality. The comparison might take the form of a join, the use of something like the set operator intersect, or it may involve the use of a group by function with a "having" clause. Good luck!
0
 
LVL 1

Author Comment

by:bejhan
ID: 22705732
sujith80, but the same count wouldn't necessarily mean the same set of items.
0
 
LVL 1

Author Comment

by:bejhan
ID: 22706771
I am totally stumped on this:

I have made a queries that finds customers who have the same number of products as c0001 but  I don't know how to check if the products are all the same.
SELECT b.cid

FROM (SELECT c.cid, COUNT(DISTINCT ol.pid) AS Count

FROM customers c, orders o, orderline ol

WHERE c.cid = 'c0001' AND o.oid = ol.oid

GROUP BY c.cid) a, 

(SELECT c.cid, COUNT(DISTINCT ol.pid) AS Count

FROM customers c, orders o, orderline ol

WHERE c.cid = o.cid AND o.oid = ol.oid

GROUP BY c.cid) b

WHERE a.count = b.count;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 27

Accepted Solution

by:
sujith80 earned 200 total points
ID: 22709618
follow this
SQL> select * from tbl1;
 

       CID PRODUCT                                                              

---------- --------------------                                                 

        10 prod1                                                                

        10 prod0                                                                

        20 prod2                                                                

        20 prod0                                                                

        40 prod1                                                                

        40 prod0                                                                

        50 prod5                                                                

        50 prod0                                                                

        60 prod1                                                                

        60 prod0                                                                

        70 prod0                                                                
 

11 rows selected.
 

SQL> select distinct cid

  2  from (

  3  select

  4   t.cid, t.product, X.cn, count(*) over (partition by t.cid) ct

  5  from

  6  tbl1 t,

  7  (select cid, product , count(*) over() cn from tbl1 where cid = 40) X

  8  where

  9  t.product = X.product

 10  )

 11  where cn = ct and cid <> 40 ;
 

       CID                                                                      

----------                                                                      

        10                                                                      

        60                                                                      
 

SQL> spool off

Open in new window

0
 
LVL 1

Author Comment

by:bejhan
ID: 22715491
Okay I figured it out just in time :) I was looking at the question the wrong way, trying to include those who met requirements instead of excluding those who didn't.
SELECT cid

FROM customers

WHERE cid <> 'c0001'

MINUS

SELECT c.cid

FROM customers c, orders o, orderline ol 

WHERE c.cid <> 'c0001' AND c.cid = o.cid AND o.oid = ol.oid AND ol.pid NOT IN (SELECT ol.pid FROM orders o, orderline ol WHERE o.cid = 'c0001' AND o.oid = ol.oid) 

MINUS 

SELECT a.cid FROM (SELECT c.cid, COUNT(DISTINCT ol.pid) AS Count 

FROM customers c, orders o,  orderline ol WHERE c.cid <> 'c0001' AND c.cid = o.cid AND o.oid = ol.oid GROUP BY c.cid) a, (SELECT COUNT(DISTINCT ol.pid) AS Count FROM orders o,  orderline ol WHERE o.cid = 'c0001' AND o.oid = ol.oid GROUP BY o.cid) b 

WHERE a.count <> b.count;

Open in new window

0
 
LVL 1

Author Comment

by:bejhan
ID: 22715550
I did not see that sujith80 had posted a solution, my apologies.
0
 
LVL 1

Author Comment

by:bejhan
ID: 22715551
I did not see that sujith80 had posted a solution, my apologies.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now