Practical T-SQL: 300+ quizzes;165 hands-on, useful exercises

You will be able to understand the difference between Windows and SQL Server authentication modes. Able to connect to a SQL Server instance on your computer

Use the SQL Server to configure SQL Server instance such as enabling remote connection

A look at the SQL Server authentication modes

You will be able to use the Object Explorer in Management Studio. Display menu commands for working with an object such as tables. Understand the purpose of database schemas. Hide and unhide the Object Explorer

Before you SQL to query a database, you need to know how the database is defined. In particular you need to know how columns in tables are defined and how the tables are related. This lesson teaches you how to use the database diagram to study the structure of your database

Become familiar with the default directory for SQL database backups. Become familiar with backup settings. You will be able to backup and restore databases

After this lesson, you will be able to use the database diagram to illustrate relationships between tables in a database; how to view and modify the column definitions of a table in the database diagram

After this lesson, you will be able to use the Object Explorer to understand the structure of your database objects such as tables; be able to describe the functions of the Table Designer; modify the columns of a table within the Designer, understand the different data types in SQL Server

• Using SQL built-in data modification tools to modify small tables without writing code using the Edit Top 200 Rows command

• Displaying the top 1000 records without writing code

• Modify the automatically-generated queries

• You will be able to debug and correct common causes of errors

• You will be able to use the IntelliSense features to help identify errors

• Understand common types of query errors (Misspelling, wrong database connection, misspelling a keyword, omitting quotation marks etc)

• How to get help from Books Online

• Using the Error Messages tab

• Describe the functions of the Query Designer panes (Diagram pane, Criteria pane, SQL pane)

• Be able to use the Query Designer to build simple action queries and edit existing queries

• Appreciate the limitations of the Query Designer

Best practices to write SELECT statement; follow logical query processing principles; use the WHERE to filter rows and the ORDER BY clause for sorting; understand the basic SELECT syntax

Be able to code SELECT statement for different scenarios such as retrieving all columns, sorting result set, columns that include calculation, retrieving data between given dates, SELECT statement that returns empty result set

1. Understand how to specify columns in a SELECT statement

   1. using the base table value (All columns, named columns)

2. Calculated values

   1. result of a calculation

   2. result of a concatenation

   3. result of a function

3. Be able to use expressions combining column names and operators that evaluate to a single value

Using functions to provide column specification

• Use of the DISTINCT keyword in a SELECT statement to prevent duplicate (identical) rows from being included in a result set

• Use the TOP clause within a SELECT clause to limit the number of rows included in a result set

• become familiar with all the SQL comparison operators for filtering data

• be aware of importance of using the operators on similar data types

• After this lesson you will be able to use the logical operators (AND, NOT, OR) in compound search conditions

• Learn the sequence in which SQL Server applies the operators

• After this lesson you will be able to code a WHERE clause that uses the IN operator

• combine the IN operator with the NOT operator

• use a subquery to supply the list for the IN operator

• be able to use the LIKE operator in your search condition

• use wildcards in string pattern for the LIKE operator

• learn the various wildcard symbols

• the proper use of the NOT operator with the string pattern

• After this lesson you will be able to give a proper definition for a null value

• how to use the IS NULL clause to test for null values in your data

• be able to use the parts of the expanded ORDER BY clause

• understand the default order of the ORDER BY clause

• understand how the DISTINCT keyword affects the ORDER BY clause

• know the default sort sequence of nulls, special characters, numbers, and letters

• be able to use arithmetic expression or string expression in the ORDER BY clause

• use column numbers to specify sort order

• use column alias in the SELECT clause in the ORDER BY clause

• use of the OFFSET clause to specify the number of rows to skip before rows are returned from the result set

• use of the FETCH clause to specify the number of rows that should be retrieved

  
  

We look at the learning objectives for this section and also all the resources for this section are included here

After this lecture you will be able to use the INNER JOIN to join two or more tables

Correlation names are temporary table names assigned in the FROM clause; understand when to use correlation names in your SELECT statement

• Be able to use join conditions that include two or more conditions connected by AND or OR operators

• Compare compound join condition with script that uses the ON expression and search conditions in the WHERE clause to achieve the same goal as compound join

• a self-join is a join that joins a table with itself

• best practices for using self-join

• Understand how to join more than two tables

• Understand the concept of interim tables in joining multiple tables

  
  

Use outer join to join more than two tables

We look at a couple of join examples

More about outer join

• a cross join joins each row from the first table with each row from the second table

• How to code a cross join using the explicit and implicit syntax (a Cartesian product)

• Understand the syntax for the union operation to join two tables

• be aware of the default result set returned by the union operator

Examples of how to use unions

More examples on unions

• learn the syntax for the EXCEPT and INTERSECT operators

• learn the structures of the tables appropriate for the operations

• Learn about the source of the column names of the result set

• Lesson objectives and learning outcome

• Be able to code summary queries involving the MIN, MAX aggregate functions

• Using the DISTINCT keyword in summary queries

• Summary queries that use the COUNT(*), AVG, and SUM functions

• Summary queries that work on non-numeric columns

Aggregate functions examples

• Understand the syntax of the SELECT statement with the GROUP BY and HAVING clauses

• Describe the differences between the HAVING clause and the WHERE clause as filtering conditions

Get some hands-on coding of the use of the GROUP BY and HAVING clauses

Know the scope of columns for the HAVING and WHERE clauses

Learn the processing of SELECT queries that include the HAVING and WHERE clauses

Be able to use the AND and OR clauses to code compound conditions in a HAVING clause

Be able to handle search conditions that include aggregate functions

Use SQL Server extensions (OVER operator) to summarize data

Section 6: hands-on exercises

Use the OVER clause and the PARTITION BY clause summary and group data

Use OVER clause with aggregate functions to summarise data and retain individual rows used to calculate the summaries

Introduction and Learning objectives

Introduction to subqueries

In most cases subqueries can also be coded as joins. We look at the pro and cons of subqueries vs. joins

• become familiar with variety of techniques to work with search queries in search conditions

• turn NOT IN operator in a subquery to an outer join

• Learn to use correlated subqueries in your SELECT statements

• Use the EXISTS operator in a SELECT clause to test one or more rows are returned by the subquery

• Use the negate EXISTS (NOT EXISTS)

Using subqueries in the FROM clause to create derived tables

Using subqueries in a SELECT statement

• Guidelines for coding complex queries

Writing pseudocodes

1. Procedure for building complex queries

   1. how to code CTE

   2. using CTE with SELECT, INSERT, UPDATE, and DELETE statements

• • Lesson and knowledge objectives

  • Resources for this section

• Use INSERT INTO to create test tables

How to insert new rows into tables

• create a sample table to test variety of the INSERT statement

• learn to use the INSERT INTO that handles null and default values

• learn the syntax of the INSERT statement for inserting rows selected from another table

• learn to use subqueries in place of the VALUES clause in the INSERT statement

• learn the rules of column specifications in the INSERT statement

• learn the syntax of the UPDATE statement

• how to perform a basic update operations

• an UPDATE statement that uses an arithmetic expression

• techniques to prevent modifying wrong rows

• learn to use subqueries in your update queries

• learn to code subqueries in the SET clause to return the value that's assigned to a column

• learn to code subqueries in the FROM clause to identify the rows that are available for update

• learn to code a subquery in the WHERE clause to provide one or more values used in the search condition

• learn to use joins in the FROM clause to specify column values or search conditions that depends on data other than the one named in the UPDATE clause

• learn to use columns from the joined tables in the values you assign to columns in the SET clause or in the search condition of a WHERE clause

• how to perform a basic delete operations

• learn the techniques to avoid deleting wrong rows

• learn to use subqueries and joins in the FROM clause of a DELETE statement to base the delete operation on the data in the tables other than the one named in the DELETE clause

• lean to use subqueries in the WHERE clause to provide one or more values in the search condition

• learn the syntax of the MERGE statement

• learn how to perform a basic merge operation

• learn how to code more complex merge operations

An overview of data types. Lesson objectives

Integer data types: Bigint, Int, Smallint, Tinyint, Bit, and the decimal data types: Decimal, Numeric, Money, Smallmoney

Presents the four most common string data types supported by SQL Server using the default collation

How to convert data: how data conversion works

The syntax and examples of the CAST function

How to convert data using the CONVERT function: Syntax and example of the CONVERT function

Use the TRY_CONVERT function to prevent possible conversion errors: syntax with examples

Introduction to SQL functions

A summary of the string functions

Solving problems that occur with string functions

A summary of the numeric functions

A summary of the date/time functions

How to use the CASE function

The syntaxes of the IIF and CHOOSE functions with examples

How to use the COALESCE and the ISNULL functions

Learn the basic approach for designing a data structure:

1. Identify the data elements

2. Subdivide each element into the smallest useful components

3. Identify the tables and assign columns

4. Identify the primary and foreign keys

5. Review whether the data structure is normalized

6. Identify the indexes

Learn the basic approach for designing a data structure:

1. Identify the data elements

2. Subdivide each element into the smallest useful components

3. Identify the tables and assign columns

4. Identify the primary and foreign keys

5. Review whether the data structure is normalized

6. Identify the indexes

Design the data structure

Identifying the data elements required by the system

How to subdivide the data elements to its atomic but useful unit of data

Example based steps to identify database entities/tables and their columns

Identify primary and foreign keys

Enforcing the relationships between tables

How to apply normalization to the data

Identify index columns

Applying the first normal form

The first normal form: How it works

The second/third normal forms