MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

Database Architect Training - Combo Course

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 Syllabus

    Oracle PL SQL Course Content

    Introduction to Oracle SQL
    What is RDBMS, Oracle versions, Architecture of Oracle Database Server, Installation of Oracle 12c

    Hands-on Exercise – Install Oracle 12c

    Using DDL Statements to Create and Manage Tables
    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

    Retrieving Data Using the SQL SELECT Statement

    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

    Restricting and Sorting Data
    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

    General functions
    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

    Using Single-Row Functions to Customize Output
    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
    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

    OLAP Functions
    The various OLAP functions, cube, model clause, roll up and grouping functions

    Hands-on Exercise – Working with OLAP commands – Cube, Roll Up, etc.

    Using Conversion Functions and Conditional Expressions
    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

    Displaying Data from Multiple Tables
    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

    Using Subqueries to Solve Queries
    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

    Using the Set Operators
    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

    Manipulating Data using SQL
    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

    Database Transactions
    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

    Creating Other Schema Objects
    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

    Writing Cursor and Conditional Statement
    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

    Introduction to Explicit Cursor
    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 concepts of explicit 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

    Exception Handling
    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

    Writing Subprogram, Procedure and passing parameters
    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

    Creating PL/SQL Package
    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

    Advance Package Concepts and functions
    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

    Introduction and writing Triggers
    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

    Compound 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

    Working with Dynamic SQL
    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

    Advance level- Scripting
    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

    MS SQL Server DBA Course Content

    Starting with SQL Server
    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
    Installing SQL Server
    Pre-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 running
    Functioning of Databases
    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 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 file
    Importing and Exporting Data
    What 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 file
    Security
    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 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 user
    Database Backups
    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 tablesHands-on Exercise – Perform database backup, Check transaction log backup, Monitor space usage of Log files, Use backup storage table
    Recovery and Restoration of Database
    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 RecoveryHands-on Exercise – Set a recovery model, Perform point-in-time recovery, Perform restoration from the last saved state
    Monitoring SQL Server 2014
    What 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 email
    Tracing SQL Server Activity
    SQL 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 server
    Auditing Data Access and Encrypting Data
    SQL server audit feature, Implement audit, Audit data access, Implement encryptionHands-on Exercise – Implement audit, Audit data access, Implement encryption
    Database Maintenance
    Common database maintenance tasks, Ensuring database integrity, Maintaining Indexes, Automating common tasksHands-on Exercise – Create index on table column, Update index after inserting new records
    Automating SQL Server 2014 Management
    Implementing and Managing SQL Server Agent Jobs, Managing job on multiple serversHands-on Exercise – Implement Server Agent Jobs Manage Jobs on same server
    Oracle PL SQL Project

    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.

    Oracle DBA Project

    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–

    • Space usage monitoring
    • SQL scripts monitoring
    • Database & SQL tuning
    • Table & Index Statistics
    • Chained Rows & Locks
    Data Warehouse Project

    Project 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 Project

    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.

    MS SQL Server DBA Project

    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.


Course Fee:
USD 460

Course Type:

Self-Study

Course Status:

Active

Workload:

1 - 4 hours / week

Attended this course?

Back to Top

Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top