SQL Server Certification: Querying Data with Transact-SQL

Explore the fundamentals of databases as related data that is easily accessed, managed, and updated, and see how Transact-SQL powers querying and problem solving.

See how computer-based databases store data safely and efficiently, accelerate searches with sorting and indexing, and relate these ideas to Transact-SQL's select, insert, update, and delete.

Discover how databases use OLTP for fast inserts and updates and OLAP, or data warehouse, for efficient reads; learn normalization and moving data from OLTP to OLAP for reports.

Explore essential database terminology for Transact-SQL and SQL Server, including CRUD, transactions, fields, records, ACID properties, result sets, normalization, commit and rollback, and SQL Server Management Studio.

Distinguish transact-sql statements as two categories: data definition language for creating and altering database objects, and data manipulation language for querying and modifying data with select, insert, update, and delete.

Explore the SQL Server 2016 editions, including Enterprise, Standard, Web, Developer, Express, and Evaluation, and understand licensing, free editions, and use for development and testing.

Learn to download and install SQL Server 2016 using express, evaluation, or developer editions, including obtaining a small setup executable, the ISO download, and Dev Essentials sign-in.

Learn to install SQL Server 2016 step by step, from mounting the ISO and running setup to selecting features, configuring the instance, and completing the database engine installation.

Install and manage sql server management studio for sql server 2016 as a separate installation, then download ssms from msdn, run setup, and connect to your server.

Attach the Adventureworks 2012 sample database to SQL Server 2016 using SQL Server Management Studio, copying the MDF to the SQL data folder, then attach and start writing Transact-SQL queries.

Learn to write basic select statements to retrieve data from tables. Explore the six parts: select, from, where, group by, having, order by, and how they shape results.

Discover how the where clause filters rows in a select statement, boosting performance by returning only needed records, with practical examples using last names and comments.

Master column aliasing in Transact-SQL to rename query results without changing underlying tables, using the as keyword optionally, and delimited multiword aliases for clear reporting.

Use the distinct keyword to eliminate duplicate rows in a result set and return only unique values in a query, demonstrated with product ID in the sales order detail table.

Learn how to control query result order in Transact-SQL using the order by clause, with ascending and descending options, and multi-column sorting in SQL Server.

Master T-SQL concatenation to turn two columns into a readable unit, using the plus operator or the concat function, with conversions for numeric values in result sets.

Learn how multi-part naming in Transact-SQL uniquely identifies objects by using server, database, schema, and object names, and when two-part naming suffices versus four-part naming.

Master pattern matching in Transact-SQL by using like and in within the where clause, with wildcards %, _, square brackets, and ranges to filter data and concatenate names.

Combine data from multiple queries into a single, alphabetically ordered result set using the union operator in Transact-SQL, illustrated with employees and customers tables.

Explore intersect and except in Transact-SQL to compare two tables and return distinct rows found in both, and rows not found in the other, using left and right queries.

Learn how to paginate large data sets in Transact-SQL using offset and fetch to return small, navigable subsets of a result set for user interfaces, with an order by requirement.

Master Transact-SQL case expressions, including simple and searched forms, to classify data per row and create readable, end-user friendly results in select, update, and more.

Explore advanced case expressions in Transact-SQL by converting simple cases to searched cases, using days to manufacture to determine immediate or six and four business days.

Learn how to pivot data in Transact-SQL by turning rows into columns using the pivot operator, including a derived table, group by, and a practical code example.

Learn how to implement pivots in Transact-SQL by building derived tables, using sum with case statements, and using the pivot keyword to rotate data across columns.

Explore database structure and its impact on Transact-SQL programming, including physical design, tables, and relationships. Learn how centralized data storage presents access challenges for concurrent users.

Understand why a single wide table causes locking and slow responses with concurrent updates, and how normalization splits data into related tables for efficient transact-sql querying.

Explore how table relationships and normalization in Transact-SQL improve data integrity and efficiency by splitting data into related tables, using primary and foreign keys, and performing joins.

Learn to query data from multiple tables using join clauses, based on primary and foreign keys, through examples like joining the person and email address tables on business entity ID.

Learn how table aliasing streamlines Transact-SQL queries by using short aliases like p and m to replace full table names, improving readability and reducing keystrokes in joins.

Explore the main join types in Transact-SQL, including inner, left, right, full outer joins, and cross joins, and see how matches and nulls affect result sets.

Explore practical Transact-SQL join types by coding inner, left, right, and cross joins on product and product review tables, comparing results and edge cases.

Explore built-in sql server functions in transact-sql, including sum, average, count, min, and max, and apply them to the sales.order_detail line_total for insights.

Master data aggregation with group by in Transact-SQL to produce totals and subtotals by product, category, or region using built-in functions such as sum and average.

Explore how the having clause filters groups created by group by using sums of line totals, returning only groups whose total exceeds 25,000.

Explore grouping sets in Transact-SQL to replace cube and rollup, gaining flexible, readable grouping and the ability to produce subtotals and a grand total across multiple groupings.

Explore the graphical query tool in SQL Server Management Studio to design, build, and edit inner and left outer joins across tables, saving time and sanity.

Master the Transact-SQL insert statement to add one or more rows using insert into table with optional column lists, handling defaults, generated values, data types, and constraints.

Demonstrate inserting data with the insert statement in SQL Server Management Studio, using an identity column, not null constraints, default values, and selective column lists.

Master insert-select to populate an existing table from a query. Use a select subquery, join sources, and ensure compatible column types through a practical example.

Create a new table using select into, inferring its columns from the select result in default file group, and note that indexes, constraints, and triggers from the source aren’t transferred.

Use the Transact-SQL update statement to modify existing data, understanding it can be processed as delete and insert behind the curtain, and always include a where clause to limit rows.

Learn to update data in SQL Server using transact SQL update statements, apply where clauses to target rows, and manage changes with transactions such as begin tran, rollback, and commit.

Delete data with Transact-SQL using delete from a table with a where clause to target rows; use truncate table for full clears and test with a select before deleting.

Demonstrate deleting data in Transact-SQL by building a test environment with a select into that creates a delete example table, then delete with and without a where clause.

Explore stored procedures in Transact-SQL to encapsulate functionality, enable secure, reusable code, reduce network traffic, and benefit from compiled execution plans for improved SQL server performance.

Learn to create a simple Transact-SQL stored procedure that returns last name, first name, and office phone by joining two tables, then run it and manage permissions.

Explore using input parameters with stored procedures in Transact-SQL to pass data, create flexible queries, and tailor results with where clauses and simple examples.

Master how to declare and use output parameters in stored procedures with t-sql, using AdventureWorks 2012 examples to return row counts and national IDs.

Declare and use table variables in T-SQL to store a result set in memory, load via join, and query like a table, with performance limits beyond 100 rows.

Explore chaining two common table expressions to reproduce results, using C1 and C2 to derive order year, count distinct customers, and apply filters with where and order by.

Explore dynamic data masking, a SQL Server 2016 feature that protects sensitive data during queries by masking columns at the presentation layer, using default, email, random, and custom rules.

Demonstrate dynamic data masking in T-SQL by altering the email column of person.email_address to mask data, and test visibility with a separate user in SQL Server Management Studio.