SQL Reports

All Things Being SQL – SQL Reports and SQL Reporting Resources

SELECTING CONDITIONALLY PART – 2 SQL Database

SQL Database

SQL database is a rich language and provides a wide range of operators, clauses and functions to formulate a query. As we have seen previously, sometimes there exists more than one to get the same result using a different set/combination of operators.

Let’s take a look at some of the other operators available to a user. A point to be noted is that these some of these operators may not be clearly classified as arithmetic or logical. The details of SQL database implementation and further uses of these operators can best be described elsewhere. For a beginner though, the important thing to remember is that these operators are convenient and simple to use.

  1. THE BETWEEN OPERATOR    
    1. WHICH INVOICES (FIGURE 7) HAVE AMOUNTS THAT LIE BETWEEN 4000 AND 10000?

SELECT * FROM INVOICES

WHERE AMOUNT

BETWEEN

4000 AND 10000;

INVOICE_NO

SUPPLIER_ID

INVOICE_DATE

AMOUNT

5

2

26-Feb-2011

9876

6

1

27-Feb-2011

4500

FIGURE 14: THE BETWEEN OPERATOR

As can be seen from the output, the BETWEEN operator requires a range of acceptable values to be clearly specified. Also notice that an AND operator separates the minimum and maximum values in the query.

Another way to write the same query would have been. “SELECT * FROM INVOICES WHERE AMOUNT >= 4000 AND AMOUNT <= 10000;

Also notice the use of the “>=” and “<=” operators. Therefore, if the table contained an invoice that amounted to 4000$, it too would have been selected.

The BETWEEN operator only accepts numerical or date/time values as inputs, it won’t accept textual queries. For Ex. the clause “BETWEEN B AND G” is invalid.

2) SELECT ONLY THOSE INVOICES WHICH HAVE BEEN ISSUED BETWEEN 24TH AND 26TH OF THE MONTH.

SELECT * FROM INVOICES
WHERE INVOICE_DATE BETWEEN ‘2011-02-24′ AND ‘2011-02-26′;

INVOICE_NO

SUPPLIER_ID

INVOICE_DATE

AMOUNT

1

1

24-Feb-2011

1234

2

2

24-Feb-2011

2345

6

1

24-Feb-2011

4500

4

3

25-Feb-2011

2314

5

2

26-Feb-2011

9876

Figure 15: The output of using the between clause

The date range has been shaded to indicate the proper format of entering dates in a query. Some databases (like Oracle) will allow the format ’24-FEB-2011′ but that is vendor dependent. You should always use the format ‘2011-02-24′ as it is correct for all SQL systems.

It is recommended the user’s refer to their program documentation and/or seek technical help before attempting to use any queries involving dates.

  1. THE IN OPERATOR    

3) SELECT ONLY THOSE INVOICES THAT AMOUNT TO 4500, 3455 OR 3345.

SELECT * FROM INVOICES

WHERE AMOUNT

IN

(4500, 2345, 3455);

INVOICE_NO

SUPPLIER_ID

INVOICE_DATE

AMOUNT

6

1

27-Feb-2011

4500

3

3

24-Feb-2011

3455

2

2

24-Feb-2011

2345

FIGURE 16: THE IN OPERATOR

Another way to write the same query would have been. “SELECT * FROM INVOICES WHERE AMOUNT = 4500 OR AMOUNT = 2345 OR AMOUNT = 3455;”. However, the query formulated using IN looks better and simpler.

Notice the use of the OR operator above. We have not used AND. The entire meaning of the query would have changed if we had used AND.

The IN operator is used when only “specific values of interest” in some particular column(s) are required. Unlike the BETWEEN operator only “a range of specific values” is required for using the IN operator.

Have a look at the table given below.

INVOICE_NO

STATUS

 

FIGURE 17: The “PAID” table – A simple table that shows invoice numbers and their payment status. Here is what the letters in the STATUS column mean

1

Y

2

Y

3

N

 

4

P

Y

Fully paid

5

P

N

Not paid at all

6

N

P

Partially paid.
  1. SELECT ONLY THOSE INVOICES FOR WHICH PAYMENT HAS BEEN ISSUED (PAID AND PARTIALLY PAID).
SELECT * FROM PAID
WHERE STATUS IN (‘P’, ‘Y’);

INVOICE_NO

PAID

1

Y

2

Y

4

P

5

P

FIGURE 18: The output

The above example proves that the IN operator can also be used for text based filtering. A more detailed explanation of text based querying will be dealt with in the following sections.

Another way to write the same query would have been: SELECT * FROM PAID WHERE NOT STATUS = ‘N';

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>