Master Microsoft SQL Server 2022

Discover core concepts of SQL Server, a relational database management system that stores and retrieves data in tables, handles permissions, backups, and uses T-SQL with Management Studio.

Master Microsoft SQL Server 2022 outlines free editions (Express and Developer) and paid editions (Enterprise and Standard), with Express 16 cores/64 GB, Developer unrestricted, and Standard 24 cores/128 GB.

Install SQL Server 2022 on a desktop by downloading the developer edition (free) from SQL Server downloads and installing SQL Server and Management Studio with the basic option.

Open SQL Server configuration manager to verify the SQL Server 2022 service status, start or restart it, and review logon settings and automatic or manual start mode.

Learn how SQL Server manages user permissions and authentication, including Windows authentication, SQL Server authentication, remote login with username and password, and roles: system admin, database admin, and database user.

Learn how to log in to SQL Server 2022 using Management Studio, choose server type and instance, configure Windows authentication, explore Object Explorer, and adjust login options.

Enable mixed mode authentication and restart the SQL Server instance, then enable the SA login with a strong password to connect via SQL Server authentication to the master database.

Learn to create a database in SQL Server 2022 using sqlcmd, connect to a specific instance, and verify the new database in SQL Server Management Studio.

Learn to create a database in SQL Server Management Studio, configure general options, file locations, and growth settings, and understand collation, recovery model, and compatibility levels.

Learn to design a table by modeling columns with data types, handling optional nulls, and choosing meaningful, space-free column names in preparation for creating a table in management studio.

Learn to create tables in SQL Server 2022 with Management Studio, covering memory-optimized, temporal, ledger, graph, external, and file tables, and define columns with varchar, nvarchar, and date types.

Alter a table in SQL Server 2022 using SQL Server Management Studio by editing column definitions, saving changes after disabling recreation option, and use Transact-SQL for updates on data-bearing tables.

learn to insert data into a sql server table, ensure mandatory fields are filled, save changes, and verify results with top 1000 rows. import data from a csv file.

Import data from a csv into SQL Server using the Import and Export Wizard in Management Studio, mapping columns, adjusting data types, and loading into the students table.

Transfer large data between a source and destination using SQL bulk copy (bcp), exporting to CSV and loading with the bcp command.

Learn to create a new table from a flat file using the import flat file wizard, adjust column data types, and insert csv data into a new customers table.

Install a sample database for SQL Server by downloading Wideworldimporters or Adventureworks backups and restoring them in Management Studio to explore tables.

Select appropriate data types for table design to optimize storage and network efficiency while ensuring data integrity, and choose the smallest data type that fits while previewing popular data types.

Explore the core sql server data types, including numeric, character, and date time categories, their storage sizes, and typical use cases.

Enable the identity specification on a column in the table designer, set identity seed and increment to auto generate student identities, and verify by selecting top 1000 rows.

Set a primary key to uniquely identify each row, enforce not null, and prevent duplicates, using a single-column or composite-key approach.

Learn how to populate columns with default values, such as setting state to Ohio and using Getdate for created date, then save changes and verify inserts.

Learn how to enforce data validity in SQL Server using check constraints on a table, such as ensuring the state abbreviation does not exceed two characters by using length(state).

Learn how unique constraints enforce unique values across columns, using unique keys alongside primary keys, and create non-clustered unique indexes to prevent duplicates such as email addresses.

Explore how foreign keys link the sales orders table to the customers table by referencing the customers' primary key, enforcing referential integrity.

Create an orders table with an identity primary key (order_id) and foreign keys to student_id and course_id, enforcing matching data types and defaults for order_date and quantity.

Establish relationships between tables in SQL Server 2022 by configuring foreign keys in design view, linking students to orders and courses to orders, and validating referential integrity with FK constraints.

Explore what SQL is and how it powers relational databases, and learn the three categories: DDL, DCL, and DML, through Transact-SQL for Microsoft SQL Server.

Examine the types of SQL statements across DDL, DML, control language, and transaction control language, and learn to create, alter, drop, truncate, insert, update, delete, grant, revoke, and manage transactions.

Navigate the management studio interface to run Transact-SQL queries with the query editor and view results in grid, text, or file formats, while learning to save and comment queries.

Learn to use the create table statement in SQL Server Management Studio to define columns (identity, not null), set a primary key (clustered), and generate change scripts.

Learn to insert records into a SQL Server table using insert into, specify columns and values, handle autoincrement ids, and use getdate for date added.

Attach the AdventureWorks database, restore it, and open a query window to execute select statements that retrieve literals, built-in functions, and aliased columns from human resources.department.

Learn to filter records with the where clause in SQL Server 2022, using predicates, in and not in, and not equal to, with group and department examples.

Learn how to use the order by clause to sort SQL query results by single or multiple columns, with ascending or descending order.

Explore column aliases in sql select statements, rename fields with as or spaces, create on-the-fly computed columns like sale price, and display nonpresent names such as company name.

Delete records with the delete from statement and a where clause. Respect foreign key constraints and delete dependent rows by using a primary key in the where clause.

Update records in a SQL Server table by using the update statement with a where clause to target a specific row, set new values for middle name and email.

Learn to remove table contents with truncate table and delete from, and drop a table with if exists. Compare minimally logged truncate with fully logged delete, noting identity insert.

Master the top keyword to filter results in SQL Server 2022, using top with or without brackets, adding order by, with ties, and the percent option to control result size.

Use the distinct clause to remove duplicates and retrieve unique city and state province ID pairs; order results by state province ID and city for readability, yielding 613 distinct records.

Explore standard and non-standard SQL Server 2022 comparison operators, filter tax rates with where clauses, apply order by, and use range and not equal conditions to refine results.

Use offset fetch to implement pagination by retrieving ten records per page from the Production.Product table in AdventureWorks. Parameterize offset and page size to display pages as users navigate.

Learn how to filter out null values with is not null and is null in where clauses, and replace nulls with constants to ensure proper data types for apps.

Learn to filter text data in SQL Server using the like operator to match patterns, including starts with, ends with, contains case-insensitive patterns, character counts, bracket ranges, and not conditions.

Master the fundamentals of table joins in SQL Server, including inner, left outer, right outer, and full joins, plus cross and self joins, to combine data from multiple tables.

Learn inner join in SQL Server by linking the students and orders tables to retrieve student names with their order IDs and dates, using aliases S and O.

Explore inner join and left, right, and full outer joins using a student id orders table, and understand how matches determine results and when null values appear.

Learn how cross joins create a cartesian product by pairing every row from one table with every row from another, enabling test data generation.

Discover self-join, or equijoin, by joining the sales.customers table to itself with aliases, using an on clause to compare records within the same table and reveal sales representatives.

Group by in SQL Server groups rows with the same value in a column, then perform counts, sums, and averages to analyze and summarize data.

Group by and count tally addresses per city and state province in the Adventureworks 2022 database, using count and order by to reveal the top city-state combinations.

Learn aggregate functions in SQL Server 2022, including count all, count distinct, sum, average, min, max, stddev, var and population variance, plus approximate count distinct and approximate percentile functions.

Learn to use the sum aggregate function to compute the total line total and total order quantity per sales order ID, count distinct products, and sort by order total descending.

Apply the having clause with group by to filter color groups by aggregated product counts, such as counts greater than 25.

Explore built-in SQL Server functions across aggregate, string, mathematical, date and time, and logical categories. Learn which functions take zero or multiple arguments, with getdate as an example.

Master string functions in SQL Server 2022 by manipulating data with length, upper, lower, left, right, and trim on columns like first name and last name.

Learn how to concatenate first, middle, and last names using the plus operator with separators, and compare the concat and concat with separator functions, including null handling.

Explore mathematical functions in sql server 2022, focusing on round, ceiling, and floor using unit price to demonstrate precision with two decimal points and negative precision.

Learn how the greatest and least functions return the maximum or minimum among multiple expressions, then apply them to compare vacation and sick leave hours in SQL.

Explore commonly used date functions in SQL Server 2022, such as get date, year, month, and day, plus date diff and date add, using the AdventureWorks 2022 employees data.

Explore the format function to render dates using four d's for full day names and four m's for full month names, with options for two-digit years.

Explore the new date bucket function in SQL Server 2022 that groups data into fixed time intervals using bucket width and origin, with weekly and bi-weekly examples.

learn to shuffle a result set with the newid function by ordering by newid, producing a random order and enabling top ten or top twenty, useful for uniqueidentifier keys.

Learn how to use the generate series function in SQL Server 2022, set compatibility level to 160, and generate numeric series with start, end, and increment.

Explore the iif function by evaluating the sales year-to-date against 1 million, returning true or false as sales status, then group by true or false to count those meeting target.

Learn how to use case statements in SQL Server 2022 to implement conditional logic, categorize job titles into managers, staff, and others, and alias the result as employee category.