SQL Tricky Interview Questions Preparation Course


This course contains tricky and nasty SQL interview questions that an interviewer asks. It is a compilation of advanced SQL interview questions after attending dozens of technical interviews in top-notch companies like- Oracle, Google, Ebay, Amazon etc. Each question is accompanied with an answer so that you can prepare for job interview in short time.

Often, these questions and concepts are used in our daily programming work. But these are most helpful when an Interviewer is trying to test your deep knowledge of SQL concepts.

Join the course now for a very low price!.

What is the biggest benefit of this course to me?

The biggest benefit of this course is that you will be able to handle interview questions on SQL. I will explain you questions and their answers in detail. So you will be well prepared for your next career jump.

What are the topics covered in this course?

We cover a wide range of topics in this course. We have questions on SQL group by, date operations etc.

How will this course help me?

By attending this course, you do not have to spend time searching the Internet for tricky SQL interview questions. We have already compiled the list of the most popular and the latest SQL Interview questions.

Are there answers in this course?

Yes, in this course each question is followed by an answer. So you can save time in interview preparation.

What is the best way of viewing this course?

You have to just watch the course from beginning to end. Once you go through all the videos, try to answer the questions in your own words. Also mark the questions that you could not answer by yourself. Then, in second pass go through only the difficult questions. After going through this course 2-3 times, you will be well prepared to face a technical interview in SQL language.

What is the level of questions in this course?

This course contains questions that are good for a Fresher to an Architect level. The difficulty level of question varies in the course from a Fresher to an Experienced professional.

What happens if SQL tricky questions list changes in future?

From time to time, we keep adding more questions to this course. Our aim is to keep you always updated with the latest interview questions on SQL.

What are the sample questions covered in this course?

Sample questions covered in this course are as follows:

  1. Write SQL query to get the second highest salary among all Employees?
  2. How can we retrieve alternate records from a table in Oracle?
  3. Write a SQL Query to find Max salary and Department name from each department.
  4. Write a SQL query to find records in Table A that are not in Table B without using NOT IN operator.
  5. What is the result of following query?
  6. Write SQL Query to find employees that have same name and email.
  7. Write a SQL Query to find Max salary from each department.
  8. Write SQL query to get the nth highest salary among all Employees.
  9. How can you find 10 employees with Odd number as Employee ID?
  10. Write a SQL Query to get the names of employees whose date of birth is between 01/01/1990 to 31/12/2000.
  11. Write a SQL Query to get the Quarter from date.
  12. Write Query to find employees with duplicate email.
  13. Is it safe to use ROWID to locate a record in Oracle SQL queries?
  14. What is a Pseudocolumn?
  15. What are the reasons for de-normalizing the data?
  16. What is the feature in SQL for writing If/Else statements?
  17. What is the difference between DELETE and TRUNCATE in SQL?
  18. What is the difference between DDL and DML commands in SQL?
  19. Why do we use Escape characters in SQL queries?
  20. What is the difference between Primary key and Unique key in SQL?
  21. What is the difference between INNER join and OUTER join in SQL?
  22. What is the difference between Left OUTER Join and Right OUTER Join?
  23. What is the datatype of ROWID?
  24. What is the difference between where clause and having clause?
  25. What is cardinality in SQL?
  26. What is Merge statement in SQL?
  27. What is the difference between UNION and UNION ALL?
  28. What will be the result of following query?
  29. What is the wrong with this SQL query?
  30. What is wrong with this query to get the list of employees not in Dept 1?
  31. What is the use of Execution plan in SQL?
  32. How many records are returned by following query?
  33. Write a query for this problem?
  34. Write SQL Query to get Employee Name, Manager ID and number of employees in the department?
  35. Write SQL Query to find duplicate rows in a database?
  36. Write SQL query to delete duplicate rows in a table?
  37. Why is the difference between NVL and NVL2 functions in SQL?
  38. What are ACID properties in a SQL transaction?
  39. What is the main difference between RANK and DENSE_RANK functions in Oracle?
  40. What is the use of WITH clause in SQL?
  41. Which SQL feature can be used to view data in a table sequentially?
  42. Write SQL Query to get Student Name and number of Students in same grade.
  43. Write SQL Query to get the list of grades with total score more than average score.
  44. What are the differences between CASE and DECODE in SQL?
  45. Write a Query to get Unique names of products without using DISTINCT keyword.
  46. Write a SQL query to maximum Zipcode from a table without using MAX or MIN aggregate functions.
  47. Given a list of student names and grade. Write a query to print a comma separated list of student names in a grade.
  48. What is the difference between Correlated and Un-correlated Sub query?
  49. Given an Employee table with Manager_ID as column, print First name, Manager ID and Level of employees in Organization Structure?
  50. Write a query to create an empty table from an existing table?

What are the requirements?

  1. Familiar with basic SQL
  2. Able to run basic SQL query

What am I going to get from this course?

  1. Learn important concepts of SQL
  2. Handle tricky SQL interview questions
  3. Implement complex SQL queries
  4. Answer popular interview questions on SQL
  5. Demand higher salary or promotion based on the knowledge gained

What is the target audience?

  • Data Engineer, DBA
  • Software Engineer, Sr. Software Engineer, Member Technical Staff, Expert
  • Operations Engineer
  • Business
      Analyst
    1. Anyone who wants to learn SQL
    2. Anyone who wants to prepare for SQL interview questions

Join the course now for a very low price!.

Write a SQL query to maximum Zipcode from a table without using MAX or MIN aggregate functions.

Consider Zipcode_list table with column Zipcode
ZIPCODE
7500
7525
7550
7600
7575

Answer: Point to be noted is that the Maximum zipcode is not smaller than any Zipcode in the list.

We can use self join to find the list of Zipcodes that are smaller than at least one other Zipcode. Once we get that list, we just use NOT IN to find the Zipcode from Zipcode_list that does not exist in this smaller list. That will be the maximum Zipcode with no Zipcode bigger than it.

Query will be as follows:

SELECT DISTINCT Zipcode
FROM Zipcode_list
WHERE Zipcode NOT IN (
SELECT Smaller_list.Zipcode
FROM Zipcode_list AS Larger_list
JOIN Zipcode_list AS Smaller_list
ON Smaller_list.Zipcode < Largerlist.Zipcode
)

Given a list of student names and grade. Write a query to print a comma separated list of student names in a grade.

Students
Grade | Name
1 | John
1 | George
1 | Jane
2 | Smith
2 | Anne
2 | Scott
3 | Larry
3 | Bill

Answer:
We can use LISTAGG function in Oracle for this purpose. It can transpose rows to column type values. We can set the delimiter as comma in LISTAGG function. And then we can group the students by using Grade in GROUP BY clause.

Query will be as follows:

SELECT grade, LISTAGG(name, ‘,’) WITHIN GROUP (ORDER BY name) AS Students
FROM student
GROUP BY grade;

Grade Students


1 John,George,Jane
2 Smith,Anne,Scott
3 Larry,Bill

What is the difference between Correlated and Un-correlated Sub query?

When we write a subquery in such a way that inner subquery and outer main query are interdependent, then we call it s correlated Sub query. In this case, for executing every row of inner query, the outer query is also executed. The inner query needs data from the outer query for its execution.

E.g.
SELECT e.emp_name
FROM employee e
WHERE e.id = (SELECT d.emp_id
FROM dept d
WHERE d.dept_id = e.dept_id);

In a non-correlated subquery, inner subquery has no dependency on outer query.

Given an Employee table with Manager_ID as column, print First name, Manager ID and Level of employees in Organization Structure?

Answer: In Oracle, we can CONNECT BY clause for this.

The starting point will be the employee who does not have a manager. Below that we can connect the employee IDs with their Manager IDs and keep printing the records.

Oracle provides a pseudocolumn LEVEL that gives the level of each record in hierarchy.

Query will be as follows:

SELECT f_name, emp_id, manager_id, LEVEL
FROM Employee
START WITH emp_id = 10
CONNECT BY PRIOR emp_id = manager_id;
F_NAME   EMP_ID        MANAGER_ID LEVEL
John                10                                          1
George           14                   10                    2
Jill                   16                    14                   3
Bill                   15                    14                   3
Jay                  18                   14                    3

 

Write a query to create an empty table from an existing table?

Answer: An empty table is a table with same structure as the given table. But it does not contain records.

To create an empty table, we have to run a SELECT query so that no records are returned. But we can use the result of this query in CREATE statement to create an empty table.

To get no records in SELECT query, we can give a false condition like 1 > 2 in WHERE clause.

Query will be as follows:

CREATE TABLE Test_table AS
SELECT * from Src_table
WHERE 1 > 2;

Write SQL Query to get the list of grades with total score more than average score.

Consider Student and Grade tables
Student: ID, name, grade_ID, score
Grade: ID, grade_num

Answer: We can use WITH clause to get the total score in each grade. We can also use WITH clause to get the average score among all grades. Then we can use the two sub-queries to get the list of GRADES with Score total more than average score.

Query will be as follows:

WITH
grade_score AS (
SELECT grade_num, SUM(s.score) grade_total
FROM student s, grade g
WHERE s.grade_ID = g.ID
GROUP BY grade_num),
avg_score AS (
SELECT SUM(grade_total)/COUNT(*) avg
FROM grade_score)
SELECT *
FROM grade_score
WHERE grade_total > (SELECT avg FROM avg_score)
ORDER BY grade_num;

What are the differences between CASE and DECODE in SQL?

Main differences between Case and Decode statements are:

  • Easier to Read: CASE is more flexible and easier to read than DECODE.
  • ANSI Compatible: CASE is an ANSI standard. But DECODE is internal to Oracle.
  • Location: DECODE is used only inside SQL statement. We can use CASE any where in SQL, even as a parameter of a function/procedure.
  • Check: DECODE works on the basis of an equality check. CASE can do many types of logical comparisons like < > etc.
  • Decision Making: We can not use complex decision making statements in a DECODE function. We cannot do decode( price = 100,’cheap’,10000,’expensive’,’ok’ )
  • Different Types; DECODE can take different types of expressions. But CASE has only one type of expression.

Write a Query to get Unique names of products without using DISTINCT keyword.

We can use GROUP BY for this purpose. It can print the distinct groups of PRODUCT NAME.

SELECT prod_name
FROM product
GROUP BY prod_name

What is the use of WITH clause in SQL?

In SQL, WITH clause is used to create a Subquery or View for a set of data.

The main uses of WITH clause are:

  • Simplify: It can simplify a SQL query by creating a subset of data.
  • Reduce Repetition: WITH clause can create a subset of data that can
    be reused multiple times in the main query.

E.g. In following query we use WITH clause to get the set of employee in Finance department. Then we use this subset fin_employee to filter based on AGE less than 30 and Female Gender.

We have used the same set fin_employee multiple times in main query.

WITH fin_employee AS
(SELECT *
FROM Employee
WHERE dep_name = ‘Finance’)
SELECT *
FROM fin_employee
WHERE AGE < 30
UNION ALL
SELECT *
FROM fin_employee
WHERE Gender = ‘Female’;