Solution for this SQL query?

Question

Suppose you have these tables:

Table Name: Salesman Fields: S_ID(Primary Key), Name

Table Name: Region_1 Fields: Reg_ID(Primary Key), S_ID(Foreign Key), sales

Table Name: Region_2 Fields: Reg_ID(Primary Key), S_ID(Foreign Key), sales

Table Name: Region_3 Fields: Reg_ID(Primary Key), S_ID(Foreign Key), sales

Table Name: Region_4 Fields: Reg_ID(Primary Key), S_ID(Foreign Key), sales

Query 1: Find out total of sales of each salesman in all the regions.

Query 2: Find out total of sales of a particual salesman in all the regions. (if the first one is solved I think this will be easy. :-) )

Answer 1

I agree this is a crappy database design, but if it is homework I guess that is not the part in question. I would consider solving this with a union -- join salesman and all the region tables together, and sum the sales figure.

Answer 2

Query 1:

SELECT S.S_ID, S.Name, 
  ((SELECT SUM(R1.sales) FROM Region_1 R1 WHERE S.S_ID = R1.S_ID) +
   (SELECT SUM(R2.sales) FROM Region_2 R2 WHERE S.S_ID = R2.S_ID) +
   (SELECT SUM(R3.sales) FROM Region_3 R3 WHERE S.S_ID = R3.S_ID) +
   (SELECT SUM(R4.sales) FROM Region_4 R4 WHERE S.S_ID = R4.S_ID)) Total
FROM Salesman S

Or:

SELECT S.S_ID, S.Name, (IFNULL(R1.sales, 0) + IFNULL(R2.sales, 0) + IFNULL(R3.sales, 0) + IFNULL(R4.sales, 0)) Total
FROM Salesman S LEFT JOIN Region_1 R1 ON S.S_ID = R1.S_ID
     LEFT JOIN Region_2 R2 ON S.S_ID = R2.S_ID
     LEFT JOIN Region_3 R3 ON S.S_ID = R3.S_ID
     LEFT JOIN Region_4 R4 ON S.S_ID = R4.S_ID;

Or ...

Create a better schema. What would you do if the business expanded to other countries, and all of a sudden you had 200000 regions? : - ).

Answer 3

This design seems a little limited, butmight be how the teacher wants to introduce some new concepts, so lets not argue to long on that point.

Read a little on

UNION

UNION is used to combine the result from multiple SELECT statements into a single result set.

And GROUP BY and SUM to group by a specific sales person, and sum the values.

Answer 4

Query 1: Find out total of sales of each salesman in all the regions.

---

Use:

   SELECT s.name,
          COALESCE(SUM(x.sales), 0) 
     FROM SALESMAN s
LEFT JOIN (SELECT r1.s_id,
                  r1.sales
             FROM REGION_1 r1
           UNION ALL
           SELECT r2.s_id,
                  r2.sales
             FROM REGION_2 r2
           UNION ALL
           SELECT r3.s_id,
                  r3.sales
             FROM REGION_3 r3
           UNION ALL
           SELECT r4.s_id,
                  r4.sales
             FROM REGION_4 r4) x ON x.s_id = s.s_id

Query 2: Find out total of sales of a particual salesman in all the regions.

---

Add the WHERE clause to the query above:

   SELECT s.name,
          COALESCE(SUM(x.sales), 0) 
     FROM SALESMAN s
LEFT JOIN (SELECT r1.s_id,
                  r1.sales
             FROM REGION_1 r1
           UNION ALL
           SELECT r2.s_id,
                  r2.sales
             FROM REGION_2 r2
           UNION ALL
           SELECT r3.s_id,
                  r3.sales
             FROM REGION_3 r3
           UNION ALL
           SELECT r4.s_id,
                  r4.sales
             FROM REGION_4 r4) x ON x.s_id = s.s_id
    WHERE s.s_id = ?

...replace the ? with the salesperson's s_id value.

The Lesson: UNION vs UNION ALL

---

UNION and UNION ALL will allow you to combine two queries, but they have to have the same data types in the column positions. For example, say query one returns data types in the following order:

1. Integer
2. String

Any subsequent query that is union'd to the first has to return the same data types in the same position. Pay special attention to the fact of data types - it doesn't guarantee the data within the column.

The second issue to UNIONs is knowing the difference between UNION and UNION ALL. UNION will remove duplicates if they exist (equivalent to using DISTINCT), which is not desired in the given scenario. UNION ALL will not remove duplicates, and is faster because of this.