Introduction to SQL

Stone Apple Solutions Pte Ltd

4/15/2018 1
Agenda
Day 1
• SQL Introduction with RDBMS concept
• SQL Data Selection
• SQL Restriction and Sorting Data
• SQL Functions
• SQL Joining Tables
Day 2
• SQL Sub-query
• SQL Grouping Functions
• SQL Data Manipulation Language
• SQL Index
• SQL Object
SQL Introduction with RDBMS concept
• Relational Database
Terminology

For example you might want to store information about all the employees in your
company, in a relational database. You could create several tables to store different
pieces of information about your employees.
• Relational Database
Terminology

Guideline for keys

• No duplicate values are allowed in a primary
key
• Primary keys generally cannot be changed
• Foreign keys are based on data values
• A foreign key value must match an exhibiting
primary key value
One To One Relationship
For testing, we have two tables on scott schema, emp and dept:
One To One Relationship
Now we have a relationship between the Dept table and the Emp table. If
each dept can belong to only one emp, this relationship is "One to One“.
Notice that now there is a field named “deptno" in the Emp table, that refers
to the matching record in the Dept table. This is called a "Foreign Key" and it
is used for all kinds of database relationships.

Example:
One To Many Relationship

This is the most commonly used type of relationship.

In these cases we would need to create "One to Many"
relationships.
Here is an example :
One To Many Relationship

Each Emp may have zero, one or multiple orders. But an order can belong to
only one Emp.
Many To Many Relationship

The Items_Orders table has only one purpose, and that is to create a "Many
to Many" relationship between the items and the orders.
Many To Many Relationship

Example:

Each order contains multiple item and each item

contain multiple order.
Many To Many Relationship

In some cases, you may need multiple instances on

both sides of the relationship. For example, each order
can contain multiple items. And each item can also be
in multiple orders.
For these relationships, we need to create an extra
table:
SQL Data Selection
Database Connection

• Connection Setting $ORACLE_HOME/network/admin/TNSNAMES.ora

Information :
<name of connection string > =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=<database host
name>)(PORT=<database_port>))
(CONNECT_DATA=
(SID=<sid instance database>)
)
)
• Connect to database: For testing, use scott as user name and tiger as
password.
Database Connection

• Connect to database: For testing, use scott as user name and tiger as
password.

Connection String
SQL Syntax
SQL Syntax is easy and most of the database action you can done
using SQL statement. Following are fewer most frequently useful
SQL Query syntax,
SQL Syntax
SQL Syntax
SQL Syntax
SQL Syntax
SQL Syntax
SQL Syntax
SQL Syntax
SQL Syntax
SQL Syntax
SQL Syntax
SQL Syntax
Exercise
• 1.

• 2
Exercise
• 3
SQL Restricting and Sorting
Restricting Data
Restricting Data
SQL WHERE Clause Statement

SQL WHERE clause is basically use for fetching specific criteria

matched data only return. SQL WHERE clause is optionally clause
in DML statement.
SQL WHERE clause we can use with SELECT, UPDATE, DELETE
statements. SELECT statement with WHERE clause execute,
fetching all table rows and apply WHERE clause filtering and
finally return the result data.

WHERE clause use with SELECT Statement

When you get table data with specific filtered data, you should
use where clause.
SQL WHERE Clause Statement

Syntax
Considering following syntax that help you to understanding WHERE clause.
SELECT * FROM table_name WHERE condition;

Example
SELECT * FROM items WHERE item_name = ‘Laptop‘;
SQL WHERE Clause Statement

WHERE clause Operators

You can use WHERE clause with following operators.
< Less than
>= Greater than or equal
<= Less than or equal
Between Fetching within range data
Like Search for a pattern
In Allow only specified values
Not In Not allow specified values
IS Null/ IS not Null
SQL WHERE Clause Statement

Exercise:
Create sql for deptno equal to 20 on emp table.
The output should shows like below:
SQL IN
SQL IN condition used to allow multiple value in a WHERE clause
condition. SQL IN condition you can use when you need to use
multiple OR condition.
SQL IN condition allow only specific value in INSERT, UPDATE,
DELETE, SELECT statement.

Syntax
WHERE column_name IN (value1, value2, ...);

Example
SELECT * FROM items WHERE item_id IN (100,101);
SQL IN
Exercise:
Create sql to shows only deptno ‘10’ and ‘20’ on
emp table.
The output should shows like below:
SQL Not IN
SQL NOT IN condition used to exclude the defined multiple value in a
WHERE clause condition. SQL NOT IN condition also identify by NOT
operator.

Syntax
NOT IN condition use with WHERE clause to exclude defined multiple
values from record data.
WHERE column_name NOT IN (value1, value2, ...);

SQL NOT condition used with BETWEEN condition or LIKE condition.

NOT BETWEEN condition;
NOT LIKE condition;
SQL Not IN
WHERE clause with NOT IN Example
SELECT * FROM items WHERE item_id NOT IN (100,101);

BETWEEN with NOT condition Example

Select all rows with add condition exclude no column range
from 6 to 10.
SELECT * FROM items WHERE item_id NOT BETWEEN 100
AND 101;

LIKE with NOT condition Example

select whose name column starting with 'Be' character.
SELECT * FROM items WHERE item_name NOT LIKE ‘La%';
SQL Not IN
Exercise:
Create sql to exclude deptno ‘20’ and ‘30’ on
emp table.
The output should shows like below:
SQL Between
SQL BETWEEN clause used for fetching within range data.
SQL BETWEEN is simply a shorthand way of expressing an
inclusive range comparison.
SQL Between clause support only range type value like
number, dates, character. But not supporting boolean,
string value range.

Syntax
Considering following syntax that help you to
understanding BETWEEN clause,
SELECT * FROM table_name WHERE column_name
BETWEEN lower_bound_value AND upper_bound_value;
SQL Between
When SQL query with BETWEEN clause parse into SQL Buffer,
it will automatically expand out into separate comparison
clauses.
SELECT * FROM table_name
WHERE column_name <= lower_bound_value AND
column_name >= upper_bound_value;

Example
In this example select all items table rows with range between
no column value 101 to 103.
SELECT * FROM items WHERE item_id BETWEEN 101 AND
103;
SQL Between
Exercise:
Create sql to shows deptno between ’10’ and
‘20’ on emp table.
The output should shows like below:
SQL Like
Null Condition
Logical Condition
Logical Condition
Logical Condition
Sorting
Sorting
Exercise
• 1

• 2
Exercise
• 3
SQL Functions
SQL Functions
Oracle SQL provide built-in SQL functions. SQL Functions
take some values as a arguments, perform some function
logic and returning some values. SQL built-in function are
so many that all are help us for no need to implementing
your own logic.

Oracle SQL built-in functions are following,

• SQL Numeric Functions
• SQL String Functions
• SQL Date Function
SQL Functions
SQL Functions
SQL Functions
SQL Functions
SQL Functions
SQL String Functions
SQL String function take some value and return string value. Function return
data type VARCHAR2 if parameter argument CHAR or VARCHAR2 data type.
Following are some SQL String function.

Function Function Parameter Description

INSTR(
Function return sub string position from the
INSTR original_string,
original string.
sub_string )
LENGTH LENGTH(string ) Function return the length of string.
Function return lowercase character in every
LOWER LOWER( char/string )
word.
LTRIM( string, Function remove all specified trim char from
LTRIM
trim_char ) left side of the string.
SQL String Functions
Function Function Description
Parameter
REPLACE( string,
Function return the string/char of every matched
REPLACE match_string,
string replace with new string.
replace_string )
RTRIM( string, Function remove all specified trim char from right
RTRIM
trim_char ) side of the string.
SUBSTR( string,
Function return a selected string character from a
SUBSTR start_position,
full string.
substr_length )
TRANSLATE( string,
Function return the string/char of every matched
TRANSLATE match_char,
character replace with new character.
replace_char )
Function remove all specified trim char from
TRIM TRIM( string )
beginning and ending of the string.
SQL String Functions
Function Function Parameter Description
Function return uppercase character in every
UPPER UPPER( char/string )
word.
CONCAT( string1, Function concatenated string1 with string2 and
CONCAT
string2 ) return to a concatenated string.
Function return capitalize string/char (capitalize
INITCAP INITCAP( char/string )
first letter of each word).
Function return the ASCII value of the given
CHR CHR( number )
argument value.

Example:
Select upper(ename) from emp;
Exercise
1. Create sql to change dept name ‘Accounting’
to ‘Oracle Developer’ on dept table.
The output should be like below:
select REPLACE (dname, 'ACCOUNTING', 'Oracle Developer') DNAME
from dept

Bila gk tau tablenya bisa tebak pake dual di from

SQL Numeric Functions
SQL numeric function take numeric value (maximum 38 digit) and return
numeric value (maximum 38 digit). SQL numeric function return single row
single column values. Following are some SQL numeric function.
Function Function Parameter Description
ABS ABS(n) Function return absolute value of n
values.
Function return exact log value (natural
LN LN(n)
logarithm value).
SQL Numeric Functions
Function Function Parameter Description

Function return the reminder value of n1

MOD MOD(n1,n2)
divide by n2. Where n1 and n2 is natural value.
ROUND(n, Function return the round number of specified
ROUND
decimal_number) nth number of decimal place.
Function generate random number between 0
to 1, but no longer support. Optionally use
RAND RAND()
Rownum to fetch random record from
database table.
Function return largest integer round value
FLOOR FLOOR(n) that is equal to or less then to a parameter
value (n).
SQL Numeric Functions
Function Function Parameter Description
Function return smallest integer round value
CEIL CEIL(n) that is greater then or equal to a parameter
value (n).
Function return the square root of the n
SQRT SQRT(n)
number. SQRT function return the real number.
TRUNC(n, Function return the truncated number of
TRUNC
decimal_number) specified nth number of decimal place.

Example:
Select avg(sal) from emp;
SQL Numeric Functions

Exercise:
Create sql to calculate all amount on orders
table.
The output should be like below:
SQL Date Functions
SQL Date Functions
SQL Date Functions
SQL Date Functions
General Functions
NVL Function
NVL2 Function
NVLLIF Function
Coalesce Function
Conditional Expressions
Case Expression
Decode Functions
Nesting Functions
Data Type Conversion
Exercise
• 1
• 2

• 3
Exercise
• 4
SQL Joining Tables
SQL Joining Tables
Type of Join

Kemungkinan untuk one to many lebih besar, jadi join yang gk terlihat
secara langsung joinnya
Equijoin
Equijoin
Non - Equijoin
Non - Equijoin
Exercise
Exercise:
Create sql Equi join on emp table and dept table.
The output should shows like below:
SQL Outer Join
Outer Join is a join two table involving common
attributes from two tables. But tables (Table A)
does not require to have a matching value to
other table (Table B).
SQL Left Join (Left Outer Join) always contains all
records of left table (Table A) even of join
condition does not find any matching record in
right table (Table B).
SQL Outer Join
SQL Outer Join
Example Statement :
SELECT * FROM emp
LEFT OUTER JOIN dept
ON emp.deptno = dept.deptno;

Following is alternative syntax result produce same as

above,

Example Statement :
SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno(+);
SQL Outer Join
SQL Outer Join
Exercise:
Create sql outer join to show all data on emp table and relate it to the dept
table.
The output should be like below:

a) b)
SQL Self Join (sama dgn outer join
hanya ke tbl yang sama)
SQL Self join joining same table to itself. SQL Self
join possible only when table stored records are
in hierarchical relationship between them.
You must use table alias name otherwise you
can't distinguish columns name (referenced of
which table's).
Logically assume that same table have two
different copies but not actually different copies.
SQL Self Join
SQL self join example statement :
SELECT A.order_number, A.order_date, B.order_number, B.order_date,
B.amount
FROM orders A, orders B
WHERE A.order_number = B.order_number;

SQL self join example explanation :

Above example we are use A and B table alias name.
Inner Join to join predicate condition (A.order_number = B.order_number)
order_number only those whose are in same order_number in Table A or
Table B. It's mean cross order_number are eliminate.
Exercise
Create sql self join on emp table.
The output should be like below:
Exercise
SQL Sub Query
SQL Sub Query
SQL Sub Query
A Sub query or Inner query or Nested query is a
query within another SQL query and embedded
within the WHERE clause.
A sub query is used to return data that will be used
in the main query as a condition to further restrict
the data to be retrieved.
Sub queries can be used with the SELECT, INSERT,
UPDATE, and DELETE statements along with the
operators like =, <, >, >=, <=, IN, BETWEEN etc.
Sub Queries with the SELECT Statement

Sub queries are most frequently used with the SELECT

statement. The basic syntax is as follows:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ] [WHERE])
Sub Queries with the SELECT Statement

Now, let us check following sub query with SELECT statement:

SELECT * FROM EMP

WHERE EMPNO IN (SELECT EMPNO FROM EMP WHERE SAL >
1000) ;
Exercise
1. Create sub query to show orders which is belong to employee who is dept
is ‘Research’.
Exercise
• 2
SQL GROUP Functions
SQL GROUP Functions
SQL GROUP Functions

Group functions are built-in SQL functions that operate on

groups of rows and return one value for the entire group. These
functions are: COUNT, MAX, MIN, AVG, SUM, STDEV, VARIANCE
SQL Count()
This function returns the number of rows in the table that satisfies the
condition specified in the WHERE condition. If the WHERE condition is
not specified, then the query returns the total number of rows in the
table.

Example :
If you want the number of employees in a particular department, the
query would be:
Select count(*)
From emp
Where deptno = 20;

The output would be ‘5' rows.

SQL MAX()
This function is used to get the maximum value
from a column.
To get the maximum salary drawn by an
employee, the query would be:

SELECT MAX (sal) FROM emp;

SQL MIN()
This function is used to get the minimum value
from a column.
To get the minimum salary drawn by an
employee, he query would be:

SELECT MIN (sal) FROM emp;

SQL AVG()
This function is used to get the average value of
a numeric column.
To get the average salary, the query would be

SELECT AVG (sal) FROM emp;

SQL SUM()
This function is used to get the sum of a numeric
column.
To get the total salary given out to the
employees,

SELECT SUM (sal) FROM emp;

SQL GROUP Functions

Exercise:
Create sql to calculate all amount on orders
table with employee’s dept is ‘Accounting’.
The output should be like below:
SQL GROUP Functions
SQL GROUP Functions
SQL GROUP Functions
Exercise

• 1. Display the following data :

Exercise

• 2
SQL Data Manipulation Language
SQL Data Manipulation Language
SQL Data Manipulation Language
SQL INSERT

SQL INSERT statement is used to inserting new

records into database table. You can insert new
record following different way,

• INSERT INTO Statement

• INSERT ALL Statement
• Multiple Row INSERT into TABLE Statement
• INSERT Data only in specified COLUMNS
• INSERT INTO SELECT Statement
INSERT INTO statement

Using INSERT INTO statement to insert record into database.

Syntax
When inserting data into table no need to specify the column
names if value is table structure wise (column wise).
INSERT INTO table_name VALUES (value1, value2, value3, ...);

Example
INSERT INTO items VALUES (106, ‘Helm’, ‘AGV‘,);
INSERT INTO statement

Syntax
When you inserting data into table and you haven't know
table structure you must specify the column name.
INSERT INTO table_name [ (column_name1,
column_name2, ...) ]
VALUES (value1, value2, ...);

Example
INSERT INTO items (item_id, item_name, description)
VALUES (107, ‘Trouser', ‘LEA');
INSERT ALL statement
Using INSERT ALL statement to insert more then one records into
table.

Syntax
Now we insert more then one record insert in single SQL INSERT
statement.
INSERT ALL
INTO table_name [ (column_name1, column_name2, ...) ] VALUES
(record1_value1, record1_value2, ...)
INTO table_name [ (column_name1, column_name2, ...) ] VALUES
(record2_value1, record2_value2, ...)
INTO table_name [ (column_name1, column_name2, ...) ] VALUES
(record3_value1, record3_value2, ...) ....
INSERT ALL statement
Example
INSERT ALL
INTO items (item_id, item_name) VALUES (108,
‘Chair')
INTO items (item_id, item_name) VALUES (109,
‘Glass')
INTO items (item_id, item_name) VALUES (110,
‘Umbrella')
INTO items (item_id, item_name) VALUES (111,
‘Cupboard');
INSERT INTO SELECT Statement

INSERT INTO SELECT Statement to insert data that data you are getting from
another table.

Syntax:
INSERT INTO new_table_name [(column_name1,column_name2,...)]
SELECT column_name1, column_name1 ... FROM another_table_name
[WHERE condition];

Example Statement:
CREATE TABLE demo_tbl( item_id NUMBER(4), item_name VARCHAR2(30),
description varchar2(100) );

INSERT INTO demo_tbl (no, name) SELECT item_id, item_name, description

FROM items;
SQL UPDATE Statement

SQL UPDATE statement to update table records with in

database. You can update all table row or update data only
matching conditionally WHERE clause.

Syntax :
UPDATE table_name
SET column_name1 = value1, column_name2 = value2, ...;

Example Statement :
UPDATE orders SET amount = 5000;
UPDATE Conditionally use WHERE Clause

UPDATE table data only where WHERE Clause condition match. User
can optionally use LIMIT number to update only limited record starting
from first.

Syntax :
UPDATE table_name
SET column_name1 = value1, column_name2 = value2, ...
[ WHERE condition ] [ LIMIT number];

Example Statement :
UPDATE orders
SET amount = 4000
WHERE order_number = ‘B1234’;
UPDATE with Sub Queries Statement

The sub query can be used in conjunction with the UPDATE statement.
Either single or multiple columns in a table can be updated when using
a sub query with the UPDATE statement.

The basic syntax is as follows:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME FROM TABLE_NAME)
[ WHERE) ]

Example:
Assuming, we have ORDERS_BKP table available which is backup of
ORDERS table.
UPDATE with Sub Queries Statement

Following example updates SALARY by 0.25 times in ORDERS

table for all the customers whose ID is greater than 1:

UPDATE ORDERS
SET AMOUNT = AMOUNT * 0.25
WHERE ORDER_NUMBER IN (SELECT ORDER_NUMBER FROM
ORDERS_BKP WHERE ORDER_NUMBER = ‘A1234’ );
SQL DELETE Statement

SQL DELETE Statement is used to delete one or more then one row
removed from table.
SQL DELETE Statement use following two way,
• Remove all TABLE rows
• Remove only specific TABLE row

Syntax :
DELETE FROM table_name
[ WHERE condition ] [ LIMIT number ];

Example Statement :
DELETE FROM items WHERE item_id = 100;
Remove all TABLE rows

Remove the all table row use this simple delete

statement to delete all table data. after execute
delete statement SELECT statement to check table is
empty or not.

Syntax :
DELETE FROM table_name;

Example Statement :
DELETE FROM items;
DELETE with Sub Queries Statement

The sub query can be used in conjunction with the DELETE statement like
with any other statements mentioned above.
The basic syntax is as follows:
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME FROM TABLE_NAME)
[ WHERE) ]

Example:
Assuming, we have ORDERS_BKP table available which is backup of ORDERS
table.
Following example delete records from ORDERS table for all orders whose
amount is greater to 1000:
DELETE with Sub Queries Statement

DELETE FROM ORDERS

WHERE ORDER_NUMBER IN (SELECT ORDER_NUMBER FROM
ORDERS_BKP
WHERE AMOUNT > 1000 );
Exercise:
Create sql to insert with sub query into orders table with existing
employee name ‘Miller’, The order date is plus one, and the
amount by 0.25 times.
The output should be like below:
SQL INDEX
SQL INDEX are used to quickly find data without searching every rows in a database
table.
SQL INDEX is improve the speed of search operation on a database table. But
additional you need more storage space to maintain duplicate copy of the database.
INDEX is a copy of the selected column of the database table to store additionally
duplicate copy of the data.
End users does not know for indexes is created on table, only they are searching data
more quickly and efficiently.

SQL INDEX Types

CREATE INDEX statement to create indexes on a table. following 3 type indexes you are
create on a table.
• Simple INDEX : Create INDEX on one column.
• Composite INDEX : Create INDEX on multiple columns.
• Unique INDEX : Create INDEX on column for restrict duplicate values on INDEX
column. (bila single column bisa multiple)
Simple INDEX
Simple INDEX create only one selected column of the database table.

Syntax :
CREATE INDEX index_name
ON table_name (column_name)
[ storage_setting ];

Storage setting specifies the table space explicitly. This are the optional storage setting
if you are not specifies automatically default storage setting used.

Example Statement :
CREATE INDEX index_item_ids ON items (item_id);

We are creating simple index on name column of the items table. In this column allow
duplicate values of the column.
Composite INDEX

Composite INDEX create on multiple selected column of the database table.

Syntax :
CREATE INDEX index_name
ON table_name (column_name, column_name)
[ storage_setting ];

Example Statement :
CREATE INDEX index_items
ON items (item_id, item_name);

We are creating composite index on item_id, item_name column of the items

table. Duplicate values are allowing for creating indexes.
Unique INDEX

Unique INDEX create on selected column of the database table and

does not allow duplicate values of that indexes column.

Syntax :
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
[ storage_setting ];

Example Statement :
CREATE UNIQUE INDEX index_item_id ON items (item_id);

We are create unique index on item_id column of the items table.

Duplicate name value are does not allow again for creating indexes.
SQL Database Object
SQL Database Object

Bs dipanggil lagi kemudian

Biar unik saat generate

Fungsi view: Selanjutnya tinggal panggil EMP_V untuk fungsi yang sama
Create or Replace EMP_V as
Select ……
SQL Create/Drop Table
SQL CREATE DATABASE Statement
SQL CREATE DATABASE statement to create new database. Oracle SQL
you have to first create new database. Before you learn how to create
new database you must have to know about SQL structure.
SQL structure is hierarchically, You first create database. Select
database for USE. Now you are in selected database are. you are
access all table as well as make new table and so many things you can
done.

Syntax
You have to create new database use following SQL CREATE DATABASE
syntax,

CREATE DATABASE database_name;

SQL Create/Drop Table
SQL Create/Drop Table
SQL Create/Drop Table
SQL Create/Drop Table
SQL Create/Drop Table
SQL Create/Drop Table
SQL Create/Drop Table

Truncate untuk mengkosongkan table, beda dengan delete adalah delete dapat
di rollback dan di commit, tetapi di truncate langsung hilang tidak disimpan lagi
Exercise

• Create The following table emp_new with the

folowing structure :
Exercise

Create a new table ‘EMPLOYEES _bkp’ with the same structure

with items table including all the data in items table.
If you query the EMPLOYEES_bkp the output should be like
EMPLOYEES table
SQL Sequence
SQL Sequence
SQL Sequence
SQL Sequence
Exercise
SQL View
SQL View
SQL View
Exercise

• Create View using table Employee,

Department , Jobs To display the following
column :
– FIRST_NAME,
– LAST_NAME,
– DEPARTMENT_NAME,
– JOB_TITLE,
– MANAGER_NAME
SQL Synonyms
SQL Synonyms