DKGT Technologies : SQL Course Syllabus:

  Basics of SQL

·         What is SQL

·         Oracle database architecture

·         DBMS concepts

·         RDBMS concepts

·         Difference between DBMS and RDBMS

·         Database concepts

·         What is SQL*PLUS

·         What is iSQLPLUS

·         Advantages of SQL

·         Alias – Column alias and Table alias

·         Distinct and unique keyword

·         Literals – ‘’

 

·         SQL Statements

·         Data retrieval – SELECT

·         Data Manipulation Language (DML)– INSERT, INSERT ALL , UPDATE , DELETE , MERGE

·         Data Definition Language (DDL) – CREATE,ALTER,DROP,RENAME,TRUNCATE

·         Transaction Control Language (TCL) – COMMIT,ROLLBACK,SAVEPOINT

·         Data Control Language (DCL)– GRANT,REVOKE

·         Create, modify and drop new USER, SCHEMA , ROLE

 

·         Data Types

·         Number Data Type – NUMBER

·         Character Data Types – CHAR,VARCHAR2,CLOB,LONG

·         Time and Date Data Types –DATE,TIMESTAMP 

·         Constraints

·         PRIMARY KEY

·         UNIQUE

·         NOT NULL

·         CHECK

·         FOREIGN KEY

·         COMPOSITE PRIMARY KEY

·         Detailed example for full table creation with all constraints and with all data types

 

·         Single Row Functions

·         Case Manipulation Function – UPPER,LOWER,INITCAP

·         Character manipulation Function – SUBSTR,INSTR,LENGTH, REPLACE, TRANSLATE, REVERSE ,  CONCAT, LTRIM, RTRIM

·         Number Functions – ROUND,TRUNC,MOD

·         Date Functions –MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY

·         Data Type Conversion functions- TO_CHAR,TO_DATE

·         General Functions – NVL,NVL2,NULLIF,COALESCE

·         Control statements – DECODE,CASE

·         Mathematical Functions – GREATEST , LEAST

 

·         Multiple Row Functions

·         Group Functions – MAX,MIN,SUM,AVG,COUNT

·         Analytic Functions – RANK,DENSE_RANK

·         Set Operators

·         UNION

·         UNION ALL

·         INTERSECT

·         MINUS

·         SQL Operators

·         Arithmetic Operators  e.g) +,-,*,/

·         Comparison Operator or Reltional Operator e.g)  <,>,<=,>=,!=,<>,=

·         Logical operator  e.g) AND,OR,NOT

·         Special  operators – IN,LIKE,BETWEEN,IS NULL, IS NOT NULL, ANY, ALL, EXISTS

·         Concatenation Operator

 

·         Clause in ORACLE

·         Mandatory Clause- SELECT,FROM

·         Optional Clause – WHERE,GROUP BY, HAVING,ORDER BY,DISTINCT,ON

·         Sub Queries

·         Single Row Sub query

·         Multiple Row Sub query

·         SCALAR SUB QUERY

·         IN-LINE VIEW

·         NESTED SUB QUERY

·         Co-related Sub query

 

·         Index

·         UNIQUE INDEX

·         NON UNIQUE INDEX

·         FUNCTION BASED INDEX

·         B TREE INDEX

·         BIT MAP INDEX

·         When to create Index

·         When not to create Index

·         Joins

·         INNER JOIN (or) EQUI JOIN

·         LEFT OUTER JOIN

·         RIGHT OUTER JOIN

·         FULL OUTER JOIN

·         CROSS JOIN (OR) NON EQUI JOIN

·         SELF JOIN

·         NATURAL JOIN

 

·         Pseudo Columns

·         SYSDATE

·         SYSTIMESTAMP

·         USER

·         UID

·         ROWNUM

·         ROWID

·         NEXTVAL

·         CURRVAL

·         Views

·         What is views

·         Advantages of views

·         Simple Views and Complex Views

·         Difference between simple view and Complex view

·         Force View

·         With check option view

·         With read only view

·         Materialized Views

·         SQL Tuning

  Sequence

·         Synonyms

·         SQL PLUS commands

·         &

·         &&         

·         Clear

·         Connect

·         Exit

·         Spool filename

·         Spool off

·         Set line size, page size, feedback on, feedback off, verify on, verify off, heading on, heading off

·         Show user

·         Set variable values

·         Define

·         Advanced SQL

·         data dictionary tables

·            Flashback Queries

·         Oracle SQL interview questions

·         Oracle tools with installation