 |
VOOZH | about |
|
VOOZH | about |
SQL is an essential skill for Business Analysts that helps in retrieving, analyzing and managing data from databases. It supports decision-making by providing meaningful insights from data.
Beginner-level SQL interview questions focus on the fundamentals of SQL which is essential for Business Analysts. These questions cover basic data retrieval (SELECT statements), filtering data using WHERE, sorting results with ORDER BY and aggregating data using GROUP BY.
SQL (Structured Query Language) is used to interact with relational databases. Business Analysts use SQL to extract insights from data, generate reports and support data-driven decision-making. It allows them to retrieve specific data points, filter datasets and perform aggregations, making it a crucial tool for analytical roles.
The SELECT statement is used to fetch data from a table. The syntax follows:
SELECT column1, column2 FROM table_name;Example:
SELECT name, age FROM employees;Explanation: This query retrieves the names and ages of all employees from the employees table.
To retrieve all records from a table, use the SELECT * statement, which fetches all columns and rows from the specified table
SELECT * FROM Customers;Explanation:
The WHERE clause filters records based on specified conditions, retrieving only relevant rows before aggregation or grouping in a SQL query.
SELECT product_name, price FROM products WHERE price > 50;It retrieves products where the price is greater than 50.
GROUP BY is used to group rows that have the same values in specified columns. It is typically used with aggregate functions such as COUNT, SUM, AVG, etc.
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
Explanation:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
This retrieves departments where the average salary exceeds 50,000.
SELECT product_name, price
FROM products
ORDER BY price DESC;
Explanation:
In this example, products are sorted by price from highest to lowest.
An INNER JOIN combines records from two tables based on a matching column. It returns only rows where there is a match in both tables
SELECT employees.name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;
This retrieves employee names along with their department names.
A subquery is a query nested inside another query. It is used to retrieve data that will be used as a condition in the main query.
SELECT product_name FROM products WHERE price > (SELECT AVG(price) FROM products);Explanation:
To update records in a table, use the UPDATE statement with the SET clause to modify specific column values and the WHERE clause to specify which rows to update.
UPDATE customers
SET city = 'New York'
WHERE customer_id = 123;
Explanation:
In this example, the city of the customer with customer_id = 123 is changed to 'New York'.
The DISTINCT keyword removes duplicate values from the result set, ensuring only unique records are retrieved from a specific column. It helps in finding unique entries in a dataset.
SELECT DISTINCT department FROM employees;Explanation:
SELECT COUNT(*) FROM table_name;This returns the total number of rows in the table.
Intermediate-level SQL interview questions delve deeper into data manipulation and complex queries. They cover concepts like self-joins, subqueries and CASE statements to handle conditional logic.
Additionally, they focus on data aggregation, extracting specific date parts and fetching ranked results (e.g., second-highest salary). These topics help Business Analysts perform advanced data analysis, categorize records and optimize queries for better performance.
A self-join joins a table with itself, commonly used for hierarchical relationships like employees and managers. It matches rows within the same table using aliases to differentiate them.
SELECT e1.name, e2.name
FROM employees e1 JOIN employees e2
ON e1.manager_id = e2.employee_id;
This retrieves employees and their corresponding managers.
SELECT YEAR(order_date) FROM orders;This extracts the year from the order_date column.
A CASE statement allows conditional logic in SQL queries, similar to an IF-ELSE structure. It is used to categorize, transform or filter data based on specified conditions
SELECT order_id,
CASE
WHEN amount > 1000 THEN 'High'
WHEN amount > 500 THEN 'Medium'
ELSE 'Low'
END AS order_category
FROM orders;
Explanation:
To fetch the second-highest salary, we need to find the maximum salary that is less than the highest salary. This can be done using a subquery with the MAX() function.
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Explanation:
A CROSS JOIN returns the Cartesian product of two tables.
SELECT * FROM products CROSS JOIN categories;This returns all possible combinations of products and categories.
Topics like Common Table Expressions (CTEs), CROSS JOINs and indexing strategies enable better performance and scalability in large databases. Mastering these topics allows analysts to work with big data, optimize reporting and streamline decision-making processes.
Window functions perform calculations across a subset of rows related to the current row.
SELECT name, salary, RANK()
OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
This ranks employees based on their salary.
The WITH clause, also known as a Common Table Expression (CTE), creates a temporary result set that can be referenced within the main query. It improves query readability and reusability.
WITH SalesData AS (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT * FROM SalesData WHERE total_spent > 500;
Explanation:
Using WITH simplifies complex queries by breaking them into smaller, manageable parts
A recursive query is a query that calls itself repeatedly to retrieve hierarchical or tree-structured data, such as organizational charts or category hierarchies. It is commonly used with Common Table Expressions (CTEs).
WITH RECURSIVE EmployeeHierarchy AS (
SELECT employee_id, name, manager_id
FROM employees
WHERE manager_id IS NULL -- Get the top-level manager
UNION ALL
SELECT e.employee_id, e.name, e.manager_id
FROM employees e
JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;
Explanation:
Recursive queries are useful for fetching data with parent-child relationships, such as company structures, bill of materials and directory trees
A stored procedure is a precompiled SQL block that executes a set of SQL statements. It improves performance, reduces redundancy and enhances security by encapsulating business logic inside the database.
CREATE PROCEDURE GetEmployeeData()
AS
BEGIN
SELECT * FROM employees;
END;
Explanation:
The CREATE PROCEDURE statement defines a stored procedure named GetEmployeeData().
Optimizing SQL queries involves: