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.
What is RDBMS, Oracle versions, Architecture of Oracle Database Server, Installation of Oracle 12c
Hands-on Exercise – Install Oracle 12c
Categorize the main database objects, Review the table structure, List the data types that are available for columns, Create a simple table, Explain how constraints are created at the time of table creation, Describe how schema objects work
Hands-on Exercise – Create a database table Person with two column (Name, Age) with constraint on age not greater than 100, Insert records using insert query
List the capabilities of SQL SELECT statements, Execute a basic SELECT statement
Hands-on Exercise – Use a basic select statement to retrieve all the records in the Person table
Limit the rows that are retrieved by a query, Sort the rows that are retrieved by a query, Ampersand substitution to restrict and sort output at runtime
Hands-on Exercise – Write a select query to retrieve records where age is more than 60 yrs, Write a select query to sort the records by name, Write a select query to sort the records by age in descending order, Use ampersand substitution to restrict and sort output at runtime
The general functions in SQL, working with any data type and handling Null values, using COALESCE() and Null function, Constructing and executing SQL query that applies the NUL, NUL1, NUL2 and COALESCE()
Hands-on Exercise – Use NULL function to deal with null values in data
Describe various types of functions (character, number, date, string etc.) available in SQL
Hands-on Exercise – Create a table with columns of type char, number and date, Use character, number, and date functions in SELECT statements
Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB, Aggregate or Group functions – COUNT, COUNT(*),MIN,MAX,SUM,AVG,etc…,Group BY Clause, HAVING Clause
Hands-on Exercise – Count records based on a condition, Use Count(*) to know the count of all records, Find Max, Min, Sum, Avg
The various OLAP functions, cube, model clause, roll up and grouping functions
Hands-on Exercise – Working with OLAP commands – Cube, Roll Up, etc.
Describe various types of conversion functions that are available in SQL, Conditional expressions in a SELECT statement
Hands-on Exercise – Group data by using the GROUP BY clause, Include or exclude grouped rows by using the HAVING clause
Joins, Inner Join, Outer Join, Left Join, Right Join, Equijoins and Non-equijoins
Hands-on Exercise – Write SELECT statements to access data from more than one table using equijoins and non equijoins, Join a table to itself by using a self-join, View data that generally does not meet a join condition by using outer joins, Generate a Cartesian product of all rows from two or more tables
Define subqueries, Describe the types of problems that the subqueries can solve, List the types of subqueries
Hands-on Exercise – Write single-row and multiple-row subqueries
Describe set operators, UNION [ALL], INTERSECT, MINUS Operators
Hands-on Exercise – Use a set operator to combine multiple queries into a single query Control the order of rows returned
Describe data manipulation language (DML) statement, Insert, Update, Delete Statements, Control transactions
Hands-on Exercise – Insert rows into a table, Update rows in a table, Delete rows from a table
What is a database transaction, Properties of a transaction (Atomic, Consistent, Isolated, Durable – ACID), Avoiding error/fault in manipulating database records using transaction
Hands-on Exercise – Begin a transaction, Execute queries to update or insert or delete records, If no error, commit the transaction Else roll back the transaction and end it
Views – simple and complex, Sequences, Index, Synonym
Hands-on Exercise – Create simple and complex views, Retrieve data from views, Create, maintain, and use sequences, Create and maintain indexes, Create private and public synonyms
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.
Hands-on Exercise – Use Boolean condition on a select query, Use logic table, Create nested loops and labels
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
Hands-on Exercise – Write an explicit cursor, Use cursor function, Fetch data cursor
Advance Explicit cursor, Cursor with parameters, For update Clause, Where current of Clause, Cursor with sub queries
Hands-on Exercise – Use cursor with subquery, Use Where Current clause to retrieve data
Handling Exception, Handling Exception with PL/SQLPredefined Exceptions, User Defined Exceptions, Non-Predefined Error, Function for trapping Exception, Trapping user-defined Exception, Raise Application Error Procedure
Hands-on Exercise – Use Predefined exception, Write user defined exception, Generate and handle exception, Use a function for trapping an exception
Overview of subprograms, PL/SQL Subprograms, What is Procedure, Syntax for creating Procedure, Creating Procedure with parameter, Example of Passing parameters, Referencing a public variable from a standalone procedure, Declaring Subprogram
Hands-on Exercise – Create a parameterized procedure, Pass parameters in a procedure call, Access a public variable from a standalone procedure
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, Guide lines for deploying packages
Hands-on Exercise – Create a package, Deploy the created package
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
Hands-on Exercise – Use supplied package, Use native dynamic SQL, Use DBMS-SQL package, Use DBMS-DDL package, Submit a job
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
Hands-on Exercise – Create a DML statement trigger, Use old and new qualifiers, Manage a trigger using the alter & drop SQL statement, Test the created 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
Hands-on Exercise – View a trigger’s information, Use a compound trigger structure for views, Use a compound trigger to resolve the mutating table error, Create triggers on system events, Use LOGON and LOGOFF triggers, Call statements in triggers
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
Hands-on Exercise – Use the executive immediate statement, Use native dynamic SQL to compile PL/SQL code, Create DBMS-SQL with a DML statement, Create a DBMS-SQL package subprograms
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 re-validation, Concepts of remote dependencies, Setting the remote dependencies mode parameter, Recompiling PL/SQL program unit, Packages and Dependencies, Successful and unsuccessful recompilation, Recompiling procedures
Hands-on Exercise – Query direct object dependencies, Display direct and indirect dependencies, Set the remote dependencies mode parameter, Recompile PL/SQL program unit, Edit a procedure and recompile it
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
Pre-requisites, Installation of Server, Post Installation configuration and verification
Hands-on Exercise – Install SQL Server on Linux, Install SQL Server on Windows, Configure the Server and verify that it is up and running
Describe Database, Types of Database and Brief explanation, System Databases, User Database, Sql Server Database Architecture, Pages, Extents, File groups, Transaction Architecture, Creating Database, Modifying Database, Adding Files, Moving and renaming of Database files, Database modes, Real time Scenario
Hands-on Exercise – Create a database schema in SQL Server, Create a table to store details of a student personal information, Add dummy data to the table, Move database file to another location, Rename database file
What is Import and Export of table data, Copy or Move a database, Tools and Techniques for data transfer
Hands-on Exercise – Import a table from a saved database file in the workspace, Export data from workspace to save in a database file
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
Hands-on Exercise – Use SQL Server Management Studio, Create a login, Create database users, Assign different roles (owner, reader, accessadmin, securityadmin, denydatawriter, denydatareader), Provide permission level and access level, Create an orphan user, Find the orphan user
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
Hands-on Exercise – Perform database backup, Check transaction log backup, Monitor space usage of Log files, Use backup storage table
Types of Recovery Models, Full, Bulk Logged, Simple, Differences between Recovery Models, Setting Recovery Models according to the Scenarios with examples, Performing Restorations, Types of Restoration, Attach and Detach, Shrinking files, Point-in-Time Recovery
Hands-on Exercise – Set a recovery model, Perform point-in-time recovery, Perform restoration from the last saved state
What is monitoring sql server, Dynamic Management Views (DMV), Dynamic Management Functions (DMF), Performance Monitor, Activity Monitor, Configure database mails, alerts and notification
Hands-on Exercise – Create a DMV, Write Functions to monitor sql server activity dynamically, Configure mails, alerts and notification, Generate alerts and notification, Send email
SQL Server Profiler, SQL Trace stored procedures, Using Traces, Optimize SQL Server workloads
Hands-on Exercise – Use SQL server profiler, Create trace stored procedures and run them Optimize workloads of server
SQL server audit feature, Implement audit, Audit data access, Implement encryption
Hands-on Exercise – Implement audit, Audit data access, Implement encryption
Common database maintenance tasks, Ensuring database integrity, Maintaining Indexes, Automating common tasks
Hands-on Exercise – Create index on table column, Update index after inserting new records
Implementing and Managing SQL Server Agent Jobs, Managing job on multiple servers
Hands-on Exercise – Implement Server Agent Jobs Manage Jobs on same server
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
– 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
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
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.
Intellipaat offers comprehensive database architect training. This is an industry designed training course that includes the Oracle Pl/SQL, DBA, data warehousing, data modeling among other things. You will gain proficiency in database administration, and SQL development. The entire course is in line with clearing the two certifications of Oracle PL/SQL Developer Certified Associate, Oracle Database Administrator Certified Associate.
You will be working on real time projects that have high relevance in the corporate world, step by step assignments and curriculum designed by industry experts. Upon completion of the training course you can apply for some of the best jobs in top MNCs around the world at top salaries. Intellipaat offers lifetime access to videos, course materials, 24/7 Support, and course material upgrading to latest version at no extra fees. Hence it is clearly a one-time investment
Intellipaat basically offers the self-paced training and online instructor-led training. Apart from that we also provide corporate training for enterprises. All our trainers come with over 12 years of industry experience in relevant technologies and also they are subject matter experts working as consultants. You can check about the quality of our trainers in the sample videos provided.
If you have any queries you can contact our 24/7 dedicated support to raise a ticket. We provide you email support and solution to your queries. If the query is not resolved by email we can arrange for a one-on-one session with our trainers. The best part is that you can contact Intellipaat even after completion of training to get support and assistance. There is also no limit on the number of queries you can raise when it comes to doubt clearance and query resolution.
The Intellipaat self-paced training is for people who want to learn at their own leisurely pace. As part of this program we provide you with one-on-one sessions, doubt clearance over email, 24/7 Live Support, lifetime LMS and upgrade to the latest version at no extra cost. The prices of self-paced training can be 75% lesser than online training. While studying should you face any unexpected challenges then we shall arrange a Virtual LIVE session with the trainer.
We provide you with the opportunity to work on real world projects wherein you can apply your knowledge and skills that you acquired through our training. We have multiple projects that thoroughly test your skills and knowledge of various aspect and components making you perfectly industry-ready. These projects could be in exciting and challenging fields like banking, insurance, retail, social networking, ecommerce, marketing, sales, high technology and so on. The Intellipaat projects are equivalent to six months of relevant experience in the corporate world.
Yes, Intellipaat does provide you with placement assistance. We have tie-ups with 80+ organizations including Ericsson, Cisco, Cognizant, TCS, among others that are looking for skilled & quality professionals and we would be happy to assist you with the process of preparing yourself for the interview and the job.
Yes, if you would want to upgrade from the self-paced training to instructor-led training then you can easily do so by paying the difference of the fees amount and joining the next batch of classes which shall be separately notified to you.
Upon successful completion of training you have to take a set of quizzes, complete the projects and upon review and on scoring over 60% marks in the qualifying quiz the official Intellipaat verified certificate is awarded.
The Intellipaat Certification is a seal of approval and is highly recognized in 80+ corporations around the world including many in the Fortune 500 list of companies.
This course is designed for clearing the two exams.
The entire training course content is in line with these certification programs and helps you clear it with ease and get the best jobs in the top MNCs. As part of this training you will be working on real time projects and assignments that have immense implications in the real world industry scenario thus helping you fast track your career effortlessly.
At the end of this training program there will be quizzes that perfectly reflect the type of questions asked in the respective certification exams and helps you score better marks in certification exam.
Intellipaat Course Completion Certification will be awarded on the completion of Project work (on expert review) and upon scoring of at least 60% marks in the quiz. Intellipaat certification is well recognized in top 80+ MNCs like Ericsson, Cisco, Cognizant, Sony, Mu Sigma, Saint-Gobain, Standard Chartered, TCS, Genpact, Hexaware, etc.
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.
"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