Mastering PL/SQL
Created by Dr. Parteek Bhatia, Last Updated 03-Nov-2020, Language:English
Mastering PL/SQL
Mastering PL/SQL
Created by Dr. Parteek Bhatia, Last Updated 03-Nov-2020, Language:English
What Will I Get ?
- Basics of PL/SQL
- Variables and Constants
- Conditional, Looping and Sequential Statements
- Exception Handling
- Cursor Handling
- Sub-Programs, Functions and Procedures
- Triggers
Requirements
- SQL
Description
In modern world, no application is possible without involving the use of database. There is a growing demand for database experts all over the world. And this course is intended to fill this gap. This course on PL/SQL has been specially designed to better equip the learners for their academic and professional career.
This course is aimed at beginners, and it will guide you on a wonderful journey to transform you from naïve to expert of PL/SQL. In this course, you'll learn the PL/SQL using Oracle, i.e., the most widely used Database Software in the world.
In this course you will learn how to use PL/SQL statements to implement business logic with the use of Cursors, Procedures, Functions and Triggers. All these concepts are covered through lot of examples to make the learning fun and exciting. The main strength of the course is explanation with the practical examples so that learners can grasp the contents easily.
Overview of Course Contents:
Section-1: Basics of PL/SQL
· Introduction to PL/SQL
· Architecture of PL/SQL
· PL/SQL Language Elements
· Structure of PL/SQL Block
Section-2: Variables and Constants
· Variables in PL/SQL
· Select into Statement of PL/SQL
· Constants in PL/SQL
· Variable Attributes
Section-3: Conditional, Looping and Sequential Statements
· Conditional Statements
· Simple Loop
· While Loop
· For Loop
· Sequential Control
· Assignments and Its Solution
Section-4: Exception Handling
· Types of Exceptions
· Handling of Predefined and Non-Predefined Oracle Server Errors
· Explicit Exceptions
· Error Trapping Functions
· Exceptions raised in Declare Section
· Propagation of Exceptions
· Re-raising of Exceptions
· Demonstrations
· Assignment and Its Solution
Section-5: Cursor Handling
· Concept of Cursor
· Implicit Cursor
· Explicit Cursor
· Demonstrations
· Cursor For Loop
· Cursor with Parameters
· Cursor Assignments and Its Solutions
Section-6: Sub-Programs, Functions and Procedures
· Sub Programs & Its Types
· Local Functions
· Stored Functions
· Demonstrations
· Local Procedures
· Stored Procedures
· Assignments & Its Solutions
Section 7: Trigger
· Concept of Trigger
· Uses and Applications of Trigger
· Types of Trigger
· Before Statement & Row Level Trigger
· After Statement & Row Level Trigger
· Syntax of Trigger
· Old and New Keywords
· Raising Application Errors
· Assignments & Its Solutions
Strengths of Course
· More than 10 Hours of Video Contents
· More than 70 Video Sessions
· Enough Reading Material to Brain Map the Concepts
· Quizzes after each section to check your progress
· Every Section has Assignments to have learning by doing pedagogy
· Excellent course to prepare for DBMS
· Excellent way to become expert of PL/SQL before placement and Interviews
Course Content
-
Basics of PL/SQL
5 Lectures 00:23:19-
PL/SQL Introduction
Preview00:07:29 -
Architecture of PL/SQL
00:03:27 -
PL/SQL Language Elements
00:05:26 -
Structure of PL/SQL Block
00:02:54 -
Demonstartion of Live SQL (Software used for execution of code)
Preview00:04:03
-
-
Section2:
5 Lectures 00:33:19-
Variables in PL/SQL
00:06:01 -
Select into Statement
00:10:22 -
Constants in PL/SQL
00:01:49 -
Variable Attributes Part-1
00:06:34 -
Variable Attributes Part-2
Preview00:08:33
-
-
Conditional, Looping and Sequential Statements.
8 Lectures 00:47:51-
Conditional Statements of PL/SQL
00:09:16 -
Simple Loop Statement of PL/SQL
00:07:04 -
While Loop Statement of PL/SQL
00:07:20 -
For Loop Statement of PL/SQL
00:09:03 -
Sequential Control Statements of PL/SQL
00:09:43 -
Assignment Based on For Loop
00:01:20 -
Solution of the Assignment
00:04:05 -
Reading Material for Section 3
-
-
Exception Handling
11 Lectures 01:34:13-
Concept of Exception Handling and Types of Exceptions
00:06:26 -
Implicit Exceptions: Trapping of Predefined Oracle Server Errors
00:12:16 -
Demonstration of Predefined Oracle Server Errors
Preview00:14:53 -
Demonstration of Trapping of Non-Predefined Oracle Server Errors
00:08:20 -
Explicit or User Defined Exceptions
00:09:41 -
Demonstration of Explicit or User Defined Exceptions
00:07:04 -
Error Trapping Functions
00:10:47 -
Handing of Errors Raised in Declare Section and Propagation of Exceptions
00:09:30 -
Re-raising of Exceptions
00:04:41 -
Assignment on Exception Handling
00:02:50 -
Solution of Assignment on Exception Handling
00:07:45
-
-
Cursor Handling
13 Lectures 01:39:42-
Concept of Cursor
00:03:15 -
Concept of Implicit Cursor
00:07:22 -
Demonstration of Implicit Cursor
00:11:31 -
Concept of Explicit Cursor
00:06:14 -
Need and Usage of Explicit Cursor
Preview00:08:19 -
Demonstration of Explicit Cursor
00:07:04 -
Cursor For Loop: Concept and Demonstration
Preview00:10:48 -
Cursor with Parameters
00:08:37 -
Demonstration of Cursor with Parameters
00:06:04 -
Cursor Assignment-1
00:01:39 -
Solution of Cursor Assignment-1
00:11:55 -
Cursor Assignment-2
00:03:52 -
Solution of Cursor Assignment-2
00:13:02
-
-
Sub Programs
14 Lectures 02:10:21-
Sub Programs and and Its Types
00:06:22 -
Local Function: Concept and Usage
00:12:45 -
Demonstration of Local Functions
00:14:17 -
Local Procedures
00:04:15 -
Stored Functions
00:07:16 -
Demonstration of Stored Functions
00:14:50 -
Demonstration of Local Procedures
00:11:25 -
Stored Procedures
00:05:11 -
Demonstration of Stored Procedures
Preview00:14:50 -
Sub Program Assignment-1
00:03:03 -
Solution of Sub Program Assignment-1
00:12:52 -
Sub Program Assignment-2
00:04:19 -
Solution Sub Program Assignment-2 Question-1
00:11:11 -
Solution of Sub Program Assignment-2 Question-2
00:07:45
-
-
Trigger
17 Lectures 02:16:42-
Concept of Trigger
00:05:32 -
Uses and Applications of Database Trigger
00:13:33 -
Types of Trigger
00:04:09 -
Before Statement Level and Row Level Trigger
00:11:13 -
After Statement Level and Row Level Trigger
00:09:29 -
Syntax of Trigger
00:10:59 -
Concept of Old and New Keywords
00:11:47 -
Trigger Demonstartion Session-1
Preview00:12:54 -
Trigger Demonstartion Session-2
00:09:32 -
Trigger Demonstartion Session-3
00:12:16 -
Raising Application Errors with Triggers
00:10:15 -
Demonstartion of Raising Application Errors
00:07:41 -
Trigger Assignment-1 Pending
-
Solution of Trigger Assignment-1
00:13:41 -
Trigger Assignment-2
00:03:41 -
Reading Material for Sub Programs, Functions and Procedures
-
Reading Material for Trigger
-

Dr. Parteek Bhatia
Simplifying Computer Education
Dr. Parteek Bhatia is Associate Professor in the Department of Computer Science and Engineering and Former Associate Dean of Student Affairs at Thapar Institute of Engineering and Technology, Patiala. He is currently on sabbatical for Post doctorate at Tel Aviv University, Tel Aviv, Israel.
He is recipient of Young Faculty Research Fellowship from Ministry of Electronics & Information Technology, Govt. of India. He has more than twenty years of academic experience, including six years at D.A.V College Amritsar.
He has earned his B.Tech from SLIET and MS from BITS Pilani. He is doctorate in the field of NLP from Thapar University. He is PhD title is “UNL Based Machine Translation System for Punjabi Language”.
He has published more than 100 research papers and articles in Journals, Conferences and Magazines of repute. His research work with UNDL foundation, Geneva, Switzerland involved participation in Advanced UNL School at Alexandria, EGYPT in 2012 and at Geneva, Switzerland in 2013 and 2014.
He is a winner of Gold Medal at International competition UNL Olympiad II,UNL Olympiad III and UNL Olympiad IV conducted by UNDL Foundation in year 2013 and 2014.
He is well known author and published text books in the field of Databases and Data Mining. His latest book titled “Data Mining and Data Warehousing: Principles and Practical Techniques” Published by Cambridge University Press in 2019. He is a author of popular books like “Simplified Approach to DBMS”, “Simplified Approach to Visual Basic” and “Simplified Approach to Oracle” and “NoSQL in a Day”.
He has handled research projects of more than one crore rupees sponsored by International and National funding agencies. He is acting as Principal Investigator of DST Funded research project on “Automatic Generation of Sign Language from Hindi Text for Communication and Education of Hearing Impaired people”.
He has completed Royal Academy of Engineering (UK) funded research project on “Innovative Research in Pedagogy with Mini-MOOCs blended with instruction strategies to enhance quality in Higher Education” as Co-PI. He has successfully completed research Project on Development of Indradhanush: An Integrated WordNet for Bengali, Gujarati, Kashmiri, Konkani, Oriya, Punjabi and Urdu Sponsored By Department of Information Technology, Ministry of Communication and Information Technology, Govt. of India.
He is passionate about teaching masses through modern platforms like MOOCs. He has more than 15,000 students on his very popular Udemy Course on “Learning SQL: A Simplified Approach”. He is also running his YouTube Channel “Parteek Bhatia: Simplifying Computer Education” for sharing video sessions on Machine Learning, Big Data, DBMS, SQL, PL/SQL and NoSQL.
He has research interest in area of NLP, Machine Learning and Assistive Technologies. He is member of IEEE, ACM and ISTE.