Course #: 14020e- Oracle SQL

Introduction to DB Concepts

•     Disadvantages of File Processing System
•     Advantages of Data Base Management System
•     Data Base Management System Terminology
•      How these are related to SQL
•     What is SQL and T-SQL

Getting Started with SQL
a.  Different Types of Commands in SQL
Data Definition Language Commands

•     CREATE COMMAND
•     INSERT COMMAND
•     ALTER COMMAND
•     TRUNCATE COMMAND
•     DROP COMMAND
•     vii.   Data Manipulation Commands
•     INSERT COMMAND
•     DELETE COMMAND
•     UPDATE COMMAND

Data Control Language Commands

•     GRANT COMMAND
•     REVOKE COMMAND
•     Data Retrieval Commands
•     SELECT COMMAND
•     WHERE CLAUSE
•     GROUP BY CLAUSE
•     HAVING CLAUSE
•     ORDER BY CLAUSE

Transaction Control Language Commands

•     COMMIT COMMAND
•     ROLLBACK COMMAND
•     SAVE POINT COMMAND

Examples for all the above categories of commands

• Data Types used in SQL
• Numeric Data Types
• Character or Text Data Types
• Date and Time related Data Types
• Miscellaneous Data Types
   

Operators in SQL
a.      What is an Operator
b.      Different Categories of Operators

• Arithmetic Operators
• Relational Operators
• Logical Operators
• Miscellaneous Operators

Functions in SQL
a.      What are different categories of functions in SQL
b.      What is a Scalar Function?
c.      What is an Aggregate Function?
d.      Different predefined Scalar functions available in SQL

• Numeric Functions
• String Functions
• Date Time functions
• Miscellaneous Functions
• e.      Different  predefined Aggregate Functions in SQL

CONSTRAINTS in SQL
What is a Constraint and its advantages
Types of Constraints

• Predefined Constraints
• User Defined Constraints

More of predefined Constraints

• PRIMARY KEY CONSTRAINT
• FOREIGN KEY CONSTRAINT
• CHECK CONSTRAINT
• NOT NULL CONSTRAINT
• UNIQUE  CONSTRAINT

What is a User Defined Constraint?

JOINS in SQL
a.      What is a JOIN and what is its necessity
b.      Types of JOINS

• INNER JOIN
• NATURAL JOIN
• OUTER JOIN : LEFT OUTER , RIGHT OUTER
• CARTESIAN JOIN

SET Operators

• UNION
• UNION ALL
• INTERSECT

SUB QUERIES
VIEWS in SQL
a.      What is a view and its advantages
b.      Types of Views

INDEXES
a.      What is an Index and its advantages
b.      Types of Indexes

• CLUSTERED INDEX
• NON CLUSTERED INDEX

T-SQL
a.       What is T-SQL?
b.       What we can do with T-SQL?

Different Programming Constructs in T-SQL
a.       Conditional Statements:

• IF Statements
• IF ELSE Statements
• NESTED IF ELSE Statements
• Switch or Case Statements

b.      Looping Statements

• FOR / WHILE Statements for Looping

Stored Procedures and Functions
a.      What is a Stored Procedure
b.      How to write and execute a Stored Procedure
c.      What is an user defined function
d.      How to write and execute a Function
e.      Differences between Stored Procedures and Functions
 

Error Handling in T-SQL
a.       Use of TRY CATCH Statements in SP and Functions to Handle Errors
 

Cursors
a.      What is a Cursor and its advantages
b.      Different types of Cursors and its usage
 

Triggers
a.      What is a Trigger and its advantages
b.      Different Types of Triggers
c.      How to write and make the Triggers to fire Automatically

Recommended Prerequisites:

• There are no mandatory prerequisites. Work experience in IT services is recommended.