Understanding MS SQL Server Magic -- Beginners' Course

This course is designed for those that are completely new to SQL or would like to brush up on their skills. If you don't yet know how you'll be using SQL in the future, if you'll go into analysis, development or anything else, this course is for you! We'll be using v2019 and start at the installation of SQL Server as well as the tools and databases we'll be using (SSMS and AdventureWorks, which is Microsoft's "teaching" database).

The course has been designed with efficiency as a prime driver so we'll be talking a fair bit about what to do and what not to do as well as some theory on data storage, data types, etc so that you get an understanding of why certain things are the way they are rather than "just do". Apart from Section 7, the lectures are designed to be not too technical for beginners' level, while S7 is a bit more technical but will be very beneficial for your better understanding of what is going on in the background.

In terms of style/delivery: UK English, bit of added sarcasm (very minimal), generally cool and avoiding buzz. Also I tend to use "you" ("if you have a look at this/do that") rather us ("let us do this/that") in most cases. It's just to say some people prefer one or the other so you know what to expect. Furthermore I don't use "I'm going to go ahead and...". Just saying. :)

Course structure:

1: Intro to SQL and SSMS

1. What’s SQL, Uses, Dialects, Analysis, Development Purposes…

2. Install SQL Server, SSMS

3. Quick intro to SSMS

4. Get AdventureWorks

5. Settings & line numbers

2: Data Concepts

1. Data Concepts -- Tables, Views, Temp Tables

2. Data Concepts -- Keys

3. Data Concepts -- Stored Procs

4. Data Types Short & NULL Values

5. Indexes Short

6. Best Practices Naming, Coding (Tabs, 1=1, Using [], Etc.)

3: Basic Commands

1. SELECT/DISTINCT & TOP ++ Where data goes (onto the screen that is.)

2. WHERE, <>, =, !=, AND/OR/NOT, text (‘’) -- relation to indexes

3. LIKE, %

4. ORDER BY -- when not to use it

5. GROUP BY & HAVING

6. UNION/UNION ALL/INTERSECT/EXCEPT

7. JOINs

4: Basic Functions

1. COUNT

2. SUM, AVG

3. MIN, MAX

4. LEN

5. LTRIM/RTRIM

6. UCASE/LCASE

7. SUBSTRING

8. COALESCE

5: Table Operations

1. CREATE TABLE

2. SELECT INTO

3. ALTER TABLE (drop col, add col, change data type, add index)  

4. DROP TABLE vs DELETE FROM vs TRUNCATE

6: Modifying Data

1. INSERT (gui, manual, insert from elsewhere)

2. UPDATE

7: Tech Deep-Dive

1. sys tables for finding information   

2. Data storage

3. More About Heaps

4. More About Indexes & Fragmentation