The Relational Databases and SQL exam is based on the Intended Learning Outcomes (ILOs) listed below.
You must log in to take a sample exam.
1. Database Design (30%, 12 questions)
Candidates can:
1.1. Given a scenario, design tables for storing data
- Identify entities, rows/records, columns/fields
1.2. Given a scenario, implement the appropriate primary key
- Primary key, composite/compound key
1.3. Given a scenario, compare and contrast 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, entity-relationship diagrams (ERDs), referential integrity
1.5. Apply normalization theory to a database
- Reasons for normalization, how to normalize a database to third normal form (3NF)
1.6. Given a scenario, design appropriate data protection measures
- Backups, restore, principle of least privilege , GRANT, WITH GRANT OPTION, REVOKE, purpose of roles
2. Database Object Management using DDL (20%, 8 questions)
Candidates can:
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, compare and contrast clustered and non-clustered indexes
- When to use clustered vs. non-clustered indexes, syntax for creating indexes
3. Data Retrieval (20%, 8 questions)
Candidates can:
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 (15%, 6 questions)
Candidates can:
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 (15%, 6 questions)
Candidates can:
5.1. Analyze and troubleshoot data object management query failures
- Syntax and runtime errors
5.2. Analyze and troubleshoot data retrieval query failures
- Syntax and runtime errors
5.3. Analyze and troubleshoot data manipulation query failures
- Syntax and runtime errors
The syntax used in the exam is ANSI SQL.
You will need to use a special browser that uses camera proctoring while you take the exam. The exam consists of forty (40) multiple choice questions. Each question has three (3) answer options, exactly one (1) of which is correct. You should answer all questions, since there is no penalty for questions answered incorrectly, but you will not get any points for questions left unanswered. To pass, you must answer at least 27 questions correctly. You will have 60 minutes to take the exam.
Ordering the exam entitles you to sit for one (1) exam attempt.
This is one of the four exams you must pass, in addition to writing a Reflection Report, to earn the CFSD® diploma.
