SQL Developer And SQL DBA Certification Training


SQL Developer and SQLDBA Certification TrainingIn Collaboration with MicrosoftSQL Developer and DBA Certification Training1 Page

Table of Contents1.About the Program2.Collaborating with Microsoft3.About Intellipaat4.Key Features5.Career Support6.Why take up this course?7.Who should take up this course?8.Program Curriculum9.Project Work10.Certification11.Intellipaat Success Stories12.Contact UsSQL Developer and DBA Certification Training2 Page

About the ProgramOur SQL Developer and DBA certification master's program lets you gain full proficiency inSQL databases. We provide the best online training classes to help you learn databaseadministration using SQL Server, relational database functionalities, and SQL queries. Youwill work on real-world industry projects in this program. As part of this online classroomtraining, you will also receive the official course material issued by Microsoft for ‘QueryingData with Transact-SQL’ and ‘Administering Microsoft SQL Server Databases.’Collaborating with MicrosoftMicrosoft is one of the largest organizations in terms of inventing creative tools for variouspurposes. Experts from Microsoft and other top MNCs will offer you their understandingand knowledge in the field through this online certification. Moreover, you will also get deepinsights into the concepts and gain hands-on experience by working on industry-specifiedassignments.Benefits for students from Microsoft: Industry-recognized Microsoft certification Real-time projects and exercisesAbout IntellipaatIntellipaat is one of the leading e-learning training providers with more than 600,000learners across 55 countries. We are on a mission to democratize education as webelieve that everyone has the right to quality education. Our courses are delivered bysubject matter experts from top MNCs, and our world-class pedagogy enables learners toquickly learn difficult topics in no time. Our 24/7 technical support and career services willhelp them jump-start their careers in their dream companies.SQL Developer and DBA Certification Training3 Page


Career SupportSESSIONS WITH INDUSTRY MENTORSAttend sessions from top industry experts and get guidance on how to boostyour career growthMOCK INTERVIEWSMock interviews to make you prepare for cracking interviews by top employersGUARANTEED INTERVIEWS & JOB SUPPORTGet interviewed by our 400 hiring partnersRESUME PREPARATIONGet assistance in creating a world-class resume from our career services teamSQL Developer and DBA Certification Training5 Page

Why take up this course?SQL Developers are in great demand in companies to work on large databases. Along withthis, SQL Administrators are much needed to manage, monitor, and secure databaseservers. This combo training course will provide you the complete set of skills to help youland high-paying jobs in one of the best companies in the world.Who should take up this course? Database Administrators and Developers Project Managers, Analysts, and Testers Big Data and Hadoop ProfessionalsSQL Developer and DBA Certification Training6 Page

Program CurriculumSQL Course Content1. INTRODUCTION TO SQL1.1 Various types of databases1.2 Introduction to Structured Query Language1.3 Distinction between client server and file server databases1.4 Understanding SQL Server Management Studio1.5 SQL table basics1.6 Data types and functions1.7 Transact-SQL1.8 Authentication for Windows1.9 Data control language1.10 The identification of the keywords in T-SQL, such as Drop Table2. DATABASE NORMALIZATION & ENTITY RELATIONSHIP MODEL2.1 Data anomalies2.2 Update anomalies2.3 Insertion anomalies2.4 Deletion anomalies2.5 Types of dependencies2.6 Functional dependency2.7 Fully functional dependency2.8 Partial functional dependency2.9 Transitive functional dependency2.10 Multi-valued functional dependency2.11 Decomposition of tables2.12 Lossy decomposition2.13 Lossless decomposition2.14 What is normalization?2.15 First normal form2.16 Second normal formSQL Developer and DBA Certification Training7 Page

2.17 Third normal form2.18 Boyce-Codd normal form (BCNF)2.19 Fourth normal form2.20 Entity-Relationship model2.21 Entity and entity sets2.22 Attributes and types of attributes2.23 Entity sets2.24 Relationship sets2.25 Degree of relationship2.26 Mapping cardinalities: one-to-one, one-to-many, many-to-one, and many-to-many2.27 Symbols used in the E-R notation3. SQL OPERATORS3.1 Introduction to relational databases3.2 Fundamental concepts of relational rows, tables, and columns3.3 Several operators (such as logical and relational), constraints, domains, indexes,stored procedures, and primary and foreign keys3.4 Understanding group functions3.5 The unique key4. WORKING WITH SQL: JOIN, TABLES, & VARIABLES4.1 Advanced concepts of SQL tables4.2 SQL functions4.3 Operators and queries4.4 Table creation4.5 Data retrieval from tables4.6 Combining rows from tables using inner, outer, cross, and self joins4.7 Deploying operators such as intersect, except, and union4.8 Temporary table creation4.9 Set operator rules4.10 Table variables5. DEEP DIVE INTO SQL FUNCTIONS5.1 Understanding SQL functions – What do they do?5.2 Scalar functionsSQL Developer and DBA Certification Training8 Page

5.3 Aggregate functions5.4 Functions that can be used on different datasets, such as numbers, characters,strings, and dates5.5 Inline SQL functions5.6 General functions5.7 Duplicate functions6. WORKING WITH SUBQUERIES6.1 Understanding SQL subqueries and their rules6.2 Statements and operators with which subqueries can be used6.3 Using the set clause to modify subqueries6.4 Understanding different types of subqueries, such as where, select, insert, update,delete, etc.6.5 Methods to create and view subqueries7. SQL VIEWS, FUNCTIONS, & STORED PROCEDURES7.1 Learning SQL views7.2 Methods of creating, using, altering, renaming, dropping, and modifying views7.3 Understanding stored procedures and their key benefits7.4 Working with stored procedures7.5 Studying user-defined functions7.6 Error handling8. DEEP DIVE INTO USER-DEFINED FUNCTIONS8.1 User-defined functions8.2 Types of UDFs like scalar8.3 Inline table value8.4 Multi-statement table8.5 Stored procedures and when to deploy them8.6 What is a rank function?8.7 Triggers and when to execute triggers9. SQL OPTIMIZATION & PERFORMANCE9.1 SQL Server Management Studio9.2 Using pivot in MS Excel and MS SQL ServerSQL Developer and DBA Certification Training9 Page

9.3 Differentiating between Char, Varchar, and NVarchar9.4 XL path, indexes, and their creation9.5 Records grouping, advantages, and searching, sorting, and modifying data9.6 Clustered indexes creation9.7 Use of indexes to cover queries9.8 Common table expressions9.9 Index guidelines10. MANAGING DATA WITH TRANSACT-SQL10.1 Creating Transact-SQL queries10.2 Querying multiple tables using joins10.3 Implementing functions and aggregating data10.4 Modifying data10.5 Determining the results of DDL statements on supplied tables and data10.6 Constructing DML statements using the output statement11. QUERYING DATA WITH ADVANCED TRANSACT-SQL COMPONENTS11.1 Querying data using subqueries and APPLY11.2 Querying data using table expressions11.3 Grouping and pivoting data using queries11.4 Querying temporal data and non-relational data11.5 Constructing recursive table expressions to meet business requirements11.6 Using windowing functions to group11.7 Rank the results of a query12. PROGRAMMING DATABASES USING TRANSACT-SQL12.1 Creating database programmability objects by using T-SQL12.2 Implementing error handling and transactions12.3 Implementing transaction control in conjunction with error handling in storedprocedures12.4 Implementing data types and NULL13. DESIGNING & IMPLEMENTING DATABASE OBJECTS13.1 Designing and implementing relational database schema13.2 Designing and implementing indexesSQL Developer and DBA Certification Training10 P a g e

13.3 Comparing between indexed and included columns13.4 Implementing clustered index13.5 Designing and deploying views13.6 Column store views14. IMPLEMENTING PROGRAMMABILITY OBJECTS14.1 Explaining foreign key constraints14.2 Using T-SQL statements14.3 Usage of Data Manipulation Language (DML)14.4 Designing the components of stored procedures14.5 Implementing input and output parameters14.6 Applying error handling14.7 Executing control logic in stored procedures14.8 Designing trigger logic, DDL triggers, etc.15. MANAGING DATABASE CONCURRENCY15.1 Applying transactions15.2 Using the transaction behavior to identify DML statements15.3 Learning about implicit and explicit transactions15.4 Isolation level management15.5 Understanding concurrency and locking behavior15.6 Using memory-optimized tables16. OPTIMIZING DATABASE OBJECTS16.1 Accuracy of statistics16.2 Formulating statistics maintenance tasks16.3 Dynamic management objects16.4 Identifying missing indexes16.5 Examining and troubleshooting query plans16.6 Consolidating overlapping indexes16.7 The performance management of database instances16.8 SQL Server performance monitoring17. ADVANCED TOPICS17.1 Correlated subqueries, grouping sets, rollup, and cubeSQL Developer and DBA Certification Training11 P a g e

Hands-on Exercise:1. Implementing correlated subqueries2. Using the exists clause with a correlated subquery3. Using the union query4. Using the grouping set query5. Using rollup6. Using cube to generate four grouping sets7. Performing a partial cube18. MICROSOFT COURSES: STUDY MATERIAL18.1 Performance Tuning and Optimizing SQL Databases18.2 Querying Data with Transact-SQLSQL Server DBA Course Content INSTALLATION & CONFIGURATIONo Plan InstallationEvaluate installation requirements; design the installation of SQL Server and itscomponents (drives, service accounts, etc.); plan scale-up vs scale-out basics; plan forcapacity, including if/when to shrink, grow, autogrow, and monitor growth; manage thetechnologies that influence SQL architecture (e.g., service broker, full text, scale out, etc.);design the storage for new databases (drives, filegroups, partitioning, etc.); design thedatabase infrastructure; configure an SQL Server standby database for reportingpurposes; Windows-level security and service-level security; core mode installation;benchmark a server before using it in a production environment (SQLIO, Tests on SQLInstance, etc.), and choose the right hardwareo Installing SQL Server and Related ServicesTest connectivity; enable and disable features; install SQL Server database engine andSSIS (but not SSRS and SSAS), and configure an OS disko Implementing a Migration StrategyRestore vs detach/attach; migrate security; migrate from a previous version; migrate tonew hardware, and migrate systems and data from other sourcesSQL Developer and DBA Certification Training12 P a g e

o Configuring Additional SQL Server ComponentsSet up and configure all SQL Server components (Engine, AS, RS, and SharePointintegration) in a complex and highly secure environment; configure full-text indexing; SSISsecurity, and filestream and filetableo Manage SQL Server AgentCreate, maintain, and monitor jobs; administer jobs and alerts; automate (setup,maintenance, and monitoring) across multiple databases and multiple instances, and sendto ‘Manage SQL Server Agent Jobs’ MANAGING INSTANCES & DATABASESo Managing and Configuring DatabasesDesign multiple file groups; database configuration and standardization: autoclose,autoshrink, recovery models; manage file space, including adding new filegroups andmoving objects from one filegroup to another; implement and configure containeddatabases; data compression; configure TDE; partitioning; manage log file growth, andDBCCo Configuring SQL Server InstancesConfigure and standardize a database: autoclose, autoshrink, and recovery models; installdefault and named instances; configure SQL to use only certain CPUs (affinity masks,etc.); configure server-level settings; configure many databases/instance, manyinstances/server, virtualization; configure clustered instances, including MSDTC; memoryallocation; database mail, and configure SQL Server engine: memory, fillfactor,sp configure, and default optionso Implementing a SQL Server Clustered InstanceInstall a cluster; manage multiple instances on a cluster; set up subnet clustering, andrecover from a failed cluster nodeo Managing SQL Server InstancesInstall an instance; manage interaction of instances; SQL patch management; installadditional instances; manage resource utilization by using Resource Governor, and cycleerror logsSQL Developer and DBA Certification Training13 P a g e

OPTIMIZING & TROUBLESHOOTINGo Identifying and Resolving Concurrency ProblemsExamine deadlocking issues using the SQL server logs using trace flags; design reportingdatabase infrastructure (replicated databases); monitor via DMV or other MS product;diagnose blocking, live locking and deadlocking; diagnose waits; performance detectionwith built-in DMVs; know what affects performance, locate them, and if necessary killprocesses that are blocking or claiming all resourceso Collecting, Analyzing, and Troubleshooting DataMonitor using Profiler; collect performance data by using System Monitor; collect tracedata by using SQL Server Profiler; identify transactional replication problems; identify andtroubleshoot data access problems; gather performance metrics; identify potentialproblems before they cause service interruptions; identify performance problems; useXEvents and DMVs; create alerts on critical server condition; monitor data and serveraccess by creating audit and other controls; identify IO vs memory vs CPU bottlenecks,and use the Data Collector toolo Auditing SQL

RESUME PREPARATION Get assistance in creating a world-class resume from our career services team . SQL Developer and DBA Certification Training 6 P a g e Why take up this course? SQL Developers are in great demand in companies to work on large databases. Along with this, SQL Administrators are much needed to manage, monitor, and secure database servers. This combo