SQL for beginners

Want to learn SQL and need some guidance? SQL is the language you communicate with most of databases today, including SQL Server.

Watch these videos:

SQL for absolute beginners https://www.youtube.com/watch?v=27axs9dO7AE
SELECT basics https://www.youtube.com/watch?v=SoxHivg9Ldw
SELECT extended https://www.youtube.com/watch?v=D37_7p0-iKM
Subqueries https://www.youtube.com/watch?v=GpC0XyiJPEo
Grouping – have SUM() fun! https://blog.datamaster.hr/have-sum-fun/
Formatting SQL code (VERY important!): https://blog.datamaster.hr/formatting-tsql-code/
Entity Relationship diagrams:
- ERD using Lucidchart (free at https://www.lucid.co) tutorial: https://www.youtube.com/watch?v=xsg9BDiwiJE
- ERD short example: https://www.youtube.com/watch?v=6uwuNRUUimY
- ERD using draw.io (free desktop and online app): https://www.youtube.com/watch?v=lAtCySGDD48
- A bit more theory: https://www.youtube.com/watch?v=5nGC4fyFPes
- Learn relations 1:n (one to many) and n:n (many to many).
- Learn how to convert these relations into tables and foreign keys. n:n requires adding a “linker” table!
- Ask chatGPT this (copy-paste into https://chat.openai.com)
  - When designing databases, we create an Entity-Relationship Diagram. There are “one-to-one,” “one-to-many,” and “many-to-many” relationships. How can we model the “one-to-many” relationship at the physical level? How can we model the “many-to-many” relationship at the physical level?

To learn better, it is not enough to watch videos, you need to get your hands dirty! Install these:

A free version of SQL Server called “Developer” edition from https://go.microsoft.com/fwlink/?linkid=866662
SQL Sever Management Studio from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
sp_xdetails stored procedure: https://blog.datamaster.hr/sp_xdetails/

To know SQL, you must be familiar at least with (google tsql+word, eg “tsql having”):

To know TSQL (assuming you know SQL):

Stored procedures – difference between parameters VS variables
Table Valued Parameters – https://blog.datamaster.hr/how-to-pass-arrays-to-sql-tvp-the-ultimate-guide/
Indexed views – without aggregates VS with aggregates

To know basics of table design you must at minimum be familiar with:

Data types – how many bytes they take? What is min and max value?
- BIT, TINYINT, INT, BIGINT
- DATE, SMALLDATETIME, DATETIME2(2) – Tip: avoid DATETIME
- CHAR, VARCHAR, NVARCHAR, NVARCHAR(MAX)
- What is unicode? What is COLLATION? How to get UTF8 in SQL Server?
Constraints: PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK

Indexes – crucial for performance

How indexes work
INCLUDED vs KEY columns of a non-clustered index
- Index physical structure: root page, intermediate pages, leaf pages – learn how index internals work!
CLUSTERED vs NON-CLUSTERED (NC) index: The Clustered Index Debate
FILTERED nc index
UNIQUE index, Unique filtered index.
COVERING index – Tip: covers a certain query. Do not cover unless you have to (query is frequent and non-covering index is really not good enough)

Database Engine

Data files, log file, and Filegroups. Initial size and growth step.
Pages and Extents
What is a checkpoint? What is a “dirty” page vs “clean” page? https://blog.datamaster.hr/checkpoint-vs-lazy-writer/
How WAL (Write Ahead Logging) works?
What is VLF (Virtual Log File)? https://blog.datamaster.hr/transaction-log-truncate-why-it-didnt-shrink-my-log/
What is LSN (Log Sequence Number)?
Backup: Full, Differential, Log (Transaction Log backup)

Links

Basic SQL: https://www.youtube.com/watch?v=vDMehWw11wI
SqlSkills MCM training videos – pay attention to “Clustered index debate” https://www.sqlskills.com/sql-server-resources/sql-server-mcm-training-videos/
SqlMaestros free training resources: https://sqlmaestros.com/sql-server-notes-amit-bansal/

I will update this page with further info a bit later.