oracle SQL Course Content

Data types

1.     Number Data Type

·       Integer

·       Float

·       DECIMAL

·       BINARY_FLOAT

·       BINARY_DOUBLE

2.     Character Data Types

·       CHAR

·       VARCHAR2

·       NCHAR

3.     Date and Time Data Types

·       DATE

·       TIMESTAMP

·       TIMESTAMP WITH TIME ZONE

·       TIMESTAMP WITH LOCAL TIME ZONE

·       INTERVAL YEAR TO MONTH

·       INTERVAL DAY TO SECOND

4.     Binary Data Types

·       RAW

·       LONG RAW

5.     Large Object Data Types

·       CLOB

·       NCLOB

·       BLOB

·       BFILE

6.     Other Data Types

·       ROWID

·       UROWID

·       XMLTYPE

7.     Data Definition Language

·       Create

·       Alter

·       Drop

·       Truncate

·       Rename

8.     Data Manipulation Language

·       Insert

·       Update

·       Delete

9.     Transaction Control Language (TCL)

·       COMMIT

·       ROLLBACK

·       SAVEPOINT

·       SET TRANSACTION

10.  DCL (Data Control Language)

·       Grant

·       Revoke

11.  Select

·       Mathematics Operators

·       Comparison Operators

·       NULL values

·       Column Aliases

·       Concatenation

·       DISTINCT

·       WHERE Clause

·       BETWEEN Operator

·       IN operator

·       LIKE operator

·       ALL operator

·       ANY operator

·       ORDER BY clause

·       CASE

·       Substitution Variables

·       PIVOT

·       UNPIVOT

·       WITH clause

·       MERGE

·       NVL

·       NVL2

·       AND

·       OR

12.  Numeric Functions

·       Abs

·       Sign

·       Sqrt

·       Mod

·       Power

·       Exp

·       Ln

·       Log

·       Ceil

·       Floor

·       Round

·       Trunc

·       Bitand

·       Coalesce

·       SIN

·       COS

13.  Character Functions

·       Lower

·       Upper

·       Initcap

·       Length

·       Concat

·       Rpad

·       Lpad

·       Trim

·       Ltrim

·       Rtrim

·       Translate

·       Replace

·       Instr

·       Substr

·       CHR

·       DECODE

14.  Date Functions

·       SYSDATE

·       CURRENT_DATE

·       CURRENT_TIMESTAMP

·       SYSTIMESTAMP

·       LOCALTIMESTAMP

·       DBTIMEZONE

·       SESSIONTIMEZONE

·       ADD_MONTHS

·       MONTHS_BETWEEN

·       NEXT_DAY

·       LAST_DAY

·       EXTRACT

·       ROUND

·       TRUNC

·       NEW_TIME

·       COALESCE

15.  Conversion Function

·       TO_CHAR

·       TO_NUMBER

·       TO_DATE

16.  Constraints

·       Not Null

·       Unique

·       Primary Key

·       Foreign Key

·       Check

·       Default

17.  Joins

·       Cross join

·       Natural Join

·       Equi Join

·       Non eui join

·       Inner join

·       Left Outer join

·       Right Outer join

·       Full Outer join

·       Self join

·       USING clause

·       Lateral join

·       Anti Join

·       Semi Join

18.  FETCH

·       FETCH clause

·       OFFSET clause

·       ONLY

·       WITH TIES

·       Top N rows

·       Limit by percentage of rows

19.  Indexes

·       Bitree

·       Bitmap

·       Function-Based Index

·       Reverse Key indexes

·       Unique Key Index

·       Simple index

·       Composite Index

20.  Aggregate Functions

·       Group by

·       Having

·       Max

·       Min

·       Sum

·       Avg

·       Count

·       Stdev

·       Variance

·       Grouping

·       Grouping_id

·       Grouping_sets

·       Rollup

·       Cube

21.  Analytical Functions

·       Over clause

·       Partition by

·       Windowing clauses

·       Rank

·       Dense Rank

·       Percent_rank

·       ROW_NUMBER

·       CUME_DIST

·       LISTAGG

·       NTILE

·       LAG

·       LEAD

·       FIRST_VALUE

·       LAST_VALUE

22.  Subquery

·       Subquery

·       Single-Row Subqueries

·       Multi-Row Subqueries

·       Multi-Column Subquery

·       Scalar Subqueries

·       Inline Views

·       Correlated

·       Non-Correlated

·       Existential Subquery (EXISTS)

·       Not Exists Subquery

·      Lateral Subquery

23.  Views

·       Simple view

·       Composite view

·       WITH CHECK OPTION

·       read-only view

·       FORCE view

24.  Materialized View

·       IMMEDIATE

·       DEFERRED

·       Fast Refresh

·       Complete Refresh

·       Force Refresh

·       On Demand

·       On Commit

·       On interval

25.  Partition

·       List Partitioning

·       Range Partitioning

·       Hash Partitioning

·       Composite Partitioning

·       Subpartitioning

·       Exchange Partitions

26.  Sequence

·       NEXTVAL

·       CURRVAL

·       MINVALUE

·       MAXVALUE

·       Start With

·       INCREMENT BY

·       CYCLE

·       NOCYCLE

·       CACHE

·       Inserting And Updating Sequences

·       Default Values

27.  Hierarchical query

·       CONNECT BY PRIOR

·       CONNECT BY LEVEL

·       CONNECT_BY_ROOT

·       SYS_CONNECT_BY_PATH

·       CONNECT_BY_ISLEAF

·       CONNECT_BY_ISCYCLE

28.  Regular Expression

·       REGEXP_LIKE

·       REGEXP_COUNT

·       REGEXP_replace

·       REGEXP_INSTR

·       REGEXP_SUBSTR

29  SET Operators

·       UNION

·       UNION ALL

·       MINUS

·       INTERSECT