RDBMS is one of the most popular databases today, so most jobs require SQL skills. What are the most common SQL (Structured Query Language) interview questions? That’s what this article is all about. If you want to learn everything you need to know about SQL, Oracle, MS SQL Server, and MySQL databases, this article is it. Our article on the Top 115 SQL Interview Questions is the best place to get help with your SQL interview prep.
SQL union queries allow you to combine the result sets from multiple SELECT statements into a single result set. This powerful feature enables you to merge data from different sources for comprehensive analysis and reporting.
However, SQL union comes with specific rules and syntax requirements. A strong understanding of unions is key to applying them effectively in complex business scenarios.
In this comprehensive guide, I’ll cover the most common SQL union interview questions that assess your practical skills. Mastering these SQL union operations will boost your confidence for technical interviews and demonstrate advanced database management proficiency.
What is a SQL Union?
A SQL union combines rows from two or more SELECT queries and returns a single result set, The union removes duplicate rows between these queries to give distinct values
Key things to know
-
The SELECT statements must have the same number of columns selected, in the same order, with similar data types.
-
Union queries are commonly used to merge data from identically structured tables stored separately.
-
By default, union eliminates duplicate rows. To allow duplicates, use UNION ALL instead of just UNION.
Common SQL Union Interview Questions
Let’s go through some of the most frequently asked SQL union interview questions and sample answers:
Q1. What is the difference between UNION and UNION ALL?
UNION combines result sets and removes duplicate rows, whereas UNION ALL simply stacks one result set after the other without removing duplicates.
UNION performs extra processing to identify and discard duplicates, so UNION ALL is faster. Use UNION ALL when duplicate values are not an issue.
Q2. Can UNION be used on tables with a different number of columns?
No, UNION requires each SELECT query to have the same number of columns selected, in the same order. The column data types must also match or be compatible for implicit conversion.
If the tables have different columns, you can use joins instead to combine them.
Q3. How does SQL handle NULL values with UNION?
SQL treats NULL values as equal during a union. If two rows are entirely NULL, they will be considered duplicates. However, a NULL value and non-NULL value in the same column position are seen as different.
Q4. Can we use aggregation functions like COUNT, SUM etc. in a UNION?
Yes, you can apply aggregations like COUNT, SUM, AVG on each individual SELECT query within the union. The column names and types must match across the queries.
SELECT COUNT(*) AS "Orders" FROM Orders UNION SELECT COUNT(*) AS "Orders" FROM Returns;This shows total order and return counts.
Q5. How do you sort a SQL UNION result set?
Use ORDER BY at the very end to sort the final union result set. Don’t apply ORDER BY directly after each SELECT within the union, as that will sort them individually before the union occurs.
SELECT col FROM table1 UNION SELECT col FROM table2ORDER BY col ASC;Q6. Can we use UNION in a subquery?
Absolutely, UNION can be used within a subquery like any other SELECT query. The individual SELECTs within the UNION must abide by the usual requirements of same number of columns and data types.
The overall subquery can then be used for filtering, joins or other operations.
Q7. What are some SQL UNION performance considerations?
Unions involve additional processing to remove duplicates and merge result sets. Some performance best practices include:
-
Use UNION ALL instead of UNION to avoid duplicate removal if possible.
-
Ensure proper indexes exist on the joined columns.
-
Keep the individual SELECT queries small through filters.
-
Put the most restrictive SELECT first in the union order.
Q8. How do you troubleshoot an error with UNION?
Some steps to debug SQL union errors:
-
Check the number of columns selected in each query matches.
-
Verify column data types are compatible.
-
Look for ambiguous column names and use aliases.
-
Handle NULL values through ISNULL or COALESCE.
-
Simplify individual SELECTs to isolate the problem source.
When to Use SQL Union in Queries
Now that we’ve covered SQL union concepts, let’s explore some common use cases:
Consolidating data from normalized tables – If data is split across multiple tables for normalization, union the tables to view integrated results.
Generating master lookup lists – Unions can create distinct consolidated lists from multiple sources, like product catalogs.
Appending query results – Add supplementary result sets using UNION ALL, like aggregations based on a main query.
Comparing similar datasets – Unions enable easy comparisons between result sets from equivalent sources.
Advanced Examples
The questions so far cover core concepts, but you need to demonstrate applying unions to realistic problems.
Here are some examples of more complex union queries:
Merge customer order history across countries
SELECT ID, Name, OrderDate FROM US_Orders UNION SELECT ID, Name, OrderDate FROM EU_Orders;Combine product search results from multiple vendors
SELECT product_id, name, price FROM amazon_products WHERE product_id IN (search_terms)UNION SELECT product_id, name, price FROM ebay_products WHERE product_id IN (search_terms);Aggregate user counts from different applications
SELECT 'EmailUsers' AS app, COUNT(ID) AS user_count FROM email_usersUNION ALLSELECT 'ForumUsers' AS app, COUNT(ID) AS user_count FROM forum_users; Summary
SQL union queries are immensely useful for integrating data from multiple sources into unified result sets.
Mastering union syntax, duplicate handling, sort order, performance implications and advanced use cases will prepare you for complex database management and analytics challenges.
Q9 How can you fetch alternate records from a table?
You can fetch alternate records i. e both odd and even row numbers. For example- To display even numbers, use the following command:
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
Now, to display odd numbers:
Q6 Explain different types of Normalization.
There are many successive levels of normalization. These are called normal forms. Each consecutive normal form depends on the previous one. The first three normal forms are usually adequate.
Normal Forms are used in database tables to remove or decrease duplication. The following are the many forms:
First Normal Form: A relation is in first normal form when all of its attributes have only one value. The first normal form is broken when a relation has a composite or multi-valued property.
Second Normal Form:
It’s in second normal form if it meets the conditions for first normal form and doesn’t have any partial dependencies. It is not possible for a relation in 2NF to have any partial dependence, which means that it does not have any non-prime attributes that depend on any suitable subset of any table candidate key. Often, the problem may be solved by setting a single column Primary Key.
Third Normal Form: A relation is in the third normal form if it meets the conditions for the second normal form and there is no transitive dependency.
Q63. What is OLTP?
Online transaction processing, or OLTP, lets huge groups of people do a huge number of database transactions at the same time, usually over the internet. A database transaction occurs when data in a database is changed, inserted, deleted, or queried.
What are the differences between OLTP and OLAP?
OLTP stands for online transaction processing, whereas OLAP stands for online analytical processing. OLTP is an online database modification system, whereas OLAP is an online database query response system.
Q64. How to create empty tables with the same structure as another table?
To create empty tables:
If you use the INTO operator to get records from one table and put them in a new table while setting the WHERE clause to false for all entries, you can make empty tables with the same structure. Because of this, SQL makes a new table with the same structure to hold the entries that were fetched, but since the WHERE clause is still in effect, nothing is stored in the new table.
Q65. What is PostgreSQL?
In 1986, a team lead by Computer Science Professor Michael Stonebraker created PostgreSQL under the name Postgres. It was made to help developers make enterprise-level apps by making sure that systems can handle errors and keep data safe. PostgreSQL is an enterprise-level, versatile, resilient, open-source, object-relational database management system that supports variable workloads and concurrent users. The international developer community has constantly backed it. PostgreSQL has achieved significant appeal among developers because to its fault-tolerant characteristics. It’s a very trustworthy database management system, thanks to more than 20 years of community work that made it so strong, honest, and correct. Many online, mobile, geospatial, and analytics applications utilise PostgreSQL as their primary data storage or data warehouse.
Q66. What are SQL comments?
SQL Comments are used to clarify portions of SQL statements and to prevent SQL statements from being executed. Comments are quite important in many programming languages. The comments are not supported by a Microsoft Access database. As a result, the Microsoft Access database is used in the examples in Mozilla Firefox and Microsoft Edge. Single Line Comments: It starts with two consecutive hyphens (–). Multi-line Comments: It starts with /* and ends with */.
Let’s move to the next question in this SQL Interview Questions.
Q67. What is the difference between the RANK() and DENSE_RANK() functions?
The RANK() function in the result set defines the rank of each row within your ordered partition. If two rows have the same rank, the next number in the list will be the rank before it plus the number of duplicates. If we have three records at rank 4, for example, the next level indicated is 7.
The DENSE_RANK() function gives each row in a partition a unique rank based on the value of the given column, with no gaps. It always indicates a ranking in order of precedence. If two rows already have the same rank, this function will give them the same rank. The next rank is the next number in the series. If we have three records at rank 4, for example, the next level indicated is 5.
Q68. What is SQL Injection?
SQL injection is a type of bug in website and web app code that lets attackers take over back-end processes and get to, delete, or change sensitive database data. In this method, bad SQL statements are put into a database entry field, and when they are run, the database is open to an attacker. This method is often used to get to private information and carry out database management tasks by using data-driven apps. SQLi attack is another name for it.
The following are some examples of SQL injection:
- Getting access to private information in order to change an SQL query to get the results you want
- The goal of a UNION attack is to steal information from several database tables.
- Look at the database to find out what version it is and how it is organized.
Q69. How many Aggregate functions are available in SQL?
SQL aggregate functions provide information about a database’s data. AVG, for example, returns the average of a database column’s values.
SQL provides seven (7) aggregate functions, which are given below:
AVG(): returns the average value from specified columns. COUNT(): returns the number of table rows, including rows with null values. MAX(): returns the largest value among the group. MIN(): returns the smallest value among the group. SUM(): returns the total summed values(non-null) of the specified column. FIRST(): returns the first value of an expression. LAST(): returns the last value of an expression.
Q70. What is the default ordering of data using the ORDER BY clause? How could it be changed?
The ORDER BY clause in MySQL can be used without the ASC or DESC modifiers. If this attribute is not present in the ORDER BY clause, the sort order is set to ASC, which stands for ascending order.
Q71. How do we use the DISTINCT statement? What is its use?
The SELECT query is used with the SQL DISTINCT keyword to get rid of all duplicate records and return only the unique ones. There may be times when a table has several duplicate records. The DISTINCT clause in SQL is used to eliminate duplicates from a SELECT statement’s result set.
Q72. What are the syntax and use of the COALESCE function?
From a succession of expressions, the COALESCE function returns the first non-NULL value. The expressions are run in the order they are given, and the function returns the first value that is not null. Only if all of the inputs are null does the COALESCE method return NULL.
The syntax of COALESCE function is COALESCE (exp1, exp2, …. expn)
8. union vs uninonall (Top 5 SQL Interview Questions)| GeeksforGeeks
FAQ
What is SQL UNION used for?
What is the difference between UNION and UNION all interview questions?
What interview questions are related to joins and subqueries in SQL?
Here are 50 interview questions related to Joins and Subqueries in SQL: 1. What is an SQL JOIN, and why is it important in database queries? Answer: An SQL JOIN is used to combine rows from two or more tables based on a related column between them. It’s crucial for retrieving data from multiple tables. 2.
How to prepare for a SQL interview?
Because of that, preparing for the interview with straightforward SQL questions is not enough. You should also brush up on the tricky questions, as the interviewers like to use them to try and catch you off guard. If you like learning SQL using hands-on exercises, then you’ve got to try All Forever SQL Package.
What questions are asked in a SQL interview?
Advanced queries. You may be asked about subqueries, both nested and correlated, as well as how to perform specific tasks like finding the nth highest value in a column. To kick off, before asking you technical questions, your interviewer may ask you some general questions about your overall experience with SQL.
How to write a SQL interview question for a freelancer?
Some of the common tricky SQL interview questions for experienced users are presented below. Write a query that selects all freelancers along with their task info: Include freelancers that don’t have any tasks assigned. Dataset: The dataset is of a company that employs freelancers on certain tasks. It consists of three tables.
