SQL & Database Interview Questions

SQL (Structured Query Language) is the standard language used to access, retrieve, and manage data stored in relational databases. Data Analysts use SQL to extract data, filter records, combine tables, calculate business metrics, and answer business questions efficiently.

The SELECT statement is used to retrieve data from one or more tables in a database. You can use it to return specific columns or all columns depending on your analysis needs.

For Example:
SELECT customer_name, sales
FROM Orders;

SELECT * retrieves every column from a table, while selecting specific columns returns only the information you need.

For example:
SELECT customer_name, sales
FROM Orders;

The WHERE clause filters records based on a specified condition. It allows analysts to retrieve only the rows that meet certain criteria.

Example:

SELECT *
FROM Orders
WHERE Sales > 1000;

This query returns only orders where sales exceed $1,000.

WHERE filters individual rows before data is grouped, while HAVING filters grouped results after aggregation.

Example:

SELECT Region, SUM(Sales)
FROM Orders
GROUP BY Region
HAVING SUM(Sales) > 50000;

Here, HAVING returns only the regions whose total sales are greater than $50,000.

The WHERE clause filters rows before the final result is returned. It helps retrieve only records that match a specific condition.

Example:

SELECT *
FROM Orders
WHERE sales > 1000;

WHERE filters rows before grouping, while HAVING filters results after aggregation.

Example:

SELECT region, SUM(sales) AS total_sales
FROM Orders
GROUP BY region
HAVING SUM(sales) > 50000;

Aggregate functions perform calculations on multiple rows and return a single result.

Common examples:
COUNT()
SUM()
AVG()
MIN()
MAX()

They are often used to calculate KPIs such as total sales, average order value, and number of customers.

GROUP BY groups rows that share the same value in one or more columns. It is commonly used with aggregate functions to calculate totals, averages, or counts by category.

Example:
SELECT category, SUM(sales) AS total_sales
FROM Orders
GROUP BY category;

ORDER BY sorts query results in ascending or descending order.
Example:
SELECT product_name, sales
FROM Orders
ORDER BY sales DESC;

This helps identify top-selling products, highest revenue regions, or lowest-performing categories.

A SQL JOIN combines data from two or more tables based on a related column, such as a primary key and foreign key. Joins allow analysts to retrieve meaningful information that is stored across multiple tables.

An INNER JOIN returns only the rows that have matching values in both tables.

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no match exists, the result contains NULL values for the right table.

Example:

SELECT c.customer_name, o.order_id
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id;

This query returns all customers, including those who have never placed an order.

Use an INNER JOIN when you only need records that exist in both tables.

For example, retrieving customers who have placed at least one order or employees assigned to a department.

A LEFT JOIN helps identify missing relationships in the data.

For example, finding:

Customers with no orders
Products that have never been sold
Employees without assigned projects
These insights are useful for business reporting and decision-making.

The ON clause specifies how two tables are related by defining the matching columns. Without it, SQL would not know how to combine the tables correctly.

Example:
SELECT c.customer_name, o.order_date
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id;
Here, customer_id is used to match records between the Customers and Orders tables.

A subquery is a query written inside another SQL query. It is used to retrieve intermediate results that help the main query answer a business question.

Example:
SELECT customer_name
FROM Customers
WHERE customer_id IN (
SELECT customer_id
FROM Orders
WHERE sales > 1000
);

A Common Table Expression (CTE) is a temporary named result set created using the WITH statement. It makes complex SQL queries easier to read, organize, and maintain.

Example:

WITH RegionalSales AS (
SELECT region,
SUM(sales) AS total_sales
FROM Orders
GROUP BY region
)
SELECT *
FROM RegionalSales;

Window functions perform calculations across a set of related rows without combining them into a single row. They are commonly used for ranking, running totals, moving averages, and comparing records.

Common window functions include:

ROW_NUMBER()
RANK()
DENSE_RANK()
LAG()
LEAD()

ROW_NUMBER() assigns a unique number to every row, even if values are tied.

RANK() assigns the same rank to tied values and skips the next rank.

For example, if two employees tie for first place:

RANK(): 1, 1, 3
ROW_NUMBER(): 1, 2, 3

The CASE statement adds conditional logic to SQL queries. It allows analysts to categorize data or create calculated columns based on business rules.
Example:
SELECT customer_name,
sales,
CASE
WHEN sales >= 1000 THEN 'High Value'
WHEN sales >= 500 THEN 'Medium Value'
ELSE 'Low Value'
END AS customer_segment
FROM Orders;
This query classifies customers into different spending segments based on their sales.

A primary key is a column (or combination of columns) that uniquely identifies each row in a table. A primary key cannot contain duplicate or NULL values.

Example:
In a Customers table, customer_id is typically the primary key because each customer has a unique ID.

A foreign key is a column in one table that references the primary key of another table. It creates a relationship between the two tables and helps maintain data integrity.
Example:
The customer_id column in the Orders table is a foreign key that references the customer_id in the Customers table.

Normalization is the process of organizing data into related tables to reduce data redundancy and improve data consistency. It helps eliminate duplicate data and makes database maintenance easier.

Indexes improve query performance by allowing the database to locate records more quickly without scanning the entire table. They are especially useful for large datasets and frequently searched columns.

Most business data is stored across multiple related tables. Understanding relationships allows Data Analysts to write accurate JOIN queries, combine data correctly, and produce reliable reports and business insights.

I would group sales by customer, calculate total sales using SUM(), sort the results in descending order with ORDER BY, and return the top five customers using LIMIT or TOP, depending on the SQL database.

I would use a LEFT JOIN between the Customers and Orders tables and filter for rows where the order information is NULL.
This identifies customers who exist in the customer table but have no matching records in the Orders table.

I would retrieve sales, profit, discounts, product categories, and regions using SQL. Then I would aggregate the data to compare profitability across different segments and identify factors contributing to lower profits.

I would calculate total sales for each product, use a window function such as RANK() or ROW_NUMBER() partitioned by category, and return the product with the highest sales in each category.

Employers want to evaluate how candidates solve real business problems using SQL. They assess analytical thinking, query design, database knowledge, and the ability to explain how SQL results support business decisions.