- Introduction
- Welcome to Udemy
- The Udemy Interface
- Do you want auto-translated subtitles in more languages?
- Exam update
- Curriculum
- Do you have Windows 7, 8 or Windows Vista?
- Downloading SQL Server Developer 2019
- Installing SQL Server Developer 2019
- Installing SQL Server Management Studio (SSMS)
Online
₹ 455 3,499
Quick facts
particular | details | |
---|---|---|
Medium of instructions
English
|
Mode of learning
Self study
|
Mode of Delivery
Video and Text Based
|
Course overview
Phillip Burton - SQL Engineer & Best Selling Instructor created the 70-461, 761: Querying Microsoft SQL Server with Transact-SQL online certification, which is delivered by Udemy and is designed for learners who want to improve their knowledge of the functionalities and methodologies associated with querying SQL server. 70-461, 761: Querying Microsoft SQL Server with Transact-SQL online course by Udemy acts as the base course for the Microsoft certificate 70-461: "Querying Microsoft SQL Server 2012" and 70-761 "Querying Data with Transact-SQL".
70-461, 761: Querying Microsoft SQL Server with Transact-SQL online classes involves more than 29.5 hours of detailed learning materials, including 32 articles, 27 downloadable resources, and 2 coding exercises that help learners create tables, constraints, triggers, and views in a database. With this training, learners will also learn about analytical functions, ranking functions, SQL grouping, SQL transactions, query optimization, data manipulation, and other topics as part of this training.
The highlights
- Certificate of completion
- Self-paced course
- 29.5 hours of pre-recorded video content
- 32 articles
- 27 downloadable resources
- 3 coding exercises
Program offerings
- Online course
- Learning resources. 30-day money-back guarantee
- Unlimited access
- Accessible on mobile devices and tv
Course and certificate fees
Fees information
certificate availability
certificate providing authority
What you will learn
After completing the 70-461, 761: Querying Microsoft SQL Server with Transact-SQL certification course, learners will be introduced to the fundamentals of Microsoft SQL server, and Transact SQL along with the functionalities of the SQL server. Learners will explore the principle and methodologies used for querying Microsoft SQL servers using transact SQL. Learners will study methodologies involved with data types, tables, ranking functions, analytical functions, grouping, geometry databases, transactions, data manipulation, and query optimization. Learners will also acquire the skills for data manipulation as well as manipulation XMLs and JSONs.
The syllabus
Session 1 - Let's begin, and let's download SQL Server
Session 1 - Starting SQL Server
- Opening SQL Server
- Looking at SQL Server Management Studio
- Create a database
- Creating our first queries
- Practice Activity Number 1 - Writing mathematical queries
- Coding Exercises
- Writing mathematical queries
- 1 question
- Databases and Queries
- 2 questions
Session 1 - Creating tables - First pass
- Creating a table - first pass using GUI
- Creating a table - first pass using T-SQL
- New Tables
- 2 questions
- Entering data using the GUI
- Entering data using T-SQL
- Entering data using T-SQL
- 1 question
- Retrieving data
- Retrieving data
- 1 question
- The FROM clause - connecting to one table
- 1 question
- Deleting the data, then the table
- Deleting data using T-SQL
- 2 questions
- Practice Activity Number 2
Session 1 - Number types and functions
- Creating an Employee table
- Session 1 Resources
- Creating temporary variables
- Integer numbers
- Integer numbers
- 5 questions
- Practice Activity Number 3
- Practice Activity Number 3 - Solution
- Non-integer numbers
- Non-integer numbers
- 3 questions
- Mathematical functions
- Mathematical functions
- 2 questions
- Converting between number types
- Converting between number types
- 1 question
- Practice Activity Number 4
- Practice Activity Number 4 - Solution
Session 1 - String data types and functions
- Strings
- Strings
- 2 questions
- String Functions - extraction
- TRIM
- String Functions - extraction
- 2 questions
- NULL - an introduction
- Joining two strings together
- NULL
- 2 questions
- Joining a string to a number
- Joining numbers to strings
- 2 questions
- Practice Activity Number 5
- Practice Activity Number 5 - The Solution
- Want more string functions?
Session 1 - Date data types and functions
- Date data types
- Non-English locales, and Books Online
- Setting dates and Date extraction
- Date data types
- 3 questions
- Today's date, and more date functions
- Today's date and more date functions
- 2 questions
- Date offset
- Converting from date to strings
- Converting from date to strings
- 1 question
- Want more date functions?
- Session 1 - Conclusion2 lectures • 2min
- BONUS - Review what you have learnt (PDF)
- Well done!
Welcome to Session
- Introduction
- Spreadsheet Data
- Session 2 Resources
Session 2 - Creating and querying part of a table
- Creation of tblEmployee table
- Adding additional columns
- SELECTing only part of a table - strings
- SELECTing only part of a table - strings
- 2 questions
- SELECTing only part of a table - numbers
- SELECTing only part of a table - numbers
- 3 questions
Session 2 - Summarising and ordering data
- Summarising and ordering data
- Summarising and ordering data
- 2 questions
- Criteria on summarised data
- Criteria on summarised data
- 4 questions
- Changing blank strings to NULLs
- Exercise
Session 2 - Adding a second table
- Adding a second table
- Designing a connection
- New spreadsheet data
- Importing data and showing tables graphically
- Writing a JOIN query
- Writing a JOIN query
- 2 questions
- Different types of JOIN
- The FROM clause - connecting to two tables
- 1 question
- Different types of JOIN
- 3 questions
- Creating a third table
- JOINing three tables
- JOINing three tables
- 1 question
Session 2 - Find missing data, and delete and update data
- Missing data
- Missing data
- 1 question
- Deleting data
- Deleting data
- 3 questions
- Updating data
- Updating data
- 2 questions
End of Session
- BONUS - Recap, Look ahead and Voucher Code
- Thank you
Session 3 - Introduction
- Introduction
- Is this your first session?
- Resources
- Objectives
- Summary of and problems with our existing database
Session 3 - Objective 4 - Create and modify constraints (simple statements)
- What are constraints?
- Unique constraints - what are they?
- Unique constraints in action
- Unique constraints
- 3 questions
- Default constraints - what are they?
- Default constraints in action
- Default constraints
- 3 questions
- Check constraint - what are they?
- Check constraints - in practice
- Check constraints
- 3 questions
- Primary key
- Primary key - in practice
- Primary Keys
- 3 questions
- Foreign key - what is it?
- Foreign key - in practice
- Foreign Keys
- 4 questions
- Summary
Session 3 - Objectives 2 and 3: Views
- Creating views
- Altering and dropping views
- CREATE OR ALTER VIEW
- Creating views
- 2 questions
- Securing views
- Security
- Security
- 3 questions
- Adding new rows to views
- Deleting rows in views
- Adding and deleting rows in views
- 3 questions
- What is an index?
- Creating an indexed view
- Creating an indexed view
- 1 question
Session 3 - Objective 5: Create and alter DML triggers
- What are triggers?
- Creating an AFTER trigger
- Creating an INSTEAD OF trigger
- Triggers
- 4 questions
- Nested triggers
- Update functions
- Nested triggers and update functions
- 2 questions
- Handling multiple rows in a session
- Summary
- Disable triggers
Session 3 - Conclusion
- BONUS - Recap, Look ahead and Voucher Code
- Well done!
Session 4 - Introduction
- Introduction
- Is this your first session?
- Session 4 - Resources
Session 4 - Objective 13: Combine datasets
- UNION and UNION ALL
- Intersect and Except
- UNION, UNION ALL, INTERSECT and EXCEPT
- 4 questions
- CASE statement
- CASE
- 2 questions
- IsNull and Coalesce
- IsNull and Coalesce
- 3 questions
- MERGE statement - in theory
- Let's Build our MERGE statement
- Let's expand our MERGE statement
- Merge with additional columns
- MERGE
- 4 questions
- Summary
Session 4 - Objective 11 - Create and alter stored procedures (simple statements
- Let's create our first procedure
- Ask for a specific employee
- Creating procedures
- 3 questions
- Exercise with IF
- Multiple arguments
- While
- Return
- While and Return
- 5 questions
- Summary
Session 4 - Objective 18a - Implement try/catch/throw
- Procedure Exercise
- TRY/CATCH
- THROW and RAISERROR
- Try/catch/throw
- 4 questions
- Summary
End of Session 4!
- BONUS - Recap, Look ahead
- Well done!
Session 5 - Introduction
- Introduction
- Is this your first session?
- Session 5 Resources
Session 5 - Objective 9: Implement aggregate queries
- Introduction
- OVER()
- OVER
- 1 question
- PARTITION BY and ORDER BY
- PARTITION BY and ORDER BY
- 2 questions
- ROWS BETWEEN
- CURRENT ROW and UNBOUNDED
- RANGE versus ROWS
- Omitting RANGE/ROW?
- Row and Range
- 5 questions
Session 5 - Objective 9d: Ranking functions
- ROW_NUMBER, RANK and DENSE_RANK
- NTILE
- Ranking functions
- 3 questions
Session 5 - Objective 9a: New analytic functions
- FIRST_VALUE and LAST_VALUE
- LAG and LEAD
- CUME_DIST and PERCENT_RANK
- PERCENTILE_CONT and PERCENTILE_DISC
- New analytic functions
- 3 questions
Section 5 - Objective 9b: Grouping sets
- Adding Totals
- ROLLUP, GROUPING and GROUPING_ID
- GROUPING SETS
- Grouping sets
- 3 questions
Session 5 - Objective 9c: Spatial aggregates
- Introduction
- POINT
- POINT queries
- Point
- 3 questions
- Line, POLYGON and Circles
- Lines and Circles
- 2 questions
- Line queries
- Line queries
- 2 questions
- Geography
- Spatial Aggregates
- Geography and Spatial Aggregates
- 3 questions
- Summary
End of Session 5
- End of Session 5
- Well done!
Session 6 - Introduction
- Introduction
- Objectives
- Session 6 Resources
Session 6 - Objective 7: Sub-queries
- Table structure
- The WHERE clause
- WHERE and NOT
- ANY, SOME and ALL
- The FROM clause
- The SELECT clause
- Correlated subquery - WHERE
- Sub-query
- 3 questions
- Top 5 from various categories
Session 6 - Objective 7e: WITH statement
- WITH statement
- Generating a list of numbers
- Grouping numbers
- WITH statement
- 2 questions
Session 6 - Objective 7b: PIVOTing and UNPIVOTing
- PIVOT
- Replacing NULLs in PIVOTs.
- UNPIVOT
- Pivotting and Unpivotting
- 3 questions
Session 6 - Objective 7d: CTE statement
- Self-Join
- Recursive CTE
- Recursive CTE
- 1 question
Session 6 - Objective 14: Functions
- Introduction
- Scalar Functions
- A more complicated scalar function
- Inline Table Function
- Multi-statement Table Function
- APPLY
- Functions
- 4 questions
Session 6 - Objectives 6b and 6c: Synonyms and Dynamics
- Synonyms
- Dynamic SQL
- Dynamic SQL
- 2 questions
Session 6 - Objectives 8c: GUIDs; and Sequences
- The problems about IDENTITY
- GUIDs
- Creating SEQUENCEs
- Using SEQUENCEs
- GUIDs and SEQUENCEs
- 3 questions
Session 6 - Objective 10: Query and manage XML data - converting tables to XML
- Introduction to XML - Let's make a shopping list
- Attributes
- Creating XML variable and XML field
- FOR XML RAW
- FOR XML AUTO
- FOR XML PATH
- FOR XML EXPLICIT
Session 6 - Objective 10: Shredding XML data
- XQuery Value and Exist methods
- XQuery Modify method
- XQuery Query method using for and return
- XQuery Query method using let, where and order by (FLWOR)
- XQuery Nodes - shredding a variable
- XQuery Nodes - shredding a table
Session 6 - Objective 10: Other XML considerations
- Importing and exporting using the bcp utility
- Bulk Insert and Openrowset
- Schema
- When should I use XML in SQL Server?
- Creating Primary and Secondary XML Indexes
Session 6 - Manipulating JSON data (SQL Server 2016 onwards)
- These next two sections require SQL Server 2016 or later
- Creating JSON and ISJSON
- JSON_VALUE and JSON_QUERY
- JSON_MODIFY
- Converting JSON data into SQL Server tables
- Converting SQL Server data into JSON
Session 6 - Temporal Tables
- What are temporal tables?
- Creating temporal tables
- Dropping temporal tables, and specifying the history table
- Altering existing tables to make them temporal tables
- Querying temporal data at a point of time
- Querying temporal data between a range of times
Session 6 - Conclusion
- Bonus lecture
- Well done!
Session 7 - Introduction
- Introduction
- Curriculum
- Session 7 Resources
Session 7 - Manage transactions
- Introduction - What are transactions?
- Implicit transactions
- Explicit Transactions - Start and end transactions
- Mark a transaction
- Trancount
- Scope and type of locks
- Locks in Practice, and using the WAITFOR statement
- What would happen if locks didn't exist?
- What are Isolation levels?
- Isolation Levels in Action
- Conclusion
Session 7 - Indexes
- Re-introducing Query plans
- Heaps, and scans
- B-Tree
- Clustered indexes
- Quiz - Clustered Indexes
- 4 questions
- Non-clustered indexes
- Filtered Indices
- Include
Session 7 - Optimise queries
- What are statistics - Include Client Statistics
- The different join types and seeing HASH join in action
- Nested Loops and Merge Joins in action
- Even bigger savings of time when using a SARG
- Reading Query plans and the cost of Sorting
- A more advanced query plan
- Plan guides
- Hints
- Statistics IO
- SET SHOWPLAN_ALL and Client Statistics
- Dynamic vs. parameterised queries
Session 7 - Dynamic Management Views and Functions
- dm_db_index_usage_stats
- sys.dm_db_index_physical_stats
- sys.dm_db_missing_index_details
- Conclusion
Session 7 - Evaluate the use of row-based operations vs. set-based operations
- How to write a cursor
- Disadvantages
- Alternatives
- Impact of scalar UDFs
- Combine multiple DML operations
Congratulations
- You have finished this course - now what?
- Thank you, and goodbye
Bonus - Installing SQL Server 2016 Developer Edition
- Downloading SQL Server back engine - the Developer edition - for free!
- Installing SQL Server back engine
- Installing SQL Server Front Engine
- The next step
Bonus - Installing SQL Server 2014 Developer Edition (for Windows 7 and Vista)
- Downloading SQL Server back engine - the Developer edition - for free!
- Mount an ISO
- Installing SQL Server 2014
- Bonus Lecture1 lecture • 3min
- Bonus Lecture
Instructors
Mr Phillip Burton
Instructor
Udemy