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:

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

  • 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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.