Learn SQL using Oracle Database | Part-1 Of 10

Within A Day, learn SQL using Oracle Database.

Retrieving Data Using the SQL SELECT statement

Capabilities of SQL SELECT Statement

A SELECT statement retrieves information from a database table. With a SELECT statement, you can do the following:

Projection

Select the column in a table that is returned by a query.

Selection

Select the rows in a table that are returned by a query. Various criteria can be used to restrict the rows that are retrieved.

Join

Bring together data that is stored in the different tables by specifying the link between them.

Basic SELECT Statement

 
SELECT * | {Distinct] column | expression [alias],....} 
FROM TABLE;

SELECT identifies the columns to be displayed.
FROM identifies the table containing those columns.

Selecting All Columns

 
Select * from employees;

Selecting Specific Columns

 
Select employee_id, employee_name from employees;

Guidelines for writing SQL statements

SQL statements are not case sensitive.
SQL statements can be entered on one or more lines.
Keywords can not be abbreviated or split across lines.
Clauses are usually placed on separate lines.
Indents are used to enhance readability.
In SQL Developer, ; (Semi-colon ) is optional, but mandatory in SQL *Plus.

Using Arithmetic Operators

 
Select employee_id, salary, salary + 5000 from employees;

Arithmetic Operators Precedence

 
Select employee_id, salary, 12 * salary + 5000 
from employees;

OR

 
Select employee_id, salary, 12 * ( salary + 5000) 
from employees;

Defining a Null value

Null is a value that is unavailable, unassigned, unknown or inapplicable.
Null is not the same as Zero or blank space.

 
Select employee_id, first_name, commission_pct from employees;

Null values on arithmetic expressions

Arithmetic expressions containing a null value evaluate to null.

 
Select employee_id, first_name, 12 * salary * commission_pct 
from employees;

Using Column Alias

 
Select employee_id, first_name as Name, 12 * salary * commission_pct 
from employees;

OR

 
Select employee_id, first_name "First Name", 12 * salary * commission_pct
from employees;

Concatenation Operator

 
Select employee_id, first_name || last_name "Full Name" from employees;

Using Literal Character String

 
Select employee_id, first_name || ' is a ' || job_id as "Employee Details"
from employees;

Remove Duplicate Rows

 
Select distinct department_id
from departments;

Displaying the Table Structure

 
Describe employees

OR

 
Desc employees;

Restricting and Sorting Data

Using the WHERE clause

 

select * from employees where department_id=50;

Character Strings and Dates

 

select *
from employees 
where last_name='Abel';

OR

 

select *
from employees 
where hire_date='17-JAN-2019';

Comparison Operators

Operator Meaning
= Equal to
> Greater Than
>= Greater Than or Equal to
< Less Than
<= Less Than or Equal to
<> Not Equal to
BETWEEN ——– AND ——- Between two values ( inclusive )
In(set) Match any of a list of values
LIKE Match a Character Pattern
IS NULL is a null value

Using Comparison Operators

 
Select * 
from employees
where salary <= 5000;

Range Conditions Using the between Operator

 
Select * 
from employees
where salary between 3000 and 8000;

Membership Condition Using the IN Operator

 
Select * 
from employees
where manager_id IN (100,101,201);

Pattern Matching Using the LIKE Operator

 
Select * 
from employees
where first_name LIKE 's%';

Combining wildcard Characters

 
Select * 
from employees
where first_name LIKE '_o%';

Using the null conditions

 
Select * 
from employees
where manager_id is null;

Defining Conditions Using the Logical Operators

Operator Meaning
AND Returns true if both component conditions are true
OR Returns true if either component conditions aris true
NOT Returns true if the condition is false

Using the AND Operator

 
Select * 
from employees
where salary >= 8000
AND job_id LIKE '%MAN%';

Using the OR Operator

 
Select * 
from employees
where salary >= 8000
OR job_id LIKE '%MAN%';

Using the NOT Operator

 
Select * 
from employees
where job_id not in ('ST_CLERK','IT_PROG');

If there is an issue, please write a comment below. I shall try to answer your issue.

Leave a Reply