Our Relational Databases and SQL course and exam are based on the following Intended Learning Outcomes (ILOs):
Database Design
Candidates can:
Given a scenario, design tables for storing data
- Identify entities, rows/records, columns/fields
Given a scenario, identify the appropriate primary key
- Primary key, composite/compound key
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
Given a scenario, design relationships between tables
- How to establish relationships using primary and foreign keys, entity-relationship diagrams (ERDs), referential integrity
Normalize a database
- Reasons for normalization, how to normalize a database to third normal form (3NF)
Given a scenario, identify data protection measures
- Backups, restore, principle of least privilege , GRANT, WITH GRANT OPTION, REVOKE, purpose of roles
Database Object Management using DDL
Candidates can:
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
Construct and analyze queries that create, alter, and drop views
- Create, alter, and drop views by using proper ANSI SQL syntax; purpose of views
Construct and analyze stored procedures and functions
- Input and output parameters, return values, purpose of stored procedures
Given a scenario, choose between clustered and non-clustered indexes
- When to use clustered vs. non-clustered indexes, syntax for creating indexes
Data Retrieval
Candidates can:
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
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
Construct and analyze queries that aggregate data
- GROUP BY, HAVING, MIN, MAX, COUNT, AVG (AVERAGE), SUM
Data Manipulation using DML
Candidates can:
Construct and analyze INSERT statements
- INSERT INTO SELECT, INSERT INTO VALUES
Construct and analyze UPDATE statements
- Update data in a single table
Construct and analyze DELETE statements
- Delete data from a single table
Troubleshooting
Candidates can:
Troubleshoot data object management query failures
- Syntax and runtime errors
Troubleshoot data retrieval query failures
- Syntax and runtime errors
Troubleshoot data manipulation query failures
- Syntax and runtime errors
The syntax used in the exam is ANSI SQL.
This is one of the four exams you need to pass (or for which you must obtain a waiver) to earn the stackable CFSD® certificate.