This examination is meant for SQL Server data source managers, system designers, and programmers with two or more years of experience looking to validate their abilities and knowledge in composing questions.
Part of the requirements for MCSA: SQL 2016 Database Development
Ø Exam 70-762: Developing SQL Databases
Languages: English
This test is meant for database professionals who develop and execute data sources throughout organizations and ensure high levels of information availability. Their obligations consist of creating data source documents, data types, and tables; planning, creating, and enhancing indexes; ensuring data stability; carrying out sights, saved procedures, and features; and managing purchases as locks.
determine correct SELECT question structure, compose detailed questions to please company needs, construct results from numerous queries using set drivers, compare UNION as well as UNION ALL actions, recognize the question that would undoubtedly return anticipated outcomes based upon provided table framework and/or information
Query multiple tables by using joins
write inquiries with sign-up with declarations based upon provided tables, knowledge, and demands; determine the correct use of INNER JOIN, LEFT/RIGHT/FULL OUTER SIGN UP WITH, and CROSS SIGN UP WITH; construct numerous JOIN operators utilizing As Well As OR; determine the correct results when offered with multi-table SELECT declarations and resource data; create queries with NULLs on joins
Implement functions and aggregate data
construct queries using scalar-valued and table-valued parts; recognize the impact of function usage on quiz performance and also WHERE provision variability; identify the distinctions between deterministic and non-deterministic features; usage integrated accumulated components; use arithmetic features, date-related features, and system features
Modify data
write INSERT, UPDATE, and also DELETE declarations; establish which statements can be utilized to fill data to a table based upon its framework as well as constraints; construct Data Control Language (DML) statements making use of the RESULT statement; establish the outcomes of Information Meaning Language (DDL) statements on provided tables as well as data
Query data with advanced Transact-SQL components (30–35%)
Query data by using sub-queries and APPLY
figure out the outcomes of queries making use of subqueries as well as table signs up, examine performance differences between table signs up with and correlated subqueries based on given information as well as question plans, distinguish between making use of CROSS APPLY and also OUTER APPLY, compose APPLY statements that return a given data set based on provided data
Query data by using table expressions
determine fundamental elements of table expressions, specify use differences between table expressions as well as short-lived tables, construct recursive table expressions to meet business demands
Group and pivot data by using queries
usage windowing operates to the team and also place the outcomes of a question; compare making use of windowing features and GROUP BY; construct complex TEAM BY provisions using GROUPING SETS, and also DICE; construct PIVOT and UNPIVOT statements to return wanted results based upon provided information; identify the effect of NULL worths in PIVOT and also UNPIVOT inquiries
Query temporal data and non-relational data
question historical information by utilizing temporal tables, query as well as output JSON information, inquiry and also outcome XML data
Program databases by using Transact-SQL (25–30%)
Create database programmability objects by using Transact-SQL
Create saved procedures, table-valued and also scalar-valued user-defined features, triggers, and also views; execute input and result criteria in stored treatments; determine whether to use scalar-valued or table-valued features; compare deterministic and non-deterministic functions; create indexed views
Implement error handling and transactions
figure out outcomes of Information Interpretation Language (DDL) statements based upon transaction control declarations, implement SHOT ... CATCH mistake managing with Transact-SQL, create error messages with THROW and also RAISERROR, carry out purchase control combined with mistake handling in stored treatments
Implement data types and NULLs
assess results of information type conversions, establish appropriate data kinds for provided information aspects or table columns, determine places of implied data type conversions in questions, establish the correct outcomes of signs up with as well as functions in the visibility of NULL values, identify proper usage of ISNULL and also COALESCE features
Exam 70-762: Developing SQL Databases
Design and implement database objects (25–30%)
Design and implement a relational database schema
design tables and schemas based on service demands, enhance the layout of tables by utilizing normalization, create table create declarations, determine one of the most efficient information kinds to use
Design and implement indexes
design new indexes based upon provided tables, questions, or plans; distinguish between indexed columns as well as included columns; carry out clustered index columns by using best practices; advise new indexes based on inquiry strategies
Design and implement views
style a view structure to choose data based upon customer or business requirements, identify the actions required to design an updateable view, implement segmented views, execute indexed sights
Implement column store indexes
identify usage instances that support using columnstore indexes, determine correct usage of gathered and non-clustered columnstore indexes, layout basic non-clustered indexes combined with clustered column store indexes, execute columnstore index upkeep
Implement programmability objects (20–25%)
Ensure data integrity with constraints
define table and also foreign crucial constraints to impose organization rules, create Transact-SQL statements to add restraints to tables, determine results of Information Adjustment Language (DML) declarations provided existing tables as well as restrictions, determine appropriate use of PRIMARY SECRET constraints
Create stored procedures
style stored treatment parts as well as structure based upon business demands, carry out input as well as output specifications, apply table-valued parameters, apply return codes, simplify existing saved treatment logic, implement error handling and deal control logic within saved treatments
Create triggers and user-defined functions
layout trigger logic based on company demands; establish when to make use of Data Control Language (DML) sets off, Information Meaning Language (DDL) activates, or logon triggers; acknowledge outcomes based upon execution of AFTER or AS OPPOSED TO triggers; style scalar-valued as well as table-valued user-defined functions based upon organization needs; identify distinctions between deterministic and non-deterministic features
Manage database concurrency (25–30%)
Implement transactions
recognize DML declaration results based on purchase behavior, acknowledge distinctions between and also recognize use of explicit and implied deals, execute save factors within deals, establish the role of transactions in high-concurrency data sources
Manage isolation levels
recognize distinctions in between Read Uncommitted, Read Committed, Repeatable Read, Serializable, as well as Picture isolation degrees; define outcomes of simultaneous queries based upon seclusion degree; recognize the source and also performance effect of given isolation degrees
Optimize concurrency and locking behavior
troubleshoot locking concerns, determine lock acceleration behaviors, capture and also analyze predicament charts, recognize methods to remediate deadlocks
Implement memory-optimized tables and native stored procedures
specify usage cases for memory-optimized tables versus standard disk-based tables, maximize efficiency of in-memory tables by altering durability setups, identify best case usage situations for natively compiled saved treatments, make it possible for collection of implementation data for natively put together kept treatments
Optimize database objects and SQL infrastructure (20–25%)
Optimize statistics and indexes
identify the accuracy of stats and the connected effect to inquire strategies and performance, layout stats maintenance tasks, utilize vibrant monitoring challenge assess present index use and also identify missing indexes, consolidate overlapping indexes
Analyze and troubleshoot query plans
capture inquiry strategies making use of extended occasions and traces determine badly doing inquiry plan operators, create effective query plans utilizing Inquiry Shop, compare approximated and actual inquiry strategies and also related metadata, configure Azure SQL Database Performance Insight
Manage performance for database instances
manage database work in SQL Server; style and apply Elastic Range for Azure SQL Database; choose a suitable service tier or edition; enhance data source documents and tempdb configuration; optimize memory configuration; screen as well as diagnose scheduling as well as wait stats using dynamic administration objects; troubleshoot and examine storage space, IO, as well as cache issues; screen Azure SQL Database inquiry strategies
Monitor and trace SQL Server baseline performance metrics
display operating system and also SQL Web server performance metrics; contrast baseline metrics to observed metrics while repairing performance problems; identify differences between performance monitoring as well as logging tools, such as perfmon and also dynamic administration objects; screen Azure SQL Database efficiency; determine finest practice use situations for extensive events; compare Extended Occasions targets; contrast the impact of Prolonged Events as well as SQL Trace; specify differences between Extended Occasions Packages, Targets, Activities, and also Sessions
To know more on MCSA SQL 2016 Database Development certification, click here.
To know more about other courses in IT Academy, click here.
