Module 2: SQL Fundamentals in Oracle Database
2.1 Introduction to SQL in Oracle
• SQL (Structured Query Language) is used to interact with Oracle Database.
SQL (Structured Query Language) is the standard language for
communicating with relational databases like Oracle.
SQL is case-insensitive (but string values are case-sensitive).
SQL is case-insensitive (you can write SELECT or select), but string values are
case-sensitive ('John' is different from 'john').
• SQL commands can be categorized into:
- DDL → CREATE, ALTER, DROP
- DML → INSERT, UPDATE, DELETE
- DQL → SELECT
- TCL → COMMIT, ROLLBACK
- DCL → GRANT, REVOKE
DDL (Data Definition Language): Defines the structure of the database.
(Examples: CREATE, ALTER, DROP)
DML (Data Manipulation Language): Manages data inside tables.
(Examples: INSERT, UPDATE, DELETE)
DQL (Data Query Language): Retrieves data. (Example: SELECT)
TCL (Transaction Control Language): Manages changes to data as one
unit of work. (Examples: COMMIT, ROLLBACK, SAVEPOINT)
DCL (Data Control Language): Controls permissions. (Examples:
GRANT, REVOKE)
2.2 Creating Tables (DDL)
Tables are created using the CREATE TABLE command.
A table is like a spreadsheet with rows (records) and columns (fields).
To create a table, we use CREATE TABLE.
Example:
CREATE TABLE employees (
emp_id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(30),
last_name VARCHAR2(30) NOT NULL,
job_id VARCHAR2(20),
salary NUMBER(8,2),
hire_date DATE DEFAULT SYSDATE
);
Constraints:
constraints are rules applied to the data within tables to ensure data integrity, consistency, and accuracy.
- PRIMARY KEY → uniquely identifies rows.
- NOT NULL → value is required.
- DEFAULT → sets a default value.
2.3 Inserting Data (DML)
Use the INSERT statement to add data into a table.
Example:
INSERT INTO employees (emp_id, first_name, last_name, job_id,
salary, hire_date)
VALUES (1001, 'John', 'Smith', 'IT_PROG', 5000, SYSDATE);
2.4 Retrieving Data (DQL - SELECT)
The SELECT statement is used to query data.
You can choose which columns to display.
Example:
SELECT first_name, last_name, salary
FROM employees;
2.5 Filtering Data (WHERE Clause)
To display only rows that meet certain conditions, use WHERE.
Examples of filtering:
SELECT first_name, salary
FROM employees
WHERE salary > 4000;
2.6 Sorting Data (ORDER BY)
To arrange results, use ORDER BY.
By default, it sorts ascending (ASC).
To sort from highest to lowest, use DESC.
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;
2.7 Updating and Deleting Data (DML)
UPDATE changes existing values.
Update Example:
UPDATE employees
SET salary = salary + 500
WHERE job_id = 'IT_PROG';
DELETE removes rows.
Delete Example:
DELETE FROM employees
WHERE emp_id = 1002;
2.8 Built-in Functions
Examples:
EMP_ID FIRST_NAME LAST_NAME JOB_ID SALARY HIRE_DATE
1001 John Smith IT_PROG 5000 15-JAN-23
1002 Maria Gonzalez HR_REP 4567 20-MAR-23
1003 David Lee IT_PROG 7891 05-NOV-22
1004 Anna Brown SA_REP 1234 10-JUL-24
SELECT UPPER(first_name), SUBSTR(last_name, 1, 3)
FROM employees;
Output:
UPPER(FIRST_NAME) SUBSTR(LAST_NAME,1,3)
JOHN SMI
MARIA GON
DAVID LEE
ANNA BRO
UPPER(first_name) → makes names all uppercase.
SUBSTR(last_name,1,3) → takes the first 3 letters of the last name.
SELECT ROUND(salary, -2), TRUNC(salary, -2)
FROM employees;
SALARY ROUNDED TRUNCATED
4567 4600 4500
7891 7900 7800
1234 1200 1200
ROUND(number, -2) → rounds to the nearest hundred.
Example: 4567 → 4600
TRUNC(number, -2) → chops off the last two digits, no rounding.
Example: 4567 → 4500
SELECT hire_date, ADD_MONTHS(hire_date, 6)
FROM employees;
HIRE_DATE ADD_MONTHS(HIRE_DATE,6)
15-JAN-23 15-JUL-23
20-MAR-23 20-SEP-23
05-NOV-22 05-MAY-23
10-JUL-24 10-JAN-25
ADD_MONTHS(hire_date,6) → simply adds 6 months to the employee’s hire date.
UPPER makes text uppercase, SUBSTR extracts a substring.
ROUND rounds to nearest value, TRUNC cuts without rounding.
ADD_MONTHS moves the date forward by 6 months.
2.9 Transactions (TCL)
A transaction is a set of SQL commands that work as one unit.
Oracle lets you commit changes or roll them back.
Transaction control in SQL is about making sure the database stays
consistent and correct even when multiple changes happen.
Example:
UPDATE employees SET salary = 7000 WHERE emp_id = 1001;
SAVEPOINT before_delete;
DELETE FROM employees WHERE emp_id = 1004;
ROLLBACK TO before_delete;
COMMIT;
Update John’s salary.
Create a savepoint (checkpoint).
Delete Anna.
Rollback to undo the delete (Anna returns).
Commit saves the final result.
Sample Data: EMPLOYEES Table
EMP_ID FIRST_NAME LAST_NAME JOB_ID SALARY HIRE_DATE
1001 John Smith IT_PROG 5000 15-JAN-23
1002 Maria Gonzalez HR_REP 4500 20-MAR-23
1003 David Lee IT_PROG 6000 05-NOV-22
1004 Anna Brown SA_REP 3000 10-JUL-24
2.4 Retrieving Data (DQL - SELECT)
Query:
SELECT first_name, last_name, salary FROM employees;
Output:
FIRST_NAME LAST_NAME SALARY
John Smith 5000
Maria Gonzalez 4500
David Lee 6000
Anna Brown 3000
2.5 Filtering Data (WHERE Clause)
Query:
SELECT first_name, salary FROM employees WHERE salary > 4000;
Output:
FIRST_NAME SALARY
John 5000
Maria 4500
David 6000
2.6 Sorting Data (ORDER BY)
Query:
SELECT first_name, salary FROM employees ORDER BY salary DESC;
Output:
FIRST_NAME SALARY
David 6000
John 5000
Maria 4500
Anna 3000
2.7 Updating Data (DML)
Query:
UPDATE employees SET salary = salary + 500 WHERE job_id = 'IT_PROG';
Updated Table:
EMP_ID FIRST_NAME LAST_NAME JOB_ID SALARY HIRE_DATE
1001 John Smith IT_PROG 5500 15-JAN-23
1002 Maria Gonzalez HR_REP 4500 20-MAR-23
1003 David Lee IT_PROG 6500 05-NOV-22
1004 Anna Brown SA_REP 3000 10-JUL-24
2.7 Deleting Data (DML)
Query:
DELETE FROM employees WHERE emp_id = 1002;
Table after delete:
EMP_ID FIRST_NAME LAST_NAME JOB_ID SALARY HIRE_DATE
1001 John Smith IT_PROG 5500 15-JAN-23
1003 David Lee IT_PROG 6500 05-NOV-22
1004 Anna Brown SA_REP 3000 10-JUL-24
