Latest SQL Interview Questions and Answers – Most Asked
In this tutorial, we will discuss some of the most important SQL Interview Questions and Answers.
Do not miss to check the second part of SQL Interview Questions at below link:
So let us know start revising SQL concepts with these SQL Interview Questions Answers:
SQL Questions and Answers
Q.1. What are the triggers in SQL?
Triggers are programs which are available in the memory, with unique names made up of SQL queries which we need to fire on our database on and off. Triggers can be made for insert, update and delete statements in SQL. We have two types of triggers:
- Row-level Triggers
- Statement level triggers
Q.2. What is the SQL query to display the current date?
In SQL we have some built-in functions which return the desired output when run.
One such command is the getDate() command. When we run this command the date which is set in the local system is returned as the result to our query.
Example: Let us try to get the date today (set in the system).
SELECT sysdate() as Date_today;
Q.3. What is a Datawarehouse and why is it important?
A data warehouse is the collection of various databases which contain loads of data. Warehousing is put to use when we need to process large blocks of data for our system execution.
It helps in online transactions and the data mining process by consolidating the data available to us.
With the help of warehousing, we can easily find data records we need in analyzing or studying the patterns around us.
The further smaller divisions of the data warehouse are also referred to as data marts.
Q.4. What are user-defined functions?
When we move to industry-level software, most often we observe that the data available to us is not following any fixed pattern.
Analysis of such data becomes a task if each time we need to change the pattern.
To avoid these hindrances, we often try to accomplish such tasks by defining flexible functions which can easily handle the variance in the data.
User-defined functions are made from scratch by the user with such a logic that it applies to the whole of the dataset.
These can be evoked in the same way as system-defined functions.
Q.5. Write a query to create an empty table from an existing table?
To create an empty table using an already existing table we can use the following query.
USE DataFlair; CREATE TABLE DataFlair_copy AS (SELECT * FROM DataFlair WHERE 23=98); SELECT * FROM DataFlair_copy;
Q.6. What is non-clustered indexing in SQL?
We create non-clustered indexes to aid the process of searching the data in our table. They originate when multiple joins or conditions or various filters are put to use in a single query.
Non-Clustered Index does not affect the physical order of the table and keeps the logical order of data as it is.
For each SQL table, we can create 999 non-clustered indexes in a session.
Q.7. What is Self Join in SQL?
Self joins in SQL tables are beneficial when we need to join the table with itself. It helps us to remove the hierarchy from the table, that is it helps us to convert the table to a flat system.
With this feature, we can easily put on conditions even when we merge the table with itself. It helps us perform the join as if the second one is a different table altogether.
Q.8. Write a query to count the number of unique records in a table?
We can count the number of unique records in our table using the Distinct and the count keywords.
USE DataFlair; SELECT COUNT(distinct(emp_id)) as noOfUniqueRecords FROM DataFlair;
Q.9. Write an SQL query to find employees whose name starts with ‘A’ at DataFlair.
To answer this query we need to use the wildcards which are accessible to us in SQL.
USE DataFlair; SELECT * FROM DataFlair WHERE name LIKE 'A%';
Q.10. What is the difference between DELETE and TRUNCATE keywords in SQL?
In SQL we use the TRUNCATE keyword to delete all the rows from our table thus, dislocating the disk space assigned to the table.
While in the DELETE keyword we delete only the rows from the table which fulfil the set condition provided by the where clause and if no condition is input in the query it deletes all the rows in the table, but in this case, the disk space is still allotted to the table and is not free to be put to some other use.
Q.11. What is Denormalization in SQL?
Denormalization is the technique that is put to use when we need to access the data from a higher to a lower form of the database.
This allows the database to increase the performance and the efficiency of the system by introducing the else removed redundancies.
This adds the redundant data back into the table by executing database queries that combine data from various source input tables into a single output table.
Q.12. Define clause in SQL.
SQL clauses are the built-in functions which help us to fetch the desired set of the result by providing a user-defined condition to the SQL query.
It helps us to filter out the rows from the entire set of records contained in our database table. Some of the prominent clauses which we use in our queries are where, having, order by etc.
Q.13. Write a Query to find the number of employees with experience between 2 to 5 years. (both inclusive)
We can do this by two methods. The queries for both are as follows:
1. Method 1: Using the Between keyword
USE DataFlair; SELECT Count(emp_id) as Experience2to5 FROM DataFlair WHERE experience BETWEEN 2 AND 5;
2. Method 2: Using the IN keyword.Query:
USE DataFlair; SELECT Count(emp_id) as Experience2to5 FROM DataFlair WHERE experience IN (2,3,4,5);
Q.14. What is the use of LIMIT keyword in SQL?
When we run a query on the database the result contains all the rows which are present in the database.
If the system returns all the contained rows the computational costs increase and thus the efficiency of the system decreases.
We as developers try to avoid such costs by limiting the number of rows the query returns as the result. Limit keyword helps us set the value till which we need to display the records returned in the resultant table.
The query can be written as follows:
USE DataFlair; SELECT * FROM DataFlair LIMIT 5 ;
Q.15. Explain in detail OLTP.
OLTP or the Online Transactional Processing is a type of data processing which is focused on transaction-oriented tasks and their occurrences.
OLTP deals with the tasks like the inserting, updating, deleting and many more in the small packets of data in a database.
It deals with the large numbers of transactions done at a time by various users around the planet.
Some of the important examples of OLTP transactions are as follows:
- Use of online banking.
- Sending text messages to one another.
- Call centre staff maintaining details of the customer. etc.
Q.16. Write a query to display the number of employees working at each location of DataFlair Office.
For this query, we need to use the count keyword along with the Group by clause. The query is as follows:
USE DataFlair; SELECT location,Count(*) FROM DataFlair GROUP BY location ;
Q.17. Are blank spaces in SQL the same as NULL. Explain your answer.
No, blank spaces are not similar to a NULL value in SQL. NULL acts as a placeholder for missing data or the data which is skipped by the user.
While the blank spaces only suggest that the data is missing and might be important for logical computations.
With NULL values we make it easier for the system to compute logical operations even if some values are missing.
For example, the Distinct keyword regards NULL as a distinct entry in the system, thus avoiding any logical faults when the system is under production.
Q.18. Write a query to show the working of the SUM() function in SQL?
The SUM() function in SQL is put to use on the integer columns to return the total of all the entries.
Let us write the query to understand it further.
USE DataFlair; SELECT SUM(experience) as Total_Experience FROM DataFlair;
Q.19. What is the IFNULL() function in SQL?
The IFNULL() function in SQL is put to use when we need to convert NULL value to some other user-specified value. IFNULL() function is available in various versions of SQL and MySQL Servers.
With the help of IFNULL(), we can easily perform the basic data cleansing of our data thus speeding up our process of data cleaning.
If put to proper use it can save us from various computational expenses and increase the efficiency of large systems significantly.
Q.20. Write a SQL query to get the record of employee ranked third based on the experience of years.[Most experienced first]
We can use the following query to get the desired result.
USE DataFlair; SELECT * FROM (SELECT * FROM DataFlair ORDER BY experience DESC LIMIT 3) AS emp ORDER BY experience ASC LIMIT 1;
Q.21. How do we count the number of records in our table?
When we need to find the number of records present in our table we use the COUNT keyword/feature present in SQL. It returns the number of records in the table.
Example: Let us find the number of records in the DataFlair table.
USE DataFlair; SELECT COUNT(*) as noOfRecords FROM DataFlair;
Q.22. How to find the first 3 characters of the name column for each employee in our table DataFlair?
For this, we can use the built-in function of Substring in SQL. The first parameter is the name of the column.
The second argument is the point of start of the substring and the third and last parameter is the length of the substring we require.
USE DataFlair; SELECT SUBSTRING(name,1,3) as firstThree FROM DataFlair;
Q.23. How to fetch alternate records from a SQL table?
We can use the following query to get alternate even/odd records from our table.
1. Even Records:
USE DataFlair; SELECT emp_id,name FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY emp_id) AS r FROM DataFlair) as result WHERE mod(result.r,2)=0;
2. Odd Records:
USE DataFlair; SELECT emp_id,name FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY emp_id) AS r FROM DataFlair) as result WHERE mod(result.r,2)=1;
Q.24. State the different types of user-defined functions in SQL?
When we talk about the user-defined functions we mostly come across 3 major variations based on the return value they provide.
In easy terms, they are different from each other based on the output they send to the system.
The various types are as follows:
- Scalar Functions: These functions return the unit as the output of the input provided to them.
- Inline table-valued functions: These functions provide the tables as the return value.
- Multi Statement value function: Like the Inline functions, these functions also provide the tables as the return value of the defined function.
Q.25. Why do we use the formatter in SQL?
Formatters are a tool in SQL to help simplify the queries and make them easy to be read and understood by anyone.
With the help of formatters, the queries are rewritten with proper brackets and indentations, thus making it easy to find and resolve the bugs if any exists in our query.
We have plenty of resources available for using the formatters and beautifiers.
Stay updated with latest technology trends
Join DataFlair on Telegram!!
In this tutorial, we have discussed some of the important SQL interview questions and answers.