Corporate Training

Microsoft SQL Developer Training

Learn how to build, develop, and maintain a SQL Server database by focusing on original features and tools of the SQL server. And how to alter tables using T-SQL, SELECT statements,indexes, queries, transaction types, and triggers. Database management skills like disaster recovery, data storage, replication, and data mirroring. Build and maintain SQL Server DB, and gain insights into enterprise data with real-world SQL Server Development Training.

Microsoft SQL Developer Training

Microsoft SQL Developer Training

During this course of Sql Server Training for Development, you will be able to learn what is SQL architecture, server/client relation, stored procedures, functions, SELECT statement, data types, views. how to modify data by using T-SQL, How to develop different operators and functions to design RDBMS, How to create and manage triggers, How to search, index, sort, and group by using records. (How to transactions - using isolation level and locking - preventing deadlocks.) How to implement optimized/efficient, scalable database, scripts/programs/queries for executions. How to recover, replicate, mirror, and store the data in the database. How to implement actions like installation, configure, planning, designing database, troubleshooting, and security, data recovering techniques. How to use advanced query techniques, advanced views, complex queries, user-defined functions, work with XML. You will also be able to monitor and control user access in optimizing the performance of the database.How to insert, update, and delete data, How to work on joins and sub-queries

Microsoft SQL Developer Training

Student Journey

Soon after enrolling in the course of Sql server Developer Training at TrainingHub.IO, you will get trained by professionals experienced with 10+ of experience. (The course is designed for software developers, BI Professionals, SQL andDatabase administrators, Big data and Hadoop professionals, BA, and Managers, Individuals looking for a career in SQL development.) There are no pre-requisites to enroll for the course.Hence anyone can gain in-depth knowledge of the content. Instructors conduct assignments and doubt clarifications for better understandings. After successful completion of the course, the expert's advice will be provided based on individuals performance.

Microsoft SQL Developer Training- Student Journey
Microsoft SQL Developer Training- Student Journey

Course Content

  • RDBMS Concept & Essential Tools
    • SQL Server Management Studio
    • Transact-SQL
    • SQL Server Configuration Manager
    • DBMS Concept
    • RDBMS Concepts
    • ER Diagram Concepts
    • Cardinality
    • Ordinality
    • SDLC for Database design and Development
    • Normalization and De-Normalization
  • Creating schemas
    • Managing schemas
    • Referencing schemas versus using the default schema
    • Hiding schemas with synonyms
    • Building tables
  • Selecting appropriate data types
    • Constructing tables with CREATE TABLE
    • Different data types and what is the internal difference
    • Importance of selecting proper data types
    • Data types and performance
    • Converting data using CAST and CONVERT
    • User Defined DataTypes
  • Adding constraints
    • Not Null
    • Primary Key
    • Foreign key
    • UniqueoCheck
    • Default
    • Candidate Key
    • Alternate Key
    • Natural Key
    • Surrogate Key
  • Implementing various types of joins
    • Inner joins
    • Cross joins
    • Left, right and full outer joins
    • Equijoins
    • The performance implications of joins
    • Adding filter conditions to outer joins
    • Writing self joins
    • Join algorithms(hash join), loop join and merge join
    • Joining a table to itself
    • Chaining self joins
    • Solving time-interval problems
  • Scalar and Aggregate Functions/Set Operators
    • Taking advantage of scalar functions
    • Converting data types
    • Handling dates
    • Manipulating strings
    • Choosing the right function for the job
    • Set operators – UNION , UNION ALL ,INTERSECT ,EXCEPT
  • Sorting and Filtering Data
    • Sorting Data
    • Filtering Data with Predicates
    • Filtering with the TOP and OFFSET-FETCH Options
    • Working with Unknown Values
  • Summarizing data with aggregate functions
    • COUNT
    • SUM
    • AVG
    • Equijoins
    • MIN
    • MAX
    • Managing NULLs
    • Suppressing duplicates
    • GROUP BY and GROUPING SETS
    • Applying conditions with HAVING
    • Calculating moving averages
    • Computing running totals
    • Nesting grouped aggregates
    • Joins and grouping
    • Introducing subtotals with CUBE and ROLLUP
  • Declaring variables and parameters
    • Creating and utilizing local variables
    • Passing input and output parameters
    • Interrogating global variables
  • RANK Function -Row_Number
    • RANK and DENSE_RANK
    • Ordering data with ranking functions
    • ROW_NUMBER with ordered sets
    • The OVER clause
  • Building Sub-queries
    • Simple sub-queries
    • Sub-queries in conditions and column expressions
    • Creating multilevel sub-queries
    • Avoiding problems when sub-queries return NULLs
    • Handling multi row sub-query results
    • Correlated sub-queries
    • Accessing values from the outer query
    • EXISTS vs. INoIdentifying duplicates
    • Avoiding accidental correlation
  • Temporary Tables
    • Create local temporary tables
    • Create global temporary tables
    • Table value parameter
    • Table variablesoCommon table expression (CTE)
    • Derived Tables
  • PIVOT/UNPIVOT
    • Importance of pivoting
    • How to pivot data
  • Maintaining Data (DML)
    • Modifying data
    • Inserting, updating and deleting data
    • Derived table in FROM clause
    • Derived table in JOIN clause
  • Views
    • Storing queries on the server
    • Concealing complexity with views
    • Indexed views
    • Partitioned views
    • Taking advantage of schema binding
    • View encryption
  • Transaction
    • ACID properties
    • Ensuring data consistency with transactions and distributed transactions
    • Isolation levels
    • Begin Transaction
    • Commit Transaction
    • Save point
    • Phantom rows
    • Non repeatable reads
    • Dirty Reads
    • Dealing with open transactions when an exception occurs
  • Programming procedural statements
    • Implementing conditions with IF...ELSE
    • Looping with WHILE and GOTO
    • Creating code blocks with BEGIN...END
    • Debugging with PRINT
    • Returning data using RETURN
    • Debugging T-SQL in Management Studio
  • Handling errors
    • Communicating problems to the client with RAISERROR
    • Intercepting errors with TRY...CATCH
    • Dealing with open transactions when an exception occurs
  • Stored Procedures
    • Batch and stored procedure processing
    • Minimizing network traffic using batches and procedures
    • Stored procedure compilation and execution
    • Using scalar functions
    • Table value parameters
    • Querying Multiple Tables
  • Creating and managing indexes
    • Clustered Index
    • Non Clustered Index
    • Unique Index
    • Filtered Index
    • Partitioned Index
    • Covered Index
    • Defining indexed views
    • Analyzing and repairing fragmentation
  • Functions
    • Scalar Function
    • In-Line table value function
    • Multi-statement table-valued function
    • Creating user-defined functions
    • Calculating values with scalar functions
    • Processing multiple rows returned from a table-valued function
    • Taking advantage of schema binding
    • Function encryption
  • Triggers & Cursors
    • INSTEAD OF vs. AFTER triggers
    • Detecting row changes using the inserted and deleted tables
    • Tracking metadata changes with DDL triggers
    • Auditing user access using a LOGON trigger
    • Tracking data changes with the OUTPUT clause
    • Track column changes using UPDATE function
    • Processing rows on the server with a cursor
  • Implementing Server and Database Security
    • Creating logins
    • Contrasting Windows and SQL Server authentication
    • Authorizing logins
    • Making logins members of server roles
    • Enforcing password policy
  • System databases
    • Master
    • MSDB
    • Model
    • TempDB
    • Importance of TempDB with respect to performance
  • Backup - Restoring databases operation + Scripting
    • Restore a full backup
    • Restore a differential backup
    • Restore a log backup
    • Restore with NO RECOVERY
    • Restore with RECOVERY
    • Restore with RESTART
    • Point in time restore
    • MARK restore
    • Recovering user databases
    • Testing recovery scenarios
    • Scripting objects and moving data with Transact-SQL
    • Detaching and attaching databases
  • Linked Server
    • What /why / how about linked server
    • To create a linked server to another instance of SQL Server
    • To create a linked server with Non-SQL Command Providers
    • Test the linked server
    • Writing a query that joins tables from a linked server
  • Export/Import data/database
    • Import wizard
    • Export wizard
    • Copy database wizard
  • Automating Tasks with Jobs and Alerts
    • The SQL Server Agent
    • Configuring the agent
    • Setting up Database Mail
    • Multistep jobs
    • Defining jobs to handle routine tasks
    • Creating alerts and operators
    • Associating alerts with jobs
  • Storing and Querying XML Data in SQL Server
    • XML and XML Schemas
    • Storing XML Data and Schemas in SQL Server
    • Implementing the XML Data Type
    • Using the T-SQL FOR XML Statement
    • Getting Started with xQuery
    • Shredding XML
  • Extras
    • Introduction to Temporal Data
    • End to End Case study Including Build Database from Scratch
    • Total 6 Assignments
    • More than 10 quizzes
    • Debugging Techniques
    • Introduction to Profiler
    • Top 50 queries to be used in Industry
    • Top 50 interview questions for preparation as Sql Server Developer
    • Review and consolidate your SQL skills with tons of exercises!

RelatedCourses

Alt
Microsoft Technologies
Business Intelligence (MSBI)