SQL

SQL PLSQL MYSQL SQL SERVER Training in Kukatpally Hyderabad

 

Oracle Training Course Objectives

  • Create Database tables.
  • Manage Database objects.
  • Access and manipulate data in the Database using SQL statements.
  • Control user access to data.
  • Data Managing
  • Working with Databases

Oracle Training Course Duration

  •  45hrs

Trainer Details:

Trainer Name          : Mr Lokesh
Qualification            : B.Tech(CSE)
Experience              : Software professional with 12 yrs experience in Dot Net/C#/ASP/MVC//JAVA/SQL/PL SQL/SQL SERVER/MYSQL

Oracle Training Content

Download course content here ORACLE-19c-SQL-PLSQL-MY-SQL-Algorithm.pdf (280 downloads )

                                 SQL

1. History of Database.
2. Difference between file system & DB.
3. DDL, DML, DCL & TCL commands.
4. Table/object Naming rules.
5. Data Types.
6. Constraints.
7. Operators.
8. Clauses.
9. Functions.
10. Joins.
11. sub queries.
12. Views.
13. Sequences.
14. Indexes.
15. Synonyms.
16. Partitions.
17. Locks.

                          PL/SQL

1. Introduction & Advantages.
2. Data types.
3. Variables & Blocks.
4. Control structures.
5. Loops.
6. CURSOR.
7. Collections.
8. Records
9. Exception handling.
10. Functions.
11. Procedures/stored procedures.
12. Triggers.
13. Packages.
14. Performance improvement techniques with BULK COLLECT & FOR ALL
15. Dynamic SQL.

 

Oracle 19c

 

Chapter 1 - Demo

● What is a database ?

ORACLE 19c

a. A database is a collection of structured data stored electronically in a computer system

1. Introduction to prerequisites

2. File and File System
3. Disadvantages of file
4. Introduction to Database
5. Introduction to Database Management systems

Chapter 2
1. Introduction to Database Models
2. Database Models Hierarchical Model Network Model Relational Model 3. Features of RDBMS
4. Client-Server Technology

Chapter 3
1. Oracle Versions

2. Oracle Corporation Products 3. About SQL & SQL*PLUS
4. Sub Languages in SQL

Chapter 4
1. Oracle Data Types

2. DDL Commands ● Create

● Alter-add, modify, drop column

● Drop Chapter 5

1. DML Commands 2. Insert Statement 3. Oracle Operators 4. Where Clause

5. Update, Delete Statements Chapter 6

1. DQL – SELECT stmt
2. Examples on Select
3. Using Where clause
4. Special Operators IN, BETWEEN, LIKE, IS NULL

Chapter 7
1. DQL – SELECT stmt

2. Examples on Select
3. Using Where clause
4. Special Operators IN, BETWEEN, LIKE, IS NULL

Chapter 8
1. Select with Arithmetic Expressions & Alias
2. Names, Distinct clause
3. Built in Functions
4. Arithmetic Functions
5. Abs, sqrt, power, sign, round, trunc, ceil, floor, sin, cos, tan, exp, ln, log 6. Character Functions
7. Inticap, lower, upper, reverse, length, ascii, chr,
8. lpad, rpad, soundex

Chapter 9
1. Built in Functions

2. Character Functions Ltrim, rtrim,trim, concat, replace, translate, substr, instr

3. Date Functions Sysdate, Add_months, months_between Chapter 10

1. Date Conversions
2. To_char, to_date
3. Group Functions
4. Count, sum,min, max, avg, stddev, variance 5. General Functions

6. Least, greatest Chapter 11

1. General Functions
2. NVL, Decode, to_number
3. Select Clauses
4. Group by Having, Order by clauses

Chapter 12
1. Joins Equi Join, Non Equi Join, Outer Joins & Self Join

Chapter 13
1. Set Operators Union all, Union, Intersect, Minus 2. Making a copy of Table

3. Data control Language (DCL)
4. Sharing Tables and Columns Grant, Revoke 5. PUBLIC, WITH GRANT OPTION Keywords

Chapter 14
1. Creating New Users

2. Transaction control Language (TCL) Commit, Rollback, Savepoint 3. Truncate, Rename commands
4. Rename Column & Constraints

Chapter 15

1. Sub Queries Introduction & Examples

2. Subqueries with Delete and Update Chapter 16

1. Special operators in sub queries Exists, Any, Some, All Correlated sub queries

2. Locks – Row Level, Table Level Locks Chapter 17

1. Integrity Constraints
2. Column level syntax
3. On delete cascade, Cascade constraints

Chapter 18
1. Integrity Constraints

2. Column, Table Constraint syntax Chapter 19

1. Database Objects in Oracle

2. Views Simple views, Read only view Chapter 20

1. Join Views
2. Materialized views
3. Synonyms – Private & Public Synonyms Sequence – Create and Alter

Chapter 21
1. Index – Normal, Composite, Unique,
2. Function Based Indexes
3. Pseudo Columns Rownum, Rowid, Level with Examples Top-N Analysis – Examples

PL/SQL

Chapter 22
1. PL/SQL

2. Introduction to Pl/Sql
3. Features, Block structure
4. Data types, Executable Stmts 5. Simple Examples

Chapter 23
1. Conditional Statements

2. If, Case
3. Iteration control Statements
4. Simple Loop, While Loop, Numeric For loop

Chapter 24
1. Cursors Introduction, Types of cursors Explicit cursors – Declaring Cursor Cursor Operations, Cursor Attributes Examples using Cursors

Chapter 25
1. Cursors with Parameters

2. Where current of Clause

3. Implicit Cursors – Examples
4. Exceptions Pre-defined & User defined Exceptions Non predefined Exception 5. Examples

Chapter 26
1. Composite Data Types

2. PL/SQL Records
3. PL/SQL Tables
4. Nested Records
5. Using Record in Table 6. Examples

Chapter 27
1. Database Triggers

2. Introduction Types of Triggers 3. Row Triggers
4. Examples

Chapter 28
1. Database Triggers – contd 2. Statement Triggers
3. Instead of Triggers – Views 4. Examples

Chapter 29
1. Sub Programs – Features Procedures, Functions 2. Examples

Chapter 30
1. Sub Programs... contd

2. Parameter Modes –IN, OUT, INOUT 3. Packages
4. Examples

Chapter 31
1. Packages – contd

2. Overloading of functions in Packages 3. Local subprograms in Packages
4. Examples

Chapter 32
1. Object Technology

2. Objects, Using in Tables, Nested Objects Chapter 33

1. Objects with Methods
2. Collections Nested Tables Varying Arrays 3. Diff between Nested tables and Varrays

Chapter 34
1. File Input & Output

2. UTL_FILE built in package 3. Oracle Utilities

4. Export, Import Chapter 35

1. Using LONG and raw datatypes
2. Using LOBS LOB Types –CLOB, NCLOB BLOB, BFILE 3. Working with LOB Tables

Chapter 36
1. Returning into Clause

2. Bulk Collect construct Using it in select, Update, Delete & Fetch 3. Temporary Tables
4. Group by with ROLLUP & CUBE Operators

Chapter 37
1. NORMALIZATION

2. Normal Forms with Example Chapter 38

1. Partition Tables Alter partitions – add, drop, merge Partition Indexes Chapter 39

1. New Date Functions Systimestamp, Current_time Stamp, Localtimestamp,

DBTIMEZONE, SESSIONTIMEZONE, EXTRACT Timestamp Datatype Chapter 40

1. New General Functions Coalesce, Nullif, NVL2 2. Analytical functions
3. Rank, Dense_Rank,Lag,Lead,First,Last
4. Multiple Inserts

5. Conditional Insert 6. Merge Statement