This is a Combo Training Course that provides you with all the necessary skills to work with database systems, data warehousing and data modeling. You will gain considerable mastery in database administration, working with SQL and so on.
There are no prerequisites for taking this Training Course.
This Combo Training Course has been developed keeping in mind the requirements of database professionals as well as beginners. Since this is a 5-in-1 elaborate Combo Course, upon completion of the course you can look for jobs in any of the five domains. This will help you land high-paying jobs in major multinationals.
Topics: Understanding of PL/SQL – SQL with procedural features of programming, importance of PL/SQL, Variables, Types and Declaration, Data types and Complex Objects, familiarizing with the PL/SQL Environment.
Topics: Deep dive into PL/SQL, PL/SQL Block syntax and guidelines, PL/SQL subtypes and conversions, nested blocks and variable scope, understanding how to update and retrieve data in PL/SQL, naming conventions.
Topics: SQL Cursor, SQL Cursor Attributes., Controlling PL/SQL flow of executions, IF Statement, Simple IF Statement, IF-THAN-ELSE Statement Execution Flow, IF-THAN-ELSE Statement, IF-THAN-ELSIF Statement, Logic Tables, Boolean Conditions., Iterative controlling loop statement, Nested Loops and Labels.
Topics: Writing Explicit cursors, About cursors, Explicit cursor functions, Controlling explicit cursor, Opening the cursor, Fetching Data cursor, Cursor and records, Cursor for loop using sub queries
Topics: Advance Explicit cursor, Cursor with parameters, For update Clause, Where current of Clause, Cursor with sub queries, Handling Exception, Handling Exception with PL/SQL, Predefined Exception
Topics: Predefined Exceptions, User Defined Exceptions, Non-Predefined Error, Function for trapping Exception, Trapping user-defined Exception, Raise Application Error Procedure
Topics: Overview of subprograms, PL/SQL Subprograms, What is Procedure, Syntax for creating Procedure, Creating Procedure with parameter, Example of Passing parameters, Declaring Subprogram
Topics: Procedures – Developing procedures, removing procedures, Benefits of subprograms, Procedural Parameter Modes, Actual vs. Formal Parameters, Methods for passing parameters, Function – Creation, overview, syntax, Restrictions on calling from SQL, Advantages of user defined functions in SQL expressions, Comparing procedures and functions, Benefits of stored procedures and functions, Detecting compilation errors
Topics: PL/SQL records, Using Pl/SQL Table method and example, Creating PL/SQL Table, Packages – Objective, overview, component, developing, removing, advantages, Creating the package specification/example, Declaring Public construct, Public and private construct, Invoking package construct, Referencing a public variable from a standalone procedure, Guide lines for deploying packages
Topics: Overloading, Using forward declaration, One time only procedure, Package functions, User define package function, Persistent state of package function, Persistent state of package variable, Controlling the persistent state of package cursor, Purity end, Using supplied package, Using native dynamic SQL, Execution flow, Using DBMS-SQL package, Using DBMS-DDL package, Submitting jobs, Interacting with operating system links
Topics: Triggers – Definition, objective and its event type, Application & database triggers, Business application scenarios for implementing triggers, Define DML triggers, Define Non – DML triggers, Triggers event type & body, Creating DML triggers using the create triggers statement, Define statement level triggers v/s low level triggers, Triggers firing sequence: single row manipulation, Creating a DML statement triggers, Using old and new qualifiers, Old and new qualifiers, Instead of triggers, Managing triggers using the alter & drop SQL statement, Testing triggers
Topics: Testing triggers, Viewing trigger information, Describe user triggers, What is a compound trigger and working with it, Compound trigger structure for tables, Timing-point sections of a table compound trigger, Compound trigger structure for views, Trigger restrictions on mutating tables, Compound trigger restrictions, Using a compound trigger to resolve the mutating table error, Creating triggers on system events, LOGON and LOGOFF triggers example, Call statements in triggers, Benefits of database-event triggers, System privileges required to manage triggers
Topics: Dynamic SQL-objectives, Describe execution flow of SQL statements, Dynamic SQL with a DDL statement-example, Working with dynamic SQL, Native Dynamic SQL(NDS), Using the executive immediate statement-example, Using native dynamic SQL to compile PL/SQL code, Using DBMS-SQL with a DML statement, Using the DBMS-SQL package subprograms, Parameterized DML statement
Topics: Managing Dependencies, Objectives, overview of schema object dependencies, Direct local dependencies, Querying direct object dependencies, Displaying direct and indirect dependencies, Fine-Grained dependency management, Changes to synonym dependencies, Maintaining valid PL/SQL program units and views, Object revalidation, Concepts of remote dependencies, Setting the remote dependencies mode parameter, Recompiling PL/SQL program unit, Packages and Dependencies, Successful and unsuccessful recompilation, Recompiling procedures.
Topics: What is the Oracle database? How it functions? Installation of Oracle database 11g.
Topics: The Architecture of Oracle server, the key components – Oracle Instance and Oracle Database, the various file types – data files, control files, redo log files.
Topics: The basics of Oracle Storage structures, logical storage units like tablespaces, data blocks, extents and segments, database Schemas, Schema Objects, Operating System blocks, table statement and way to check “create table”.
Topics: The Oracle Instance consists of background processes and memory structures, learn about the Oracle process and memory architecture, shared pool, redo log buffer, and buffer cache.
Topics: The Oracle background process errors can be monitored using the Trace Files and Alert Logs, learn how this can be deployed for Oracle Administration and support.
Topics: The methodology of starting a database instance, initializing parameter files, preparing for startup, serving user requests, understanding of the Server process and user process.
Topics: Learn about the powerful Oracle database security features, data privacy, regulatory compliance, “create user” process, altering and dropping users, generating profiles and limiting resources, auditing, activity monitoring and blocking.
Topics: Understanding of Database Schema objects, the various types available like views, tables, clusters, indexes, sequence, database links, packages and procedures, learning how data is stored in database tables, creating of temporary tables and external tables.
Topics: Mastering the database Schema objects, learning Materialized View, deploying user-generated schema object for generating sequence, the balanced search tree index structure for placing and locating files, learn about data concurrency and data consistency in multi-user databases, the concepts of Locking and Deadlocks.
Topics: Study the client/server Network Environment, configuring the network, connecting to the database, the Oracle Network Environment and database link.
Topics: Understand how Oracle database ecosystem backup and recovery is deployed, the database testing, database standby, Media recovery options, backup in offline mode.
Topics: Learn the significance of Oracle Recovery Manager, how it deploys performance, management and restoration of entire databases, learn about SQL Loader, Oracle Data Pump for import and export and support for External Table.
Topics: The Oracle Data Dictionary contains information related to database privileges, objects, users, and roles, learn Dynamic Performance Table for identifying instance-level problems, get complete knowledge to work with Oracle DBA.
Topics: Introduction to database tuning, ensuring higher throughput, lowering response time of time-critical applications, optimizing the storage space, SQL scripts, deploying Index Selectivity, Index Statistics and Table Statistics for tuning the database, understanding Oracle Chained Rows and Locks.
Topics: Deep diving into Oracle Database Tuning, shared pool tuning, improving performance using Data Dictionary, tuning of Data Dictionary, deploying PL/SQL code, code reuse, database buffer.
Topics – Introducing Data Warehouse and Business Intelligence, understanding difference between database and data warehouse, working with ETL tools, SQL parsing.
Topics – Understanding the Data Warehousing Architecture, system used for Reporting and Business Intelligence, understanding OLAP vs. OLTP, introduction to Cubes.
Topics – The various stages from Conceptual Model, Logical Model to Physical Schema, Understanding the Cubes, benefits of Cube, working with OLAP multidimensional Cube, creating Report using a Cube.
Topics – Understanding the process of Data Normalization, rules of normalization for first, second and third normal, BCNF, deploying Erwin for generating SQL scripts.
Topics – The main components of Business Intelligence – Dimensions and Fact Tables, understanding the difference between Fact Tables & Dimensions, understanding Slowly Changing Dimensions in Data Warehousing.
Topics – SQL parsing, compilation and optimization, understanding types and scope of cubes, Data Warehousing Vs. Cubes, limitations of Cubes and evolution of in-memory analytics.
Topics – Learning the Erwin model, understanding the Design Layer Architecture, data warehouse modeling, creating and designing user defined domains, managing naming and data type standards.
Topics – Understanding of the forward and reverse engineering, comparison between the two.
Topics: The various types of databases, introduction to Structured Query Language, distinction between client server and file server databases, understanding SQL Server Management Studio, SQL Table basics, data types and functions, Transaction-SQL, authentication for Windows, Data control language, Identification and Keywords in T-SQL, Drop Table.
Topics: Introduction to relational databases, basic concepts of relational tables, working with rows and columns, various operators used like logical and relational, domains, constraints, stored procedures, indexes, primary key and foreign key, understanding group functions, unique key.
Topics: Deep dive into SQL Tables, working with SQL functions, operators and queries, creation of tables, retrieving data from tables, combining rows from tables using Inner, Outer, Cross and Self joins, deploying Operators like Union, Intersect, Except, creation of Temporary Table, Set Operator rules, working with Table variables.
Topics: Understanding what SQL functions do, aggregate functions, scalar functions, functions that work on different data sets like numbers, characters & strings, dates, learning Inline SQL functions, general functions and duplicate functions.
Topics: Understanding of SQL Subqueries, rules of Subqueries, the statements and operators with which Subqueries can be used, modification of sub queries using set clause, understanding the different types of Subqueries – Where, Insert, Update, Select, Delete, etc., ways to create and view Subqueries.
Topics: Learning about SQL Views, ways of creating, using, altering, dropping, renaming and modifying Views, understanding Stored Procedures, key benefits of it, working with Stored Procedures, error handling, studying user-defined functions.
Topics: Detailed study of user-defined functions, various types of UDFs like Scalar, Inline Table Value, multi-statement Table, what are Stored Procedures? when to deploy Stored Procedures? What is Rank Function? Triggers, when to execute Triggers?
Topics: Detailed understanding of SQL Server Management Studio, learning what is Pivot in Excel and SQL Server, XL path, differentiating between Char, Varchar and NVarchar, working with Indexes, creation of Index, advantages, records grouping, searching, sorting, modifying data, creation of clustered indexes, using index to cover queries, index guidelines and Common Table Expression.
Topics: Responsibilities of Database Administrator, Types of DBAs, History of SQL Server, Editions of SQL Server, Tools of SQL Server, Differences between Standard and Enterprise editions, Instances types in SQL Server, Default Instance, Named Instance, SQL Server Services, Instance aware services, Instance unaware services
Topics: Pre-requisites, Installation, Post Installation verification Session
Topics: Database, Types of Database and Brief explanation, System Databases, User Database, Database Architecture, Pages, Extents, File groups, Database, Transaction Architecture, Creating Database, Modifying Database, Adding Files, Moving and renaming of Database files, Database modes, Real time Scenario
Topics: Authentication Types in SQL Server, Types of Login, Windows Login, SQL Login, Creating Users and Logins, Server roles, Password policy, Understanding Database and Server Roles, Permissions, Working on access level issues, Orphan users Finding and Fixing, Important Queries
Topics: Database Backups, Why we need backups, Types of Backup, Full Backup, Differential Backup, Transaction Log Backup, Copy-only, Mirrored, Split and Tail log Backups, Differences between backups, Backup Strategy, Understanding how the data moving from Log to Data File,CHECKPOINT, Monitoring the space usage of Log File and fixing, Checking the backup files VALID or CORRUPTED, Backup storage tables, Important Queries, Performing Restorations, Types of Restoration, Attach and Detach, Shrinking files
Topics: Types of Recovery Models, Full, Bulk Logged, Simple, Differences between Recovery Models, Setting Recovery Models according to the Scenarios with examples
Project – DML
Topics – In this project you will learn about the Data Manipulation Language (DML), understand all about the DML triggers which are a special type of stored procedures that are automatically executed when DML operation is performed. You will learn about the various DML operations like INSERT, UPDATE, or DELETE which when fired on a Table or View can activate a DML Trigger. This project involves working with database and application Triggers, deploying the Triggers for various business applications and testing of the DML Triggers.
Project – Database Tuning
Topics – This project is involved with working on the Oracle Database for deploying the database performance tuning which includes optimizing and homogenizing the database performance. It is associated with query tuning but also includes DBMS selection, and configuration of the database environment. Some of the tasks that you will perform are–
Project 1–Logical & Physical Data Modelling Using ERWin (Invoice Mgmt)
Problem Statement –It creates a logical and physical data model using Erwin.
Project 2– End-to-End implementation of Data Warehouse (Retail Store)
Problem Statement – It describes how to create and deploy a data warehouse. It also involves loading the data into data warehouse using ETL.
Project – Writing complex Sub Queries
Topics – This project will give you hands-on experience in working with SQL sub-queries and utilizing it in various scenarios. Some of the sub-queries that you will be working with and gain hands-on experience in are – IN or NOT IN, ANY or ALL, EXISTS or NOT EXISTS, and other major queries.
Project – SQL Server Audit
Topics – This project is involved with implementing an SQL Server audit that includes creating of the TestDB database, triggering audit events from tables, altering audit, checking, filtering, etc. You will learn to audit an SQL Server instance by tracking and logging the events on the system. You will work with SQL Server Management; learn about database level and Server level auditing.
In Intellipaat self-paced training program you will receive recorded sessions, course material, Quiz,related software’s and assignments.The courses are designed such that you will get real world exposure and focused on clearing relevant certification exam. After completion of training you can take quiz which enable you to check your knowledge and enables you to clear relevant certification at higher marks/grade also you will be able to work on the technology independently.
In Self-paced courses trainer is not available whereas in Online training trainer will be available for answering queries at the same time. In self-paced course we provide email support for doubt clearance or any query related to training also if you face some unexpected challenges we will arrange live class with trainer.
All Courses are highly interactive to provide good exposure. You can learn at your own place and at your leisure time. Prices of self-paced is training is 75% cheaper than online training. You will have lifetime access hence you can refer it anytime during your project work or job.
Yes, at the top of the page of course details you can see sample videos.
As soon as you enroll to the course, your LMS (The Learning Management System) Access will be Functional. You will immediately get access to our course content in the form of a complete set of previous class recordings, PPTs, PDFs, assignments and access to our 24×7 support team. You can start learning right away.
24/7 access to video tutorials and Email Support along with online interactive session support with trainer for issue resolving.
Yes, You can pay difference amount between Online training and Self-paced course and you can be enrolled in next online training batch.
Yes, we will provide you the link from where you can download the required software’s.
Please send an email . You can also chat with us to get an instant solution.
Intellipaat verified certificates will be awarded based on successful completion of course projects. There are set of quizzes after each couse module that you need to go through . After successful submission, official Intellipaat verified certificate will be given to you.
Towards the end of the Course, you will have to work on a Training project. This will help you understand how the different components of course are related to each other.
Classes are conducted via LIVE Video Streaming, where you get a chance to meet the instructor by speaking, chatting and sharing your screen. You will always have the access to videos and PPT. This would give you a clear insight about how the classes are conducted, quality of instructors and the level of Interaction in the Class.
Yes, We do keep launching multiple offers, please see offer page.
We will help you with the issue and doubts regarding the course. You can attempt the quiz again.
At the end of the course there will be a quiz and project assignments once you complete them you will be awarded with Intellipaat Course Completion certificate.
You will get Lifetime access to high quality interactive tutorials along with life time access to complete Course Material .There will be 24/7 access to video tutorials with email support. If you stuck in any unexpected problem we will provide online interactive sessions with trainer for issue resolving.
We provide 24X7 support by email for issues or doubts clearance for Self-paced training.
In online Instructor led training, trainer will be available to help you out with your queries regarding the course. If required, the support team can also provide you live support by accessing your machine remotely. This ensures that all your doubts and problems faced during labs and project work are clarified round the clock.
At the end of the course there will be a quiz and project assignments once you complete them you will be awarded with Intellipaat Course Completion certificate.
"PMI®", "PMP®" and "PMI-ACP®" are registered marks of the Project Management Institute, Inc.
The Open Group®, TOGAF® are trademarks of The Open Group.
The Swirl logoTM is a trade mark of AXELOS Limited.
ITIL® is a registered trade mark of AXELOS Limited.
PRINCE2® is a Registered Trade Mark of AXELOS Limited.
Certified ScrumMaster® (CSM) and Certified Scrum Trainer® (CST) are registered trademarks of SCRUM ALLIANCE®
Professional Scrum Master is a registered trademark of Scrum.org