Querying Microsoft SQL Server with T-SQL | Basic to Pro

Define what a database is and how a dbms stores and retrieves data. Explain system databases like master, model, msdb, and tempdb, and the mdf and ldf files.

Learn to create and delete databases in SQL Server using SSMS or T-SQL, including choosing default paths for MDF and LDF files.

Create tables in SQL Server with SSMS or queries, define columns and data types, and apply nullability using a product example (id, name, barcode, price, created at, updated at).

Learn to create and drop tables in SQL Server using T-SQL, including using databases, writing queries, and generating creation scripts with SQL Server Management Studio.

Insert data into a table and retrieve it with select queries in SQL Server Management Studio, using a test database and understanding column order, data types, and syntax.

Learn to update records in a table using SSMS or SQL queries. Use the where clause to target specific rows and update salaries and departments.

Learn how to delete records in SQL Server tables with both the SSMS UI and a T-SQL delete query, using where clauses to target rows.

Discover primary keys, foreign keys, and unique keys in SQL Server; primary keys cannot be null and one per table; foreign keys link tables; unique keys may be null.

Learn to set a primary key in a table using design options and T-SQL, prevent duplicate IDs, and understand the PK constraint naming (PK employees) and top-table best practices.

Learn to configure identity columns in SQL Server by enabling identity on a primary key, adjusting identity seed and increment to auto generate sequential IDs.

Learn to enforce uniqueness with a unique key, convert emails to a unique constraint, and create composite unique keys on first and last name using SSMS or SQL.

Explore setting default values in SQL Server tables, including string and numeric defaults, and getdate for current dates, with examples like date of birth, salary, and department.

Implement check constraints in SQL Server with T-SQL to enforce age between 18 and 60 and to block specific names, using inline and named constraints.

Learn to create computed columns in SQL Server with T-SQL, where expressions generate full name from first name and last name, and annual salary from salary multiplied by 12.

Import a predefined Adventureworks database by restoring an .bak backup in SSMS, then explore predefined tables with sample data for customers and products, noting primary keys, identity, and data types.

Explore SQL Server schemas as logical containers that organize product, customer, and order tables, learn schema ownership, creating schemas, and using schema-qualified queries and comments in SSMS.

Explore truncate to quickly delete all rows and reset identity, use distinct to return unique values, apply aliases with as for readable results, and replace nulls with isnull.

Discover how coalesce returns the first non-null value, how nullif handles equal expressions, and how cast converts data types for concatenation and aliases in SQL Server.

Explore the convert function for data type and date/time conversions, compare it with cast, and learn case statements, including simple and searched, for conditional logic and ordering.

Learn the SQL Server string aggregate function that concatenates values with a separator to form comma separated lists, plus for xml path outputs and the substring function for extraction.

Learn to use sql server t-sql string functions replace, stuff, and translate to modify strings, including replacing substrings, deleting and inserting characters, and distinguishing replace from translate.

Prefix strings with N to indicate Unicode in SQL Server. Handle Arabic or Persian text with the replace function and avoid question marks in databases that support Unicode.

Master core date and time functions in SQL Server, including getdate, dateadd, datediff, datepart, and datename, to obtain current timestamps, adjust dates, and compute date differences and parts.

Learn to use dateadd, datediff, and datepart with time values, and compare get date and date time for the current timestamps. Convert dates with style codes like 101 and 102.

Master database design and normalization in SQL Server, covering ERD, entities, attributes, relationships, and foreign keys. Normalize tables to eliminate redundancy and boost data integrity and efficiency.

Explore a one-to-one relationship between categories and products by linking the category_id in products to categories' primary key, and configure cascade, set null, or set default rules.

This lecture demonstrates cascade and set null delete rules on category and product tables and how referential constraints control deletions.

Explain the one-to-many relationship by showing how a single customer can place many orders, linked by customer ID as a foreign key from orders to customers.

Demonstrate how to model a many-to-many relationship in SQL Server by creating a junction table with a composite primary key of meeting ID and employee ID, linking meetings and employees.

Join customers and orders to retrieve customer names with their orders using inner joins and left joins. Use aliases to simplify queries and maintain data consistency with shared customer_id.

Explore right joins and full joins in Microsoft SQL Server, learning that right join returns all rows from the right table and full join returns all rows from both tables.

Learn to perform self joins by linking a table to itself to reveal relationships, and use cross joins to generate a Cartesian product while cautioning about large data sets.

Master advanced joins across multiple tables by linking customers, orders, and products with inner joins, returning customer details, order ids, product names, colors, quantities, and amounts.

Explore essential sql server string functions, including concat to join fields and substring to extract parts. Use upper, lower, trim, ltrim, and rtrim, and charindex to locate substrings.

Explore arithmetic functions in T-SQL, including abs for absolute values, ceiling to round up to the nearest integer, floor to round down, and round to a number of decimal places.

Explore aggregate functions in SQL Server, including sum, average, min, max, and count, demonstrated on an orders table to compute totals, averages, and per-customer non-null counts.

Explore sorting with order by on one or more columns in ascending or descending order. Apply joins and the IN operator to filter by multiple values.

Learn subqueries and correlated subqueries in T-SQL, using in-clause checks against orders and mastering relational operators with examples from customers and orders.

Master logical operators such as and, or, not, and in to combine conditions in where clauses, and use the between operator for inclusive ranges in orders and customers from USA.

Learn to use the like operator for pattern matching with % wildcards and to group data with group by for aggregates like count, sum, average, min, and max.

Learn how the having clause filters grouped data after a group by in T-SQL, with examples using country counts and average order amounts.

Define a common table expression as a temporary named result set to improve readability. Use the CTE to select customers from USA and Canada and compute average order amount.

Learn how to use SQL Server pivot and unpivot to transform rows into columns and back, summarizing sales by agent and country with aggregates.

Learn how to use union and union all in T-SQL to combine results from multiple selects, with or without duplicates, and how to align column counts and types.

Explore the intersect and except operators in T-SQL: intersect returns rows in all selects, while except excludes rows from the first select, illustrated with product and customer examples.

Use the offset and fetch next clauses in SQL Server to implement pagination by skipping a set of rows and retrieving the next subset, with order by required.