SQL Server Interview Q & A

SQL Server deadlock definition and Overview

1) What are different types of statements supported by SQL?

There are 3 types of SQL statements:

1) DDL (Data Definition Language): It is used to define the database structure such as tables. It includes three statements such as Create, Alter, and Drop.

Some of the DDL Commands are listed below:

CREATE: It is used for creating the table.

CREATE TABLE table_name
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),

ALTER: The ALTER table is used for modifying the existing table object in the database.

ALTER TABLE table_name ADD column_name datatype
OR
ALTER TABLE table_name
DROP COLUMN column_name

2) DML (Data Manipulation Language): These statements are used to manipulate the data in records. Commonly used DML statements are Insert, Update, and Delete.
The Select statement is used as partial DML statement that is used to select all or relevant records in the table.

3) DCL (Data Control Language): These statements are used to set privileges such as Grant and Revoke database access permission to the specific user.

2) How do we use DISTINCT statement? What is its use?

The DISTINCT statement is used with the SELECT statement. If the records contain duplicate values then DISTINCT is used to select different values among duplicate records.

Syntax: SELECT DISTINCT column_name(s) FROM table_name;

3) What are different Clauses used in SQL?

WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfill the given condition.

Syntax: SELECT column_name(s) FROM table_name WHERE condition;

GROUP BY Clause: It is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly.

Syntax: SELECT column_name(s) FROM table_name GROUP BY column_name;

HAVING clause: This clause is used in association with the GROUP BY clause. It is applied to each group of result or the entire result as a single group and much similar as WHERE clause, the only difference is you cannot use it without GROUP BY clause.

Syntax: SELECT column_name(s) FROM table_name GROUP BY column_name HAVING condition;

ORDER BY clause: This clause is to define the order of the query output either in ascending (ASC) or in descending (DESC) order. Ascending (ASC) is the default one but descending (DESC) is set explicitly.

Syntax: SELECT column_name(s) FROM table_name WHERE condition ORDER BY column_name ASC|DESC;

USING clause: USING clause comes in use while working with SQL Joins. It is used to check equality based on columns when tables are joined. It can be used instead ON clause in Joins.

Syntax: SELECT column_name(s) FROM table_name JOIN table_name USING (column_name);

4) Why do we use SQL constraints? Which constraints we can use while creating database in SQL?

Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.
Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER TABLE statement.

There are 5 major constraints are used in SQL, such as

NOT NULL: That indicates that the column must have some value and cannot be left null
UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column
PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one columns to identify the particular record with a unique identity.
FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key
CHECK: It is used to ensure whether the value in columns fulfills the specified condition

5) What are different JOINS used in SQL?

SQL Joins

There are 4 major types of joins made to use while working on multiple tables in SQL database.

INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from BOTH tables when it has at least one column matched

Syntax:

SELECT column_name(s) 
 FROM table_name1 
 INNER JOIN table_name2 
 ON column_name1=column_name2;

Example:

In this example, we have a table Employee with the following data

The second Table is joining

Enter the following SQL statement

    SELECT Employee.Emp_id, Joining.Joining_Date
    FROM Employee
    INNER JOIN Joining
    ON Employee.Emp_id = Joining.Emp_id
    ORDER BY Employee.Emp_id;

There will be 4 records selected. These are the results that you should see
result of innerjoin

Employee and orders tables where there is a matching customer_id value in both the Employee and orders tables.

LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from a LEFT table and its matched
rows from a RIGHT table.

Syntax: SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON column_name1=column_name2;

Example:
In this example, we have a table Employee with the following data

Employee table

Second Table is joining

Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date
    FROM Employee
    LEFT OUTER JOIN Joining
    ON Employee.Emp_id = Joining.Emp_id
    ORDER BY Employee.Emp_id;

There will be 4 records selected. These are the results that you should see:

RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from the RIGHT table and its matched rows from a LEFT table.

Syntax: SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON column_name1=column_name2;

Example:
In this example, we have a table Employee with the following data

The second Table is joining

Enter the following SQL statement:

    SELECT Employee.Emp_id, Joining.Joining_Date
    FROM Employee
    LEFT OUTER JOIN Joining
    ON Employee.Emp_id = Joining.Emp_id
    ORDER BY Employee.Emp_id;

There will be 4 records selected. These are the results that you should see

FULL JOIN (FULL OUTER JOIN): This joins returns all when there is a match either in the RIGHT table or in the LEFT table.

Syntax: SELECT column_name(s)
FROM table_name1
FULL OUTER JOIN table_name2
ON column_name1=column_name2;

Example:
In this example, we have a table Employee with the following data:

Second Table is joining

Enter the following SQL statement:

    SELECT Employee.Emp_id, Joining.Joining_Date
   FROM Employee
   FULL OUTER JOIN Joining
   ON Employee.Emp_id = Joining.Emp_id
   ORDER BY Employee.Emp_id;

There will be 8 records selected. These are the results that you should see

6) What are transaction and its controls?

A transaction can be defined as the sequence task that is performed on databases in a logical manner to gain certain results. Operations performed like Creating, updating, deleting records in the database comes from transactions.

In simple word, we can say that a transaction means a group of SQL queries executed on database records.

There are 4 transaction controls such as:

COMMIT: It is used to save all changes made through the transaction
ROLLBACK: It is used to roll back the transaction such as all changes made by the transaction are reverted back and database remains as before
SET TRANSACTION: Set the name of transaction
SAVEPOINT: It is used to set the point from where the transaction is to be rolled back

7) What are properties of the transaction?

Properties of transaction are known as ACID properties, such as

Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone
Consistency: Ensures that all changes made through successful transaction are reflected properly on database
Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other
Durability: Ensures that the changes made in database with committed transactions persist as it is even after system failure

8) How many Aggregate Functions are available there in SQL?

SQL Aggregate Functions calculates values from multiple columns in a table and returns a single value.

There are 7 aggregate functions we use in SQL:

AVG(): Returns the average value from specified columns
COUNT(): Returns number of table rows
MAX(): Returns largest value among the records
MIN(): Returns smallest value among the records
SUM(): Returns the sum of specified column values
FIRST(): Returns the first value
LAST(): Returns Last value

9) What are Scalar Functions in SQL?

Scalar Functions are used to return a single value based on the input values. Scalar Functions are as follows:

UCASE(): Converts the specified field in upper case
LCASE(): Converts the specified field in lower case
MID(): Extracts and returns character from text field
FORMAT(): Specifies the display format
LEN(): Specifies the length of text field
ROUND(): Rounds up the decimal field value to a number

10) What are triggers?

Triggers in SQL is kind of stored procedures used to create a response to a specific action performed on the table such as Insert, Update or Delete. You can invoke triggers explicitly on the table in the database.

Action and Event are two main components of SQL triggers when certain actions are performed the event occurs in response to that action.

Syntax: CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]}
ON table_name [FOR [EACH] {ROW|STATEMENT}]
EXECUTE PROCEDURE functionname {arguments}

11) What is View in SQL?

A View can be defined as a virtual table that contains rows and columns with fields from one or more table.

Syntax: CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

12) How we can update the view?

SQL CREATE and REPLACE can be used for updating the view.

Following query syntax is to be executed to update the created view:

Syntax: CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

13) What is SQL Injection?

SQL Injection is a type of database attack technique where malicious SQL statements are inserted into an entry field of database such that once it is executed the database is opened for an attacker. This technique is usually used for attacking Data-Driven Applications to have an access to sensitive data and perform administrative tasks on databases.

For Example: SELECT column_name(s) FROM table_name WHERE condition;

14) What is SQL Sandbox in SQL Server?

SQL Sandbox is the safe place in SQL Server Environment where untrusted scripts are executed. There are 3 types of SQL sandbox, such as:

Safe Access Sandbox: Here a user can perform SQL operations such as creating stored procedures, triggers etc. but cannot have access to the memory and cannot create files.
External Access Sandbox: User can have access to files without having a right to manipulate the memory allocation.
Unsafe Access Sandbox: This contains untrusted codes where a user can have access to memory.

15) What is the difference between SQL and PL/SQL?

SQL is a structured query language to create and access databases whereas PL/SQL comes with procedural concepts of programming languages.

16) What is the use of NVL function?

NVL function is used to convert the null value to its actual value.

17) What is the Cartesian product of table?

The output of Cross Join is called as a Cartesian product. It returns rows combining each row from the first table with each row of the second table. For Example, if we join two tables having 15 and 20 columns the Cartesian product of two tables will be 15×20=300 Rows.

18) What do you mean by Subquery?

Query within another query is called as Subquery. A subquery is called inner query which returns output that is to be used by another query

19) What is the difference between clustered and non-clustered indexes?

One table can have only one clustered index but multiple nonclustered indexes.
Clustered indexes can be read rapidly rather than non-clustered indexes.
Clustered indexes store data physically in the table or view and non-clustered indexes do not store data in table as it has separate structure from data row.

20) What is the difference between DELETE and TRUNCATE?

The basic difference in both is DELETE is DML command and TRUNCATE is DDL
DELETE is used to delete a specific row from the table whereas TRUNCATE is used to remove all rows from the table
We can use DELETE with WHERE clause but cannot use TRUNCATE with it

21) What is the difference between DROP and TRUNCATE?

TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back.

22) What is the difference between Nested Subquery and Correlated Subquery?

Subquery within another subquery is called as Nested Subquery. If the output of a subquery is depending on column values of the parent query table then the query is called Correlated Subquery.

SELECT adminid(SELEC Firstname+' ‘+Lastname FROM Employee WHERE empid=emp. adminid)AS EmpAdminId FROM Employee

This query gets details of an employee from the Employee table.

23) What is Normalization? How many Normalization forms are there?

Normalization is used to organize the data in such manner that data redundancy will never occur in the database and avoid insert, update and delete anomalies.

There are 5 forms of Normalization

First Normal Form (1NF): It removes all duplicate columns from the table. Creates a table for related data and identifies unique column values
First Normal Form (2NF): Follows 1NF and creates and places data subsets in an individual table and defines the relationship between tables using the primary key
Third Normal Form (3NF): Follows 2NF and removes those columns which are not related through primary key
Fourth Normal Form (4NF): Follows 3NF and do not define multi-valued dependencies. 4NF also known as BCNF

24) What is Relationship? How many types of Relationship are there?

The relationship can be defined as the connection between more than one tables in the database.

There are 4 types of relationships

One to One Relationship
Many to One Relationship
Many to Many Relationship
One to Many Relationship

25) What do you mean by Stored Procedures? How do we use it?

A stored procedure is a collection of SQL statements which can be used as a function to access the database. We can create these stored procedures previously before using it and can execute these them wherever we require and also apply some conditional logic to it. Stored procedures are also used to reduce network traffic and improve performance.

Syntax:

CREATE Procedure Procedure_Name
(
//Parameters
)
AS
BEGIN
SQL statements in stored procedures to update/retrieve records
END

26) State some properties of Relational databases?

In relational databases, each column should have a unique name
The sequence of rows and columns in relational databases are insignificant
All values are atomic and each row is unique

27) What is Collation?

Collation is a set of rules that check how the data is sorted by comparing it. Such as Character data is stored using correct character sequence along with case sensitivity, type, and accent.

28) What are the possible values for the BOOLEAN data field?

For a BOOLEAN data field, two values are possible: -1(true) and 0(false).

29) Explain DML and DDL?

DML stands for Data Manipulation Language. INSERT, UPDATE and DELETE are DML statements.

DDL stands for Data Definition Language. CREATE, ALTER, DROP, RENAME are DDL statements.

30) What is a transaction?

A transaction is a sequence of code that runs against a database. It takes the database from one consistent state to another.

31) What is a composite primary key?

Primary key created on more than one column is called composite primary key.

32) What is schema?

A schema is a collection of database objects of a User.

33) What is the difference between Having clause and Where clause?

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.

34). If we drop a table, does it also drop related objects like constraints, indexes, columns, defaults, Views and Stored Procedures ?

YES, SQL Server drops all related objects, which exists inside a table like, constraints, indexes, columns, defaults etc. BUT dropping a table will not drop Views and Stored Procedures as they exists outside the table.

35) What is the Difference between COALESCE() & ISNULL() ?

ISNULL accepts only 2 parameters. The first parameter is checked for NULL value, if it is NULL then the second parameter is returned, otherwise it returns first parameter.

COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter,

36) How do you prevent SQL Server from giving you informational messages during and after a SQL statement execution?

SET NOCOUNT OFF

37) What is faster, a correlated sub query or an inner join?

Correlated sub query.

38) You are supposed to work on SQL optimization and given a choice which one runs faster, a correlated sub query or an exists?

Exists

39) What are user defined data types and when you should go for them?

User-defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined data type calledFlight_num_type of varchar(8) and use it across all your tables.

See sp_addtype, sp_droptype in books online.

40) What is maximum number of tables that can joins in a single query ?

256, check SQL Server Limits

41) What is Magic Tables in SQL Server ?

The MAGIC tables are automatically created and dropped, in case you use TRIGGERS. SQL Server has two magic tables named, INSERTED and DELETED.

These are mantained by SQL server for there Internal processing. When we use update insert or delete on tables these magic tables are used.These are not physical tables but are Internal tables.When ever we use insert statement is fired the Inserted table is populated with newly inserted Row and when ever delete statement is fired the Deleted table is populated with the deleted row.

But in case of update statement is fired both Inserted and Deleted table used for records the Original row before updation get store in Deleted table and new row Updated get store in Inserted table.

42) Why you need indexing? where that is Stored and what you mean by schema object? For what purpose we are using view?

We can’t create an Index on Index.. Index is stoed in user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table. So that is a view.

Indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It’s customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.

43) What are the main differences between #temp tables and @table variables and which one is preferred ?

SQL Server can create column statistics on #temp tables
Indexes can be created on #temp tables
@table variables are stored in memory up to a certain threshold.

44) 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.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Q1) In what sequence SQL statement are processed?

Ans. The clauses of the select are processed in the following sequence

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
TOP clause

Q2) Can we write a distributed query and get some data which is located on other server and on Oracle Database ?

Ans. SQL Server can be lined to any server provided it has an OLE-DB provider from Microsoft to allow a link.

E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.

Q3) If we drop a table, does it also drop related objects like constraints, indexes, columns, defaults, Views and Stored Procedures?

Ans. YES, SQL Server drops all related objects, which exists inside a table like, constraints, indexes, columns, defaults etc. BUT dropping a table will not drop Views and Stored Procedures as they exists outside the table.

How would you determine the time zone under which a database was operating?

Q4) Can we add an identity column to decimal datatype?

Ans. YES, SQL Server support this

Q5) What is the difference between LEFT JOIN with WHERE clause & LEFT JOIN with nowhere clause?

Ans. OUTER LEFT/RIGHT JOIN with WHERE clause can act like an INNER JOIN if not used wisely or logically.

Q6) What are the multiple ways to execute a dynamic query?

Ans. EXEC sp_executesql, EXECUTE()

Q7) What is the Difference between COALESCE() & ISNULL()?

Ans. ISNULL accepts only 2 parameters. The first parameter is checked for a NULL value, if it is NULL then the second parameter is returned, otherwise, it returns the first parameter.

COALESCE accepts two or more parameters. One can apply 2 or as many parameters, but it returns only the first non NULL parameter,

Q8) How do you generate file output from SQL?

Ans. While using SQL Server Management Studio or Query Analyzer, we have an option in Menu BAR.QUERTY >> RESULT TO >> Result to FILE

Q9) How do you prevent SQL Server from giving you informational messages during and after a SQL statement execution?

Ans. SET NOCOUNT OFF

Q10) By Mistake, Duplicate records exists in a table, how can we delete copy of a record ?

Ans.

with T as
(
    select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
    from employee
)
 
delete
from T
where rank > 1

Q11) WHAT OPERATOR PERFORMS PATTERN MATCHING?

Ans. Pattern matching operator is LIKE and it has to used with two attributes

1. % means matches zero or more characters and

2. _ ( underscore ) means matching exactly one character

Q12) What’s the logical difference, if any, between the following SQL expressions?

-- Statement 1 
SELECT COUNT ( * ) FROM Employees
 
-- Statement 2
SELECT SUM ( 1 ) FROM Employees

They’re the same unless table Employee table is empty, in which case the first yields a one-column, one-row table containing a zero and the second yields a one-column, one-row table "containing a null."

Q13) Is it possible to update the Views? If yes, How, If Not, Why?

Ans. Yes, We can modify views but a DML statement on a join view can modify only one base table of the view (so even if the view is created upon a join of many tables, only one table, the key preserved table can be modified through the view).

Q14) Could you please name different kinds of Joins available in SQL Server?

OUTER JOIN – LEFT, RIGHT, CROSS, FULL ;
INNER JOIN

Q15) How important do you consider cursors or while loops for a transactional database?

Ans. would like to avoid cursor in OLTP database as much as possible, Cursors are mainly only used for maintenance or warehouse operations.

Q16) What is a correlated subquery?

Ans. When a subquery is tied to the outer query. Mostly used in self joins.

Q17) What is faster, a correlated subquery or an inner join?

Ans. Correlated subquery.

Q18) You are supposed to work on SQL optimization and given a choice which one runs faster, a correlated sub query or an exists?

Ans. Exists

Q19) Can we call .DLL from SQL server?

Ans. YES, We can call .Dll from SQL Server.

Q20) What are the pros and cons of putting a scalar function in a queries select list or in the where clause?

Ans. Should be avoided if possible as Scalar functions in these places make the query slow down dramatically.

Q21) What are user-defined data types and when you should go for them?

Ans. User-defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined data type called Flight_num_type of varchar(8) and use it across all your tables.

See sp_addtype, sp_droptype in books online.

Q22) Can You Explain Integration Between SQL Server 2005 And Visual Studio 2005 ?

Ans. This integration provides a wider range of development with the help of CLR for database server because CLR helps developers to get flexibility for developing database applications and also provides language interoperability just like Visual C++, Visual Basic .Net and Visual C# .Net.

The CLR helps developers to get the arrays, classes and exception handling available through programming languages such as Visual C++ or Visual C# which is use in stored procedures, functions and triggers for creating database application dynamically and also provide more efficient reuse of code and faster execution of complex tasks. We particularly liked the error-checking powers of the CLR environment, which reduces run-time errors

Q23) What is Index, cluster index and non cluster index ?

Ans.

Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.

Non-NonClustered Index:- A Non-Clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows in the disk. The leaf nodes of a non-clustered index does not consists of the data pages. instead the leaf node contains index rows.

Q24) Write down the general syntax for a SELECT statements covering all the options.

Ans. Here’s the basic syntax: (Also checkout SELECT in books online for advanced syntax)

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ]

Q25). 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.

For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".

Q26) What is OSQL utility ?

Ans. OSQL is a command-line tool which is used execute the query and display the result same a query analyzer but everything is in command prompt.

Q27) What Is the Difference Between OSQL And Query Analyzer?

Ans. OSQL is the command-line tool that executes the query and displays the result same a query analyzer but the query analyzer is graphical and OSQL is a command-line tool. OSQL is quite useful for batch processing or executing remote queries.

Q28) What Is Cascade delete/update?

Ans. CASCADE allows deletions or updates of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed.

SQL Server Interview Questions For 2-5 Years Experienced

Q29) What are some of the join algorithms used when SQL Server joins tables.

Loop Join (indexed keys unordered)
Merge Join (indexed keys ordered)
Hash Join (non-indexed keys)

Q30) What is the maximum number of tables that can joins in a single query?

Ans. 256, check SQL Server Limits

Q31) What is Magic Tables in SQL Server?

Ans. The MAGIC tables are automatically created and dropped, in case you use TRIGGERS. SQL Server has two magic tables named, INSERTED and DELETED

These are maintained by the SQL server for there Internal processing. When we use update insert or delete on tables these magic tables are used. These are not physical tables but are Internal tables.Whenever we use insert statement is fired the Inserted table is populated with newly inserted Row and whenever delete statement is fired the Deleted table is populated with the delete d row.

But in case of update statement is fired both Inserted and Deleted table used for records the Original row before updating get store in the Deleted table and new row Updated get store in Inserted table.

Q32) Can we disable a triger?, if yes HOW?

Ans. YES, we can disable a single trigger on the database by using “DISABLE TRIGGER triggerName ON <>”

we also have an option to disable all the trigger by using, “DISABLE Trigger ALL ON ALL SERVER”

Q33) Why you need indexing? where that is Stored and what you mean by schema object? For what purpose we are using view?

Ans. We can’t create an Index on Index.. Index is stoed in user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table. So that is a view.

Q34) What the difference between UNION and UNIONALL?

Ans. Union will remove the duplicate rows from the result set while Union all does’nt.

Q35) Which system table contains information on constraints on all the tables created ?

Ans. USER_CONSTRAINTS,

system table contains information on constraints on all the tables created

SQL Server Joins Interview Questions

Q35) What are different Types of Join?

Ans.

Cross Join: A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
Inner Join: A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.
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:
1. Left Outer Join: In Left Outer Join all rows in the first-named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
2. Right Outer Join: In Right Outer Join all rows in the second-named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
3. Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.
Self Join: This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

Related Article: [SQL Server Joins]

Q36) What is Data-Warehousing?

Ans.

Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.
Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent.

Q37) What is a live lock?

Ans. A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

Q38) How SQL Server executes a statement with nested subqueries?

Ans. When SQL Server executes a statement with nested subqueries, it always executes the innermost query first. This query passes its results to the next query and so on until it reaches the outermost query. It is the outermost query that returns a result set.

Q39) How do you add a column to an existing table?

Ans. ALTER TABLE Department ADD (AGE, NUMBER);

Q40) Can one drop a column from a table?

Ans. YES, to delete a column in a table, use ALTER TABLE table_name DROP COLUMN column_name

Q41) Which statement do you use to eliminate padded spaces between the month and day values in a function TO_CHAR(SYSDATE,’Month, DD, YYYY’) ?

Ans. To remove padded spaces, you use the "fm" prefix before the date element that contains the spaces. TO_CHAR(SYSDATE,’fmMonth DD, YYYY’)

Q42) Which operator do you use to return all of the rows from one query except rows are returned in a second query?

Ans. You use the EXCEPT operator to return all rows from one query except where duplicate rows are found in a second query. The UNION operator returns all rows from both queries minus duplicates. The UNION ALL operator returns all rows from both queries including duplicates. The INTERSECT operator returns only those rows that exist in both queries.

Q43) How will you create a column alias?

Ans. The AS keyword is optional when specifying a column alias.

Q44) In what sequence SQL statement are processed?

Ans. The clauses of the subselect are processed in the following sequence (DB2):

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
FETCH FIRST clause

Q45) How can we determine what objects a user-defined function depends upon?

Ans. sp_depends system stored procedure or query the sysdepends system table to return a list of objects that a user-defined function depends upon

SELECT DISTINCT so1.name, so2.name FROM sysobjects so1
INNER JOIN sysdepends sd
ON so1.id = sd.id
INNER JOIN sysobjects so2
ON so2.id = sd.depid
WHERE so1.name = '<>'

Q46). What is lock escalation ?

Ans. A query first takes the lowest level lock possible with the smallest footprint (row-level). When too many rows are locked (requiring too much RAM) the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock.

Q47) What are the main differences between #temp tables and @table variables and which one is preferred?

Ans.

SQL Server can create column statistics on #temp tables
Indexes can be created on #temp tables
@table variables are stored in memory up to a certain threshold.

Q48) What are Checkpoint In SQL Server ?

Ans. When we done operation on SQL SERVER that is not commited directly to the database.All operation must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint are the point which alert Sql Server to save all the data to main database if no check point is there then log files get full we can use Checkpoint command to commit all data in the SQL SERVER.When we stop the SQL Server it will take long time because Checkpoint is also fired.

Read these latest SQL Interview Questions For 5+ Years Experienced that helps you grab high-paying jobs!

Q49) Why we use OPEN XML clause?

Ans. OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the DB.

Q50) Can we store we store PDF files inside SQL Server table ?

Ans. YES, we can store this sort of data using a blob datatype.

Q51). Can we store Videos inside SQL Server table ?

Ans. YES, we can store Videos inside SQL Server by using FILESTREAM datatype, which was introduced in SQL Server 2008.

Q52. Can we hide the definition of a stored procedure from a user ?

Ans. YES, while creating stored procedure we can use WITH ENCRYPTION which will convert the original text of the CREATE PROCEDURE statement to an encrypted format.

Q53). What are included columns when we talk about SQL Server indexing?

Ans. Indexed with included columns were developed in SQL Server 2005 that assists in covering queries. Indexes with Included Columns are non clustered indexes that

have the following benefits:

Columns defined in the include statement, called non-key columns, are not counted in the
number of columns by the Database Engine.
Columns that previously could not be used in queries, like nvarchar(max), can be included
as a non-key column.
A maximum of 1023 additional columns can be used as non-key columns.

Q54). What is an execution plan? How would you view the execution plan?

Ans. An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query.

From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.

Q55). Explain UNION, MINUS, UNION ALL, INTERSECT ?

Ans. INTERSECT returns all distinct rows selected by both queries.

MINUS: returns all distinct rows selected by the first query but not by the second.

UNION: returns all distinct rows selected by either query

UNION ALL: returns all rows selected by either query, including all duplicates

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

SQL SERVER Query Interview Questions with Answers

SQL Server DATEADD() Function

Q56) Write a Query to display the date after 15 days?

Ans.

1	`SELECT DATEADD(dd, 15,getdate())`

Q57) Write a Query to display date after 12 months?

Ans.

1	`SELECT DATEADD(mm, 2, getdate())`

Q58) Write a Query to display date before 15 days?

Ans.

1	`SELECT DATEADD(dd, -15, getdate())`

SQL Server DATEDIFF() Function

Q59) Write a Query to display employee details along with exp?

Ans.

SELECT *
DATEDIFF(yy, doj, getdate()) AS ‘Exp’ FROM employee

Q60) Write a Query to display employee details who is working in ECE department & who his having more than 3 years of exp?

Ans.

SELECT *
DATEDIFF(yy, doj, getdate()) AS ‘Exp’ 
FROM employee 
WHERE DATEDIFF(yy, doj, getdate())>3 AND dept_name=’ECE’

Q61) Write a Query to display employee details along with age?

Ans.

SELECT *
DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee

Q62) Write a Query to display employee details whose age >18?

Ans.

SELECT *
DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee
WHERE DATEDIFF(yy, dob, getdate())>18

SQL Server Multi Row Functions

Q63) Write a Query to display minimum salary of an employee?

Ans.

SELECT MIN (salary)
FROM employee

Q64) Write a Query to display maximum salary of an employee?

Ans.

SELECT MAX(salary)
FROM employee

Q65) Write a Query to display total salary of all employees?

Ans.

1	`SELECT SUM(salary) FROM employee`

Q66) Write a Query to display average salary of an employee?

Ans.

1	`SELECT AVG(salary) FROM employee`

Q67) Write a Query to count the number of employees working in the company?

Ans.

1	`SELECT COUNT(*) FROM employee`

Q68) Write a Query to display minimum & maximum salary of employee?

Ans.

SELECT MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’ FROM employee

Q69) Write a Query to count the number of employee working in ECE department?

Ans.

1	`SELECT COUNT(*) FROM employee WHERE dept_name=’ECE’`

Q70) Write a Query to display second max salary of an employee?

Ans.

SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary) FROM emp)

Q71) Write a Query to display third max salary of an employe?

Ans.

SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary) FROM emp where salary < (SELECT MAX(salary) FROM emp))

SQL SERVER: GROUP BY Clause

Q72) Write a Query to display total salary of employee based on city?

Ans.

SELECT city, SUM(salary) 
FROM employee 
GROUP BY city;

Q73) Write a Query to display number of employee based on city?

Ans.

SELECT city, COUNT(emp_no) 
FROM employee
GROUP BY city;

(OR)

SELECT city, COUNT(emp_no) AS ‘no.of employees’ 
FROM employee 
GROUP BY city;

Q74) Write a Query to display total salary of employee based on region?

Ans.

SELECT region, SUM(salary) AS ‘total_salary’ 
FROM employee
GROUP BY region;

Q75) Write a Query to display the number of employees working in each region?

Ans.

SELECT region, COUNT(gender) 
FROM employee
GROUP BY region;

(OR)

SELECT region, COUNT(gender) AS ‘no.of males’
FROM employee
GROUP BY region;

Q76) Write a Query to display minimum salary & maximum salary based on dept_name?

Ans.

SELECT dept_name, MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’ 
FROM employee
GROUP BY dept_name

Q77) Write a Query to display the total salary of employee based on dept_name?

Ans.

SELECT dept_name, SUM(salary) AS ‘total_sal’ 
FROM employee
GROUP BY dept_name

Q78) Write a Query to display no.of males in each department?

Ans.

SELECT dept_name, COUNT(gender) 
FROM employee
GROUP BY dept_name 
WHERE gender=’male’

(OR)

SELECT dept_name, COUNT(gender) AS ‘no.of males’ 
FROM employee
WHERE gender=’male’ 
GROUP BY dept_name;

Note: We cannot apply where condition in GROUP BY CLAUSE if we want apply use having clause.

We have to use WHERE condition before GROUP BY but cannot apply where condition after GROUP BY.

SQL SERVER: Having Clause

Q79) Write a Query to display total salary of employee based on whose total salary > 12000?

Ans.

SELECT city, SUM(salary) AS ‘total_salary’ 
FROM employee
GROUP BY city
HAVING SUM(salary)>12000;

Q80) Write a Query to display the total salary of all employees based on city whose average salary >= 23000?

Ans.

SELECT city, SUM(salary) AS ‘total_salary’ 
FROM employee
GROUP BY city
HAVING AVG(salary)  >= 23000;

SQL SERVER: SUB QUERIES

Q81) Write a Query to display employee details whose employee numbers are 101, 102?

Ans.

SELECT *
FROM employee
WHERE Emp_No in (101, 102)

(OR)

SELECT * FROM employee
WHERE Emp_No in (select emp_no from emp)

Q82) Write a Query to display employee details belongs to ECE department?

Ans.

SELECT Emp_No, Emp_Name, Salary
FROM employee
WHERE dept_no in (select dept_no from dept where dept_name = ‘ECE’)

SQL SERVER TOP Clause

Q83) Write a Query to display first record from the table?

Ans.

SELECT TOP 1 * 
FROM employee

Q84) Write a Query to display top 3 records from the table?

Ans.

SELECT TOP 3 *
FROM employee

Q85) Write a Query to display last record from the table?

Ans.

SELECT TOP 1 *
FROM employee 
ORDER BY emp_no descending

SQL SERVER: Ranking Functions

Student Details Table:

Student_No	Student_Name	Percentage	Row_ID	Rank_ID	DenseRank_ID
105	James	87	1	1	1
106	John	83	2	2	2
101	Anil	83	3	2	2
104	Vijay	83	4	2	2
108	Rakesh	76	5	5	3
102	Sunil	76	6	5	3
103	Ajay	76	7	5	3
107	Ram	75	8	8	4

Q86) Write a Query to display student details along with the row_no order by student name?

Ans.

SELECT *, ROW_NUMBER() OVER (ORDER BYstudent_name) AS ‘Row_ID’
FROM employee

Q87) Write a Query to display even records from the table?

Ans.

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS ‘ Row_ID’ FROM student)
WHERE row_id %2=0

Q88) Write a Query to display odd records from student table?

Ans.

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS Row_ID FROM student)
WHERE row_id %2!=0

List of Related Microsoft Certification Courses:

SSIS	Power BI
SSRS	SharePoint
SSAS	SQL Server DBA
SCCM	BizTalk Server
Team Foundation Server	BizTalk Server Administrator