Exam Syllabus Contents

• Section 1. Database Fundamentals (15%) (8)
• Section 2. Core SQL Commands (35%) (10)
• Section 3. Querying and Organizing Data (30%) (13)
• Section 4. Reliability, Troubleshooting, and Professional Practices (20%) (9)

Section 1. Database Fundamentals (15%) (8)

8 objectives covered by the section → 8 exam items

1.1 Understanding Data (1)

Objective 1.1.1 Define and differentiate between structured and unstructured data (1)

1. Identify the characteristics of structured data.
2. Identify the characteristics of unstructured data.
3. Distinguish semi-structured data as a middle category.
4. Classify datasets as structured, unstructured, and semi-structured data.

1.2 Types of Databases (3)

Objective 1.2.1 Describe relational databases (1)

1. Explain the concept of tables, rows, and columns.
2. Recognize the advantages of relational databases.
3. Define the role of primary and foreign keys in maintaining relationships.

Objective 1.2.2 Describe non-relational (NoSQL) databases (1)

1. Identify the common models: key-value store, document store, column store, graph database.
2. Explain the flexible schema approach.
3. Recognize the advantages of NoSQL databases.

Objective 1.2.3 Compare SQL vs NoSQL and explain when each might be used (1)

1. Match SQL and NoSQL databases to common use cases.
2. Recognize the key trade-offs between SQL and NoSQL databases.

1.3 Database Engines (1)

Objective 1.3.1 Identify common relational database engines and distinguish their key characteristics (1)

1. Identify key differences among MySQL, MariaDB, PostgreSQL, and SQLite in terms of features, standards compliance, scalability, and ecosystem.
2. Demonstrate awareness that engines may exist in different versions, and that newer versions often include enhancements, bug fixes, or additional features.

1.4 Concept of Query Language (3)

Objective 1.4.1 Describe the concept of communicating with a database (1)

1. Describe the request-response model
2. Explain how database engine processes queries

Objective 1.4.2 Explain what SQL is (1)

1. Describe how a declarative language allows the user to specify the desired outcome without prescribing the steps to achieve it.
2. Explain how SQL hides the underlying algorithms from the programmer.

Objective 1.4.3 Identify the properties that result from SQL being declarative language (1)

1. Identify the advantages of high-level code readability and maintainability.
2. Identify the advantages of physical data independence.
3. Identify the ability of the database engine to adapt and find the fastest execution path.

Section 2. Core SQL Commands (35%) (10)

2.1 SQL Categories (1)

Objective 2.1.1 Identify categories of SQL statements.

1. DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.
2. DML (Data Manipulation Language): INSERT, UPDATE, DELETE.
3. DQL (Data Query Language): SELECT.
4. DCL (Data Control Language): GRANT, REVOKE.
5. TCL (Transaction Control Language): COMMIT, ROLLBACK.

2.2 Table Creation and Data Types (3)

Objective 2.2.1 Demonstrate how to create a database and a table using CREATE DATABASE and CREATE TABLE.

1. Write a valid CREATE DATABASE statement with correct syntax.
2. Select and use the created database.
3. Write a valid CREATE TABLE statement with correct syntax.
4. Define column names and data types in the statement.
5. Include a primary key column with a defined value-generation strategy.
6. Include a foreign key to establish a relationship between two tables.
7. Explain constraints such as NOT NULL, DEFAULT, UNIQUE, and CHECK.

Objective 2.2.2 Identify and apply common SQL data types (VARCHAR, CHAR, TEXT, INT, DATE, BOOLEAN).

1. Differentiate between character data types.
2. Recognize numeric, date, and boolean types.
3. Choose appropriate types for example data.

Objective 2.2.3 Modify table schemas using ALTER TABLE.

1. Add a new column.
2. Drop an existing column.
3. Rename a column.
4. Add a new constraint.
5. Remove an existing constraint.

2.3 Basic Data Retrieval and Manipulation (4)

Objective 2.3.1 Use SELECT to retrieve specific columns and rows with WHERE.

1. Write SELECT * queries.
2. Select specific columns.
3. Filter with WHERE.
4. Use operators: =, <, >, AND, OR, IN, NOT IN.
5. Use BETWEEN to filter ranges.
6. Handle null values using IS NULL and IS NOT NULL.
7. Apply DISTINCT to get unique values.

Objective 2.3.2 Use INSERT INTO to add new data records.

1. Insert values into all columns.
2. Insert values into selected columns.
3. Insert multiple rows.

Objective 2.3.3 Use UPDATE to modify data, with appropriate caution.

1. Update values in one or more rows.
2. Use WHERE to target specific records.
3. Recognize the risk of omitting WHERE.

Objective 2.3.4 Use DELETE to remove data, with appropriate caution.

1. Delete one or more rows from a table.
2. Use WHERE to control deletion.
3. Recognize the risk of deleting all rows without a condition.

2.4 Additional SQL Clauses (2)

Objective 2.4.1 Restrict a number of returned rows.

1. Identify the row limiting clauses for the most popular database engines.
2. Use LIMIT or TOP to control the number of rows returned.

Objective 2.4.2 Use LIKE for simple pattern matching in queries.

1. Use % wildcard for partial matches.
2. Use _ wildcard for single-character matches.
3. Combine LIKE with WHERE for flexible filters.

Section 3. Querying and Organizing Data (30%) (13)

3.1 Aggregations and Grouping (2)

Objective 3.1.1 Use aggregate functions (COUNT, SUM, AVG, MIN, MAX) to summarize data.

1. Apply COUNT to return the number of rows.
2. Use SUM to calculate totals for numeric fields.
3. Use AVG to calculate averages.
4. Use MIN and MAX to find smallest and largest values.
5. Combine aggregates with WHERE to filter before aggregation.

Objective 3.1.2 Apply GROUP BY to group data with aggregate functions.

1. Write a query grouping rows by a column.
2. Use aggregate functions within groups.
3. Distinguish between HERE and HAVING.
4. Recognize when grouping is useful in real-world examples.

3.2 Sorting and Organizing (2)

Objective 3.2.1 Apply ORDER BY to sort query results.

1. Sort results in ascending order (default).
2. Sort results in descending order using DESC.
3. Sort by one or multiple columns.
4. Recognize performance considerations for sorting.

Objective 3.2.2 Combine ORDER BY with GROUP BY queries.

1. Sort grouped results by aggregated values.
2. Write queries grouping by one column and ordering by another.
3. Recognize that ordering grouped results helps with meaningful reporting.

3.3 Table Joins (6)

Objective 3.3.1 Explain the purpose of joins.

1. Understand that joins combine data from multiple tables.
2. Recognize how primary and foreign keys enable joins.
3. Identify scenarios requiring joins.

Objective 3.3.2 Demonstrate use of INNER JOIN.

1. Write a query joining two tables using a matching column.
2. Interpret results where only matching rows are returned.

Objective 3.3.3 Demonstrate use of LEFT JOIN.

1. Write a query returning all rows from the left table and matched rows from the right table.
2. Interpret results where unmatched rows contain NULL values.

Objective 3.3.4 Demonstrate use of RIGHT JOIN.

1. Write a query returning all rows from the right table and matched rows from the left table.
2. Interpret results where unmatched rows contain NULL values.

Objective 3.3.5 Recognize the behavior of FULL OUTER JOIN.

1. Recognize that a FULL OUTER JOIN returns all rows from both tables.
2. Identify that unmatched rows from either table contain NULL values.

Objective 3.3.6 Recognize the behavior of CROSS JOIN and self joins

1. Recognize that a CROSS JOIN returns the Cartesian product of two tables.
2. Recognize that a self join joins a table to itself using aliases.
3. Identify simple real-world cases for self joins.

3.4 Subqueries (3)

Objective 3.4.1 Explain the concept of layered data processing.

1. Explain why complex problems require massive queries.
2. Explain how and why the result of one query can serve as input for another query.
3. Describe how to break the query logic down by nesting or chaining queries together.

Objective 3.4.2 Describe the main properties of subqueries.

1. Describe the scope and purpose of the subqueries.
2. Explain how subqueries are embedded in a main SQL query.
3. Explain the order in which operations are performed and results are passed on.

Objective 3.4.3 Describe the main properties of CTEs.

1. Explain how a CTE is defined and its purpose.
2. Explain how to use the results of a CTE.
3. Describe the advantage of separating the data retrieval logic from the final presentation logic.

Section 4. Reliability, Troubleshooting, and Professional Practices (20%) (9)

4.1 Data Reliability Concepts (1)

Objective 4.1.1 Explain the purpose of normalization.

1. Define normalization as the process of organizing data to reduce redundancy.
2. Recognize problems caused by unnormalized data (duplication, inconsistency).
3. Identify the basic principles of 1NF, 2NF, and 3NF.
4. Recognize real-world scenarios where normalization improves data quality.

4.2 Transactions and Consistency (2)

Objective 4.2.1 Define a transaction and use its basic commands.

1. Explain a transaction as a sequence of database operations that function as a single unit.
2. Explain why opening a transaction may block queries from other users.
3. Recognize why transactions help maintain data accuracy.
4. Begin a transaction.
5. Apply COMMIT to save changes.
6. Apply ROLLBACK to undo changes.

Objective 4.2.2 Define and explain ACID properties.

1. Atomicity: all or nothing execution.
2. Consistency: maintaining valid data.
3. Isolation: transactions don’t interfere with each other.
4. Durability: changes persist after a commit.
5. Recognize why ACID ensures reliability in databases.

4.3 Performance Basics (2)

Objective 4.3.1 Explain what an index is.

1. Define indexes as structures that speed up data retrieval.
2. Recognize that indexes are like a “book index” for fast lookup.
3. Identify that indexes can be created as unique indexes (UNIQUE INDEX) to ensure that no duplicate values exist in a column.

Objective 4.3.2 Demonstrate how indexes improve performance.

1. Compare queries with and without indexes (conceptual).
2. Recognize trade-offs: faster reads but slower writes.

4.4 Troubleshooting (1)

Objective 4.4.1 Identify common SQL errors and interpret simple SQL error messages.

1. Missing semicolons at the end of statements.
2. Misspelled keywords.
3. Quoting issues with text values.
4. Wrong column or table names.
5. Recognize error codes/messages as hints.
6. Match common error types to typical fixes.

4.5 Security Awareness (2)

Objective 4.5.1 Demonstrate basic awareness of security in databases.

1. Recognize that SQL commands can cause unintended data loss if used incorrectly.
2. Understand that database access is controlled by user roles and permissions.
3. Be aware of SQL injection as a common security risk.
4. Recognize the importance of handling personal/sensitive data responsibly.

Objective 4.5.2 Explain backup concepts and purposes

1. Identify common risks and reasons for database data loss.
2. Describe different backup types and their characteristics.
3. Explain key recovery metrics: RPO and RTO.
4. Differentiate between data backups, transaction logs, and live replication.

4.6 Communication and Collaboration (1)

Objective 4.6.1 Understand the importance of teamwork in database-related tasks.

1. Recognize that database projects often involve developers, analysts, DBAs, and testers.
2. Appreciate the need for clear communication when sharing queries, schemas, or reports.
3. Recognize that documenting queries and results helps collaboration.
4. Understand the role of teamwork in maintaining data quality and reliability.