• This SQL and PL/SQL course is intended for beginners unfamiliar with SQL and PL/SQL syntax and/or students looking for a quick update in SQL and PL/SQL. No prior programming knowledge is required. More advanced and complex queries and Oracle OCA and OCE certifications.

Section 1: Oracle SQL - Introduction
1. Oracle SQL Presentation
2. Oracle SQL and PL/SQL Presentation
3. Advanced Oracle PL/SQL Presentation
4. Installation and configuration
5. Installation and configuration (Continued)
6. Creating initial tables and commands

Section 2: Oracle SQL - Running Basic SQL Commands
7. Running basic SQL commands

Section 3: Oracle SQL - Restricting and Ordering Data
8. Restricting and Ordering Data - Class

Section 4: Oracle SQL - Basic Functions
9. Introduction to basic functions
10. Initcap, InStr, Length, Lower, Upper, LPad, SubStr, Replace commands
11. More examples using Initcap, InStr, Length, Lower, Upper, LPad, SubStr, Replace
12. Commands Round, Trunc, Mod, Sysdate, Month_Between, Add_Months, Next_Day
13. To_Char, Trim, Dual, Nvl, Nvl2
14th Case .. When, Decode, Null

Section 5: Oracle SQL - Viewing Data from Multiple Tables
15. Introduction to queries from various tables.
16th Join, Inner Join
17. More examples of Join, Between

Section 6: Oracle SQL - Aggregating Data using Group Functions
18. Introduction to group functions
19th Count, Avg, Min, Max, Sum
20. Group by, Group by with several tables

Section 7: Oracle SQL - Sub-Queries
21. Introduction to sub-consultations
22. Sub-Select, in, not in

Section 8: Oracle SQL - Manipulating Data
23. Introduction to DML
24th Insert, Update, Delete
25. More examples of Insert, Update, Delete

Section 9: Oracle SQL - Creating and Managing Tables
26. Introduction to the creation of tables
27. Creating tables, data type, changing table
28. Renaming Tables, Excluding Tables

Section 10: Oracle SQL - Implementing constraints
29. Introduction to constraints
30th Primary Key, Foreign Key, Uniques, Checks, Not Null

Section 11: Oracle SQL - Creating Visions
31. Introduction to the creation of views
32. Examples of creating views, using views, maintaining views

Section 12: Oracle SQL - Other Database Objects
33. Introducing the Creation of Database Objects
34. Sequence Creation and Maintenance
35. Creation and Maintenance of Indexes, synonyms.

Section 13: Introduction to PL/SQL
36. Introduction to PL/SQL

Section 14: Writing Executable Commands
37. Introduction to executable commands
38. Anonymous blocks, declare.. Begin.. end, If, Else, Elsif, End If, Bind Variable.

Section 15: Interacting with Oracle Server
39. Select in PL/SQL, DML in PL/SQL, transactions, result of DML commands

Section 16: Writing Control Structures
40. Control Structures, If Command, Case Command, Different Loop Commands

Section 17: Record Data Type and Collections
41. Introduction to the Record and Collection data type.
42. Examples and practices of Record and Collection types.

Section 18: Writing Explicit Cursors
43. Introduction to explicit cursors.
44. Examples and practices of explicit cursors.

Section 19: Exception Handling
45. Introduction to PL/SQL exceptions
46. Examples and practice of exceptions and error handling in PL/SQL.

Section 20: Database Procedures
47. Introduction to Procedures
48. Creation and Maintenance of Procedures

Section 21: Database Roles
49. Introduction to Functions
50. Creation and Maintenance of Functions

Section 22: Developing Packages
51. Introduction to Packages
52. Creation and Maintenance of Packages

Section 23: Developing Triggers
53. Introduction to Triggers
54. Creation and Maintenance of Triggers - Part 1
55. Creation and Maintenance of Triggers - Part 2

Section 24: OPERATORS SET
56. Introduction
57. Union, Union All, Intersect, Minus

Section 25: Perfecting the Group By clause
58. Introduction
59th Group By Rollup
60. Group By Cube, Grouping, Grouping_Id
61. Grouping Sets, Rank() Over, Dense_Rank(), Ratio_To_Report(), Partition_By

Section 26: Database Link
62. Introduction
63. Creating Database Link

Section 27: Advanced DML Commands
64. Introduction
65. Insert All, Merge Command

Section 28: Dynamic SQL in PL/SQL
66. Dynamic constructs with SQL, Execute Immediate, Open.. Is.. Fetch.. Close

Section 29: Advanced PL/SQL - Introduction
67. Presentation
68. Scripts
69. Environmental Preparedness

Section 30: Collections
70. Record and Collections - Presentation
71. Record and Collections - Practice
72. Record and Collections - Script
73. Pipelined Function - Presentation
74. Pipelined Function - Scripts
75th Bulk Collect

Section 31: Merge Command
76. Merge Command
77. Merge Script Command

Section 32: Full Text Search
78. Oracle Text and Contains Content
79. Oracle Text and Contains

Section 33: DDL Triggers
80. Database DDL Trigger

Section 34: Utl_File
81. Utl_File Package
82. Utl_File

Section 35: Manipulating XML
83. Manipulating XML
84. Manipulating XML - Scripts
85. Manipulating XML - Practice

Section 36: Query Optimization
86. Query Optimisation
87. Query Optimisation - Practice

Section 37: Index Creation
88. Index Creation
89. Monitoring use of indexes
90. Monitoring the use of indexes - Practice

Section 38: Statistics
91. Statistics
92. Statistics - Scripts
93. Statistics - Practice

Section 39: Materialized Views
94. View Materialized Content
95. View Materialized Scripts
96. Materialized View - Practice

Section 40: Search Hints
97. Search Hints
98. Search Hints - Script
99. Research Hints - Practice