Week 3 Complete Data Analytics

Question 1

Consider the following SQL query:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

What does this query do?

• It calculates the average salary for each department and filters departments with an average salary greater than 50000.

• It calculates the total salary for each department and filters departments with a total salary greater than 50000.

• It calculates the average salary for all employees and filters employees with a salary greater than 50000.

• It calculates the total salary for all employees and filters employees with a salary greater than 50000.

Question 2

Which of the following statements about the SQL WHERE clause is true?

• The WHERE clause is used for sorting the result set.

• The WHERE clause is used for grouping rows based on a specified condition.

• The WHERE clause is used for filtering rows based on a specified condition.

• The WHERE clause is used for performing aggregate functions

Question 3

Consider the following SQL query:

SELECT *
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' OR (total_amount > 1000 AND customer_id = 1);

What does this query do?

• Retrieves all orders placed between January 1, 2023, and January 31, 2023.

• Retrieves orders with a total amount greater than 1000 and customer_id equal to 1.

• Retrieves orders placed between January 1, 2023, and January 31, 2023, or orders with a total amount greater than 1000 and customer_id equal to 1.

• Retrieves orders placed between January 1, 2023, and January 31, 2023, and orders with a total amount greater than 1000 and customer_id equal to 1.

Question 4

What is the purpose of a window function in SQL?

• To group rows based on a specified condition.

• To calculate aggregate values over a specified range of rows related to the current row.

• To join two or more tables.

• To filter rows based on a specified condition.

Question 5

In the context of window functions, what does the PARTITION BY clause do?

• Specifies the sorting order for the window function.

• Divides the result set into partitions to which the window function is applied separately.

• Filters rows based on a specified condition.

• Defines the window frame for the window function.

Question 6

In a window function, what does the ORDER BY clause within the OVER() clause determine?

• The sorting order of the entire result set.

• The sorting order of the rows within each partition.

• The filtering conditions for the window function.

• The grouping conditions for the window function.

Question 7

Consider the following SQL query:

SELECT customers.customer_id, customers.customer_name, COUNT(orders.order_id) AS order_count
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, customers.customer_name
HAVING COUNT(orders.order_id) > 5;

What does this query do?

• Retrieves customers with more than 5 orders.

• Retrieves customers and the count of their orders, including those with no orders.

• Retrieves customers with exactly 5 orders.

• Retrieves customers and the count of their orders, excluding those with no orders.

Question 8

What is the purpose of the SQL HAVING clause?

• To filter rows before grouping

• To filter groups after grouping

• To filter columns in the SELECT statement

• To filter NULL values in a column

Question 9

Which window function is used to assign a unique rank to each row within a partition?

• ROW_NUMBER()

• RANK()

• DENSE_RANK()

• COUNT()

Question 10

What is the purpose of the EXISTS keyword in a subquery?

• To filter rows based on a specified condition.

• To check if a column exists in a table.

• To check if any rows exist in the result set of a subquery.

• To perform a self-join.