39 questions · Data Querying And Analysis · All types, answers revealed
Which SQL function is used to convert a NULL value in a query result into a default value, such as 0?
COALESCE is the standard ANSI SQL function for handling NULLs.
Why this answer
COALESCE is a standard function used to return the first non-null value in a list, often used to replace NULLs.
Which of the following is a limitation of the 'Star Schema' design compared to a fully normalized schema?
Denormalization in dimensions introduces redundancy.
Why this answer
Star schemas increase redundancy by duplicating attributes in dimension tables, which can lead to larger storage footprints.
Which TWO of the following actions are considered best practices for maintaining data integrity in a data pipeline?
Ensures data matches expected structure.
Why this answer
Implementing automated schema validation and logging data quality metrics are critical for identifying and fixing issues early.
Which THREE of the following represent common data transformation tasks in a preparation workflow?
Fixing errors or missing values.
Why this answer
Data cleaning, data filtering, and column renaming are standard tasks to ready data for analysis.
Which TWO of the following are benefits of using a data warehouse?
Designed for heavy analytical loads.
Why this answer
Data warehouses provide a centralized source of truth and are optimized for complex query performance.
When performing sentiment analysis on customer feedback, which preprocessing step is most critical to ensure words like 'Running' and 'run' are treated as the same base concept?
Lemmatization maps variations of a word to a root lemma.
Why this answer
Lemmatization or stemming reduces words to their root or base form.
Which THREE of the following are common data types in relational databases?
Stores true/false values.
Why this answer
Integer, Varchar, and Boolean are foundational data types in most database systems.
Which THREE of the following are examples of descriptive statistics?
Measures variability.
Why this answer
Mean, median, and standard deviation describe the central tendency and spread of a dataset.
A report needs to identify the 'top 10' customers by total spend. In SQL, which clause would you use to return only the first 10 results after sorting by spend descending?
LIMIT is commonly used to restrict the number of rows returned.
Why this answer
The LIMIT clause (or TOP/FETCH FIRST depending on dialect) restricts the result set to the specified number of rows.
An analyst is using a CTE (Common Table Expression) to pre-aggregate sales data before performing a join. What is the primary benefit of using a CTE instead of a subquery in this scenario?
CTEs provide logical structure and allow for recursive self-referencing queries.
Why this answer
CTEs improve readability and maintainability of complex queries by allowing the logic to be defined in a named temporary result set.
Which THREE of the following are types of data joins in SQL?
Standard intersection join.
Why this answer
INNER, LEFT, and RIGHT joins are all standard SQL join types.
When working with time-series data, what does a 'lag' feature represent in an analysis?
Lags are used to compare current values with past values.
Why this answer
A lag feature represents the value of a variable at a previous time step, which is useful for trend analysis.
Which TWO of the following are legitimate ways to optimize SQL query performance?
Indexes speed up data retrieval.
Why this answer
Creating appropriate indexes and avoiding redundant subqueries significantly improve query efficiency.
In Power BI, which visualization component is best suited to show the relationship between two numerical variables and identify potential trends or clusters?
Scatter plots effectively reveal relationships between two numerical dimensions.
Why this answer
A Scatter Plot is specifically designed to plot two numerical variables to show correlations.
When creating an ETL pipeline, which stage involves validating that the data meets business rules (e.g., age must be positive)?
Transformation is where data is cleaned and validated.
Why this answer
Data validation or quality checks occur during the transformation phase to ensure data integrity.
Which THREE of the following SQL aggregate functions are commonly supported in most relational databases?
Calculates the total.
Why this answer
SUM, COUNT, and AVG are fundamental aggregate functions supported across virtually all SQL standards.
Which dashboarding practice helps prevent users from being overwhelmed by too much data at once?
Interactivity allows users to control the volume of data displayed.
Why this answer
Progressive disclosure or the use of filters allows users to drill down into details only as needed.
Which SQL command is used to add new records to an existing table?
INSERT adds new records.
Why this answer
INSERT INTO is the standard command for adding new rows to a database table.
You are performing data transformation in Python using Pandas. You have a 'DataFrame' with missing values in the 'Revenue' column. Which method should be used to replace these missing values with the column mean?
fillna() allows replacement of missing values with a specified statistic.
Why this answer
The fillna() method is the standard way to replace NaN values in a Pandas DataFrame.
When cleaning a dataset, you notice that column names contain inconsistent casing (e.g., 'Name', 'NAME', 'name'). Which transformation step should be prioritized to facilitate join operations?
Normalizing casing ensures consistency for joins.
Why this answer
Standardizing string casing (normalization) is a fundamental prerequisite for accurate merging and joining.
When using Python's 'Matplotlib' library, which function is used to add a new figure to the canvas?
This creates a new figure container.
Why this answer
The plt.figure() function initializes a new figure, allowing multiple plots to be managed.
Which of the following is a 'categorical' variable?
Categories are discrete groups.
Why this answer
A categorical variable represents qualitative information, such as department, color, or gender.
In the context of data quality, what does 'Completeness' refer to?
Completeness confirms all necessary fields are populated.
Why this answer
Completeness measures whether all required data is present in the dataset without missing values.
A data analyst is writing a SQL query to retrieve unique department names from the 'Employees' table. Which keyword should be utilized to ensure the results do not contain duplicate entries?
DISTINCT effectively filters out duplicate rows in a result set.
Why this answer
The DISTINCT keyword is used to return only unique values from a specific column.
When troubleshooting a performance bottleneck in a visualization, which action is most effective if the data source is a large SQL view?
Materialization pre-calculates the results and allows for indexing.
Why this answer
Materializing the view (creating a physical table with indexes) or optimizing the view's underlying query is the most effective way to resolve performance issues.
An analyst notices that a database query is performing full table scans even though a WHERE clause is present on a indexed column. What is a likely reason for this?
Non-SARGable queries (those using functions on the left side of the operator) force index misses.
Why this answer
Applying a function (like UPPER() or YEAR()) to an indexed column in the WHERE clause prevents the database from using the index effectively.
When normalizing a database, what is the primary goal of moving data into a third normal form (3NF)?
3NF focuses on eliminating transitive dependencies.
Why this answer
3NF removes transitive dependencies to ensure that every non-key attribute depends only on the primary key.
Which TWO of the following are commonly used methods for handling outliers during data analysis?
Removing extreme values is a valid, though sometimes risky, strategy.
Why this answer
Capping (winsorizing) and imputation are standard techniques to manage extreme values that might skew analytical results.
You are debugging a SQL query that uses an aggregate function. You receive an error stating that a column in your SELECT list is not part of an aggregate or a GROUP BY clause. What is the cause?
Columns in SELECT that are not aggregated must be grouped.
Why this answer
Standard SQL requires every non-aggregated column in the SELECT clause to be included in the GROUP BY clause to ensure deterministic results.
Which THREE of the following are components of a standard SQL SELECT statement?
Filters rows.
Why this answer
The FROM, WHERE, and GROUP BY clauses are fundamental components used to define the source, filter criteria, and aggregation groups.
What is the benefit of using a 'Dashboard' over a static report?
Interactivity is the defining feature of dashboards.
Why this answer
Dashboards offer real-time or near-real-time interactivity, allowing users to explore data dynamically.
In data visualization, what is the 'Data-Ink Ratio' intended to improve?
It focuses on reducing chart junk to clarify data trends.
Why this answer
The Data-Ink Ratio, a concept by Edward Tufte, aims to maximize the information-carrying ink while minimizing non-essential decorations.
When analyzing JSON data stored in a relational database using SQL, how do you typically access nested key-value pairs?
JSON path accessors are required to traverse hierarchies.
Why this answer
Modern SQL engines provide specific operators or functions (like the '->>' operator in PostgreSQL) to navigate JSON paths.
When joining two tables in SQL where you need to return all records from the left table even if there is no match in the right table, which JOIN type is appropriate?
LEFT JOIN preserves all data from the primary table.
Why this answer
A LEFT JOIN returns all rows from the left table and the matched rows from the right table, filling with NULL if no match exists.
Which TWO of the following are common types of data visualizations used to represent trends over time?
Line charts are the standard for temporal trends.
Why this answer
Line charts and area charts are the most effective visual representations for showing changes over continuous time intervals.
Which TWO of the following are common issues encountered when integrating data from disparate sources?
Example: date formats differing between systems.
Why this answer
Inconsistent data formats and conflicting identifiers are the most common barriers to effective data integration.
Which of the following is an example of an unstructured data source that a data analyst might need to ingest?
Textual content in emails is unstructured.
Why this answer
Emails are considered unstructured because they contain free-form text that does not follow a strict relational schema.
A database administrator needs to ensure that a query calculating running totals is performant. Which window function clause is essential to order the calculation sequence?
ORDER BY is required to define the sequential nature of a running total.
Why this answer
The ORDER BY clause inside the OVER() function determines the sequence for running calculations.
What does the 'P' in the acronym 'KPI' stand for?
Correct terminology.
Why this answer
KPI stands for Key Performance Indicator.
Ready to test yourself?
Try a timed practice session using only Data Querying And Analysis questions.