Solved

Four Queries - two diff & two easy one

Posted on 2004-04-22
12
437 Views
Last Modified: 2008-07-03
Salespeople

Snum  - PK
Sname
City
Comm.


Customers

Cnum  - PK
Cname
City
Rating
Snum  - FK

Orders

Onum - PK
Amt
Odate
Cnum  - FK
Snum  - FK



1)      Write a query that extracts from the customers table every customer assigned to a salesperson who currently has at least one other customer (besides the customer being selected) with orders in the orders table.

2)      Write a query using the EXISTS operator that selects all salespeople with customers located in their cities who are not assigned to them.

3)      Write a query that joins the customer table to itself to find all pairs of customers served by a single salesperson.

4)      Which is the date, order number, amt and city for each salesperson (by name) for the maximum order he has obtained



0
Comment
Question by:saj_john
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
12 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 10896358
Looks like an assignment. Violation of Membership agreement. Read this if you haven't :

http://www.experts-exchange.com/Community_Support/New_Topics/memberAgreement.jsp

Suggest you try writing the queries.If & when you face problems...you can seek the help of experts here..
0
 

Author Comment

by:saj_john
ID: 10896378
hi,

Its not an assignment due to some privacy issue , I can not explain you the real issue here thats Why :-) any ways never mind....
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 10896447
all right. You try them out..and we are here to help if you run into problems.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:saj_john
ID: 10897508
Sorry for the trouble, actually I am a web developer and do not have any db experiance. I'm just going through some of the tutorials now, let me see how I can make it.

You are free to eliminate this question :-) and if i still in trouble, will post the question again with what ever I come across.

Thanks.
0
 

Author Comment

by:saj_john
ID: 10897992
select c.cnum,s.snum from customer c,salesperson s ,orders o where c.cnum in
(select c.cnum from customer where 1 < 
(select count(*) from customers c,orders o where c.snum=s.snum && o.snum=s.snum));

this is what I found for the first one, is this ok????
0
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 25 total points
ID: 10898135
This might do the job for (1)

select C.*
from customers c, salesperson s
where c.snum = s.snum
and (s.snum,c.cnum) in (select s.snum, c.cnum
               from orders o, salesperson s
             where o.snum = s.snum
               group by s.snum, c.cnum
               having count(*) > 1 )

The sub-query will retrieve salesperson depnding on whether the salesperson has more than 1 customer (having count(*) > 1).

For the retrieved sales person & customer find the customer details.

Try it out and let me know if it works !!!
0
 

Author Comment

by:saj_john
ID: 10898467
Hi catch,

SQL> select * from customers;

     CNUM CNAME           CITY               RATING      SNUM
--------- --------------- --------------- --------- ---------
     2001 Hoffman         London                100      1001
     2002 Giovanni        Rome                  200      1003
     2003 Liu             San Jose              200      1002
     2004 Grass           Berlin                300      1002
     2006 Clemens         London                100      1001
     2008 Cisneros        San Jose              444      1007
     2007 Pereira         Rome                  100      1004

7 rows selected.

SQL> select * from orders;

     ONUM       AMT ODATE          CNUM      SNUM
--------- --------- --------- --------- ---------
     3001     18.69 03-OCT-96      2008      1007
     3003    767.19 03-OCT-96      2001      1001
     3002    1900.1 03-OCT-96      2007      1004
     3005   5160.45 03-OCT-96      2003      1002
     3006   1098.16 03-OCT-96      2008      1007
     3009   1713.23 04-OCT-96      2002      1003
     3007     75.75 04-OCT-96      2002      1003
     3008      4723 05-OCT-96      2006      1001
     3010   1309.95 06-OCT-96      2004      1002
     3011   9891.88 06-OCT-96      2006      1001

10 rows selected.

SQL> select * from salespeople;

     SNUM SNAME           CITY                 COMM
--------- --------------- --------------- ---------
     1001 Peel            London                .12
     1002 Serres          San Jose              .13
     1004 Motika          London                .11
     1007 Rifkin          Barcelona             .15
     1003 AxelRod         New York               .1
     1005 Fran            London                .26

6 rows selected.




SQL> select C.*
  2  from customers c, salespeople s
  3  where c.snum = s.snum
  4  and (s.snum,c.cnum) in (select s.snum, c.cnum
  5                 from orders o, salespeople s
  6              where o.snum = s.snum
  7                 group by s.snum, c.cnum
  8                 having count(*) > 1 )
  9  ;

     CNUM CNAME           CITY               RATING      SNUM
--------- --------------- --------------- --------- ---------
     2001 Hoffman         London                100      1001
     2002 Giovanni        Rome                  200      1003
     2003 Liu             San Jose              200      1002
     2004 Grass           Berlin                300      1002
     2006 Clemens         London                100      1001
     2008 Cisneros        San Jose              444      1007

6 rows selected.



select c.cnum,s.snum from customer c,salesperson s ,orders o where c.cnum in (select c.cnum from customer where 1 < (select count(*) from customers c,orders o where c.snum=s.snum and o.snum=s.snum));
 
This was returning 280 rows
DESIRED OUTPUT IS 8.

0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 25 total points
ID: 10901794
>> select c.cnum,s.snum from customer c,salesperson s ,orders o where c.cnum in
>>(select c.cnum from customer where 1 <
>>(select count(*) from customers c,orders o where c.snum=s.snum && o.snum=s.snum));

>>this is what I found for the first one, is this ok????


this is better
select c.cnum,c.cname,c.city,c.rating,c.snum
  from customers as c
 Where exists (select Onum from orders as o
                 where o.snum = c.snum
                   and c.cnum <> o.cnum)

you only need to "join" the customer and orders table...
its a correlated subquery....
good luck
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 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