SQL Clauses
SQL Clauses Interview with follow-up questions
Interview Question Index
- Question 1: Can you explain the difference between WHERE and HAVING clause in SQL?
- Follow up 1 : Can you provide an example where both WHERE and HAVING clause are used in a single SQL query?
- Follow up 2 : What happens if we use WHERE clause with aggregate functions?
- Follow up 3 : Why can't we use HAVING clause without GROUP BY in SQL?
- Question 2: What is the purpose of the GROUP BY clause in SQL?
- Follow up 1 : Can you provide an example of a SQL query using GROUP BY clause?
- Follow up 2 : What is the difference between ORDER BY and GROUP BY clause?
- Follow up 3 : Can you use GROUP BY clause without an aggregate function?
- Question 3: How does the ORDER BY clause work in SQL?
- Follow up 1 : Can you provide an example of a SQL query using ORDER BY clause?
- Follow up 2 : What is the default sorting order of the ORDER BY clause?
- Follow up 3 : Can you use ORDER BY clause in a subquery?
- Question 4: What is the use of the DISTINCT clause in SQL?
- Follow up 1 : Can you provide an example of a SQL query using DISTINCT clause?
- Follow up 2 : What is the difference between DISTINCT and UNIQUE in SQL?
- Follow up 3 : Can you use DISTINCT clause with multiple columns?
- Question 5: Can you explain the concept of the JOIN clause in SQL?
- Follow up 1 : Can you provide an example of a SQL query using JOIN clause?
- Follow up 2 : What are the different types of JOINs in SQL?
- Follow up 3 : What is the difference between INNER JOIN and OUTER JOIN?
Question 1: Can you explain the difference between WHERE and HAVING clause in SQL?
Answer:
The WHERE clause is used to filter rows based on a condition in a SQL query. It is used with the SELECT, UPDATE, and DELETE statements. The WHERE clause is applied before any grouping or aggregation is done.
The HAVING clause is used to filter rows based on a condition in a SQL query that involves aggregate functions. It is used with the SELECT statement and is applied after the GROUP BY clause. The HAVING clause is used to filter the results of a grouped query.
In summary, the WHERE clause is used to filter individual rows, while the HAVING clause is used to filter groups of rows.
Follow up 1: Can you provide an example where both WHERE and HAVING clause are used in a single SQL query?
Answer:
Sure! Here's an example:
SELECT category, COUNT(*)
FROM products
WHERE price > 100
GROUP BY category
HAVING COUNT(*) > 5;
In this example, the WHERE clause filters the rows where the price is greater than 100. Then, the GROUP BY clause groups the rows by category. Finally, the HAVING clause filters the groups where the count of products is greater than 5.
Follow up 2: What happens if we use WHERE clause with aggregate functions?
Answer:
When we use the WHERE clause with aggregate functions, the condition specified in the WHERE clause is applied to individual rows before the aggregation is performed. Only the rows that satisfy the condition are included in the aggregation.
For example, consider the following query:
SELECT SUM(sales)
FROM orders
WHERE date > '2021-01-01';
In this query, the WHERE clause filters the rows where the date is greater than '2021-01-01'. Then, the SUM function is applied to the filtered rows to calculate the total sales.
Follow up 3: Why can't we use HAVING clause without GROUP BY in SQL?
Answer:
The HAVING clause is used to filter the results of a grouped query. It is applied after the GROUP BY clause, which means it operates on the groups created by the GROUP BY clause.
If we use the HAVING clause without the GROUP BY clause, there are no groups to filter, and the query would not make sense. The HAVING clause requires the GROUP BY clause to determine the groups on which the filtering should be applied.
In summary, the HAVING clause can only be used in conjunction with the GROUP BY clause in SQL.
Question 2: What is the purpose of the GROUP BY clause in SQL?
Answer:
The GROUP BY clause in SQL is used to group rows that have the same values in specified columns. It is commonly used with aggregate functions, such as COUNT, SUM, AVG, etc., to perform calculations on each group of rows.
Follow up 1: Can you provide an example of a SQL query using GROUP BY clause?
Answer:
Sure! Here's an example:
SELECT department, COUNT(*) as total_employees
FROM employees
GROUP BY department;
This query groups the rows in the 'employees' table by the 'department' column and calculates the total number of employees in each department.
Follow up 2: What is the difference between ORDER BY and GROUP BY clause?
Answer:
The ORDER BY clause is used to sort the result set based on one or more columns, while the GROUP BY clause is used to group rows based on one or more columns. The ORDER BY clause affects the order in which the rows are displayed, while the GROUP BY clause affects the way the rows are grouped and the calculations performed on each group.
Follow up 3: Can you use GROUP BY clause without an aggregate function?
Answer:
No, the GROUP BY clause must be used with at least one aggregate function, such as COUNT, SUM, AVG, etc. The purpose of the GROUP BY clause is to perform calculations on each group of rows, and the aggregate function is used to specify the calculation to be performed.
Question 3: How does the ORDER BY clause work in SQL?
Answer:
The ORDER BY clause is used in SQL to sort the result set of a query in ascending or descending order based on one or more columns. It is typically used in conjunction with the SELECT statement. The syntax for using the ORDER BY clause is as follows:
SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...
The ORDER BY clause can be used with both numeric and string columns. By default, the sorting order is ascending (ASC), but you can specify descending (DESC) order by appending it to the column name. If multiple columns are specified in the ORDER BY clause, the sorting is done based on the first column, and then the second column is used as a tiebreaker if there are any equal values in the first column.
Follow up 1: Can you provide an example of a SQL query using ORDER BY clause?
Answer:
Sure! Here's an example of a SQL query using the ORDER BY clause:
SELECT name, age FROM employees ORDER BY age DESC;
This query selects the 'name' and 'age' columns from the 'employees' table and sorts the result set in descending order based on the 'age' column. The result will be a list of employee names and ages, ordered from oldest to youngest.
Follow up 2: What is the default sorting order of the ORDER BY clause?
Answer:
The default sorting order of the ORDER BY clause is ascending (ASC). If you don't specify the sorting order explicitly, the result set will be sorted in ascending order based on the specified column(s).
Follow up 3: Can you use ORDER BY clause in a subquery?
Answer:
Yes, you can use the ORDER BY clause in a subquery. The ORDER BY clause can be used in the subquery itself or in the outer query that references the subquery. When using the ORDER BY clause in a subquery, it is important to note that the ordering of the result set in the subquery does not affect the ordering of the outer query. The ORDER BY clause in the outer query will determine the final ordering of the result set.
Question 4: What is the use of the DISTINCT clause in SQL?
Answer:
The DISTINCT clause in SQL is used to retrieve unique values from a column or a combination of columns in a table. It eliminates duplicate rows from the result set.
Follow up 1: Can you provide an example of a SQL query using DISTINCT clause?
Answer:
Sure! Here's an example:
SELECT DISTINCT column_name FROM table_name;
This query will retrieve all the unique values from the specified column in the table.
Follow up 2: What is the difference between DISTINCT and UNIQUE in SQL?
Answer:
In SQL, the DISTINCT keyword is used in the SELECT statement to retrieve unique values from a column or a combination of columns in a table. On the other hand, the UNIQUE constraint is used when creating a table to ensure that the values in a column or a combination of columns are unique.
While DISTINCT is used in queries to filter the result set, UNIQUE is used to define the data integrity rules for a table.
Follow up 3: Can you use DISTINCT clause with multiple columns?
Answer:
Yes, the DISTINCT clause can be used with multiple columns in a SQL query. Here's an example:
SELECT DISTINCT column1, column2 FROM table_name;
This query will retrieve all the unique combinations of values from the specified columns in the table.
Question 5: Can you explain the concept of the JOIN clause in SQL?
Answer:
The JOIN clause in SQL is used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables in a single query. The JOIN clause specifies the relationship between tables by specifying the columns to match on. There are different types of JOINs in SQL, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Follow up 1: Can you provide an example of a SQL query using JOIN clause?
Answer:
Sure! Here's an example of a SQL query using the JOIN clause:
SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;
In this example, the query retrieves the order_id from the orders table and the customer_name from the customers table. The JOIN clause is used to match the customer_id column in both tables.
Follow up 2: What are the different types of JOINs in SQL?
Answer:
There are several types of JOINs in SQL:
- INNER JOIN: Returns only the matching rows between the tables.
- LEFT JOIN: Returns all the rows from the left table and the matching rows from the right table.
- RIGHT JOIN: Returns all the rows from the right table and the matching rows from the left table.
- FULL JOIN: Returns all the rows from both tables, including the non-matching rows.
These JOIN types allow you to control how the rows are combined based on the relationship between the tables.
Follow up 3: What is the difference between INNER JOIN and OUTER JOIN?
Answer:
The main difference between INNER JOIN and OUTER JOIN is how they handle non-matching rows:
- INNER JOIN: Returns only the matching rows between the tables. If there is no match, the row is not included in the result set.
- OUTER JOIN: Returns all the rows from one table and the matching rows from the other table. If there is no match, NULL values are used for the columns of the non-matching table.
In other words, INNER JOIN filters out the non-matching rows, while OUTER JOIN includes them in the result set.