Database & Query - Let's Talk SQL (SQL Server, SQLite etc.)

Explore database tools across SQL Server, MySQL, MariaDB, PostgreSQL, and SQLite, including SQL Management Studio, Azure Data Studio, and VS Code extensions, with express setup for this course.

Explore database structure and objects, including schema objects, tables, views, and indexes, and master SQL for storing, querying, and managing data across databases.

Install SQL Server Express with mixed mode, install SSMS, connect to your instance, create a sample database via a query, and restore Adventure Work to explore tables, views, and synonyms.

Learn how to restore the AdventureWorks database from a backup, inspect file lists, and move data and log files to a local path with admin rights.

Explain how sql server tables store data in rows and columns, with primary keys and constraints, and how to create and alter tables with data types like integer and varchar.

Explore predefined constraints that enforce rules on single or multiple columns to maintain data integrity, accuracy, and reliability, including not null, primary key, unique, composite primary keys, default, and check.

Learn how to insert data into a customers table using insert into with specified fields and values, handle varchar, numbers, and binary, and practice inserting multiple records and basic selects.

Learn to retrieve data with select queries, filter with where clauses, and modify or remove records using update and delete commands, with practical examples.

Discover how database views function as virtual tables defined by a query, exposing a subset of data from one or more tables, simplifying complex queries, and improving security and performance.

Explore SQL comparison operators to filter data with select, update, delete, and where clauses, using greater than, less than, equal, not equal, and between.

explore core sql statements, including select distinct, top, where, order by, between, in, and like, with practical demos on the human resources.employee table to build flexible queries.

Master SQL aggregate functions such as count, max, min, and avg, and use group by and having to summarize data by fields.

Master inner joins, left joins, right joins, and full outer joins to combine related rows from two tables, and use unions to merge datasets with a shared schema.

Explore sql server built-in functions for date, string, and numeric handling, including getdate, isnumeric, len, isnull, newid, convert, cast, left, right, ltrim, trim, and abs, with practical demos.

Introduce the index as an on disk structure tied to a table or view that speeds retrieval and reduces query time, with primary keys automatically creating indexes and additional indexes.

Create and optimize non-clustered and clustered indexes, handle unique constraints and multi-column combos, follow where-clause order for performance, and monitor fragmentation.

Improve query performance on large tables by understanding index fragmentation and using rebuild or reorganize to reorder and compact index pages, with options for a single index or all indexes.

Learn how stored procedures encapsulate complex SQL logic, accept parameters, and return multiple result sets, enabling efficient, secure data access within the database environment.

Create and modify stored procedures, execute with or without parameters, returning all customers and those with id over 1000; also fetch the latest date's customers and a sales total.

Explore scalar functions in SQL Server, learn how they take inputs, return a single value, and how to create, call, and reuse them inside queries and procedures.

Compare inline and multi-statement table valued functions in SQL Server, detailing syntax, returns table, and whether the schema is predefined; learn how to return and query the resulting table.

Master SQL Server error handling with try catch, inspect error information, log to a log table, and use transactions to ensure data integrity and rollback on errors.

Learn how SQL transactions ensure data integrity by wrapping multiple modifications in begin transaction and commit, using try-catch for rollback on errors.

Explore dynamic query, a technique that builds SQL statements as strings and executes them to generate flexible queries for complex reporting based on optional input parameters.

Learn how to implement stored procedures as reusable inner routines to log errors, create a centralized error log table, and call the logging procedure from other procedures.