• Introduction to the SQL Server Management Studio (SSMS) graphical tool

• You learn how to create, modify, or delete a table using the Management Studio interface

• Use management studio to set basic properties of a table, such as identity columns, key columns

• How to connect to a database server using either Windows or SQL Server authentication

• Exploring the database objects

You learn to:

• Explore the Management Studio and its windows

• Explore the Object Browser and its onjects

• Use the database diagram to illustrate the relationships between the tables in a database

• The anatomy of the database diagram

• Learn:

  1. How to view the column definition of a table

  2. How to modifying the column definition

• Using the Management Studio to view and modify records in a table

1. Introduction to the Query Editor. Entering and executing queries. Using the IntelliSense feature

2. Open saved queries

3. Save your queries

4. The Available Database combo box

• After you get your query working you may want to save it:

  1. Saving and opening saved queries

  2. Cut, copy, paste from one query to another

• The anatomy of the Query Designer

• Using the query designer to build simple queries

• Modify queries build by the Query Designer

A look at the syntax of a simple SELECT statement specifying the WHERE filter clause and the ORDER BY clause

Become familiar with some of the benefits provided by views

• A look at the five commonly used arithmetic operators in SELECT statements

• The precedence of the arithmetic operators

• The use of parentheses to simplify complex expressions

Using functions in column specifications in SELECT statements

LEFT()

CONVERT()

GETDATE() etc.

Use of the DISTINCT keyword in a SELECT statement to eliminate duplicate rows in result set

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

• Including the PERCENT and the WITH TIES with the TOP clause

• The row played by the ORDER BY clause when the TOP clause is used in a SELECT statement

The use of the WHERE clause as a filter condition

• Using the AND, OR and NOT logical operators to create compound filter conditions

1. Use of the IN operator to test whether an expression is equal to a value in a list of expressions

2. Using the NOT operator with the IN operator

3. Compare the test expression to items in a list returned by a subquery

• Using the BETWEEN operator to test whether an expression falls within a range of values

• Using the NOT operator with the BETWEEN operator

• The use of the LIKE operator to retrieve rows that match a string pattern (mask)

• Wildcards

• Performance issues associated with the LIKE operator

Using the IS NULL clause to test for null values

Using the ORDER BY clause to sort result set

Using expressions and column alias to sort result set

The syntax of the ORDER BY clause to retrieve a range of rows

• Introduction to this section

Become familiar with the syntax of the INNER JOIN and how to use it in a SELECT statement

• The syntax for an inner join that uses correlation names

• Use of correlation names to simplify or clarify query

• Coding compound join conditions

• Coding join condition in the FROM clause versus coding in the WHERE clause

• A self-join is a join where a table is joined to itself

• Examples of self-join

• Multi-table join explained; how they work

• Examples

Working with outer joins (LEFT OUTER and RIGHT OUTER, FULL OUTER joins)

Use the OBJECT_ID function to test the the existence of a database object and take appropriate action

• Looking at examples of outer join

Outer joins than join more than two tables

How to use cross joins

1. How to work with unions:

   
   

   1. The syntax of a union

   2. Unions that combine data from different tables

Create dynamic SQL scripts that accept parameters

Use the EXEC statement to execute dynamic queries

Demonstrate unions with examples

Looking at more examples of the UNION operator

Be able to use the EXCEPT and INTERSECT operators to work with two or more tables

Compare the EXCEPT and INTERSECT operators with the UNION operator

• Become familiar with the syntax of the aggregate functions

• Use aggregate functions in SELECT statement and the rules governing the use of aggregate functions

• Use of aggregate functions in summary queries

• Examples of aggregate functions

• Using COUNT(*) and DISTINCT in aggregate functions

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

• Coding summary queries that use the GROUP BY and HAVING clauses

Queries that use the GROUP BY and HAVING clauses - Examples

• Understanding the differences between search condition specified in the WHERE clause and search condition in the HAVING clause

• Using the AND and OR logical operators to code complex search clauses

• How to use the OVER clause

Applied

1. Code SELECT statements that require subqueries.

2. Code SELECT statements that use common table expressions (CTEs) to define the subqueries.

Knowledge

1. Describe the way subqueries can be used in the WHERE, HAVING, FROM and SELECT clauses of a SELECT statement.

2. Describe the difference between a correlated subquery and a noncorrelated subquery.

3. Describe the use of common table expressions (CTEs).

Become familiar with the four ways to introduce a subquery in a SELECT statement:

1. In a WHERE clause as a search condition

2. In a HAVING clause as a search condition

3. In a FROM clause as a table specification

4. In the SELECT clause as a column specification

• Become familiar with when to use a subquery instead of a join.

• Advantages of using a join

• Advantages of using a subquery

How to use subqueries with the IN operator

A correlated subquery is a subquery that is executed once for each row processed by the outer query

Learn how to write correlated subqueries

Learn the syntax of the EXISTS operator demonstrated with examples

Although you will typically use subqueries in the WHERE or HAVING clause of a SELECT statement, you can also use them in the FROM and SELECT clauses

• How to code subqueries in the FROM clause

Coding subqueries in the SELECT clause

Become familiar with the procedure to plan and test queries

Writing pseudocode for complex queries

A Common Table Expression (CTE) is a feature that allows you to code an expression that defines a derived table

• Lean how to code a CTE

Using the SELECT INTO statement to create tables

Use the INSERT statement to insert rows into a table

Using INSERT with or without naming columns and handling identity columns properly

Examples of the INSERT INTO statement:

• Insert statement that adds the new row without using a column list

• Insert statement that adds the new row using a column list

• Insert statement that adds multiple new rows

• How to insert default values and null values

How to insert rows into a table where the rows are selected from a second table

• Perform a basic update operation

How to use subqueries in an update operation

How to use joins to update records in a table

How to perform a basic delete operation

How to use subqueries and joins in a delete operation

How to merge rows:

• Perform basic merge operation

• Perform more complex merge operation

A column's data type specifies the kind of information the column is intended to store. In addition, a column's data type determines the operations that can be performed on the column

• A thorough understanding of SQL data types

An understanding of the numeric data types:

• bigint

• int

• smallint

• tinyint

• bit etc.

The string data types used to store standard characters

• Converting from one data type to another

• Implicit and explicit conversion

• The CAST and CONVERT functions

How to convert data using the CAST function

How to convert data types using the CONVERT function

Using the TRY_CONVERT function to prevent conversion errors

An introduction to the most common functions used in SQL Server

Examples of string functions

Solve common problems that occur with string data

• Use of the CAST function to sort by a string column that contains numbers

• Use string functions to parse string

• Use LEFT, RIGHT, SUBSTRING, and LEN to extract individual components from a string

In addition to the string functions, SQL Server provides several numeric functions

• How to use numeric functions to solve common problem that occur when you work with numeric data

How to solve common problems that occur with numeric data

1. Search for approximate real values

2. Format real numbers

A summary of the date/time functions

Using the CASE function:

1. The syntax of the simple CASE function

2. The syntax of the searched CASE function

3. Examples of SELECT statement that uses a searched CASE function

The IIF and CHOOSE functions are known as logical functions

The syntaxes of the COALESCE and ISNULL functions

The use of the COALESCE and ISNULL functions to substitute null values

The basic steps for designing a database:

1. Identify the data elements

2. Subdivide each element and assign columns

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

A look at the database structure for this section

The basic steps for a database design

Identify the data elements on a table

Guidelines for subdividing data elements into components

The necessity to recombine subdivisions for reporting purposes

Grouping identified data elements by entities that will later become the tables of the database

Identify primary keys and foreign keys to establish relationships

Enforcing relationships between tables to enforce referential integrity

Normalizing the database tables

Identify columns to be indexed:

1. When the column is a foreign key

2. When the column is used frequently in search conditions or joins

3. When the column contains a large number of distinct values

4. When the column is updated frequently

How to apply the first three normal forms

Applying the first normal form to a table

Applying the second and the third normal forms