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
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.
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
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
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
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?
- 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.
To explain a SQL statement, use the
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-
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