SQL Queries and Set Operations

Understanding SQL queries, set operations, and keywords.

SQL Queries and Set Operations Interview with follow-up questions

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.

Back to Top ↑

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;
Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

Follow up 3: What are the requirements for using UNION?

Answer:

To use the UNION operation in SQL, the following requirements must be met:

  1. The number of columns in the SELECT statements must be the same.
  2. The data types of the corresponding columns in the SELECT statements must be compatible.
  3. 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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

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
Back to Top ↑

Follow up 3: What are the requirements for using INTERSECT?

Answer:

To use the INTERSECT operation in SQL, the following requirements must be met:

  1. The number of columns and their data types must be the same in all SELECT statements.
  2. The columns must be in the same order in all SELECT statements.
  3. The result sets must have the same number of columns.
  4. 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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

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.

Back to Top ↑

Follow up 3: What are the requirements for using EXCEPT?

Answer:

To use the EXCEPT operation in SQL, the following requirements must be met:

  1. The number of columns and their data types must be the same in both the left and right input queries.
  2. The columns being compared must have compatible data types.
  3. The left and right input queries must have the same number of columns in the same order.
Back to Top ↑