Select Statements
To fully use the power of a relational database we need to communicate with it. The ultimate communication would be to turn to our computer and say, in a clear, distinct voice, `Show me all the left-handed, brown- eyed bean counters who have worked for this company for at least 10 years.`A few of us may already be doing so (talking to our computer, not listing bean counters). Everyone else needs a more conventional way of retrieving information from the database.
Overview
We can make this vital link through SQL’s middle name, Query The name Query is really a misnomer in this context. An SQL query is not necessarily a question to the DB.
It can be a command to do one of the following:
Build or delete a table
Insert, modify, or delete rows or fields
Search several tables for specific information and return the results in a specific order
Modify security information
A query can also be a simple question to DB. To use this powerful tool, we need to learn how to write an SQL query.
General Rules of Syntax
As we will find, syntax in SQL is quite flexible, although there are rules to follow as in any programming language. A simple query illustrates the basic syntax of an SQL select statement. Pay close attention to the case, spacing, and logical separation of the components of each query by SQL keywords.
SELECT ENAME, MGR, HIREDATE
FROM EMP
WHERE ENAME = 'SMITH';
In this example everything is capitalized, but it doesn’t have to be. The preceding query would work just as well if it were written like this:
SELECT ename, mgr, hiredate
FROM emp
WHERE ename = ' SMITH ';
Note that SIMTH appears in capital letters in both examples. Although actual SQL statements are not case sensitive, referred to data in DB. For instance, many companies store their data in uppercase.
In preceding example, assume that column ENAME stores its contents in uppercase. Therefore, a query searching for ‘Smith’ in ENAME column wouldn’t find any data to return.
Check our implementation and/or company policies for any case requirements.
Note: Commands in SQL are not case-sensitive.
Take another look at the sample query. Is there something magical in the spacing? Again the answer is no. The following code would work as well:
SELECT ename, mgr, hiredate
FROM emp
WHERE ename = 'SMITH';
However, some regard for spacing and capitalization makes our statements much easier to read. It also makes our statements much easier to maintain when they become a part of our project.
Another important feature of (semicolon) semicolon (;) the sample query is the semicolon at the end of the expression. This punctuation mark tells the command-line SQL program that our query is complete.
If the magic isn’t in the capitalization or the format, then just which elements are important? The answer is keywords, or the words in SQL that are reserved as a part of syntax. (Depending on the SQL statement, a keyword can be either a mandatory element of the statement or optional.) The keywords in the current example are
SELECT
FROM
WHERE
The Building Blocks of DRL
As our experience with SQL grows, we will notice that we are typing the words SELECT and FROM more than any other words in the SQL vocabulary. They aren’t as glamorous as CREATE or as ruthless as DROP, but they are indispensable to any conversation we hope to have with the computer concerning data retrieval.
And isn’t data retrieval the reason that we entered mountains of information into our very expensive database in the first place?
SELECT
This discussion starts with SELECT because most of our statements will also start with SELECT:
Syntax:
SELECT <COLUMN NAMES>
Basic statements like SELECT couldn’t be simpler. However, SELECT does not work alone. If we typed just SELECT into our system, we might get the following response:
SELECT;
SELECT
*
ERROR at line 1:
ORA-00936: missing expression
FROM
The asterisk under the offending line indicates where Oracle thinks the offense occurred. The error message tells we that something is missing. That something is the FROM clause:
Syntax:
FROM <TABLE>
Together, the statements SELECT and FROM begin to unlock the power behind our database. Note: keywords clauses at this point we may be wondering what the difference is between a keyword, a statement, and a clause. SQL keywords refer to individual SQL elements, such as SELECT and FROM.
A clause is a part of an SQL statement; for example, SELECT column1, column2, … is a clause. SQL clauses combine to form a complete SQL statement. For example, we can combine a SELECT clause and a FROM clause to write an SQL statement.
Terminating an SQL Statement
In some implementations of SQL, the semicolon at the end of statement tells the interpreter that we are finished writing the query.
For example, Oracle’s SQL*PLUS won’t execute query until it finds a semicolon (or a slash). On the other hand, some implementations of SQL don’t use the semicolon as a terminator.
For example, Microsoft Query and Borland’s ISQL don’t require a terminator, because our query is typed in an edit box and executed when we push a button.
Expressions
The definition of an expression is simple: An expression returns a value. Expression types are very broad, covering different data types such as String, Numeric, and Boolean. In fact, pretty much anything following a clause (SELECT or FROM, for example) is an expression. In the following example EMPNO is an expression that returns the value contained in the EMPNO column.
SELECT empno FROM emp;
In the following statement ENAME, HIREDATE and SAL is expressions:
SQL> SELECT ENAME, HIREDATE, SAL FROM EMP;
Now, examine the following expression:
WHERE ENAME = 'SMITH'
It contains a condition, NAME = ‘SMITH’, which is an example of a Boolean expression. NAME = ‘SMITH’ will be either TRUE or FALSE, depending on the condition =. 2.9.1. Conditions
If we ever want to find a particular item or group of items in our database, we need one or more conditions. Conditions are contained in the WHERE clause. In the preceding example, the condition is
ENAME = 'SMITH'
For Ex: to find everyone in our organization that worked more than 100 hours last month, our condition would be
NUMBEROFHOURS > 100
Conditions enable us to make selective queries. In their most common form, conditions comprise a variable, a constant, and a comparison operator.
In the first example the variable is ENAME, the constant is ‘SMITH’, and the comparison operator is =. In the second example the variable is NUMBEROFHOURS, the constant is 100, and the comparison operator is >.
We need to know about two more elements before we can write conditional queries: the WHERE clause and operators.
⌖ sql rdbms select