Online try-it tool –  http://sqlfiddle.com/#!4/9de39/590



1. Find the result of the below query, Given the following tables:

sql> SELECT * FROM authors;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John     |
|  2 | Peter    |
|  3 | Steve  |
|  4 | Shane  |
+----+--------------+

sql> SELECT * FROM books;
+----+----------------+-----------+
| id | book          | author_id |
+----+----------------+-----------+
|  1 | book1          |  2        |
|  2 | book2          |  3        |
|  3 | book3 .        |  2        |
|  4 | book4          |  NULL     |
+----+----------------+-----------+

What will be the result of the query below?

SELECT * FROM authors WHERE id NOT IN 
                      (SELECT author_id FROM books)

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

 

Solution: 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 id matches on both tables.

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

SELECT * FROM authors WHERE id NOT IN 
      (SELECT author_id FROM books WHERE author_id IS NOT null)

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

2.  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:

Select * From Emp, Dept

Explain your answer.

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

3.

Given two tables created as follows

create table test_a(id numeric);

create table test_b(id numeric);
insert into test_a(id) values (10);
insert into test_a(id) values (20);
insert into test_a(id) values (30);
insert into test_a(id) values (40);
insert into test_a(id) values (50);
insert into test_b(id) values (10);
insert into test_b(id) values (30);
insert into test_b(id) values (50);

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

Solution:  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

4.

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.)

Solution :This can be done as follows:

SELECT TOP (1) Salary FROM
(
    SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC
) AS Emp ORDER BY Salary

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  use the LIMIT keyword, as follows:

SELECT Salary FROM
(
    SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 10
) AS Emp ORDER BY Salary LIMIT 1;

Or even more concisely, in MySQL this can be:

SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 9,1;

Oracle does not support TOP. Use ROWNUM 

SELECT * FROM your_table
WHERE ROWNUM <= 5

and apply the same logic.

Write SQL query to find second highest salary in employee table? 

You can also use keyword’s like TOP or LIMIT if you are using SQL Server or MySQL

The simplest way to find 2nd highest salary is following:

SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)

This query first finds maximum salary and then exclude that from the list and again finds maximum salary. Obviously second time, it would be second highest salary.

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

Solution:

  • 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.

Oracle-https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm#i17492

To explain a SQL statement, use the EXPLAIN PLAN FOR clause immediately before the statement. For example:

EXPLAIN PLAN FOR
  SELECT last_name FROM employees;

This explains the plan into the PLAN_TABLE table. You can then select the execution plan from PLAN_TABLE. See “Displaying PLAN_TABLE Output”.

6. SQL interview questions – watch in video below- 

https://www.youtube.com/watch?v=fvPddKyHxpQ&list=PL6n9fhu94yhXcztdLO7i6mdyaegC8CJwR

7. Union vs UnionAll, Count(*) vs count

8. View vs Materialized view

9. Find second highest repeated/duplicated salary

10. How database behaves in multithreading environment?

11. Questions related to Join and scenarios.

12. Aggregate function related questions

13. Analytical function related questions

14. Having vs Where

Reference: https://www.toptal.com/sql/interview-questions