SQL Interview Questions
Here you can find recently asked SQL interview questions which helps you to know the expectations of different HR’s. Few questions will be repeated in all interviews so by preparing this you can attend the interviews without fear. SQL interview questions that are commonly asked during programmer interviews, both for beginners and advanced database programmers.
Mostly of the companies will include sql questions in the interview for freshers and also for experienced candidates. So you must have some basic and needed knowledge related to sql. Here we provided some repeated sql interview questions which indicates that those questions satisfies the basic and necessary needs.
If you wish you can also learn about basic SQL and SQL commands
THEORY BASED SQL INTERVIEW QUESTIONS
1. What is a join?
This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.
2. What are the types of join and explain each?
There are various types of join which can be used to retrieve data and it depends on the relationship between tables.
Inner join.
Inner join return rows when there is at least one match of rows between the tables.
Right Join.
Right join return rows which are common between the tables and all rows of Right hand side table. Simply, it returns all the rows from the right hand side table even though there are no matches in the left hand side table.
Left Join.
Left join return rows which are common between the tables and all rows of Left hand side table. Simply, it returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
Full Join.
Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.
3. What is normalization?
Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.
4. What is Denormalization ?
Denormalization is a technique used to access the data from higher to lower normal forms of database. It is also process of introducing redundancy into a table by incorporating data from the related tables.
5. What are all the different normalizations ?
The normal forms can be divided into 5 forms, and they are explained below -.
First Normal Form (1NF) :
This should remove all the duplicate columns from the table. Creation of tables for the related data and identification of unique columns.
Second Normal Form (2NF) :
Meeting all requirements of the first normal form. Placing the subsets of data in separate tables and Creation of relationships between the tables using primary keys.
Third Normal Form (3NF) :
This should meet all requirements of 2NF. Removing the columns which are not dependent on primary key constraints.
Fourth Normal Form (3NF) :
Meeting all the requirements of third normal form and it should not have multi- valued dependencies.
6. What is a trigger?
A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.
Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.
7. What is data Integrity?
Data Integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to enforce business rules on the data when it is entered into the application or database.
8. What is the difference between Cluster and Non-Cluster Index?
Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.
A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.
9. Advantages and Disadvantages of Stored Procedure?
Stored procedure can be used as a modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.
Disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.
10. What is Online Transaction Processing (OLTP)?
Online Transaction Processing or OLTP manages transaction based applications which can be used for data entry and easy retrieval processing of data. This processing makes like easier on simplicity and efficiency. It is faster, more accurate results and expenses with respect to OTLP.
Example – Bank Transactions on a daily basis.
11. How to fetch alternate records from a table?
Records can be fetched for both Odd and Even row numbers -.
To display even numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
1
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
To display odd numbers-.
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
1
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
from (Select rowno, studentId from student) where mod(rowno,2)=1.[/sql]
12. Which operator is used in query for pattern matching?
LIKE operator is used for pattern matching, and it can be used as -.
% – Matches zero or more characters.
_(Underscore) – Matching exactly one character.
Example -.
Select * from Student where studentname like ‘a%’
1
Select * from Student where studentname like ‘a%’
Select * from Student where studentname like ‘ami_’
1
Select * from Student where studentname like ‘ami_’
13. What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.
14. What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
15. What is Nested Trigger?
A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
16. What is Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by- row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor we need to perform some steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor
17. What is User Defined Functions? What kind of User-Defined Functions can be created?
User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
Different Kinds of User-Defined Functions created are:
Scalar User-Defined Function A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.
Inline Table-Value User-Defined Function An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.
Multi-statement Table-Value User-Defined Function A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.
18. How is the primary key different from a unique key?
A. Both the primary and unique keys uniquely identify a record in a database table. One difference is that you can have more than one unique key per table, but only one primary key. Also, the primary key does not allow any null value, while the unique key allows one.
19. How is the primary key different from a unique key?
A. Both the primary and unique keys uniquely identify a record in a database table. One difference is that you can have more than one unique key per table, but only one primary key. Also, the primary key does not allow any null value, while the unique key allows one.
20. When might someone denormalize their data?
Typically done for performance reasons, to reduce the number of table joins. This is not a good idea in a transactional environment as there are inherent data integrity risks or performance risks due to excessive locking to maintain data integrity.
Questions related to the Unified Modeling Language (UML) or Entity-Relationship Diagrams (ERDs) may also be asked here.
21. What are the elements of an ERD?
The three elements include the entities for which someone is seeking information, the attributes of those entities, and the relationships between the entities.
22. What is the difference between an inner and outer join?
An inner join involves joining two tables where a common id/key exists in both. An outer join is the joining of two tables, but where there is no match in the second (or first).
23. How do you maintain database integrity where deletions from one table will automatically cause deletions in another table?
You create a trigger that will automatically delete elements in the second table when elements from the first table are removed.
24. What is CLAUSE?
SQL clause is defined to limit the result set by providing condition to the query. This usually filters some rows from the whole set of records.
Example – Query that has WHERE condition
Query that has HAVING condition.
25. What is the difference between Cluster and Non-Cluster Index?
Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.
A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.
QUERY BASED SQL INTERVIEW QUESTIONS
By asking Query based SQL interview questions, it will help them to know how strong you are in writing sql queries and your logical thinking to solve various queries. They will be asking some basic commands which we won’t like to revise and little mixed queries to observe your coding knowledge in few questions. Mostly in technical hr or else in first round there will be query based SQL interview questions. Some important sql interview questions are
- Using Joins
- Trigger
- How to create database
- Using Inner query
- Find max value at particular column
- Display the current date
- How to check date format
- Using groupby
- Using distinct
- Query with multiple conditions
These are the common possible questions asked by the interviewers to test the coding knowledge. Mostly while selecting students for software development any of these questions were expected. Students interested to work in development team must know this for their easy clearance in technical interview. Both query based sql interview questions and theory based sql interview questions were important.
