SQL - Beyond The Basics

Use the convert function to change data types for concatenation. Format a birthday this year by converting year, month, and day to strings to form a date.

Create a temporary table by prefixing the name with a hash symbol that exists only for the current session, then insert from table data, and query, update, and manage it.

Indexing speeds data retrieval by ordering data with a primary key cluster and secondary indexes on columns like date of birth and contact id, including multi-column indexes.

Declare and use variables in SQL by defining named variables with declare, assigning values with set, and applying them in select statements across data types.

Explore subqueries by embedding a select within another select, including correlated subqueries tied to the outer query. Learn to use subqueries in where clauses to relate contracts, contacts, and regions.

Explore when to use subqueries versus joins to retrieve data, including how grouping affects results and when a subquery beats the join for data with contracts, contacts, and payments.

Learn how correlated subqueries link to the outer query to compute per-contract payments and client-level subtotals, using joins, aliases, and targeted where clauses to produce accurate tallies and annual totals.

Explore how the exists keyword uses a subquery to determine whether to return outer query results, with examples of correlated and non-correlated usage, including in and join patterns.

Identify and manage duplicate records by concatenating first name and surname, grouping to count duplicates, then retrieve full records and find the first occurrence with a correlated subquery for cleanup.

Learn how to use the case statement to map column values to readable labels with when-then clauses, else, and end, creating new columns for gender, region, and birth month.

Explore using the case statement with criteria on multiple columns to output tailored results, including gender, country, region, and email domain, through when, then, else, and end case constructs.

Explore using a case statement with custom criteria in the when clause, including between ranges and correlated subqueries to classify contracts by year and client size.

Learn to use a case statement beyond select, applying it in where, group by, and order by to label contract years and sum values while counting contracts.

Apply case statements in update and insert operations to adjust contract years, build temporary tables, and drive data via select and group by clauses.

Combine two case-based selects with subqueries to produce contract year, contract value, and weeks, and insert into the hash deals temporary table.

Explore numbering and ranking records in a select using the row_number and rank functions, controlled by over with order by, and learn how ties affect ranking versus unique row numbers.

Filter ranked SQL results by numbering rows with row_number or rank over clauses to select top or bottom entries using subqueries or with clauses, and handle ties.

Learn to group results and apply the row number function and rank to data using group by and partition by, ranking contracts by total sales and contract value by year.

Learn to rank records within regions and extract the top N performing clients by joining contacts and contracts, using partition by region and top-n filtering.

Explore built-in SQL functions, including deterministic and non-deterministic types, with pi and get date, and apply left, uppercase, and month in select statements.

Create and use a user defined function (UDF) to compute age from a date of birth, with input parameters and an integer return, reusable in queries.

A view is a stored predefined select statement created with create view that can be joined, filtered, grouped, or ranked, and queried like a table to reuse a single query.

Learn how to convert ranking SQL statements into views, test them, and join views with tables to retrieve the top contracts for each year and the top 10 clients.

Use the union clause to append two selects with the same column count into one view. Compare yearly top contracts and contact details across years.

learn how to create and use stored procedures, including output parameters, return values, and recordset, with hands-on examples showing mechanics, naming, and execution.

Create a stored procedure with create proc, define input outputs and defaults, enable no count, and query TV contracts joined to TV contacts by start date, end date, city.

Learn how to run a stored procedure using output parameters, return values, and recordsets, including declaring variables, passing inputs, and printing results.

Learn how to call one stored procedure from another, reuse code, and use input parameters and existence checks to output a town-based recordset.

Discover how global variables @@ROWCOUNT and @@IDENTITY reveal affected rows and the last identity, and use them in stored procedures to link multi-table inserts.

Learn to implement if and while loops with begin and end blocks, including single-line if and break logic, using a counter x to iterate contacts and check existence until 20.

Dynamically build sql statements by assembling the select, columns, from, and where clauses into a string, then execute with exec; or use sp_executesql to run with parameters without quoting text.

Learn to trap errors in SQL using begin try and end catch, catch blocks, and built-in error information to gracefully handle issues in stored procedures.

Explore cursors in use in SQL: declare and open a cursor, fetch records, loop through data, update or insert, then close and deallocate, with temporary tables and age calculations.

Leverage a cursor with a subquery to compute total contract value per client and populate a temp table with client id, sales value, and age when positive.

Explore how transactions ensure data integrity in SQL by using implicit and explicit transactions, begin tran, commit, rollback, row count checks, error handling, and stored procedures.

Wrap up the sql beyond the basics course by recapping useful skills like queries, case statements, ranking, views, stored procedures, and transactions to speed data retrieval and reporting.