Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query

Posted on 2008-10-18
29
Medium Priority
?
330 Views
Last Modified: 2012-05-05
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"?
0
Comment
Question by:dtivmk
[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
  • 9
  • 8
  • 6
  • +1
29 Comments
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22747342
what are the relation between these tables
0
 
LVL 1

Author Comment

by:dtivmk
ID: 22747350
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22747361
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22747367
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
 
LVL 1

Author Comment

by:dtivmk
ID: 22747393
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
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22747399
select cust_num from CUSTOMER c where and 1 =(select count(max(sum(price)) from SUPPLIED s
c.cust_num=s.cust_num);
0
 
LVL 1

Author Comment

by:dtivmk
ID: 22747452
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22747599
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22748716
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
 
LVL 1

Author Comment

by:dtivmk
ID: 22755463
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
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22755480
instead of  }  this use );
0
 
LVL 1

Author Comment

by:dtivmk
ID: 22755485
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
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22755497
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22755499
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 1000 total points
ID: 22755515
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22755525
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22755528
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
 
LVL 1

Author Comment

by:dtivmk
ID: 22755548
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
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22755550
IN THE GROUP BY you need to put c.cnum, c.cname, c.caddress
0
 
LVL 1

Author Comment

by:dtivmk
ID: 22755552
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22755560
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22755565
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22755566
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22755567
and inner is normally spelled INNER
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22755585
@dtivmk - BTW what are the correct column names ?
0
 
LVL 1

Author Comment

by:dtivmk
ID: 22755620
(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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22755633
(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
 
LVL 1

Author Comment

by:dtivmk
ID: 22755649
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
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…
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…

688 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