Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

Four Queries - two diff & two easy one

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
saj_john
Asked:
saj_john
  • 4
  • 3
2 Solutions
 
catchmeifuwantCommented:
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
 
saj_johnAuthor Commented:
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
 
catchmeifuwantCommented:
all right. You try them out..and we are here to help if you run into problems.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
saj_johnAuthor Commented:
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
 
saj_johnAuthor Commented:
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
 
catchmeifuwantCommented:
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
 
saj_johnAuthor Commented:
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
 
LowfatspreadCommented:
>> 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now