SELECT Statement - IX

The SELECT statement is used to query the database and retrieve selected data that match the criteria that you specify.

The SELECT statement has five main clauses to choose from, although, FROM is the only required clause. Each of the clauses have a vast selection of options, parameters, etc. The clauses will be listed below, but each of them will be covered in more detail later in the tutorial.

Here is the format of the SELECT statement:

 
SELECT [ALL | DISTINCT] column1[,column2]
 
FROM table1[,table2]
 
[WHERE "conditions"]
 
[GROUP BY "column-list"]
 
[HAVING "conditions]
 
[ORDER BY "column-list" [ASC | DESC] ]

FROM & WHERE clause

SELECT first_column_name, second_column_name

FROM table_name
WHERE first_column_name > 1000;

*The column names that follow the SELECT keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a * to select all columns. The order they are specified will be the order that they are returned in your query results.

*The table name that follows the keyword FROM specifies the table that will be queried to retrieve the desired results.

*The WHERE clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where.

Example:

 
SELECT name, age, salary
 
FROM employee
 
WHERE age > 50;

The above statement will select all of the values in the name, age, and salary columns from the employee table whose age is greater than 50.

Note: Remember to put a semicolon at the end of your SQL statements. The ; indicates that your SQL statment is complete and is ready to be interpreted.

Comparison Operators

=	Equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<> or !=	Not equal to
LIKE	String comparison test

Note about LIKE

SELECT first_column_name, second_column_name
FROM table_name
WHERE first_column_name LIKE 'Mc%';

The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful character string comparison operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example:

select first, last, city
from empinfo
where first LIKE 'Mc%';

This SQL statement will match any first names that start with 'Mc'. Strings must be in single quotes.

 
Example:

SELECT name, title, dept FROM employee WHERE title LIKE 'Pro%';

The above statement will select all of the rows/values in the name, title, and dept columns from the employee table whose title starts with 'Pro'. This may return job titles including Programmer or Pro-wrestler.

ALL and DISTINCT are keywords used to select either ALL (default) or the "distinct" or unique records in your query results. If you would like to retrieve just the unique records in specified columns, you can use the "DISTINCT" keyword. DISTINCT will discard the duplicate records for the columns you specified after the "SELECT" statement: For example:

 
SELECT DISTINCT age 
 
FROM employee_info;

This statement will return all of the unique ages in the employee_info table.

ALL will display "all" of the specified columns including all of the duplicates. The ALL keyword is the default if nothing is specified.