Q1) What is the Difference between MySql Vs SQL Server Performance?

Ans.
MySQl Server Vs SQL Server

Function MS SQL Server MySql

Developer Microsoft Oracle

License Commercial OpenSource

Cloud-based No No

Implementation C++ C & C++

Language

XML Support Yes Yes

Supported It supports C#, PHP, Python, It supports c++, C#, Java, PHP,
Programming Lang Ruby, R, Visual Basic, Java Perl, Python, Ruby, Tcl, Delphi, D
etc etc

Server-Side Net & TransaScripting. Yes, it supports

Scripting.

Concurreny It supports ,yes It supports ,yes

Consistency It enables immediate It enables immediate consistency

Concept's consistency

Transaction concepts It supports ACID It supports ACID

Q2) What is normalization? Explain different levels of normalization?

Ans. It is the way to eliminate redundant data
1. Reduces null value

2. Enables efficient indexing

3. 1NF – Removes duplicated attributes, Attribute data should be atomic, and attribute should be same kind.

4. 2NF – Should be in 1NF and each non-key is fully dependent on the primary key.

5. 3NF – Should be in 2NF and all the non-key attributes which are not dependent on the primary key should be
removed. All the attributes which are dependent on the other non-key attributes should also be removed.
Normalization is done in OLTP.

Q3) What is denormalization and when would you go for it?

Ans. It is the reverse process of normalization. It increases the query performance by
reducing the joins. It is used for OLAP applications.
Q4) How do you implement one-to-one, one-to-many and many-to-many relationships
while designing tables?
Ans. Relationships in sql server are explained below
1. One to One –It can be implemented as a single table. Rarely it is implemented in two tables. For each instance in
the first entity there is one and only one in the second entity and vice versa.

2. One to Many –For each instance in the first entity there can be one or more in the second entity. For each
instance in the second entity there can be one and only one instance in the first entity.

3. Many to Many –For each instance in the first entity there can be one or more instance in the second entity and
moreover, for each instance in the second entity there can be one or more instance in the first entity.

Q5) Difference between Primary key and Unique key.

Ans. Primary Key
 1.Enforces uniqueness of the column in a table

 2.Default clustered index

 3.Does not Allow nulls

Unique Key
 1.Enforces uniqueness of the column in a table.

 2.Default non-clustered index.

 3.Allows one null value

Q6) Define following keys:

Ans. Candidate key, Alternate key, Composite key.
 1.Candidate key –Key which can uniquely identify a row in table.

 2.Alternate key –If the table has more than one candidate keys and when one becomes a primary key the rest
becomes alternate keys.

 3.Composite key –More than one key uniquely identify a row in a table.

Q7) What are defaults? Is there a column to which a default can’t be bound?
Ans. 1.It is a value that will be used by a column if no value is supplied to that column
while inserting data.
2.I can’t be assigned for identity and timestamp values.
Q8) What are user defined data types and when you should go for them?
Ans. Lets you extend the base SQL server data types by providing a descriptive name
and format to the database.
E.g. Flight_num appears in many tables and all these tables have varchar(8)
Create a user defined data-type
Q9) What is a transaction and what are ACID properties?
Ans. A transaction is a logical unit of work in which, all the steps must be performed or
none. ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the
properties of a transaction.
Q10) What part does database design have to play in the performance of a SQL Server-
based application?
Ans. It plays a very major part. When building a new system, or adding to an existing
system, it is crucial that the design is correct. Ensuring that the correct data is captured
and is placed in the appropriate tables, that the right relationships exist between the
tables and that data redundancy is eliminated is an ultimate goal when considering
performance. Planning a design should be an iterative process, and constantly reviewed
as an application is developed. It is rare, although it should be the point that everyone
tries to achieve, when the initial design and system goals are not altered, no matter how
slightly. Therefore, a designer has to be on top of this and ensure that the design of the
database remains efficient..
Q11) What can a developer do during the logical and physical design of a database in
order to help ensure that their database and SQL Server-based application will perform
well?
Ans. A developer must investigate volumes of data (capacity planning), what types of
information will be stored, and how that data will be accessed. If you are dealing with an
upgrade to an existing system, analyzing the present data and where existing data
volumes occur, how that data is accessed and where the current response bottlenecks
are occurring, can help you search for problem areas in the design.
A new system would require a thorough investigation of what data will be captured, and
looking at volumes of data held in other formats also will aid design. Knowing your data
is just as important as knowing the constituents of your data. Also, constantly revisit
your design. As your system is built, check relationships, volumes of data, and indexes
to ensure that the physical design is still at its optimum. Always be ready to check your
system by using tools like the SQL Server Profiler.
Q12) What are the main steps in Data Modeling?
Ans.
 1.Logical – Planning, Analysis and Design

 2.Physical – Design, Implementation and Maintenance

DATABASE DEVELOPMENT / PROGRAMMING
Q13) What are cursors? Explain different types of cursors. What are the disadvantages of
cursors? How can you avoid cursors?
Ans. Cursors allow row-by-row processing of the result sets.
Types of cursors:
Static – Makes a temporary copy of the data and stores in tempdb and any
modifications on the base table does not reflected in data returned by fetches made by
the cursor.
Dynamic – Reflects all changes in the base table.
Forward-only – specifies that cursor can only fetch sequentially from first to last.
Keyset-driven – Keyset is the set of keys that uniquely identifies a row is built in a
tempdb.
Disadvantages of cursors:
Each time you fetch a row from the cursor, it results in a network roundtrip, whereas a
normal SELECT query makes only one roundtrip, however large the result set is.
Cursors are also costly because they require more resources and temporary storage
(results in more IO operations). Further, there are restrictions on the SELECT
statements that can be used with some types of cursors.
Most of the times set-based operations can be used instead of cursors.
Here is an example:
If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 — 5000 hike
Salary between 40000 and 55000 — 7000 hike
Salary between 55000 and 65000 — 9000 hike
In this situation, many developers tend to use a cursor, determine each employee’s
salary and update his salary according to the above formula. But the same can be
achieved by multiple update statements or can be combined in a single UPDATE
statement as shown below:
UPDATE tbl_emp SET salary =
CASE
WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
another situation in which developers tend to use cursors: You need to call a stored
procedure when a column in a particular row meets certain condition. You don’t have to
use cursors for this. This can be achieved using WHILE loop, as long as there is a
unique key to identify each row.
Q14) Write down the general syntax for a SELECT statement covering all the options.
Ans. Here’s the basic syntax: (Also checkout SELECT in books online for advanced
syntax).
1
SELECT select_list
2 [HDEV:INTO new_table_]
3 FROM table_source
4 [HDEV:WHERE search_condition]
5 [HDEV:GROUP BY group_by_expression]
6 [HDEV:HAVING search_condition]
[ORDER BY order_expression [ASC | HDEV:DESC] ]
7
Q15) What is a Join and explain different types of Joins?
Ans. Joins are used in queries to explain how different tables are related. Joins also let
you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are
further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER
JOINS.
Q16) Can you have a nested transaction?
Ans. Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN
and @@TRANCOUNT

Q17) What is an extended stored procedure? Can you instantiate a COM object by using
T-SQL?

Ans. An extended stored procedure is a function within a DLL (written in a programming

language like C, C++ using Open Data Services (ODS) API) that can be called from T-
SQL, just the way we call normal stored procedures using the EXEC statement.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL
by using sp_OACreate stored procedure. Also see books online for sp_OAMethod,
sp_OAGetProperty, sp_OASetProperty, sp_OADestroy.

Q18) What is the system function to get the current user’s userid?

Ans. USER_ID(). Also check out other system functions like USER_NAME(),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(),
HOST_NAME().
Q19) What are triggers? How many triggers you can have on a table? How to invoke a
trigger on demand?

Ans. Triggers are special kind of stored procedures that get executed automatically
when an INSERT, UPDATE or DELETE operation takes place on a table. In SQL
Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE
and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you
could create multiple triggers per each action. But in 7.0 there’s no way to control the
order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires
first or fires last using sp_settriggerorder.
Triggers can’t be invoked on demand. They get triggered only when an associated
action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be
used to extend the referential integrity checks, but wherever possible, use constraints
for this purpose, instead of triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification operation happens. So
in a way, they are called post triggers. But in SQL Server 2000 you could create pre
triggers also – INSTEAD OF triggers.
Virtual tables – Inserted and Deleted form the basis of trigger architecture.

Q20) What is a self join? Explain it with an example.

Ans. Self join is just like any other join, except that two instances of the same table will
be joined in the query. Here is an example: Employees table which contains rows for
normal employees as well as managers. So, to find out the managers of all the
employees, you need a self join.
1
2 CREATE TABLE emp
3 (
4 empid int,
mgrid int,
5 empname char(10)
6 )
7 INSERT emp SELECT 1,2,’Vyas’
8 INSERT emp SELECT 2,3,’Mohan’
9 INSERT emp SELECT 3,NULL,’Shobha’
INSERT emp SELECT 4,2,’Shridhar’
10 INSERT emp SELECT 5,2,’Sourabh’
11 SELECT t1.empname [HDEV:Employee], t2.empname [HDEV:Manager]
12 FROM emp t1, emp t2
13 WHERE t1.mgrid = t2.empid
14
Here’s an advanced query using a LEFT OUTER JOIN that even returns the employees
without managers (super bosses)
1 SELECT t1.empname [HDEV:Employee], COALESCE(t2.empname, ‘No manager’)
2 [HDEV:Manager]
3 FROM emp t1
LEFT OUTER JOIN
4 emp t2
5 ON
6 t1.mgrid = t2.empid

Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Q21) Write a SQL Query to find first Week Day of month?

SELECT DATENAME(dw, DATEADD(dd, – DATEPART(dd, GETDATE()) + 1, GETDATE())) AS
1 FirstDay

Q22) How to find 6th highest salary from Employee table?

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 6 salary FROM employee ORDER BY salary DE
1 a ORDER BY salary

Q23) How can I enforce to use particular index?

You can use index hint (index=index_name) after the table name. SELECT au_lname
FROM authors (index=aunmind)

Q24) What is ORDER BY and how is it different than clustered index?

The ORDER BY clause sorts query results by one or more columns up to 8,060 bytes.
This will happen by the time when we retrieve data from database. Clustered indexes
will physically sort data, while inserting/updating the table.

Q25) What is the difference between a UNION and a JOIN?

Subscribe to our youtube channel to get new updates..!

A JOIN selects columns from 2 or more tables. A UNION selects rows.

Q26) What is the Referential Integrity?

Referential integrity refers to the consistency that must be maintained between primary
and foreign keys, i.e. every foreign key value must have a corresponding primary key
value
Q27) What is the purpose of UPDATE STATISTICS?

It updates information about the distribution of key values for one or more statistics
groups (collections) in the specified table or indexed view.

Q28) What is the use of SCOPE_IDENTITY() function?

It returns the most recently created identity value for the tables in the current execution
scope.

Q29) What do you consider are the best reasons to use stored procedures in your
application instead of passing Transact-SQL code directly to SQL Server?

First and foremost, a stored procedure is a compiled set of code, where passing T-SQL
through languages such as VB, Visual FoxPro, etc., means that the set of code needs
to be compiled first. Although T-SQL within VB, etc., can be prepared before running,
this is still slower than using a stored procedure. Then, of course, there is the security
aspect, where, by building a stored procedure, you can place a great deal of security
around it. When dealing with sensitive data, you can use an encrypted stored procedure
to hide sensitive columns, calculations, and so on. Finally, by using a stored procedure,
I feel that transactional processing becomes a great deal easier and, in fact, using
nested transactions become more insular and secure. Having to deal with transactions
within code that may have front end code, will slow up a transaction and therefore a lock
will be held for longer than necessary.

Q30) What are some techniques for writing fast performing stored procedures?

Fast performing stored procedures are like several other areas within T-SQL. Revisiting
stored procedures every six months or so, to ensure that they are still running at their
optimum performance is essential. However, actual techniques themselves include
working with as short a transaction area as possible, as lock contention will certainly
impact performance. Recompiling your stored procedures after index additions if you
are unable or not wishing to restart SQL Server, will also ensure that a procedure is
using the correct index, if that stored procedure is accessing the table which has
received the new index. If you have a T-SQL command that joins several tables, and it
takes a long time to return a value, first of all check out the indexes. But what you may
find tends to help, is to break down the code and try to determine which join it is that is
causing the performance problem. Then analyze this specific join and see why it is a
problem.
Always check out a stored procedure’s performance as you build it up by using the
SHOWPLAN commands.
Also, try to use EXISTS, rather than a JOIN statement. An EXISTS statement will only
join on a table until one record is found, rather than joining all the records . Also, try to
look at using sub queries when you are trying to find a handful of values in the sub
query statement, and there is no key on the column you are looking up on.

Q31) When should SQL Server-based cursors be used, and not be used?

SQL Server cursors are perfect when you want to work one record at a time, rather than
taking all the data from a table as a single bulk. However, they should be used with care
as they can affect performance, especially when the volume of data increases. From a
beginner’s viewpoint, I really do feel that cursors should be avoided every time because
if they are badly written, or deal with too much data, they really will impact a system’s
performance. There will be times when it is not possible to avoid cursors, and I doubt if
many systems exist without them. If you do find you need to use them, try to reduce the
number of records to process by using a temporary table first, and then building the
cursor from this. The lower the number of records to process, the faster the cursor will
finish. Always try to think “out of the envelope”.
Q32) What alternatives do developers have over using SQL Server-based cursors? In
other words, how can developers perform the same function as a cursor without using a
cursor?
Perhaps one of the performance gains least utilized by developers starting out in SQL
Server are temporary tables. For example, using one or more temporary tables to break
down a problem in to several areas could allow blocks of data to be processed in their
own individual way, and then at the end of the process, the information within the
temporary tables merged and applied to the underlying data. The main area of your
focus should be, is there an alternative way of doing things? Even if I have to break this
down into several chunks of work, can I do this work without using cursors, and so
result in faster performance. Another area that you can look at is the use of CASE
statements within your query. By using a CASE statement, you can check the value
within a column and make decisions and operations based on what you have found.
Although you will still be working on a whole set of data, rather than a subset found in a
cursor, you can use CASE to leave values, or records as they are, if they do not meet
the right criteria. Care should be taken here though, to make sure that by looking at all
the data, you will not be creating a large performance impact. Again, look at using a
subset of the data by building a temporary table first, and then merging the results in
afterwards. However, don’t get caught out with these recommendations and do any of
them in every case. Cursors can be faster if you are dealing with small amounts of data.
However, what I have found, to be rule number one, is get as little data in to your cursor
as is needed.

Q33) If you have no choice but to use a SQL Server-based cursor, what tips do you have
in order to optimize them?

Perhaps the best performance gain is when you can create a cursor asynchronously
rather than needing the whole population operation to be completed before further
processing can continue. Then, by checking specific global variables settings, you can
tell when there is no further processing to take place. However, even here, care has to
be taken. Asynchronous population should only occur on large record sets rather than
those that only deal with a small number of rows. Use the smallest set of data possible.
Break out of the cursor loop as soon as you can. If you find that a problem has
occurred, or processing has ended before the full cursor has been processed, then exit.
If you are using the same cursor more than once in a batch of work, and this could
mean within more than one stored procedure, then define the cursor as a global cursor
by using the GLOBAL keyword, and not closing or deallocating the cursor until the
whole process is finished. A fair amount of time will be saved, as the cursor and the
data contained will already be defined, ready for you to use.
DATABASE PERFORMANCE OPTIMIZATION / TUNING

Q34) What are the steps you will take to improve performance of a poor performing
query?

This is a very open ended question and there could be lot of reasons behind the poor
performance of a query. But some general issues that you could talk about would be:
1. No indexes

2. No Table scans

3. Missing or out of date statistics

4. Blocking

5. Excess recompilations of stored procedures

Q35) What is an ER Diagram?

An ER diagram or Entity-Relationship diagram is a special picture used to represent the
requirements and assumptions in a system from a top down perspective. It shows the
relations between entities (tables) in a database.

Q36) What is a prime attribute?

A prime attribute is an attribute that is part of a candidate key.

Q37) What are the properties of a transaction?

The ACID properties. Atomicity, Consistency, Isolation, and Durability.

Q38) What is a non-prime attribute?

A non-prime attribute is an attribute that is not a part of a candidate key.

Q39) What is Atomicity?

This means the transaction finish completely, or it will not occur at all.

Q40) What is Consistency?

Consistency means that the transaction will repeat in a predictable way each time it is
performed.

Q41) What is Isolation?

The data the transactions are independent of each other. The success of one
transaction doesn’t depend on the success of another.

Q42) What is Durability?

Guarantees that the database will keep track of pending changes so that the server will
be able to recover if an error occurs.

Q43) What is a DBMS?

A DBMS is a set of software programs used to manage and interact with databases.

Q44) What is a RDBMS?

It is a set of software programs used to interact with and manage relational databases.
Relational databases are databases that contain tables.
Q45) What is business intelligence?

Refers to computer-based techniques used in identifying, extracting, and analyzing

business data, such as sales revenue by products and/or departments, or by associated
costs and incomes.

Q46) What is normalization?

Database normalization is the process of organizing the fields and tables of a relational
database to minimize redundancy and dependency.

Q47) What is a relationship?

The way in which two or more concepts/entities are connected, or the state of being
connected.

Q48) What are the different types of relationships?

SQL Server Certification Training!

Explore Curriculum

One to one, one to many, many to many, many to fixed cardinality.

Q49) What is the difference between a OLTP and database?

An OLTP is the process of gathering the data from the users, and a database is the
initial information.

Q50) What are the different kinds of relationships?

Identifying and non-identifying.

Q51) What is an entity?

Something that exists by itself, although it need not be of material existence.

Q52) What is a conjunction table?

A table that is composed of foreign keys that points to other tables.

Q53) What is a relational attribute?

An attribute that would not exist if it were not for the existence of a relation.

Q54) What are associative entities?

An associative entity is a conceptual concept. An associative entity can be thought of as

both an entity and a relationship since it encapsulates properties from both. It is a
relationship since it is serving to join two or more entities together, but it is also an entity
since it may have its own properties.

Q55) What is the difference between a derived attribute, derivedpersistent attribute, and
computed column?

A derived attribute is a attribute that is obtained from the values of other existing
columns and does not exist on it’s own. A derived persistent attribute is a derived
attribute that is stored. A computed attribute is a attribute that is computed from internal
system values.

Q56) What are the types of attributes?

Simple, composite (split into columns), multi-valued (becomes a separate table),

derived, computed, derived persistent.

Q57) Is the relationship between a strong and weak entity always identifying?

Yes, this is the requirement.

Q58) Do stand alone tables have cardinality?

No.

Q59) What is a simple key?

It is a key that in composed of one attribute.

Give/ recite the types of UDF functions.
Scalar, In-line, Multi

Q60) Describe what you know about PK, FK, and UK.

Primary keys – Unique clustered index by default, doesn’t accept null values, only one
primary key per table.
Foreign Key – References a primary key column. Can have null values. Enforces
referential integrity.
Unique key – Can have more than one per table. Can have null values. Cannot have
repeating values. Maximum of 999 clustered indexes per table.

Q61) What do you mean by CTEs? How will you use it?

CTEs also known as common table expressions are used to create a temporary table
that will only exist for the duration of a query. They are used to create a temporary table
whose content you can reference in order to simplify a queries structure.

Q62) What is a sparse column?

It is a column that is optimized for holding null values.

Q63) What would the command: DENY CREATE TABLE TO Peter do?

It wouldn’t allow the user Peter to perform the operation CREATE TABLE regardless of
his role.

Q64) What does the command: GRANT SELECT ON project TO Peter do?

It will allow the SELECT operation on the table ‘project’ by Peter.

Q65) What does the command: REVOKE GRANT SELECT ON project TO Peter do?

It will revoke the permission granted on that table to Peter.

Q66) New commands in SQL 2008?

Database encryption, CDCs tables – For on the fly auditing of tables, Merge operation,
INSERT INTO – To bulk insert into a table from another table, Hierarchy attributes,
Filter indexes, C like operations for numbers, resource management, Intellisense – For
making programming easier in SSMS, Execution Plan Freezing – To freeze in place
how a query is executed.
What is new in SQL 2008 R2?
PowerPivot, maps, sparklines, data bars, and indicators to depict data.

Q67) What is faster? A table variable or temporary table?

A table variable is faster in most cases since it is held in memory while a temporary
table is stored on disk. However, when the table variable’s size exceeds memory size
the two table types tend to perform similarly.
Q68) How big is a tinyint, smallint, int, and bigint?

1 byte, 2 bytes, 4 bytes, and 8 bytes.

Q69) What does @@trancount do?

It will give you the number of active transactions for the current user.
Q70) What are the drawbacks of CTEs?
It is query bound.

Q71) What is the transaction log?

Upcoming Batches - SQL Server Training!


11
JUL

Thursday
6:30 AM IST

13
JUL

Saturday
6:30 AM IST

14
JUL

Sunday
7:00 AM IST

16
JUL

Tuesday
6:30 AM IST

More Batches

It keeps a record of all activities that occur during a transaction and is used to roll back
changes.

Q72) What are before images, after images, undo activities and redo activities in relation
to transactions?
Before images refers to the changes that are rolled back on if a transaction is rolled
back. After images are used to roll forward and enforce a transaction. Using the before
images is called the undo activity. Using after images is called the redo activity.

Q73) What are shared, exclusive and update locks?

A shared lock, locks a row so that it can only be read. An exclusive lock locks a row so
that only one operation can be performed on it at a time. An update lock basically has
the ability to convert a shared lock into an exclusive lock.

Q74) What does WITH TIES do?

If you use TOP 3 WITH TIES *, it will return the rows, that have a similarity in each of
their columns with any of the column values from the returned result set.

Q75) How can you get a deadlock in SQL?

By concurrently running the same resources that access the same information in a
transaction.

Q76) What is LOCK_TIMEOUT used for?

It is used for determining the amount of time that the system will wait for a lock to be
released.

Q77) What is the ANY predicate used for?

SELECT * FROM emp_table WHERE enter_date > ANY (SELECT enter_date FROM
1 works_on)

Q78) What is the ALL predicate used for?

SELECT * FROM emp_table WHERE enter_date > ALL (SELECT enter_date FROM
1 works_on)

Q79) What are some control flow statements in SQL?

while, if, case, for each etc..

Q80) What is the EXISTS function used for?

It is used to determine whether a query returns one or more rows. If it does, the EXIST
function returns TRUE, otherwise, it will return FALSE.
SQL Query Interview Questions with Answers
Inner Join: It is used to retrieve matching records from both the tables
Department:
Department_No Department_Name

10 ECE

20 ECE

30 CSE

40 IT
Employee Details:
Employee_No Emp_Name Address Age Department_No Salary

1 Anil Hyderabad 23 10 20000

2 Sunil Hyderabad 22 10 21000

3 Ajay Chennai 24 20 23000

4 Vijay Chennai 25 30 22000

5 James Hyderabad 24 50 230000

Q1) Write a Query to display employee details who are working in ECE department?

1 SELECT employee.employee_name, employee.address, employee.salary,

employee.age,
2 FROM Department D
3 INNER JOIN Employees E
4 ON department.D_no=employee.D_no WHERE department.D_name= ‘ECE’

Q2) Write a Query to display employee details?

1 SELECT * FROM employee;

Q3) Write a Query to display employee details along with department_name?

1 SELECT employee.employee_no, employee.employee_name, employee.address, employee.salary,

department.department_name
2 FROM department D
3 INNER JOIN employee E
4 ON department.D_no=employee.D_no

Q4) Write a Query to display employee details whose sal>20000 and who is working in
ECE department?
1 SELECT employee.employee_no, employee.employee_name, employee.address,
2 employee.salary, employee.age
FROM department D
3 INNER JOIN employee E
4 ON dept.D_no=emp.D_no
5 WHERE dept.D_name=’ECE’ and E.salary>20000

5) Write a Query to display employee details along with department_name and who is
working in ECE department, whose name starts with a?

1 SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.dname

2 FROM department D
3 INNER JOIN employee E
4 ON dept.D_no=emp.D_no
WHERE dept.D_name=’ECE’ and emp.E_name like ‘a%’
5

Q6) Write a Query to display employee details along with department_name and whose
age between 20 and 24?

1 SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name

2 FROM department D
3 INNER JOIN employee E
4 ON dept.D_no=emp.D_no
WHERE E.age between 20 and 24
5

Q7) Write a Query to display employee details along with department_name and who are
staying in hyderabad?

1 SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name

2 FROM department D
3 INNER JOIN employee E
4 ON dept.D_no=emp.D_no
WHERE E.address=’hyd’
5

Q8) Write a Query to display employee details whose salary>20000 and whose age>20 &
who is working in ECE department?

1 SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name

2 FROM department D
3 INNER JOIN employee E
4 ON dept.D_no=emp.D_no
5 WHERE E.age>20 and E.salary>20000 and dept.D_name=’ECE’
State Table:
State ID State Name
S1 Telangana

S2 AP

S3 Tamil Nadu

S4 Karnataka

S5 Kerala
City
City ID City Name State ID

1 Hyderabad S1

2 Vizag S2

3 Vijayawada S2

4 Chennai S3

5 Madhurai S3

6 Bangalore S4
Blood Group Details
Blood Group ID Blood Group

B1 A+ve

B2 B+ve

B3 AB +ve

B4 A -ve

B5 O +ve
Donor Details
Donor ID Donor Name Phone Number City ID Blood Group ID

D1 Anil 9999 1 B1

D2 Sunil 8888 1 B1

D3 Ajay 7777 2 B1

D4 John 6666 4 B3
D5 James 5555 4 B5

Q9) Write a Query to display city names belongs to AP?

1 SELECT C.City_Name
2 FROM State S
3 INNER JOIN City C
4 ON S.State_ID
WHERE S.State_Name ‘AP’
5

Q10) Write a Query to display Donor_ID, Donor_Name, Phone No, City?

1 SELECT D.Donor_ID, D_Name, D_Phone No, C.City_Name

2 FROM Donor D
3 INNER JOIN City C
4 ON D.City_ID=C.City_ID

Q11) Write a Query to display Donor_ID, Donor_Name, Phone No, Blood Group?

1 SELECT D.Donor_ID, D_Name, D_Phone No, B.Blood_Group

2 FROM Donor D
3 INNER JOIN Blood B
4 ON D.Blood_ID=B.Blood_ID;

Q12) Write a Query to display Donor_ID, Donor_Name, Phone No and who are staying in
hyderabad?

1 SELECT D.Donor_ID, D_Name, D_Phone No, C.City_Name

2 FROM Donor D
3 INNER JOIN City C
4 ON C.City_ID=D.City_ID
WHERE C.City_Name=’hyderabad’
5

Q13) Write a Query to display donor details whose blood group is A +ve?

1 SELECT D.Donor_ID, D_Name, D_Phone No

2 FROM Donor D
3 INNER JOIN Blood B
4 ON D.Donor_ID=B.Blood_ID
WHERE B.Blood_Group=’A+ve’
5

Q14) Write a Query to display Donor_ID, Donor_Name, Phone No, City, Blood Group?
SELECT D.Donor_ID, D_Name, D_Phone No, C.City_Name B.Blood_Group
1 FROM Blood B
2 INNER JOIN Donor D
3 ON D.Blood_ID=B.Donor_Name
4 INNER JOIN City C
ON D.City_ID=C.City_ID
5
6

Q15) Write a Query to display Donor_Name, Phone No, Blood Group of the donors who is
staying in hyderabad and whose blood group is A+ve?

1
SELECT D.Donor_Name, D. Phone_Number, B.Blood_Group
2 FROM Donor D
3 INNER JOIN Blood B
4 ON D.Blood_ID=B.Blood_ID
5 INNER JOIN City C
6 ON D.City_ID=C.City_ID
WHERE C.City_Name=’hyderabad’ and B.Blood_Group=’A+ve’
7
Outer Join A join that includes rows even if they do not have related rows in the joined table is an Outer
Join.. You can create three different outer join to specify the unmatched rows to be included:

 Left Outer Join

 Right Outer Join

 Full Outer Join

Employee Details Table

Employee_No Employee_Name Dept_No

101 Anil 10

102 Sunil 20

103 Ajay 30

104 Vijay 40

105 Null Null

Department Details Table
Dept_No Depat_Name

10 EEE

20 EEE

30 CSE

Null Null
50 IT

Q16) Write a Query to display only left records?

1 SELECT e.*
2 FROM Employee E
3 LEFT OUTER JOIN Department D
4 ON E.D_no
WHERE D.D_No IS NULL
5

Q17) Write a Query to display employee details where employee no is 101?

1 SELECT *
2 FROM Employee E
3 WHERE E_No=101

Q18) Write a Query to display employee details where employee number is null?

1 SELECT *
2 FROM Employee E
3 WHERE E_No IS NULL

Q19) Write a Query to display only right records?

1 SELECT D.*
2 FROM Employee E
3 RIGHT OUTER JOIN Department D
4 ON E.D.No=D.D_No
WHERE E.D_No IS NULL
5

Q20) Write a Query to display all the records from the table except matching records?

1 SELECT E.*, D.*

2 FROM Employee E
3 FULL JOIN Department D
4 ON E.D_No=D.D_No
5 WHERE E.D_No IS NULL or D.D_No IS NULL
Department Details Table
Dept_No Dept_Name

1 ECE

2 CSE

3 EEE
Course Details Table
Course_ID Course_Name Cr

1 EDC 4

2 PDC 4

3 SS 4

4 DAA 4

5 OS 4
Student Details Table
Student_No Student_Name

101 Anil

102 Sunil

103 Ajay

104 Vijay

105 John
Enroll Details Table
Enroll_Date Student_No Dpet_No S_ID

1/2/2014 101 10 S1

3/2/2016 102 10 S1

3/2/2016 103 10` S1

3/2/2016 104 20 S2

3/2/2016 105 20 S2
Address Table
Emp_No Address

E1 Hyderabad

E2 Vizag

E3 Hyderabad
E4 Bangalore

E5 Hyderabad
Employee Details Table
Emp_No Emp_Name

E1 Arun

E2 Kiran

E3 Kumar

E4 Anus

E5 James
Semester Details Table
Semester Sn

S1 1

S2 2-1

S3 2-2

S4 3-1

S5 3-2

S6 4-1

S7 4-2
Course Department Details
Dept_No Course_ID

10 1

10 2

10 3

20 4

20 5
Syllabus Table
Dept_No Course_ID S_ID

10 1 S1

10 2 S1

10 3 S1

20 4 S2

20 5 S2
Instructor Details Table
Emp_No Dept_No

E1 10

E2 10

E3 10

E4 20

E5 30
Course Instructor Table
Course_ID Emp_No S_ID Dept_No

1 E1 S1 10

1 E1 S1 20

1 E2 S1 30

2 E3 S1 10

4 E4 S2 20

5 E4 S2 20

5 E5 S1 10

Q) Write a query to display Student No, Student Name, Enroll Date, Department Name?

1 SELECT S.Student_No, S.Student_Name, S.Enroll_Date, D.Dept_Name

FROM Student S
2 INNER JOIN Enroll E
3 ON S.Student_No=E.Student_No
4 INNER JOIN Department D
5 ON D.Dept_No=E.Dept_No
6

Q) Write a query to display Employee Number, Employee Name and address, department
name?

1
SELECT E.Emp_No, E.Emp_Name, A.Address, D.Dept_Name
2 FROM Employee E
3 INNER JOIN Address A
4 ON E.Emp_No=A.Emp_No
5 INNER JOIN Instructor I
6 ON A.Emp_No=I.Emp_No
INNER JOIN Department D
7 ON I.Dept_No=D.Dept_No
8

Q) Write a query to display course name belongs to ECE department?

1
SELECT C.Course_Name
2 FROM Department D
3 INNER JOIN Course Department CD
4 ON D.Dept_NO=CD.Dept_NO
5 INNER JOIN Course C
ON CD.CourseDept_ID=C.Course_ID
6 WHERE D.Dept_Name=’ECE’
7

Q) ) Write a query to display student number, student name, enroll date, dept name,
semester name?

1 SELECT S.Student_No, S.Student_Name, S.Enroll_Date, D.Dpet_Name,

2 Sem.Student_Name
3 FROM Enroll E
INNER JOIN Student S
4 ON S.Student_No=E.Student_No
5 INNER JOIN Deprtment D
6 ON E.Dept_No=D.Dept_No
7 INNER JOIN Semester SE
8 ON E.Student_ID=Sem.Student_ID

Q) Write a query to display the syllabus of ECE department 1st year?

1 SELECT C.Course_Name
2 FROM Department D
3 INNER JOIN Syllabus Sy
ON D.Dept_No=Sy.Dept_No
4 INNER JOIN Course C
5 ON Sy.Course_ID=C.Course_ID
6 INNER JOIN Semester Se
7 ON Syllabus_Sy_ID=Se_Sy_ID
8 WHERE D.Dept_Name=’ECE’ and Se.Semester=’1’
9

Q) Write a query to display the employee names and faculty names of ECE dept 1st
year?

1
2 SELECT E.Emp_Name
FROM Employee E
3 INNER JOIN Course Instructor Ci
4 ON E.Emp_No=Ci.Emp_No
5 INNER JOIN Semester Se
6 ON Se.Student_ID=Ci.Student_ID
INNER JOIN Dept D
7 ON Ci.Dept_No=D.Dept_No
8 WHERE D.Dept_Name=’ECE’ and Se.Student_Name=’1’
9

Q) ) Write a query to display student details who enrolled for ECE department?

1
SELECT S.Student_NO, S.Student_Name, S.Enroll_Date
2 FROM Student S
3 INNER JOIN Enroll E
4 ON S.Student_No=E.Student_No
5 INNER JOIN Department D
ON E.Dept_No=D.Dept_No
6 WHERE D.Dept_Name=’ECE’
7

Q) ) Write a query to display student details along with dept name who are enrolled in
ECE department first year?

1
SELECT S.Student_No, S.Student_Name, S.Enroll_Date, D.Dept_Name
2
FROM Student S
3 INNER JOIN Enrollment E
4 ON S.Student_No=E.Student_No
5 INNER JOIN Department D
6 ON D.Dept_No=E.Dept_No
INNER JOIN Semester Se
7 ON E.Student_ID=Se.Student_ID
8 WHERE D.Dept_Name=’ECE’ and Se.Student_Name=’1’
9

Q) ) Write a query to display employee name who is teaching EDC?

1 SELECT E.Emp_Name
2 FROM Employee E
INNER JOIN Course Instructor Ci
3 ON E.Emp_No=Ci.Emp_No
4 INNER JOIN Course C
5 ON Ci.Course_ID=C.Course_ID
6 WHERE C.Course_Name=’EDC’
7

Q) ) Write a query to display employee details along with dept name who are staying in
Hyderabad?

1
2 SELECT E.Emp_No, Emp_Name, D.Dept_Name
FROM Employee E
3 INNER JOIN Address A
4 ON E.Emp_No=A.Emp_No
5 INNER JOIN Instructor I
6 ON A.Emp_No=I.Emp_No
INNER JOIN Department D
7 ON I.Dept_No=D.Dept_No
8 WHERE A.Address=’hyderabad’
9

Emp_No Emp_Name Salary Age Dept_Name

101 Anil 20,000 22 ECE

102 Sunil 23000 23 EEE

103 Vijay 32000 24 CSE

Using Range Operator:: BETWEEN, NOT BETWEEN

Q) Write a Query to display employee details whose salary > 20000 and whose age >23?

1 SELECT * FROM Employee

2 WHERE Salary>20000 AND Age>23;

Q) Write a Query to display employee details whose salary >20000 and who is working in
ECE department?

1 SELECT * FROM Employee

2 WHERE Salary>20000 AND Dept_Name=’ECE’

Q) Write a Query to display employee details whose age is BETWEEN 18 and 22?

1 SELECT * FROM Employee Details

2 WHERE Age BETWEEN 18 AND 22;

Q) Write a Query to display employee details whose salary range BETWEEN 20000 and
23000?
1 SELECT * FROM Employee
2 WHERE Salary BETWEEN 20000 AND 23000;

Q) Write a Query to display employee details whose age is NOT BETWEEN 18 & 22?

1 SELECT * FROM Employee

2 WHERE Age NOT BETWEEN 18 AND 22;
Using String Operators:: LIKE, NOT LIKE

Q) Write a Query to display employee details whose name starts with a?

1 SELECT * FROM Employee

2 WHERE Emp_Name LIKE ‘a%’
a% ----> starts with a
%a ----> ends with a

Q) Write a Query to display employee details and whose age>20 & whose name starts
with a?

1 SELECT * FROM Employee

2 WHERE Salary>20000 AND Age>20 AND Emp_Name LIKE ‘a%’

Q) Write a Query to display employee details whose name not starts with a?

1 SELECT * FROM employee

2 WHERE Emp_Name NOT LIKE ‘a%’
List of Related Microsoft Certification Courses:

SSIS Power BI

SSRS SharePoint

SSAS SQL Server DBA

SCCM BizTalk Server

Team Foundation Server BizTalk Server Administrator

1. What are the two authentication modes in SQL Server?

There are two authentication modes –

  Windows Mode
 Mixed Mode

Modes can be changed by selecting the tools menu of SQL Server configuration
properties and choose security page.

2. What Is SQL Profiler?

SQL Profiler is a tool which allows system administrator to monitor events in the
SQL server. This is mainly used to capture and save data about each event of a file
or a table for analysis.

3. What is recursive stored procedure?

SQL Server supports recursive stored procedure which calls by itself. Recursive
stored procedure can be defined as a method of problem solving wherein the
solution is arrived repetitively. It can nest up to 32 levels.

CREATE PROCEDURE [dbo].[Fact]

(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively)
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN
GO

4. What are the differences between local and global temporary tables?

 Local temporary tables are visible when there is a connection, and are
deleted when the connection is closed.

CREATE TABLE #<tablename>

  Global temporary tables are visible to all users, and are deleted when the
connection that created it is closed.

CREATE TABLE ##<tablename>

5. What is CHECK constraint?

A CHECK constraint can be applied to a column in a table to limit the values that
can be placed in a column. Check constraint is to enforce integrity.

6. Can SQL servers linked to other servers?

SQL server can be connected to any database which has OLE-DB provider to give a
link. Example: Oracle has OLE-DB provider which has link to connect with the SQL
server group.

7. What is sub query and its properties?

A sub-query is a query which can be nested inside a main query like Select, Update,
Insert or Delete statements. This can be used when expression is allowed.
Properties of sub query can be defined as

 A sub query should not have order by clause

 A sub query should be placed in the right hand side of the comparison
operator of the main query
 A sub query should be enclosed in parenthesis because it needs to be
executed first before the main query
 More than one sub query can be included

8. What are the types of sub query?

There are three types of sub query –

 Single row sub query which returns only one row

 Multiple row sub query which returns multiple rows
 Multiple column sub query which returns multiple columns to the main query.
With that sub query result, Main query will be executed.

9. What is SQL server agent?

The SQL Server agent plays a vital role in day to day tasks of SQL server
administrator(DBA). Server agent's purpose is to implement the tasks easily with the
scheduler engine which allows our jobs to run at scheduled date and time.
10. What are scheduled tasks in SQL Server?

Scheduled tasks or jobs are used to automate processes that can be run on a
scheduled time at a regular interval. This scheduling of tasks helps to reduce human
intervention during night time and feed can be done at a particular time. User can
also order the tasks in which it has to be generated.

11. What is COALESCE in SQL Server?

COALESCE is used to return first non-null expression within the arguments. This
function is used to return a non-null from more than one column in the arguments.

Example –

Select COALESCE(empno, empname, salary) from employee;

12. How exceptions can be handled in SQL Server Programming?

Exceptions are handled using TRY----CATCH constructs and it is handles by writing

scripts inside the TRY block and error handling in the CATCH block.

13. What is the purpose of FLOOR function?

FLOOR function is used to round up a non-integer value to the previous least

integer. Example is given

FLOOR(6.7)

Returns 6.

14. Can we check locks in database? If so, how can we do this lock check?

Yes, we can check locks in the database. It can be achieved by using in-built stored
procedure called sp_lock.

15. What is the use of SIGN function?

SIGN function is used to determine whether the number specified is Positive,

Negative and Zero. This will return +1,-1 or 0.

Example –

SIGN(-35) returns -1

16. What is a Trigger?

Triggers are used to execute a batch of SQL code when insert or update or delete
commands are executed against a table. Triggers are automatically triggered or
executed when the data is modified. It can be executed automatically on insert,
delete and update operations.

17. What are the types of Triggers?

There are four types of triggers and they are:

 Insert
 Delete
 Update
 Instead of

18. What is an IDENTITY column in insert statements?

IDENTITY column is used in table columns to make that column as Auto incremental
number or a surrogate key.

19. What is Bulkcopy in SQL?

Bulkcopy is a tool used to copy large amount of data from Tables. This tool is used
to load large amount of data in SQL Server.

20. What will be query used to get the list of triggers in a database?

Query to get the list of triggers in database-

Select * from sys.objects where type='tr'

21. What is the difference between UNION and UNION ALL?

 UNION: To select related information from two tables UNION command is

used. It is similar to JOIN command.
 UNION All: The UNION ALL command is equal to the UNION command,
except that UNION ALL selects all values. It will not remove duplicate rows,
instead it will retrieve all rows from all tables.

22. How Global temporary tables are represented and its scope?

Global temporary tables are represented with ## before the table name. Scope will
be the outside the session whereas local temporary tables are inside the session.
Session ID can be found using @@SPID.

23. What are the differences between Stored Procedure and the dynamic SQL?
Stored Procedure is a set of statements which is stored in a compiled form. Dynamic
SQL is a set of statements that dynamically constructed at runtime and it will not be
stored in a Database and it simply execute during run time.

24. What is Collation?

Collation is defined to specify the sort order in a table. There are three types of sort
order –

1. Case sensitive
2. Case Insensitive
3. Binary

25. How can we get count of the number of records in a table?

Following are the queries can be used to get the count of records in a table -

Select * from <tablename> Select count(*) from <tablename> Select rows from sysindexes where
id=OBJECT_ID(tablename) and indid<2

26. What is the command used to get the version of SQL Server?

Select SERVERPROPERTY('productversion')

is used to get the version of SQL Server.

27. What is UPDATE_STATISTICS command?

UPDATE_STATISTICS command is used to update the indexes on the tables when

there is a large amount of deletions or modifications or bulk copy occurred in
indexes.

28. What is the use of SET NOCOUNT ON/OFF statement?

By default, NOCOUNT is set to OFF and it returns number of records got affected
whenever the command is getting executed. If the user doesn't want to display the
number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).

29. Which SQL server table is used to hold the stored procedure scripts?

Sys.SQL_Modules is a SQL Server table used to store the script of stored

procedure. Name of the stored procedure is saved in the table called
Sys.Procedures.

30. What are Magic Tables in SQL Server?

During DML operations like Insert, Delete, and Update SQL Server create magic
tables to hold the values during the DML operations. These magic tables are used
inside the triggers for data transaction.

31. What is the difference between SUBSTR and CHARINDEX in the SQL
Server?

The SUBSTR function is used to return specific portion of string in a given string.
But, INSTR function gives character position in a given specified string.

SUBSTR("Smiley",3)

Gives result as Smi

CHARINDEX("Smiley",'i',1)

Gives 3 as result as I appears in 3rd position of the string

32. What is the use of =,==,=== operators?

= is used to assign one value or variable to another variable. == is used for

comparing two strings or numbers. === is used to compare only string with the string
and number with numbers.

33. What is ISNULL() operator?

ISNULL function is used to check whether value given is NULL or not NULL in sql
server. This function also provides to replace a value with the NULL.

34. What is the use of FOR Clause?

FOR clause is mainly used for XML and browser options. This clause is mainly used
to display the query results in XML format or in browser.

35. What will be the maximum number of index per table?

For SQL Server 2008 100 Index can be used as maximum number per table. 1
Clustered Index and 999 Non-clustered indexes per table can be used in SQL
Server.

1000 Index can be used as maximum number per table. 1 Clustered Index and 999
Non-clustered indexes per table can be used in SQL Server.

1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL
Server.
36. What is the difference between COMMIT and ROLLBACK?

Every statement between BEGIN and COMMIT becomes persistent to database

when the COMMIT is executed. Every statement between BEGIN and ROOLBACK
are reverted to the state when the ROLLBACK was executed.

37. What is the difference between varchar and nvarchar types?

Varchar and nvarchar are same but the only difference is that nvarhcar can be used
to store Unicode characters for multiple languages and it also takes more space
when compared with varchar.

38. What is the use of @@SPID?

A @@SPID returns the session ID of the current user process.

39. What is the command used to Recompile the stored procedure at run
time?

Stored Procedure can be executed with the help of keyword called RECOMPILE.

Example

Exe <SPName> WITH RECOMPILE

Or we can include WITHRECOMPILE in the stored procedure itself.

40. How to delete duplicate rows in SQL Server?

Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.

41. Where are SQL Server user names and passwords stored in SQL Server?

User Names and Passwords are stored in sys.server_principals and sys.sql_logins.

But passwords are not stored in normal text.

42. What is the difference between GETDATE and SYSDATETIME?

Both are same but GETDATE can give time till milliseconds and SYSDATETIME
can give precision till nanoseconds. SYSDATE TIME is more accurate than
GETDATE.

43. How data can be copied from one table to another table?

INSERT INTO SELECT

This command is used to insert data into a table which is already created.

SELECT INTO

This command is used to create a new table and its structure and data can be
copied from existing table.

44. What is TABLESAMPLE?

TABLESAMPLE is used to extract sample of rows randomly that are all necessary
for the application. The sample rows taken are based on the percentage of rows.

45. Which command is used for user defined error messages?

RAISEERROR is the command used to generate and initiates error processing for a
given session. Those user defined messages are stored in sys.messages table.

46. What do mean by XML Datatype?

XML data type is used to store XML documents in the SQL Server database.
Columns and variables are created and store XML instances in the database.

47. What is CDC?

CDC is abbreviated as Change Data Capture which is used to capture the data that
has been changed recently. This feature is present in SQL Server 2008.

48. What is SQL injection?

SQL injection is an attack by malicious users in which malicious code can be

inserted into strings that can be passed to an instance of SQL server for parsing and
execution. All statements have to checked for vulnerabilities as it executes all
syntactically valid queries that it receives.

Even parameters can be manipulated by the skilled and experienced attackers.

49. What are the methods used to protect against SQL injection attack?

Following are the methods used to protect against SQL injection attack:

 Use Parameters for Stored Procedures

 Filtering input parameters
 Use Parameter collection with Dynamic SQL
 In like clause, user escape characters
50. What is Filtered Index?

Filtered Index is used to filter some portion of rows in a table to improve query
performance, index maintenance and reduces index storage costs. When the index
is created with WHERE clause, then it is called Filtered Index

Q#1. Which TCP/IP port does SQL Server run on?

Ans. By default SQL Server runs on port 1433.
Q#2. What is the difference between clustered and a non-clustered index?
Ans. A clustered index is an index that rearranges the table in the order of index itself. Its leaf
nodes contain data pages. A table can have only one clustered index.
A non-clustered index is an index that does not re-arranges the table in the order of index itself.
Its leaf nodes contain index rows instead of data pages. A table can have many non-clustered
indexes.
Q#3. List the different index configurations possible for a table?
Ans. A table can have one of the following index configurations:
 No indexes
 A clustered index
 A clustered index and many non-clustered indexes
 A non-clustered index
 Many non-clustered indexes
Q#4. What is the recovery model? List the types of recovery model available in SQL
Server?
Ans. Recovery model basically tells SQL Server what data should be kept in the transaction log
file and for how long. A database can have only one recovery model.
It also tells SQL server that which backup is possible in a particular recovery model selected.
There are three types of recovery model:

 Full
 Simple
 Bulk-Logged
Q#5. What are different backups available in SQL Server?
Ans. Different possible backups are:
 Full backup
 Differential Backup
 Transactional Log Backup
 Copy Only Backup
 File and Filegroup backup
Q#6. What is a FULL Backup?
Ans. A full backup is the most common type of backup in SQL Server. This is the complete
backup of the database. It also contains part of transaction log so it can be recovered.
Q#7. What is OLTP?
Ans. OLTP means Online transaction processing which follows rules of data normalization to
ensure data integrity. Using these rules complex information is broken down into a most simple
structure.
Q#8. What is RDBMS?
Ans. RDBMS or Relational Data Base Management Systems are database management systems
that maintain data in the form of tables. We can create relationships between the tables. An
RDBMS has the capability to recombine the data items from different files, providing powerful
tools for data usage.
Q#9. What are the properties of the Relational tables?
Ans. Relational tables have six properties:
 Values are atomic.
 Column values are of the same kind.
 Each row is unique.
 The sequence of columns is insignificant.
 The sequence of rows is insignificant.
 Each column must have a unique name.
Q#10. What's the difference between a primary key and a unique key?
Ans. The differences between the primary key and a unique key are:
 The primary key is a column whose values uniquely identify every row in a table.
Primary key values can never be reused. They create a clustered index on the column and
cannot be null.
 A Unique key is a column whose values also uniquely identify every row in a table but
they create a non-clustered index by default and it allows one NULL only.
Q#11. When is UPDATE_STATISTICS command used?
Ans. As the name implies UPDATE_STATISTICS command updated the statistics used by the
index to make the search easier.
Q#12. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Ans. The differences between HAVING CLAUSE and WHERE CLAUSE are:
 Both specify a search condition but Having clause is used only with the SELECT
statement and typically used with GROUP BY clause.
 If GROUP BY clause is not used then Having behaved like WHERE clause only.
Q#13. What is Mirroring?
Ans. Mirroring is a high availability solution. It is designed to maintain a hot standby server
which is consistent with the primary server in terms of a transaction. Transaction Log records are
sent directly from principal server to a secondary server which keeps a secondary server up to
date with the principal server.
Q#14. What are the advantages of the Mirroring?
Ans. Advantages of Mirroring are:
 It is more robust and efficient that Log shipping.
 It has an automatic failover mechanism.
 The secondary server is synced with the primary in near real time.
Q#15. What is Log Shipping?
Ans. Log shipping is nothing but the automation of backup and restores of a database from one
server to another standalone standby server. This is one of the disaster recovery solutions. If one
server fails for some reason we will have the same data available on the standby server.
Q#16. What are the advantages of Log shipping?

 Blocking
 Missing and unused indexes.
 I/O bottlenecks
 Poor Query plans
 Fragmentation
Q#23. List the various tools available for performance tuning?
Ans. There are various tools available for performance tuning:
 Dynamic Management Views
 SQL Server Profiler
 Server Side Traces
 Windows Performance monitor.
 Query Plans
 Tuning advisor
Q#24. What is a performance monitor?
Ans. Windows performance monitor is a tool to capture metrics for the entire server. We can use
this tool for capturing events of SQL server also.
Some useful counters are – Disks, Memory, Processors, Network etc.
Q#25. What are 3 ways to get a count of the number of records in a table?
Ans. SELECT * FROM table_Name
SELECT COUNT(*) FROM table_Name
SELECT rows FROM indexes WHERE id = OBJECT_ID(tableName) AND indid< 2
Q#26. Can we rename a column in the output of SQL query?
Ans. Yes by using the following syntax we can do this.
SELECT column_name AS new_name FROM table_name;

Q#27. What is the difference between a Local and a Global temporary table?
Ans. If defined in inside a compound statement a local temporary table exists only for the
duration of that statement but a global temporary table exists permanently in the database but its
rows disappear when the connection is closed.
Q#28. What is the SQL Profiler?
Ans. SQL Profiler provides a graphical representation of events in an instance of SQL Server for
the monitoring and investment purpose. We can capture and save the data for further
analysis. We can put filters as well to captures the specific data we want.
Q#29. What do you mean by authentication modes in SQL Server?
Ans. There are two authentication modes in SQL Server.
 Windows mode
 Mixed Mode – SQL and Windows.
Q#30. How can we check the SQL Server version?
Ans. By running the following command:
SELECT @@Version

Q#31. Is it possible to call a stored procedure within a stored procedure?

Ans. Yes, we call a stored procedure within a stored procedure It is called recursion property of
SQL server and these type of stored procedures are called nested stored procedures.
Q#32. What is SQL Server Agent?
Ans. SQL Server agent allows us to schedule the jobs and scripts. It helps is implementing the
day to day DBA tasks by automatically executing them on a scheduled basis.
Q#33. What is the PRIMARY KEY?
Ans. The primary key is a column whose values uniquely identify every row in a table. Primary
key values can never be reused.
Q#34. What is a UNIQUE KEY constraint?
Ans. A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no
duplicate values are entered. The unique key constraints are used to enforce entity integrity as the
primary key constraints.
Q#35. What is FOREIGN KEY
Ans. When a one table’s primary key field is added to related tables in order to create the
common field which relates the two tables, it called a foreign key in other tables.
Foreign Key constraints enforce referential integrity.

Q#36. What is a CHECK Constraint?

Ans. A CHECK constraint is used to limit the values or type of data that can be stored in a
column. They are used to enforce domain integrity.
Q#37. What are a Scheduled Jobs?
Ans. The scheduled job allows a user to run the scripts or SQL commands automatically on the
scheduled basis. The user can determine the order in which commands need to execute and the
best time to run the job to avoid the load on the system.
Q#38. What is a heap?
Ans. A heap is a table that does not contain any clustered index or non-clustered index.
Q#39. What is BCP?
Ans. BCP or Bulk Copy is a tool by which we can copy a large amount of data to tables and
views. BCP does not copy the structures same as source to destination. BULK INSERT
command helps to import a data file into a database table or view in a user-specified format.
Q#40. What is Normalization?
Ans. The process of table design to minimize the data redundancy is called normalization. We
need to divide a database into two or more table and define relationships between them.
Normalization usually involves dividing a database into two or more tables and defining
relationships between the tables.
Q#41. List the different normalization forms?
Ans. Different normalization forms are:
1NF (Eliminate Repeating Groups): Make a separate table for each set of related attributes,
and give each table a primary key. Each field contains at most one value from its attribute
domain.
2NF (Eliminate Redundant Data): If an attribute depends on only part of a multi-valued key,
remove it to a separate table.
3NF (Eliminate Columns Not Dependent On Key): If attributes do not contribute to a
description of the key, remove them to a separate table. All attributes must be directly dependent
on the primary key.
BCNF (Boyce-Codd Normal Form): If there are non-trivial dependencies between candidate
key attributes, separate them out into distinct tables.
4NF (Isolate Independent Multiple Relationships): No table may contain two or more 1:n or
n:m relationships that are not directly related.
5NF (Isolate Semantically Related Multiple Relationships): There may be practical
constraints on information that justifies separating logically related many-to-many relationships.
ONF (Optimal Normal Form): A model limited to only simple (elemental) facts, as expressed
in Object Role Model notation.
DKNF (Domain-Key Normal Form): A model free from all modification is said to be in
DKNF.
Q#42. What is De-normalization?
Ans. De-normalization is the process of adding redundant data to a database to enhance the
performance of it. It is a technique to move from higher to lower normal forms of database
modeling in order to speed up database access.
Q#43. What is a Trigger and types of a trigger?
Ans. The trigger allows us to execute a batch of SQL code when a table event occurs (Insert,
update or delete command executed against a specific table). Triggers are stored in and managed
by DBMS. It can also execute a stored procedure.
2 types of triggers that are available in the SQL Server are as follows:
 DML Triggers: DML or Data Manipulation Language triggers are invoked whenever
any of the DML commands like INSERT, DELETE or UPDATE happens on the table or
on the view.
 DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any
changes occur in the definition of any of the database objects instead of actual data.
These are very helpful to control the production and development of database
environments.
 Logon Triggers: These are very special triggers which fire in case of the logon event of
the SQL Server. This is fired before setup of a user session in the SQL Server.
Q#44. What is the Subquery?
Ans. A Subquery is a subset of select statements whose return values are used in filtering
conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE
clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another
subquery.
Types of Sub-query:
 Single-row sub-query: where the sub-query returns only one row
 Multiple-row sub-query: where the subquery returns multiple rows, and
 Multiple column sub-query: where the sub-query returns multiple columns
Q#45. What is a Linked Server?
Ans. Linked Servers is a concept by which we can connect another SQL server to a Group and
query both the SQL Servers database using T-SQL Statements
sp_addlinkedsrvloginisssed to add link server.

Q#46. What is Collation?

Ans. Collation refers to a set of rules that determine how data is sorted and compared. Character
data is sorted using rules that define the correct character sequence, with options for specifying
case sensitivity, accent marks, kana character types and character width.
Q#47. What is View?
Ans. A view is a virtual table which contains data from one or more tables. Views restrict data
access of the table by selecting only required values and make complex queries easy.
Rows updated or deleted in the view are updated or deleted in the table the view was created
with. It should also be noted that as data in the original table changes, so does data in the view,
as views are the way to look at part of the original table. The results of using a view are not
permanently stored in the database

Q#48. Where SQL server usernames and passwords are stored in a SQL server?
Ans. They get stored in System Catalog Views sys.server_principals and sys.sql_logins.
Q#49. What are the properties of a transaction?
Ans. Generally, these properties are referred to as ACID properties. They are:
 Atomicity
 Consistency
 Isolation
 Durability
Q#50. Define UNION, UNION ALL, MINUS, INTERSECT?
Ans. UNION – returns all distinct rows selected by either query.
UNION ALL – returns all rows selected by either query, including all duplicates.
MINUS – returns all distinct rows selected by the first query but not by the second.
INTERSECT – returns all distinct rows selected by both queries.
Q#51. What is SQL Server used for?
Ans. SQL Server is one of the very popular Relational Database Management Systems. This is a
product from Microsoft to store and manage the information in the database.
Q#52. Which language is supported by SQL Server?
Ans. SQL Server is based upon the implementation of the SQL also known as Structured Query
Language to work with the data inside the database.
Q#53. Which is the latest version of SQL Server and when it is released?
Ans. SQL Server 2017 is the latest version of SQL Server that is available in the market and
Microsoft launched this on 2 October 2017 with the support of the Linux O/S.
Q#54. What are the various editions of SQL Server 2017 that are available in the market?
Ans. SQL Server 2017 is available in 4 editions. These are as follows:
 Enterprise: This supports in leading the high performance for the Tier 1 database along
with the capability of supporting business intelligence and workloads of advanced
analytics.
 Standard: This supports mid-tier applications to achieve fast performance. This can be
easily upgraded to an enterprise edition also without having any changes in the coding
part.
 Express: This is available for free and supports the building
of web and mobile applications up to 10 GB in the size.
 Developer: This supports building, testing and demonstrating applications in a non-
production.
Q#55. What are functions in the SQL Server?
Ans. Functions are the sequence of the statements which accepts inputs, process the inputs to
perform some specific task and then provides the outputs. Functions should have some
meaningful name but these should not start with a special character such as %,#,@, etc.
Q#56. What is a User-Defined function in the SQL Server and what is its advantage?
Ans. User-Defined Function is a function which can be written as per the needs of the user by
implementing your own logic. The biggest advantage of this function is that the user is not
limited to pre-defined functions and can simplify the complex code of pre-defined function by
writing a simple code as per the needs.
This returns Scalar value or a table.

Q#57. Explain the creation and execution of a user-defined function in the SQL Server?
Ans. A User-Defined function can be created in the following way:

Create Function fun1(@num int)

returns table
as
return select * from employee where empid=@num
This function can be executed as follows:
select * from fun1(12)
So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of
an employee having empid=12.

Q#58. What are the Pre-Defined functions in the SQL Server?

Ans. These are Built-In functions of the SQL Server like String functions which are provided by
SQL Server like ASCII, CHAR, LEFT, etc. string functions.
Q#59. Why are Views required in the SQL Server or in any other database?
Ans. Views are very beneficial because of the following reasons:
 Views are required to hide the complexity that is involved in the database schema and
also to customize the data for a particular set of users.
 Views provide a mechanism to control access to particular rows and columns.
 These help in aggregating the data to improve the performance of the database.
Q#60. What is TCL in SQL Server?
Ans. TCL is Transaction Control Language Commands which are used to manage the
transactions in the SQL Server.
Q#61. Which TCL Commands are available on the SQL Server?
Ans. There are 3 TCL Commands in the SQL Server. These are as follows:
 Commit: This command is used to save the transaction permanently in the database.
 Rollback: This is used to roll back the changes that are done i.e. to restore the database
in the last committed state.
 Save Tran: This is used for saving the transaction so as to provide the convenience that
the transaction can be rolled back to the point wherever required.
Q#62. What are the 2 types of classifications of constraints in the SQL Server?
Ans. Constraints are classified into the following 2 types in the SQL Server:
 Column Types Constraints: These constraints are applied to the columns of a table in
the SQL Server. Definition of these can be given at the time of the creation of a table in
the database.
 Table Types Constraints: These constraints are applied on a table and these are defines
after the creation of a table is completed. Alter command is used to apply the table type
constraint.
Q#63. How is table type constraint applied to a table?
Ans. The Table Type Constraint is applied in the following way:
Alter Table Name of the Constraint
Alter Table Constraint_1
Q#64. What are the different types of Columns Types Constraints in the SQL Server?
Ans. SQL Server provides 6 types of Constraints. These are as follows:
 Not Null Constraint: This puts a constraint that the value of a column cannot be null.
 Default Constraint: This constraint provides some default value that can be inserted in
the column if no value is specified for that column.
 Check Constraint
 Unique Constraint
 Primary Key Constraint
 Foreign Key Constraint
Q#64. What command is used to delete a table from the database in the SQL Server and
how?
Ans. Delete Command is used to delete any table from the database in the SQL Server.
Following is the way to use this command:
Delete Name of the table
Ex: If the name of a table is “employee” then delete command to delete this table can be written
as Delete employee.
Q#65. Why is replication required on the SQL Server?
Ans. Replication is the mechanism which is used to synchronize the data among the multiple
servers with the help of a replica set.
This is mainly used to increase the capacity of the reading and to provide an option to its users
to select among various different servers to perform the read/write operations.
Q#66. What command is used to create a database in the SQL Server and how?
Ans. CREATEDATABASE Command is used to create any database in the SQL Server.
Following is the way to use this command:
CREATEDATABASE Name of the Database
Ex: If the name of a database is “employee” then create command to create this database can be
written as CREATEDATABASE employee.
Q#67. What function does a database engine serve in the SQL Server?
Ans. Database Engine is a type of service in the SQL Server which starts as soon as the
Operating System starts. This may run by default depending upon the settings in the O/S.
Q#68. What are the advantages of having an index on the SQL Server?
Ans. The index has the following advantages:
 Index supports the mechanism of having faster data retrieval from the database.
 This forms a data structure in a way which helps in minimizing the data comparisons.
 This improves the performance of the retrieval of the data from the database.

Q1. What is the difference between DELETE and TRUNCATE statements?

DELETE TRUNCATE

Delete command is used to delete a row in a Truncate is used to delete all the rows
table. from a table.
You can rollback data after using delete
You cannot rollback data.
statement.
It is a DML command. It is a DDL command.
It is slower than truncate statement. It is faster.
Q2. What are the different subsets of SQL?

 DDL (Data Definition Language) – It allows you to perform various operations on

the database such as CREATE, ALTER and DELETE objects.
 DML ( Data Manipulation Language) – It allows you to access and manipulate
data. It helps you to insert, update, delete and retrieve data from the database.
 DCL ( Data Control Language) – It allows you to control access to the database.
Example – Grant, Revoke access permissions.

Q3. What do you mean by DBMS? What are its different types?

A database is a structured collection of data.

A Database Management System (DBMS) is a software application that interacts with

the user, applications and the database itself to capture and analyze data.

A DBMS allows a user to interact with the database. The data stored in the database
can be modified, retrieved and deleted and can be of any type like strings, numbers,
images etc.

There are two types of DBMS:

 Relational Database Management System: The data is stored in relations

(tables). Example – MySQL.
 Non-Relational Database Management System: There is no concept of relations,
tuples and attributes. Example – Mongo

Q4. What do you mean by table and field in SQL?

A table refers to a collection of data in an organised manner in form of rows and

columns. A field refers to the number of columns in a table. For example:

Table: StudentInformation
Field: Stu Id, Stu Name, Stu Marks

Q5. What are joins in SQL?

A JOIN clause is used to combine rows from two or more tables, based on a related
column between them. It is used to merge two tables or retrieve data from there. There
are 4 joins in SQL namely:
  Inner Join
 Right Join
 Left Join
 Full Join

Q6. What is the difference between CHAR and VARCHAR2 datatype in SQL?

Both Char and Varchar2 are used for characters datatype but varchar2 is used for
character strings of variable length whereas Char is used for strings of fixed length. For
example, char(10) can only store 10 characters and will not be able to store a string of
any other length whereas varchar2(10) can store any length i.e 6,8,2 in this variable.

Q7. What is a Primary key?

 A Primary key is a column (or collection of columns)

or a set of columns that uniquely identifies each row in the table.
 Uniquely identifies a single row in the table
 Null values not allowed

Example- In the Student table, Stu_ID is the primary key.

Q8. What are Constraints?

Constraints are used to specify the limit on the data type of the table. It can be specified
while creating or altering the table statement. The sample of constraints are:

 NOT NULL
 CHECK
 DEFAULT
 UNIQUE
 PRIMARY KEY
 FOREIGN KEY

Q9. What is the difference between SQL and MySQL?

SQL is a standard language which stands for Structured Query Language based on the
English language whereas MySQL is a database management system. SQL is the core
of relational database which is used for accessing and managing database, MySQL is
an RDMS (Relational Database Management System) such as SQL Server, Informix
etc.
Q10. What is a Unique key?

 Uniquely identifies a single row in the table.

 Multiple values allowed per table.
 Null values allowed.

Apart from this SQL Interview Questions blog, if you want to get trained from
professionals on this technology, you can opt for a structured training from edureka!
Click below to know more.

Q11. What is a Foreign key?

 Foreign key maintains referential integrity by enforcing a link between the data in
two tables.
 The foreign key in the child table references the primary key in the parent table.
 The foreign key constraint prevents actions that would destroy links between the
child and parent tables.

Q12. What do you mean by data integrity?

Data Integrity defines the accuracy as well as the consistency of the data stored in a
database. It also defines integrity constraints to enforce business rules on the data
when it is entered into an application or a database.

Q13. What is the difference between clustered and non clustered index in SQL?

The differences between the clustered and non clustered index in SQL are :

1. Clustered index is used for easy retrieval of data from the database and its faster
whereas reading from non clustered index is relatively slower.
2. Clustered index alters the way records are stored in a database as it sorts out
rows by the column which is set to be clustered index whereas in a non clustered
index, it does not alter the way it was stored but it creates a separate object
within a table which points back to the original table rows after searching.
3. One table can only have one clustered index whereas it can have many non
clustered index.

Q14. Write a SQL query to display the current date?

In SQL, there is a built-in function called GetDate() which helps to return the current
timestamp/date.

Q15. List the different type of joins?

There are various types of joins which are used to retrieve data between the tables.
There are four types of joins, namely:
Inner join: Inner Join in MySQL is the most common type of join. It is used to return all
the rows from multiple tables where the join condition is satisfied.

Left Join: Left Join in MySQL is used to return all the rows from the left table but only
the matching rows from the right table where the join condition is fulfilled.

Right Join: Right Join in MySQL is used to return all the rows from the right table but
only the matching rows from the left table where the join condition is fulfilled.

Full Join: Full join returns all the records when there is a match in any of the tables.
Therefore, it returns all the rows from the left-hand side table and all the rows from the
right-hand side table.

Q16. What do you mean by Denormalization?

Denormalization refers to a technique which is used to access data from higher to lower
forms of a database. It helps the database managers to increase the performance of the
entire infrastructure as it introduces redundancy into a table. It adds the redundant data
into a table by incorporating database queries that combine data from various tables
into a single table.

Q17. What are Entities and Relationships?

Entities: A person, place, or thing in the real world about which data can be stored in a
database. Tables store data that represents one type of entity. For example – A bank
database has a customer table to store customer information. Customer table stores
this information as a set of attributes (columns within the table) for each customer.

Relationships: Relation or links between entities that have something to do with each
other. For example – The customer name is related to the customer account number
and contact information, which might be in the same table. There can also be
relationships between separate tables (for example, customer to accounts).

Q18. What is an Index?

An index refers to a performance tuning method of allowing faster retrieval of records

from the table. An index creates an entry for each value and hence it will be faster to
retrieve data.

Q19. Explain different types of index.

There are three types of index namely:

Unique Index:

This index does not allow the field to have duplicate values if the column is unique
indexed. If a primary key is defined, a unique index can be applied automatically.

Clustered Index:

This index reorders the physical order of the table and searches based on the basis of
key values. Each table can only have one clustered index.

Non-Clustered Index:

Non-Clustered Index does not alter the physical order of the table and maintains a
logical order of the data. Each table can have many nonclustered indexes.

Q20. What is Normalization and what are the

advantages of it?
Normalization is the process of organizing data to avoid duplication and
redundancy. Some of the advantages are:

 Better Database organization

 More Tables with smaller rows
 Efficient data access
 Greater Flexibility for Queries
 Quickly find the information
 Easier to implement Security
 Allows easy modification
 Reduction of redundant and duplicate data
 More Compact Database
 Ensure Consistent data after modification

Apart from this SQL Interview Questions Blog, if you want to get trained from
professionals on this technology, you can opt for a structured training from
edureka! Click below to know more.

Q21. What is the difference between DROP

and TRUNCATE commands?
DROP command removes a table and it cannot be rolled back from the database
whereas TRUNCATE command removes all the rows from the table.

Q22. Explain different types of Normalization.

There are many successive levels of normalization. These are called normal
forms. Each consecutive normal form depends on the previous one.The first three
normal forms are usually adequate.

 First Normal Form (1NF) – No repeating groups within rows

 Second Normal Form (2NF) – Every non-key (supporting) column value is
dependent on the whole primary key.
 Third Normal Form (3NF) – Dependent solely on the primary key and no
other non-key (supporting) column value.

Q23. What is ACID property in a database?

ACID stands for Atomicity, Consistency, Isolation, Durability. It is used to ensure
that the data transactions are processed reliably in a database system.

Atomicity: Atomicity refers to the transactions that are completely done or failed
where transaction refers to a single logical operation of a data. It means if one part
of any transaction fails, the entire transaction fails and the database state is left
unchanged.

Consistency: Consistency ensures that the data must meet all the validation rules.
In simple words, you can say that your transaction never leaves the database
without completing its state.

Isolation: The main goal of isolation is concurrency control.

Durability: Durability means that if a transaction has been committed, it will occur
whatever may come in between such as power loss, crash or any sort of error.

Q24. What do you mean by “Trigger” in SQL?

Trigger in SQL is are a special type of stored procedures that are defined to execute
automatically in place or after data modifications. It allows you to execute a batch
of code when an insert, update or any other query is executed against a specific
table.

Q25. What are the different operators

available in SQL?
There are three operators available in SQL, namely:

1. Arithmetic Operators
2. Logical Operators
3. Comparison Operators
Apart from this SQL Interview Questions blog, if you want to get trained from
professionals on this technology, you can opt for a structured training from
edureka! Click below to know more.

Q26. Are NULL values same as that of zero or

a blank space?
A NULL value is not at all same as that of zero or a blank space. NULL value
represents a value which is unavailable, unknown, assigned or not applicable
whereas a zero is a number and blank space is a character.

Q27. What is the difference between cross

join and natural join?
The cross join produces the cross product or Cartesian product of two tables
whereas the natural join is based on all the columns having the same name and
data types in both the tables.

Q28. What is subquery in SQL?

A subquery is a query inside another query where a query is defined to retrieve
data or information back from the database. In a subquery, the outer query is
called as the main query whereas the inner query is called subquery. Subqueries
are always executed first and the result of the subquery is passed on to the main
query. It can be nested inside a SELECT, UPDATE or any other query. A subquery
can also use any comparison operators such as >,< or =.

Q29. What are the different types of a

subquery?
There are two types of subquery namely, Correlated and Non-Correlated.

Correlated subquery: These are queries which select the data from a table
referenced in the outer query. It is not considered as an independent query as it
refers to another table and refers the column in a table.

Non-Correlated subquery: This query is an independent query where the output

of subquery is substituted in the main query.

Q30. List the ways to get the count of records

in a table?
To count the number of records in a table, you can use the below commands:

SELECT * FROM table1

SELECT COUNT(*) FROM table1

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2

Apart from this SQL Interview Questions Blog, if you want to get trained from
professionals on this technology, you can opt for a structured training from
edureka! Click below to know more.

Q31. Write a SQL query to find the names of

employees that begin with ‘A’?
To display name of the employees that begin with ‘A’, type in the below command:

1 SELECT * FROM Table_name WHERE EmpName like 'A%'

Q32. Write a SQL query to get the third

highest salary of an employee from
employee_table?
1 SELECT TOP 1 salary
2 FROM(
3 SELECT TOP 3 salary
4 FROM employee_table
5 ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
6
Q33. What is the need for group functions in SQL?

Group functions work on the set of rows and returns one result per group. Some of
the commonly used group functions are: AVG, COUNT, MAX, MIN, SUM, VARIANCE.

Q34 . What is a Relationship and what are

they?
Relation or links are between entities that have something to do with each other.
Relationships are defined as the connection between the tables in a database. There
are various relationships, namely:

 One to One Relationship.

 One to Many Relationship.
 Many to One Relationship.
  Self-Referencing Relationship.

Q35. How can you insert NULL values in a

column while inserting the data?
NULL values can be inserted in the following ways:

 Implicitly by omitting column from column list.

 Explicitly by specifying NULL keyword in the VALUES clause

Q36. What is the main difference between

‘BETWEEN’ and ‘IN’ condition operators?
BETWEEN operator is used to display rows based on a range of values in a row
whereas the IN condition operator is used to check for values contained in a specific
set of values.

Example of BETWEEN:
SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;
Example of IN:
SELECT * FROM students where ROLL_NO IN (8,15,25);
Q37. Why are SQL functions used?
SQL functions are used for the following purposes:

 To perform some calculations on the data

 To modify individual data items

 To manipulate the output

 To format dates and numbers

 To convert the data types

Q38. What is the need of MERGE statement?

This statement allows conditional update or insertion of data into a table. It
performs an UPDATE if a row exists, or an INSERT if the row does not exist.

Q39. What do you mean by recursive stored

procedure?
Recursive stored procedure refers to a stored procedure which calls by itself until it
reaches some boundary condition. This recursive function or procedure helps the
programmers to use the same set of code n number of times.

Q40. What is CLAUSE in SQL?

SQL clause helps to limit the result set by providing a condition to the query. A
clause helps to filter the rows from the entire set of records.

For example – WHERE, HAVING clause.

Apart from this SQL Interview Questions Blog, if you want to get trained from
professionals on this technology, you can opt for a structured training from
edureka! Click below to know more.

Q41. What is the difference between

‘HAVING’ CLAUSE and a ‘WHERE’ CLAUSE?
HAVING clause can be used only with SELECT statement. It is usually used in a
GROUP BY clause and whenever GROUP BY is not used, HAVING behaves like a
WHERE clause.
Having Clause is only used with the GROUP BY function in a query whereas WHERE
Clause is applied to each row before they are a part of the GROUP BY function in a
query.

Q42. List the ways in which Dynamic SQL can

be executed?
Following are the ways in which dynamic SQL can be executed:

 Write a query with parameters.

 Using EXEC.

 Using sp_executesql.

Q43. What are the various levels of

constraints?
Constraints are the representation of a column to enforce data entity and
consistency. There are two levels of a constraint, namely:

 column level constraint

  table level constraint

Q44. How can you fetch common records

from two tables?
You can fetch common records from two tables using INTERSECT. For example:

Select studentID from student. <strong>INTERSECT </strong> Select StudentID

1
from Exam
Q45. List some case manipulation functions in
SQL?
There are three case manipulation functions in SQL, namely:

 LOWER: This function returns the string in lowercase. It takes a string as an

argument and returns it by converting it into lower case. Syntax:

LOWER(‘string’)

 UPPER: This function returns the string in uppercase. It takes a string as an

argument and returns it by converting it into uppercase. Syntax:

UPPER(‘string’)

 INITCAP: This function returns the string with the first letter in uppercase
and rest of the letters in lowercase. Syntax:

INITCAP(‘string’)

Apart from this SQL Interview Questions blog, if you want to get trained from
professionals on this technology, you can opt for a structured training from
edureka! Click below to know more.

Q46. What are the different set operators

available in SQL?
Some of the available set operators are – Union, Intersect or Minus operators.

Q47. What is an ALIAS command?

ALIAS name can be given to any table or a column. This alias name can be referred
in WHERE clause to identify a particular table or a column.
For example-

Select emp.empID, dept.Result from employee emp, department as dept where

emp.empID=dept.empID
In the above example, emp refers to alias name for employee table and dept refers
to alias name for department table.

Q48. What are aggregate and scalar

functions?
Aggregate functions are used to evaluate mathematical calculation and returns a
single value. These calculations are done from the columns in a table. For example-
max(),count() are calculated with respect to numeric.

Scalar functions return a single value based on the input value. For example –
UCASE(), NOW() are calculated with respect to string.

Q49. How can you fetch alternate records

from a table?
You can fetch alternate records i.e both odd and even row numbers. For example-
To display even numbers, use the following command:

Select studentId from (Select rowno, studentId from student) where

mod(rowno,2)=0

Now, to display odd numbers:

Select studentId from (Select rowno, studentId from student) where

mod(rowno,2)=1

Q50. Name the operator which is used in the query for pattern matching?

LIKE operator is used for pattern matching, and it can be used as -.

1. % – It matches zero or more characters.

For example- select * from students where studentname like ‘a%’

_ (Underscore) – it matches exactly one character.

For example- select * from student where studentname like ‘abc_’
Apart from this SQL Interview Questions Blog, if you want to get trained from
professionals on this technology, you can opt for a structured training from
edureka! Click below to know more.

Q51. How can you select unique records from

a table?
You can select unique records from a table by using the DISTINCT keyword.

Select DISTINCT studentID from Student

Using this command, it will print unique student id from the table Student.

Q52. How can you fetch first 5 characters of

the string?
There are a lot of ways to fetch characters from a string. For example:

Select SUBSTRING(StudentName,1,5) as studentname from student

Q53. What is the main difference between

SQL and PL/SQL?
SQL is a query language that allows you to issue a single query or execute a single
insert/update/delete whereas PL/SQL is Oracle’s “Procedural Language” SQL, which
allows you to write a full program (loops, variables, etc.) to accomplish multiple
operations such as selects/inserts/updates/deletes.

Q54. What is a View?

A view is a virtual table which consists of a subset of data contained in a table.
Since views are not present, it takes less space to store. View can have data of one
or more tables combined and it depends on the relationship.

Q55. What are Views used for?

A view refers to a logical snapshot based on a table or another view. It is used for
the following reasons:

 Restricting access to data.

 Making complex queries simple.
 Ensuring data independence.
 Providing different views of same data.
Q56. What is a Stored Procedure?
A Stored Procedure is a function which consists of many SQL statements to access
the database system. Several SQL statements are consolidated into a stored
procedure and execute them whenever and wherever required which saves time
and avoid writing code again and again.

Q57. List some advantages and

disadvantages of Stored Procedure?
Advantages:

A Stored Procedure can be used as a modular programming which means create

once, store and call for several times whenever it is required. This supports faster
execution. It also reduces network traffic and provides better security to the data.

Disadvantage:

The only disadvantage of Stored Procedure is that it can be executed only in the
database and utilizes more memory in the database server.

Q58. List all the types of user-defined

functions?
There are three types of user-defined functions, namely:

 Scalar Functions
 Inline Table-valued functions
 Multi-statement valued functions

Scalar returns the unit, variant defined the return clause. Other two types of
defined functions return table.

Q59. What do you mean by Collation?

Collation is defined as a set of rules that determine how data can be sorted as well
as compared. Character data is sorted using the rules that define the correct
character sequence along with options for specifying case-sensitivity, character
width etc.

Q60. What are the different types of Collation

Sensitivity?
Following are the different types of collation sensitivity:

 Case Sensitivity: A and a and B and b.

 Kana Sensitivity: Japanese Kana characters.
 Width Sensitivity: Single byte character and double-byte character.
 Accent Sensitivity.

Apart from this SQL Interview Questions Blog, if you want to get trained from
professionals on this technology, you can opt for a structured training from
edureka! Click below to know more.

Q61. What are Local and Global variables?

Local variables:

These variables can be used or exist only inside the function. These variables are
not used or referred by any other function.

Global variables:

These variables are the variables which can be accessed throughout the program.
Global variables cannot be created whenever that function is called.

Q62. What is Auto Increment in SQL?

Auto increment keyword allows the user to create a unique number to get
generated whenever a new record is inserted into the table.
This keyword is usually required whenever PRIMARY KEY is used.

AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be
used in SQL SERVER.

Q63. What is a Datawarehouse?

Datawarehouse refers to a central repository of data where the data is assembled
from multiple sources of information. Those data are consolidated, transformed and
made available for the mining as well as online processing. Warehouse data also
have a subset of data called Data Marts.

Q64. What are the different authentication

modes in SQL Server? How can it be changed?
Windows mode and Mixed Mode – SQL and Windows. You can go to the below steps
to change authentication mode in SQL Server:
  Click Start> Programs> Microsoft SQL Server and click SQL Enterprise
Manager to run SQL Enterprise Manager from the Microsoft SQL Server
program group.

 Then select the server from the Tools menu.

 Select SQL Server Configuration Properties, and choose the Security page.

Q65. What are STUFF and REPLACE function?

STUFF Function: This function is used to overwrite existing character or inserts a
string into another string. Syntax:
STUFF(string_expression,start, length, replacement_characters)
where,
string_expression: it is the string that will have characters substituted
start: This refers to the starting position
length: It refers to the number of characters in the string which are substituted.
replacement_string: They are the new characters which are injected in the string.

REPLACE function: This function is used to replace the existing characters of all
the occurrences. Syntax:
REPLACE (string_expression, search_string, replacement_string)
Here every search_string in the string_expression will be replaced with the
replacement_string.

1. Truncate vs Delete in MSSQL .

Delete in MSSQL

 Deleting is one the most expensive operation, it's logged. Every row deleted is logged to the
transaction log.
 Delete from ... allows to you delete specific rows.
 Delete from ... marks the row as a ghost row.
 Delete from ... does not release the space of the deleted rows, you will need to run DBCC
SHRINKFILE to recover the space [single user mode].

Truncate in MSSQL

 Often people want to empty a temporary table to repopulate it with current data, then it's much
faster to use the truncate statement instead of the delete statement.
 truncate table is the most efficient way of emptying a table.
 It's not possible to use the truncate table when you only want to delete some rows based on a
condition.
 Truncate table does not delete rows, it deallocates the data pages and release the space.
 Truncate table cannot be used when the table is referenced by a foreign key or involved in data
replication.
  Truncate table is recommended for temporary tables that need to be cleared for the next process.

2. List the data types available in mssql?

3. Write query to return all rows sql?

4. What is a virtual table in SQL?

5. Explain cross join or cartesian product in sql?

6. What is difference between foreign key and unique key?

7. What is difference between clustered index and non clustered index?

8. What is the difference between left and right outer join?

9. Write an sql query for deleting duplicate rows ?

10. What is a cursor, index in SQL ?

11. Explain Subquery and state its properties?
Subquery – It is a query which is used when expressions are allowed and can be nested inside the main
query like SELECT, UPDATE, DELETE or INSERT statements.
Properties:

 A subquery in the main query that has to be placed on the right-hand side of the comparison
operator
 A subquery has to be placed parenthesis, in order to get executed first before the main query.
 It cannot have any order by clause.
 More than one subquery can be included in the main query.

12. What are types of subqueries?

The types of Subqueries are:

 Single Row – It returns only one row.

 Multiple Row – It returns multiple rows.
 Multiple Column – It returns multiple columns to the main query.

13. Explain Trigger and its types?

Trigger – It is used to execute a batch of SQL code when the commands like INSERT, UPDATE or
DELETE are executed against a table.
They are automatically executed or triggered when the data gets modified.
Types of Triggers:

 Insert
 Update
 Delete
 Instead of

14. What do you mean by Collation recursive stored procedure?

Collation – It is defined to specify the sort order and there are three sort orders.

 Binary
 Case sensitive
 Case insensitive.
Recursive stored procedure – It is defined as a process of resolving a problem where the solution occurs
repetitively.This can be nested up to 32 levels.

15. Explain Magic tables in SQL Server?

When the triggers are fired for any DML command, insert and delete tables are created, these tables are
called Magic tables in SQL server.These tables are used inside the triggers for transactions.

16. State the difference between Local and Global temporary tables?
Local temporary table – These tables are invisible when there is a connection and are deleted when it is
closed.
Global temporary table – These tables are visible to all users and are deleted when the connection is
closed.

17. Explain CDC and SQL injection?

CDC – CDC stands for Change Data Capture, which captures the data that has been modified recently.
SQL injection – SQL injection is an attack by malicious users in which the malicious code is inserted
into the strings that are passed to an instance of SQL server for parsing and execution.
All the statements have to be checked for the vulnerabilities as they execute all syntactically valid queries
received.The parameters can also be changed by the experienced and skilled attackers.

18. What methods do you follow to protect from SQL injection attack?
Following methods are used to protect from SQL injection attack:

 Filtering input parameters

 Use parameter collection with Dynamic SQL
 Use Parameters for Stored procedures
 In like clause, use escape characters

19. What is filter index?

When the index is created with WHERE clause, it is called Filter index.It is used to filter some of the
rows in a table in order to improve the performance, index maintenance and reduce the index storage cost.
20. Distinguish between COMMIT and ROLLBACK?
COMMIT – Every statement between BEGIN and COMMIT becomes persistent to the database when
the COMMIT is executed.
ROLLBACK – Every statement between BEGIN and ROLLBACK are revealed to the state when the
ROLLBACK is executed.

21. Explain Logical operators in SQL Server?

The logical operators are basically used to test the truths of conditions.

 ALL – It returns true if all the set of operations are true.

 AND – It returns true if the Boolean expressions are true.
 IN – It returns true if the operand is equal to one of the lists of expressions.
 ANY – It returns true if any of the set of comparisons is true.
 BETWEEN – It returns true if the operand is within a range.
 EXISTS – It returns true if the subquery contains any rows.
 LIKE – It returns true if the operand matches a pattern
 NOT – It reverses the values of any Boolean operator.
 OR – It returns true if either of the Boolean expression is true.
 SOME – It returns true if some of the set of comparisons are true.

22. Explain the commands in SQL Server?

DML (Data Manipulation Language)

 Select
 Insert
 Update
 Delete

DDL (Data Definition Language)

 Create
 Alter
 Drop
 Truncate

DCL (Data Control Language)

 Grant
 Revoke

TCL (Transactional Control Language)

  Commit
 RollBack
 Save Transaction

23. State the difference between UNION and UNION ALL?

UNION – It is used to select the related information.
It is similar to that of JOIN command.
UNION ALL – It is similar to that of UNION command, but it selects all the values.
It does not remove the values from the table but will retrieve the data.

24. Explain TABLESAMPLE?

TABLESAMPLE is used to extract the sample of rows that are necessary for the application.

25. What are the purposes of FLOOR and SIGN functions?

FLOOR – It is used to round up the non-integer value to the preceding least integer.
SIGN – It is used to determine whether the number provided is positive, Zero, negative and returns +1, 0,
-1.

26. How are the exceptions handled in SQL Server Programming?

The exceptions are handled using TRY-CATCH blocks, wherein TRY block the scripts are written and in
CATCH block the errors are handled.

27. What is MSSQL?

MSSQL stands for Microsoft Server SQL that is a Microsoft’s relational database management system. It
is a featured database which is designed to compete against Oracle Database and MySQL. MSSQL is also
referred as SQL Server.

28. What is SQL server agent and what are the two modes of authentication in SQL
Server?
SQL Server agent
The SQL Server agent plays an important part in the day to day tasks of the SQL Server Database
Administrator (DBA).
Its purpose is to implement the tasks easily with the Scheduler engine that allows our tasks to run at a
scheduled time and date.
Modes of Authentication in SQL Server
The two authentication modes in SQL Server are:

 Windows Mode
 Mixed Mode

From the tools menu of SQL Server configuration properties in the security page, the modes can be
changed.

29. What is SQL Profiler?

It is a tool that allows system’s administrator to monitor the events in SQL Server.It is mainly used to
capture and save the data of each event of a file or a table for analysis.

30. What is COALESCE and CHECK constraint in SQL server?

COALESCE – It is a function that is used to return the first non-null expression from more than one
column within the arguments.
CHECK constraint – It is used to enforce the integrity. It is applied to a column in a table to limit the
values that have to be placed in a column