SQL Reports

All Things Being SQL – SQL Reports and SQL Reporting Resources

SQL REPORTS – SELECTING CONDITIONALLY PART – 6.2

Find out more on SQL Reports: Earlier we had assumed how the suppliers and the Invoice tables looked. A question that may be asked is why can’t the table be stored in the below mentioned format? Why split the table in the first place?

INVOICE_NO SUPPLIER_ID NAME CITY INVOICE_DATE AMOUNT
1 1 ABC Corp New York 24-Feb-11 1234
2 2 DEF Inc Washington 24-Feb-11 2345
3 3 GHI Associates San Francisco 24-Feb-11 3455
4 3 GHI Associates San Francisco 25-Feb-11 2314
5 2 GHI Associates San Francisco 26-Feb-11 9876
6 1 ABC Corp New York 27-Feb-11 4500

FIGURE42: The clubbed table

WHY SPLIT THE DATA INTO TABLES AT ALL?

There are many disadvantages of this approach especially when the number of rows in this table exceeds more than a 1000 and in practical databases this may usually be the case. The database is actually split because of the following reasons:

  1. To reduce redundancy: In the SUPPLIER_ID field the values are repeated twice, therefore even the corresponding NAME and CITY values are repeated. Imagine if there are about 50 suppliers who have issued about 1000 invoices in total. Can you now imagine how much repeated values the database will contain? A repeated text value will only further increase the size of the table. When the tables were split, only the SUPPLIER_ID column contained repeated values.
  2. To improve access time: A larger table means that the system would have to traverse a large section of stored data to retrieve a value. This becomes particularly important if the table is accessed frequently. Nested SELECT queries may require multiple accesses to a table. A large table basically makes a query run slower than it ought to be.

To sum it up, database designers split data into various tables to reduce query execution times and maximize storage efficiency. Having said all that, a database designer might even choose not to split the table if the number of rows is low. This in turn might actually increase certain query execution speeds simply because all the data is basically clubbed n a single table. There is no need to look into another table to get the supplier name.

NESTING ONE OR MORE SELECT CLAUSES TO JOIN

  1. HOW WOULD YOU ANSWER THIS: WHICH SUPPLIERS HAVE NOT ISSUED ANY INVOICES TO US?

The answer to this question lies in the clever use of the IN and DISTINCT clause as well as the feature which allows us to include one SELECT query within another.

SELECT SUPPLIER_ID, NAME FROM SUPPLIERS
WHERE SUPPLIER_ID NOT IN
(SELECT DISTINCT(SUPPLIER_ID) FROM INVOICES);
SUPPLIER_ID NAME
4 JKL Industries

FIGURE 43: The Output

  • The inner query is evaluated first, the results in the following output: SUPPLIER ID: 1, 2, 3.
  • The outer query then becomes SELECT SUPPLIER_ID, NAME FROM SUPPLIERS WHERE SUPPLIER_ID NOT IN (1, 2, 3);
  • Hence the result.

This example has been added simply to show how even the most impossible looking questions can be answered by clever use of the operators. Such use is only limited by the users imagination.

THE “SET” OPERATORS

SUPPLIER_ID NAME CITY SUPPLIER_ID NAME CITY
1 ABC Corp New York 2 DEF Inc Washington
2 DEF Inc Washington 3 GHI Associates San Francisco
4 JKL Industries Washington 4 JKL Industries Washington
5 MNO Sales Florida

FIGURE 44: Two similar tables named SUP1 and SUP2

Imagine two tables as shown above. Note that they are similar i.e. they may have different values, but their column formats are essentially the same. Three clauses, namely UNION, MINUS and INTERSECT are called set operators. If you remember the set theory taught in your mathematics class, you would instantly have an idea about what these clauses do. Even if you do not know anything about the Set theory in general, these operators are still very easy to understand.

UNION OPERATOR

SELECT * FROM SUP1
UNION
SELECT * FROM SUP2;
SUPPLIER_ID NAME CITY
1 ABC Corp New York
2 DEF Inc Washington
3 GHI Associates San Francisco
4 JKL Industries Washington
5 MNO Sales Florida

FIGURE 45: The UNION operator

A UNION operator combines two similar tables. It removes duplicate entries. Basically the combined table contains the following:

  1. All rows in SUP1.
  2. All rows in SUP2
  3. All rows in SUP1 and SUP2

INTERSECT OPERATOR

SELECT * FROM SUP1
INTERSECT
SELECT * FROM SUP2;
SUPPLIER_ID NAME CITY
2 DEF Inc Washington
4 JKL Industries Washington

FIGURE 46: The INTERSECT operator

The INTERSECT operator combines two similar tables. It only includes those rows that are common to both the tables. In our case the combined table contains rows that are common to both SUP1 and SUP2

MINUS OPERATOR

SELECT * FROM SUP1
MINUS
SELECT * FROM SUP2;
SUPPLIER_ID NAME CITY
1 ABC Corp New York

FIGURE 47: The MINUS Operator

The MINUS operator combines two similar tables. It only includes those rows that are contained in the first table, but not in the second one. In our case the combined table contains rows that are unique to SUP1.

Had we written

SELECT * FROM SUP2
MINUS
SELECT * FROM SUP1;

We would have got this result.

SUPPLIER_ID NAME CITY
3 GHI Associates San Francisco
5 MNO Sales Florida

FIGURE 48: A different result obtained by using the MINUS operator

This basically sums up the discussion on the SELECT clause as well as the SET operators. A point to note is that so far, we have only covered some of the few uses of the SELECT clause. The SELECT clause can be used in many other ways by an advanced user of SQL. But for the sake of simple reporting and formatting, this much will suffice.

SQL is an easy to learn language and is also very close to the English. Each query in SQL is basically a grammatically sound sentence in the English language. The more a reader delves deeper into this language, the richer it would seem.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>