Oracle SQL Syllabus

Module-1 Introduction to SQL

Introduction to Oracle Database
This section introduces Oracle Database 12c, including its key features, basic database design, and the classification of SQL statements. Students will learn to describe the dataset used in the course, log in using SQL Developer, and save queries to files or scripts for reuse.

Retrieve Data using the SQL SELECT Statement
Students will understand the capabilities of the SELECT statement, retrieve all columns or specific columns from tables, use arithmetic and concatenation operators, and employ the DESCRIBE command to examine table structures.

Learn to Restrict and Sort Data
This section teaches the use of the WHERE clause to filter output, comparison and logical operators, precedence rules, character string literals, and sorting data using ORDER BY. Students will also learn SQL row-limiting clauses, including FETCH FIRST, OFFSET, and WITH TIES.

Usage of Single-Row Functions to Customize Output
Covers differences between single-row and multiple-row functions, string manipulation functions, numeric functions like ROUND, TRUNC, MOD, arithmetic with date data, and various DATE functions.

Invoke Conversion Functions and Conditional Expressions
Students will learn implicit and explicit data type conversion, TO_CHAR, TO_NUMBER, TO_DATE functions, nesting multiple functions, using NVL, NULLIF, COALESCE, and applying IF-THEN-ELSE logic in queries.

Aggregate Data Using the Group Functions
This section covers aggregation functions, grouping data with GROUP BY, and filtering grouped data using the HAVING clause to produce meaningful reports.

Display Data From Multiple Tables Using Joins
Students will learn to write SELECT statements across multiple tables, use outer joins to include unmatched data, and perform self-joins.

Use Sub-queries to Solve Queries
Covers defining sub-queries, identifying problem types solved by sub-queries, types of sub-queries, and writing single-row and multiple-row sub-queries.

The SET Operators
Introduces SET operators, combining results using UNION, UNION ALL, INTERSECT, MINUS, and controlling the order of returned rows.

Data Manipulation Statements
Students will learn the DML statements INSERT, UPDATE, DELETE, transaction control using COMMIT and ROLLBACK, and the concept of read consistency.

Use of DDL Statements to Create and Manage Tables
This section covers creating, altering, truncating, and dropping tables, enforcing data integrity using constraints like primary key, foreign key, NOT NULL, UNIQUE, and CHECK, and understanding the Recycle Bin concept.

Other Schema Objects
Students will learn to create simple and complex views, retrieve data from views, manage sequences, create and maintain indexes, and work with private and public synonyms.