SQL Server 2017 Express Basics

Name: SQL Server 2017 Express Basics
Rating: 4.5 (512 reviews)

Update Notice 4/3/2018: Course has been updated to SQL Server 2017!

Created byT O, Adiv Abramson

Last updated 3/2018

English

English [Auto],Italian [Auto],

What you'll learn

Download and install SQL Server 2017 Express
Download and Install SQL Server Management Studio 2017
Write queries that control how many columns are displayed
Write queries that summarize large quantities of data
Understand how to choose different data types
Create a table to represent an entity
Insert records into tables
Create indexes to ensure data integrity
Learn how to restore databases
Learn how clustered indexes operate
Learn about SQL Server execution plans
Learn about a variety of SQL functions
Understand how to use a variety of online resources related to SQL Server
Learn how to read simple execution plans
Learn about physical SQL Server operators like Table Scan and Index Scan
Learn about scalar physical operators
Understand how/why to apply column constraints
Understand the concept of a computed column and why they are useful
Understand how to use a variety of logical conditions
Understand how to use where clauses
Understand how to use where clauses with two logical conditions
Describe the concept of a parameter
Explain the reasons for data conversions
Explain how to use N and $ symbols in data conversions
Understand the difference between clustered and non clustered indexes
Understand how to use key statistics like io/cpu readings
Understand how to differentiate between various icons in the sql plan panel
Understand the concept of a seek
Understand how to read and interpret a variety of online resources related to SQL Server
Understand how to read values from the various panels in SQL Server SSMS
Understand how to sort data on indexes
Understand how to read seek predicates
Understand how to build a nonclustered index with included columns
Understand how to summarize records with group by
Understand how to read various stats related to stream aggregation
Understand the concepts behind clustered index designs
Understand how to use the over clause
Understand how clauses are designed to run logically, step by step
Understand when aliased columns can be used with various clauses

Course content

5 sections • 41 lectures • 6h 8m total length

Get SQL Server 2017 with Management Studio5:38
Restore AdventureWorks2016CTP From Backup6:47

Display All the Columns in a Table8:46
Practical Consequences of Understanding and Not Understanding Data Types12:25
Select a Variety of Columns7:12
Introduction to Clustered Indexes, Execution Plans and Properties9:17
Learn how to generate and read an execution plan, understand query cost, and analyze clustered index scans and properties to interpret performance.
Index Scans, Table Scans, and Physical Operators9:07
Compare index scans and table scans, showing how copying to a temp table removes indexes and changes execution plans and timings, explained with physical operators.
User Defined Data Types and Online Resoureces7:53
Lesson Objectives:
Use where with multiple conditions
Use where with functions
Aliasing Columns6:34
Formatting Displayed Columns9:03
Using Functions that Operate on Two Columns6:53
Execution Plans with Computed Scalars11:26
Explore execution plans and computed scalars in SQL Server 2017 Express, examining how date differences feed a compute scalar, how Pascall casing guides naming, and how formatting affects data size.
Column Constraints8:06
Computed Columns9:24

Filtering Rows with Where, Part 18:07
Filtering Rows with Where, Part 29:04
Where with Two Logical Conditions7:52
Explore how two logical conditions in the where clause affect query results and execution time, including parameterization, data types, and avoiding implicit conversions with dollar notation.
Make a Table with a Clustered Index6:40
Index Scans, Table Scans with Where10:13
Explore index scans and table scans, including clustered index seeks with where predicates, and compare estimated cost and cpu cost to understand performance implications.
Index Scans Vs. Index Seeks with Non-Clustered Indexes9:23
Where with Like10:39
Index Seek with Statistics On7:55
Nonclustered Indexs with Ordering and Included Columns8:16
Clustered Indexes with Order By11:54
Learn how a clustered index on unit price changes query execution, reduces table scans, and reshapes sorts when using order by in SQL Server 2017 Express.
Clustered Index Design Guidelines13:48
Summarizing Rows with Group By, Part 110:06
Lesson Objectives:
Write code to summarize results with group by
Write code to combine group by and previous concepts
Summarizing Grouped Data with Group By, Part 29:10
Lesson Objectives:
Understand how to use the over clause
Understand how to use computed columns
Ordering by Group By Columns4:34
Explore how SQL Server orders by group by columns using a clustered index scan and sorts on job title and vacation hours. Understand aggregation timing and execution order.
Group by with Having9:06
Explore grouping with a having clause by computing total value per sales order using a temp table, then filter and sort results by total value.
Grouped Data with Formatting7:37
Breaking Rows into Sets with Over, Part 19:11
Breaking Rows into Sets with Over, Part 28:49
Format and sort with the over clause in SQL Server 2017 Express Basics: compute each line total as a percent of its order total and sort within each block.
Understand the Clause Sequence8:02
Using the Query Designer13:06
Lesson Objectives: Add Internet Information Services web server to computer

Deleting Records in Tables With/Without Indexes9:14
Explore deleting records with and without indexes, using unit price, and learn how a nonclustered index on unit price shifts from table scan to index seek, lowering estimated costs.
Deleting Records from a Table with a Clustered Index9:41
Explore how deleting records uses clustered indexes, compare cluster index delete with table delete, and analyze estimated versus actual row counts to optimize performance.
Inserting Values into Records, Part 111:58
Inserting Values into Records, Part 211:35
Inserting Values into Records, Part 3 (Clustered Indexes)8:09
Inserting Query Results into Tables14:37

Requirements

Must be able to download and install SQL Server 2017 Express
Must be able to download and install SQL Server 2017 Management Console
You need to use AdventureWorks2016CTP3 or AdventureWorks2017

Description

Most Important: Friends, please be sure to look through the course curriculum with meticulous care before buying to be sure this course will work for you. Thank you.

This course is being updated to bring it up to date with SQL Server 2017.
As of 3/27/2018, 40 videos have been replaced.
Replacement of all videos will happen over a month or so.
Reviews from before 3/6/2017 are from the older version of this course.
The new course features questions built right into the videos.
The new course features audio done with a studio microphone.
If you are at the intermediate or advanced level in SQL, do not join this course.
My explanation can be very detailed at times, so be sure you enjoy that.
Some of the videos cover programming, and some cover using a variety of resources without programming.
I use AdventureWorks2016CTP3, but you can also use AdventureWorks2017. Both are saved under the lesson 2 resources. There is no practical difference between the two for the purposes of this course. You must already know how to download lesson resources. I do not show how to do that.
Various topics are discussed through the videos. For example, there is not just one video on indexes because indexes are too big a topic. I discuss indexes through the videos where they play a role. The same applies to other concepts.
Be sure to look under the lesson resources for additional information and the code.
My explanations, in some of the videos, but not all, can be very detailed, so be sure you enjoy that before enrolling.
If you are looking for powerpoints, fancy graphics or a talking head, please do not enroll. I don't use those.
I do in different places use zooming to emphasize points.
The videos are an average in 10 minutes in length, so please be aware of that.
All the code is created in the Microsoft SQL Server Management Studio.
I talk about execution plans quite a lot over multiple videos.
This course does not cover joins, normalization or stored procedures.
Friends, to get the most from this course, please watch it all, from beginning to end. There is a lot of information here:)

Thank you,

Tom and Adiv

Who this course is for:

This is a course for beginners in SQL.
This is NOT a course for intermediate or advanced SQL programmers.
This is a course for those who like to pause and answer questions as they watch.
You should already have some familiarity with the concept of a table as a collection of rows and columns.
You should have some sense of the difference between kilobytes and megabytes.

SQL Server 2017 Express Basics

What you'll learn

Explore related topics

Course content

Get the Needed Tools2 lectures • 12min

Operating on Columns12 lectures • 1hr 46min

Operating on Rows20 lectures • 3hr 4min

Changing One Table6 lectures • 1hr 5min

Final Thoughts1 lecture • 1min

Requirements

Description

Who this course is for: