Publisher's Synopsis
I wrote this course to supply the background you need to be a T-SQL querying expert. The text uses SQL 2017 but is perfectly valid for SQL 2008R2 to SQL 2019.It covers: How SQL came about and what data management issues it solves. What primary key and foreign keys are. What an object in SQL is and what naming conventions/rules refer to them.How to write SQL scripts using variables, WHILE, IF, BEGIN...ENDHow to use SQL Server Management Studio Solutions to organise your .SQL files.How to use SELECT statement and know it subtleties including the WHERE and ORDER BY clause.How to use column and table aliases.How to reformat and do arithmetic with dates.How to use type conversions to format data so that it is suitable for printing or displaying.How joins are and how they work to link tables together.How to do inner joins, outer joins, self-joins, cross joins and full joins.How to group data using the GROUP BY and HAVING clauses.How to use CTE's (Common Table Expressions) to break a complex query into manageable pieces.How to use data partitioning/windowing functions (OVER, PARTITION BY) to display totals and data rows simultaneously.How to create tables including creating copies of the data in existing tables.How to specify and use primary key to foreign key relationships to protect the integrity and consistency of your data.How to use the default, check, NULL and computed columns to aid in data correctness.How the many variations of how to insert, delete and update data. How to copy data from one table to another using SQL commands.How database transactions work.What an implicit transaction is and how it will affect your data modification queries.How to create explicit transactions to protect a user from data changes using BEGIN TRAN, COMMIT TRAN and ROLLBACK TRAN.How locks affect your and other user's queries.How to override row locks for reporting purposes.How to write scripts that elegantly deal with errors using BEGIN TRY...END TRY and related statements.BONUS CONTENT: The basics of writing Stored procedures, quickly and easily.Each chapter has exercises for you to try based on the Microsoft Adventure Works sample database. The answers to all the exercises are in the last appendix. I've included an appendix that will show you how to install MS SQL and the Adventure Works database.Everything you need to pass the official Microsoft 70-761 or 70-461 querying is covered here, but I would read the relevant exam guide to almost guarantee your passing.
