#

Command

Syntax

Example

Description

1

SELECT

SELECT column1,

column2 FROM

table_name;

SELECT name, age FROM

employees;

Retrieve data from

table.

2

SELECT

ALL

SELECT * FROM

table_name;

SELECT * FROM

employees;

Retrieve all

columns.

3

DISTINCT

SELECT DISTINCT

column FROM table;

SELECT DISTINCT

department_id FROM

employees;

Remove

duplicates.

4

WHERE

SELECT columns

FROM table WHERE

condition;

SELECT * FROM

employees WHERE salary

> 5000;

Filter rows by

condition.

5

AND / OR

`WHERE condition1

AND

OR condition2`

SELECT * FROM

employees

WHERE

salary>5000 AND

department_id=10;

6

ORDER

BY

`SELECT columns

FROM table ORDER

BY column ASC

DESC;`

SELECT * FROM

employees

ORDER BY salary

DESC;

7

ROWNUM

SELECT * FROM

table WHERE

ROWNUM <= n;

SELECT * FROM

employees WHERE

ROWNUM <= 5;

Limit number of

rows.

8

FETCH

FIRST

SELECT * FROM

table ORDER BY col

FETCH FIRST n

ROWS ONLY;

SELECT * FROM

employees ORDER BY

salary DESC FETCH

FIRST 5 ROWS ONLY;

Limit rows (Oracle

12c+).

9

OFFSET

SELECT * FROM

table ORDER BY col

OFFSET n ROWS

FETCH NEXT m

ROWS ONLY;

SELECT * FROM

employees ORDER BY

salary DESC OFFSET 5

ROWS FETCH NEXT 5

ROWS ONLY;

Skip rows.

10

ALIAS

SELECT column AS

alias FROM table;

SELECT name AS

emp_name FROM

employees;

Rename columns

in result.

2. Aggregate Functions

#

Command

Syntax

Example

Description

11

COUNT

SELECT

COUNT(column) FROM

table;

SELECT COUNT(*) FROM

employees;

Count rows.

12

SUM

SELECT SUM(column)

FROM table;

SELECT SUM(salary)

FROM employees;

Sum

numeric

values.

13

AVG

SELECT AVG(column)

FROM table;

SELECT AVG(salary)

FROM employees;

Average of

column.

14

MIN

SELECT MIN(column)

FROM table;

SELECT MIN(salary) FROM

employees;

Minimum

value.

15

MAX

SELECT MAX(column)

FROM table;

SELECT MAX(salary)

FROM employees;

Maximum

value.

16

GROUP BY

SELECT column,

AGG_FUNC(column)

FROM table GROUP BY

column;

SELECT department_id,

COUNT(*) FROM

employees GROUP BY

department_id;

Group rows

for

aggregation.

17

HAVING

SELECT column,

AGG_FUNC(column)

FROM table GROUP BY

column HAVING

condition;

SELECT department_id,

COUNT(*) FROM

employees GROUP BY

department_id HAVING

COUNT(*)>5;

Filter

aggregated

rows.

18

ROLLUP

SELECT col1, SUM(col2)

FROM table GROUP BY

ROLLUP(col1);

SELECT department_id,

SUM(salary) FROM

employees GROUP BY

ROLLUP(department_id);

Subtotals

and grand

totals.

19

CUBE

SELECT col1, col2,

SUM(col3) FROM table

GROUP BY

CUBE(col1,col2);

SELECT department_id,

job_id, SUM(salary) FROM

employees GROUP BY

CUBE(department_id,

job_id);

Aggregate

multiple

dimensions.

20

GROUPING

SETS

SELECT col1, col2,

SUM(col3) FROM table

GROUP BY GROUPING

SETS((col1),(col2),

(col1,col2));

Multi-level aggregation

Multiple

groupings

in one

query.

3. String Functions

#

Command

Syntax

Example

Description

21

CONCAT

`column1

column2`

22

LENGTH

LENGTH(column)

SELECT LENGTH(first_name)

FROM employees;

Length of

string.

23

SUBSTR

SUBSTR(column,

start, length)

SELECT SUBSTR(first_name,1,3)

FROM employees;

Extract

substring.

24

INSTR

INSTR(column,

'substring')

SELECT INSTR(first_name,'a')

FROM employees;

Position of

substring.

25

REPLACE

REPLACE(column,

'old','new')

SELECT

REPLACE(first_name,'a','@')

FROM employees;

Replace

substring.

26

LOWER

LOWER(column)

SELECT LOWER(first_name)

FROM employees;

Convert to

lowercase.

27

UPPER

UPPER(column)

SELECT UPPER(first_name)

FROM employees;

Convert to

uppercase.

28

TRIM

TRIM(column)

SELECT TRIM(first_name)

FROM employees;

Remove spaces.

29

LTRIM

LTRIM(column)

SELECT LTRIM(first_name,'A')

FROM employees;

Remove

leading

characters.

30

RTRIM

RTRIM(column)

SELECT RTRIM(first_name,'A')

FROM employees;

Remove

trailing

characters.

4. Date and Time Functions

#

Command

Syntax

Example

31

SYSDATE

SYSDATE

SELECT SYSDATE FROM dual;

32

CURRENT_DATE

CURRENT_DATE

SELECT CURRENT_DATE FROM

dual;

33

ADD_MONTHS

ADD_MONTHS(date, n)

SELECT

ADD_MONTHS(SYSDATE,1) FROM

dual;

34

MONTHS_BETWEEN

MONTHS_BETWEEN(date1,date2)

SELECT

MONTHS_BETWEEN(SYSDATE,'01-

JAN-2025') FROM dual;

35

NEXT_DAY

NEXT_DAY(date, 'DAY')

SELECT

NEXT_DAY(SYSDATE,'MONDAY')

FROM dual;

36

LAST_DAY

LAST_DAY(date)

SELECT LAST_DAY(SYSDATE)

FROM dual;

37

TRUNC

TRUNC(date,'fmt')

SELECT TRUNC(SYSDATE,'MM')

FROM dual;

38

ROUND

ROUND(date,'fmt')

SELECT ROUND(SYSDATE,'MM')

FROM dual;

39

EXTRACT

`EXTRACT(YEAR

MONTH

40

INTERVAL

`date + INTERVAL 'n' DAY

MONTH

5. Joins

#

Command

Syntax

Example

Description

41

INNER

JOIN

SELECT * FROM

t1 INNER JOIN

t2 ON

t1.col=t2.col;

SELECT e.first_name,

d.department_name FROM employees

e INNER JOIN departments d ON

e.department_id=d.department_id;

Matching

rows from

both tables.

42

LEFT

JOIN

SELECT * FROM

t1 LEFT JOIN t2

ON t1.col=t2.col;

Return all rows from left table.

43

RIGHT

JOIN

SELECT * FROM

t1 RIGHT JOIN

t2 ON

t1.col=t2.col;

Return all rows from right table.

44

FULL

OUTER

JOIN

SELECT * FROM

t1 FULL OUTER

JOIN t2 ON

t1.col=t2.col;

Return all rows from both tables.

45

CROSS

JOIN

SELECT * FROM

t1 CROSS JOIN

t2;

Cartesian product of tables.

46

SELF JOIN

SELECT a.col1,

b.col2 FROM

table a, table b

WHERE

a.col=b.col;

Join table to itself.

47

NATURAL

JOIN

SELECT * FROM

t1 NATURAL

JOIN t2;

Automatically join on columns with

same name.

48

JOIN

USING

SELECT * FROM

t1 JOIN t2

USING(col);

Join using same column name.

49

OUTER

APPLY

SELECT * FROM

t1 OUTER

APPLY (SELECT

...);

Oracle 12c+ lateral joins.

50

CROSS

APPLY

SELECT * FROM

t1 CROSS

APPLY (SELECT

Oracle lateral join equivalent.

...);

6. Subqueries & Set Operations

#

Command

Syntax

Example

Description

51

Subquery

SELECT col

FROM table

WHERE col =

(SELECT col

FROM table2

WHERE

condition);

SELECT first_name FROM employees

WHERE department_id = (SELECT

department_id FROM departments

WHERE department_name='IT');

Query within

a query.

52

Correlated

Subquery

Subquery

references outer

query: SELECT col

FROM table t1

WHERE col =

(SELECT

MAX(col) FROM

table t2 WHERE

t2.id=t1.id);

Find department highest salary.

53

EXISTS

SELECT * FROM

table WHERE

EXISTS(subquery);

SELECT * FROM employees WHERE

EXISTS(SELECT * FROM orders

WHERE

orders.emp_id=employees.employee_id);

Check if

subquery

returns rows.

54

NOT

EXISTS

SELECT * FROM

table WHERE

NOT

EXISTS(subquery);

Find employees without orders.

55

IN

`SELECT * FROM

table WHERE col

IN (subquery

values);`

SELECT *

FROM

employees

WHERE

department_id

IN (10,20,30);

56

NOT IN

`SELECT * FROM

table WHERE col

NOT IN (subquery

values);`

SELECT *

FROM

employees

WHERE

department_id

NOT IN

(10,20);

57

ANY /

col operator ANY

SELECT * FROM employees WHERE

Compare to

SOME

(subquery)

salary > ANY (SELECT salary FROM

employees WHERE department_id=10);

any value in

subquery.

58

ALL

col operator ALL

(subquery)

SELECT * FROM employees WHERE

salary > ALL (SELECT salary FROM

employees WHERE department_id=10);

Compare to

all values.

59

UNION

SELECT col

FROM table1

UNION SELECT

col FROM table2;

Combine queries, remove duplicates.

60

UNION

ALL

SELECT col

FROM table1

UNION ALL

SELECT col

FROM table2;

Combine queries including duplicates.

61

MINUS

SELECT col

FROM table1

MINUS SELECT

col FROM table2;

Rows in first query not in second.

62

INTERSECT

SELECT col

FROM table1

INTERSECT

SELECT col

FROM table2;

Rows common to both queries.

7. Data Manipulation (DML)

#

Command

Syntax

Example

Description

63

INSERT

INSERT INTO table (col1,

col2) VALUES (val1, val2);

INSERT INTO employees

(first_name,last_name,salary)

VALUES

('John','Doe',5000);

Add new

rows.

64

INSERT

ALL

INSERT ALL INTO table1

(col1) VALUES (val1)

INTO table2 (col2)

VALUES (val2) SELECT *

FROM dual;

Insert multiple rows/tables in

one statement.

65

INSERT

FIRST

INSERT FIRST INTO table1

... SELECT * FROM dual;

Conditional insert (Oracle).

66

UPDATE

UPDATE table SET

col1=val1 WHERE

condition;

UPDATE employees SET

salary=5500 WHERE

first_name='John';

Modify

rows.

67

DELETE

DELETE FROM table

WHERE condition;

DELETE FROM employees

WHERE salary<3000;

Remove

rows.

68

MERGE

MERGE INTO target

USING source ON condition

WHEN MATCHED THEN

UPDATE ... WHEN NOT

MATCHED THEN INSERT

...;

Upsert data from one table to

another.

8. Transactions & Locks

#

Command

Syntax

Example

Description

69

COMMIT

COMMIT;

Save changes

permanently.

70

ROLLBACK

ROLLBACK;

Undo

changes.

71

SAVEPOINT

SAVEPOINT

savepoint_name;

Create a

savepoint.

72

ROLLBACK TO

SAVEPOINT

ROLLBACK TO

SAVEPOINT

savepoint_name;

Rollback to a

savepoint.

73

SET

TRANSACTION

`SET TRANSACTION

READ ONLY

READ

WRITE;`

Set transaction

properties.

74

LOCK TABLE

`LOCK TABLE table IN

EXCLUSIVE

SHARE

MODE;`

Prevent other sessions

from modifying table.

9. Table Management (DDL)

#

Command

Syntax

Example

Description

75

CREATE

TABLE

CREATE TABLE

table_name (col datatype,

...);

CREATE TABLE

employees(id NUMBER,

name VARCHAR2(50));

Create

table.

76

DROP

TABLE

DROP TABLE

table_name;

Delete table and data.

77

ALTER

TABLE

ADD

ALTER TABLE table

ADD (col datatype);

ALTER TABLE employees

ADD email

VARCHAR2(50);

Add

column.

78

ALTER

TABLE

MODIFY

ALTER TABLE table

MODIFY col datatype;

Change column type.

79

ALTER

TABLE

DROP

COLUMN

ALTER TABLE table

DROP COLUMN col;

Remove column.

80

TRUNCATE

TABLE

TRUNCATE TABLE

table;

Remove all rows.

81

CREATE

VIEW

CREATE VIEW

view_name AS SELECT

...;

CREATE VIEW emp_view

AS SELECT name,salary

FROM employees;

Create

virtual

table.

82

DROP VIEW

DROP VIEW view_name;

Delete view.

83

CREATE

SEQUENCE

CREATE SEQUENCE

seq_name START WITH n

INCREMENT BY n;

Auto-generate numbers.

84

DROP

SEQUENCE

DROP SEQUENCE

seq_name;

Remove sequence.

10. Indexes & Performance

#

Command

Syntax

Example

85

CREATE

INDEX

CREATE INDEX idx_name ON table(col);

Improve

query

performance.

86

CREATE

UNIQUE

INDEX

CREATE UNIQUE INDEX idx_name ON table(col);

Ensure

uniqueness.

87

DROP INDEX

DROP INDEX idx_name;

Remove

index.

88

ANALYZE

TABLE

ANALYZE TABLE table COMPUTE STATISTICS;

Gather

statistics for

optimizer.

89

EXPLAIN

PLAN

EXPLAIN PLAN FOR SELECT ...;

Show

execution

plan.

90

DBMS_STATS

EXEC

DBMS_STATS.GATHER_TABLE_STATS('schema','table');

Gather

optimizer

stats.

11. Analytical / Window Functions

#

Command

Syntax

Example

91

ROW_NUMBER

ROW_NUMBER() OVER(PARTITION BY col

ORDER BY col2)

Unique row

numbers.

92

RANK

RANK() OVER(PARTITION BY col ORDER

BY col2)

Rank with

gaps.

93

DENSE_RANK

DENSE_RANK() OVER(PARTITION BY col

ORDER BY col2)

Rank

without

gaps.

94

NTILE

NTILE(n) OVER(ORDER BY col)

Divide into

n buckets.

95

LAG

LAG(col,offset) OVER(PARTITION BY col

ORDER BY col2)

Previous

row value.

96

LEAD

LEAD(col,offset) OVER(PARTITION BY col

ORDER BY col2)

Next row

value.

97

FIRST_VALUE

FIRST_VALUE(col) OVER(PARTITION BY

col ORDER BY col2)

First value

in partition.

98

LAST_VALUE

LAST_VALUE(col) OVER(PARTITION BY

col ORDER BY col2 ROWS BETWEEN

UNBOUNDED PRECEDING AND

UNBOUNDED FOLLOWING)

Last value

in partition.

99

CUME_DIST

CUME_DIST() OVER(ORDER BY col)

Cumulative

distribution.

100

PERCENT_RANK

PERCENT_RANK() OVER(ORDER BY col)

Percent

rank of

rows.

#

Command

Syntax

Example

Description

1

SELECT

SELECT column1,

column2 FROM

table_name;

SELECT name, age FROM

employees;

Retrieve data from

table.

2

SELECT

ALL

SELECT * FROM

table_name;

SELECT * FROM

employees;

Retrieve all

columns.

3

DISTINCT

SELECT DISTINCT

column FROM table;

SELECT DISTINCT

department_id FROM

employees;

Remove

duplicates.

4

WHERE

SELECT columns

FROM table WHERE

condition;

SELECT * FROM

employees WHERE salary

> 5000;

Filter rows by

condition.

5

AND / OR

`WHERE condition1

AND

OR condition2`

SELECT * FROM

employees

WHERE

salary>5000 AND

department_id=10;

6

ORDER

BY

`SELECT columns

FROM table ORDER

BY column ASC

DESC;`

SELECT * FROM

employees

ORDER BY salary

DESC;

7

ROWNUM

SELECT * FROM

table WHERE

ROWNUM <= n;

SELECT * FROM

employees WHERE

ROWNUM <= 5;

Limit number of

rows.

8

FETCH

FIRST

SELECT * FROM

table ORDER BY col

FETCH FIRST n

ROWS ONLY;

SELECT * FROM

employees ORDER BY

salary DESC FETCH

FIRST 5 ROWS ONLY;

Limit rows (Oracle

12c+).

9

OFFSET

SELECT * FROM

table ORDER BY col

OFFSET n ROWS

FETCH NEXT m

ROWS ONLY;

SELECT * FROM

employees ORDER BY

salary DESC OFFSET 5

ROWS FETCH NEXT 5

ROWS ONLY;

Skip rows.

10

ALIAS

SELECT column AS

alias FROM table;

SELECT name AS

emp_name FROM

employees;

Rename columns

in result.