Categories

There are currently no items in your shopping cart.

User Panel

Forgot your password?.

70-461 Session 1-7: Querying Microsoft SQL Server (SQL code)

Video Introducing this tutorial


Session 1 - Let's begin, and let's download SQL Server :
Introduction
Welcome to Udemy
Curriculum
Do you have Windows 7 or Windows Vista?
Downloading SQL Server
Installing SQL Server Front Engine
Installing SSMS

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
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
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

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 or a string and number 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

Session 1 - Date data types and functions :
Date data types
Date data types
3 questions
Setting dates and Date extraction
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

Session 1 - Conclusion :
BONUS - Review what you have learnt (PDF)
Well done!

Welcome to Session 2 :
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
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
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 2 :
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
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

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 database :
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
PRINT
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
RANGE
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 XML 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 - 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
Hints
Plan guides
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

Congra :
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!
Installing SQL Server 2014