Overview

Introduction

The Core Database Concepts module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

Learning Outcomes

In this module, you will complete the following exercises:

  • Exercise 1 - Relational Database Concepts
  • Exercise 2 - Working with Data Manipulation Statements
  • Exercise 3 - Working with Data Definition Language

After completing this lab, you will be able to:

  • Demonstrate how relations are established between tables
  • Create tables, store, modify and retrieve data from tables.

Exam Objectives

The following exam objective is covered in this lab:

  • 1.1 Understand how data is stored in tables

Lab Duration

It will take approximately 20 minutes to complete this lab.

Exercise 1 - Relational Database Concepts

A relational database is a means of storing and accessing information through relationships. In relational databases, you can retrieve data from more than one related table. You must enforce certain integrity rules in relational tables so that the data stored are accurate and are always accessible. For this, all rows in a table must have a unique identifier, known as the primary key value. Any column that is defined as a primary key cannot be null.

Also, both the related tables should have one common column in order to relate them to each other. This column, which must be the primary key in one table, should be defined as a foreign key in the second table. In the referencing table, foreign keys need not have unique values. Also, you cannot delete a row in the referenced table nor can change the primary key, if a foreign key refers to the row, unless cascade action is permitted.

Learning Outcomes

After completing this exercise, you will be able to:

Demonstrate how relations are established between tables

Exercise 2 - Working with Data Manipulation Statements

You can use data manipulation statements (DML) to work with the data in the database tables. DML statements allow you to modify or retrieve data from table. The commonly used DML statements include:

  • INSERT: This statement helps you to insert new rows (data) into a table.
  • UPDATE: This statement helps you to modify an existing data in a table.
  • DELETE: This statement helps you to delete data from a table.
  • SELECT: This statement helps you to retrieve data from a table.

Exercise 3 - Working with Data Definition Language

You can use the data definition language (DDL) to define the structure of database objects. For example, you can use the DDL to create databases, tables in a database, and so on. Also, the DDL statements helps you to alter and drop data structures. The commonly used DDL statements include:

  • CREATE: This statement helps you to create new objects. For example, you can use the CREATE DATABASE to create a new database in a SQL Server instance.
  • ALTER: This statement helps you to modify the definition of an existing object. For example, you can use the ALTER DATABASE statement to alter the options of an existing database.
  • DROP: This statement helps you to remove existing objects. For example, you can use the DROP TABLE statement to remove a table from a database.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.