Channel Title: Database by Doug Channel Description: Basic, intermediate, and advanced Structured Query Language (SQL) demonstrations using Microsoft's SQL Server. SQL from the very beginning, plus best practices for coding and performance. Demos for beginners, programmers, and "accidental" database administrators. <video> Model Building: The Big Picture Description: A big picture look at the model building process for data analytics, statistics, data science, etc. In honor of a great professor I had in PhD school! Table of Contents: 00:00 - Introduction 00:00 - Marker 1 00:10 - Slide 2 00:28 - Slide 3 00:57 - Slide 4 01:17 - Slide 5 01:41 - Slide 6 02:03 - Slide 7 02:28 - Slide 8 02:58 - Slide 9 03:21 - Slide 8 03:27 - Slide 9 03:29 - Slide 10 03:44 - Slide 11 03:46 - Slide 12 03:55 - Slide 13 04:27 - Slide 14 05:54 - Slide 15 06:16 - Slide 16 06:41 - Slide 17 07:04 - Slide 18 08:13 - Slide 19 08:34 - Slide 20 09:26 - Slide 21 </video> <video> Azure Data Studio Introduction Description: A brief introduction to Azure Data Studio Table of Contents: 00:00 - Introduction 00:30 - Connecting to a server 01:47 - Explorer 02:03 - Properties Dialog 02:16 - Select Query 02:37 - IDE Experience 03:05 - Exporting 03:26 - JSON Export 03:47 - edit JSON 04:19 - Server Groups 05:04 - copy table schema 05:49 - SQL Notebook 06:09 - Markdown 06:28 - SQL Code Block 07:14 - Command Palette 07:35 - 3rd Party Extensions 08:01 - no diagramming 08:26 - Git / Version Control Integration 08:56 - Cross Platform 09:07 - Continuous Release </video> <video> Using the SwitchOffset function for Time Zones Description: SwitchOffset is a function that will shift a time in one time zone to the correct time in another time zone. It takes a DATETIMEOFFSET data type, and produces a DATETIMEOFFSET data type. Get the code here: https://douglaskline.blogspot.com/2019/01/the-switchoffset-function.html </video> <video> Using DISTINCT in SQL Description: A brief beginner's introduction to the DISTINCT operator in SQL. DISTINCT creates a set of unique records, based on field values. In other words, duplicates records, based on field values, are removed. DISTINCT is often used with COUNT, to achieve a count of distinct values. Be aware that NULL is treated as a distinct value in terms of records, but COUNT ignores NULLs. See the full code here: https://douglaskline.blogspot.com/2018/10/using-distinct-in-sql.html </video> <video> IN versus EXISTS - are they the same? Description: Intermediate level video on Structured Query Language. The IN and EXISTS operators are sometimes viewed as "equivalent". They serve a similar purpose and are sometimes used to produce the same records. However, their underlying mechanisms are quite different. This example using the Northwind database demonstrates how NULLs are treated differently when using NOT IN versus NOT NULL. </video> <video> Using the CAST function in SQL Description: Using CAST() to convert from one data type to another in SQL. See the code here: https://douglaskline.blogspot.com/2018/10/using-cast-function-in-sql.html </video> <video> SQL to find NULL in any column Description: You've got a table with hundreds of columns. You'd like to find records that have a NULL in any of those columns. I'll show how to use sys.columns to get the list of all the columns and create a SELECT statement that tests for NULLs in any of those columns. Get the code here: https://douglaskline.blogspot.com/2024/05/find-null-in-any-column.html </video> <video> Running SQL Server in a Docker Container Description: Running SQL Server as a local instance in a Docker Container. This is a very handy capability, and you can get it running in just a few minutes after you have Docker installed - much faster than installing SQL Server natively on your host machine. The video is quick with just the necessary steps. Full details of all commands and explanations are available here: https://douglaskline.blogspot.com/2020/09/running-sql-server-instance-in-docker.html </video> <video> ANY and ALL in SQL Server Description: Basic use of the ANY and ALL comparison modifiers in Structured Query Language. Comparison operators (equals, less than, etc.) which compare one single value with another single value can be modified with ANY or ALL to compare one single value with all the values in a single-column table. Get the code here: https://douglaskline.blogspot.com/2018/10/using-any-and-all-in-sql.html </video> <video> Run a SQL Server on your Macbook M1 2024 Description: Setting up SQL Azure Edge as a Docker container on my Macbook Pro M1. Great for SQL programming, data modeling, etc. Works without an internet connection. Lightweight instance runs in under 1GB memory. See my blog post for the command and a full discussion of what each option does. https://douglaskline.blogspot.com/2024/05/azure-sql-edge-instance-on-macbook-pro.html </video> <video> Understanding Relational Division in SQL Description: Intermediate level SQL on Relational Division using GROUP BY, COUNT, HAVING, and a correlated subquery or common table expression (CTE). A simple example, stripped-down example of Job Applicants with certain Skills, and Jobs that require certain skills is provided to teach the concept of relational division. Here is the script to create the example JobSkills database, with data. https://douglaskline.blogspot.com/2018/11/jobskills-database-schema-and-data-for.html Here is the code used in the example. https://douglaskline.blogspot.com/2018/11/understanding-relational-division-in-sql.html </video> <video> Time Zones and the DATETIMEOFFSET data type in SQL Description: Get the code here: https://douglaskline.blogspot.com/2018/10/time-zones-and-datetimeoffset-data-type.html </video> <video> Foreign Keys in Relational Databases Description: Beginner video on Foreign Keys in relational databases. Foreign keys allow for "lookups" between two tables. To ensure that these "lookups" are valid, we can enforce referential integrity, which makes sure that all foreign key values actually exist in the primary key table. Although referential integrity is specified as a constraint on the foreign key table, both tables involved check data changing operations to ensure all foreign key values are always valid. Primary Keys are always UNIQUE and NOT NULL, but Foreign Keys are generally not UNIQUE, and can be NULL. See the full code here: http://douglaskline.blogspot.com/2018/10/foreign-keys-in-relational-databases.html </video> <video> Finding UnMatched Records in SQL Description: Short video for finding unmatched records using the LEFT JOIN ... WHERE IS NULL pattern. Get the code here: https://douglaskline.blogspot.com/2018/10/finding-unmatched-records-in-sql.html </video> <video> Approach to Complex SQL Queries Description: How to approach more complicated SELECT statements in Structured Query Language. </video>

Contacts and Links

Description

Basic, intermediate, and advanced Structured Query Language (SQL) demonstrations using Microsoft's SQL Server.
SQL from the very beginning, plus best practices for coding and performance. Demos for beginners, programmers, and "accidental" database administrators.

Videos from channel