Retrieving Data Using the SQL SELECT Statement Part 1

Retrieving Data Using the SQL SELECT Statement 

·         List the capabilities of SQL SELECT statements

·         Execute a basic SELECT statement

To retrieve information from database, the use of Structured Query Language (SQL) SELECT statement will be necessary. One may need to restrict the columns that will be display. This post will describe all the SQL statements that will be needed for illustrations.

SELECT statements will be use time to time and SQL*PLUS commands is use to execute SQL statements. SQL*PLUS   is a SQL tool that enables you to interact with the database, or is an interactive command line interface (CLI) oracle.

Capabilities of SQL SELECT Statements
A SELECT statement extracts information from the database, SELECT statement can do the following:

Selection: One can use the selection capability in SQL to choose the rows in a table that you want returned by a query. One can use various ways to selectively limit the rows that appear.

Projection: One can use the projection capability in SQL to choose the columns in a table that you want returned by your query. One can choose as few or as many columns of the table as you preferred.

Join: One can use the join capability in SQL to bring together data that is stored in different tables by creating a link (Primary keys and Foreign Keys) through a column that both the tables share.

Basic SELECT Statement

SELECT [DISTINCT] {*, columnName [alias], , , , , , , ,}    FROM table;
•SELECT identifies target columns.
•FROM identifies which table.
SELECT statement must include the following:

A SELECT clause, which specifies the columns to be displayed.
A FROM clause, which specifies the table containing the columns listed in the SELECT clause.
The meaning of syntax above:
1. SELECT:  list of one or more columns.
2. DISTINCT:  removes duplicates.
3. Asterisk (*) it selects all columns
4. column selects the named column.
5. Alias shows selected columns with different headings.
6. FROM table shows the table containing the columns.

A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords.
A clause is a part of an SQL statement. For example, SELECT empid, firstname, jobid   etc … is a clause.
A statement is a combination of two or more clauses. For example,  SELECT * FROM emp, SELECT firstName, empno from employees is a SQL statement.

Writing of SQL Statements

there are rules and guidelines in constructing valid statements that are both readable and editable, such rules and guidelines are as follows:

 • SQL statements are not case sensitive, unless indicated.
• SQL statements can be entered on one or many lines.

• Keywords cannot be split across lines or abbreviated.

 • Clauses are usually placed on separate lines for readability and ease of
editing.

• Keywords typically are entered in uppercase; all other words, such as table names and
columns, are entered in lowercase.
• Within SQL*Plus. a SQL statement is entered at the SQL prompt, and the subsequent lines are numbered. This is called the SQL buffer. Only one statement can be current at any time within the buffer.

Note that in executing SQL statements, Place a semicolon (;) at the end of the last clause. Also, for the purpose of BECOME AN OCA BLOG, we are 

going to limit illustrations to HR SCHEMA only.

SELECTING ALL COLUMNS

Let’s check how many columns does employees table has;

desc employees;

SELECT * FROM employees;

SELECTING ALL COLUMNS AND ROWS

SELECT first_name, last_name, job_id FROM employees;

USING ARITHMETICS OPERATORS

Operator       Description
+               Add
-               Subtract
*              Multiply
/              Divide

SELECT first_name, salary, salary*1.20 FROM employees;

SELECT first_name || last_name AS FULLNAME salary, salary*1.50 AS BONUS FROM employees;

Note: CONCATENATION (||) is use in oracle to merge two columns while ALIAS is use to rename column. The use of ‘AS’ indicates ALIAS.

SELECT first_name || 'is a' || ' ' || job
AS "Employee Details"
FROM employees ;

ELIMINATING DUPLICATES

The use of DISTINCT key word will eliminate duplicate rows in the SELECT clause.

Example without DISTINCT keyword.

SELECT job_id from employees;

Example with DISTINCT keyword

SELECT DISTINCT job_id from employees;

NULL VALUES

Null  signifies that a value is undefined or not known. This is not the same as a zero, a blank, an empty string, or a default value.

SELECT first_name, 12 * salary + comm
FROM employees
WHERE first_name ='KING'

USING PARENTHESIS

SELECT first_name, salary, 12 * (salary+ 100)
FROM employees;

Parentheses  specify the order in which operators are executed, Parentheses are used to force prioritized evaluation and to clarify statements. For example in arithmetic operator without parenthesis,

 SELECT first_name, 12 * salary + 100
FROM employees;

But with parenthesis, the result is different in the diagram above.