Session 1 - Let's begin, and let's download SQL Server

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)

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

Popular Searches

70-461, 761: Querying Microsoft SQL Server with Transact-SQL

Online

₹ 4099

Quick Facts

Course overview

The highlights

Program offerings

Course and certificate fees

Fees information

certificate availability

certificate providing authority

Who it is for

What you will learn

The syllabus

Session 1 - Let's begin, and let's download SQL Server

Session 1 - Starting SQL Server

Session 1 - Creating tables - First pass

Session 1 - Number types and functions

Session 1 - String data types and functions

Session 1 - Date data types and functions

Welcome to Session

Session 2 - Creating and querying part of a table

Session 2 - Summarising and ordering data

Session 2 - Adding a second table

Session 2 - Find missing data, and delete and update data

End of Session

Session 3 - Introduction

Session 3 - Objective 4 - Create and modify constraints (simple statements)

Session 3 - Objectives 2 and 3: Views

Session 3 - Objective 5: Create and alter DML triggers

Session 3 - Conclusion

Session 4 - Introduction

Session 4 - Objective 13: Combine datasets

Session 4 - Objective 11 - Create and alter stored procedures (simple statements

Session 4 - Objective 18a - Implement try/catch/throw

End of Session 4!

Session 5 - Introduction

Session 5 - Objective 9: Implement aggregate queries

Session 5 - Objective 9d: Ranking functions

Session 5 - Objective 9a: New analytic functions

Section 5 - Objective 9b: Grouping sets

Session 5 - Objective 9c: Spatial aggregates

End of Session 5

Session 6 - Introduction

Session 6 - Objective 7: Sub-queries

Session 6 - Objective 7e: WITH statement

Session 6 - Objective 7b: PIVOTing and UNPIVOTing

Session 6 - Objective 7d: CTE statement

Session 6 - Objective 14: Functions

Session 6 - Objectives 6b and 6c: Synonyms and Dynamics

Session 6 - Objectives 8c: GUIDs; and Sequences

Session 6 - Objective 10: Query and manage XML data - converting tables to XML

Session 6 - Objective 10: Shredding XML data

Session 6 - Objective 10: Other XML considerations

Session 6 - Manipulating JSON data (SQL Server 2016 onwards)

Session 6 - Temporal Tables

Session 6 - Conclusion

Session 7 - Introduction

Session 7 - Manage transactions

Session 7 - Indexes

Session 7 - Optimise queries

Session 7 - Dynamic Management Views and Functions

Session 7 - Evaluate the use of row-based operations vs. set-based operations

Congratulations

Bonus - Installing SQL Server 2016 Developer Edition

Bonus - Installing SQL Server 2014 Developer Edition (for Windows 7 and Vista)

Instructors

Articles

Popular Articles

Latest Articles

Trending Courses

Popular Courses

Popular Platforms

Learn more about the Courses

Thank You!

Download the Careers360 App on your Android phone