Foundations of MS SQL Server: A Beginner's Guide

Compare SQL Server editions and administration tools to choose the right option for your environment, then plan the installation by selecting components, OS, root directory, instances, and authentication modes.

Plan and install SQL Server on Windows by following hardware and software requirements, file system considerations (NTFS or ReFS), memory and disk space needs, and security and system configuration checks.

Install SQL Server 2019 developer edition, choose features, and configure authentication options; then connect to the server using SQL Server Management Studio with Windows or SQL Server authentication.

Explore front end tools for SQL Server, including SSMS and Azure Data Studio. Learn to connect to servers, use Object Explorer, registered servers, and Query Editor.

Explore SQL Server tools, including SSMS for registering and connecting to servers, using the Query Editor and Solution Explorer, plus Azure Data Studio as cross-platform options.

Explore the core SQL components in Transact-SQL, from literal values and identifiers to delimiters, comments, and reserved keywords, and understand numeric and character data types used in SQL Server.

Explore temporal data types such as date time, small date time, date, time, date time offset, plus miscellaneous binary, bit, and large object types, xml, geography, geometry, and hierarchy.

Explore SQL Server components, including data types and user defined types. Learn Transact-SQL functions, focusing on aggregate functions, such as avg, max, min, sum, and count, plus scalar functions.

Explore core sql string functions, including ascii, char, charindex, concat_ws, left, len, lower, ltrim, replace, stuff, reverse, soundex, patindex, space, and str, with practical examples.

Explore SQL components through functions and operators, including substring, trim, Unicode, cast and convert, plus current time, current user, null handling, and meta data and scalar operators.

Connect to SQL Server Management Studio, create a database and tables with Transact-SQL, and use the Object Explorer to manage databases in SQL Server 2019, covering data definition language basics.

Learn to create a sql server database from ddl statements, defining mdf and ldf files. Create abc_employee with int and varchar columns and not null constraints, plus a nullable department_id.

Learn to create tables for departments, employees, projects, and operations, rename objects using the sc_rename stored procedure, and apply primary key, foreign key, and various constraints.

Create tables using constraints by applying primary keys and foreign keys on department and employee tables, then add an operations schema with a composite primary key on employee_id and operation_id.

Define tables with constraints, enforce date order with a check constraint on order and ship dates, create a postal code user defined type, and insert department and employee records.

Learn to create and query relational data with insert and select statements, apply where clauses, and use and, or, in, not in, and between to filter records across multiple tables.

Explore advanced SQL queries on three tables (employees, emp_bonus, emp_titles), including aliasing, upper, distinct, substring, length, replace, and concatenation with rtrim and ltrim.

Master advanced sql queries by sorting results with order by across one or two columns, filtering with where, like, in, not in, and between, and querying date parts.

Count employees by department using group by and order by, identify managers via inner joins with emp_title data, and clone tables while applying intersect and except.

Master advanced SQL queries by retrieving current date and time, top salaries, and the fifth highest salary, then applying distinct, aliases, unions, and group by having.