In this article, we will be covering SQL query interview questions and answers. SQL (Structured Query Language) is a programming language that is widely used in the field of data analysis and management. It is a powerful tool for retrieving, manipulating and managing data stored in relational databases.
The purpose of this article is to provide a resource for those who are preparing for a SQL interview. We will be covering a range of topics, including basic SQL syntax and concepts, data types and operators, functions, data management, joins, and subqueries. By the end of this article, you should have a good understanding of the most fundamental aspects of SQL and be well-prepared to tackle common interview questions.
Table of Contents
Basic SQL Syntax and Concepts
In this section, we will cover the basic syntax and concepts of SQL.
The SELECT statement is used to retrieve data from a database. It is the most commonly used SQL statement.
The FROM clause specifies the table or tables from which the data is to be retrieved.
The WHERE clause is used to filter the results of the SELECT statement based on specific conditions.
The GROUP BY clause is used to group the results of the SELECT statement by one or more columns.
The HAVING clause is used to filter the results of the SELECT statement based on aggregated data.
The LIMIT clause is used to limit the number of rows returned by the SELECT statement.
Here is an example SELECT statement that demonstrates the use of these clauses:
SELECT column1, column2, AVG(column3)
FROM table1
WHERE column4 > 100
GROUP BY column1, column2
HAVING AVG(column3) > 50
LIMIT 10;
This statement will retrieve column1, column2, and the average of column3 from table1, only for rows where column4 is greater than 100. The results will be grouped by column1 and column2 and filtered to only include groups where the average of column3 is greater than 50. The final result will be limited to the first 10 rows.
Data Types and Operators
In this section, we will cover common data types and operators in SQL.
Data types define the kind of data that a column can hold, such as integers, decimals, strings, and dates. Some common data types include:
INTEGER: a whole number
DECIMAL: a number with a decimal point
VARCHAR: a variable-length string
DATE: a date value
Comparison operators are used to compare values in a WHERE clause. Some common comparison operators include:
=: equal to
<>: not equal to
>: greater than
>=: greater than or equal to
<: less than
<=: less than or equal to
Logical operators are used to combine multiple conditions in a WHERE clause. Some common logical operators include:
AND: both conditions must be true
OR: at least one condition must be true
NOT: negates the condition
Here is an example of a SELECT statement using comparison and logical operators:
SELECT *
FROM table1
WHERE column1 > 100 AND column2 < 50 OR column3 = 'ABC';
This statement will retrieve all rows from table1 where column1 is greater than 100 and column2 is less than 50, or where column3 is equal to ‘ABC’.
Functions
In this section, we will cover common functions in SQL.
Aggregate functions are used to perform calculations on multiple rows of data and return a single result. Some common aggregate functions include:
SUM: calculates the total of a set of values
AVG: calculates the average of a set of values
MIN: finds the minimum value in a set of values
MAX: finds the maximum value in a set of values
String functions are used to manipulate character strings. Some common string functions include:
LENGTH: returns the length of a string
SUBSTRING: extracts a portion of a string
CONCAT: concatenates two or more strings
Date functions are used to manipulate date values. Some common date functions include:
CURDATE: returns the current date
MONTH: returns the month of a date
YEAR: returns the year of a date
Here is an example of a SELECT statement using aggregate and string functions:
SELECT SUM(column1) AS total, CONCAT(column2, ' ', column3) AS full_name
FROM table1
GROUP BY full_name;
This statement will calculate the sum of column1 for each unique full_name, which is the concatenation of column2 and column3. The result will be displayed as two columns: total and full_name.
Subqueries
In this section, we will cover subqueries in SQL.
A subquery is a SELECT statement that is nested within another SELECT, INSERT, UPDATE, DELETE, or SET statement. It is used to return data that will be used in the outer query.
There are two types of subqueries: simple and correlated.
A simple subquery is a standalone SELECT statement that is nested within the outer query. It is executed first, and its results are used by the outer query.
A correlated subquery is a SELECT statement that is nested within the outer query and references one or more columns from the outer query. It is executed once for each row of the outer query, and its results are used by the outer query.
Here is an example of a simple subquery:
SELECT *
FROM table1
WHERE column1 = (SELECT column2 FROM table2 WHERE column3 = 'ABC');
This statement will retrieve all rows from table1 where column1 is equal to the value of column2 in table2, for rows where column3 is equal to ‘ABC’.
Here is an example of a correlated subquery:
SELECT t1.*
FROM table1 t1
WHERE (SELECT COUNT(*) FROM table2 t2 WHERE t2.column1 = t1.column1) > 1;
This statement will retrieve all rows from table1 where there is more than one row in table2 with the same value in column1. The correlated subquery is executed once for each row of the outer query, and its results are used to filter the outer query.
| SQL Interview Resources
Top 60 Basic SQL Interview Questions and Answers Top 10 SQL Scenario-Based Interview Questions and Answers |
SQL Query Interview Questions and Answers
Write a query to find the top 3 most populous cities in each country.
SELECT country, city, population
FROM cities
WHERE population IN (SELECT population FROM cities ORDER BY population DESC LIMIT 3)
ORDER BY country, population DESC;
This query uses a subquery to select the top 3 most populous cities in the cities table, and then uses the IN operator to filter the outer query to only include those cities. The final result is sorted by country and population.
Write a query to find the number of customers who have placed an order in the past month.
SELECT COUNT(*)
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= CURDATE() - INTERVAL 1 MONTH);
This query uses a subquery to select the customer_id of all customers who have placed an order within the past month. The outer query uses the IN operator to count the number of customers in the customers table whose ID appears in the list of customer_ids returned by the subquery.
Write a query to find the average order value for each customer.
SELECT customer_id, AVG(order_total) AS avg_order_value
FROM orders
GROUP BY customer_id;
This query calculates the average order total for each customer by using the AVG function and grouping the results by customer_id. The alias avg_order_value is used to label the resulting column.
Write a query to find the total number of orders placed by each customer.
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;
Write a query to find the most popular product among customers who have placed at least 3 orders.
SELECT product_id, COUNT(*) AS popularity
FROM orders
WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) >= 3)
GROUP BY product_id
ORDER BY popularity DESC
LIMIT 1;
Write a query to find the total number of products in stock for each category.
SELECT category, SUM(stock) AS total_in_stock
FROM products
GROUP BY category;
Write a query to find the average price of products in each category that have a price greater than $50.
SELECT category, AVG(price) AS avg_price
FROM products
WHERE price > 50
GROUP BY category;
Write a query to find the top 5 highest-selling products.
SELECT product_name, SUM(quantity) AS total_sold
FROM orders
GROUP BY product_name
ORDER BY total_sold DESC
LIMIT 5;
Write a query to find the total number of orders placed on each day.
SELECT order_date, COUNT(*) AS total_orders
FROM orders
GROUP BY order_date;
Write a query to find the average order value on weekends.
SELECT AVG(order_total) AS avg_weekend_order_value
FROM orders
WHERE DAYOFWEEK(order_date) IN (1, 7);
Write a query to find the total number of customers who have placed orders in the past year.
SELECT COUNT(DISTINCT customer_id) AS total_customers
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 1 YEAR;
Write a query to find the most popular category among customers who have placed at least 5 orders.
SELECT category, COUNT(*) AS popularity
FROM products p
JOIN orders o ON p.product_id = o.product_id
WHERE o.customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5)
GROUP BY category
ORDER BY popularity DESC
LIMIT 1;
Write a query to find the total number of products that are low in stock (less than 10 units).
SELECT COUNT(*) AS total_low_stock
FROM products
WHERE stock < 10;
Write a query to find the average price of products in each category that have a rating of 4 or 5.
SELECT category, AVG(price) AS avg_price
FROM products
WHERE rating IN (4, 5)
GROUP BY category;
Write a query to find the top 3 highest-grossing products.
SELECT product_name, SUM(order_total) AS total_revenue
FROM orders
GROUP BY product_name
ORDER BY total_revenue DESC
LIMIT 3;
Write a query to find the total number of orders placed by each customer in the past 6 months.
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 6 MONTH
GROUP BY customer_id;
Write a query to find the average order value for each month.
SELECT MONTH(order_date) AS month, AVG(order_total) AS avg_order_value
FROM orders
GROUP BY month;
Write a query to find the total number of customers who have placed orders on each day.
SELECT order_date, COUNT(DISTINCT customer_id) AS total_customers
FROM orders
GROUP BY order_date;
Conclusion
In this article, we have covered the basic syntax and concepts of SQL, as well as common data types, operators, functions, and techniques for managing data and performing queries. We have also provided examples of SQL query interview questions and answers.
By reviewing these concepts and practicing with sample queries, you should have a strong foundation in SQL and be well-prepared for a SQL query interview. However, it is important to continue learning and practicing your SQL skills, as there is always more to learn and master in this powerful programming language. You may want to consider reviewing more advanced SQL concepts and techniques to further enhance your skills and prepare for more advanced interviews.