Database Architect Training - Combo Course
![Intellipaat](https://static.mytechlogy.com/cache/120eb50138d5e4ddec9e3d84afbf6e36_w30_h30_cp_sc.gif)
Intellipaat
Course Summary
Our database architect certificate master training program lets you gain proficiency in the Oracle database. We provide the best online training classes to help you learn Oracle PL/SQL scripting, data warehousing, data modelling, Erwin, SQL developer, SQL DBA. Work on real world projects.
-
+
Course Description
About Course
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.
List of courses in this combo pack:
- Oracle PL-SQL
- Oracle DBA
- Data Warehousing, Data Modeling, Erwin
- SQL Developer
- SQL DBA
What you will learn in this Training Course?
- Learn core database concepts and role of database administrator
- Study SQL, PL/SQL, client/server relation and MS SQL architecture
- Write SQL to query database, modify using T-SQL
- Learn ETL life-cycle, Data Modeling and Data Warehousing
- Better understand Erwin-Design Layer Architecture
- Work with Oracle and design, build, manage database applications
- Learn database performance, integrity and security management
- Learn about PL/SQL sections like Declaration, Execution, and Exception Handling
- Understand SQL command major groups – DDL, DML, DCL and TCL.
Who should take this Training Course?
- SQL Developers, Database Administrators, Software developers and BI professionals
- Project Managers, Business Analysts, Testing, Data Warehousing and Hadoop Professionals
What are the prerequisites for taking this Training Course?
There are no prerequisites for taking this Training Course.Why should you take 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.
-
+
Course Syllabus
Oracle PL SQL Course Content
Introduction to Oracle SQLWhat is RDBMS, Oracle versions, Architecture of Oracle Database Server, Installation of Oracle 12cHands-on Exercise – Install Oracle 12c
Using DDL Statements to Create and Manage TablesCategorize 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 workHands-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
Retrieving Data Using the SQL SELECT StatementList 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
Restricting and Sorting DataLimit 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 runtimeHands-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
General functionsThe 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
Using Single-Row Functions to Customize OutputDescribe various types of functions (character, number, date, string etc.) available in SQLHands-on Exercise – Create a table with columns of type char, number and date, Use character, number, and date functions in SELECT statements
Large Object FunctionsLarge object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB, Aggregate or Group functions – COUNT, COUNT(*),MIN,MAX,SUM,AVG,etc…,Group BY Clause, HAVING ClauseHands-on Exercise – Count records based on a condition, Use Count(*) to know the count of all records, Find Max, Min, Sum, Avg
OLAP FunctionsThe various OLAP functions, cube, model clause, roll up and grouping functionsHands-on Exercise – Working with OLAP commands – Cube, Roll Up, etc.
Using Conversion Functions and Conditional ExpressionsDescribe various types of conversion functions that are available in SQL, Conditional expressions in a SELECT statementHands-on Exercise – Group data by using the GROUP BY clause, Include or exclude grouped rows by using the HAVING clause
Displaying Data from Multiple TablesJoins, Inner Join, Outer Join, Left Join, Right Join, Equijoins and Non-equijoinsHands-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
Using Subqueries to Solve QueriesDefine subqueries, Describe the types of problems that the subqueries can solve, List the types of subqueriesHands-on Exercise – Write single-row and multiple-row subqueries
Using the Set OperatorsDescribe set operators, UNION [ALL], INTERSECT, MINUS OperatorsHands-on Exercise – Use a set operator to combine multiple queries into a single query Control the order of rows returned
Manipulating Data using SQLDescribe data manipulation language (DML) statement, Insert, Update, Delete Statements, Control transactionsHands-on Exercise – Insert rows into a table, Update rows in a table, Delete rows from a table
Database TransactionsWhat is a database transaction, Properties of a transaction (Atomic, Consistent, Isolated, Durable – ACID), Avoiding error/fault in manipulating database records using transactionHands-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
Creating Other Schema ObjectsViews – simple and complex, Sequences, Index, SynonymHands-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
Writing Cursor and Conditional StatementSQL 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
Introduction to Explicit CursorWriting Explicit cursors, About cursors, Explicit cursor functions, Controlling explicit cursor, Opening the cursor, Fetching Data cursor, Cursor and records, Cursor for loop using sub queriesHands-on Exercise – Write an explicit cursor, Use cursor function, Fetch data cursor
Advance concepts of explicit CursorAdvance Explicit cursor, Cursor with parameters, For update Clause, Where current of Clause, Cursor with sub queriesHands-on Exercise – Use cursor with subquery, Use Where Current clause to retrieve data
Exception HandlingHandling Exception, Handling Exception with PL/SQLPredefined Exceptions, User Defined Exceptions, Non-Predefined Error, Function for trapping Exception, Trapping user-defined Exception, Raise Application Error ProcedureHands-on Exercise – Use Predefined exception, Write user defined exception, Generate and handle exception, Use a function for trapping an exception
Writing Subprogram, Procedure and passing parametersOverview 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 SubprogramHands-on Exercise – Create a parameterized procedure, Pass parameters in a procedure call, Access a public variable from a standalone procedure
Creating PL/SQL PackagePL/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 packagesHands-on Exercise – Create a package, Deploy the created package
Advance Package Concepts and functionsOverloading, 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 linksHands-on Exercise – Use supplied package, Use native dynamic SQL, Use DBMS-SQL package, Use DBMS-DDL package, Submit a job
Introduction and writing TriggersTriggers – 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 triggersHands-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
Compound TriggersViewing 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 triggersHands-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
Working with Dynamic SQLDynamic 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 statementHands-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
Advance level- ScriptingManaging 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 proceduresHands-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
MS SQL Server DBA Course Content
Starting with SQL ServerResponsibilities 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 servicesInstalling SQL ServerPre-requisites, Installation of Server, Post Installation configuration and verificationHands-on Exercise – Install SQL Server on Linux, Install SQL Server on Windows, Configure the Server and verify that it is up and runningFunctioning of DatabasesDescribe 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 ScenarioHands-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 fileImporting and Exporting DataWhat is Import and Export of table data, Copy or Move a database, Tools and Techniques for data transferHands-on Exercise – Import a table from a saved database file in the workspace, Export data from workspace to save in a database fileSecurityAuthentication 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 FixingHands-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 userDatabase BackupsDatabase 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 tablesHands-on Exercise – Perform database backup, Check transaction log backup, Monitor space usage of Log files, Use backup storage tableRecovery and Restoration of DatabaseTypes 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 RecoveryHands-on Exercise – Set a recovery model, Perform point-in-time recovery, Perform restoration from the last saved stateMonitoring SQL Server 2014What is monitoring sql server, Dynamic Management Views (DMV), Dynamic Management Functions (DMF), Performance Monitor, Activity Monitor, Configure database mails, alerts and notificationHands-on Exercise – Create a DMV, Write Functions to monitor sql server activity dynamically, Configure mails, alerts and notification, Generate alerts and notification, Send emailTracing SQL Server ActivitySQL Server Profiler, SQL Trace stored procedures, Using Traces, Optimize SQL Server workloadsHands-on Exercise – Use SQL server profiler, Create trace stored procedures and run them Optimize workloads of serverAuditing Data Access and Encrypting DataSQL server audit feature, Implement audit, Audit data access, Implement encryptionHands-on Exercise – Implement audit, Audit data access, Implement encryptionDatabase MaintenanceCommon database maintenance tasks, Ensuring database integrity, Maintaining Indexes, Automating common tasksHands-on Exercise – Create index on table column, Update index after inserting new recordsAutomating SQL Server 2014 ManagementImplementing and Managing SQL Server Agent Jobs, Managing job on multiple serversHands-on Exercise – Implement Server Agent Jobs Manage Jobs on same serverOracle PL SQL ProjectProject – 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.
Oracle DBA ProjectProject – 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–
- Space usage monitoring
- SQL scripts monitoring
- Database & SQL tuning
- Table & Index Statistics
- Chained Rows & Locks
Data Warehouse ProjectProject Work
Project 1–Logical & Physical Data Modelling Using ERWin (Invoice Mgmt)
Data –Sales
Problem Statement –It creates a logical and physical data model using Erwin.
Project 2– End-to-End implementation of Data Warehouse (Retail Store)
Data –Sales
Problem Statement – It describes how to create and deploy a data warehouse. It also involves loading the data into data warehouse using ETL.
SQL ProjectProject – 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.
MS SQL Server DBA ProjectProject – 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.