SQL Questions
Estimated Time : 45 Minutes
1. Which MySQL function would you use to concatenate strings from multiple
rows into a single string?
CONCAT_WS
GROUP_CONCAT
CONCAT
STRING_CONCAT
2. Which SQL keyword is used to define a window function?
WINDOW
OVER
PARTITION
WITH
3. In a SQL query containing multiple clauses (e.g., SELECT, FROM, WHERE,
JOIN, GROUP BY, ORDER BY), what is the typical execution order?
FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT , ORDER BY,
LIMIT/OFFSET
SELECT, FROM/JOIN, WHERE, GROUP BY, HAVING ORDER BY,
LIMIT/OFFSET
SELECT, FROM/JOIN, WHERE, ORDER BY, GROUP BY, HAVING,
LIMIT/OFFSET
WHERE, FROM, GROUP BY, HAVING, ORDER BY, SELECT,
LIMIT/OFFSET
4. In MySQL, which of the following commands can be used to remove an
index from a table?
DROP INDEX
DELETE INDEX
SQL Questions 1
REMOVE INDEX
ERASE INDEX
5. What is the primary purpose of the CASE WHEN statement in SQL?
To define conditions for filtering rows in a WHERE clause.
To specify the order in which columns should be sorted in a result set.
To perform conditional logic and return different values based on
specified conditions.
To join multiple tables based on common columns.
6. In MySQL, which statement is used to add an index to an existing table?
ALTER INDEX
MODIFY INDEX
ADD INDEX
CREATE INDEX
7. Which JOIN type in MySQL is suitable for combining rows from two tables
even if there is no match found in the other table?
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
8. In SQL, what is the typical syntax for using the LAG function?
LAG(column_name)
LAG(column_name, n)
LAG(n, column_name)
LAG()
9. Question - 1:
Consider the following scenario where you have a table named
"employees" in a MySQL database. The table has the following structure:
SQL Questions 2
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
department_id INT,
salary DECIMAL(10, 2),
hire_date DATE
);
Now, imagine you frequently execute the following query to retrieve
employees earning a salary greater than a specified amount within a
particular department:
SELECT emp_name, salary
FROM employees
WHERE department_id = ? AND salary > ?
ORDER BY hire_date DESC;
Based on the provided query, which column or columns would you
recommend indexing on the "employees" table to optimize the query's
performance? Write the index structure and explain your reasoning.
10. Question - 2
The ideal time between when a customer places an order and when the
order is delivered is below or equal to 45 minutes.
You have been tasked with evaluating delivery driver performance by
calculating the average order value for each delivery driver who has
delivered at least once within this 45-minute period.
Your output should contain the driver ID along with their corresponding
average order value.
Table: Delivery_details
Create Table Query
CREATE TABLE orders (
customer_placed_order_datetime DATETIME,
placed_order_with_restaurant_datetime DATETIME,
driver_at_restaurant_datetime DATETIME,
delivered_to_consumer_datetime DATETIME,
SQL Questions 3
driver_id INT,
restaurant_id INT,
consumer_id INT,
is_new BOOLEAN,
delivery_region VARCHAR(255),
is_asap BOOLEAN,
order_total FLOAT,
discount_amount FLOAT,
tip_amount FLOAT,
refunded_amount FLOAT
);
SQL Questions 4
