Solved

SQL Query

Posted on 2008-10-18
29
311 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 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
in SAS how to create a record for each combination of the two duplicates? 16 104
Need help with a Stored Proc on Sql Server 2012 4 28
check mysql insert 12 26
sql server query 12 25
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…

861 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