This is a combo training course that includes the complete SQL Developer and Administrator concepts. You will learn relational database functionalities, SQL queries, managing, maintaining, and securing databases, and automation of SQL Server. Further, you will also receive the official course material issued by Microsoft for ‘Querying Data with Transact-SQL’ and ‘Administering Microsoft® SQL Server® 2014 Databases’.
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
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
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
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
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 sub-queries
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
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?
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
Create Transact SQL Queries, Query multiple tables by using joins, implementing functions and aggregate data, modifying data, determining the results of DDL statements on supplied tables and data, and constructing DML statements using OUTPUT statement
Query data by using subqueries and APPLY, querying data by using table expressions, group and pivot data by using queries, querying temporal data and non-relational data, constructing recursive table expressions to meet business requirements, and using Windowing functions to group and rank the results of a query
Create database programmability objects by using T-SQL, implement error handling and transactions, implement transaction control in conjunction with error handling in stored procedures, and implement data types and NULL
Design and implement a relational database schema, design and implement indexes, distinguish between indexed and included columns, implementing clustered index, designing and implementing views, implementing column store views
Project: Writing complex Sub Queries
Problem Statement: How to create sub queries using SQL
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 experiences in are – IN or NOT IN, ANY or ALL, EXISTS or NOT EXISTS, and other major queries.
Project 2: Querying a large relational database
Problem Statement: How to get details about customers by querying the database
Description: In this project you will work on downloading a database and restoring it on the server. You will then query the database to get customer details like name, phone number, email id, sales made in a particular month, increase in month-on-month sales and even total sales made to a particular customer.
Project 3: Relational database design
Problem Statement: How to convert a relational design into a table in SQL Server
Topics: In this project you will work on converting a relational design that has enlisted within it the various users, user roles, user accounts and their statuses. You will create a table in SQL Server and insert data into it. With at least 2 rows in each of the tables, you have to ensure that you have created respective foreign keys.
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 : Writing complex Sub Queries
Industry : General
Problem Statement : How to create sub queries using SQL
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 experiences in are – IN or NOT IN, ANY or ALL, EXISTS or NOT EXISTS, and other major queries.
Project 2 :Querying a large relational database
Industry : General
Problem Statement : How to get details about customers by querying the database
Description : In this project you will work on downloading a database and restoring it on the server. You will then query the database to get customer details like name, phone number, email id, sales made in a particular month, increase in month-on-month sales and even total sales made to a particular customer.
Project 3 : Relational database design
Industry : General
Problem Statement : How to convert a relational design into a table in SQL Server
Topics : In this project you will work on converting a relational design that has enlisted within it the various users, user roles, user accounts and their statuses. You will create a table in SQL Server and insert data into it. With at least 2 rows in each of the tables, you have to ensure that you have created respective foreign keys.
Project : SQL Server Audit
Industry : General
Problem Statement : How to track and log events happening on the database engine
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.
Project 2 : Managing SQL Server for a high tech company
Industry : Information Technology
Problem Statement : An IT company wants to manage its MS SQL Server database and gain valuable insights from it.
Description : In this project you will be administrating the MS SQL Server database. You will learn about the complete architecture of MS SQL Server. You will be familiarized with the enterprise edition of SQL Server, various tools of SQL Server, creating and modifying databases in real-time.
This Intellipaat SQL Developer and SQL DBA combo training is a complete course that includes both the Developer and Administrator aspects of SQL. You will master the concepts of database functionalities, querying using SQL, deploying, maintaining, managing, and securing of large databases and also SQL Server automation through this combo course. This entire course content is in line with the requirements of clearing the Microsoft Certified Database Administrator (MCDBA) and the Microsoft SQL Server Certification exams.
You will be working on real-time SQL Developer and Database Administrator projects and step-by-step assignments that have high relevance in the corporate world, and the course curriculum is designed by industry experts. Upon the 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 the latest version at no extra fee. Hence, it is clearly a one-time investment.
This course is designed for clearing the following exams:
You will also receive the course completion certificate by Microsoft for ‘Querying Data with Transact-SQL’ and ‘Administering Microsoft® SQL Server® 2014 Databases’.
The entire course content is in line with the certification program and helps you clear the certification exam with ease and get the best jobs in 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 scenarios, thus helping you fast-track your career effortlessly.
At the end of this training program, there will be a quiz that perfectly reflects the type of questions asked in the certification exam and helps you score better.
Intellipaat Course Completion Certificate will be awarded upon the completion of the project work (after the expert review) and upon scoring at least 60% marks in the quiz. Intellipaat certification is well recognized in top MNCs like Ericsson, Cisco, Cognizant, Sony, Mu Sigma, Saint-Gobain, Standard Chartered, TCS, Genpact, Hexaware, etc.