SQL Query

sql tables :
SHOP(shop_number,shop_name,shop_address,shop_owner)
CUSTOMER(cust_num,cust_name,cust_addr)
SUPPLIED(item_num,cust_num,shop_num,date,price)

How to :
(i) find the names of customers who have been supplied items of max total price?
(ii) list the shop owners who supplied some item to the address "addr1"?
LVL 1
dtivmkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaitu chaituCommented:
what are the relation between these tables
0
dtivmkAuthor Commented:
cust_num is PRIMARY key for CUSTOMER and foreign key for others.
item_num is PRIMERY for SUPPLIED and foreign for others.
shp_number is PRIMERY for SHOP and foreign key for others.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as this sounds like homework/exercise, you should know that you should not receive the full solution directly, but only guidance.
please try to find out how it should be, and posting what you have with explanation what is wrong in the results.

note: (i) is not 100% clear as question to me...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chaitu chaituCommented:
I)select cust_num from CUSTOMER c, SUPPLIED s
where c.cust_num=s.cust_num
and 1 =(select count(max(price)) from SUPPLIED);

select shop_name from SHOP sh
where
exists(
SELECT "X" from SUPPLIED s
where sh.shop_number=s.shop_number
and shop_address="addr1")
0
dtivmkAuthor Commented:
Firstly,it is not a homework exercise.

And, I do need help...

Secondly,
select cust_num from CUSTOMER c, SUPPLIED s
where c.cust_num=s.cust_num
and 1 =(select count(max(price)) from SUPPLIED);
 
will select the customer, to whom an item was sold with maximum price.

I need to select the customer, for whom, the sum of total purchases is maximum.

Please !
0
chaitu chaituCommented:
select cust_num from CUSTOMER c where and 1 =(select count(max(sum(price)) from SUPPLIED s
c.cust_num=s.cust_num);
0
dtivmkAuthor Commented:
SELECT c.name, max.total
FROM {
SELECT custId, total
FROM {
SELECT CUSTOMER.cnum AS "custId", sum( SUPPLIED.price ) AS "total"
FROM CUSTOMER, SUPPLIED
WHERE SUPPLIED.cnum = CUSTOMER.cnum
GROUP BY CUSTOMER.cnum}tt
WHERE total = {
SELECT max( total )
FROM {
SELECT sum( SUPPLIED.price ) AS "total"
FROM SUPPLIED, CUSTOMER
WHERE CUSTOMER.cnum = SUPPLIED.cnum
GROUP BY }tt}}max, CUSTOMER c
WHERE c.cnum = max.custId
LIMIT 0 , 30

MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT custId,total FROM
{
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
don't use { } ... but ( )

>will select the customer, to whom an item was sold with maximum price.
any item? or just the most expensive item? or the total order with the max(sum(price)) ?
0
Mark WillsTopic AdvisorCommented:
Which address is addr 1 ?

1) please explain total Max Price

SELECT c.cust_num,c.cust_name,c.cust_addr,sum( S.price ) AS "total"
FROM SUPPLIED S, CUSTOMER c
WHERE C.cust_num = S.cust_num
GROUP BY c.cust_num,c.cust_name,c.cust_addr

2)

SELECT shop.shop_number,shop_name,shop_address,shop_owner, cust_addr as "sold_to_address"
FROM SUPPLIED S, CUSTOMER, SHOP
WHERE CUSTOMER.cust_num = S.cust_num and SHOP.shop_num = S.shop_num
AND cust_addr = "ADDR"

0
dtivmkAuthor Commented:
SELECT c.name, max.total
FROM (


SELECT custId, total
FROM (

SELECT CUSTOMER.cnum AS "custId", sum( SUPPLIED.price ) AS "total"
FROM CUSTOMER, SUPPLIED
WHERE SUPPLIED.cnum = CUSTOMER.cnum
GROUP BY CUSTOMER.cnum}tt
WHERE total = (

SELECT max( total )
FROM (

SELECT sum( SUPPLIED.price ) AS "total"
FROM SUPPLIED, CUSTOMER
WHERE CUSTOMER.cnum = SUPPLIED.cnum
GROUP BY
)tt
)
)max, CUSTOMER c
WHERE c.cnum = max.custId
LIMIT 0 , 30

MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}tt

WHERE total =
   (
     SELECT max( total ) FROM
       (
         ' at line 10

0
chaitu chaituCommented:
instead of  }  this use );
0
dtivmkAuthor Commented:
Ok,
let me restart the question..
3 tables :
CUSTOMER -> cnum, cname, caddress (cnum is the primary key)
SHOP -> shopnum, shopname, shopaddress, owner ( shopnum is the primary key)
SUPPLIED -> inum, cnum, shopnum, date, price (cnum is the foreign key in the CUSTOMER table,
shopnum is the foreign key in the SHOP table)

(i) Find the name(s) of the customer(s) who have been supplied items, whose sum of prices is maximum.
For e.g. if  customer named 'A' bought 2 items worth 30,20
and customer named 'B' bought 2 items worth 40,5
output should be
A 50(total purchase value is maximum)

(ii)
0
chaitu chaituCommented:
can you post some sample data in each of these tables so that we can give you better answer.what is the result you are expecting
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in oracle:
with s as (
  SELECT cust_num, sum(price) total_price
    FROM SUPPLIED 
   GROUP BY cust_num
)
, d as (
  SELECT s.cust_num, s.total_price, c.cust_name
       , row_number() over (order by s.total_price desc) r
    FROM s
    JOIN customer c
      on c.cust_num = s.cust_num
)
SELECT cust_num, cust_name, total_price
  FROM d
 WHERE r = 1
;

Open in new window

0
Mark WillsTopic AdvisorCommented:
how is this different ?


SELECT c.cust_num,c.cust_name,c.cust_addr,sum( S.price ) AS "total"
FROM SUPPLIED S, CUSTOMER c
WHERE C.cust_num = S.cust_num
GROUP BY c.cust_num,c.cust_name,c.cust_addr
ORDER BY sum( S.price ) desc
LIMIT 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
LIMIT 1 is MySQL syntax, and not Oracle Syntax...
I checked only the question zones, and overlooked the error message which indicates that it is indeed MySQL.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that MySQL will allow this:
SELECT c.cust_num,c.cust_name,c.cust_addr,sum( S.price ) AS `total`
FROM SUPPLIED S, CUSTOMER c
WHERE C.cust_num = S.cust_num
GROUP BY c.cust_num,c.cust_name,c.cust_addr
ORDER BY `total` desc
LIMIT 1

Open in new window

0
dtivmkAuthor Commented:
SELECT c.cnum, c.cname, c.caddress, sum( S.price ) AS "total"
FROM SUPPLIED S, CUSTOMER c
WHERE c.cnum = S.cnum
GROUP BY c.cnum
ORDER BY sum( S.price ) DESC
LIMIT 1

MySQL said:  

#1111 - Invalid use of group func
0
chaitu chaituCommented:
IN THE GROUP BY you need to put c.cnum, c.cname, c.caddress
0
dtivmkAuthor Commented:
This is the latest :

SELECT c.cnum, c.cname, c.caddress, sum( S.price ) AS "total"
FROM SUPPLIED S, CUSTOMER c
WHERE c.cnum = S.cnum
GROUP BY c.cnum, c.cname, c.address
ORDER BY sum( S.price ) DESC
LIMIT 1

MySQL said:  

#1111 - Invalid use of group function

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let's try to remove the spaces:
SELECT c.cust_num,c.cust_name,c.cust_addr, sum(S.price) AS `total`
FROM SUPPLIED S, CUSTOMER c
WHERE C.cust_num = S.cust_num
GROUP BY c.cust_num,c.cust_name,c.cust_addr
ORDER BY `total` desc
LIMIT 1

Open in new window

0
Mark WillsTopic AdvisorCommented:
OK, don't use the equi-join, use inner instead...

SELECT c.cnum, c.cname, c.caddress, sum( S.price ) AS "total"
FROM SUPPLIED S
IINER JOIN CUSTOMER c ON c.cnum = S.cnum
GROUP BY c.cnum, c.cname, c.address
ORDER BY sum( S.price ) DESC
LIMIT 1

or order by "total" as per angelIII...

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
btw, with MySQL you do NOT need to put all the columns in the group by:
SELECT c.cust_num,c.cust_name,c.cust_addr,sum(S.price) AS `total`
FROM SUPPLIED S, CUSTOMER c
WHERE C.cust_num = S.cust_num
GROUP BY c.cust_num
ORDER BY `total` desc
LIMIT 1

Open in new window

0
Mark WillsTopic AdvisorCommented:
and inner is normally spelled INNER
0
Mark WillsTopic AdvisorCommented:
@dtivmk - BTW what are the correct column names ?
0
dtivmkAuthor Commented:
(i) what is the "DESC" keyword for?
(ii) Is there a way to write this query without using LIMIT, i.e.
is there way to write a query which actually returns only 1 row,
rather than relying on LIMIT to achieve it.

this is the final query that has worked :

SELECT c.cnum, c.cname, c.caddress, sum( S.price ) AS `total`
FROM SUPPLIED S, CUSTOMER c
WHERE c.cnum = S.cnum
GROUP BY c.cnum
ORDER BY `total` DESC
LIMIT 1

@mark_wills - above query has the correct column names.
-------
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
(i) what is the "DESC" keyword for?
are you serious? ...
it's to ORDER BY in DESCENDING order...

(ii) Is there a way to write this query without using LIMIT,
yes, but that will be MUCH less efficient
0
dtivmkAuthor Commented:
ok,
here is the final post from my side, with both the questions and answers
3 tables :
CUSTOMER -> cnum, cname, caddress (cnum is the primary key)
SHOP -> shopnum, shopname, shopaddress, owner ( shopnum is the primary key)
SUPPLIED -> inum, cnum, shopnum, date, price (cnum is the foreign key in the CUSTOMER table,
shopnum is the foreign key in the SHOP table)

(i) Find the name(s) of the customer(s) who have been supplied items, whose sum of prices is maximum.
For e.g. if  customer named 'A' bought 2 items worth 30,20
and customer named 'B' bought 2 items worth 40,5
output should be
A 50(total purchase value is maximum)

(ii) Find the name(s) of SHOP owner(s) who supplied at least one item
to customer with address "addr_c0"
 
 
(i)
SELECT c.cnum, c.cname, c.caddress, sum( S.price ) AS `total`
FROM SUPPLIED S, CUSTOMER c
WHERE c.cnum = S.cnum
GROUP BY c.cnum
ORDER BY `total` DESC
LIMIT 1
 
(ii)
SELECT sh.owner
FROM SHOP sh, CUSTOMER c, SUPPLIED s
WHERE c.cnum = s.cnum
AND sh.shopnum = s.shopnum
AND c.caddress = "addr_c0"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

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.