MTA Database Fundamentals (98-364) Cert Prep: 1 Core Concepts

Introduction

• Welcome
• Preparing for Microsoft Technology Associate (MTA) Exam 98-364
• Using the exercise files
• Challenges explained

1. Understanding Data Storage Models

• What are databases?
• Understanding flat file databases
• Understanding hierarchical databases
• Understanding relational databases
• Exploring database fundamentals
• Calculating values

2. Building Your Server

• Understanding the role of the server
• Downloading SQL Server 2014 Express
• Installing SQL Server 2014 Express
• Previewing the SQL Server Management Studio (SSMS) graphical interface
• Using SSMS to restore a database
• Creating your first database in SSMS
• Creating your first table in SSMS
• Modifying your table design
• Removing a database from the server
• Introducing views
• Peering inside the system tables
• Challenge: Start building your own database
• Solution: Start building your own database

3. Understanding Data Definition Language (DDL)

• Using DDL statements to create database objects
• Creating new database objects
• Choosing the active database with USE
• Altering existing database objects
• Removing objects with DROP
• Removing records from a table with TRUNCATE
• Challenge: Creating a table using DDL
• Solution: Creating a table using DDL

4. Understanding Data Manipulation Language (DML)

• Writing commands in DML
• Selecting data from a table
• Inserting records into a table
• Updating data
• Deleting rows from a table
• Merging data tables
• Challenge: Using DML
• Solution: Using DML

5. Understanding Relational Database Concepts

• Establishing relationships
• Exploring data constraints
• Adding indexes to tables
• Following naming conventions
• Organizing the design with schemas

Conclusion

• Next steps