SQL Server: The 28 hour Masterclass course

Course
355 Lessons

Welcome to the most comprehensive course on SQL server you will see on the streaming platforms. This course is divided into three chapters: ◉ 8 hours on the Transact SQL development part ◉ 13 hours on the administration of a SQL server to become an experienced DBA ◉ 6 hours on how to improve your performance on your SQL server Want to get started on SQL then join me :)

Acheter maintenant

Contenu

Setting up your environment

Definition of SQL Server

Tools for working

Installation of Management Studio

First use of Management Studio

Installation of SQL Server

Let's create our first wonderful database and table!

What is a database ?

How to create a database in SQL Server ?

How to create a table ?

How to delete and modify the name of a database ?

Exercise on a database

Corrected Exercise on the database

exercice on the table

Corrected for the exercise on the table

Let's start playing with data with SELECT,UPDATE,INSERT and DELETE

Démo :Let's learn how to insert data with INSERT

Démo : Discover the SELECT statement

Démo: let's talk about DELETE statement in this vidéo

Démo : Discover the UPDATE statement

How to make an UPDATE on two columns ?

Démo : The comments and square brackets utility on SQL

Exercise and correction 1 to 5 on SELECT,UPDATE,INSERT,DELETE

Exercice 1 UK

Exercice 2

Exercice 3

Exercice 4

Exercice 5

Correction exercice 1

Corrected exercise 2

Corrected exercice 3

Correction Exercice 4

Corrected Exercice 5

Let's go a little further in the SQL transact (DISTINCT,LIKE ..)

Démo : Do you know the usefulness of the TOP ?

Démo : Remove duplicates with the DISTINCT

Démo: How to copy a table with the SELECT INTO ?

Démo : Discover the column aliases

Démo : Tackle the secrets of LIKE

Demo :Comparison operators = != and so on

Demo :The IN, NOT IN, IS and IS NOT operators

Exercise and correction 6 to 16 (DISTINCT,LIKE ...)

Exercice 6

Exercice 7

Exercice 8

Exercice 9

Exercice 10

Exercice 11

Exercice 12

Exercice 13

Exercice 14

Exercice 15

Exercice 16

Correction Exercice 6 to 16

Let's go even further with Transact SQL (AND,OR, MIN,MAX...)

How to SELECT a range of data with the BETWEEN

Discover aggregate functions with MIN MAX and AVG

ORDER BY

Let's discover the COUNT and SUM

Let's discover the specificities of HAVING

Group several results with the GROUP BY

The AND and the OR

Lets talk about the PRINT

Exercice 17

Exercice 18

Exercice 19

Exercice 20

Exercice 21

Exercice 22

Exercice 23

Exercice 24

Exercice 25

Correction part 2

Learn to combine data with the power of JOINS

INNER JOIN

How to make a join with several tables

The CROSS JOIN

Update a table with JOIN

The CROSS APPLY and OUTER APPLY

FULL JOIN

Do you know EXECPT and INTERSECT

LEFT JOIN VS NOT IN VS NOT EXISTS

Do You know the HINT on the joins

LEFT and RIGHT JOIN

Exercice 26

Exercise 27

Exercice 28

Exercise 29

Exercice 30

Exercice 31

Exercice 32

Exercice 33

Let's go even further with Transact SQL Part 3

The CASE WHEN

The SUBSTRING

TRIMLTRIMRTRIM and UPPER LOWER

Do you know the OFFSET FETCH NEXT ROWS ?

IIF

Drop if exists

LEFT and RIGHT

COLLESCE

LEN and REPLACE

The FORMAT

CHOOSE

UNION and UNION ALL

Exercice 34

exercice 35

Exercice 36

Exercice 37

Exercice 38

Exercice 39

Exercice 40

Exercice 41

Correction exercice Partie 3

The conversion functions and the different types of columns

CAST and CONVERT

TRY_CONVERT and TRY_CAST

The columns with string types CHAR and VARCHAR.

The columns with numeric types INT,SMALLINT,BIGINT,TYNINT

The columns with DATE types DATETIME2,DATETIME,DATE,SMALLDATETIME

Démo : Add, modify, delete, rename a column

Let's discover together the usefulness of views and stored procedure on SQL

Let's discover together the usefulness of views on SQL

Démo : Let's create our first stored procedure !

How to declare a variable in a Stored procedure ?

Exercice 42

exercice 43

Exercice 44

Exercice 45

Exercice 46

Exercice 47

Corrected exercise 42 to 47

TSQL :Let's deepen our knowledge of TSQL even more

Démo : IF/ELSE and WAITFOR

Démo : CHARINDEX REVERSE REPLICATE

Démo : CONCAT and CONCAT WS

Démo : STRING SPLIT/STUFF

Démo : Discover the powerful MERGE

Démo : DATEADD and DATEDIFF

Démo : DATENAME/DATEPART/GETUTCDATE/GETDATE

Démo : EOMONTH

Let's discover the little known CTE (common table expression) and derived table

Démo : Let's see a simple CTE and derived table.

Démo : CTE and the semicolon

Démo : CTE and the restriction

Démo : When should we specify the name of the columns in the CTE

Define several CTE UK finale (2).mp4

Démo : CTE with the view and stored procedure

Changing data via the CTE UK.mp4

Démo : Several SELECT in the CTE

The Schema, the functions and Temporary tables, variable tables and global table

Démo : Let's see the définition and the création of a schéma

Démo : Let's discover the scalar function

Démo : The function type table

Démo : The global table

The Temporary table

Démo : The variable table

Let's discover the power of the Ranking functions

Example of an introduction to windowing functions

Démo: The OVER clause with PARTITION BY

Demo : The functions of RANKING (ROW_NUMBER, DENSE _RANK, RANK, NTILE)

Démo : The functions of RANKING in the real world

Démo: Aggregation with RANKING functions

Démo : Aggregation with RANKING functions in the real world

Demo : The analytical functions LAG and LEAD

Démo : ROWS /RANGE UNBOUNDED PRECEDING

Demo: The analytical functions FIRST_VALUES and LAST_VALUES

Chapter 1 : Administration on SQL Server the BACKUP and RESTORE course

Démo: Simple BACKUP and RESTORE

Démo: The different database recovery model in SQL Server

Why you should to backup your data?

Démo : BACKUP and RESTORE LOGS

Démo : BACKUP WITH INIT

Démo: Understand what is a CHECKPOINT on your SQL Server engine

The différential BACKUP

Démo: RESTORE moving the directory files

Démo : BACKUP WITH COPY ONLY

Démo : BACKUP WITH CHECKSUM

Démo: What is the Tail log backup ?

Démo : How to RESTORE just a single page !

Démo: How to increase performance, with BACKUP on several files !

Démo: BACKUP WITH COMPRESSION and WITH MIRROR

RESTORE WITH STOPAT

restore WITH STANDBY

Does compression do a CHECKSUM ?

restore metadata english.mp4

BACKUP with BUFFERCOUNT, MAXTRANSFERTSIZE

Restore from a SNAPSHOT avec sous titre anglais.mp4

Démo : How to RESTORE from a LSN ? (log sequential number)

Don't forget to Backup your system databases !

Démo : How to RESTORE your MASTER system database ?

Démo : How to RESTORE ALL systems databases ?

Conclusion on BACKUP and RESTORE course

Administration chapter 2 : Detecting and Correcting Database Corruption

faire face à la corruption anglais.mp4

What are the causes of corruption?

Corruption that disappears?

Démo : TORN PAGE DETECTION and PAGE CHECKSUM

Démo :IO/errors

Verification of data consistency

Démo : Creation of a job for consistency check

Explanation on the CHECKDB

Survey on the CHECKDB

Démo : Options on the CHECKDB

Démo : Repair of a non-clustered index

Démo : Let's launch our first REPAIR !

Démo : DBCC IND and DBCC PAGE

The different states of a database

Démo : Corruption with XVI32 editor

Démo : Recreating a log file by attaching a database

Démo : The EMERGENCY mode

Démo : Repairing a database using the HACK ATTACH mode

Démo : Restore from a corrupted backup

Restore Vs Repair

Conclusion on Detecting and Correcting Database Corruption

Administration SQL chapter 3: Configuring and modifying a BDD on SQL

Activation of the Contained database UK.srt

Démo : Explanation of the compatibility mode

Attach detach a BDD

Démo : AUTO options on a database

Démo : How to change your collation ?

Demo: Changing the DB owner

Démo : Define the files when creating the DB

Démo : OFF LINE and EMERGENCY on a database

CHECKSUM page UK.mp4

Démo : Change configurations limited to databases

Démo : What is the CHANGE TRACKING on your database ?

Démo : SET ANSI_NULL and SET QUOTE IDENTIFIER

Démo : What is the TRUSTWORTHY option on a Database ?

target.mp4

Démo : SET ANSI_NULL and SET QUOTE IDENTIFIER

SINGLE_USER et RESTRICTED_USER, READ_ONLY

Démo : Définition and création of a filegroup

Démo : Discover the accelerated recovery database on SQL Server 2019

Démo : Be careful on your AUTOGROW settings !

Démo : What is the MAXDOP on your database ?

Démo: Definition and activation of a broker

Démo : Let's discover the powerful Query Store !

IFI UK finale sans sous titre.mp4

Administration chapter 4 : The different modes of isolation

Presentation of the different isolation modes on SQL

Démo : Do you know the REPETEABLE READ isolation mode ?

Démo: Let's discover the SERIALIZABLE Mode !

Démo : The READ UNCOMMITED isolation mode

Démo : The SNAPSHOT isolation mode

Do you know the UPLOCK command ?

Démo : The READ COMMITED isolation mode

Démo : What is a DEADLOCK ?

Administration chapter 5 :The Trigger,synonym, the sequence, and the constraints

Démo: Discover how to create a trigger

Démo : What is a virtual table in a trigger ?

Démo : Learn how to define a trigger on a login SQL

Démo : The constraint CHECK

Démo : Let's discover the synonym !

Démo : The primary Key

Démo : The constraint DEFAULT

Démo : The constraint UNIQUE

Démo : Let's discover the foreign key

Démo: What is the Cascading Referential Integrity Constraint ?

Another topics on the Adminstration of your SQL Server.

Démo : Do you know the CDC (change data capture) ?

Démo : Discover the fantastic Row Level security

Démo : Discover the powerful Dynamic Data Masking !

Démo : Do you know what is the temporal data ?

* Performance Part 1 : The complete course on the Index *

What is a index ?

Démo : Définition and création of a clustered index

Démo : Définition and création of a non clustered index

Structure of the clustered index with a non clustered index

Démo : The table SEEK

Démo : The table SCAN

Démo : Do you know the covering Index ?

Démo : Be careful about the duplicate index !

Démo : Learn the INCLUDE clause on your index

Démo : Let's discover the UNIQUE index

Démo : Indexing and column order

Démo : Learn to consolidate your indexes!

Démo : Improve performance by deactivating the indexes

Démo : What is a filtered index ?

Démo : The Filtered indexes and the cache plan

How to choice your clustered index ?

Clustered index choice UK.mp4

Démo : What is the OVER indexing ?

Démo : Let's a create a indexed view !

Démo : The index columnstore

Why is fragmentation important?

Démo : Example of fragmentation

Démo: What is the FILLFACTOR ?

Démo : Tracking page splits

Démo : REBUILD vs REORGANIZE

Démo : REBUILD INDEX with the option ABORT_AFTER_WAIT

Avoid an index clustered on a NEWID column !

Performance Part 2 :Other performance topics

Do you know the term SARGABLE on SQL Server?

Démo : Be careful on your AUTOGROW settings !

Démo : Discover the accelerated recovery database on SQL Server 2019

Démo : Don't forget to activate the IFI on SQL Server !

* Enter the interview room, it's HERE !!! *

Introduction to this interview

Questions 1 to 10

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Answers from 1 to 10

Answer 1

Answer 2

Answer 3

Answer 4

Answer 5

Answer 6

Answer 7

Answer 8

Answer 9

Answer 10

Questions from 11 to 20

Question 11

Question 12

Question 13

Question 14

Question 15

Question 16

Question 17

Question 18

Question 19

Question 20

Answers from 11 to 20

Answer 11

Answer 12

Answer 13

Answer 14

Answer 15

Answer 16

Answer 17

Answer 18

Answer 19

Answer 20

Questions from 21 to 30

Question 21

Question 22

Question 23

Question 24

Question 25

Question 26

Question 27

Question 28

Question 29

Question 30

Answers from 21 to 30

Answer 21

Answer 22

Answer 23

Answer 24

Answer 25

Answer 26

Answer 27

Answer 28

Answer 29

Answer 30

Questions from 31 to 40

Question 31

Question 32

Question 33

Question 34

Question 35

Question 36

Question 37

Question 38

Question 39

Question 40

Answers from 31 to 40

Answer 31

Answer 32

Answer 33

Answer 34

Answer 35

Answer 36

Answer 37

Answer 38

Answer 39

Answer 40