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:
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:
•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.
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.
• 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.
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.
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
+ 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 ;
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'
FROM employees
WHERE first_name ='KING'
USING PARENTHESIS
SELECT first_name, salary, 12 * (salary+ 100)
FROM employees;
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;
FROM employees;
But with parenthesis, the result is different in the diagram
above.
