10985WV (55356): Introduction to SQL Databases Courseware

This course examines the services and features of Microsoft SQL from 2012 to 2022, including lessons and lab exercises on key attributes you’ll need on the job. It’s designed for novices and professionals who are interested in interacting with databases using Structured Query Language (SQL) and seek foundational knowledge and skills. This audience typically includes people who are moving into a database role or expect their role to require interacting with a database server that uses SQL.

There are instructor materials available for this course.

Audience profile

This course is intended for people with varied computer knowledge. As an introductory course, it does not require any previous knowledge of SQL, databases, or information technologies. As an introductory path, this course is ideal for those who haven’t previously interacted with databases or those who have previous experience with databases and languages distinct from SQL.

Why choose this course?

• Meticulous content relevance, tailored to Microsoft SQL 2022.
• Content on obsolete technology has been removed.
• Offers an array of demonstrations that bolster the learning material.
• Expert instructional design ensures a superior learning experience.
• Labs are developed by Waypoint in parallel with courseware, so they are 100% aligned.

• Conceptualize data organization in a database.
• Identify various types of databases.
• Retrieve information from a database using SQL, Transact-SQL (T-SQL), and other languages.
• Normalize and denormalize data stored in a database.
• Create entity relationship diagrams.
• Write effective and performant queries, use clustered and nonclustered indexes, and address concurrency issues.

Course Details

Course Duration: 2 Days

Module 1 Introduction to databases

This module introduces databases and tables, methods of using them to track relevant data, and common issues you might experience when working with them. You’ll explore the inner workings of relational databases, including how to use primary and foreign keys and write basic yet effective queries for a SQL Server database using SQL.

Lesson 1 Introduction to relational databases

• What is a database?
• What is a table?
• What is a relational database?
• Develop tables with primary and foreign keys

By completing this lesson, you’ll achieve the knowledge and skills to:

• Explain databases.
• Explain tables.
• Describe the key characteristics of relational databases.
• Describe table relationships in relational databases.
• Define primary and foreign keys in a relational database.
• Explain how keys constrain data in a relational database.

Lesson 2 Retrieve data from a relational database by using SQL

• Interact with a relational database
• What is SQL?
• Compare and contrast types of SQL commands
• Retrieve data with the SELECT statement
• Select individual columns in queries
• Refine a SQL SELECT statement with the WHERE clause
• Demonstration: Retrieve data from the database using a SELECT statement

By completing this lesson, you’ll achieve the knowledge and skills to:

• Interact with a relational database using SQL.
• Define SQL.
• Compare types of SQL commands.
• Write an SQL SELECT statement to retrieve a data table from a database.
• Modify an SQL SELECT statement to retrieve specific columns from a table.
• Refine an SQL SELECT statement with the WHERE clause.
• List logical and comparative operators that you can use in a WHERE clause.
• Write a SELECT statement using multiple conditions in a WHERE clause.

Lab 1 Use the SELECT statement to retrieve data from a database

• Exercise 1: Connect to the database
• Exercise 2: Retrieve data from the database

By completing this lab, you’ll achieve the knowledge and skills to:

• Connect to databases.
• Retrieve data from databases.

Module 2 Database tables

To create databases that are effective, usable, and more secure, it’s necessary for you to choose the types of data, properties, and constraints you implement in your database tables. This module covers why it’s essential to use schemas and views to improve organization and security as you grow your database.

Lesson 1 Identify and use Microsoft SQL Server data types

• Data types in Microsoft SQL Server
• Choose the correct data type
• NULL values

By completing this lesson, you’ll achieve the knowledge and skills to:

• Describe the differences between string, numeric, date/time, and other data types used in Microsoft SQL Server.
• Name common data formats for string, numeric, and date/time fields.
• Detail the steps to determine the best data type for a given field.
• List characteristics of NULL values.

Lesson 2 Create tables with SQL

• Create a table with SQL
• Demonstration: Create a table in Microsoft SQL Server Management Studio
• Control values allowed in columns using constraints
• Create a surrogate primary key by using the IDENTITY property
• Group tables by using a schema

By completing this lesson, you’ll achieve the knowledge and skills to:

• Create a table by using a SQL statement.
• Name and define seven commonly used constraints in SQL.
• Create a surrogate primary key by using the IDENTITY property.
• Create a schema and add tables to it.

Lesson 3 Altering and dropping a table

• Add a column to a table using the ALTER TABLE statement
• Change a column using the ALTER TABLE statement
• Drop a column using the ALTER TABLE statement
• Remove a table using the DROP TABLE statement

By completing this lesson, you’ll achieve the knowledge and skills to:

• Add a column to a table by using the ALTER TABLE statement.
• Change a column’s data type, constraints, and properties by using the ALTER TABLE statement.
• Drop a column from a table by using the ALTER TABLE statement.
• Drop a table from a database by using the DROP TABLE statement.

Lab 2 Create and alter a table in Microsoft SQL Server

• Exercise 1: Connect to the database
• Exercise 2: Create and alter a table

By completing this lab, you’ll achieve the knowledge and skills to create and alter database tables.

Lesson 4 Collect information from multiple tables by using joins and views

• Use JOIN to obtain data from multiple tables
• JOIN statement types
• Create views
• Demonstration: Create a view with SQL

By completing this lesson, you’ll achieve the knowledge and skills to:

• Use a JOIN statement to obtain data from multiple tables.
• Compare and contrast the four different types of JOIN statements.
• Define and create views.
• Create a view joining two tables.

Lab 3 Create and retrieve a view that uses the JOIN statement in Microsoft SQL Server

• Exercise 1: Connect to the database
• Exercise 2: Create a view that retrieves information using a JOIN statement.

By completing this lab, you’ll achieve the knowledge and skills to:

• Retrieve a synthesis of information from two tables using the SELECT and JOIN statements.
• Create a view that runs a SELECT statement.

Module 3 Add and update data

This module covers using statements to add, alter, and remove rows in a table. It also covers creating stored procedures and using input parameters in stored procedures.

Lesson 1 Insert, update, and delete information

• Add information to a table using the INSERT keyword
• Add multiple rows to a table at once
• Update values in a table
• Use DELETE to delete rows in a table

By completing this lesson, you’ll achieve the knowledge and skills to:

• Add rows to a table using the INSERT statement.
• Use the INSERT statement to add multiple rows of information at once.
• Alter values in a table using the UPDATE statement.
• Remove rows in a table using the DELETE statement.

Lab 4 Add and change data in SQL Server

• Exercise 1: Connect to the database
• Exercise 2: Inspect a table in Microsoft SQL Server Management Studio
• Exercise 3: Add and change data in a database using INSERT and UPDATE statements

By completing this lab, you’ll achieve the knowledge and skills to:

• Inspect a table using Microsoft SQL Server Management Studio.
• Add data to a table using the INSERT statement.
• Change data in a table using the UPDATE statement.

Lesson 2 Stored procedures

• Define a stored procedure
• Use multiple statements in a stored procedure
• Use input parameters in a stored procedure
• Describe the uses of stored procedures
• Demonstration: Create a stored procedure

By completing this lesson, you’ll achieve the knowledge and skills to:

• Define a stored procedure.
• Use multiple statements in a single stored procedure.
• Include input parameters in a stored procedure.
• Describe some of the uses of stored procedures.

Lab 5 Create and use stored procedures in Microsoft SQL Server

• Exercise 1: Connect to the database
• Exercise 2: Create and run a stored procedure in Microsoft SQL Server

By completing this lab, you’ll achieve the knowledge and skills to:

• Create a stored procedure with input parameters.
• Run a stored procedure.

Module 4 Entity relationships

This module covers entities and the relationships between them. It also discusses using the various types of referential integrity.

Lesson 1 Model entity relationships

• What is an entity?
• Entity keys
• Candidate keys
• Entity relationships and cardinality
• Join tables
• Compare optional and mandatory relationships
• Entity relationship modeling
• Demonstration: Create an entity relationship diagram in Microsoft SQL Server Management Studio

By completing this lesson, you’ll achieve the knowledge and skills to:

• Define an entity including its attributes and domains.
• Define common keys in entities.
• Define entity relationships.
• Describe the relationship cardinality types one-to-one, one-to-many, and many-to-many.
• Compare optional and mandatory relationships.
• Model entity relationships using crow’s feet notation.

Lesson 2 Design for referential integrity

• What is referential integrity?
• Ensure declarative referential integrity by using key constraints
• Ensure procedural referential integrity by using triggers
• Cascading referential integrity

By completing this lesson, you’ll achieve the knowledge and skills to:

• Define and describe referential integrity.
• Use primary and foreign key constraints to ensure declarative referential integrity.
• Describe triggers used to ensure procedural referential integrity.
• Enable foreign key options to create cascading referential integrity.

Lab 6 Create an entity relationship diagram in Microsoft SQL Server Management Studio

• Exercise 1: Connect to the database
• Exercise 2: Create a database diagram in Microsoft SQL Server Management Studio

By completing this lab, you’ll achieve the knowledge and skills to Create a database diagram in Microsoft SQL Server Management Studio.

Module 5 Normalization and denormalization

This module covers normalization and denormalization in a database, their benefits, and when each should be used.

Lesson 1 Normalization

• What is normalization?
• First normal form
• Second normal form
• Third normal form

By completing this lesson, you’ll achieve the knowledge and skills to:

• Define normalization.
• Describe the first normal form.
• Describe the second normal form.
• Describe the third normal form.

Lesson 2 Denormalization

• Defining denormalization
• Adding redundant columns
• Saving computed data
• Potential issues
• Demonstration: Denormalize a table

By completing this lesson, you’ll achieve the knowledge and skills to:

• Define denormalization.
• Describe how redundant columns can be beneficial to a database.
• Explain why it might be beneficial to save computed data in a column or table.
• List concerns and issues with denormalization.

Lab Denormalize a table by adding a redundant column of computed data

• Exercise 1: Connect to the database
• Exercise 2: Denormalize a table by adding a column and populate it with computed data

By completing this lab, you’ll achieve the knowledge and skills to denormalize a table by adding a column and populate it with computed data.

Module 6 Query performance and safety

This module covers writing effective and performant queries and using clustered and nonclustered indexes. It also discusses concurrency issues and the transactions and isolation levels you can use to address them.

Lesson 1 Indexes

• What are indexes?
• Clustered indexes
• Nonclustered indexes
• Indexes and performance

By completing this lesson, you’ll achieve the knowledge and skills to:

• Define indexes.
• Define and describe a clustered index.
• Define and describe a nonclustered index.

Lesson 2 Write performant SQL code

• Limit data referenced and returned
• Use indexes
• Use SARGable queries

By completing this lesson, you’ll achieve the knowledge and skills to:

• Limit data referenced and returned by a query.
• Use indexes to boost the efficiency of data retrieval and collation.
• Write SQL queries in a SARGable manner.

Lab 8 Create indexes and consult execution plans in Microsoft SQL Server Management Studio

• Exercise 1: Connect to the database
• Exercise 2: Check and create indexes
• Exercise 3: Consult execution plans in Microsoft SQL Server Management Studio

By completing this lab, you’ll achieve the knowledge and skills to:

• Check the indexes on a table in Microsoft SQL Server Management Studio.
• Create a nonclustered index on a table.
• Compare execution plans in Microsoft SQL Server Management Studio.

Lesson 3 Concurrency

• What is concurrency?
• Problems caused by concurrency
• Isolating transactions
• Limiting concurrency
• Demonstration: Write an isolated transaction

By completing this lesson, you’ll achieve the knowledge and skills to:

• Define and describe concurrency.
• List and compare the most common issues caused by concurrency.
• Implement transaction isolation to solve some problems caused by concurrency.