SQL Reports

All Things Being SQL – SQL Reports and SQL Reporting Resources

Selecting Conditionally Part – 3 SQL Database

TEXTUAL SEARCHING AND QUERYING IN SQL DATABASE

  1. SQL Database – Let us consider the “Suppliers” table (which we have modified a bit). Let the question be: WHICH SUPPLIERS ARE LOCATED IN WASHINGTON?

SUPPLIER_ID

NAME

CITY

1

ABC Corp

New York

2

DEF Inc

Washington

3

GHI Associates

San Francisco

4

JKL Industries

Washington

Figure 19: The “Suppliers” table

SELECT * FROM SUPPLIERS
WHERE CITY = “Washington”;

SUPPLIER_ID

NAME

CITY

2

DEF Inc

Washington

4

JKL Industries

Washington

Figure 20: The output as expected

If we had written SELECT * FROM SUPPLIERS WHERE CITY = “WASHINGTON“, the query would have returned nothing. This is because even though SQL DOES NOT IGNORE CASE WHEN THE CONTENT IS INSIDE DOUBLE INVERTED COMMAS. That is the reason the query contained “Washington” and not “washington” or “WASHINGTON” There however exists a simple workaround for this issue. Simply put the city name in SINGLE INVERTED COMMAS like this: SELECT * FROM SUPPLIERS WHERE CITY = ‘WASHINGTON’. SINGLE INVERTED COMMAS SIMPLY TELL THE SQL INTERPRETER THAT CHARACTER CASE IS TO BE IGNORED while searching.

THE LIKE CLAUSE    

Imagine a scenario where the user is not aware of the position of characters in a name. What if you want to search for all the names in a customer table which contain the character group “ike” anywhere in their name? When executing such a query you would want that a name like “Mike” should be displayed along with a name like “Spike” or “Ikea”. This is where the LIKE clause comes handy.

  1. LIST ALL SUPPLIER NAMES THAT HAVE THE CHARACTER GROUP “IN” ANYWHERE IN THEIR NAMES.
SELECT NAME FROM SUPPLIERS
WHERE NAME LIKE ‘%IN%’;

SUPPLIER_ID

NAME

CITY

2

DEF Inc

Washington

4

JKL Industries

Washington

FIGURE 21: The use of LIKE

Notice the use of single inverted commas.

A BRIEF INTRODUCTION TO WILDCARDS

The “%” sign used here is what in programming jargon is known as a wildcard. The “%” sign can simply be read as “any number/combination of characters”. The use of the “%” sign, before and after “In” character group simply instructs the computer, to search for the “In” group anywhere in the name, regardless of the number of characters before or after it.

So even if the NAME column contained some names like “Intech Solutions” or “Pablo’s Drive In”, these names too would have been displayed. The addition of the “%” sign before and after the “In” character group makes its position in the name irrelevant.

If we would have used ‘In%’ nothing would have been displayed as there are no names that begin with “In”. Similar remarks apply to the use of ‘%In’ as there are no names that end with “In” in the table.

Another common wildcard in the SQL database language is the “_” (Underscore). It can simply be considered a “one character fill in the blank”. An example would make this clear.

SELECT NAME FROM SUPPLIERS
WHERE NAME LIKE ‘DEF IN_’;

SUPPLIER_ID

NAME

CITY

2

DEF Inc

Washington

FIGURE 22: The use of “_”

If there was a name like “DEF Ind” here, it too would have been displayed in the output. The “_” simply tells the computer that “any single character or symbol” can replace the space denoted by “_” symbol.

COMPUTED COLUMNS

Let’s have a look at example and the table given below to understand the need and convenience of having computed columns.

STUDENT_NAME

ENGLISH

MATHS

SCIENCE

Mark

84

70

90

Steve

79

65

82

Clarke

93

60

87

FIGURE 23: A simple table named “STUDENTS” that shows marks obtained by these students (out of 100 in each subject).

  1. OBTAIN THE TOTAL MARKS FOR ALL STUDENTS IN A SEPARATE COLUMN.

    We can obviously print this table and do it manually. Another way would be to paste this table in a separate spreadsheet program and then, calculate and create the required column there. An easier way however, would be to calculate this column using an SQL query itself.

SELECT (ENGLISH + MATHS + SCIENCE)
FROM STUDENTS

(ENGLISH + MATHS + SCIENCE)244226240FIGURE 24: The output – the column title does not look good This column is what is technically termed as a computed column. Such columns never physically exist in the database itself, but can be created on the fly by SQL. Such columns are best used for reporting purposes and also provide an additional insight into the data. Note that we have used an arithmetic operator (the “+” sign) to create this column. Below is the list of common arithmetic operators that can be used in SQL SOME OF THE COMMONLY USED ARITHMETIC OPERATORS IN SQLOperatorDescription+Addition-Subtraction*Multiplication/DivisionFIGURE 25: Standard Arithmetic Operators The AS clause As can be seen from figure 20, the column name “ENGLISH + MATHS +SCIENCE” will not look good on any report. These is where the AS clause is useful. The AS clause tells the computer to substitute a column title with some other (and more meaningful) title; if required. CREATE A TABLE THAT DISPLAYS THE NAME, MARKS, AVERAGE AND TOTAL OF ALL THE STUDENTS.

SELECT *, (ENGLISH + MATHS + SCIENCE) AS TOTAL,
((ENGLISH + MATHS +SCIENCE) / 3 ) AS AVERAGE,
FROM STUDENTS

STUDENT_NAME

ENGLISH

MATHS

SCIENCE

TOTAL

AVERAGE

Mark

84

70

90

244

81.33

Steve

79

65

82

226

75.33

Clarke

93

60

87

240

80

FIGURE 26: A SIMPLE TABLE NAMED “STUDENTS”

 SQL Database

The above needs no explanation. And of course, the column headings look more presentable.

Have a look at this section of the query. “(ENGLISH + MATHS + SCIENCE) AS TOTAL, ((ENGLISH + MATHS + SCIENCE) / 3) AS AVERAGE”. We had to repeat the same calculations to create the “AVERAGE” column.

We cannot write “(ENGLISH + MATHS + SCIENCE) AS TOTAL, TOTAL / 3 AS AVERAGE,” simply because we cannot reference a column that does not physically exist in the table.

In the next section we will focus on some of the simple statistical/aggregate functions provided by SQL database.

2 comments

  • Please update your post to use correct terminology. Use single quote instead of single inverted comma. Use double quote instead of double inverted commas. Single quote and double quote are standard terms used around the world. Using nonstandard terms promotes confusion.

  • The_Author on April 8, 2011 at 11:29 pm said:

    Reply

    I apologize for any inconvenience caused. Anyway, “Old habits die hard” and so i wrote this one.
    Thanks for sharing your concern. I will ask to admin to edit it.

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>