?
Solved

Four Queries - two diff & two easy one

Posted on 2004-04-22
12
Medium Priority
?
471 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
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!

 

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 100 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 100 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

762 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