Microsoft Database Exam 98-364

https://www.microsoft.com/en-us/learning/exam-98-364.aspx

Understanding core database concepts (20–25%)

Create database objects (20–25%)

Manipulate data (25–30%)

Understand data storage (15–20%)

Administer a database (10–15%)

      Core

• Understand how data is stored in tables
  • Understand what a table is and how it relates to the data that will be stored in the database; columns/fields, rows/records
• Understand relational database concepts
  • Understand what a relational database is, the need for relational database management systems (RDBMS), and how relations are established
• Understand data manipulation language (DML)
  • Understand what DML is and its role in databases
• Understand data definition language (DDL)
• Understand how T-SQL can be used to create database objects, such as tables and views

Database Objects

• Choose data types
  • Understand what data types are, why they are important, and how they affect storage requirements
• Understand tables and how to create them
  • Purpose of tables; create tables in a database by using proper ANSI SQL syntax
• Create views
  • Understand when to use views and how to create a view by using T-SQL or a graphical designer
• Create stored procedures and functions
• Select, insert, update, or delete data

Manipulating Data

• Select data
  • Utilize SELECT queries to extract data from one table, extract data by using joins, combine result sets by using UNION and INTERSECT
• Insert data
  • Understand how data is inserted into a database, how to use INSERT statements
• Update data
  • Understand how data is updated in a database and how to write the updated data to the database by using the appropriate UPDATE statements, update by using a table
• Delete data
• Delete data from single or multiple tables, ensure data and referential integrity by using transactions

Data Storage

• Understand normalization
  • Understand the reasons for normalization, the five most common levels of normalization, how to normalize a database to third normal form
• Understand primary, foreign, and composite keys
  • Understand the reason for keys in a database, choose appropriate primary keys, select appropriate data type for keys, select appropriate fields for composite keys, understand the relationship between foreign and primary keys
• Understand indexes
• Understand clustered and non-clustered indexes and their purpose in a database

Administration

• Understand database security concepts
  • Understand the need to secure a database, what objects can be secured, what objects should be secured, user accounts, and roles
• Understand database backups and restore
  • Understand various backup types, such as full and incremental, importance of backups, how to restore a database