-
Module 1: Introduction to querying SQL Server
12 Lessons-
StartModule introduction
-
StartWhat is SQL Server?
-
StartWhat is T-SQL?
-
StartTypes of T-SQL statements
-
StartWhat are Server Instances?
-
StartConnecting to SQL Server Logins
-
StartWhat are databases?
-
StartConnecting to SQL Server users
-
StartUsing SQL Server Management Studio
-
StartFinding help and examples for T-SQL syntax
-
StartLab 1: Introduction to querying SQL Server
-
StartQuiz 1: Introduction to querying SQL Server
-
-
Module 2: Querying a single table
17 Lessons-
StartModule introduction
-
StartWhat are tables?
-
StartFinding out what's in a table
-
StartWhat are schemas?
-
StartIntroduction to PopkornKraze
-
StartSELECT statements
-
StartUsing statement terminators
-
StartQuoting (delimiting) names
-
StartUsing 2 part names
-
StartUsing aliases for columns and tables
-
StartORDER BY clause
-
StartRestricting output with OFFSET and FETCH
-
StartRemoving duplicates with DISTINCT
-
StartQuerying literals, expressions and functions
-
StartLab 2: Querying a single table
-
StartLab 2: Answers
-
StartQuiz 2: Querying a single table
-
-
Module 3: Filtering the rows to return
13 Lessons-
StartModule introduction
-
StartFiltering output with the WHERE clause
-
StartFinding patterns with the LIKE operator
-
StartLogical operations with AND, OR, and NOT
-
StartRanges of values with the BETWEEN operator
-
StartChecking lists of values by using the IN operator
-
StartLimiting output rows by using the TOP operator
-
StartUsing TOP WITH TIES
-
StartWorking with NULL values (the lack of data)
-
StartWhat are user-defined functions?
-
StartLab 3: Filtering the rows to return
-
StartLab 3: Answers
-
StartQuiz 3: Filtering the rows to return
-
-
Module 4: Working with SQL Server queries
7 Lessons -
Module 6: Querying strings
17 Lessons-
StartModule introduction
-
StartString data types
-
StartLiteral character values
-
StartComparing strings with collations
-
StartCharacter operators
-
StartTrimming strings with LTRIM, RTRIM, and TRIM
-
StartChanging case with UPPER, LOWER
-
StartExtracting parts of strings with LEFT, RIGHT, SUBSTRING
-
StartDetermining length with LEN, DATALENGTH
-
StartReplace substrings using REPLACE and STUFF
-
StartDuplicating strings with REPLICATE
-
StartFinding substrings with CHARINDEX and PATINDEX
-
StartSplitting strings with STRING_SPLIT
-
StartReplacing characters with TRANSLATE
-
StartLab 6: Querying strings
-
StartLab 6: Answers
-
StartQuiz 6: Querying strings
-
-
Module 7: Querying dates and times
17 Lessons-
StartModule introduction
-
StartDate and time data types
-
StartLiteral date and time values
-
StartCurrent date and time with SYSDATETIME, GETDATE, SYSUTCDATETIME
-
StartExtracting date components with YEAR, MONTH, DAY
-
StartAdding and subtracting periods using DATEADD
-
StartSubtracting dates using DATEDIFF
-
StartExtracting other date components with DATEPART
-
StartGet string names of date parts with DATENAME
-
StartCalculating end of month with EOMONTH
-
StartConstructing dates from components with DATEFROMPARTS
-
StartConstructing datetimeoffset values with TODATETIMEOFFSET
-
StartChanging datetimeoffset values to different offsets with SWITCHOFFSET
-
StartCustom formats of dates and times with FORMAT
-
StartLab 7: Querying dates and times
-
StartLab 7: Answers
-
StartQuiz 7: Querying dates and times
-
-
Module 8: Converting between data types
11 Lessons-
StartModule introduction
-
StartReplacing NULL values with ISNULL, COALESCE
-
StartConverting data types with CAST
-
StartConverting data types with CONVERT
-
StartConverting data types with PARSE
-
StartChecking if a string is a number or date with ISNUMERIC, ISDATE
-
StartTesting conversions with TRY_CAST, TRY_CONVERT, TRY_PARSE
-
StartOther data types
-
StartLab 8: Converting between data types
-
StartLab 8: Answers
-
StartQuiz 8: Converting between data types
-
-
Module 9: Aggregating data
11 Lessons-
StartModule introduction
-
StartCounting rows and columns with COUNT
-
StartSummarizing data with SUM, AVG, MIN, MAX
-
StartSummarizing data in sections with GROUP BY
-
StartFiltering returned groups with HAVING
-
StartUnderstanding logical query execution order
-
StartNumbering rows with ROW_NUMBER, RANK, DENSE_RANK, NTILE, and OVER
-
StartPartitioning ranking and numbering with PARTITION BY
-
StartLab 9: Aggregating data
-
StartLab 9: Answers
-
StartQuiz 9: Aggregating data
-
-
Module 10: Implementing logic in scripts
11 Lessons-
StartModule introduction
-
StartOutputting messages with PRINT
-
StartUsing conditional expressions with CASE, IIF
-
StartDefining variables and assigning values to them using DECLARE, SET
-
StartQuerying system variables and functions
-
StartAdding conditional logic with IF, ELSE
-
StartCreating composite statements with BEGIN, END
-
StartCreating loops with WHILE
-
StartLab 10: Implementing logic in scripts
-
StartLab 10: Answers
-
StartQuiz 10: Implementing logic in scripts
-
-
Module 11: Querying multiple tables
14 Lessons-
StartModule introduction
-
StartUsing CROSS JOIN
-
StartWhat is a primary key?
-
StartWhat is a foreign key?
-
StartUsing INNER JOIN
-
StartUsing modern join syntax
-
StartUsing LEFT OUTER JOIN
-
StartOther outer joins with RIGHT OUTER JOIN, FULL OUTER JOIN
-
StartJoining more than two tables
-
StartJoining a table to itself (self joins)
-
StartJoins without equality (non-equi joins)
-
StartLab 11: Querying multiple tables
-
StartLab 11: Answers
-
StartQuiz 11: Querying multiple tables
-
-
Module 12: Applying set operations to tables
9 Lessons-
StartModule introduction
-
StartUsing UNION and UNION ALL
-
StartExcluding data with EXCEPT
-
StartFinding common data with INTERSECT
-
StartSelecting from table-valued functions
-
StartRepetitively selecting with CROSS APPLY, OUTER APPLY
-
StartLab 12: Applying set operations to tables
-
StartLab 12: Answers
-
StartQuiz 12: Applying set operations to tables
-
-
Module 13: Using subqueries
12 Lessons-
StartModule introduction
-
StartUsing subqueries
-
StartCreating scalar subqueries
-
StartCreating lists from subqueries
-
StartChecking for existence with EXISTS subqueries
-
StartCreating table expressions from subqueries
-
StartUsing VALUES row constructors
-
StartSimplifying queries with common table expressions (CTEs)
-
StartUsing multiple CTEs in a single query
-
StartLab 13: Using subqueries
-
StartLab 13: Answers
-
StartQuiz 13: Using subqueries
-
-
Module 14: Working with stored procedures and catalog views
8 Lessons-
StartModule introduction
-
StartWhat is a stored procedure?
-
StartExecuting stored procedures
-
PreviewExecuting dynamic SQL statements
-
StartQuerying the system catalog
-
StartLab 14: Working with stored procedures and catalog views
-
StartLab 14: Answers
-
StartQuiz 14: Working with stored procedures and catalog views
-
-
Module 15: Modifying data
19 Lessons-
StartModule introduction
-
StartInserting data into a table
-
StartInserting multiple rows at once with VALUES
-
StartWhat are default constraints?
-
StartWhat are identity columns?
-
StartIdentifying the last value inserted
-
StartWhat is a check constraint?
-
StartWhat is a unique constraint?
-
StartWhat is a foreign key constraint?
-
StartDeleting rows from a table
-
StartInserting rows with data from another table
-
StartInserting rows returned by a stored procedure
-
StartSelecting rows into a new table
-
StartTruncating a table vs deleting all rows
-
StartUpdating data in a table
-
StartMerging data into a table
-
StartLab 15: Modifying data
-
StartLab 15: Answers
-
StartQuiz 15: Modifying data
-
Writing T-SQL Queries for SQL Server
Creating reports, analytics, or applications? And need to get data out of SQL Server? Learn to write T-SQL queries like a pro !
Course Summary
Do you need to learn how to write T-SQL queries?
- You know that the information that you need is stored in SQL Server
- You need to find some data to extract it
- You need to create reports with tools like Reporting Services
- You are using analytic tools like Power BI, Tableau, QlikView, Excel, or Access and need to get data from SQL Server
- You want to learn to write queries properly, using commercial coding standards
- You are new to writing queries, or you are self-taught and want to make sure you are doing it correctly
We encourage you to complete the practical exercises. We have tried to make this as easy as possible. You do not need to install SQL Server, databases, etc. We have an online version hosted for you. All you need is to install a client tool. And there are client tools for Windows, Mac, and Linux.
