Solved

SQL Query

Posted on 2008-10-18
29
302 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
  • 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 142

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

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 142

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 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 142

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 142

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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 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 142

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 142

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

759 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

20 Experts available now in Live!

Get 1:1 Help Now