Querying and Filtering Data

What is often the first task a data analyst performs when working with data?

Querying the data for the required information

What tools might a data analyst use to query data?

SQL or graphical data tools like SQL Server Management Studio, Tableau

Why might a report with only unique identifiers, like product IDs, be difficult to understand?

It lacks detailed information about the products making it less informative

In the example of a sales database, why would a report with just product IDs be insufficient?

It lacks the full product name which is necessary for understanding sales

What does a data analyst need to do to make a report with product IDs more useful?

Join the product ID with a separate product table to pull the product name

What is an example of a real-world application of joining tables?

Joining payments and customer tables to get customer names for a report

What is the purpose of using a join command in a query?

To merge data from multiple tables into one unified report.

What is one key challenge when creating queries?

Making the output human-readable and understandable  

How are payments and customers tables related?

The customer number in payments and customer table can be linked

Why might there be complexity in performing a join in SQL?

There are various ways to construct joins and manage table relationships

Which type of join is the most commonly used in data analysis?

Inner join

What does an inner join return?

Only the rows where a matching field exists between two tables

In the left outer join, which data is included in the result set?

All data from the left table, and matching data from the right table

What is the difference between a left outer join and a right outer join?

Includes all left data; includes all right data

What does a full outer join return?

All rows from both tables, whether they match or not

What is the result of performing a cross join between two tables?

The cartesian product of both tables combining each record from both tables

What is a potential risk of performing a cross join?

It can result in a very large number of rows in the output

When is a full outer join most useful?

When you need to return all data from both tables, regardless of matches

What does a left outer join return when there is no matching data in the right table?

Only rows from left table are returned with nulls for missing right data

Which of the following is true about using inner joins in SQL queries?

Inner joins return only where a specific field matches in both tables

What is the primary purpose of filtering data in SQL?

To restrict the query to a subset of the source data

Which SQL clause is commonly used to filter data?

WHERE

Why is filtering data important for query performance optimization?

It reduces the data set early, improving subsequent query efficiency

What is an example of using a WHERE clause to filter data?

ALL OF THE ABOVE 

What does filtering data early in a query help with in large data warehouses?

It reduces the resources required and speeds up query execution

What does the WHERE clause WHERE state = ‘CA’ do?

Filter data to show customers from California only

When constructing a query, what might be the first step to improve performance?

Filtering the data to include only the necessary records

Which of the following is NOT a benefit of filtering data in SQL? 

Increases the amount of data retrieved

What is the main purpose of parameterization in SQL queries?

to reuse queries with different values for filter

How do parameters work in SQL queries?

The parameter is entered externally and inserted into the query

In which scenario would parameterization be most useful?

When executing the same query with different filter values

How is a parameter typically defined in a SQL query?

It is set using a variable, such as set @state = ‘MA’

What tool allows you to use parameters in a graphical interface?

Tableau

How does parameterization make it easier to generate different reports?

It allows the user to enter different filter values to get different result

What is the benefit of using parameterization in SQL queries?

It simplifies executing the same query with different parameters

How would you modify a query to filter by a different state in a parameterized query?

Change the value o the parameter (e.g. set @state = ‘New State’)

What is one advantage of using parameterization when building applications that use SQL queries?

It allows the user to easily input different filter values

What is the main purpose of an index in a database table?

To speed up queries on a given column

When would you create an index on a column in a database?

When the column is frequently used in filtering or querying

What happens if you do not use an index on a frequently queried column?

The database will have to scan through every record to find matches

Which of the following is indexed automatically by the database?

Primary keys

What is one potential downside of using indexes in a database?

They increase the time and space required to add or update records.

What is an example of when an explicit index might be created in a table?

To speed up queries filtering on a field like sales rep employee number

Which of the following is NOT a cost of using indexes in a database?

They can slow down queries on columns that are indexed

Why would you create an index on a column that is not a key in the table?

To speed up queries that filter on that column

What happens when an index is created on a column that is frequently updated?

The index must be updated manually whenever a record is added or updated

What is a trade-off when deciding to create an index on a column?

Increased query speed but at the cost of additional storage and maintenance.

What is the primary purpose of filtering data before executing complex queries?

To reduce the amount of data being processed, improving the query performance.

What happens to a temporary table when you disconnect from the database?

It is deleted as it only exists within the session.

How do you create a temporary table in SQL?

CREATE TEMPORARY TABLE table_name AS SELECT…

Which of the following is an advantage of using temporary tables?

Allows queries to be executed on smaller subset of data improving efficiency.

Why might breaking down complex queries into multiple temporary tables be beneficial.

It makes queries easier to understand and debug.

What is another term for a nested query?

Subquery

What is the primary purpose of a subquery?

To simplify complex queries by operating on a subset of data.

How should you typically approach understanding a nested query?

From the inner query to the outer query.

What does the inner query “SELECT AVG(buyPrice) FROM products” calculate?

The average buy price.

What is the goal of example query “SELECT * FROM products WHERE buyPrice > (SELECT AVG(buyPrice) FROM products)”?

To select all products with a buy price greater than the average.

Which of the following is considered as a challenge with complex subqueries?

Optimizing performance and understanding execution efficiency.

What is the main difference between using a subquery and a temporary table to achieve a similar result?

Nest queries within a single command; break the operation into multiple steps.

What is the primary purpose of a Query Execution Plan?

To visualize the order in which a query is executed.

What is another common name for a Query Execution Plan?

Explain Plan

What SQL command is often used to generate a Query Execution Plan

EXPLAIN

What potential performance issue in a query execution plan?

Multiple full table scans

What is a potential strategy to improve query performance?

Executing filtering operations earlier in the query.

Who is typically best suited to optimize complex database queries?

Database Specialists