• Online, Self-Paced
Course Description

A database is used to store data in electronic format. Organizations use a Database Management System, also known as a DBMS, to control, store, organize, and retrieve that data for users. There are multiple types of databases, such as flat-file and hierarchical. The most widely used database is the relational database that stores data in relations, which are tables that store data in rows or tuples and columns or attributes. In this course, you will learn what databases, DBMS, and RDBMS are and be introduced to different database models. The course covers table design for relational databases including the data types and constraints supported by SQL Server. It also demonstrates how to use DML statements to query data and manipulate result sets. It also covers how to update and delete data from a SQL Server database and how to use transactions to ensure data and referential integrity. This course can be used as part of the preparation for Microsoft's exam 98-364, Database Fundamentals, which leads to a Microsoft Technical Associate (MTA) certification.

Learning Objectives

Getting Started

  • start the course
  • define a database, describe how data is stored in tables containing records and fields, and describe flat, hierarchical, and relational databases
  • define database management systems (DBMS) and relational database management systems (RDBMS)
  • describe the purpose of and navigate SQL Server Management Studio (SSMS), a SQL Server instance, and its objects

 

Database Design

  • describe the data types supported by SQL Server
  • describe the importance of data integrity to related database tables and list the methods for enforcement
  • define the standards used in tables for data storage such as removing duplicates, breaking down fields, and the use of primary keys in the context of SQL Server examples
  • define the standards used in column design and its properties such as auto increment columns, default and null values, and modifying size and format in the context of SQL Server examples
  • define the table and column properties such as collation, data type, size, and field properties in the context of SQL Server examples
  • define relationships in a database such as one-to-many, one-to-one, and many-to-many, outlining the role of primary, composite, and foreign keys in the context of SQL Server examples

 

DML Statements

  • describe the purpose of SQL, Data Definition Language or DDL, and Data Manipulation Language or DML statements in the context of SQL Server
  • use SELECT statements to query tables and columns
  • use SELECT statements with WHERE, AND, NOT, BETWEEN to filter results
  • use the ORDER By clause to order result sets returned from a SELECT statement
  • use the SQL Server aggregate functions COUNT, AVG, MIN, and MAX to return specific result sets

 

Multi-Table Queries

  • use INNER JOINS to retrieve data from multiple SQL Server tables
  • use OUTER JOINS to retrieve data from multiple SQL Server tables
  • use CROSS JOINS to retrieve data from multiple SQL Server tables
  • use the UNION statement to combine the results from multiple SELECT statements executed on a SQL Server database
  • use INTERSECT and EXCEPT to compare and extract data from result sets returned from SQL Server data

 

Working with Data

  • describe how data is inserted into a database and use INSERT statements to insert one or more rows into a SQL Server database
  • use BULK INSERT to insert data into a SQL Server database
  • describe how data is updated to a SQL Server database and use appropriate UPDATE statements
  • use DELETE to delete data from single or multiple SQL Server tables
  • use transactions with SQL Server to ensure data and referential integrity

 

Practice: DML Statements

  • use Data Manipulation Language or DML statements to query, update, and delete data in a SQL Server database

 

Framework Connections

The materials within this course focus on the Knowledge Skills and Abilities (KSAs) identified within the Specialty Areas listed below. Click to view Specialty Area details within the interactive National Cybersecurity Workforce Framework.

Feedback

If you would like to provide feedback for this course, please e-mail the NICCS SO at NICCS@hq.dhs.gov.