Sql course content | Topics

Basic Database concept and sql      

                   Basic history of database concept: DBMS, RDBMS.

                   Advantage of ORACLE database and version information

                   Interface tools usage: Sqlplus, isqlplus, Sql developer, Toad

                   SQL Language overview: DQL, DML, DDL, DCL, TCL

                   SELECT Command - Column Alias Rules, String data,

                   Concatenations with various data

                   Null Value handling with number and characters,

                   Arithmetic Operator

                   Concatenation Operator,

                   Eliminating Duplicate Rows

Restricting and Sorting Data (Where clause)

                   WHERE Clause - Character Strings and Dates, number

                   General Comparison Conditions = > >= < <= <>

                   Other Comparison BETWEEN , IN , LIKE , NULL

                   Logical Conditions AND OR NOT

                  ORDER BY Clause, Sorting by Column Alias , Column Position, Multiple Columns

Single-Row Functions

                Character Functions

                Number Functions

    Dates Functions

    Conversion Functions

    General Functions

                CASE Expression , DECODE

Multi-Row Functions

                Group Functions Rules

                SUM, MIN, MAX, COUNT, AVG

                Creating Groups of Data: GROUP BY Clause

                Filtering Group Results: The HAVING Clause

Sub query

    Sub Queries types

    Single row, multiple row and co-related sub query

                Single-Row Sub queries- Rules, Operators: = > >= < <= <>

                Null Values in a Sub query

                Multi-Row Sub queries- Rules, Operators: IN, ANY, ALL

Joins

               

                EQUI JOIN/SIMPLE JOIN/NORMAL JOIN

                LEFT OUTER, RIGHT OUTER, FULL OUTER

                INNER JOIN

                JOIN ... USING clause

                Oracle standard outer join

                CROSS JOIN

Data Definition Language - DDL

               

                DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE

                DEFAULT Option

                Creating table by using sub query

                              With data

                              Without data

Data Manipulation Language DML and Transaction Control Language TCL

               

                DML :  INSERT, UPDATE, DELETE, MERGE

                TCL   :  COMMIT, ROLLBACK, SAVEPOINT

Constraints

                NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

                Adding a Constraint, Dropping a Constraint,

                Disabling Constraints, Enabling Constraints

View & Materialize View

Other Database Objects

                Sequence- NEXTVAL and CURRVAL

                Index - When to Create an Index, When Not to Create an Index.

                Synonyms

Sqlplus commands

Pseudo columns

                Sysdate, Systimestamp, Rownumn, Rowid.

Set operators

                UNION, UNION ALL, INTERSECT, MINUS.

Metadata - Dictionary Tables

                Tables, Views, Synonyms, Index, Sequence, Constrains.

Explain Plan

Data Loader - Sqlldr

Multi table insert

                With condition

                Without condition