Structured Query Language Made Simple
Fukula Hastings Nyekanyeka

The SELECT Statement

When one accesses the database, they usually are trying to get something from the data that is stored in the database. One can choose to select all the data in the database or part of all the data available in the database. Since in relational database systems, the data is organized in tables, we need to SELECT information FROM a table or tables.
The general format of the SELECT statement is:
SELECT ColumnName1, ColumnName2, ColumnName2 ...
FROM TableName;

Lets start with an example of a Student Information Table that would relate IDNum, First Name, Last Name, Address and City:

StudentTable

IDNUM

FIRSTNAME

LASTNAME

ADDRESS

CITY

33236

Patrick

Betha 

P.O. Box 28

Lilongwe

33234

Twinko

Phiri 

P.O. Box 33

Blantyre

33235

Chiza

Phiri 

P.O. Box 21

Zomba

33237

Abel

Moya 

P.O. Box 72

Blantyre

33238

Roryon

Davis 

P.O. Box 18

Zomba

Now, let's say you want to see the information on each student in the database. You need to query the database for the information. Issuing a query in a relational database is like sending someone to get some information for you from a specific location in the house. If you wanted someone to bring you all the video tapes in your collection, you would need to tell them what you want them to get for you and where to get them as in "GET ME all the tapes FROM the tape rack". For the student information, the same query would say "GET ME all the student records FROM the student information table" which translates into the following SQL SELECT statement:

SELECT ALL
FROM StudentTable;

SELECT *
FROM StudentTable;

SELECT IDNum, FirstName, LastName, Address, City
FROM StudentTable;

If you sat down and asked people to get things for you for the whole day, one phrase phrase that you would always need to get things done would be " GET ME something" hence the "SELECT is the back bone of SQL ".

The following is the results of our query of the database:

StudentTable

IDNUM

FIRSTNAME

LASTNAME

ADDRESS

CITY

33236

Patrick

Betha 

P.O. Box 28

Lilongwe

33234

Twinko

Phiri 

P.O. Box 33

Blantyre

33235

Chiza

Phiri 

P.O. Box 21

Zomba

33237

Abel

Moya 

P.O. Box 72

Blantyre

33238

Roroyn

Davis

P.O. Box 18

Zomba

As you can observe from the statements issued above, you can display all fields from the table by listing all the field names or by representing the whole field list with an asterisk (*) or the reserved word "ALL".

Now, say you don't want all the information about each student. For example you want only every student's first name and last name. All you need to do is issue a SELECT statement asking for data from the same table but only displaying the data in the first names and last names as follows:

SELECT FirstName, LastName
FROM StudentTable;

Which says
"GET ME only each students' first names and last names
FROM the student information table"

NOTE:
Column names and table names must not have spaces Column names and table names must be typed as one word All colum names must be separated by a comma No comma is needed after the last column The statement ends with a semicolon (;)

Select statement with "Condition(s)"

Now, what if you do not want to list all the students in the table but only students with a particular last name or students from a particular city? This will require a "Condition" to be included in the SELECT statement. Say GET all student whose last names are Phiri.

A Conditional SELECT uses Relation Operators to specify a condition.

Relational Operators

SQL has six Relational Operators in SQL, namely,

=

Equal

!=

Not Equal 

>

Greater Than

<

Less Than

>=

Greater Than or Equal To 

<=

Less Than or Equal To

The WHERE clause is used to specify a condition in SQL. This tells the RDBMS to display only certain rows in the table based on the criteria described in that WHERE clause.
For example, the statement GET all student whose last names are Phiri would be issued in SQL as

SELECT IDNum, FirstName, LastName, Address, City
FROM StudentTable;
WHERE Lastname="Phiri";

Which displays the following

StudentTable

IDNUM

FIRSTNAME

LASTNAME

ADDRESS

CITY

33234

Twinko

Phiri 

P.O. Box 33

Blantyre

33235

Chiza

Phiri 

P.O. Box 21

Zomba

Suppose you had a table for employees of a company and you wanted to display the employee id, first names, last names, positions and salary for all employees whose salary is above K15,000. The SQL query for that would be

SELECT EmpID, FirstName, LastName, Position, Salary
FROM Employees;
WHERE Salary > 15,000;

How about employees who are making less than K3,000? The query would be issued as follows:-

SELECT EmpID, FirstName, LastName, Position, Salary
FROM Employees;
WHERE Salary < 3,000;

Sometimes you would want to know which employeess make K4,000 or less? The following query with the "LESS THAN OR EQUAL TO" operator would take care of it:-

SELECT EmpID, FirstName, LastName, Position, Salary
FROM Employees;
WHERE Salary <= 4,000;

Logical Operators (AND and OR)

Now lets assume that the StudentTable had more fields for Grade and Major and you wanted to find out which students scored a grade 80 percent and above and are in a particular major, say CS for Computer Science. In this case, you need to use the AND logical operator to join the two conditions as follows:-

SELECT FirstName, LastName, Grade, Major
FROM StudentTable WHERE Grade >= 80 AND Major = "CS";

Thus the AND operator joins two or more conditions, and diaplays only the rows that satisfy both or all conditions involved.

What if you wanted to find out which students are majoring in either Mathematics (MAT) or have a grade below 70 percent? In this case, you need to use the OR logical operator to join the two conditions as follows:-

SELECT FirstName, LastName, Grade, Major
FROM StudentTable WHERE Major = "MAT" OR Grade < 70;

Thus the OR operator joins two or more conditions but displays only the rows that satisfy either of the conditions involved.


The AND operator and the OR operator can also be combined to select data from the database, for example:

SELECT FirstName, LastName
FROM StudentTable
WHERE Gender = 'F' AND Major = "MAT" OR Grade > 80;

First, SQL finds the rows where the Major is MAT and the Gender column is equal to F for female, then checks if the data in any of the rows selected satisfy the previous AND condition or the condition that the Grade is 80 or above.

Notice that the AND operation is done first.

If one wanted to perform the OR operator before the AND operator, use of parentheses would be the best option. For example, in the example above, if one wanted to perform the Or operator before the AND they can do the following:

SELECT Student_ID
FROM StudentTable
WHERE Gender = 'F' AND (Major = "MAT" OR Grade > 80);

 

IN & BETWEEN

To simplify the use of compound or sometimes complicated conditions, use IN or BETWEEN.  

To list all students whose grades fall in a particular range eg between 70 and 80, one would issue the following: 

SELECT FirstName, LastName
FROM StudentTable
WHERE Grade BETWEEN 70 AND 80;

To list all students whose grades are NOT  between 70 and 80, one would issue the following: 

SELECT FirstName, LastName
FROM StudentTable
WHERE Grade NOT BETWEEN 70 AND 80;

To list all students majoring in MAT and CS, one would issue the following: 

SELECT FirstName, LastName
FROM StudentTable
WHERE Major IN ('MAT', 'CS');

To list all students NOT majoring in MAT and CS, one would issue the following: 

SELECT FirstName, LastName
FROM StudentTable
WHERE Major NOT IN ('MAT', 'CS');

 

LIKE

In the student table above, if one wanted to list all students whose last name starts with "Ph" they would issue the following:

SELECT FirstName, LastName
FROM StudentTable
WHERE LastName LIKE 'Ph*');

This will list  Twinko Phiri and Chiza Phiri

To list all students whose last name ends with "s", one would issue the following:

SELECT FirstName, LastName
FROM StudentTable
WHERE LastName LIKE '*s');

This will list Roroyn Davis