SQL Developer, SQL DBA Training Masters Program
Intellipaat
Course Summary
Our SQL developer DBA certification master program lets you gain full proficiency in Oracle database. We provide the best online training classes to help you learn database administration using SQL Server, relational database functionalities, SQL queries. Work on real world industry projects.
-
+
Course Description
About Course
Gain an in-depth understanding of the functions performed by a Database Administrator using SQL Server.
This is a Combo Training Course that includes the complete SQL Developer Course and Administrator Course. You will learn about relational database functionalities, SQL Queries, managing, maintaining and securing databases and automation of SQL Server.
What you will learn in this Training Course?
- Learn about the Client Server Architecture
- Study SQL, databases, and data manipulation language
- Create relational databases and query it
- Deploy Set Operators, Table Variables and Joins
- Modify data using Views, T-SQL and Stored Procedures
- Creation and concepts of Triggers
- Search, sort and index data using Records
- Deploy security and authentication methods on SQL Server
- SQL Server Agent Clustering and Replication
Who should take this Training Course?
- Database Administrators and Developers
- Project Managers, Analysts and Testers
- Big Data and Hadoop professionals
What are the prerequisites for taking this Training Course?
A basic understanding of computer programming is enough to take this course.Why should you take this Training Course?
SQL Developers are in great demand in companies to work on large databases. Along with this the SQL Administrators are also needed in order to manage, monitor and secure the database servers. So this Combo Training Course will provide you with the complete set of skills to help you land high-paying jobs in some of the best companies in the world.
-
+
Course Syllabus
SQL Course Content
Introduction to SQLThe 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.
SQL operatorsIntroduction 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.
Working With SQL – Join,Tables,VariablesDeep 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.
Deep dive into SQL FunctionsUnderstanding 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.
WORKING WITH SUBQUERIESUnderstanding 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.
SQL Views,Functions,Stored ProceduresLearning 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.
Deep Dive into User-defined FunctionsDetailed 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?
SQL Optimization & PerformanceDetailed 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.
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 serverSQL 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.
