SQL Server: The 28 hour Masterclass course (16 courses in 1)

Presentation and overview
1 All my training catalogue (16 courses) is on Podia, join me )
2 READ THIS BEFORE YOU BUY! you will save money
3 Presentation and overview
4 Small explanation on the subtitles

Setting up your environment
5 Definition of SQL Server
6 Tools for working
7 Installation of SQL Server
8 Installation of Management Studio
9 First use of Management Studio

Part 1 The Transact SQL (10 hours of class)
10 Welcome on the first chapter about the Transact SQL

Let’s create our first wonderful database !
11 What is a Database
12 Demo How to create a database in SQL Server
13 Demo How to delete and modify the name of a database
14 Database Exercise
15 Corrected Exercise on the database

Let’s create our first fantastic table !
16 What is a table
17 Démo How to create a table
18 Démo How to modify and delete a table
19 Exercise on the table
20 Corrected for the exercise on the table

Let’s start playing with data with SELECT,UPDATE,INSERT and DELETE
21 Démo Let’s learn how to insert data with INSERT
22 Démo Discover the SELECT statement
23 Démo Discover the UPDATE statement
24 Démo let’s talk about DELETE statement in this vidéo
25 Démo How to make an UPDATE on two columns
26 Démo The comments and square brackets utility on SQL
27 How to INSERT several identical values at once

Exercise and correction 1 to 5 on SELECT,UPDATE,INSERT,DELETE
28 Exercise 1
29 Exercise 2
30 Exercise 3
31 Exercise 4
32 Exercise 5
33 Correction Exercise 1
34 Correction Exercise 2
35 Correction Exercise 3
36 Correction Exercise 4
37 Correction Exercise 5
38 Focus on previous sessions

Let’s go a little further in the SQL transact (DISTINCT,LIKE ..)
39 Démo Discover the column aliases
40 Démo Tackle the secrets of LIKE
41 Démo Do you know the usefulness of the TOP
42 Démo Remove duplicates with the DISTINCT
43 Demo Comparison operators = != and so on
44 Demo The IN, NOT IN, IS and IS NOT operators
45 Démo How to copy a table with the SELECT INTO

Exercise and correction 6 to 16 (DISTINCT,LIKE …)
46 Exercise 6
47 Exercise 7
48 Exercise 8
49 Exercise 9
50 Exercise 10
51 Exercise 11
52 Exercise 12
53 Exercise 13
54 Exercise 14
55 Exercise 15
56 Exercise 16
57 Correction of exercise 6 to 16

Let’s go even further with Transact SQL (AND,OR, MIN,MAX…)
58 What are we going to discuss in this second part on the Transact SQL
59 Démo Let’s talk about the AND,OR operators
60 Démo Sort your data with ORDER BY
61 Démo Discover aggregate functions with MIN MAX and AVG
62 Démo Let’s discover the COUNT and SUM
63 Démo How to SELECT a range of data with the BETWEEN
64 Démo Group several results with the GROUP BY
65 Démo Let’s discover the specificities of HAVING
66 Démo Let’s talk about the PRINT
67 Focus on previous sessions

Exercise and correction 17 to 25 (AND,OR, MIN,MAX…)
68 Exercise 17
69 Exercise 18
70 Exercise 19
71 Exercise 20
72 Exercise 21
73 Exercise 22
74 Exercise 23
75 Exercise 24
76 Exercise 25
77 Correction Exercise 17 to 25 on part 2

Learn to combine data with the power of JOINS
78 Introduction on the Joins
79 Démo INNER JOIN
80 Démo LEFT and RIGHT JOIN
81 Démo FULL OUTER JOIN
82 Démo The CROSS JOIN
83 Démo The CROSS APPLY and OUTER APPLY
84 Démo How to make a join with several tables
85 Démo Update a table with JOIN
86 Démo LEFT JOIN VS NOT IN VS NOT EXISTS
87 Démo Do you know EXECPT and INTERSECT
88 Démo Do You know the HINT on the joins

Exercise and correction 26 to 33 on the joins
89 Set up exercises on the joins
90 Exercise 26
91 Exercise 27
92 Exercise 28
93 Exercise 29
94 Exercise 30
95 Exercise 31
96 Exercise 32
97 Exercise 33
98 Correction Exercise 26 to 33

Let’s go even further with Transact SQL Part 3
99 What are we going to discuss in this third part on the Transact SQL
100 Démo The CASE WHEN
101 Démo The FORMAT
102 Démo The IIF
103 Démo Do you know the OFFSET FETCH NEXT ROWS
104 Démo The LEFT and RIGHT
105 Démo TRIMLTRIMRTRIM and UPPER LOWER
106 Démo The SUBSTRING
107 Démo LEN and REPLACE
108 Démo The CHOOSE function
109 Démo COALLESCE
110 Démo UNION and UNION ALL
111 Démo DROP IF EXISTS

Exercise and correction 34 to 41 (SUBSTRING,CHOOSE..)
112 Let’s go for the exercice Part 3 !
113 Exercise 34
114 Exercise 35
115 Exercise 36
116 Exercise 37
117 Exercise 38
118 Exercise 39
119 Exercise 40
120 Exercise 41
121 Corrected exercise 34 to 41

The conversion functions and the different types of columns
122 Démo CAST and CONVERT
123 Démo TRY CONVERT and TRY CAST
124 The columns with string types CHAR and VARCHAR
125 The columns with numeric types INT,SMALLINT,BIGINT,TYNINT
126 The columns with DATE types DATETIME2,DATETIME,DATE,SMALLDATETIME
127 Démo Add, modify, delete, rename a column

Let’s discover together the usefulness of views and stored procedure on SQL
128 Let’s discover together the usefulness of views on SQL
129 Démo Let’s create our first view !
130 Démo Let’s create our first stored procedure !
131 How to declare a variable in a Stored procedure

Exercise and correction 42 to 47 (Stored procedure and view)
132 Exercise 42
133 Exercise 43
134 Exercise 44
135 Exercise 45
136 Exercise 46
137 Exercise 47
138 Corrected exercise 42 to 47

TSQL Let’s deepen our knowledge of TSQL even more
139 Démo IFELSE and WAITFOR
140 Démo CHARINDEX REVERSE REPLICATE
141 Démo CONCAT and CONCAT WS
142 Démo STRING SPLITSTUFF
143 Démo Discover the powerful MERGE
144 Démo DATENAMEDATEPARTGETUTCDATEGETDATE
145 Démo DATEADD and DATEDIFF
146 Démo EOMONTH

Let’s discover the little known CTE (common table expression) and derived table
147 Démo Let’s see a simple CTE and derived table
148 Démo CTE and the semicolon
149 Démo CTE and the restriction
150 Démo When should we specify the name of the columns in the CTE
151 Démo Define several CTE in the same request
152 Démo CTE with the view and stored procedure
153 CTE Changing data via the CTE
154 Démo Several SELECT in the CTE

The Schema, the functions and Temporary tables, variable tables and global table
155 Démo Let’s see the définition and the création of a schéma
156 Démo The function type table
157 Démo Let’s discover the scalar function
158 Démo The variable table
159 Démo The global table
160 Démo The temporary table

Let’s discover the power of the Ranking functions
161 Example of an introduction to windowing functions
162 Démo The OVER clause with PARTITION BY
163 Demo The functions of RANKING (ROW NUMBER, DENSE RANK, RANK, NTILE)
164 Démo The functions of RANKING in the real world
165 Démo Aggregation with RANKING functions
166 Démo Aggregation with RANKING functions in the real world
167 Demo The analytical functions LAG and LEAD
168 Démo ROWS RANGE UNBOUNDED PRECEDING
169 Demo The analytical functions FIRST VALUES and LAST VALUES

Another topics on the Transact SQL
170 TRUNCATE VS DELETE

Chapter 1 Administration on SQL Server the BACKUP and RESTORE course
171 Why you should to backup your data
172 Démo Simple BACKUP and RESTORE
173 Démo BACKUP and RESTORE LOGS
174 The différential BACKUP
175 Démo Differential BACKUP and RESTORE
176 Démo BACKUP WITH INIT
177 Démo BACKUP WITH COPY ONLY
178 Démo RESTORE moving the directory files
179 Démo The different database recovery model in SQL Server
180 Démo What is the Tail log backup
181 Démo BACKUP WITH CHECKSUM
182 Démo How to RESTORE just a single page !
183 Démo RESTORE WITH STOPAT
184 Démo RESTORE WITH STANDBY
185 Démo RESTORE from a SNAPSHOT
186 Démo How to RESTORE from a LSN (log sequential number)
187 Does a COMPRESSION do a CHECKSUM
188 Démo Understand what is a CHECKPOINT on your SQL Server engine
189 Démo Consult BACKUP and RESTORE metadata
190 Démo Learn how to simply clone your database !
191 Démo How to increase performance, with BACKUP on several files !
192 Démo BACKUP WITH COMPRESSION and WITH MIRROR
193 Démo BACKUP WITH BUFFERCOUNT and MAXTRANSFERTSIZE
194 Never forget to BACKUP yours system database !!!
195 Démo How to RESTORE your MASTER system database
196 Démo How to RESTORE ALL systems databases
197 Conclusion on BACKUP and RESTORE course

Administration chapter 2 Detecting and Correcting Database Corruption
198 Why is this course important
199 What is the first thing to think about corruption
200 What are the causes of corruption
201 Corruption that disappears
202 Démo IOerrors
203 Démo TORN PAGE DETECTION and PAGE CHECKSUM
204 Démo DBCC IND and DBCC PAGE
205 Verification of data consistency
206 Démo Creation of a job for consistency check
207 Explanation on the CHECKDB
208 Survey on the CHECKDB
209 Démo Options on the CHECKDB
210 Démo BACKUP WITH CHECKSUM
211 Démo Let’s launch our first REPAIR !
212 Démo Repair of a non-clustered index
213 Démo Restore from a corrupted backup
214 Restore Vs Repair
215 The different states of a database
216 Démo Recreating a log file by attaching a database
217 Démo Corruption with XVI32 editor
218 Démo The EMERGENCY mode
219 Démo Repairing a database using the HACK ATTACH mode
220 Conclusion on Detecting and Correcting Database Corruption

Administration SQL chapter 3 Configuring and modifying a BDD on SQL
221 What are we going to see in this course
222 Démo Define the files when creating the DB
223 Démo Définition and création of a filegroup
224 Démo What is the Containment database
225 Démo Explanation of the compatibility mode
226 Démo OFF LINE and EMERGENCY on a database
227 Démo SINGLE USER et RESTRICTED USER, READ ONLY
228 Démo Create a database by attaching a file
229 Démo AUTO options on a database
230 Démo What is the TARGET RECOVERY Time
231 Démo But what is CHECKPOINT anyway
232 Demo Changing the DB owner
233 Démo What is the TRUSTWORTHY option on a Database
234 Démo What is the different database recovery model on your database
235 Démo What is the MAXDOP on your database
236 Démo How to change your collation
237 Démo Definition and activation of a broker
238 Démo SET ANSI NULL and SET QUOTE IDENTIFIER
239 Démo Change configurations limited to databases
240 Démo What is the CHANGE TRACKING on your database
241 Démo Why do not activate the DB CHAINING on your BDD
242 Démo Let’s discover the powerful Query Store !
243 Démo Discover the accelerated recovery database on SQL Server 2019
244 Démo Be careful on your AUTOGROW settings !
245 Démo Don’t forget to activate the IFI on SQL Server !
246 Démo Learn how to simply clone your database !
247 Conclusion on this section

Administration chapter 4 The different modes of isolation
248 Presentation of the different isolation modes on SQL
249 Démo The READ COMMITED isolation mode
250 Démo The READ UNCOMMITED isolation mode
251 Démo Do you know the REPETEABLE READ isolation mode
252 Démo Let’s discover the SERIALIZABLE Mode !
253 Démo The SNAPSHOT isolation mode
254 Do you know the XABORT command
255 Démo What is a DEADLOCK
256 Do you know the UPLOCK command
257 Conclusion on the isolation mode

Administration chapter 5 The course on Security and Privilege Management
258 Introduction on the security
259 Démo Creating our first account
260 Démo The Default database configuration
261 Démo Discover the Server Roles on your SQL Server !
262 Démo What is the Row level security
263 Démo Discover the schema

Administration chapter 6 The Trigger,synonym, the sequence, and the constraints
264 Démo Let’s discover the synonym !
265 Démo Discover how to create a trigger
266 Démo What is a virtual table in a trigger
267 Démo Learn how to define a trigger on a login SQL
268 Démo How to set up a IDENTITY on your tables
269 Démo Let’s talk about the Powerful SEQUENCE !
270 Démo The primary Key
271 Démo Let’s discover the foreign key
272 Démo The constraint UNIQUE
273 Démo The constraint CHECK
274 Démo The constraint DEFAULT
275 Démo What is the Cascading Referential Integrity Constraint

Another topics on the Adminstration of your SQL Server
276 Démo Discover the powerful Dynamic Data Masking !
277 Démo Do you know what is the temporal data
278 Démo Discover the fantastic Row Level security
279 Démo Do you know the CDC (change data capture)

Performance Part 1 The complete course on the Index
280 Setting up databases
281 What is a index
282 Démo Définition and création of a clustered index
283 Démo Définition and création of a non clustered index
284 Structure of the clustered index with a non clustered index
285 Démo The table SEEK
286 Démo The table SCAN
287 Démo Do you know the covering Index
288 Démo Be careful about the duplicate index !
289 Démo Learn the INCLUDE clause on your index
290 Démo Let’s discover the UNIQUE index
291 Démo Learn to consolidate your indexes!
292 Démo Indexing and column order
293 Démo Improve performance by deactivating the indexes
294 Démo What is a filtered index
295 Démo The Filtered indexes and the cache plan
296 How to choice your clustered index
297 Avoid an index clustered on a NEWID column !
298 Démo In search of the perfect index
299 Démo What is the OVER indexing
300 Démo The index columnstore
301 Démo Let’s a create a indexed view !
302 Why is fragmentation important
303 Démo Example of fragmentation
304 Démo What is the FILLFACTOR
305 Démo Tracking page splits
306 Démo REBUILD vs REORGANIZE
307 Démo REBUILD INDEX with the option ABORT AFTER WAIT
308 Conclusion on the index

Performance Part 2 Other performance topics
309 Do you know the term SARGABLE on SQL Server
310 Démo Don’t forget to activate the IFI on SQL Server !
311 Démo Be careful on your AUTOGROW settings !
312 Démo Discover the accelerated recovery database on SQL Server 2019

Enter the interview room, it’s HERE !!!
313 Introduction to this interview

Questions 1 to 10
314 Question 1
315 Question 2
316 Question 3
317 Question 4
318 Question 5
319 Question 6
320 Question 7
321 Question 8
322 Question 9
323 Question 10

Answers from 1 to 10
324 Answer 1
325 Answer 2
326 Answer 3
327 Answer 4
328 Answer 5
329 Answer 6
330 Answer 7
331 Answer 8
332 Answer 9
333 Answer 10
334 Démonstration 1 for the answer 10
335 Demonstration 2 for the answer 10

Questions from 11 to 20
336 Question 11
337 Question 12
338 Question 13
339 Question 14
340 Question 15
341 Question 16
342 Question 17
343 Question 18
344 Question 19
345 Question 20

Answers from 11 to 20
346 Answer 11
347 Answer 12
348 Answer 13
349 Answer 14
350 Answer 15
351 Answer 16
352 Answer 17
353 Answer 18
354 Answer 19
355 Answer 20

Questions from 21 to 30
356 Question 21
357 Question 22
358 Question 23
359 Question 24
360 Question 25
361 Question 26
362 Question 27
363 Question 28
364 Question 29
365 Question 30

Answers from 21 to 30
366 Answer 21
367 Answer 22
368 Answer 23
369 Answer 24
370 Answer 25
371 Answer 26
372 Answer 27
373 Answer 28
374 Answer 29
375 Answer 30

Questions from 31 to 40
376 Question 31
377 Question 32
378 Question 33
379 Question 34
380 Question 35
381 Question 36
382 Question 37
383 Question 38
384 Question 39
385 Question 40

Answers from 31 to 40
386 Answer 31
387 Answer 32
388 Answer 33
389 Answer 34
390 Answer 35
391 Answer 36
392 Answer 37
393 Answer 38
394 Answer 39
395 Answer 40