SQL: from 0 to hero

Question 1

What is the difference between create table and create view?

Table 2: departments (dept_id, de

Table 1: employees (id, name, dept_id)

easy

Answer 1

create table: Stores data physically; faster for repeated access but uses storage.

• Use: Static data or precomputed results.
• Avoid: Dynamic data needing frequent updates.

create view: Virtual table; reflects live data but slower for complex queries.

Use: Dynamic, query-based data.
Avoid: Performance-intensive queries.

Question 2

What is the difference between INNER JOIN and OUTER JOIN?

T2: departments (dept_id, dept_name)

T1: employees (id, name, dept_id), T2: departments (dept_id, dept_name)

easy

Answer 2

INNER JOIN returns matching rows from both tables, while OUTER JOIN includes unmatched rows from one or both tables.

– INNER JOIN: Only matching rows

SELECT e.name, d.dept_name 
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

– OUTER JOIN: Includes unmatched rows

SELECT e.name, d.dept_name 
FROM employees e
LEFT OUTER JOIN departments d ON e.dept_id = d.dept_id;

Question 3

How can you **find duplicate records **in a table?

Table: employees (id, name, dept_id)

difficulty: easy

Answer 3

Use GROUP BY on columns and HAVING COUNT(*) > 1

SELECT name, COUNT(*) 
FROM employees 
GROUP BY name 
HAVING COUNT(*) > 1;

Question 4

How do window functions differ from aggregate functions?

employees (id, name, dept_id, salary)

**difficulty: ** medium

Answer 4

Window functions operate over a subset of rows defined by OVER() without collapsing them into one row, unlike aggregate functions.

-- Window Function
SELECT name, salary, SUM(salary) OVER (PARTITION BY dept_id) AS dept_salary 
FROM employees;

-- Aggregate Function
SELECT dept_id, SUM(salary) AS dept_salary 
FROM employees 
GROUP BY dept_id;

Question 5

What is a Common Table Expression (CTE), and when would you use it?

Table: sales (region, sales)

**difficulty: ** medium

Answer 5

A CTE is a temporary result set used for modular, readable queries (e.g., recursive operations).

WITH SalesCTE AS (
  SELECT region, SUM(sales) AS total_sales 
  FROM sales 
  GROUP BY region
)
SELECT region, total_sales 
FROM SalesCTE;

Question 6

How can you calculate the **cumulative sum **of a column?

sales (name, sales, sales_date)

**difficulty: ** medium

Answer 6

Use SUM(column) OVER (ORDER BY column) in a window function.

SELECT name, sales, 
       SUM(sales) OVER (ORDER BY sales_date) AS cumulative_sales 
FROM sales;

Question 7

What is the purpose of the EXPLAIN statement?

Table: employees (id, name, dept_id)

**difficulty: ** medium

Answer 7

It shows the query execution plan to optimize performance.

Question 8

How do you remove duplicate rows from a table?

employees (id, name, dept_id)

**difficulty: ** medium

Answer 8

Use DELETE with ROW_NUMBER() or DISTINCT.

DELETE FROM employees 
WHERE ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) > 1

Question 9

Write a query to get the second highest salary from an employees table.

Table: employees (id, name, salary)

**difficulty: ** medium

Answer 9

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

Question 10

What is the difference between HAVING and WHERE?

employees (id, name, dept_id, salary)

**difficulty: ** easy

Answer 10

WHERE filters rows before grouping and HAVING filters groups after aggregation.

-- WHERE
SELECT * 
FROM employees 
WHERE salary > 50000;

-- HAVING
SELECT dept_id, AVG(salary) 
FROM employees 
GROUP BY dept_id 
HAVING AVG(salary) > 60000;

Question 11

How do you handle nulls in SQL when performing calculations?

Table: employees (id, name, salary)

**difficulty: ** easy

Answer 11

Use COALESCE() or ISNULL() to replace nulls with default values.

SELECT name, COALESCE(salary, 0) AS salary 
FROM employees; -- COALESCE replaces NULL with a value

Question 12

How do you find the 90th percentile in a dataset?

Table: employees (id, name, salary)

**difficulty: ** hard

Answer 12

With PERCENTILE_CONT

SELECT PERCENTILE_CONT(0.9) 
WITHIN GROUP (ORDER BY salary) AS percentile_salary 
FROM employees;

Question 13

How do you **rank rows **in a table?

Table: employees (id, name, salary)

**difficulty: ** medium

Answer 13

SELECT name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS rank 
FROM employees;

Question 14

What is the difference between TRUNCATE and DELETE?

Table: employees (id, name, dept_id)

**difficulty: ** easy

Answer 14

TRUNCATE TABLE employees; -- Deletes all rows

DELETE FROM employees WHERE dept_id = 101; -- Deletes specific rows

Table: employees (id, name, dept_id)

Question 15

What is a primary key, and why is it important? Create a table with a primary key.

Table: employees (id, name)

**difficulty: ** easy

Answer 15

The primary key is a column in a relational database table that’s distinctive for each record. Primary keys must contain UNIQUE values, and cannot contain NULL values.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50)
)

Learn more about primary keys.

Question 16

How do you pivot data in SQL?

Table: employees (id, dept_id, gender)

**difficulty: ** medium

Answer 16

SELECT dept_id,
       SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
       SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees 
GROUP BY dept_id

Question 17

Write a query to calculate the percentage of total sales per region?

Table: sales (region, sales)

**difficulty: ** hard

Answer 17

SELECT region, 
       SUM(sales) / SUM(SUM(sales)) OVER () * 100 AS percentage 
FROM sales 
GROUP BY region;

Question 18

What is the purpose of normalization in SQL?

employees (id, name, dept_id)

Tables: departments (dept_id, dept_name),

**difficulty: ** easy

Answer 18

To reduce redundancy and dependency by organizing data into logical tables.

CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(50));
CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(50), dept_id INT);

Question 19

How do you get the total row count of a table or grouped result set?

Table: employees (id, name, dept_id)

**difficulty: ** easy

Answer 19

Use COUNT(*) for the table or COUNT(column) for grouped subsets.

-- Total row count of the table
SELECT COUNT(*) AS total_rows 
FROM employees;

-- Row count grouped by department
SELECT dept_id, COUNT(*) AS dept_row_count 
FROM employees 
GROUP BY dept_id;

Question 20

What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

Table: employees (id, name, salary)

**difficulty: ** medium

Answer 20

• RANK() assigns ranks with gaps for tied values.
• DENSE_RANK() assigns consecutive ranks without gaps.
• ROW_NUMBER() assigns unique row numbers regardless of ties.

SELECT name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

Question 21

How can you replace rows based on a condition while updating a table?

Table: employees (id, name, salary, dept_id)

**difficulty: ** medium

Answer 21

Use the UPDATE statement with a CASE expression in the SET clause to selectively update rows based on conditions.

UPDATE employees
SET salary = CASE 
               WHEN dept_id = 101 THEN salary * 1.1 
               ELSE salary 
               END
WHERE dept_id IN (101, 102);

Question 22

What are common SQL data types, and how are they used?

sales (id INT, product STRING, amount DOUBLE, sale_date TIMESTAMP

**difficulty: ** easy

Answer 22

• INT: Whole numbers
• STRING: Text
• DOUBLE: Decimal numbers
• TIMESTAMP: Date and time

CREATE TABLE sales (
  id INT,
  product STRING,
  amount DOUBLE,
  sale_date TIMESTAMP
);

Question 23

How do you query across multiple databases in Databricks?

Tables: sales, campaigns

Databases: sales_db, marketing_db

**difficulty: ** medium

Answer 23

-- Joining tables across databases
SELECT s.id, s.amount, c.campaign_name
FROM sales_db.sales s
JOIN marketing_db.campaigns c 
ON s.id = c.sale_id;

Question 24

What is advanced SQL functions like NTILE?

Table: sales (id, amount, sale_date)

**difficulty: ** medium

Answer 24

NTILE: Divide rows into quartiles

SELECT id, amount, 
       NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales;

Question 25

What is the advanced SQL function `LEAD`? | Table: sales (id, amount, sale_date) ## Footnote **difficulty: ** medium

Answer 25

`LEAD`: Access the next row's value ```SELECT id, amount, LEAD(amount) OVER (ORDER BY sale_date) AS next_amount FROM sales;```

Question 26

How can you use `CASE` for advanced query logic? | Table: sales (id, amount) ## Footnote **difficulty: ** easy

Answer 26

Use `CASE` to create conditional columns or filters in queries. ```SELECT id, amount, CASE WHEN amount > 100 THEN 'High' WHEN amount > 50 THEN 'Medium' ELSE 'Low' END AS sales_category FROM sales;```

Question 27

What does the `LAG()` function do in SQL? | Table: sales (id, amount, sale_date) ## Footnote **difficulty: ** medium

Answer 27

`LAG()` retrieves the value of a column from the previous row in a window. ```SELECT id, amount, sale_date, LAG(amount) OVER (ORDER BY sale_date) AS prev_sale FROM sales;```

Question 28

What does `UNBOUNDED PRECEDING` mean in window functions? | sales (id, amount, sale_date) ## Footnote **difficulty: ** hard

Answer 28

It refers to the start of the window, including all rows before the current one. To create a cumulative sum with `UNBOUNDED PRECEDING`: ```SELECT id, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales FROM sales;```

Question 29

How does `PARTITION BY` work in SQL window functions? | Table: sales (id, amount, sale_date, region) ## Footnote **difficulty: ** medium

Answer 29

`PARTITION BY` divides data into partitions to apply window functions separately to each group. ```SELECT region, id, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank FROM sales;```

Question 30

What is the difference between `RANGE` and `ROWS` in windowing? | Table: sales (id, amount, sale_date) ## Footnote **difficulty: ** hard

Answer 30

`ROWS` operates on a physical set of rows (e.g., current and previous). `RANGE` operates on a logical range of values (e.g., same amount). -- Using ROWS ```SELECT id, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS moving_sum FROM sales;``` -- Using RANGE ```SELECT id, amount, SUM(amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW) AS moving_sum FROM sales;```

Question 31

How do `FIRST_VALUE()` and `LAST_VALUE()` work in SQL? | Table: sales (id, amount, sale_date) ## Footnote **difficulty: ** medium

Answer 31

`FIRST_VALUE()` returns the first value in a window; `LAST_VALUE()` returns the last value. ```SELECT id, amount, sale_date, FIRST_VALUE(amount) OVER (ORDER BY sale_date) AS first_sale, LAST_VALUE(amount) OVER (ORDER BY sale_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_sale FROM sales;```

Question 32

How do you **calculate a moving average** in SQL using window functions? | Table: sales (id, amount, sale_date) ## Footnote **difficulty: ** hard

Answer 32

Use `AVG()` over a window frame to calculate a moving average. -- 3-day moving average ```SELECT id, amount, sale_date, AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales;```

Question 33

How can you **calculate percentiles** with SQL window functions? ## Footnote **difficulty: ** medium

Answer 33

Use `PERCENT_RANK()` or `CUME_DIST()` to calculate percentile rankings. ```SELECT id, amount, PERCENT_RANK() OVER (ORDER BY amount) AS percent_rank, CUME_DIST() OVER (ORDER BY amount) AS cumulative_dist FROM sales;```

Question 34

What is the `QUALIFY` statement in SQL, and how is it used? | sales (id, amount, sale_date) ## Footnote **difficulty: ** medium

Answer 34

The `QUALIFY` statement is used to filter rows after applying window functions, simplifying complex filtering logic. In the example below, we only keep the row with the highest rank: ```SELECT id, amount, RANK() OVER (ORDER BY amount DESC) AS rank FROM sales QUALIFY rank = 1;```

Question 36

# Exercise How would you calculate the **total revenue** for each product for **the past 7 days**? **Table Name:** `orders` - `order_id` INT - `product_id` INT - `order_date` TIMESTAMP - `status` STRING (values: 'completed', 'pending', 'cancelled') - `total_amount` DOUBLE **Table Name:** `products` - `product_id` INT - `product_name` STRING - `category` STRING - `price` DOUBLE ## Footnote **medium**

Answer 36

```SELECT p.product_name, SUM(o.total_amount) AS total_revenue FROM orders o JOIN products p ON o.product_id = p.product_id WHERE o.status = 'completed' AND o.order_date >= CURRENT_DATE - INTERVAL 7 DAY GROUP BY p.product_name;```

Question 37

How would you calculate the **average order value** for each product **category** in the **last 60 days**? **Table Name:** `orders` - `order_id` INT - `product_id` INT - `order_date` TIMESTAMP - `status` STRING (values: 'completed', 'pending', 'cancelled') - `total_amount` DOUBLE **Table Name:** `products` - `product_id` INT - `category` STRING ## Footnote **medium**

Answer 37

```SELECT p.category, AVG(o.total_amount) AS avg_order_value FROM orders o JOIN products p ON o.product_id = p.product_id WHERE o.status = 'completed' AND o.order_date >= CURRENT_DATE - INTERVAL 60 DAY GROUP BY p.category```

Question 38

How would you find the **most recent order** placed by each customer? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE ## Footnote **medium**

Answer 38

```SELECT customer_id, MAX(order_date) AS latest_order FROM orders GROUP BY customer_id;```

Question 39

How would you count the **total number of orders** by **order status** for the **last 90 days**? **Table Name:** `orders` - `order_id` INT - `order_date` TIMESTAMP - `status` STRING (values: 'completed', 'pending', 'cancelled')

Answer 39

```SELECT status, COUNT(order_id) AS total_orders FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL 90 DAY GROUP BY status```

Question 40

How would you calculate the **7-day rolling average** of total sales for each product category? **Table Name:** `orders` - `order_id` INT - `product_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE **Table Name:** `products` - `product_id` INT - `category` STRING ## Footnote **advanced**

Answer 40

```SELECT p.category, o.order_date, AVG(o.total_amount) OVER (PARTITION BY p.category ORDER BY o.order_date RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW) AS rolling_avg FROM orders o JOIN products p ON o.product_id = p.product_id;```

Question 41

How would you compare the **current order total** with the **previous order total** for each customer and calculate the difference? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE ## Footnote **advanced**

Answer 41

```SELECT customer_id, order_id, total_amount, LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order, total_amount - LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS difference FROM orders;```

Question 42

How would you calculate the **cumulative sales total** for each product category? **Table Name:** `orders` - `order_id` INT - `product_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE **Table Name:** `products` - `product_id` INT - `category` STRING ## Footnote **advanced**

Answer 42

```SELECT p.category, o.order_date, SUM(o.total_amount) OVER (PARTITION BY p.category ORDER BY o.order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales FROM orders o JOIN products p ON o.product_id = p.product_id```

Question 43

How would you retrieve the **first order** placed by each customer? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE ## Footnote **advanced**

Answer 43

```WITH ranked_orders AS ( SELECT customer_id, order_id, order_date, total_amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num FROM orders ) SELECT customer_id, order_id, order_date, total_amount FROM ranked_orders WHERE row_num = 1```

Question 44

How would you rank **products by total sales** within each category for the last 30 days? **Table Name:** `orders` - `order_id` INT - `product_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE **Table Name:** `products` - `product_id` INT - `category` STRING ## Footnote **advanced**

Answer 44

```SELECT p.category, p.product_id, SUM(o.total_amount) AS total_sales, RANK() OVER (PARTITION BY p.category ORDER BY SUM(o.total_amount) DESC) AS sales_rank FROM orders o JOIN products p ON o.product_id = p.product_id WHERE o.order_date >= CURRENT_DATE - INTERVAL 30 DAY GROUP BY p.category, p.product_id```

Question 45

How would you **rank products** by their **sales total** in each category, ensuring no gaps in ranking? **Table Name:** `orders` - `order_id` INT - `product_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE **Table Name:** `products` - `product_id` INT - `category` STRING ## Footnote **advanced**

Answer 45

```SELECT p.category, p.product_id, SUM(o.total_amount) AS total_sales, DENSE_RANK() OVER (PARTITION BY p.category ORDER BY SUM(o.total_amount) DESC) AS sales_rank FROM orders o JOIN products p ON o.product_id = p.product_id GROUP BY p.category, p.product_id```

Question 46

How would you **divide customers into 4 quartiles** based on their total order value over the last 90 days? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE ## Footnote **advanced**

Answer 46

```SELECT customer_id, SUM(total_amount) AS total_spent, NTILE(4) OVER (ORDER BY SUM(total_amount) DESC) AS quartile FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL 90 DAY GROUP BY customer_id```

Question 47

How would you calculate the **total sales by category** and **overall sales** using **GROUPING SETS**? **Table Name:** `orders` - `order_id` INT - `product_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE **Table Name:** `products` - `product_id` INT - `category` STRING ## Footnote **advanced**

Answer 47

```SELECT p.category, SUM(o.total_amount) AS total_sales FROM orders o JOIN products p ON o.product_id = p.product_id GROUP BY GROUPING SETS (p.category, ())```

Question 48

How would you generate a **list of all possible combinations** of products and customers? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `product_id` INT **Table Name:** `products` - `product_id` INT - `category` STRING **Table Name:** `customers` - `customer_id` INT - `customer_name` STRING ## Footnote **advanced**

Answer 48

```SELECT c.customer_id, p.product_id FROM customers c CROSS JOIN products p```

Question 49

How would you get a **list of all customers** with **their order details** (including those who didn't place any order)? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE **Table Name:** `customers` - `customer_id` INT - `customer_name` STRING ## Footnote **advanced**

Answer 49

```SELECT c.customer_name, o.order_id, o.total_amount FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id```

Question 50

How would you extract **values from a JSON column** to find the **product name** from a JSON structure in the **orders** table? **Table Name:** `orders` - `order_id` INT - `order_date` TIMESTAMP - `product_info` STRING (JSON format: `{"product_id": 1, "product_name": "Widget"}`) ## Footnote **advanced**

Answer 50

```SELECT order_id, JSON_EXTRACT(product_info, '$.product_name') AS product_name FROM orders```

Question 51

How would you **retrieve only the latest order** for each customer using the **QUALIFY** statement? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `order_date` TIMESTAMP - `total_amount` DOUBLE ## Footnote **advanced**

Answer 51

```SELECT customer_id, order_id, order_date, total_amount FROM ( SELECT customer_id, order_id, order_date, total_amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS row_num FROM orders ) QUALIFY row_num = 1```

Question 52

How would you **flatten an array** of product IDs from the `orders` table to see each individual product in the order? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `order_date` TIMESTAMP - `product_ids` ARRAY (example: `[1, 2, 3]`) ## Footnote **medium**

Answer 52

```SELECT order_id, customer_id, order_date, EXplode(product_ids) AS product_id FROM orders```

Question 53

How would you **join orders** with a list of **products** using an exploded field for `product_ids` in the `orders` table? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `order_date` TIMESTAMP - `product_ids` ARRAY (example: `[1, 2, 3]`) **Table Name:** `products` - `product_id` INT - `product_name` STRING ## Footnote **advanced**

Answer 53

```SELECT o.order_id, o.customer_id, o.order_date, p.product_name FROM orders o LATERAL VIEW EXPLODE(o.product_ids) exploded AS product_id JOIN products p ON o.product_id = p.product_id```

Question 54

How would you **join orders with products** and **filter** only the orders containing a specific product (e.g., product_id = 2) from the exploded `product_ids` field? **Table Name:** `orders` - `order_id` INT - `customer_id` INT - `order_date` TIMESTAMP - `product_ids` ARRAY (example: `[1, 2, 3]`) **Table Name:** `products` - `product_id` INT - `product_name` STRING ## Footnote **advanced**

Answer 54

```SELECT o.order_id, o.customer_id, o.order_date, p.product_name FROM orders o LATERAL VIEW EXPLODE(o.product_ids) exploded AS product_id JOIN products p ON exploded.product_id = p.product_id WHERE exploded.product_id = 2```

Question 55

How would you **check the number of NULL values** in the `email` column of the `users` table? **Table Name:** `users` - `user_id` INT - `email` STRING ## Footnote **medium**

Answer 55

```SELECT COUNT(*) AS null_count FROM users WHERE email IS NULL```

Question 56

How would you identify **rows with missing values** in the `first_name` or `last_name` columns? **Table Name:** `users` - `user_id` INT - `first_name` STRING - `last_name` STRING ## Footnote **medium**

Answer 56

```SELECT user_id, first_name, last_name FROM users WHERE first_name IS NULL OR last_name IS NULL;```

Question 57

How would you **check for duplicate entries** in the `email` column of the `users` table? **Table Name:** `users` - `user_id` INT - `email` STRING ## Footnote **easy**

Answer 57

```SELECT email, COUNT(*) AS duplicate_count FROM users GROUP BY email HAVING COUNT(*) > 1```

Question 58

How would you find **non-numeric values** in a column `amount` which should contain numeric data? **Table Name:** `transactions` - `transaction_id` INT - `amount` STRING ## Footnote **advanced**

Answer 58

```SELECT transaction_id, amount FROM transactions WHERE NOT amount REGEXP '^[0-9]+(\.[0-9]+)?$'```

Question 59

How would you identify if there are **duplicate rows** based on the combination of `email` and `user_id` in the `users` table? **Table Name:** `users` - `user_id` INT - `email` STRING ## Footnote **medium**

Answer 59

```SELECT email, user_id, COUNT(*) AS duplicate_count FROM users GROUP BY email, user_id HAVING COUNT(*) > 1```

Question 60

How would you detect **invalid date formats** in the `birth_date` column (which should contain valid `DATE` values)? **Table Name:** `users` - `user_id` INT - `birth_date` STRING

Answer 60

```SELECT user_id, birth_date FROM users WHERE NOT ISDATE(birth_date)```

Question 61

How would you check if the **values in the `product_id`** column in the `orders` table match the valid IDs in the `products` table? **Table Name:** `orders` - `order_id` INT - `product_id` INT **Table Name:** `products` - `product_id` INT - `product_name` STRING

Answer 61

```SELECT o.order_id, o.product_id FROM orders o LEFT JOIN products p ON o.product_id = p.product_id WHERE p.product_id IS NULL```

Question 62

How would you identify **orders with unusually high amounts** greater than 1000 in the `orders` table? **Table Name:** `orders` - `order_id` INT - `total_amount` DOUBLE

Answer 62

```SELECT order_id, total_amount FROM orders WHERE total_amount > 1000```

Question 63

# Handling Missing Values (Imputation Techniques) How would you **fill in missing `total_amount` values** in the `orders` table with the **average** `total_amount`? **Table Name:** `orders` - `order_id` INT - `total_amount` DOUBLE

Answer 63

```SELECT order_id, COALESCE(total_amount, (SELECT AVG(total_amount) FROM orders)) AS filled_total_amount FROM orders```

Question 64

# Handling Outliers in Numerical Data How would you **identify outliers** in the `total_amount` column of the `orders` table using the **Interquartile Range (IQR)** method? **Table Name:** `orders` - `order_id` INT - `total_amount` DOUBLE ## Footnote **advanced**

Answer 64

```WITH percentiles AS ( SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY total_amount) AS Q1, PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_amount) AS Q3 FROM orders ) SELECT order_id, total_amount FROM orders, percentiles WHERE total_amount < Q1 - 1.5 * (Q3 - Q1) OR total_amount > Q3 + 1.5 * (Q3 - Q1)```

Question 65

# Removing Duplicates in a Large Dataset How would you **remove duplicate entries** in the `users` table, keeping only the first occurrence based on `email`? **Table Name:** `users` - `user_id` INT - `email` STRING ## Footnote **advanced**

Answer 65

```WITH ranked_users AS ( SELECT user_id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS row_num FROM users ) DELETE FROM ranked_users WHERE row_num > 1;```

Question 66

# Data Type Conversions and Implicit Casting How would you **convert** the `amount` column from **STRING** to **FLOAT** in the `transactions` table and check for any conversion errors? **Table Name:** `transactions` - `transaction_id` INT - `amount` STRING

Answer 66

```SELECT transaction_id, CAST(amount AS FLOAT) AS amount_float FROM transactions WHERE TRY_CAST(amount AS FLOAT) IS NULL```

Question 67

# Empty Strings vs NULL Values How would you find **rows where the `email` is either NULL or an empty string** in the `users` table? **Table Name:** `users` - `user_id` INT - `email` STRING

Answer 67

```SELECT user_id, email FROM users WHERE email IS NULL OR email = ''```

Question 68

# Data Redundancy How would you identify **redundant rows** where the same `email` appears multiple times in the `users` table? **Table Name:** `users` - `user_id` INT - `email` STRING

Answer 68

```SELECT email, COUNT(*) AS duplicate_count FROM users GROUP BY email HAVING COUNT(*) > 1```

Question 69

A product manager needs to update the **price** of a product, insert a new product, and delete an outdated product in the `products` table. 1. **Insert** a new product with `product_id` = 101, `product_name` = 'Wireless Mouse', and `price` = 29.99. 2. **Update** the price of the product with `product_id` = 101 to 24.99. 3. **Delete** the product with `product_id` = 101 as it’s outdated. **Table Name:** `products` - `product_id` INT - `product_name` STRING - `price` DOUBLE ## Footnote **easy**

Answer 69

**insert** ```INSERT INTO products (product_id, product_name, price) VALUES (101, 'Wireless Mouse', 29.99);``` ** update** ```UPDATE products SET price = 24.99 WHERE product_id = 101;``` **delete** ```DELETE FROM products WHERE product_id = 101;```