Databases

Candidates for this exam will demonstrate foundational knowledge of how to design and query relational databases, such as MySQL, Microsoft SQL Server, or Oracle.

Database Design

1.1 Given a scenario, design tables for storing data

• Identify entities, rows/records, columns/fields

1.2 Given a scenario, identify the appropriate primary key

• Primary key, composite/compound key

1.3 Given a scenario, choose data types to meet requirements

• Definition and importance of data types; how data types affect storage

requirements; data types for storing text, numbers, dates and times, and

Boolean values

1.4 Given a scenario, design relationships between tables

• How to establish relationships using primary and foreign keys, entityrelationship diagrams (ERDs), referential integrity

1.5 Normalize a database

• Reasons for normalization, how to normalize a database to third normal

form (3NF)

1.6 Given a scenario, identify data protection measures

• Backups, restore, principle of least privilege , GRANT, WITH GRANT

OPTION, REVOKE, purpose of roles

2. Database Object Management using DDL

2.1 Construct and analyze queries that create, alter, and drop tables

• Create, alter, and drop tables by using proper ANSI SQL syntax; NULL and

NOT NULL

2.2 Construct and analyze queries that create, alter, and drop views

• Create, alter, and drop views by using proper ANSI SQL syntax; purpose of

views

2.3 Construct and analyze stored procedures and functions

• Input and output parameters, return values, purpose of stored procedures

2.4 Given a scenario, choose between clustered and non-clustered

indexes

• When to use clustered vs. non-clustered indexes, syntax for creating

indexes

3. Data Retrieval

3.1 Construct and analyze queries that select data

• INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN (Cartesian product),

and FULL OUTER JOIN; self joins; combine result sets by using UNION and

INTERSECT; DISTINCT; column alias; computed columns

3.2 Construct and analyze queries that sort and filter data

• ORDER BY, WHERE, LIKE, BETWEEN, AND, OR, NOT, TOP (LIMIT), IN, NOT

IN, ANY, ALL, NULL, NOT NULL, comparison operators

3.3 Construct and analyze queries that aggregate data

• GROUP BY, HAVING, MIN, MAX, COUNT, AVG (AVERAGE), SUM

4. Data Manipulation using DML

4.1 Construct and analyze INSERT statements

• INSERT INTO SELECT, INSERT INTO VALUES

4.2 Construct and analyze UPDATE statements

• Update data in a single table

4.3 Construct and analyze DELETE statements

• Delete data from a single table

5. Troubleshooting

5.1 Troubleshoot data object management query failures

• Syntax and runtime errors

5.2 Troubleshoot data retrieval query failures

• Syntax and runtime errors

5.3 Troubleshoot data manipulation query failures

• Syntax and runtime errors