ORACLE PLSQL Training in Kukatpally Hyderabad
5
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:
Oracle SQL PLSQL Training Content
Download course content here ORACLE-19c-SQL-PLSQL-MY-SQL-Algorithm.pdf (369 downloads )
PLSQL
SNO | TOPIC | Sub topic | Lab |
1 | introduction | ||
2 | Operators | ||
3 | Table | create table
drop table delete table rename table copy table alter table
|
|
4 | Insert | insert single rows
insert multiple rows |
|
5 | Select | select all colums
select few columns select with where clause select with having clause select unique select distinct select count select rownum select first select last select random select date select columns from multiple tables select in select sum select null / not null |
|
6 | Clause | where clause
AND clause OR clause
|
|
7 | Order by | Ascending order
descending order random limit
|
|
8 | DML | insert single rows
insert multiple rows update delete |
|
9 | JOIN | ||
10 | KEYS | ||
11 | TCL commands | ||
12 | Subquery | ||
13 | Constraints | ||
14 | DDL commands | ||
15 | DML commands | ||
16 | Cursor | ||
17 | Strings | ||
18 | Views | ||
19 | Injection | ||
19 | CRUD on Columns |
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
SQL
SNO | TOPIC | Sub topic | Lab |
1 | Data Types | ||
2 | Operators | ||
3 | Table | create table
drop table delete table rename table copy table alter table
|
|
4 | insert | ||
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