SQL For Data Analyst and Data Engineer

Explore data definition language by learning how to create, drop, and rename databases in SQL Server using both syntax and the SQL Server Management Studio interface.

Learn to transfer a securable between schemas in the same database using alter schema, and create a new employee schema to move dbo.event to employee.

Explore how a SQL Server database schema groups objects under an owner, defaults to dbo, and how to create or drop schemas in the current database.

Learn how to create a table in a database with a basic example, defining columns, data types, and constraints like primary key, not null, and identity for automatic id generation.

Learn to alter a table to add a new column named name with varchar(50), and explore changing column names, data types, or dropping columns.

Learn to modify a column's data type with alter table and alter column, changing varchar to char, and understand the differences between varchar and char.

Learn to create a computed column in SQL Server that derives full name from first name and last name. Understand persisted versus non-persisted behavior.

Drop an existing column that has no constraint. Drop the constraint on a constrained column using alter table, then drop the column.

See how truncate table removes all data from a table, equating to delete without a filter, with no where clause, and learn the upcoming difference between truncate and delete.

Learn how to drop a table with an optional if exists clause, and why foreign key constraints prevent drops until constraints are removed.

Create and use temporary tables in SQL Server to store immediate results during a session. Distinguish local temp tables (session-scoped) from global temp tables, with examples using an employee table.

Explore global temp tables and their difference from local temp tables, using two hashes to share data across multiple sessions, with ETL data pipelines for temporary data.

Master basic SQL syntax by writing queries with select, from, where, group by, having, and order by, and learn to limit rows and sum salaries by department.

Learn how SQL Server processes a query in this short video, following the order of execution: from, where, group by, having, select, order by, and limit or top.

Explore SQL server data types and how they define the data a column stores, focusing on the bit data type and its zero/one conversions for true, false, and non-zero inputs.

Explore the SQL server integer data types: big int, int, small int, and tiny int, and how range and storage influence performance.

Explore the decimal data type in SQL Server, its precision and scale, and how these define total digits and digits to the right of the decimal.

Explore the char, anchor, and varchar data types, their fixed versus variable lengths, how inserting fills trailing spaces and selecting trims them. Length limits include max and unicode storage.

Learn how date time, date, and time data types store date and time values in SQL, with practical table creation examples showing how choosing a type enforces value formats.

Explore the GUID data type in SQL Server, generated by newid(), a 16-byte globally unique primary key that aids cross-server data merging and URL safety, but increases storage.

Insert data into a table using insert into, including single and multiple row inserts, view the inserted values in the employee new table, and preview update, delete, and merge operations.

Learn how to insert data from one table into another using insert into select, apply where filters, and explore top n or top percentage options on the source data.

Insert the top n rows from one table into another using insert into and select from the source table, with top three and top percentage options.

Explore how update statements work in SQL server, applying changes with and without conditions, using where clauses to target rows for state and city updates.

Insert the top 10% of rows from the employee table into a new table using top percentage logic, illustrating row counts and the effect on the destination table.

Update rows from another table using a join to set the employee's department head from the department table based on matching department names.

Learn to delete rows in SQL using multiple methods, from deleting all rows to conditional deletes and top or top percentage deletions, with a brief note on truncate in DDL.

Learn how to delete the top N rows from a table using the top statement in SQL, demonstrated with a 31-row table reduced to 29 after removing two.

Learn how to delete the top n percent of rows in a table, using percent syntax for percentage-based deletion on an employee table, with a 50% example.

Explore how an identity column in SQL Server auto assigns values with a seed of 1 and an increment of 2, so inserts don't require explicit id values.

Learn how truncate table deletes all data from a table, with no filter conditions, and how it compares to the delete statement, with the difference explained in a later video.

Compare truncate table and delete statements, focusing on log efficiency and speed. Truncate deallocates data and resets identity to seed; delete logs each row and preserves identity.

Learn how a primary key on a single column uniquely identifies each row in SQL Server, using an identity constraint to auto-generate unique values and prevent duplicates.

Learn how to create a composite primary key on multiple columns, ensuring not null and uniqueness across sale id and customer id to prevent duplicates.

Learn how to add a primary key to an existing table by ensuring columns are not null, including scenarios for single and multi-column primary keys.

Learn how to create a foreign key linking the employee table to the department table using department id, the department's primary key, enforcing referential integrity and preventing invalid department references.

Learn how foreign key constraints enforce referential integrity by allowing inserts into the child table only when a matching parent exists, illustrated with a customer and sales example.

Explore how on update actions affect child tables in sql server, including no action, cascade, set null, and set default behavior for foreign key relationships.

Understand on delete actions for foreign keys: cascade, set null, set default, and no action, including required nullability and default value considerations.

Learn how to enforce uniqueness across multiple columns by creating a composite unique key on first name and email, ensuring duplicates are prevented when both fields match.

Learn how to add a unique key constraint on an existing table using alter table, and enforce uniqueness on the email column with a duplicate entry test.

Explore how check constraints in SQL Server enforce data validity by restricting column values, such as unit price > 0 or age > 18, with and without constraint names.

Learn how to drop a check constraint in SQL Server by creating a table with a unit price greater than zero, then dropping the constraint to allow zero values.

Learn how to limit rows in SQL using offset and fetch, skip rows with offset, and fetch the first rows, with notes on using top as an alternative.

Apply the top clause to limit rows, using order by unit price descending and viewing top 100 or 10%. Note how ties pull in extra rows when duplicates share values.

Learn how to obtain distinct values in sql server using select distinct and group by to remove duplicates across single or multiple columns such as department, salary, and name.

Explore the where clause to filter rows using equal and not equal operators, showing how exact value matches return only sale and how not equal excludes other departments.

Learn to use >, <, >=, <= with salary data, including 50,000, and note the next video on is null is not null.

master the not like operator in sql by filtering rows with the where clause to exclude names that start with, end with, or contain a given pattern using percent wildcards.

Explore how the where clause uses the like and not like operators with percentage wildcards to match strings, including start with, end with, and contain patterns.

explains how to apply multiple conditions in the SQL where clause using and and or operators to filter by department, salary, and name.

Master using is null and is not null to identify and filter null values, with examples of inserting nulls and querying where department is null or not null.

Explore the where clause using in for multiple values and the exists and not exists operators, with examples and their use in subqueries and data analysis tasks.