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

[code lang=”js”]
SELECT * | {Distinct] column | expression [alias],….}
FROM TABLE;
[/code]

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

Selecting All Columns

[code lang=”js”]
Select * from employees;
[/code]

Selecting Specific Columns

[code lang=”js”]
Select employee_id, employee_name from employees;
[/code]

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

[code lang=”js”]
Select employee_id, salary, salary + 5000 from employees;
[/code]

Arithmetic Operators Precedence

[code lang=”js”]
Select employee_id, salary, 12 * salary + 5000
from employees;
[/code]

OR

[code lang=”js”]
Select employee_id, salary, 12 * ( salary + 5000)
from employees;
[/code]

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.

[code lang=”js”]
Select employee_id, first_name, commission_pct from employees;
[/code]

Null values on arithmetic expressions

Arithmetic expressions containing a null value evaluate to null.

[code lang=”js”]
Select employee_id, first_name, 12 * salary * commission_pct
from employees;
[/code]

Using Column Alias

[code lang=”js”]
Select employee_id, first_name as Name, 12 * salary * commission_pct
from employees;
[/code]

OR

[code lang=”js”]
Select employee_id, first_name "First Name", 12 * salary * commission_pct
from employees;
[/code]

Concatenation Operator

[code lang=”js”]
Select employee_id, first_name || last_name "Full Name" from employees;
[/code]

Using Literal Character String

[code lang=”js”]
Select employee_id, first_name || ‘ is a ‘ || job_id as "Employee Details"
from employees;
[/code]

Remove Duplicate Rows

[code lang=”js”]
Select distinct department_id
from departments;
[/code]

Displaying the Table Structure

[code lang=”js”]
Describe employees
[/code]

OR

[code lang=”js”]
Desc employees;
[/code]

Restricting and Sorting Data

Using the WHERE clause

[code lang=”js”]

select * from employees where department_id=50;

[/code]

Character Strings and Dates

[code lang=”js”]

select *
from employees
where last_name=’Abel’;

[/code]

OR

[code lang=”js”]

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

[/code]

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

[code lang=”js”]
Select *
from employees
where salary <= 5000;
[/code]

Range Conditions Using the between Operator

[code lang=”js”]
Select *
from employees
where salary between 3000 and 8000;
[/code]

Membership Condition Using the IN Operator

[code lang=”js”]
Select *
from employees
where manager_id IN (100,101,201);
[/code]

Pattern Matching Using the LIKE Operator

[code lang=”js”]
Select *
from employees
where first_name LIKE ‘s%’;
[/code]

Combining wildcard Characters

[code lang=”js”]
Select *
from employees
where first_name LIKE ‘_o%’;
[/code]

Using the null conditions

[code lang=”js”]
Select *
from employees
where manager_id is null;
[/code]

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

[code lang=”js”]
Select *
from employees
where salary >= 8000
AND job_id LIKE ‘%MAN%’;
[/code]

Using the OR Operator

[code lang=”js”]
Select *
from employees
where salary >= 8000
OR job_id LIKE ‘%MAN%’;
[/code]

Using the NOT Operator

[code lang=”js”]
Select *
from employees
where job_id not in (‘ST_CLERK’,’IT_PROG’);
[/code]

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

We will be happy to hear your thoughts

Leave a reply

Register New Account
Reset Password