SQL Interview Questions and Answers – The ULTIMATE List

This is a summary of numerous important questions that have the potential to be asked in an SQL related job interview. There is no need to worry for your next interview test, because Java Code Geeks are here for you! The majority of the things you may be asked is collected in the list below.

You can also check this tutorial in the following video:

The term SQL basically connects with the database in general. The article tries to cover the concepts of SQL, the basic queries of SQL and moves towards some of the advanced and complicated SQL queries. Further, we also cover the advanced database concepts like triggers, functions, views and procedures in great detail.

The questions and answers are covered using every detail possible and they also contain queries as and where necessary. This ensures that you learn every bit of it and be well-prepared for the interview. To begin with, we will cover the basic concepts about SQL. Further, we will move towards various possible queries that have been defined under SQL. Later, we will discuss some of the database concepts in depth to provide an insight about complex database operations. To conclude, we would look into some standard functions that are available by default in SQL.

Table of Contents

• SQL Basics
• SQL Advanced
• SQL functions and procedures

SQL Basics

1. What does SQL stand for ?

SQL is an acronym for Structured Query Language. SQL is a defined standard for creating normal database queries in any database. The standard has been defined with the view to ensure extremely lower amount of changes when switching from one database to another.

2. What is RDBMS? How is it linked with SQL?

RDBMS is an acronym for Relational Database Management System. An RDBMS contains relational database that carries structured data. This structured data can be queried using some standard predefined statements. SQL is the standard of these predefined statements called queries.

3. What are tables in SQL?

The structured data in an RDBMS is stored in a unit called the Table. A table is a collection of rows organised in the form of columns. The structure of a table is defined using a set of columns. For instance, a User table can contain the columns user_id, password and email.

4. What are datatypes in SQL?

Datatypes of a column specify the type of data that the specific column holds. Datatypes differ across databases depending on how they interpret and store the data. Thus, SQL does not define any specific standard for the datatypes.

5. How do you define a record in SQL?

In SQL tables, a record is defined as a data set of columns with a unique identifier associated with every column. The data set may contain values of varying data types. A record can also be considered as a row in a normal table way. Similar to a normal table, every SQL table can contain multiple records.

6. What are the different types of queries in SQL?

There are 3 major types of queries in SQL. These types of listed and explained below:

1. DDL: A Data Definition Language query is a query that defines the structure and datatype of the data in a table. These queries include CREATE, ALTER and DROP queries.
2. DML: A Data Manipulation Language query is a query that is used to insert, update, or delete the data from a database table. These queries include INSERT INTO, UPDATE, SELECT and DELETE queries
3. DCL: A Data Control Language query is a query that is used for administrative purpose. The administrative tasks include creation of user, partitioning of database, providing grants to the user, assigning roles to the user and creating users as well.

7. How do we select data from column ‘username’ and ’email’  from table named ‘users’?

SQL defines a standard SELECT query for this purpose. The above operation can be executed using the below query:

SELECT username, email from users;

8. What is a clause in an SQL query?

A clause is a part of the query that performs various tasks. Every keyword in SQL query forms a different clause. These clauses can be used for purposes like:

• Selecting the data
• Filtering the data
• Grouping the data
• Ordering the data

9. What are different types of clauses in SQL?

The available clauses in a standard SQL query are as listed below:

• SELECT clause: A SELECT clause is the most used clause that will be required to fetch data from the database.
• FROM clause: This clause is used to specify the database tables from which the records need to be selected.
• WHERE clause: This clause is used to apply a filter to the records being fetched. A WHERE clause can be used to filter the records based on one or more fields from a single or multiple tables as well.
• ORDER BY clause: This clause, as the name indicates, is used for ordering the records that are being fetched from the database using SQL statement. The ordering can be done using one or more columns. It can be either in the ascending or descending order. Ordering can be applied on any column irrespective of whether it is selected in the SELECT clause
• GROUP BY clause: This clause is used to group the data based on any specific column data from a specific table. This clause is normal used in relation with a group function or a join.
• HAVING clause: The HAVING clause is defined in order to manipulate the data after it is fetched. For instance, we grouped the data of employees by gender and tried to retrieve the number of male and female and need the record with count more than 100, this clause can be used.

10. Is it possible to select data from multiple tables at the same time?

Yes. It is absolutely possible to select data from multiple tables using SQL. This can be done in several ways. These include:

• JOIN tables
• Use subqueries
• Use comma separated list of tables

11. What is a JOIN in SQL? What are the types?

JOIN is a feature used to get the data from multiple tables by logically joining the data by matching one or more columns of each table. There are 4 different types of JOIN. These are listed and explained below:

• (INNER) JOIN: Returns the records that have values matching the columns in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, while only the matched records from the right hand table
• RIGHT (OUTER) JOIN: Returns all records from the right hand table, and only the matched records from the left hand table
• FULL (OUTER) JOIN: Return all records when there is a match in either left or right table. In such a case the records get multiplied. For instance, if there are 10 records in one table and 80 records in the other, the minimum records we would get is 80 and the maximum records will be 800.

12. What are aliases in SQL?

Aliases in SQL are used to provide alternate names for columns and tables in SQL queries. The aliases help in resolving anonymity when trying to join tables. If two different tables contain the columns of same name, aliases help in selecting a specific column only in the select clause. A sample query using aliases is shown below.

SELECT U.NAME, U.EMAIL FROM USERS U;

Here U acts as an alias to select the columns. A better use case of the same is discussed in the next question.

13. How do I join tables using comma separated table names?

In order to join the tables using just comma separated table names, all you need to do is get the relevant table names and execute the query as shown below.

Consider two tables, users and profile containing columns email for both the tables. These tables can be joined as shown below:

SELECT P.NAME,U.LAST_LOGIN_DATE FROM USERS U, PROFILE P WHERE U.EMAIL = P.EMAIL;

14. What is INNER join? Explain with example

INNER join is used to join the records from two tables using a common data column. For instance, in the above example of user and user profile tables, the same SQL can be written in the form of INNER join as shown below.

SELECT P.NAME,U.LAST_LOGIN_DATE
FROM Users U
INNER JOIN PROFILE P ON U.EMAIL = P.EMAIL;

15. What is LEFT(OUTER) join? Explain with example

LEFT(OUTER) join returns all the records from left hand side table while returns only the matched records from the joined table. Thus, the columns for which data from the joined table is not available, the rows remain blank. The above statement can be converted to a LEFT join as below.

SELECT P.NAME,U.LAST_LOGIN_DATE
FROM Users U
LEFT JOIN PROFILE P ON U.EMAIL = P.EMAIL;

16. What is RIGHT join? Explain with example

RIGHT join is completely opposite of LEFT join. RIGHT(OUTER) join returns all the records from right hand side table while returns only the matched records from the joined table. Thus, the columns for which data from the joined table is not available, the rows remain blank. The above statement can be converted to a RIGHT join as below.

SELECT P.NAME,U.LAST_LOGIN_DATE
FROM Users U
RIGHT JOIN PROFILE P ON U.EMAIL = P.EMAIL;

17. What is FULL join? Explain with example

FULL join is a combination of the LEFT and RIGHT joins. The LEFT join makes sure that all the records of left table are fetched and RIGHT join makes sure all the records of right hand table are fetched. Thus, in FULL join, the obtained records contain the data from whichever table has it. If the records match on the specified column, they have the complete row filled with data. An example of FULL join is shown below.

SELECT P.NAME,U.LAST_LOGIN_DATE
FROM Users U
FULL OUTER JOIN PROFILE P ON U.EMAIL = P.EMAIL;

18. How do I insert a record using SQL?

A record in SQL table is inserted using an INSERT statement. Consider the USERS table with columns – id, name, password, email. To insert a record in the table, the standard syntax of INSERT statement is as shown below.

INSERT into users values (1,'Abhishek','javacodgeeks',abc@javacodegeeks.com');

The above statement inserts a record with data for all the columns. For a large table, there is often a need to insert record with data available for a limited number of columns. To insert such a record, you need to specify the columns for which the data is being inserted. This can be done as shown below.

INSERT INTO users(id,name,email) values (1,'Abhishek','abc@javacodegeeks.com');

In this manner, you can avoid to provide data for the column as long as it is not marked as required.

19. How do I update a record using SQL?

To update a record, you can use the UPDATE statement as shown below.

UPDATE users set password='java123' where id = 1

The above statement sets the value of password column for the user with id as 1.

20. How do I delete a record using SQL?

To delete a record, you can use the DELETE statement as shown below.

DELETE from users where id=1;

The statement deletes the record of user with id 1 from the table users.

21. What is ORDER BY clause?

In a large database, it becomes necessary to get the data in a specific order. The ORDER BY clause is used in combination with SELECT clause to make this possible. For instance, we wish to sort the students by the percentage they scored. You can use a query like the one shown below.

SELECT name,percentage from results ORDER BY percentage;

The above statement sorts the records in ascending order by default. To sort the records in descending order, you need to suffix desc after the column name.

22. Is it possible to order the records by two columns?

Yes. It is possible to order the records by using two columns. In such a case, the table records are ordered by first column first and later it arranges the ordered records by the second columns. Thus, it uses a grouping of rows ordered by first column to sort by the second column. The syntax of such a statement is shown below.

SELECT * FROM TABLENAME ORDER BY COLUMN1 COLUMN2;

23. How do you check for records having no data for a particular column?

To check for such records, you need to use a WHERE clause that checks if the column value is NULL. An example of the same has been shown below.

SELECT * FROM TABLENAME WHERE COLUMN1 IS NULL;

SQL Advanced

24. What are SQL functions?

SQL defines certain standard functions that can be used to perform mathematical manipulations. Few such functions are MIN, MAX, AVG, COUNT and others.

25. Which function can be used to find the minimum and maximum value for any column?

The MIN and MAX functions are used to find the minimum and maximum value for any specific column. These functions evaluate all the rows and return a single value as result set. Consider a students result table. Let us find the maximum marks scored by any student.

SELECT MAX(marks) from result;

26. Which function can be used to find the average marks scored by the class?

The AVG function is used to calculate an average of values from a particular column. For instance, the average marks of a class can be calculated as shown below.

SELECT AVG(marks) from result;

27. How do I get the number of records in a particular table?

The SQL function COUNT can be used to find out the number of records in any table. A sample SQL for the same has been shown below.

SELECT COUNT(1) FROM RESULT; 

The above SQL statement provides you with the count of records in the result table.

28. How do I get the total of the values of a particular column?

To calculate the total of the values, you can use the SQL function SUM. For instance, you need to calculate the total sales value, you can use the SQL as shown below.

SELECT SUM(amount) from sales;

30. What is a LIKE operator in SQL?

In SQL, we can easily check if a column value for specific record is equal to a certain value. For instance, I wish to find a user named Abhishek, I would use a WHERE clause like WHERE firstname='Abhishek'. However, with the equal operator, you cannot look for all users whose name starts with ‘A’. To do so, you need to use the LIKE operator.

31. What is a wildcard? How can it be used with LIKE operator?

A wildcard is a character that can be equated to any character. It is used with LIKE to provide a wildcard match of values. For instance, to find the users whose name start with ‘A’, all you need to do is suffix a wildcard and use a LIKE clause as shown below.

SELECT * FROM USERS WHERE firstname like 'A%';

32. Is it possible to find the names containing the letter ‘e’ using SQL query?

Yes, it is possible to do so. To match the values with one or more characters in between, all you need to do is insert the wildcard character on both the sides of the character. Thus, the query would now be written as shown below.

SELECT * FROM USERS WHERE firstname like '%e%';

33. What is IN operator in SQL?

In SQL, the IN operator is used to find the records for which a particular column value is from a value set. For instance, I wish to find the list of movies belonging to the categories Romance, Action & Horror. We can write an SQL query as shown below.

SELECT * FROM MOVIES WHERE CATEGORY IN ('Romance', 'Action', 'Horror');

34. How do we find records where a particular column value is in a specified range?

In order to find such records, we need to use the BETWEEN operator provided in SQL. The BETWEEN operator allows you to specify the range of a specific field. For instance, you wish to get the list of students whose marks are between 80 and 90, the SQL statement can be written as shown below.

SELECT * FROM results where marks between 80 and 90;

35. How do we combine results from multiple tables?

In order to get data from multiple tables, you need to use the UNION operator. This operator combines the rows from both the tables. A sample SQL statement is provided as shown below.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

SQL Functions & Procedures

36. How can I group the results based on a specific column?

The results can be grouped using the operator GROUP BY. The GROUP BY operator lets you group the records based on values in a specific column. For instance, I need the count of movies from category Action, the SQL can be written as shown below.

SELECT COUNT(1) from movies group by category;

37. What is a user defined function in SQL?

A function is a set of statements that can be programmed in SQL. A function allows the user to program complex tasks like creating a view or creating a summary table. The functions can be stored in the database and used in query just like AVG, SUM, MIN, MAX provided by SQL.

38. Which are different case manipulation functions in SQL?

The case of the data can be modified in SQL and changed to either lowercase, uppercase or initial letter as capital as per the need. These transformations can be done directly using the functions provided by default in SQL. These functions are described below.

LOWER('Abhishek') //Converts to Lower case - Output: abhishek
UPPER('Abhishek')//Converts to upper case - Output: ABHISHEK
INITCAP('ABHISHEK')//Converts first letter to uppercase and remaining letters to lower case - Output:Abhishek

39. How do I get unique list of values from a specific column?

In order to get a unique list of values, you need to use the operator DISTINCT. This operator when used with a particular column, it removes the duplicates and provides only the unique values. For instance, if you wish to find the list of categories related to which movies are available, you can write the SQL for the same as shown below:

SELECT DISTINCT(CATEGORY) FROM MOVIES;

40. Is SQL case-sensitive?

This is a common question by almost every fresher developer. SQL is absolutely case-insensitive when it comes to the case of clauses and queries. However, it does treat the data in a case sensitive manner. Hence, if you put a case sensitive WHERE clause, SQL ensures it is taken care of perfectly.

41. What are stored procedures?

A stored procedure in SQL is a set of predefined tasks that can be performed by just executing the procedure. These tasks can include generation of reports, sending mails, creating a summary and so on. A stored procedure is not a generally supported feature. It is specific to certain databases. Databases like MySQL and Oracle DB does support SQL procedures.

Conclusion

This is a summary of numerous important questions that have the potential to be asked in an SQL related job interview. We covered the SQL concepts and some of the advanced and complicated SQL queries. We also covered the advanced database concepts like triggers, functions, views and procedures in great detail.

Ok, so now you are ready for your interview! Don’t forget to check our Examples dedicated subsection!

If you enjoyed this, then subscribe to our newsletter to enjoy weekly updates and complimentary whitepapers! Also, check out our courses for more advanced training!

You are welcome to contribute with your comments and we will include them in the article!

Last updated on Jan. 17th, 2022

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.