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. At Course Completion 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.