Top 150 SQL Server Questions and Answers for Job Interview :
1. What is the SQL Server?
Answer : The SQL Server is an enormous, easy, and a powerful Relational Database Management application provided by Microsoft. It offers Database Management, Business Intelligence and Database Development capabilities. It is very easy to manage and can be mastered with practice.
2. What are the advantages of the SQL Server?
Answer : The SQL server has the following advantage:
- Datawarehouse Support
- Enhanced Security Features
- Cheaper as compared to other RDBMS applications
- Easy To Use
- Support for Small, Medium and Large Database Storage
- Enterprise Feature Support
3. What are the requirements specific to the SQL server?
Answer : The SQL Server provided by Microsoft had been planned to be used on the Windows Systems. Previous versions of the SQL Server are compatible on Linux. The SQL Server comes with different Editions in each Version. The SQL Server is available in 32 bit and 64 bit compilers. The minimum requirements are as follows:
- Internet Explorer 5.0 or higher
- .NET Framework 3.5
The OS System requirements depend on the Version and Edition being used. The Enterprise and Datacenter Editions require Windows Server 2003 Sp2 or Windows Server 2008 for complete features support.
4. Mention the list of Editions in SQL Server 2008 R2.
Answer : The following are the list of the Editions in the SQL Server 2008 R2:
- SQL Server 2008 R2 Datacenter (64-bit) IA64
- SQL Server 2008 R2 Enterprise (64-bit) IA64
- SQL Server 2008 R2 Datacenter (64-bit) x64
- SQL Server 2008 R2 Enterprise (64-bit) x64
- SQL Server 2008 R2 Standard (64-bit) x64
- SQL Server 2008 R2 Datacenter (32-bit)
- SQL Server 2008 R2 Enterprise (32-bit)
- SQL Server 2008 R2 Standard (32-bit)
5. Mention the list of specialized editions in SQL Server 2008 R2.
Answer : We have provided a list of the Specialized Editions in the SQL Server 2008 R2:
- SQL Server 2008 R2 Developer (64-bit) IA64
- SQL Server 2008 R2 Developer (64-bit) x64
- SQL Server 2008 R2 Workgroup (64-bit) x64
- SQL Server 2008 R2 Web (64-bit) x64
- SQL Server 2008 R2 Express (64-bit) x64
- SQL Server 2008 R2 Express with Tools (64-bit) x64
- SQL Server 2008 R2 Express Advanced (64-bit) x64
- SQL Server 2008 R2 Developer (32-bit)
- SQL Server 2008 R2 Workgroup (32-bit)
- SQL Server 2008 R2 Web (32-bit)
6. Why is the SQL profiler required by the SQL Server?
Answer : The SQL Profiler successfully captures the SQL Server events from a server. The events are saved in a trace file that can be used to analyze and diagnose any problem.
7. Why is the SQL Profiler used in a SQL server?
Answer : The different uses of the SQL Profiler in the SQL Server are:
- It can be used to tune workload of the SQL server.
- It stores security-related actions that can be reviewed by a security administrator.
- It is used to find the reason behind the problem by stepping through the various problem queries.
- It is useful in analyzing the cause of the slow running of executed queries.
- The SQL Profiler helps in auditing the actions performed on the various instances of the SQL Server.
8. Differentiate between TRUNCATE and DELETE.
Answer : With the DELETE statement, one can provide a conditional WHERE clause to remove or delete specific rows. This function cannot be performed by the TRUNCATE statement. The TRUNCATE statement is faster than the DELETE statement as the latter keeps a log of each row it deletes in the transaction logs. However, the former keeps logs of only deallocated pages in transaction logs. The DELETE statement is a DML command like INSERT, UPDATE while the TRUNCATE statement is a DDL command like the CREATE, ALTER, DROP, etc.
9. Which statement can be Rolled back: TRUNCATE and DELETE?
Answer : Both the TRUNCATE and DELETE statements can be rolled backed if they are provided in a transaction. If the BEGIN TRANS is not provided, then none of the statements can be rolled back.
10. What do you know about extended stored procedures?
Answer : The extended stored procedures allow the users to create their own external routines in any commonly used programming language. The extended stored procedures are similar to regular stored procedures and are executed in a similar way. Parameters can be passed to extended stored procedures and the extended stored procedures can return results and return status. Extended stored procedures are DLLs that are dynamically loaded and executed by an instance of the SQL Server. These procedures run directly in the address space of an instance of the SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.
11. How does one execute any DOS command from SQL or using a SQL query by using the xp_cmdshell?
Answer : Execute the exec xp_cmdshell ‘dir c:\*.exe’ to execute any DOS command from SQL or the SQL query.
12. How does one insert a result set of the above proc in a SQL table?
Answer : To insert a result into the above, use the exec xp_cmdshell ‘dir c:\*.exe’.
13. What are cursors in the SQL server and what are their types?
Answer : The different types of cursors available in the SQL server are FORWARD-ONLY, FAST-FORWARD or READ-ONLY cursors. The types of cursors in the SQL server are Dynamic, Static and Keyset. The FORWARD-ONLY is the fastest server and the READ-ONLY is the slowest.
14. Should the users use a cursor?
Answer : The users should avoid using cursors in the SQL servers due to overhead and inconvenience.
15. What are the alternatives of cursors in SQL server?
Answer : The alternatives of the cursors in the SQL server are CASE stmt, derived tables, while loops with temp tables, and correlated sub-queries.
16. What are the different ways to execute a dynamic query?
Answer : The different codes that are used to execute a dynamic array are EXEC sp_executesql, EXECUTE(.
17. How can a user migrate an SSIS package from Development to Production environment?
Answer : In order to migrate an SSIS package from Development to Production environment, the db connections and file paths must not be included in the workflow. Instead the user has to create a number of configuration files. This will help in deploying the pkg created in the DEV server to Testing and finally to the PROD environment.
18. Differentiate between the COALESCE() and ISNULL().
Answer : The ISNULL() is used for data type determination of the resulting expression. It uses the first parameter type while COALESCE() follows the CASE expression rules and returns type of value with highest precedence.
The NULLability of result expression is different for the ISNULL() and COALESCE() methods. The ISNULL() method returns values which are always considered NOT NULLable (assuming the return value is a non-nullable one) whereas the COALESCE() method is not. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) are equivalent but both of them have different NULLability values. This makes a difference if the user is using these expressions in computed columns and creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed.
19. Mention the ideal combination with IN and UNION(ALL) based on performance.
Answer : The following pieces of code can be implemented as the ideal combination with IN and UNION (ALL):
- SELECT *FROM
WHERE
IN (SELECT… UNION SELECT…) - SELECT * FROM
WHERE
IN (SELECT… UNION ALL SELECT…)
20. What do you know about the IDENTITY column?
Answer : The IDENTITY column can be used by a user with any column from the table to make it auto incremental or to convert it into a surrogate key.
21. Can a user create a Primary Key without a clustered index?
Answer : Whenever a Primark Key is created, a clustered index is automatically created upon the column(s). Hence, it is impossible to create a Primary Key without a clustered index.
22. What do you know about the ROLLUP and CUBE sets?
Answer : The CUBE as well as the ROLLUP sets are the grouping sets used with the GROUP BY clause and they are very helpful in creating reports.
23. What are the different types of XML indexes?
Answer : The SQL Server 2005 is built to support four different types of XML indexes. An XML index is different than a relational index and hence, it is necessary to know their implementation before the user approaches how to use them for maximum effectiveness.
There is a single “primary XML index” and has three different flavors of “secondary XML index”. The primary XML index isn’t strictly an index on the original form of the XML. The primary XML index on an XML column is a clustered index on an internal table known as the node table that users cannot use directly from their T-SQL statements. The primary XML index is a B+tree and its usefulness is caused by the way that the optimizer creates a plan for the entire query. Although the optimizer can operate on the entire XML column collectively when the user needs to execute XML queries, it is more often useful to decompose the XML into relational columns and rows. The primary XML index essentially contains one row for each node in the XML instance.
24. What are the new indexes that have been introduced in the 2005 version of the SQl Server?
Answer : The Spatial and the XML indexes have been introduced in the 2005 version of the SQL Server as compared to the SQL Server 2000.
25. How are exceptions handled in the SQL Server programming?
Answer : Exceptions are handled in the SQL Server programming by using the TRY-CATCH constructs. The error is handled by putting the SQL statements/scripts inside the TRY block and the error handling code is moved in the CATCH block.
26. What do you mean by virtual tables in Triggers?
Answer : The INSERT and DELETE are the only virtual tables present in Triggers.
27. Mention the various Isolation levels.
Answer : The various Isolation Levels that have been provided in the SQL Server are mentioned below:
- Serializable Isolation Level
- Read Uncommitted Isolation Level
- Snapshot Isolation Level
- Read Committed Isolation Level
- Read Committed Snapshot Isolation Level
- Repeatable Read Isolation Level
28. Mention the scope of a temporary table.
Answer : The Scope of a Temporary Table is limited to its session.
29. Is there a special name for a table with a ‘##’ placed before the name?
Answer : Yes, a table with a ‘##’, also called a double pound sign placed before the table name has a special name and is called the ‘Global Temp Table’.
30. What is the scope of a Global Temp Table?
Answer : The Global Temp Table has a scope outside its session. However, it remains accessible outside the session only until the original session exists.
31. How can a user copy a schema from one SQL Server to another?
Answer : In order to copy a schema from one SQL server to another with the DTS or the import/export wizard or by scripting out Database objects.
32. How can a user load large data in the SQL Server?
Answer : In order to load large data into the SQL Server, the user can use BulkCopy. It is a tool used to copy large amounts of data from various tables. The BULK INSERT command helps the user to import a data file into a database table or a view in a user-specified format.
33. Up to how many levels can stored-procedures be recursive?
Answer : The stored-procedures can be recursive up to 32 levels in a SQL Server program.
34. What is the maximum number of non-clustered indexes that can be present in a table?
Answer : A user can create a maximum of 249 non-clustered indexes in a SQL table.
35. What are the possible indexes that can be present in a table?
Answer : The different types of indexes that can be used in a SQL table are the One Clustered Index, one or more non-clustered index, unique index, filtered, spatial, xml, etc.
36. What is the difference between VARCHAR and VARCHAR2?
Answer : Both the VARCHAR and the VARCHAR2 methods have the same use. The VARCHAR2 is specific to the Oracle version. The MS SQL Server has two types of VARCHAR methods: the VARCHAR and the VARCHAR(MAX) data types.
37. What do you mean by de-normalization?
Answer : In SQL, de-normalization is the process of attempting the optimization of the performance of a database by adding redundant data. The process is extremely necessary in some cases because of the current implementation of the relational model in the DBMS, poorly. A true relational DBMS allows for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. The de-normalization is a technique that is used to move from the higher to the lower and normal forms of database modeling in order to speed up the database access.
38. How can a user get both error and a row at the same time?
Answer : If the @@Rowcount is checked after the error checking statement, then it will have 0 as the value of @@Recordcount as it would have been reset, automatically. However, if the @@Recordcount is checked before the error checking statement, then the @@Error would be reset. To get the @@error and the @@rowcount at the same time, the user needs to perform both functions in the same statement and store them in the local variable. The following statement can be used for the same:
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
39. How is an error handled in the SQL Server?
Answer : There are multiple ways of handling an error in the SQL Server. A user can use the @@Error, right after the query condition is executed. The user can also check for the@@Error <> 0. If the statement does not return a zero, it means some error has occurred. The @@Raiserror is another command for raising an error and handling it accordingly. The user can also use the try catch block for handling exceptions in the SQL Server program.
40. What do you know about the PatIndex?
Answer : The PatIndex returns the starting position of the first occurrence of a pattern in a specified expression. It returns zeros if the pattern is not found in the specified expression.
The following syntax can be used for checking of occurrence of a pattern.
PATINDEX ( ‘%pattern%’ , expression )
Follow the given example to understand the use of the PatIndex operation where the table used is called AdventureWorks:
USE AdventureWorks;
USE AdventureWorks;
GO
SELECT PATINDEX(‘%ensure%’,DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO
41. How does a user contain query a String that contains a ‘%’?
Answer : The following statement can be used to check for a String that contains a ‘%’ where the table used is tblPlayer:
SELECT Name FROM tblPlayer WHERE Name Like ‘%[”%”]’
42. How can a user get a value from a table with a separated comma?
Answer : The following syntax is used to get a value from a table with a separated comma. The table used in the example is HumanResources.Shift.
declare @vName nvarchar(100)
set @vName = ”
select @vName = @vName + ‘,’+ [Name] from HumanResources.Shift
select @vName
43. How does a user update a ‘Yes’ or ‘No’ and vice versa in a SQL Query?
Answer : The following code can be used to update a ‘Yes’ or ‘No’ in a SQL Query:
Update tablename set ColumnName1 = (case ColumnName1 when ‘Yes’
then ‘No’else ‘Yes’ end).
44. How does a user create a Temporary Table?
Answer : There are two ways of creating a temporary table with non-clustered index applied on it. The following pieces of code can be used to create a temporary table.
- CREATE TABLE #tmpTable
(
OfficeName varchar(50)
, officeid int
, CustID int
, AgentID int
, mlsid varchar(4)
, RequestMoreDetails int null
, Emails int null
)
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID) - select
OfficeName
, officeid
, o.CustID
, AgentID –
, o.mlsid
, PrintBrochure_Views = null
, RequestMoreDetails = null
, Emails = null
into #ForOffices from #Offices o
LEFT JOIN dbo.planparts WITH (NOLOCK)
ON bppa.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID)
45. What is a ’nolock’?
Answer : The ‘nolock’ is usually applied by the user while querying on production servers. This is used to make the records being queried sharable on the table, which means it will not prevent other queries from querying the same record parallel on the same table.
The risk with ‘nolock’ is that it has a tendency to return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.
46. Mention the different subsets present in SQL.
Answer : The different subsets that have been provided in the SQL server are:
- Data Definition Language (DDL) – It allows the user to perform various operations on the database such as the CREATE, ALTER and DELETE methods for objects.
- Data Manipulation Language (DML) – It allows the user to access and manipulate data. It helps one to insert, update, delete and retrieve data from the database.
- Data Control Language (DCL) – It allows the user to gain access to the database. The Grant, Revoke, etc. are the DCL methods that help the user to access permissions.
47. What is DBMS?
Answer : A Database Management System (DBMS) is a software application that interacts with the user, applications and the database itself to capture as well as 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 is usually of various types like strings, numbers, images etc.
48. What are the different types of DBMS?
Answer : There are two types of Database Management Systems:
Relational Database Management System: The data is stored in relations (tables). MySQL is a form of the RDBMS.
Non-Relational Database Management System: There are no relations, tuples and attributes in this type of database management systems. Mongo is one example of the Non-Relational Database Management Systems.
49. What is a table in SQL?
Answer : A table refers to a collection of data in an organized manner in form of rows and columns. The whole concept of DBMS in the SQL is based on the creation and manipulation of the table.
50. What is a field in SQL?
Answer : A field refers to the number of columns in a table. A table can have a number of columns and they are commonly called attributes.
51. What do you mean by JOINS in SQL Server Programming?
Answer : The JOIN clause is used to combine rows from two or more tables/ relations on the basis of a related column between them. It is used to merge two tables or retrieve data from there. There are 4 types of JOIN in SQL:
- Inner Join
- Right Join
- Left Join
- Full Join
The Full Outer Join is also another form of join in SQL.
52. Differentiate between CHAR and VARCHAR2 in SQL.
Answer : Both the Char and the Varchar2 methods are used for datatype of character. However, the VARCHAR2 method is used for character strings of variable length whereas the CHAR method is used for strings of fixed length. Observe the given example:
The char(10) can store a maximum of 10 characters only and will not be able to store a string of any other length whereas the varchar2(10) can store any length of character in the same variable.
53. What do you mean by a Primary Key?
Answer : A Primary key is a column in a relation that uniquely identifies each row in the table. No null values are allowed in the column which has been assigned the Primary Key. The Primary Key is used for relating to the other tables and establishing a realtion between both. It is a very important part of a SQL relation. The constraint NOT NULL is usually used with the Primary Key.
54. What do you mean by CONSTRAINTS in a SQL relation?
Answer : Constraints in a SQL Relation are used to specify a particular limit on the data type of the table. It can be specified while creating or altering any table statement. The generally used constraints are:
- UNIQUE
- NOT NULL
- CHECK
- PRIMARY KEY
- DEFAULT
- FOREIGN KEY
55. Differentiate between MySQL and SQL.
Answer : SQL is a standard language which stands for Structured Query Language and is based on the English language whereas MySQL is a database management system. SQL is the core of any relational database which is used for accessing and managing database whereas MySQL is a RDMS (Relational Database Management System) such as SQL Server.
56. What do you mean by Unique Key?
Answer : A Unique Key uniquely identifies a single row in the table. Multiple values are allowed in the same table unlike a Primary Key. Null values are allowed in the Unique Key attribute.
57. What do you mean by a Foreign Key?
Answer : Foreign key is a constraint that maintains referential integrity by enforcing a link between data in two tables or relations. The foreign key in the child table references the primary key in the parent table. The foreign key prevents actions that would destroy links between the child and parent tables.
58. What is Data Integrity?
Answer : Data Integrity is used as a measure of the accuracy and the consistency of the data stored in a database. It also serves as a measure of the integrity constraints to enforce business rules on the data when it is entered into an application or a database.
59. Differentiate between clustered and non-clustered index in SQL.
Answer : The following are the differences between the clustered and the non-clustered index in SQL are:
- Clustered index is used to alter the way records are stored in a database as it sorts out rows by the column, which are 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 to the original table rows after searching.
- One table can only have one clustered index whereas it can have numerous non-clustered index.
- Clustered index is used for easy retrieval of data from the database and it is faster whereas reading from a non-clustered index is relatively slower.
60. How is a SQL Query written to display the current date?
Answer : The built-in function known as GetDate() is generally used which helps to return the current timestamp/date.
61. Describe the different types of joins present in SQL.
Answer :
- Inner join:The Inner Join in MySQL is the most common type of join and it is used to return all the rows from multiple tables where the join condition is satisfied.
- Left Join:The Left Join in MySQL is used to return all the rows from the left table but only the matching rows from the right table are displayed in the query where the join condition is fulfilled.
- Right Join:The Right Join in MySQL is used to return all the rows from the right table but only the matching rows from the left table are displayed where the join condition is fulfilled.
- Full Join:The Full join returns all the records when there is a match in any of the tables. Hence, all the rows from the left-hand side table and all the rows from the right-hand side table are displayed.
62. What do you mean by Entities?
Answer : An entity can be describes as a person, place, or thing in the real world about which data can be stored in a database. Tables are used to store data that represents one type of entity.
A bank database, for example, 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.
63. What do you mean by relationships?
Answer : Relationships are relations or links between the various entities that have something to do with each other.
The customer name is related to the customer account number and the contact information which might be a part of the same table. There can also be relationships between separate tables (Customer Table to Accounts Table).
64. What do you mean by an Index?
Answer : An index in a SQL refers to a performance tuning method which allows faster retrieval of records from the table. An index creates an entry for each value and this speeds up the process of retrieving the required piece of data.
65. What are the different types of Index present in SQL?
Answer : The different types of Index present in SQL are Unique Index, Non-Clustered Index and Clustered Index.
66. What do you mean by a Unique Index?
Answer : The Unique index does not allow the field, to which it has been applied to, to have duplicate values if the column is unique indexed. If a primary key is defined, a unique index can be applied automatically.
67. What do you mean by a Non-Clustered Index?
Answer : The Non-Clustered Index does not alter the physical order of the table and maintains a logical order of the data. Each table can have multiple non-clustered indexes.
68. What do you mean by a Clustered Index?
Answer : The Clustered 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.
69. What do you mean by Normalization?
Answer : Normalization is the process of organizing data in a SQL relation to avoid duplication and redundancy within the rows of the same relation.
70. What are the advantages of Normalization?
Answer : The advantages of the Normalization have been listed below:
- Better Database organization
- Easier Implementation of Security Features
- More Tables with smaller rows
- Ensures Consistent data after Modification
- Quickly finds the information
- Efficient data access
- Allows easy modification
- Greater Flexibility for Queries
- Reduction of redundant and duplicate data
- More Compact Databases
71. Differentiate between TRUNCATE and DROP methods.
Answer : The DROP command removes a table and it cannot be rolled back from the database whereas the TRUNCATE command removes all the rows from the table.
72. What are the different types of Normalization?
Answer : There are many successive levels of normalization in SQL. They are known as normal forms. Each consecutive normal form depends upon the previous one. The first three normal forms that are usually adequate.
- First Normal Form (1NF) – There are 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) – This normalization is solely dependent on the primary key and no other non-key, supporting column value.
73. What do you mean by the ACID property in database?
Answer : ACID is the acronym for Atomicity, Consistency, Isolation, and 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 have failed where the 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, any 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 despite any restriction or error such as power loss, crash or any sort of error.
74. What is Trigger in SQL?
Answer : The Trigger in SQL is a special type of stored procedure that is used to define the automatic execution in a place or after certain data modifications. It allows the user to execute a batch of code when an INSERT, UPDATE or any other query is executed against a specific table.
75. Mention the different types of operators available in SQL Server.
Answer : There are three types of operators provided in a SQL Server:
- Arithmetic Operators
- Comparison Operators
- Logical Operators
76. Is a NULL value similar to a zero value or a blank space?
Answer : A NULL value is not similar to a zero or a blank space. The NULL value represents a value which is unavailable, unknown, unassigned or not applicable whereas a zero is a number and blank space is a character. Both the zero and blank space are assigned some values.
77. Differentiate between the NATURAL JOIN and the CROSS JOIN.
Answer : The CROSS JOIN produces the cross product or Cartesian product of two tables while the NATURAL JOIN is based on all the columns having the same name and data types in both the tables.
78. What do you mean by a Sub Query in a SQL?
Answer : A subquery in a SQL Server is a query inside another query where a query is defined to retrieve data or any kind of information back from the database. In a subquery, the outer query is known as the main query whereas the inner query is known as the 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 =.
79. Mention the different types of Subqueries present in SQL Server.
Answer : There are two types of subquery in SQL- Correlated and Non-Correlated subqueries.
- Non-Correlated subquery: This query is an independent query where the output of the subquery is substituted in the main query.
- Correlated subquery: These are queries in which the select the data from a table is 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.
80. Mention the different ways of counting of records in the table.
Answer : The following command can be used to count the number of records:
- SELECT * FROM table1
- SELECT COUNT(*) FROM table1
- SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
81. Write a Query to find all the names of employees beginning with ‘A’.
Answer : SELECT * FROM Table_name WHERE EmpName like ‘A%’
82. Write a Query to get the third highest salary from a table called ‘EMPLOYEE_TABLE’.
Answer : The following Query can be used to get the third highest salary from the EMPLOYEE_TABLE
SELECT TOP 1 salary FROM(
SELECT TOP 3 salary
FROM employee_table
ORDER BY salary DESC) AS emp
ORDER BY salary ASC;
83. Why do we need group functions in SQL?
Answer : The Group functions in SQL work on the set of rows and returns only one result per group.
84. What are the different types of relationships in SQL?
Answer : Relationships are the different connections between the tables in a database. There are various relationships available in SQL:
- One to One Relationship
- Many to One Relationship
- One to Many Relationship
- Self-Referencing Relationship
85. How to insert NULL values in a column in a relation?
Answer : SQL provides two ways to insert NULL values in any column in a relation:
- Implicitly NULL values can be inserted by omitting a column from the column list
- Explicitly NULL values can be inserted by specifying the NULL keyword in the VALUES clause
86. Differentiate between the IN and BETWEEN condition operators.
Answer : The BETWEEN operator is used to display the rows based on a range of values in a row. Observe the given example:
SELECT * FROM Students where ROLL_NO BETWEEN 10 AND 50;
The IN condition operator is used to check for the values contained in a specific set of values. Follow the given example:
SELECT * FROM students where ROLL_NO IN (8,15,25);
87. What are the various uses of SQL functions?
Answer : The various SQL functions are used for the given purposes:
- To perform some calculations on the data provided in the relation
- To manipulate the output produced from a query
- To format dates and numbers in the table
- To modify individual data items in the relation
- To convert the data types in the relation
88. Why is the MERGE statement used?
Answer : The MERGE statement allows a conditional update or the insertion of data into a table. It performs an UPDATE if a row exists and an INSERT if the row does not exist.
89. What is a recursive stored procedure?
Answer : A recursive stored procedure refers to a stored procedure which calls itself until it reaches some boundary condition mentioned by the user. This recursive function or procedure helps the programmers to use the same set of code multiple number of times.
90. What do you mean by a CLAUSE in SQL?
Answer : A SQL clause helps a user 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. The WHERE and HAVING clause are two of the most commonly used clauses in SQL.
91. Differentiate between the WHERE and HAVING clause in SQL.
Answer : The HAVING clause can be used only with the SELECT statement. It is usually used along with the GROUP BY clause and whenever the GROUP BY is not used the HAVING clause behaves like a WHERE clause. The HAVING Clause is only used with the GROUP BY clause in a query whereas the WHERE Clause is applied to each row before the rows become a part of the GROUP BY function in a query.
92. What are the various ways in which a Dynamic SQL can be executed?
Answer : The following methods can be used to create and execute a Dynamic SQL:
- By writing a query with specific parameters.
- By using the EXEC.
- By using the sp_executesql.
93. Mention the various levels of constraints provided in SQL.
Answer : Constraints in the SQL are the representation of a column to enforce data entity and consistency. There are two levels of a SQL constraint:
- column level constraint
- table level constraint
94. How does a user fetch common records from two different tables?
Answer : The INTERSECT method is used to fetch common records from two different tables:
SELECT studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam
95. What are the case manipulation functions in SQL?
Answer : There are three types of case manipulation functions in SQL:
- 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’)
- 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’)
- INITCAP: This function returns the string with the first letter in uppercase and rest of the letters in lowercase.
Syntax: INITCAP(‘string’)
96. Mention the different set operators available in SQL.
Answer : The functions Union, Intersect and Minus operators are the set operators available in SQL.
97. What do you mean by an ALIAS command in SQL?
Answer : The ALIAS command is used in a SQL so that a name can be given to any table or a column. This alias name can be referred to in the WHERE clause to identify a particular table or a column. Observe the given example:
Select emp.empID, dept.Result from employee emp, department as dept where emp.empID=dept.empID
Here, the table aliases for the tables’ employee and department are emp and dept.
98. What do you mean by aggregate functions in SQL?
Answer : The aggregate functions are used to evaluate mathematical calculation and return a single value. These calculations are done from the columns in a table. The max() and count() methods are common example of aggregate functions that are calculated with respect to numeric.
99. What do you mean by scalar functions in SQL?
Answer : The Scalar functions return a single value based on the input value. The UCASE() and the NOW() methods are examples of scalar functions that are used to calculate with respect to string.
100. How are alternate records fetched from a table?
Answer : A user can fetch alternate records, both odd and even row numbers.
To display even numbers, the following piece of command can be used:
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0
Similarly to display odd numbers, the following piece of code can be used:
Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=1
101. Which operator is used in the query for pattern matching?
Answer : The LIKE operator is used for pattern matching in SQL. The other operators that can be used for pattern matching are -.
- % – It is used to match zero or more characters.
Observe the given example: SELECT * from students where studentname like ‘a%’
- Underscore (_)- It is used to match exactly one character.
Observe the given example- SELECT * from student where studentname like ‘abc_’
102. How can unique records be selected from a table?
Answer : The DISTINCT keyword can be used to select unique records from a table in SQL. Observe the following example where only the Unique studentID is displayed:
Select DISTINCT studentID from Student
103. How does one print the first 5 characters of a string?
Answer : There are a number of ways provided in SQL to fetch characters from a string. Observe the given example to understand better:
Select SUBSTRING(StudentName,1,5) as studentname from student
104. Differentiate between PL/SQL and SQL.
Answer : SQL is an established query language that allows the user to issue a single query or execute a single INSERT/UPDATE/DELETE whereas the PL/SQL is Oracle’s “Procedural Language” SQL which allows the user to write a full program with loops, variables, etc. to accomplish multiple operations such as SELECT/INSERT/UPDATE/DELETE.
105. What do you mean by a View?
Answer : A View in SQL is a virtual table which consists of a subset of data contained in a table. Since views are not present, they use up less space to store. A View can have data of one or more tables combined and it depends on the relationship between the two related tables.
106. Why are Views used in a SQL?
Answer : A View refers to a logical snapshot based on a table or another view and is used for the following reasons:
- Restricting access to data.
- Ensuring data independence.
- Making complex queries simple to use.
- Providing different views of same data.
107. What do you mean by a Stored Procedure?
Answer : A Stored Procedure is a function which consists of a number of SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and are executed whenever and wherever required by the user. This flexibility of the stored procedure saves time and avoids the time wastage concerned with writing the same piece of code more than once.
108. What are the advantage of Stored Procedures?
Answer : A Stored Procedure can be used as a means of modular programming which means that it has to be created once, stored for future use and can be called several times, whenever and wherever, according to requirement. This supports faster execution and reduces network traffic and provides better security to the data present in the relation.
109. What are the disadvantages of Stored Procedures?
Answer : The disadvantage of the Stored Procedures is that they can be executed only within the database and they utilize more memory in the database server.
110. What are the different types of user-defined functions?
Answer : SQL Server has defined three types of user-defined functions:
- Scalar Functions: They return the unit and variant defined within the return clause.
- Multi-statement valued functions: They return a table according to specification.
- Inline Table-valued functions: They return a table according to specification.
111. What is meant by Collation in SQL?
Answer : Collation in SQL is defined as a set of rules that determine how will data be sorted and compared. For example, character data is sorted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width etc.
112. Mention the different types of Collation Sensitivity.
Answer : SQL has defined four different types of Collation Sensitvity that have been listed below:
- Case Sensitivity: ‘A’ and ‘a’; ‘B’ and ‘b’.
- Kana Sensitivity: Sensitivity concerning the included Japanese Kana characters.
- Width Sensitivity: Sensitivity regarding a single byte character and a double-byte character.
- Accent Sensitivity
113. What are Local Variables in SQL?
Answer : Local Variables can be used only inside the function where they have been defined. Their scope is also limited to that particular function. These variables are not used or referred to by any other function.
114. What are Global Variables in SQL?
Answer : Global variables are the variables which can be accessed throughout the program as they have been declared globally. Global variables cannot be created whenever a particular function is called. The global variable ceases to exist as soon as the SQL Server Program ends.
115. What do you mean by Auto Increment in SQL?
Answer : The ‘AUTO INCREMENT’ keyword allows the user to create a unique number. This unique number is used to generate a number whenever a new record is inserted into the table. This keyword is generally used by the user whenever a PRIMARY KEY is used. The ‘AUTO INCREMENT’ keyword can be used in Oracle and the IDENTITY keyword can be used in SQL SERVER.
116. What do you mean by a Datawarehouse?
Answer : A Datawarehouse refers to a central repository of data where the data has been assembled from multiple sources of information. These snippets of data have been consolidated, transformed and made available for both mining as well as online processing. Warehouse data also has a subset of data called Data Marts.
117. What do you know about the different authentication modes in SQL Server?
Answer : The different authentication modes in SQL server are the Windows mode and the Mixed Mode.
118. How to switch between the various authentications modes in SQL Server?
Answer : The following steps can be used to switch between authentication modes:
- Click Start and then Programs and then on Microsoft SQL Server and click the SQL Enterprise Manager to run the SQL Enterprise Manager from the Microsoft SQL Server program group.
- Select the server from the Tools menu.
- Select the SQL Server Configuration Properties, and choose the Security page.
119. What is the STUFF function and why is it used?
Answer : The STUFF function is used to overwrite existing character or insert a string into another string.
Follow the given Syntax:
STUFF(string_expression,start, length, replacement_characters) where,
string_expression is the string that will have characters substituted, start refers to the starting position, and length refers to the number of characters in the string which are substituted and replacement_string are the new characters which are injected in the string.
120. What is the REPLACE function and why is it used?
Answer : This function is used to replace the existing characters of all the occurrences. Follow the given syntax:
REPLACE (string_expression, search_string, replacement_string)
Here every search_string in the string_expression will be replaced with the replacement_string.
121. What do you mean by a cursor?
Answer : A cursor can be defined as a database object that allows to manipulate data on a row-by-row basis.
122. What do you mean by a linked server?
Answer : The Linked Server option of SQL Server allows querying of data from several SQL Server instances. Using the Linked Server, the user can connect to other database instances on the same server or databases present on another machine or any kind of remote servers.
123. What are the different sort orders present in SQL?
Answer : The different types of sort orders are Case sensitive, Case Insensitive and Binary.
124. When can a user use the UPDATE_STATISTICS command?
Answer : The UPDATE_STATISTICS can be used by the users in the following cases:
- When the processing of large data has been completed.
- The command is used whenever a large number of deletions, modifications or copying of a large amount of data takes place into the tables or the indexes need to be updated to take care of these changes.
125. What do you know about the WITH CHECK OPTION clause?
Answer : The WITH CHECK OPTION is used for a view and prevents any modification to the data that does not confirm to the WHERE clause of the view definition. It allows the data belonging to the view to be updated through the view.
126. What is the Query Execution Plan?
Answer : The optimizer provided by the SQL Server optimizes the code to be effectively executed. A query execution plan is used to show how this particular optimizer would run the query. The Query execution plan can be understood by the given:
- Using the Show Execution Plan option available in Query Analyzer.
- Displaying Estimated Execution Plan on the query dropdown menu.
- Using the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.
127. Why is a SQL server Agent Windows Service used?
Answer : The SQL Server is a Windows service which handles the tasks scheduled within the SQL Server environment. These tasks are also called JOBS and are stored within the SQL server. The jobs can run through a trigger, a predefined schedule or on demand. This service is very useful in determining why a particular job did not run as intended.
128. What do you know about Transactions in SQL?
Answer : By using transactions the user can group all SQL commands into a single unit. The transaction begins with some task and finishes only when all the tasks within it are over. The transaction ends successfully only when all the commands in it have been successfully executed. Even if one command fails, the whole transaction fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are the commonly used by users. A group of tasks starts with the BEGIN statement. In case of a problem, the rollback command is executed to abort the transaction. If all the tasks run successfully, all the commands are executed through the COMMIT statement.
129. What are the differences between a Primary Key and a Unique Key?
Answer : By default, a clustered index on the column are created by the Primary Key whereas a non-clustered index are created by the unique key. The Primary key does not allow any NULL values but the unique key allows only one NULL value.
130. What is an execution plan?
Answer : An execution plan can be compared to a road map that graphically or textually points to the data retrieval methods which have been chosen by the SQL server query optimizer for a stored procedure or an ad-hoc query.
131. Why does one use an execution plan?
Answer : An execution plan is used only because it is a very useful tool for a developer to understand the performance characteristics of a query or a stored procedure.
132. How does a user view an execution plan?
Answer : SQL provides an option called the “Show Execution Plan” which can be found in the Query Analyzer. If this option is turned on, the user can view the query execution plan in a separate window when the query is run again.
133. Mention the properties of Relational Tables.
Answer : Relational tables in general have the following properties:
- Stored values are atomic.
- Each row in the relation is unique.
- The Column values are of the same kind.
- The sequence of the rows is insignificant.
- The sequence of the columns is also insignificant.
- Each column must have a unique name because many statements work on the attributes.
134. What do you mean by a dirty page?
Answer : Dirty pages are the buffer pages that contain modifications which have not been written to the disk.
135. What is ETL?
Answer : ETL is the acronym for Extraction, Transformation and Loading. It is the process of copying and cleaning data from the heterogeneous sources. It is an important part of development projects for data warehousing and business intelligence.
136. What are the differences between a Global Temporary Table and a Local Temporary Table?
Answer : Global temporary tables are visible to all sessions and are dropped when the session, that created it, ends, and all other sessions have stopped referencing that particular global table. A local temporary table exists only for the duration of a connection or inside a compound statement, for the duration of the compound statement.
137. What are different types locks provided in the SQL Server?
Answer : There are three kinds of locks provided in the SQL server:
Shared locks: They are used for operations which do not allow any change or update of data. SELECT is an example of a shared lock.
Exclusive locks: They are used for data modification operations. UPDATE, INSERT and DELETE are some examples of Exclusive locks.
Update locks: They are used when the SQL Server wants to modify a page. The update page lock is then promoted to an exclusive page lock before actually making the changes.
138. What is the TABLE data region?
Answer : The Table Data region has a fixed tabular structure, which means it has a fixed number of columns. It is useful for displaying data grouped according to the row. The user can have a maximum of 1 report item per cell. The size of the table depends on the number of rows a dataset fetches, i.e., if a number of rows returned by the dataset is more, the table can be expanded to multiple pages.
139. What is the MATRIX data region?
Answer : The Matrix Data region displays data in a pivot table format and is thus, popularly known as a pivot table or a crosstab report. It has a minimum of one row group and one column group. The size of the matrix data region depends on the columns and the rows fetched from a dataset.
140. What is the LIST data region?
Answer : The List Data region is a free layout which is useful for working with complex reporting designs. The list can be used to display multiple tables as well as matrices. Each table and matrix receives data from a different dataset.
141. What is the CHART data region?
Answer : The Chart Data region is used for displaying the data graphically, i.e., in the form of a chart. A number of chart types have been included in the SSRS 2008 such as the line chart, the pie chart, the columns chart etc.
142. What do you mean by a GAUGE in the SQL Server?
Answer : A Gauge is generally used by a user in a table or matrix to show the relative value of a field in a range of values in the data region. The user is also free to add a gauge to the design surface to show a single relative value.
143. How can a user enhance his/her SSRS report?
Answer : There are a number of methods that can be applied to enhance a SSRS report. Some of the methods have been given below:
- The data can be displayed in graphic format using the Chart Data Region.
- Sorting can be used.
- If a couple of reports are related, the whole report can be made interactive by using a link and connecting the relations using the bookmark link, the hyper link or the drill through the report link.
- By adding sub-report. A sub-report is a stand-alone report which can be linked by the user to another report based on its content using specific parameters.
- By adding custom fields. Custom fields provide the same functionality as an alias columns provides in a SQL server query. It is the timing of the operation that differs from the alias columns. The calculation is performed on the dataset by the report server.
- By using expression.
- By using custom code. SSRS allows users to include a custom code written in VB.Net. 8. A document map (navigational links to report item once report is rendered) can also be added to a report.
144. Mention the different aggregate functions provided within the SQL server.
Answer : The following are various aggregate functions available:
1. SUM
2. AVG
3. COUNT
4. COUNTDISTINCT
5. MAX
6. MIN
7. STDEV
8. STDEVP
9. VAR
10. VARP
145. How can a SSRS report be integrated within an application?
Answer : A SSRS report can be integrated within an application in three different ways:
By programmatically sending a web request using the SOAP to report server.
By navigating to an URL: This is the simplest and most popular way. A separate login might be required since the report from report server is being called directly. The address of the report server gets exposed to the user.
By using the IFrame, Browser control or the Report Viewer Control: The URL of the report is embedded into the server of the application. The address of the reportserver is not exposed in this situation. No separate window opens here. A user does not come to know that he/she has moved to different server.
146. What are custom fields? Why do we need to use custom field in a SQL Server?
Answer : Custom fields can be defined as alias columns of the report since the operation is performed on the report server instead of the database server. The custom field is very useful for the data manipulation like adding some fields whose value can be calculated based on expression, text.
147. How to add custom field to the SQL Server?
Answer : Custom fields can be added by right clicking on the dataset and then selecting the ‘Add in Dataset’ window. The New field dialog box will open. The name of the custom field has to be added and it has to be mentioned whether it is a database field or a calculated one. If it is calculated, then the computation in the window can be mentioned.
148. Can a user add a Custom Code in SSRS?
Answer : The SSRS allows all developers to add custom code in their reports. The code can be written directly into the embedded VB.Net and can be called by using the property expression. The user can also write a custom class library and refer to it in the report server. The advantage of the first method is that it is simple and easy to use but the only disadvantage is that it is available exclusively only for that report. While the second method has the advantage of being available for multiple reports, it has a lot of configuration overhead.
149. How does a user add a Custom Code in SSRS?
Answer : To write a custom code, one needs to right click on the Report Designer outside the report body and select the Properties and go to the Code tab and the custom code can be written there. To add a custom class library, one needs to right click on the Report Designer outside the report body and select the Properties and go to the Reference tab and add the reference by browsing to the assembly of the desired class library. A class library has to be created and then compiled before referencing it in the concerned SSRS report.
150. What do you mean by a SQL Injection?
Answer : The SQL Injection is an attack in which the attacker takes the advantage of an insecure application on the internet by running the SQL command against the database and is used to steal information from it, using the GUI of the website. This attack can happen with the applications in which SQL queries are generated in the code. The attacker tries to inject their own SQL into the statement that the application will use to query the database.
SQL Server has been there in market for long time, and it will be there for many more years. SQL Server is known as one of the robust database technologies, and we forecast the constant demand for SQL Server in upcoming years as well. Go through above 150 SQL Server Interview questions and surely they will help you in your Job interview.
