Solved

Four Queries - two diff & two easy one

Posted on 2004-04-22
12
397 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
  • 4
  • 3
12 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
Comment Utility
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
Comment Utility
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
Comment Utility
all right. You try them out..and we are here to help if you run into problems.
0
 

Author Comment

by:saj_john
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:saj_john
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>> 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
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…

743 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

15 Experts available now in Live!

Get 1:1 Help Now