Introduction to SQL Databases Courseware (10985WV)

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.

Lessons

• Introduction to relational databases
• Retrieve data from a relational database by using SQL

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

• Use the SELECT statement to retrieve data from a database

After completing this module, students will be able to:

• Develop tables with primary and foreign keys
• Interact with a relational database
• 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

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.

Lessons

• Identify and use Microsoft SQL Server data types
• Create tables with SQL
• Altering and dropping a table
• Collect information from multiple tables by using joins and views

Lab 1: Create and alter a table in Microsoft SQL Server

• Create and alter a table in Microsoft SQL Server
• Create and retrieve a view that uses the JOIN statement in Microsoft SQL Server

Lab 2: Create and alter a table in Microsoft SQL Server

• Create and alter a table in Microsoft SQL Server

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

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

After completing this module, students will be able to:

• Data types in Microsoft SQL Server
• Choose the correct data type
• NULL values
• Create a table with SQL
• Control values allowed in columns using constraints
• Create a surrogate primary key by using the IDENTITY property
• Group tables by using a schema
• 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
• Describe the key features, editions, and hardware requirements of Windows 7.
• Use JOIN to obtain data from multiple tables
• JOIN statement types
• Create views

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.

Lessons

• Insert, update, and delete information

Lab 1: Add and change data in SQL Server

• Add and change data in SQL Server

Lab 2: Create and use stored procedures in Microsoft SQL Server

• Create and use stored procedures in Microsoft SQL Server

After completing this module, students will be able to:

• 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
• Define a stored procedure
• Use multiple statements in a stored procedure
• Use input parameters in a stored procedure
• Describe the uses of stored procedures

Module 4: Entity relationships

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

Lessons

• Model entity relationships
• Design for referential integrity What is referential integrity?

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

• Create an entity relationship diagram in Microsoft SQL Server Management Studio

After completing this module, students will be able to:

• What is an entity?
• Entity keys
• Candidate keys
• Entity relationships and cardinality
• Join tables
• Compare optional and mandatory relationships
• Entity relationship modeling
• Ensure declarative referential integrity by using key constraints
• Ensure procedural referential integrity by using triggers
• Cascading referential integrity

Module 5: Normalization and denormalization

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

Lessons

• Normalization
• Denormalization

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

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

After completing this module, students will be able to:

• What is normalization?
• First normal form
• Second normal form
• Third normal form
• Defining denormalization
• Adding redundant columns
• Saving computed data
• Potential issues

Module 6: Query performance and safety

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

Lessons

• Indexes
• Write performant SQL code
• Concurrency

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

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

After completing this module, students will be able to:

• Clustered indexes
• Nonclustered indexes
• Indexes and performance
• Limit data referenced and returned
• Use indexes
• Use SARGable queries
• What is concurrency?
• Problems caused by concurrency
• Isolating transactions
• Limiting concurrency