Data Analytics Data Wrangling and SQL Syllabus

Data Wrangling and SQL syllabus — full chapter and topic list

Expand any chapter to see its topics and sub-topics. This is the whole examinable outline for Data Wrangling and SQL in Data Analytics, not a summary of it.

1. 1

   Spreadsheets for Analysis

   4 topics

   • Formulas and Functions
   • Pivot Tables
   • Conditional Formatting
   • Charts in Spreadsheets
2. 2

   Relational Databases

   4 topics

   • Tables, Keys and Relationships
   • Database Schemas and Normalization
   • Primary and Foreign Keys
   • Entity-Relationship Modeling
3. 3

   SQL Fundamentals

   4 topics

   • SELECT and FROM
   • WHERE Filtering
   • ORDER BY and LIMIT
   • DISTINCT and Aliases
4. 4

   SQL Aggregation and Joins

   4 topics

   • GROUP BY and HAVING
   • Aggregate Functions
   • Joins
   • Unions
5. 5

   Advanced SQL

   5 topics

   • Subqueries
   • Common Table Expressions (CTEs)
   • Window Functions
   • CASE Statements
   • Query Optimization and Indexing
6. 6

   Data Cleaning and Preparation

   6 topics

   • Handling Missing Values
   • Removing Duplicates
   • Data Type Conversion
   • Outlier Detection and Treatment
   • Data Transformation and Standardization
   • Data Validation and Integrity

Data Wrangling and SQL flashcards for Data Analytics

24 of 51 cards from the Data Wrangling and SQL deck — real questions with worked answers.

1. In spreadsheets, what is the difference between a relative reference (A1) and an absolute reference ($A$1) in a formula?

   A relative reference (A1) shifts its row/column when the formula is copied to another cell. An absolute reference ($A$1) is locked and stays fixed when copied. Mixed forms like $A1 or A$1 lock only the column or only the row.

2. What does the spreadsheet function VLOOKUP do, and what is its basic argument order?

   VLOOKUP searches for a value in the leftmost column of a range and returns a value from a specified column in the same row. Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Use FALSE (exact match) for range_lookup in most cases.

3. When would you use INDEX/MATCH instead of VLOOKUP in a spreadsheet?

   Use INDEX/MATCH when the lookup column is to the right of the return column (VLOOKUP can only look right), when you need faster performance on large sheets, or when column insertions would break a hard-coded VLOOKUP index. MATCH finds the position and INDEX returns the value at that position.

4. What is the difference between the spreadsheet functions COUNT, COUNTA, and COUNTIF?

   COUNT counts only numeric cells. COUNTA counts all non-empty cells (numbers, text, etc.). COUNTIF counts cells in a range that meet a single specified condition, e.g. COUNTIF(A1:A10, ">5").

5. What does the spreadsheet IF function do, and give its syntax.

   IF returns one value when a logical test is TRUE and another when it is FALSE. Syntax: IF(logical_test, value_if_true, value_if_false). It can be nested or combined with AND/OR for multiple conditions.

6. What is a Pivot Table used for in a spreadsheet?

   A Pivot Table is an interactive tool that summarizes, groups, and aggregates large datasets without formulas. It lets you drag fields into Rows, Columns, Values, and Filters areas to quickly compute totals, counts, averages, and cross-tabulations.

7. In a Pivot Table, what is the role of each of the four areas: Rows, Columns, Values, and Filters?

   Rows: field values listed vertically as row labels. Columns: field values spread horizontally as column headers. Values: the numeric field being aggregated (sum, count, average, etc.). Filters: field that restricts which records are included in the whole table.

8. What is Conditional Formatting in a spreadsheet?

   Conditional Formatting automatically applies visual styling (colors, data bars, color scales, icon sets) to cells based on rules or their values, so patterns, outliers, and thresholds become visible without manual formatting.

9. Name three common types of conditional formatting rules used to visualize data.

   (1) Highlight/cell rules (e.g. greater than a value, duplicate values). (2) Color scales (a gradient mapping value magnitude to color). (3) Data bars or icon sets that show relative magnitude within each cell.

10. Which spreadsheet chart type is best for showing composition (parts of a whole), and which is best for showing trends over time?

    A pie chart (or 100% stacked bar) is best for composition/parts of a whole. A line chart is best for showing trends over continuous time. Bar/column charts are best for comparing discrete categories.

11. In relational database terms, what is a table, and what do its rows and columns represent?

    A table (relation) is a structured set of data about one entity type. Each row (record/tuple) is one instance of that entity, and each column (field/attribute) holds one property with a defined data type.

12. What is a database schema?

    A database schema is the formal blueprint that defines the structure of a database: its tables, columns and data types, keys, relationships, constraints, and how the tables connect. It describes the logical organization, not the data itself.

13. What is database normalization and why is it done?

    Normalization is the process of organizing tables and columns to reduce data redundancy and improve integrity by decomposing tables and defining relationships. It prevents insertion, update, and deletion anomalies by ensuring each fact is stored in exactly one place.

14. State the requirement of First Normal Form (1NF).

    A table is in 1NF if every column holds atomic (indivisible) values, there are no repeating groups or multi-valued cells, and each row is unique (typically identified by a primary key).

15. State the requirement of Second Normal Form (2NF).

    A table is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the entire primary key, not just part of it. This removes partial dependencies (relevant when there is a composite key).

16. State the requirement of Third Normal Form (3NF).

    A table is in 3NF if it is in 2NF and has no transitive dependencies, i.e. no non-key attribute depends on another non-key attribute. Every non-key attribute must depend only on the primary key.

17. What is a primary key in a relational table?

    A primary key is a column (or set of columns) that uniquely identifies each row in a table. It must be unique and cannot contain NULL values. Each table can have at most one primary key.

18. What is a foreign key, and what does it enforce?

    A foreign key is a column (or set of columns) in one table that references the primary key of another table. It enforces referential integrity, ensuring that a value in the child table must correspond to an existing row in the parent table.

19. What is the difference between a composite key and a candidate key?

    A candidate key is any column or set of columns that could uniquely identify a row (one is chosen as the primary key). A composite key is a key made of two or more columns combined to achieve uniqueness.

20. In Entity-Relationship (ER) modeling, what do entities, attributes, and relationships represent?

    An entity is a real-world object or concept about which data is stored (becomes a table). An attribute is a property of an entity (becomes a column). A relationship is an association between entities (implemented via keys).

21. List the three main types of cardinality in an ER model.

    One-to-one (1:1): each row in A relates to at most one row in B. One-to-many (1:N): one row in A relates to many rows in B. Many-to-many (M:N): rows in A relate to many in B and vice versa, usually resolved with a junction/bridge table.

22. How is a many-to-many relationship implemented in a relational database?

    It is implemented with a junction (bridge/associative) table that holds foreign keys pointing to the primary keys of both related tables. Each row represents one pairing, turning the M:N relationship into two 1:N relationships.

23. What do the SELECT and FROM clauses do in a SQL query?

    SELECT specifies which columns (or expressions) to return in the result set. FROM specifies the table (or tables) from which to retrieve the rows. SELECT * returns all columns.

24. What does the WHERE clause do in SQL, and give an example.

    WHERE filters rows, returning only those that satisfy a condition, before any grouping. Example: SELECT name FROM customers WHERE country = 'US' AND age >= 18.

See more Data Wrangling and SQL flashcards →

Planning Data Wrangling and SQL for Data Analytics

Data Wrangling and SQL (Data Analytics) FAQ