SQL for absolute 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”):
- LEFT JOIN, FULL JOIN, CROSS JOIN, OUTER APPLY, CROSS APPLY
- INTERSECT, EXCEPT, UNION ALL, UNION – Note: UNION ALL is different from UNION!
- CASE, ISNULL, NULLIF, CONVERT, DATALENGTH
- HAVING, MIN, MAX, AVG, SUM, COUNT
- GROUPING SETS
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
- 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)
- 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)
- 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.