SQL Queries and Set Operations
SQL Queries and Set Operations Interview with follow-up questions
Interview Question Index
- Question 1: What is a SQL query and how is it used?
- Follow up 1 : Can you write a basic SQL query?
- Follow up 2 : What are some common SQL functions used in queries?
- Follow up 3 : What is the difference between a query and a subquery?
- Question 2: What are set operations in SQL?
- Follow up 1 : Can you provide examples of set operations?
- Follow up 2 : How do set operations differ from joins?
- Follow up 3 : What are the different types of set operations in SQL?
- Question 3: How do you use the UNION operation in SQL?
- Follow up 1 : What is the difference between UNION and UNION ALL?
- Follow up 2 : Can you provide an example of a UNION operation?
- Follow up 3 : What are the requirements for using UNION?
- Question 4: What is the INTERSECT operation in SQL?
- Follow up 1 : How does INTERSECT differ from UNION?
- Follow up 2 : Can you provide an example of an INTERSECT operation?
- Follow up 3 : What are the requirements for using INTERSECT?
- Question 5: What is the EXCEPT operation in SQL?
- Follow up 1 : How does EXCEPT differ from INTERSECT?
- Follow up 2 : Can you provide an example of an EXCEPT operation?
- Follow up 3 : What are the requirements for using EXCEPT?
Question 1: What is a SQL query and how is it used?
Answer:
A SQL query is a request for data or information from a database. It is used to retrieve, insert, update, or delete data in a database. Queries are written in SQL (Structured Query Language), which is a programming language specifically designed for managing and manipulating relational databases.
Follow up 1: Can you write a basic SQL query?
Answer:
Sure! Here's an example of a basic SQL query to retrieve all records from a table:
SELECT * FROM table_name;
Follow up 2: What are some common SQL functions used in queries?
Answer:
There are many SQL functions that can be used in queries, but some common ones include:
COUNT()
: Returns the number of rows that match a specified condition.SUM()
: Calculates the sum of a column's values.AVG()
: Calculates the average of a column's values.MAX()
: Returns the maximum value in a column.MIN()
: Returns the minimum value in a column.UPPER()
: Converts a string to uppercase.LOWER()
: Converts a string to lowercase.CONCAT()
: Concatenates two or more strings.SUBSTRING()
: Extracts a substring from a string.DATE()
: Extracts the date part from a datetime value.NOW()
: Returns the current date and time.
These are just a few examples, and there are many more SQL functions available depending on the database system you are using.
Follow up 3: What is the difference between a query and a subquery?
Answer:
A query is a standalone SQL statement that retrieves data from one or more tables. It can be used to perform various operations such as filtering, sorting, and aggregating data.
On the other hand, a subquery is a query that is nested inside another query. It is used to retrieve data that is based on the result of another query. The result of a subquery can be used as a condition or value in the outer query.
In summary, a query is used to retrieve data directly from tables, while a subquery is used to retrieve data based on the result of another query.
Question 2: What are set operations in SQL?
Answer:
Set operations in SQL are used to combine the results of two or more SELECT statements into a single result set. The result set of a set operation contains only distinct rows, eliminating any duplicates.
Follow up 1: Can you provide examples of set operations?
Answer:
Sure! Here are some examples of set operations in SQL:
UNION: Combines the results of two or more SELECT statements, and returns all distinct rows.
INTERSECT: Returns only the common rows between two SELECT statements.
EXCEPT: Returns only the rows from the first SELECT statement that are not present in the second SELECT statement.
Follow up 2: How do set operations differ from joins?
Answer:
Set operations and joins are both used to combine data from multiple tables, but they differ in their approach and purpose.
Set operations combine the results of SELECT statements, focusing on the rows and eliminating duplicates. They do not consider the relationship between tables.
Joins, on the other hand, combine tables based on a common column or condition, focusing on the columns and preserving all rows. They consider the relationship between tables.
Follow up 3: What are the different types of set operations in SQL?
Answer:
There are three main types of set operations in SQL:
UNION: Combines the results of two or more SELECT statements, and returns all distinct rows.
INTERSECT: Returns only the common rows between two SELECT statements.
EXCEPT: Returns only the rows from the first SELECT statement that are not present in the second SELECT statement.
Question 3: How do you use the UNION operation in SQL?
Answer:
The UNION operation in SQL is used to combine the result sets of two or more SELECT statements into a single result set. The syntax for using UNION is as follows:
SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2
This will return a result set that contains all the distinct rows from both table1 and table2.
Follow up 1: What is the difference between UNION and UNION ALL?
Answer:
The main difference between UNION and UNION ALL is that UNION removes duplicate rows from the result set, while UNION ALL does not. When using UNION, only distinct rows are returned, meaning that if there are any duplicate rows between the result sets of the SELECT statements, only one copy of the duplicate row will be included in the final result set. On the other hand, UNION ALL does not remove duplicates and simply combines all the rows from the SELECT statements.
Here is an example to illustrate the difference:
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2
This will return a result set with distinct rows.
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2
This will return a result set with all rows, including duplicates.
Follow up 2: Can you provide an example of a UNION operation?
Answer:
Certainly! Here is an example of using UNION in SQL:
Let's say we have two tables, 'employees' and 'customers', with the following columns:
- employees: employee_id, first_name, last_name
- customers: customer_id, first_name, last_name
To combine the first names from both tables into a single result set, we can use the following query:
SELECT first_name FROM employees
UNION
SELECT first_name FROM customers
This will return a result set with all the distinct first names from both tables.
Follow up 3: What are the requirements for using UNION?
Answer:
To use the UNION operation in SQL, the following requirements must be met:
- The number of columns in the SELECT statements must be the same.
- The data types of the corresponding columns in the SELECT statements must be compatible.
- The columns in the SELECT statements must be in the same order.
If these requirements are not met, the UNION operation will result in an error.
Question 4: What is the INTERSECT operation in SQL?
Answer:
The INTERSECT operation in SQL is used to combine the result sets of two or more SELECT statements and return only the rows that are common to all result sets. It returns the intersection of the result sets.
Follow up 1: How does INTERSECT differ from UNION?
Answer:
The INTERSECT operation returns only the common rows between the result sets, while the UNION operation returns all the rows from both result sets, removing duplicates. In other words, INTERSECT performs an AND operation, while UNION performs an OR operation.
Follow up 2: Can you provide an example of an INTERSECT operation?
Answer:
Sure! Let's say we have two tables, 'TableA' and 'TableB', with the following data:
TableA:
id | name |
---|---|
1 | John |
2 | Mary |
TableB:
id | name |
---|---|
1 | John |
3 | Adam |
To find the common rows between the two tables, we can use the INTERSECT operation like this:
SELECT id, name FROM TableA
INTERSECT
SELECT id, name FROM TableB;
This will return the following result:
id | name |
---|---|
1 | John |
Follow up 3: What are the requirements for using INTERSECT?
Answer:
To use the INTERSECT operation in SQL, the following requirements must be met:
- The number of columns and their data types must be the same in all SELECT statements.
- The columns must be in the same order in all SELECT statements.
- The result sets must have the same number of columns.
- The result sets must be union-compatible, meaning that the data types of corresponding columns must be compatible.
If these requirements are not met, the INTERSECT operation will result in an error.
Question 5: What is the EXCEPT operation in SQL?
Answer:
The EXCEPT operation in SQL is used to return the distinct rows from the left input query that are not present in the right input query.
Follow up 1: How does EXCEPT differ from INTERSECT?
Answer:
The EXCEPT operation returns the distinct rows from the left input query that are not present in the right input query, while the INTERSECT operation returns the distinct rows that are common to both the left and right input queries.
Follow up 2: Can you provide an example of an EXCEPT operation?
Answer:
Certainly! Here's an example:
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;
This query will return the distinct rows from table1
that are not present in table2
based on the values of column1
and column2
.
Follow up 3: What are the requirements for using EXCEPT?
Answer:
To use the EXCEPT operation in SQL, the following requirements must be met:
- The number of columns and their data types must be the same in both the left and right input queries.
- The columns being compared must have compatible data types.
- The left and right input queries must have the same number of columns in the same order.