sql employee table interview questions

SQL Interview – Employee Manager Hierarchy – Self Join

SQL Query Interview Questions for Freshers

Here is a list of top SQL query interview questions and answers for fresher candidates that will help them in their interviews. In these queries, we will focus on the basic SQL commands only.

Ques.1. Write an SQL query to fetch the EmpId and FullName of all the employees working under Manager with id – ‘986’. Ans. We can use the EmployeeDetails table to fetch the employee details with a where clause for the manager-

Ques.2. Write an SQL query to fetch the different projects available from the EmployeeSalary table. Ans. While referring to the EmployeeSalary table, we can see that this table contains project values corresponding to each employee, or we can say that we will have duplicate project values while selecting Project values from this table. So, we will use the distinct clause to get the unique values of the Project.

Ques.3. Write an SQL query to fetch the count of employees working in project ‘P1’. Ans. Here, we would be using aggregate function count() with the SQL where clause-

Ques.4. Write an SQL query to find the maximum, minimum, and average salary of the employees.Ans. We can use the aggregate function of SQL to fetch the max, min, and average values-

Ques.5. Write an SQL query to find the employee id whose salary lies in the range of 9000 and 15000. Ans. Here, we can use the ‘Between’ operator with a where clause.

Ques.6. Write an SQL query to fetch those employees who live in Toronto and work under manager with ManagerId – 321. Ans. Since we have to satisfy both the conditions – employees living in ‘Toronto’ and working in Project ‘P2’. So, we will use AND operator here-

Ques.7. Write an SQL query to fetch all the employees who either live in California or work under a manager with ManagerId – 321. Ans. This interview question requires us to satisfy either of the conditions – employees living in ‘California’ and working under Manager with ManagerId ‘321’. So, we will use the OR operator here-

Ques.8. Write an SQL query to fetch all those employees who work on Project other than P1. Ans. Here, we can use the NOT operator to fetch the rows which are not satisfying the given condition.

Or using the not equal to operator-

For the difference between NOT and <> SQL operators, check this link – Difference between the NOT and != operators.

Ques.9. Write an SQL query to display the total salary of each employee adding the Salary with Variable value. Ans. Here, we can simply use the ‘+’ operator in SQL.

Ques.10. Write an SQL query to fetch the employees whose name begins with any two characters, followed by a text “hn” and ending with any sequence of characters. Ans. For this question, we can create an SQL query using like operator with ‘_’ and ‘%’ wild card characters, where ‘_’ matches a single character and ‘%’ matches ‘0 or multiple characters’.

Ques.11. Write an SQL query to fetch all the EmpIds which are present in either of the tables – ‘EmployeeDetails’ and ‘EmployeeSalary’. Ans. In order to get unique employee ids from both the tables, we can use Union clause which can combine the results of the two SQL queries and return unique rows.

Ques.12. Write an SQL query to fetch common records between two tables. Ans. SQL Server – Using INTERSECT operator-

MySQL – Since MySQL doesn’t have INTERSECT operator so we can use the sub query-

Ques.13. Write an SQL query to fetch records that are present in one table but not in another table. Ans. SQL Server – Using MINUS- operator-

MySQL – Since MySQL doesn’t have MINUS operator so we can use LEFT join-

Ques.14. Write an SQL query to fetch the EmpIds that are present in both the tables – ‘EmployeeDetails’ and ‘EmployeeSalary. Ans. Using sub query-

Ques.15. Write an SQL query to fetch the EmpIds that are present in EmployeeDetails but not in EmployeeSalary. Ans. Using sub query-

Ques.16. Write an SQL query to fetch the employee full names and replace the space with ‘-’. Ans. Using ‘Replace’ function-

Ques.17. Write an SQL query to fetch the position of a given character(s) in a field. Ans. Using ‘Instr’ function-

Ques.18. Write an SQL query to display both the EmpId and ManagerId together. Ans. Here we can use the CONCAT command.

Ques.19. Write a query to fetch only the first name(string before space) from the FullName column of the EmployeeDetails table. Ans. In this question, we are required to first fetch the location of the space character in the FullName field and then extract the first name out of the FullName field. For finding the location we will use the LOCATE method in MySQL and CHARINDEX in SQL SERVER and for fetching the string before space, we will use the SUBSTRING OR MID method. MySQL – using MID

SQL Server – using SUBSTRING

Ques.20. Write an SQL query to upper case the name of the employee and lower case the city values. Ans. We can use SQL Upper and Lower functions to achieve the intended results.

Ques.21. Write an SQL query to find the count of the total occurrences of a particular character – ‘n’ in the FullName field. Ans. Here, we can use the ‘Length’ function. We can subtract the total length of the FullName field with a length of the FullName after replacing the character – ‘n’.

Ques.22. Write an SQL query to update the employee names by removing leading and trailing spaces. Ans. Using the ‘Update’ command with the ‘LTRIM’ and ‘RTRIM’ function.

Ques.23. Fetch all the employees who are not working on any project. Ans. This is one of the very basic interview questions in which the interviewer wants to see if the person knows about the commonly used – Is NULL operator.

Ques.24. Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000. Ans. Here, we will use BETWEEN in the ‘where’ clause to return the EmpId of the employees with salary satisfying the required criteria and then use it as subquery to find the fullName of the employee from EmployeeDetails table.

Ques.25. Write an SQL query to find the current date-time. Ans. MySQL-

Ques.26. Write an SQL query to fetch all the Employees details from EmployeeDetails table who joined in the Year 2020. Ans. Using BETWEEN for the date range ’01-01-2020′ AND ’31-12-2020′-

Also, we can extract year part from the joining date (using YEAR in mySQL)-

Ques.27. Write an SQL query to fetch all employee records from EmployeeDetails table who have a salary record in EmployeeSalary table. Ans. Using ‘Exists’-

Ques.28. Write an SQL query to fetch project-wise count of employees sorted by project’s count in descending order. Ans. The query has two requirements – first to fetch the project-wise count and then to sort the result by that count. For project-wise count, we will be using the GROUP BY clause and for sorting, we will use the ORDER BY clause on the alias of the project-count.

Ques.29. Write a query to fetch employee names and salary records. Display the employee details even if the salary record is not present for the employee. Ans. This is again one of the very common interview questions in which the interviewer just wants to check the basic knowledge of SQL JOINS. Here, we can use left join with EmployeeDetail table on the left side of the EmployeeSalary table.

Ques.30. Write an SQL query to join 3 tables. Ans. Considering 3 tables TableA, TableB, and TableC, we can use 2 joins clauses like below-

Basic SQL Interview Questions

All set to kickstart your career in SQL? Look no further and start your professional career with these SQL interview questions for freshers. We will start with the basics and slowly move towards slightly advanced questions to set the pace. If you are an experienced professional, this section will help you brush up on your SQL skills.

Toptal sourced essential questions that the best SQL developers and engineers can answer. Driven from our community, we encourage experts to submit questions and offer feedback.

Im hiring

I’m looking for work

What does UNION do? What is the difference between UNION and UNION ALL?

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

List and explain the different types of JOIN clauses supported in ANSI-standard SQL.

ANSI-standard SQL specifies five types of JOIN clauses as follows:

Given the following tables:

What will be the result of the query below?

Explain your answer and also provide an alternative version of this query that will avoid the issue that it exposes.

Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN condition contains any values that are null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races table.

Knowing this, a query that avoids this issue would be as follows:

Note, this is assuming the standard SQL behavior that you get without modifying the default ANSI_NULLS setting.

Apply to Join Toptals Development Network

and enjoy reliable, steady, remote Freelance SQL Developer Jobs

Given two tables created and populated as follows:

What will the result be from the following query:

The result of the query will be as follows:

The EXISTS clause in the above query is a red herring. It will always be true since ID is not a member of dbo.docs. As such, it will refer to the envelope table comparing itself to itself!

The idnum value of NULL will not be set since the join of NULL will not return a result when attempting a match with any value of envelope.

Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name).

If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in the result of the following SQL query:

The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.

Given two tables created as follows

Write a query to fetch values in table test_a that are and not in test_b without using the NOT keyword.

Note, Oracle does not support the above INSERT syntax, so you would need this instead:

In SQL Server, PostgreSQL, and SQLite, this can be done using the except keyword as follows:

In Oracle, the minus keyword is used instead. Note that if there are multiple columns, say ID and Name, the column should be explicitly stated in Oracle queries: Select ID from test_a minus select ID from test_b

MySQL does not support the except function. However, there is a standard SQL solution that works in all of the above engines, including MySQL:

Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer.

(Note: You may assume that there are at least 10 records in the Employee table.)

This can be done as follows:

This works as follows:

First, the SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC query will select the top 10 salaried employees in the table. However, those salaries will be listed in descending order. That was necessary for the first query to work, but now picking the top 1 from that list will give you the highest salary not the the 10th highest salary.

Therefore, the second query reorders the 10 records in ascending order (which the default sort order) and then selects the top record (which will now be the lowest of those 10 salaries).

Not all databases support the TOP keyword. For example, MySQL and PostreSQL use the LIMIT keyword, as follows:

Or even more concisely, in MySQL this can be:

And in PostgreSQL this can be:

Write a SQL query using UNION ALL (not UNION) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?

You can avoid duplicates using UNION ALL and still run much faster than UNION DISTINCT (which is actually same as UNION) by running a query like this:

The key is the AND a!=X part. This gives you the benefits of the UNION (a.k.a., UNION DISTINCT) command, while avoiding much of its performance hit.

Given the following tables:

Write a query to to get the list of users who took the a training lesson more than once in the same day, grouped by user and training lesson, each ordered from the most recent lesson date to oldest date.

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.

In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” (located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is run.

List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows:

Given a table dbo.users where the column user_id is a unique numeric identifier, how can you efficiently select the first 100 odd user_id values from the table?

(Assume the table contains well over 100 records with odd user_id values.)

SELECT TOP 100 user_id FROM dbo.users WHERE user_id % 2 = 1 ORDER BY user_id

What are the NVL and the NVL2 functions in SQL? How do they differ?

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null.

With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1.

With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

How can you select all the even number records from a table? All the odd number records?

To select all the even number records from a table:

To select all the odd number records from a table:

What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

What is the difference between the WHERE and HAVING clauses?

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

However, when GROUP BY is used:

Given a table Employee having columns empName and empId, what will be the result of the SQL query below?

“Order by 2” is only valid when there are at least two columns being used in select statement. However, in this query, even though the Employee table has 2 columns, the query is only selecting 1 column name, so “Order by 2” will cause the statement to throw an error while executing the above sql query.

What will be the output of the below query, given an Employee table having 10 records?

This query will return 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.

Imagine a single column in a table that is populated with either a single digit (0-9) or a single character (a-z, A-Z). Write a SQL query to print ‘Fizz’ for a numeric value or ‘Buzz’ for alphabetical value for all values in that column.

Example:

[d, x, T, 8, a, 9, 6, 2, V]

…should output:

[Buzz, Buzz, Buzz, Fizz, Buzz,Fizz, Fizz, Fizz, Buzz]

What is the difference between char and varchar2?

When stored in a database, varchar2 uses only the allocated space. E.g. if you have a varchar2(1999) and put 50 bytes in the table, it will use 52 bytes.

But when stored in a database, char always uses the maximum length and is blank-padded. E.g. if you have char(1999) and put 50 bytes in the table, it will consume 2000 bytes.

Write an SQL query to display the text CAPONE as:

Or in other words, an SQL query to transpose text.

In Oracle SQL, this can be done as follows:

Can we insert a row for identity column implicitly?

Yes, like so:

Given this table:

What will be the output of below snippet?

Table is as follows:

Print the rows which have ‘Yellow’ in one of the columns C1, C2, or C3, but without using OR.

Write a query to insert/update Col2’s values to look exactly opposite to Col1’s values.

Or if the type is numeric:

How do you get the last id without the max function?

In MySQL:

In SQL Server:

What is the difference between IN and EXISTS?

IN:

EXISTS:

Suppose in a table, seven records are there.

The column is an identity column.

Now the client wants to insert a record after the identity value 7 with its identity value starting from 10.

Is it possible? If so, how? If not, why not?

Yes, it is possible, using a DBCC command:

How can you use a CTE to return the fifth highest (or Nth highest) salary from a table?

Given the following table named A:

Write a single query to calculate the sum of all positive values of x and he sum of all negative values of x.

Given the table mass_table:

Write a query that produces the output:

Consider the Employee table below.

Write a query to generate below output:

How do you copy data from one table to another table ?

Find the SQL statement below that is equal to the following: SELECT name FROM customer WHERE state = VA;

Given these contents of the Customers table:

Here is a query written to return the list of customers not referred by Jane Smith:

What will be the result of the query? Why? What would be a better way to write it?

Although there are 4 customers not referred by Jane Smith (including Jane Smith herself), the query will only return one: Pat Richards. All the customers who were referred by nobody at all (and therefore have NULL in their ReferredBy column) don’t show up. But certainly those customers weren’t referred by Jane Smith, and certainly NULL is not equal to 2, so why didn’t they show up?

SQL Server uses three-valued logic, which can be troublesome for programmers accustomed to the more satisfying two-valued logic (TRUE or FALSE) most programming languages use. In most languages, if you were presented with two predicates: ReferredBy = 2 and ReferredBy <> 2, you would expect one of them to be true and one of them to be false, given the same value of ReferredBy. In SQL Server, however, if ReferredBy is NULL, neither of them are true and neither of them are false. Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN.

The query should be written in one of two ways:

…or:

Watch out for the following, though!

This will return the same faulty set as the original. Why? We already covered that: Anything compared to NULL evaluates to the third value in the three-valued logic: UNKNOWN. That “anything” includes NULL itself! That’s why SQL Server provides the IS NULL and IS NOT NULL operators to specifically check for NULL. Those particular operators will always evaluate to true or false.

Even if a candidate doesn’t have a great amount of experience with SQL Server, diving into the intricacies of three-valued logic in general can give a good indication of whether they have the ability learn it quickly or whether they will struggle with it.

Given a table TBL with a field Nmbr that has rows with the following values:

1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1

Write a query to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.

This can be done as follows:

Suppose we have a Customer table containing the following data:

Write a single SQL statement to concatenate all the customer names into the following single semicolon-separated string:

This is close, but will have an undesired trailing ;. One way of fixing that could be:

In PostgreSQL one can also use this syntax to achieve the fully correct result:

How do you get the Nth-highest salary from the Employee table without a subquery or CTE?

This will give the third-highest salary from the Employee table. Accordingly we can find out Nth salary using LIMIT (N-1),1.

But MS SQL Server doesn’t support that syntax, so in that case:

OFFSET’s parameter corresponds to the (N-1) above.

How to find a duplicate record?

 SELECT name, COUNT(email) FROM users GROUP BY email HAVING COUNT(email) > 1 

 SELECT name, email, COUNT(*) FROM users GROUP BY name, email HAVING COUNT(*) > 1 

Considering the database schema displayed in the SQLServer-style diagram below, write a SQL query to return a list of all the invoices. For each invoice, show the Invoice ID, the billing date, the customer’s name, and the name of the customer who referred that customer (if any). The list should be ordered by billing date.

This question simply tests the candidate’s ability take a plain-English requirement and write a corresponding SQL query. There is nothing tricky in this one, it just covers the basics:

Note that this query will not return Invoices that do not have an associated Customer. This may be the correct behavior for most cases (e.g., it is guaranteed that every Invoice is associated with a Customer, or unmatched Invoices are not of interest). However, in order to guarantee that all Invoices are returned no matter what, the Invoices table should be joined with Customers using LEFT JOIN:

There is more to interviewing than tricky technical questions, so these are intended merely as a guide. Not every “A” candidate worth hiring will be able to answer them all, nor does answering them all guarantee an “A” candidate. At the end of the day, hiring remains an art, a science — and a lot of work.

Tired of interviewing candidates? Not sure what to ask to get you a top hire?

Let Toptal find the best people for you.

Our Exclusive Network of SQL Developers

Looking to land a job as a SQL Developer?

Let Toptal find the right job for you.

Job Opportunities From Our Network

Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of Toptal, LLC.

Duy has 20+ years of software development experience using Microsofts technology stack, primarily with .NET. He builds high-quality and high-performance back-end systems and creates web applications with good UX using modern frameworks like Angular or React. Duys proactive and results-oriented with a love of not only writing but also removing code to ensure efficient, stable, and flexible software is delivered within the projected scope and budget.

Matthew has over 15 years of experience in database management and software development, with a strong focus on full-stack web applications. He specializes in Django and Vue.js with expertise deploying to both server and serverless environments on AWS. He also works with relational databases and large datasets.

Vedansh is a senior software engineer at HackerRank and has 5+ years of experience in writing scalable, maintainable code in Rails and Python. He has built apps that are being used by 9 million+ developers around the world. He strongly believes in TDD and communicates really well and has worked with teams of different sizes across the globe. He also comes with a strong background in freelancing with various startups in the valley.

Looking for SQL Developers? Check out Toptal’s SQL developers.

Toptal Connects the Top 3% of Freelance Talent All Over The World.

What is RDBMS? How is it different from DBMS?

RDBMS stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stores data in the form of a collection of tables, and relations can be defined between the common fields of these tables. Most modern database management systems like MySQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS.

FAQ

What are good SQL interview questions?

• Q1. What is normalization? What are the different normalizations?
• Q2. What is denormalization?
• Q3. What is collation? What are the different types of collation sensitivity?
• Q4. What do you understand about CASE statements?
• Q5. What are some common SQL commands?

How do I practice SQL queries in interview?

1. SQL Exercise 1 – Write a Statement. …
2. SQL Exercise 2 – Write a Statement. …
3. SQL Exercise 3 – Find the Error. …
4. SQL Exercise 4 – Find the Result. …
5. SQL Exercise 5 – Write a Query. …
6. SQL Exercise 6 – Write a Date Query. …
7. SQL Exercise 7 – Write a Query. …
8. SQL Exercise 8 – Find and Delete Duplicates.

What are the 3 types of SQL?