1. What is DBMS ?
The database management system is a collection of programs that enables user to store, retrieve,
update and delete information from a database.
2. What is RDBMS ?
Relational Database Management system (RDBMS) is a database management system (DBMS)
that is based on the relational model. Data from relational database can be accessed or
reassembled in many different ways without having to reorganize the database tables. Data from
relational database can be accessed using an API , Structured Query Language (SQL).
3. What is SQL ?
Structured Query Language(SQL) is a language designed specifically for
communicating with databases. SQL is an ANSI (American National Standards
Institute) standard.
4. What are the different type of SQL's statements ?
This is one of the frequently asked SQL Interview Questions to freshers. SQL statements are broadly
classified into three. They are
1. DDL – Data Definition Language
DDL is used to define the structure that holds the data. For example, Create,
Alter, Drop and Truncate table.
2. DML– Data Manipulation Language
DML is used for manipulation of the data itself. Typical operations are
Insert, Delete, Update and retrieving the data from the table. Select
statement is considered as a limited version of DML, since it can't change
data in the database. But it can perform operations on data retrieved from
DBMS, before the results are returned to the calling function.
3. DCL– Data Control Language
DCL is used to control the visibility of data like granting database access
and set privileges to create tables etc. Example - Grant, Revoke access
permission to the user to access data in database.
5. What are the Advantages of SQL ?
1. SQL is not a proprietary language used by specific database vendors.
Almost every major DBMS supports SQL, so learning this one language will
enable programmers to interact with any database like ORACLE, SQL ,MYSQL etc.
2. SQL is easy to learn. The statements are all made up of descriptive
English words, and there aren't that many of them.
3. SQL is actually a very powerful language and by using its language
elements you can perform very complex and sophisticated database operations.
6. what is a field in a database ?
A field is an area within a record reserved for a specific piece of data.
Examples: Employee Name, Employee ID etc
7. What is a Record in a database ?
A record is the collection of values / fields of a specific entity: i.e. an
Employee, Salary etc.
8. What is a Table in a database ?
A table is a collection of records of a specific type. For example, employee
table, salary table etc.
Interview Questions on Database Transactions
9. What is a database transaction?
Database transaction takes database from one consistent state to another. At the end of the
transaction the system must be in the prior state if the transaction fails or the status of the system
should reflect the successful completion if the transaction goes through.
10. What are properties of a transaction?
Expect this SQL Interview Questions as a part of an any interview, irrespective of your experience.
Properties of the transaction can be summarized as ACID Properties.
1. Atomicity
A transaction consists of many steps. When all the steps in a transaction
gets completed, it will get reflected in DB or if any step fails, all the
transactions are rolled back.
2. Consistency
The database will move from one consistent state to another, if the
transaction succeeds and remain in the original state, if the transaction
fails.
3. Isolation
Every transaction should operate as if it is the only transaction in the
system.
4. Durability
Once a transaction has completed successfully, the updated rows/records must
be available for all other transactions on a permanent basis.
11. What is a Database Lock ?
Database lock tells a transaction, if the data item in questions is currently
being used by other transactions.
12. What are the type of locks ?
1. Shared Lock
When a shared lock is applied on data item, other transactions can only read
the item, but can't write into it.
2. Exclusive Lock
When an exclusive lock is applied on data item, other transactions can't read
or write into the data item.
Database Normalization Interview Questions
13. What are the different type of normalization?
In database design, we start with one single table, with all possible
columns. A lot of redundant data would be present since it’s a single table.
The process of removing the redundant data, by splitting up the table in a
well defined fashion is called normalization.
1. First Normal Form (1NF)
A relation is said to be in first normal form if and only if all underlying
domains contain atomic values only. After 1NF, we can still have redundant
data.
2. Second Normal Form (2NF)
A relation is said to be in 2NF if and only if it is in 1NF and every non key
attribute is fully dependent on the primary key. After 2NF, we can still have
redundant data.
3. Third Normal Form (3NF)
A relation is said to be in 3NF, if and only if it is in 2NF and every non
key attribute is non-transitively dependent on the primary key.
Database Keys and Constraints SQL Interview Questions
14. What is a primary key?
A primary key is a column whose values uniquely identify every row in a
table. Primary key values can never be reused. If a row is deleted from the
table, its primary key may not be assigned to any new rows in the future. To
define a field as primary key, following conditions had to be met :
1. No two rows can have the same primary key value.
2. Every row must have a primary key value
3. The primary key field cannot be null
4. Values in primary key columns can never be modified or updated
15. What is a Composite Key ?
A Composite primary key is a type of candidate key, which represents a set of
columns whose values uniquely identify every row in a table.
For example - if "Employee_ID" and "Employee Name" in a table is combined to
uniquely identify a row its called a Composite Key.
16. What is a Composite Primary Key ?
A Composite primary key is a set of columns whose values uniquely identify
every row in a table. What it means is that, a table which contains composite
primary key will be indexed based on the columns specified in the primary
key. This key will be referred in Foreign Key tables.
For example - if the combined effect of columns, "Employee_ID" and "Employee
Name" in a table is required to uniquely identify a row, its called a
Composite Primary Key. In this case, both the columns will be represented as
primary key.
17. What is a Foreign Key ?
When a "one" table's primary key field is added to a related "many" table in
order to create the common field which relates the two tables, it is called a
foreign key in the "many" table.
For example, the salary of an employee is stored in salary table. The
relation is established via foreign key column “Employee_ID_Ref” which refers
“Employee_ID” field in the Employee table.
18. What is a Unique Key ?
Unique key is same as primary with the difference being the existence of
null. Unique key field allows one value as NULL value.
SQL Insert, Update and Delete Commands Interview Questions
19. Define SQL Insert Statement ?
SQL INSERT statement is used to add rows to a table. For a full row insert,
SQL Query should start with “insert into “ statement followed by table name
and values command, followed by the values that need to be inserted into the
table. The insert can be used in several ways:
1. To insert a single complete row.
2. To insert a single partial row.
20. Define SQL Update Statement ?
SQL Update is used to update data in a row or set of rows specified in the
filter condition.
The basic format of an SQL UPDATE statement is, Update command followed by
table to be updated and SET command followed by column names and their new
values followed by filter condition that determines which rows should be
updated.
21. Define SQL Delete Statement ?
SQL Delete is used to delete a row or set of rows specified in the filter
condition.
The basic format of an SQL DELETE statement is, DELETE FROM command followed
by table name followed by filter condition that determines which rows should
be updated.
22. What are wild cards used in database for Pattern Matching ?
SQL Like operator is used for pattern matching. SQL 'Like' command takes more time to
process. So before using "like" operator, consider suggestions given below on when and where to
use wild card search.
1) Don't overuse wild cards. If another search operator will do, use it instead.
2) When you do use wild cards, try not to use them at the beginning of the search pattern, unless
absolutely necessary. Search patterns that begin with wild cards are the slowest to process.
3) Pay careful attention to the placement of the wild card symbols. If they are misplaced, you
might not return the data you intended.
SQL Joins Interview Questions and answers
23. Define Join and explain different type of joins?
Another frequently asked SQL Interview Questions on Joins. In order to avoid
data duplication, data is stored in related tables. Join keyword is used to
fetch data from related tables. "Join" return rows when there is at least one
match in both table. Type of joins are
Right Join
Return all rows from the right table, even if there are no matches in the
left table.
Outer Join
Left Join
Return all rows from the left table, even if there are no matches in the
right table.
Full Join
Return rows when there is a match in one of the tables.
24. What is Self-Join?
Self-join is query used to join a table to itself. Aliases should be used for
the same table comparison.
25. What is Cross Join?
Cross Join will return all records where each row from the first table is
combined with each row from the second table.
Database Views Interview Questions
26. What is a view?
The views are virtual tables. Unlike tables that contain data, views simply
contain queries that dynamically retrieve data when used.
27. What is a materialized view?
Materialized views are also a view but are disk based. Materialized views get
updates on specific duration, base upon the interval specified in the query
definition. We can index materialized view.
28. What are the advantages and disadvantages of views in a database?
Advantages:
1. Views don't store data in a physical location.
2. The view can be used to hide some of the columns from the table.
3. Views can provide Access Restriction, since data insertion, update and
deletion is not possible with the view.
Disadvantages:
1. When a table is dropped, associated view become irrelevant.
2. Since the view is created when a query requesting data from view is
triggered, its a bit slow.
3. When views are created for large tables, it occupies more memory.
Stored Procedures and Triggers Interview Questions
29. What is a stored procedure?
Stored Procedure is a function which contains a collection of SQL Queries. The procedure can
take inputs , process them and send back output.
30. What are the advantages a stored procedure?
Stored Procedures are precomplied and stored in the database. This enables the database to
execute the queries much faster. Since many queries can be included in a stored procedure, round
trip time to execute multiple queries from source code to database and back is avoided.
31. What is a trigger?
Database triggers are sets of commands that get executed when an event(Before
Insert, After Insert, On Update, On delete of a row) occurs on a table,
views.
32. Explain the difference between DELETE , TRUNCATE and DROP commands?
Once delete operation is performed, Commit and Rollback can be performed to
retrieve data.
Once the truncate statement is executed, Commit and Rollback statement cannot
be performed. Where condition can be used along with delete statement but it
can't be used with truncate statement.
Drop command is used to drop the table or keys like primary,foreign from a
table.
33. What is the difference between Cluster and Non cluster Index?
A clustered index reorders the way records in the table are physically
stored. There can be only one clustered index per table. It makes data
retrieval faster.
A non clustered index does not alter the way it was stored but creates a
completely separate object within the table. As a result insert and update
command will be faster.
34. What is Union, minus and Interact commands?
MINUS operator is used to return rows from the first query but not from the
second query. INTERSECT operator is used to return rows returned by both the
queries.
SQL Server Interview Questions and
Answers
1) What is SQL or Structured Query Language?
SQL is a language which is used to communicate with the database and this language supports
operations like insertion, updation, retrieval and deletion.
2) Explain Relational Database Management System (RDBMS)?
RDBMS is database management system which is used to maintain the data records in the tables
and also indices in tables. Relationships can be created to maintain the data in the table.
3) Explain the properties of a relational table?
Below are the list of properties relational table should have –
Column value of any kind.
Insignificant sequence of columns.
Insignificant sequence of rows.
Unique name for each column.
Atomic values.
4) What is ACID mean in Sql Server?
ACID is used for evaluating application and database architecture. Below are the ACID
properties –
Atomicity
Consistency
Isolation
Durability
5) What are the difference between “Where” and “Having” clause in Sql Server?
“Where” clause is used to filter the rows based on condition. “Having” clause used with
SELECT clause and this is used with GROUP BY clause. If GROUP BY clause not used then
“HAVING” clause works like a “WHERE” clause.
6) Explain primary key in Sql Server?
This is the combination of fields/columns which are used to uniquely specify a row. Primary Key
has a unique constraint defined on the column and the value in the column cannot be NULL.
7) Explain unique key in Sql Server?
Unique Key constraint will enforce the uniqueness of values in the list of columns of the table.
No duplicate values are allowed. Unique key will allow NULL in one row unlike Primary Key.
8) Explain foreign key in Sql Server?
Foreign key is used to establish a relationship between the columns of other table. Foreign key
relationship to be created between two tables by referencing a column of the table to primary key
of another table.
9) What is the use of “JOIN” in Sql Server?
“JOIN” is used to get the data from multiple tables by joining those. Keys created in the tables
will play a major role in the “JOIN”.
10) Explain the types of JOINS in Sql Server?
Below are the list of JOINS in Sql Server –
Inner Join
Right Join
Left Join
Full Join
11) In which TCP/IP port does Sql Server run?
By default it runs on port – 1433 and it can be changed from “Network Utility TCP/IP”
properties.
12) Why to use Stored Procedures in Sql Server?
Stored Procedures are mainly used for reusability and security for data. Stored Procedures are
pre-compiled files so whenever the application want to use run the stored procedure it will not be
compiled again.
13) List out some advantages and disadvantages of stored procedure in Sql Server?
Advantages
Testing
Maintainability
Speed
Optimization
Security etc.
Disadvantages
Portability
Limited Coding functionality
14) List out some differences between DELETE and TRUNCATE?
Below are the following differences between DELETE and TRUNCATE –
DELETE can be rolled back but TRUNCATE cannot be rolled back.
When executing the query DELETE will keep lock on row unlike TRUNCATE which
keeps lock over whole table.
TRUNCATE will reset the value of identity column whereas DELETE will not do that.
Trigger will be fired in case of DELETE but in case of TRUNCATE it will not.
15) Explain COLLATE keyword in Sql Server?
COLLATE keyword can be applied to either column definitions or database definitions. For
example
SELECT EmpId FROM Employee ORDER BY EmpId COLLATE Latin1_General_CS_AS_KS_WS
ASC;
16) List out the differences between Global and Local temp tables in Sql Server?
Global temp tables can be created with – “##” and it will be visible to all active sessions and this
temp table will be deleted when all active sessions are abandoned or disconnected. Local temp
table will be visible to only to the user who created and users of other session will not be able to
see this. And this will be deleted once the table creator session is abandoned or disconnected.
17) List out the different types of locks available in Sql Server?
Below are the list of locks available in Sql Server –
Update Locks
Shared Locks
Exclusive Locks
18) What are the differences between Left join and Inner join in Sql Server?
Left join will return all the rows from the left table and matching rows from right table.
“Left Join” and “Left Outer Join” are used interchangeably because records which are
returned will be the same with either of these.
Inner join matches the common records in two tables joined. In Inner join each record of
table A Is matched with each record of Table B and the matched records are then be
displayed in the resultant table.
19) List out the differences between Clustered Index and Non Clustered Index in Sql
Server?
Clustered Index – Clustered Index will reorder the number of records in a table and by
default the reordering will be done on the basis of primary key as it default acts like a
clustered index.
Non Clustered Index – Non Clustered Index depends on clustered index internally. Leaf
nodes will not be data pages as in clustered index instead it will have index rows, which
acts like a pointer to point to root node of clustered index.
20) List the different types of collation sensitivities in Sql Server?
Below are the list of collation sensitivities in sql server –
Case sensitivity
Accent sensitivity
Kana Sensitivity
Width sensitivity
21) Why to use UPDATE_STATISTICS command in Sql Server?
This command is used to update the index of the table whenever there is a bulk insertion or
updation or deletion in the table.
22) Explain Sql server authentication modes?
Below are the two authentication modes of sql server –
Mixed Mode
Windows Mode
23) Explain Mixed authentication mode of sql server?
Mixed mode of authentication can either use SQL Server authentication or Windows
authentication. If the user opt using windows authentication then the validation will happen in
the operating system level and if the user opt for Sql server authentication then the password has
to be set up while installing.
24) How the authentication mode can be changed?
Authentication mode can be changed using following steps –
Start -> Programs -> Microsoft SQL Server -> “SQL Enterprise Manager” and run
SQL Enterprise Manager.
25) What is recursive stored procedure in Sql Server?
Recursive stored procedure is the stored procedure called as child stored procedure inside the
parent or main stored procedure. This can be done easily in Sql Server by using “EXEC”
keyword in a stored procedure. For example
Create Procedure SP_Test
AS
BEGIN
EXEC sp_Child @params
END
26) What is Normalization and DeNormalization in Sql Server?
Normalization – It’s the process of minimizing dependency and redundancy by properly
organizing the fields or columns of the table.
DeNormalization – It is the process of accessing the data from higher to lower
normalization forms.
27) List out different types of normalizations in Sql Server and explain each of them?
Below are the types of normalizations –
1 NF – Removing the duplicate records from the table by assigning primary key to a
table.
2 NF - Meet all the requirements of 1 NF and create the relationship between the tables
and segregate the data storing between multiple tables.
3 NF - Meet all the requirements of 2 NF. Remove the list of columns from the table
which does not meet primary key constraint.
4 NF – Tables should not more than two relationships
5 NF – Practical constraints on info for justifying the many-to-many relationships.
28) What you mean by Unique Index in Sql Server?
Unique Index will not allow a column to have duplicate values. Unique Index is associated with
Primary key by default.
29) Explain View in Sql Server?
It is a virtual table which will have the data from multiple tables. Views can be used to retrieve /
update / delete the rows. When the data in the table changes in view also it changes.
30) Explain Indexing and what are the advantages of it?
Indexing contains pointers to the data in a table. Indexes are created in a table to retrieve the data
quickly. So Indexing improves the performance as the retrieval of data takes less time. Indexing
will be done for columns which are being used more often while retrieving.
31) What is the significance of QUOTED_IDENTIFIER ON in Sql Server?
When “QUOTED_IDENTIFIER” set ON, all identifiers should be delimited by double quotation
marks and all literals or single quotation marks or single quotation mark.
32) Why to use Cross Join in Sql Server?
Cross join gives the “cartesian product” of joined tables. In this join no need to specify the
conditions while joining the tables.
33) Why to use “STUFF” keyword in Sql Server? Give an example of it.
“STUFF” keyword is used for inserting one string into other string. For example
SELECT STUFF('testing', 3, 3, 'Hey');
Output – teHeyng
34) Explain “CHECK Constraint” in Sql Server?
“CHECK Constraint” will be used when the column value has to be restricted within a limit.
This constraint can be set in the column level.
35) Why to use “DISTINCT” in Sql Server?
“DISTINCT” keyword is used to remove the duplicate values in a given column value. For
example
SELECT DISTINCT column_name From table
36) Explain Trigger in Sql Server?
Trigger is used for initiating any action when the operation like Insert/Update/Delete is occurred
on an object.
37) Explain Common Table Expression (CTE) in Sql Server?
CTEs are used to make the query easier. Whenever there are too many joins are being used in a
query then we can use CTEs to make it simple. For example
With test
AS
(
SELECT col1,col2
FROM table
)
SELECT * FROM test
38) Why to use “IN” clause in Sql Server?
“IN” clause is used to specify multiple values in WHERE clause. For example
SELECT * FROM Employees WHERE City IN (‘bangalore’,’Kochin’)
39) What does man by SQL Wildcard Characters in Sql Server?
WildCard Characters are used with “LIKE” operator in Sql Server. Wildcards are used for data
retrieval process from the table. Some of the wildcards are
“-“ - This is used for substituting a single character.
“%” - This is used for substituting zero or more characters.
[listofchars] – Ranges of characters for matching.
40) Explain “NOT NULL Constraint” in Sql Server?
“NOT NULL Constraint” is used in a column to make sure the value in the column is not null. If
this constraint has not set then by default columns will accept NULL values too.
41) Explain “@@ROWCOUNT” and “@@ERROR” in Sql Server?
@@ROWCOUNT - Used to return the number of rows affected in the table due to last
statement.
@@ERROR – Used to return the error code which is occurred due to last SQL statement.
‘0’ means there are no errors.
42) Why to use Cursor in Sql Server?
Cursor is used in case of row traversal. This can be considered as a pointer pointing to one row at
a time in the list of rows. Cursors can be used for retrieval, removal or addition of records in a
table.
43) Why to use Sub Query in Sql Server and List out types of Sub Queries?
Sub Queries are queries within a query. The parent or outer query is being called as main query
and the inner query is called as inner query or sub query. Different types of Sub Queries are
Correlated - It is not an independent subquery. It is an inner query which is referred by
outer query.
Non Correlated - It is an independent subquery. It can be executed even without outer
query.
44) What are user defined functions (UDFs) in Sql Server?
User Defined functions are being used to handle complex queries.
There are two types of user defined functions –
Scalar – This type of functions are used for returning single scalar value.
Table Valued – This type of function are used for returning a table which has list of rows.
Sql supports datatype called table which is used here for returning a table.
45) List all types of constraints in Sql Server?
Below are the list of constraints in Sql Server –
NOT NULL
DEFAULT
CHECK
PRIMARY KEY
FOREIGN KEY
UNIQUE
46) Why to use IDENTITY in Sql Server?
IDENTITY is used for a column to auto increment the value of the column in a table and it is
mainly used with Primary Key.
47) What are the differences between Union, Interact and Minus operators?
Union operator is used to combine all the results or records of the table and it removes
the duplicate values.
Interact operator is used to return the common list of records between two result sets.
Minus operator is used to get the list of records from the first result set and which is not
there in second result set.
48) List out difference between Union and UnionAll in Sql Server?
Union is used to combine all result sets and it removes the duplicate records from the final result
set obtained unlike UnionAll which returns all the rows irrespective of whether rows are being
duplicated or not.
Union checks the number of columns given in the SELECT statement should be equal or not and
the datatypes are also should be same and same applied to UnionAll.
49) Explain “ROW_NUMBER()” in Sql Server with an example.
“ROW_NUMBER()” is used to return a sequential number of each row within a given partition.
“1” will be the first position. “Partition By” and “Order By” can be used along with
“ROW_NUMBER()”. Below is the example for the same
SELECT ROW_NUMBER() OVER(ORDER BY EmpSalary DESC) AS Row FROM Employees WHERE
EmpNameName IS NOT NULL
50) What are the differences between “ROW_NUMBER()”, “RANK()” and
“DENSE_RANK()”?
“ROW_NUMBER” - Used to return a sequential number of each row within a given
partition.
“RANK” - Used to returns a new row number for each distinct row in the result set and it
will leave a number gap in case of duplicates.
“DENSE_RANK” - Used to returns a new row number for each distinct row in the result
set and it will not leave any number gap in case of duplicates.
51) Explain about Link Server in Sql Server?
Linked Server is used to enable execution of OLEDB data sources in remote servers. With
Linked servers we can create easy SQL statements which will allow remote data to be joined,
combined and retrieved with data in local.
52) What are the advantages of user defined functions over stored procedures in Sql
Server?
User Defined functions can be used in SELECT/WHERE/HAVING clauses whereas stored
procedure cannot be called. In case of table valued functions, the returned table cam be used for
joining with other tables.
53) What is Snapshot Isolation and how it can turned ON and OFF in Sql Server?
Updated versions of the rows for a transaction is maintained in “TempDB” and once the
transaction begins it will ignore all the rows updated or inserted in a table. Below Sql Statement
can be used for turning ON snapshot isolation –
SET ALLOW_SNAPSHOT_ISOLATION ON
54) Why to use “OUTPUT” clause in Sql Server?
OUTPUT clause can be used for determining the rows which are affected due to operations like
– INSERT/DELETE/UPDATE. For Insert statements we have a table called “INSERTED”, for
delete we have “DELETED” table for tracking the rows which are inserted and deleted
respectively.
55) List out the differences between “REPLACE” and “STUFF” functions in Sql Server?
“REPLACE” function used for replace the characters in a string with the given character. For
example
SELECT REPLACE(‘my Name’, ‘m’, ‘h’)
Output : hy nahe
“STUFF” function is used replace the part of one string to another. For example
SELECT STUFF('testing', 3, 3, 'Hello');
Output – teHellong
56) Why to use “No Lock” in Sql Server?
“No Lock” is used for unlocking the rows which are locked by some other transaction. Once
after the rows are committed or rolled back no need to use No Lock. For example
SELECT * from Employees WITH(NOLOCK)
57) What are the significance of master, tempdb and model databases?
master - This database will have data and catalog of all the databases of SQL Server
instance.
tempdb - tempdb database will have temporary objects like local and global temporary
tables and stored procedures as well.
model - model database is mainly used for creating new user databases.
58) Explain about unique identifier datatype in Sql Server?
Unique Identifier datatype mainly used for primary key columns of the tables or any other
columns which need to have unique Ids. “NEWID()” function can be used for generating unique
identifier for the column. Unique Identifiers are also named as GUIDs.
59) Why to use “PIVOT” in Sql Server?
Pivot table automatically count, sort and total the data in a table or spreadsheet and used to create
a separate table for displaying summarized data.
60) Explain Alternate key, Candidate Key and Composite Key in Sql Server?
Alternate Key – To identity a row uniquely we can have multiple keys one of them is
called primary key and rest of them are called alternate keys.
Candidate Key – Set of fields or columns which are uniquely identified in a row and they
constitute candidate keys.
Composite Key – One key formed by combining at least two or more columns or fields.
61) How to use “BETWEEN” operator in Sql Server?
“BETWEEN” operator is used for selecting the range of values. For example
SELECT * FROM Employees WHERE EmpSalary BETWEEN 10000 AND 20000
62) How to use “DROP” keyword in Sql Server and Give an example.
“DROP” keyword is used to drop either Index or database or table. Below are list of Sql
statements using Drop keyword.
Dropping Index
DROP INDEX my_index
Dropping Database
DROP DATABASE my_database
Dropping Table
DROP TABLE my_table
63) List out all Null functions in Sql Server?
Below are the list of Null functions in Sql –
ISNULL()
NVAL()
IFNULL()
COALESCE()
64) What are the differences between ISNULL() and COALESCE() in Sql Server?
ISNULL() – This function is used to replace the given value in case of NULL value. For
example
SELECT ISNULL(@myvar, ‘replacetext’)
COALESCE() – This function will return the first non-null expression given in the list of
expressions. Advantage of this function over “ISNULL()” is it takes more than two inputs for
checking non null expression unlike ISNULL() which takes only two inputs. For example
SELECT COALESCE(@firvar, @secondvar, @thirdvar);
65) Give list of Scalar and Aggregate functions of Sql Server?
Scalar Functions
MID() - Extracting characters from a string.
LEN() – Get the length of string.
ROUND() – Rounding the number.
UCASE() - Change it to upper case.
LCASE() - Change it to lower case etc.
Aggregate Functions
AVG() – Returns the average value.
MAX() – Returns maximum value.
MIN() - Returns minimum value.
COUNT() - Returns count of rows.
SUM() – Returns sum value etc.
66) Give list of Date functions of Sql Server?
Date Functions
GETDATE() - Get current data and time.
DATEDIFF() – Get time between two dates.
DATEPART() – Get single part of Datetime.
DATEADD() – Adding time interval from Date.
CONVERT() – Display date in different formats.
Advanced SQL Interview Questions and
Answers
Write a query to find the range of missing employee id's in the employee table ?
Answers to this question helps you answers similar SQL interview questions like missing
sequence number in SQL, find gaps and islands in a sequence and SQL query to find missing
numbers in a sequence of numbers.
Table Name : Employee
employee_id EMPLOYEE_NAME
1 Chris
4 Alex
5 Sam
7 Robert
For ease of understanding, we had specified only a few rows. In actual scenario, there will be
thousands of rows in the employee table. We need to fetch missing sequence. Following query
will fetch the gaps in the employee id sequence of the above table.
SELECT (a.employee_id + 1) range_start , ( MIN (b.employee_id) - 1 )
range_end FROM employee A, employee B WHERE A.employee_id < B.employee_id
GROUP BY A.employee_id HAVING A.employee_id + 1 < MIN(B.employee_id)
Confused about the above SQL query. Don't worry, following explanations will help you
understand each and every part of it. To start with, let us crack the above query into parts.
First, we need to use "Self Join" on the EMPLOYEE table. We make two EMPLOYEE tables, A
and B. Now join them using employee id field. In where clause of the above query less than
operator is used instead of equal to operator. Less than operator is used to eliminate the
maximum employee id from the result. Now our query looks like this.
SELECT A.employee_id range_start, B.employee_id range_end FROM employee A,
employee B WHERE A.employee_id < B.employee_id
Since less than condition is satisfied for multiple cases, query gives all the possible ranges in the
intermediate result as shown below.
RANGE_START RANGE_END
1 4
1 5
1 7
4 5
4 7
5 7
In order to avoid duplicate rows and to find the actual ending point of the range, we use "group
by" in the query. if we are using group by clause, we need to use at least one aggregate function.
So we use the "min aggregate function" to find the nearest value.
SELECT A.employee_id range_start, MIN(B.employee_id) range_end FROM employee
A, employee B WHERE A.employee_id < B.employee_id GROUP BY A.employee_id
The above query returns all the ranges including missing and available sequences in the
employee table.
RANGE_START RANGE_END
1 4
4 5
5 7
But we need to pick the missing range. For that, we use "having" clause. The condition specified
in the having clause should display results where the gap in each sequence is more than 1.
SELECT A.employee_id range_start, MIN(B.employee_id) range_end FROM employee
A, employee B WHERE A.employee_id < B.employee_id GROUP BY A.employee_id
HAVING A.employee_id + 1 < MIN(B.employee_id)
Now the intermediate result shows the missing ranges.
RANGE_START RANGE_END
1 4
5 7
To specify the exact range, we add one to range start and reduce one from range end, as shown in
the first query.
SELECT (A.employee_id+1) range_start, (MIN(B.employee_id)-1) range_end FROM
employee A, employee B WHERE A.employee_id < B.employee_id GROUP BY
A.employee_id HAVING A.employee_id + 1 < MIN(B.employee_id)
Our final results are as shown below
RANGE_START RANGE_END
2 3
6 6
Write a SQL query to find missing numbers in employee id sequence ?
This question is not similar to the previous question. Query to this questions in much more
complex than the previous query. If the gaps in employee id is only one, we can use the
following query.
SELECT A.employee_id +1 missing FROM employee A, employee B WHERE
A.employee_id = B.employee_id and A.employee_id + 1 NOT IN (SELECT
employee_id FROM employee) AND A.employee_id < (SELECT max(employee_id) FROM
employee)
Above query gives us following numbers
Missing
2
6
As you can see that, the query returns the first missing number in the gap. As per our employee
table we should get 2,3 and 6. But 3 is missing. So earlier query doesn't seem to work. But if use
level and connect by in case of Oracle database, we can find the complete set of missing numbers
in the sequence. Here goes our query.
SELECT LEVEL AS missing_sequence FROM ( SELECT MIN(employee_id) id_min ,
max(employee_id) id_max FROM employee) CONNECT BY LEVEL <= ID_MAX minus
SELECT employee_id FROM employee
Result of the above query.
Missing_Sequence
2
3
6
For better understanding, we will look into the above query in parts. The first thing we do is to
fetch the min and max id's of the employee table.
SELECT MIN(employee_id) id_min , MAX(employee_id) id_max FROM employee
Now let us query to get all the id's which is less than the maximum value in the table using
"CONNECT BY" and "LEVEL". Oracle connect by clause helps us to fetch all
possible hierarchical permutations from the table when it used with "level" keyword. What it
does is that it will give us all the numbers from the one with the maximum id.
SELECT LEVEL as missing_sequence FROM (SELECT MIN(employee_id) id_min ,
MAX(employee_id) id_max from employee) CONNECT BY LEVEL <= id_max
But we need missing values. So we use "minus" keyword and sub query to fetch the id's which
is not present in the above query.
The database management system is a collection of programs that enables user to store, retrieve,
update and delete information from a database.
2. What is RDBMS ?
Relational Database Management system (RDBMS) is a database management system (DBMS)
that is based on the relational model. Data from relational database can be accessed or
reassembled in many different ways without having to reorganize the database tables. Data from
relational database can be accessed using an API , Structured Query Language (SQL).
3. What is SQL ?
Structured Query Language(SQL) is a language designed specifically for
communicating with databases. SQL is an ANSI (American National Standards
Institute) standard.
4. What are the different type of SQL's statements ?
This is one of the frequently asked SQL Interview Questions to freshers. SQL statements are broadly
classified into three. They are
1. DDL – Data Definition Language
DDL is used to define the structure that holds the data. For example, Create,
Alter, Drop and Truncate table.
2. DML– Data Manipulation Language
DML is used for manipulation of the data itself. Typical operations are
Insert, Delete, Update and retrieving the data from the table. Select
statement is considered as a limited version of DML, since it can't change
data in the database. But it can perform operations on data retrieved from
DBMS, before the results are returned to the calling function.
3. DCL– Data Control Language
DCL is used to control the visibility of data like granting database access
and set privileges to create tables etc. Example - Grant, Revoke access
permission to the user to access data in database.
5. What are the Advantages of SQL ?
1. SQL is not a proprietary language used by specific database vendors.
Almost every major DBMS supports SQL, so learning this one language will
enable programmers to interact with any database like ORACLE, SQL ,MYSQL etc.
2. SQL is easy to learn. The statements are all made up of descriptive
English words, and there aren't that many of them.
3. SQL is actually a very powerful language and by using its language
elements you can perform very complex and sophisticated database operations.
6. what is a field in a database ?
A field is an area within a record reserved for a specific piece of data.
Examples: Employee Name, Employee ID etc
7. What is a Record in a database ?
A record is the collection of values / fields of a specific entity: i.e. an
Employee, Salary etc.
8. What is a Table in a database ?
A table is a collection of records of a specific type. For example, employee
table, salary table etc.
Interview Questions on Database Transactions
9. What is a database transaction?
Database transaction takes database from one consistent state to another. At the end of the
transaction the system must be in the prior state if the transaction fails or the status of the system
should reflect the successful completion if the transaction goes through.
10. What are properties of a transaction?
Expect this SQL Interview Questions as a part of an any interview, irrespective of your experience.
Properties of the transaction can be summarized as ACID Properties.
1. Atomicity
A transaction consists of many steps. When all the steps in a transaction
gets completed, it will get reflected in DB or if any step fails, all the
transactions are rolled back.
2. Consistency
The database will move from one consistent state to another, if the
transaction succeeds and remain in the original state, if the transaction
fails.
3. Isolation
Every transaction should operate as if it is the only transaction in the
system.
4. Durability
Once a transaction has completed successfully, the updated rows/records must
be available for all other transactions on a permanent basis.
11. What is a Database Lock ?
Database lock tells a transaction, if the data item in questions is currently
being used by other transactions.
12. What are the type of locks ?
1. Shared Lock
When a shared lock is applied on data item, other transactions can only read
the item, but can't write into it.
2. Exclusive Lock
When an exclusive lock is applied on data item, other transactions can't read
or write into the data item.
Database Normalization Interview Questions
13. What are the different type of normalization?
In database design, we start with one single table, with all possible
columns. A lot of redundant data would be present since it’s a single table.
The process of removing the redundant data, by splitting up the table in a
well defined fashion is called normalization.
1. First Normal Form (1NF)
A relation is said to be in first normal form if and only if all underlying
domains contain atomic values only. After 1NF, we can still have redundant
data.
2. Second Normal Form (2NF)
A relation is said to be in 2NF if and only if it is in 1NF and every non key
attribute is fully dependent on the primary key. After 2NF, we can still have
redundant data.
3. Third Normal Form (3NF)
A relation is said to be in 3NF, if and only if it is in 2NF and every non
key attribute is non-transitively dependent on the primary key.
Database Keys and Constraints SQL Interview Questions
14. What is a primary key?
A primary key is a column whose values uniquely identify every row in a
table. Primary key values can never be reused. If a row is deleted from the
table, its primary key may not be assigned to any new rows in the future. To
define a field as primary key, following conditions had to be met :
1. No two rows can have the same primary key value.
2. Every row must have a primary key value
3. The primary key field cannot be null
4. Values in primary key columns can never be modified or updated
15. What is a Composite Key ?
A Composite primary key is a type of candidate key, which represents a set of
columns whose values uniquely identify every row in a table.
For example - if "Employee_ID" and "Employee Name" in a table is combined to
uniquely identify a row its called a Composite Key.
16. What is a Composite Primary Key ?
A Composite primary key is a set of columns whose values uniquely identify
every row in a table. What it means is that, a table which contains composite
primary key will be indexed based on the columns specified in the primary
key. This key will be referred in Foreign Key tables.
For example - if the combined effect of columns, "Employee_ID" and "Employee
Name" in a table is required to uniquely identify a row, its called a
Composite Primary Key. In this case, both the columns will be represented as
primary key.
17. What is a Foreign Key ?
When a "one" table's primary key field is added to a related "many" table in
order to create the common field which relates the two tables, it is called a
foreign key in the "many" table.
For example, the salary of an employee is stored in salary table. The
relation is established via foreign key column “Employee_ID_Ref” which refers
“Employee_ID” field in the Employee table.
18. What is a Unique Key ?
Unique key is same as primary with the difference being the existence of
null. Unique key field allows one value as NULL value.
SQL Insert, Update and Delete Commands Interview Questions
19. Define SQL Insert Statement ?
SQL INSERT statement is used to add rows to a table. For a full row insert,
SQL Query should start with “insert into “ statement followed by table name
and values command, followed by the values that need to be inserted into the
table. The insert can be used in several ways:
1. To insert a single complete row.
2. To insert a single partial row.
20. Define SQL Update Statement ?
SQL Update is used to update data in a row or set of rows specified in the
filter condition.
The basic format of an SQL UPDATE statement is, Update command followed by
table to be updated and SET command followed by column names and their new
values followed by filter condition that determines which rows should be
updated.
21. Define SQL Delete Statement ?
SQL Delete is used to delete a row or set of rows specified in the filter
condition.
The basic format of an SQL DELETE statement is, DELETE FROM command followed
by table name followed by filter condition that determines which rows should
be updated.
22. What are wild cards used in database for Pattern Matching ?
SQL Like operator is used for pattern matching. SQL 'Like' command takes more time to
process. So before using "like" operator, consider suggestions given below on when and where to
use wild card search.
1) Don't overuse wild cards. If another search operator will do, use it instead.
2) When you do use wild cards, try not to use them at the beginning of the search pattern, unless
absolutely necessary. Search patterns that begin with wild cards are the slowest to process.
3) Pay careful attention to the placement of the wild card symbols. If they are misplaced, you
might not return the data you intended.
SQL Joins Interview Questions and answers
23. Define Join and explain different type of joins?
Another frequently asked SQL Interview Questions on Joins. In order to avoid
data duplication, data is stored in related tables. Join keyword is used to
fetch data from related tables. "Join" return rows when there is at least one
match in both table. Type of joins are
Right Join
Return all rows from the right table, even if there are no matches in the
left table.
Outer Join
Left Join
Return all rows from the left table, even if there are no matches in the
right table.
Full Join
Return rows when there is a match in one of the tables.
24. What is Self-Join?
Self-join is query used to join a table to itself. Aliases should be used for
the same table comparison.
25. What is Cross Join?
Cross Join will return all records where each row from the first table is
combined with each row from the second table.
Database Views Interview Questions
26. What is a view?
The views are virtual tables. Unlike tables that contain data, views simply
contain queries that dynamically retrieve data when used.
27. What is a materialized view?
Materialized views are also a view but are disk based. Materialized views get
updates on specific duration, base upon the interval specified in the query
definition. We can index materialized view.
28. What are the advantages and disadvantages of views in a database?
Advantages:
1. Views don't store data in a physical location.
2. The view can be used to hide some of the columns from the table.
3. Views can provide Access Restriction, since data insertion, update and
deletion is not possible with the view.
Disadvantages:
1. When a table is dropped, associated view become irrelevant.
2. Since the view is created when a query requesting data from view is
triggered, its a bit slow.
3. When views are created for large tables, it occupies more memory.
Stored Procedures and Triggers Interview Questions
29. What is a stored procedure?
Stored Procedure is a function which contains a collection of SQL Queries. The procedure can
take inputs , process them and send back output.
30. What are the advantages a stored procedure?
Stored Procedures are precomplied and stored in the database. This enables the database to
execute the queries much faster. Since many queries can be included in a stored procedure, round
trip time to execute multiple queries from source code to database and back is avoided.
31. What is a trigger?
Database triggers are sets of commands that get executed when an event(Before
Insert, After Insert, On Update, On delete of a row) occurs on a table,
views.
32. Explain the difference between DELETE , TRUNCATE and DROP commands?
Once delete operation is performed, Commit and Rollback can be performed to
retrieve data.
Once the truncate statement is executed, Commit and Rollback statement cannot
be performed. Where condition can be used along with delete statement but it
can't be used with truncate statement.
Drop command is used to drop the table or keys like primary,foreign from a
table.
33. What is the difference between Cluster and Non cluster Index?
A clustered index reorders the way records in the table are physically
stored. There can be only one clustered index per table. It makes data
retrieval faster.
A non clustered index does not alter the way it was stored but creates a
completely separate object within the table. As a result insert and update
command will be faster.
34. What is Union, minus and Interact commands?
MINUS operator is used to return rows from the first query but not from the
second query. INTERSECT operator is used to return rows returned by both the
queries.
SQL Server Interview Questions and
Answers
1) What is SQL or Structured Query Language?
SQL is a language which is used to communicate with the database and this language supports
operations like insertion, updation, retrieval and deletion.
2) Explain Relational Database Management System (RDBMS)?
RDBMS is database management system which is used to maintain the data records in the tables
and also indices in tables. Relationships can be created to maintain the data in the table.
3) Explain the properties of a relational table?
Below are the list of properties relational table should have –
Column value of any kind.
Insignificant sequence of columns.
Insignificant sequence of rows.
Unique name for each column.
Atomic values.
4) What is ACID mean in Sql Server?
ACID is used for evaluating application and database architecture. Below are the ACID
properties –
Atomicity
Consistency
Isolation
Durability
5) What are the difference between “Where” and “Having” clause in Sql Server?
“Where” clause is used to filter the rows based on condition. “Having” clause used with
SELECT clause and this is used with GROUP BY clause. If GROUP BY clause not used then
“HAVING” clause works like a “WHERE” clause.
6) Explain primary key in Sql Server?
This is the combination of fields/columns which are used to uniquely specify a row. Primary Key
has a unique constraint defined on the column and the value in the column cannot be NULL.
7) Explain unique key in Sql Server?
Unique Key constraint will enforce the uniqueness of values in the list of columns of the table.
No duplicate values are allowed. Unique key will allow NULL in one row unlike Primary Key.
8) Explain foreign key in Sql Server?
Foreign key is used to establish a relationship between the columns of other table. Foreign key
relationship to be created between two tables by referencing a column of the table to primary key
of another table.
9) What is the use of “JOIN” in Sql Server?
“JOIN” is used to get the data from multiple tables by joining those. Keys created in the tables
will play a major role in the “JOIN”.
10) Explain the types of JOINS in Sql Server?
Below are the list of JOINS in Sql Server –
Inner Join
Right Join
Left Join
Full Join
11) In which TCP/IP port does Sql Server run?
By default it runs on port – 1433 and it can be changed from “Network Utility TCP/IP”
properties.
12) Why to use Stored Procedures in Sql Server?
Stored Procedures are mainly used for reusability and security for data. Stored Procedures are
pre-compiled files so whenever the application want to use run the stored procedure it will not be
compiled again.
13) List out some advantages and disadvantages of stored procedure in Sql Server?
Advantages
Testing
Maintainability
Speed
Optimization
Security etc.
Disadvantages
Portability
Limited Coding functionality
14) List out some differences between DELETE and TRUNCATE?
Below are the following differences between DELETE and TRUNCATE –
DELETE can be rolled back but TRUNCATE cannot be rolled back.
When executing the query DELETE will keep lock on row unlike TRUNCATE which
keeps lock over whole table.
TRUNCATE will reset the value of identity column whereas DELETE will not do that.
Trigger will be fired in case of DELETE but in case of TRUNCATE it will not.
15) Explain COLLATE keyword in Sql Server?
COLLATE keyword can be applied to either column definitions or database definitions. For
example
SELECT EmpId FROM Employee ORDER BY EmpId COLLATE Latin1_General_CS_AS_KS_WS
ASC;
16) List out the differences between Global and Local temp tables in Sql Server?
Global temp tables can be created with – “##” and it will be visible to all active sessions and this
temp table will be deleted when all active sessions are abandoned or disconnected. Local temp
table will be visible to only to the user who created and users of other session will not be able to
see this. And this will be deleted once the table creator session is abandoned or disconnected.
17) List out the different types of locks available in Sql Server?
Below are the list of locks available in Sql Server –
Update Locks
Shared Locks
Exclusive Locks
18) What are the differences between Left join and Inner join in Sql Server?
Left join will return all the rows from the left table and matching rows from right table.
“Left Join” and “Left Outer Join” are used interchangeably because records which are
returned will be the same with either of these.
Inner join matches the common records in two tables joined. In Inner join each record of
table A Is matched with each record of Table B and the matched records are then be
displayed in the resultant table.
19) List out the differences between Clustered Index and Non Clustered Index in Sql
Server?
Clustered Index – Clustered Index will reorder the number of records in a table and by
default the reordering will be done on the basis of primary key as it default acts like a
clustered index.
Non Clustered Index – Non Clustered Index depends on clustered index internally. Leaf
nodes will not be data pages as in clustered index instead it will have index rows, which
acts like a pointer to point to root node of clustered index.
20) List the different types of collation sensitivities in Sql Server?
Below are the list of collation sensitivities in sql server –
Case sensitivity
Accent sensitivity
Kana Sensitivity
Width sensitivity
21) Why to use UPDATE_STATISTICS command in Sql Server?
This command is used to update the index of the table whenever there is a bulk insertion or
updation or deletion in the table.
22) Explain Sql server authentication modes?
Below are the two authentication modes of sql server –
Mixed Mode
Windows Mode
23) Explain Mixed authentication mode of sql server?
Mixed mode of authentication can either use SQL Server authentication or Windows
authentication. If the user opt using windows authentication then the validation will happen in
the operating system level and if the user opt for Sql server authentication then the password has
to be set up while installing.
24) How the authentication mode can be changed?
Authentication mode can be changed using following steps –
Start -> Programs -> Microsoft SQL Server -> “SQL Enterprise Manager” and run
SQL Enterprise Manager.
25) What is recursive stored procedure in Sql Server?
Recursive stored procedure is the stored procedure called as child stored procedure inside the
parent or main stored procedure. This can be done easily in Sql Server by using “EXEC”
keyword in a stored procedure. For example
Create Procedure SP_Test
AS
BEGIN
EXEC sp_Child @params
END
26) What is Normalization and DeNormalization in Sql Server?
Normalization – It’s the process of minimizing dependency and redundancy by properly
organizing the fields or columns of the table.
DeNormalization – It is the process of accessing the data from higher to lower
normalization forms.
27) List out different types of normalizations in Sql Server and explain each of them?
Below are the types of normalizations –
1 NF – Removing the duplicate records from the table by assigning primary key to a
table.
2 NF - Meet all the requirements of 1 NF and create the relationship between the tables
and segregate the data storing between multiple tables.
3 NF - Meet all the requirements of 2 NF. Remove the list of columns from the table
which does not meet primary key constraint.
4 NF – Tables should not more than two relationships
5 NF – Practical constraints on info for justifying the many-to-many relationships.
28) What you mean by Unique Index in Sql Server?
Unique Index will not allow a column to have duplicate values. Unique Index is associated with
Primary key by default.
29) Explain View in Sql Server?
It is a virtual table which will have the data from multiple tables. Views can be used to retrieve /
update / delete the rows. When the data in the table changes in view also it changes.
30) Explain Indexing and what are the advantages of it?
Indexing contains pointers to the data in a table. Indexes are created in a table to retrieve the data
quickly. So Indexing improves the performance as the retrieval of data takes less time. Indexing
will be done for columns which are being used more often while retrieving.
31) What is the significance of QUOTED_IDENTIFIER ON in Sql Server?
When “QUOTED_IDENTIFIER” set ON, all identifiers should be delimited by double quotation
marks and all literals or single quotation marks or single quotation mark.
32) Why to use Cross Join in Sql Server?
Cross join gives the “cartesian product” of joined tables. In this join no need to specify the
conditions while joining the tables.
33) Why to use “STUFF” keyword in Sql Server? Give an example of it.
“STUFF” keyword is used for inserting one string into other string. For example
SELECT STUFF('testing', 3, 3, 'Hey');
Output – teHeyng
34) Explain “CHECK Constraint” in Sql Server?
“CHECK Constraint” will be used when the column value has to be restricted within a limit.
This constraint can be set in the column level.
35) Why to use “DISTINCT” in Sql Server?
“DISTINCT” keyword is used to remove the duplicate values in a given column value. For
example
SELECT DISTINCT column_name From table
36) Explain Trigger in Sql Server?
Trigger is used for initiating any action when the operation like Insert/Update/Delete is occurred
on an object.
37) Explain Common Table Expression (CTE) in Sql Server?
CTEs are used to make the query easier. Whenever there are too many joins are being used in a
query then we can use CTEs to make it simple. For example
With test
AS
(
SELECT col1,col2
FROM table
)
SELECT * FROM test
38) Why to use “IN” clause in Sql Server?
“IN” clause is used to specify multiple values in WHERE clause. For example
SELECT * FROM Employees WHERE City IN (‘bangalore’,’Kochin’)
39) What does man by SQL Wildcard Characters in Sql Server?
WildCard Characters are used with “LIKE” operator in Sql Server. Wildcards are used for data
retrieval process from the table. Some of the wildcards are
“-“ - This is used for substituting a single character.
“%” - This is used for substituting zero or more characters.
[listofchars] – Ranges of characters for matching.
40) Explain “NOT NULL Constraint” in Sql Server?
“NOT NULL Constraint” is used in a column to make sure the value in the column is not null. If
this constraint has not set then by default columns will accept NULL values too.
41) Explain “@@ROWCOUNT” and “@@ERROR” in Sql Server?
@@ROWCOUNT - Used to return the number of rows affected in the table due to last
statement.
@@ERROR – Used to return the error code which is occurred due to last SQL statement.
‘0’ means there are no errors.
42) Why to use Cursor in Sql Server?
Cursor is used in case of row traversal. This can be considered as a pointer pointing to one row at
a time in the list of rows. Cursors can be used for retrieval, removal or addition of records in a
table.
43) Why to use Sub Query in Sql Server and List out types of Sub Queries?
Sub Queries are queries within a query. The parent or outer query is being called as main query
and the inner query is called as inner query or sub query. Different types of Sub Queries are
Correlated - It is not an independent subquery. It is an inner query which is referred by
outer query.
Non Correlated - It is an independent subquery. It can be executed even without outer
query.
44) What are user defined functions (UDFs) in Sql Server?
User Defined functions are being used to handle complex queries.
There are two types of user defined functions –
Scalar – This type of functions are used for returning single scalar value.
Table Valued – This type of function are used for returning a table which has list of rows.
Sql supports datatype called table which is used here for returning a table.
45) List all types of constraints in Sql Server?
Below are the list of constraints in Sql Server –
NOT NULL
DEFAULT
CHECK
PRIMARY KEY
FOREIGN KEY
UNIQUE
46) Why to use IDENTITY in Sql Server?
IDENTITY is used for a column to auto increment the value of the column in a table and it is
mainly used with Primary Key.
47) What are the differences between Union, Interact and Minus operators?
Union operator is used to combine all the results or records of the table and it removes
the duplicate values.
Interact operator is used to return the common list of records between two result sets.
Minus operator is used to get the list of records from the first result set and which is not
there in second result set.
48) List out difference between Union and UnionAll in Sql Server?
Union is used to combine all result sets and it removes the duplicate records from the final result
set obtained unlike UnionAll which returns all the rows irrespective of whether rows are being
duplicated or not.
Union checks the number of columns given in the SELECT statement should be equal or not and
the datatypes are also should be same and same applied to UnionAll.
49) Explain “ROW_NUMBER()” in Sql Server with an example.
“ROW_NUMBER()” is used to return a sequential number of each row within a given partition.
“1” will be the first position. “Partition By” and “Order By” can be used along with
“ROW_NUMBER()”. Below is the example for the same
SELECT ROW_NUMBER() OVER(ORDER BY EmpSalary DESC) AS Row FROM Employees WHERE
EmpNameName IS NOT NULL
50) What are the differences between “ROW_NUMBER()”, “RANK()” and
“DENSE_RANK()”?
“ROW_NUMBER” - Used to return a sequential number of each row within a given
partition.
“RANK” - Used to returns a new row number for each distinct row in the result set and it
will leave a number gap in case of duplicates.
“DENSE_RANK” - Used to returns a new row number for each distinct row in the result
set and it will not leave any number gap in case of duplicates.
51) Explain about Link Server in Sql Server?
Linked Server is used to enable execution of OLEDB data sources in remote servers. With
Linked servers we can create easy SQL statements which will allow remote data to be joined,
combined and retrieved with data in local.
52) What are the advantages of user defined functions over stored procedures in Sql
Server?
User Defined functions can be used in SELECT/WHERE/HAVING clauses whereas stored
procedure cannot be called. In case of table valued functions, the returned table cam be used for
joining with other tables.
53) What is Snapshot Isolation and how it can turned ON and OFF in Sql Server?
Updated versions of the rows for a transaction is maintained in “TempDB” and once the
transaction begins it will ignore all the rows updated or inserted in a table. Below Sql Statement
can be used for turning ON snapshot isolation –
SET ALLOW_SNAPSHOT_ISOLATION ON
54) Why to use “OUTPUT” clause in Sql Server?
OUTPUT clause can be used for determining the rows which are affected due to operations like
– INSERT/DELETE/UPDATE. For Insert statements we have a table called “INSERTED”, for
delete we have “DELETED” table for tracking the rows which are inserted and deleted
respectively.
55) List out the differences between “REPLACE” and “STUFF” functions in Sql Server?
“REPLACE” function used for replace the characters in a string with the given character. For
example
SELECT REPLACE(‘my Name’, ‘m’, ‘h’)
Output : hy nahe
“STUFF” function is used replace the part of one string to another. For example
SELECT STUFF('testing', 3, 3, 'Hello');
Output – teHellong
56) Why to use “No Lock” in Sql Server?
“No Lock” is used for unlocking the rows which are locked by some other transaction. Once
after the rows are committed or rolled back no need to use No Lock. For example
SELECT * from Employees WITH(NOLOCK)
57) What are the significance of master, tempdb and model databases?
master - This database will have data and catalog of all the databases of SQL Server
instance.
tempdb - tempdb database will have temporary objects like local and global temporary
tables and stored procedures as well.
model - model database is mainly used for creating new user databases.
58) Explain about unique identifier datatype in Sql Server?
Unique Identifier datatype mainly used for primary key columns of the tables or any other
columns which need to have unique Ids. “NEWID()” function can be used for generating unique
identifier for the column. Unique Identifiers are also named as GUIDs.
59) Why to use “PIVOT” in Sql Server?
Pivot table automatically count, sort and total the data in a table or spreadsheet and used to create
a separate table for displaying summarized data.
60) Explain Alternate key, Candidate Key and Composite Key in Sql Server?
Alternate Key – To identity a row uniquely we can have multiple keys one of them is
called primary key and rest of them are called alternate keys.
Candidate Key – Set of fields or columns which are uniquely identified in a row and they
constitute candidate keys.
Composite Key – One key formed by combining at least two or more columns or fields.
61) How to use “BETWEEN” operator in Sql Server?
“BETWEEN” operator is used for selecting the range of values. For example
SELECT * FROM Employees WHERE EmpSalary BETWEEN 10000 AND 20000
62) How to use “DROP” keyword in Sql Server and Give an example.
“DROP” keyword is used to drop either Index or database or table. Below are list of Sql
statements using Drop keyword.
Dropping Index
DROP INDEX my_index
Dropping Database
DROP DATABASE my_database
Dropping Table
DROP TABLE my_table
63) List out all Null functions in Sql Server?
Below are the list of Null functions in Sql –
ISNULL()
NVAL()
IFNULL()
COALESCE()
64) What are the differences between ISNULL() and COALESCE() in Sql Server?
ISNULL() – This function is used to replace the given value in case of NULL value. For
example
SELECT ISNULL(@myvar, ‘replacetext’)
COALESCE() – This function will return the first non-null expression given in the list of
expressions. Advantage of this function over “ISNULL()” is it takes more than two inputs for
checking non null expression unlike ISNULL() which takes only two inputs. For example
SELECT COALESCE(@firvar, @secondvar, @thirdvar);
65) Give list of Scalar and Aggregate functions of Sql Server?
Scalar Functions
MID() - Extracting characters from a string.
LEN() – Get the length of string.
ROUND() – Rounding the number.
UCASE() - Change it to upper case.
LCASE() - Change it to lower case etc.
Aggregate Functions
AVG() – Returns the average value.
MAX() – Returns maximum value.
MIN() - Returns minimum value.
COUNT() - Returns count of rows.
SUM() – Returns sum value etc.
66) Give list of Date functions of Sql Server?
Date Functions
GETDATE() - Get current data and time.
DATEDIFF() – Get time between two dates.
DATEPART() – Get single part of Datetime.
DATEADD() – Adding time interval from Date.
CONVERT() – Display date in different formats.
Advanced SQL Interview Questions and
Answers
Write a query to find the range of missing employee id's in the employee table ?
Answers to this question helps you answers similar SQL interview questions like missing
sequence number in SQL, find gaps and islands in a sequence and SQL query to find missing
numbers in a sequence of numbers.
Table Name : Employee
employee_id EMPLOYEE_NAME
1 Chris
4 Alex
5 Sam
7 Robert
For ease of understanding, we had specified only a few rows. In actual scenario, there will be
thousands of rows in the employee table. We need to fetch missing sequence. Following query
will fetch the gaps in the employee id sequence of the above table.
SELECT (a.employee_id + 1) range_start , ( MIN (b.employee_id) - 1 )
range_end FROM employee A, employee B WHERE A.employee_id < B.employee_id
GROUP BY A.employee_id HAVING A.employee_id + 1 < MIN(B.employee_id)
Confused about the above SQL query. Don't worry, following explanations will help you
understand each and every part of it. To start with, let us crack the above query into parts.
First, we need to use "Self Join" on the EMPLOYEE table. We make two EMPLOYEE tables, A
and B. Now join them using employee id field. In where clause of the above query less than
operator is used instead of equal to operator. Less than operator is used to eliminate the
maximum employee id from the result. Now our query looks like this.
SELECT A.employee_id range_start, B.employee_id range_end FROM employee A,
employee B WHERE A.employee_id < B.employee_id
Since less than condition is satisfied for multiple cases, query gives all the possible ranges in the
intermediate result as shown below.
RANGE_START RANGE_END
1 4
1 5
1 7
4 5
4 7
5 7
In order to avoid duplicate rows and to find the actual ending point of the range, we use "group
by" in the query. if we are using group by clause, we need to use at least one aggregate function.
So we use the "min aggregate function" to find the nearest value.
SELECT A.employee_id range_start, MIN(B.employee_id) range_end FROM employee
A, employee B WHERE A.employee_id < B.employee_id GROUP BY A.employee_id
The above query returns all the ranges including missing and available sequences in the
employee table.
RANGE_START RANGE_END
1 4
4 5
5 7
But we need to pick the missing range. For that, we use "having" clause. The condition specified
in the having clause should display results where the gap in each sequence is more than 1.
SELECT A.employee_id range_start, MIN(B.employee_id) range_end FROM employee
A, employee B WHERE A.employee_id < B.employee_id GROUP BY A.employee_id
HAVING A.employee_id + 1 < MIN(B.employee_id)
Now the intermediate result shows the missing ranges.
RANGE_START RANGE_END
1 4
5 7
To specify the exact range, we add one to range start and reduce one from range end, as shown in
the first query.
SELECT (A.employee_id+1) range_start, (MIN(B.employee_id)-1) range_end FROM
employee A, employee B WHERE A.employee_id < B.employee_id GROUP BY
A.employee_id HAVING A.employee_id + 1 < MIN(B.employee_id)
Our final results are as shown below
RANGE_START RANGE_END
2 3
6 6
Write a SQL query to find missing numbers in employee id sequence ?
This question is not similar to the previous question. Query to this questions in much more
complex than the previous query. If the gaps in employee id is only one, we can use the
following query.
SELECT A.employee_id +1 missing FROM employee A, employee B WHERE
A.employee_id = B.employee_id and A.employee_id + 1 NOT IN (SELECT
employee_id FROM employee) AND A.employee_id < (SELECT max(employee_id) FROM
employee)
Above query gives us following numbers
Missing
2
6
As you can see that, the query returns the first missing number in the gap. As per our employee
table we should get 2,3 and 6. But 3 is missing. So earlier query doesn't seem to work. But if use
level and connect by in case of Oracle database, we can find the complete set of missing numbers
in the sequence. Here goes our query.
SELECT LEVEL AS missing_sequence FROM ( SELECT MIN(employee_id) id_min ,
max(employee_id) id_max FROM employee) CONNECT BY LEVEL <= ID_MAX minus
SELECT employee_id FROM employee
Result of the above query.
Missing_Sequence
2
3
6
For better understanding, we will look into the above query in parts. The first thing we do is to
fetch the min and max id's of the employee table.
SELECT MIN(employee_id) id_min , MAX(employee_id) id_max FROM employee
Now let us query to get all the id's which is less than the maximum value in the table using
"CONNECT BY" and "LEVEL". Oracle connect by clause helps us to fetch all
possible hierarchical permutations from the table when it used with "level" keyword. What it
does is that it will give us all the numbers from the one with the maximum id.
SELECT LEVEL as missing_sequence FROM (SELECT MIN(employee_id) id_min ,
MAX(employee_id) id_max from employee) CONNECT BY LEVEL <= id_max
But we need missing values. So we use "minus" keyword and sub query to fetch the id's which
is not present in the above query.
No comments:
Post a Comment