SQL Server Interview Questions

SQL Server Interview Questions and Answers

In case you’re searching for SQL Server Interview Questions and answers for Experienced or Freshers, you are at the correct place. Here we cover the entire frequently asked questions and answers on SQL Server Interview Questions which would help you to be familiar with SQL SERVER concepts related to a job interview. SQL Server questionnaire over here indicates the importance of Relational Database Management Systems and most of the questions prepared by the experts to get detailed knowledge on the database. The contents covered like normalization, Primary Key and Unique Key, Defaults, ACID, Index, Data Modeling, cursors, SQL Server syntax and statements,  Joins, Triggers etc will surely help you crack the interview and obtain the dream profession in SQL Server field. If you are looking for roles like SQL Programmer, Database Administration Specialist, SQL Server Developer, Power BI Developer, and Server Software Engineer, then it is good to go through the questions provided by our team.

Database Management is the skill which is learned through the SQL Server Interview Questions and Answers with the GangBoard. Here we take a deep look in to the interview questions which are needed after the live training to get prepared for the Placement Training to face the interviews. The inventory details, employee information customer information, budgeting details, pricing of the product and the competitor details are maintained through the database management which supports for the 524 terabytes of data for the storage. Job Oriented Training and Placement Training are essential to get the competitive skill to gain the 100 percent knowledge which the corporate expect from the fresher. Corporate companies don’t want to waste time and resources in the training and the demand for the Certification Training to make the employees work individually towards the goals of the organization.

The SQL Server software supports the enhanced security and minimization of the cost for the ownership of the software. The different functions in the RDBMS are analyzed with the questions which bring a clear picture of the responsibilities of a database administrator job. Let us take a virtual tour towards the database products, different tasks associated with the product and the challenges undertaken by the responsible authorities in the job roles with these interview question answers. The dream and goal of the learner arrive with constant support from the GangBoard through the SQL Server Course Online.SQL Server is the most suitable platform store data and it is a popular Relational Database Management System (RDMS) to perform the various functions such as retrieve, update, and store. SQL Server is known as the primary language to manage data in RDBMS. To become a skilled professional in the SQL Server then you are at the right place to learn all the basic concepts of SQL Server by the SQL Server interview questions and Answers which we provide.

Best SQL Server Interview Questions and Answers

We are living in a world where there is a need for a huge amount of data so, for that we need a system to store that huge amount of data. Here comes the solution and that is the Relational Database Management System. This database system is used to store a huge amount of data and each and every should have an idea about SQL Server, so in order to gain the basic knowledge, we need to have a look at these SQL Server Interview Questions and Answers.

We are providing you with Top SQL Server Interview questions and answers to build your career in this field. We have researched a lot to provide you with the most frequently asked and important SQL Server interview questions to make sure that every individual to be familiar with the concepts of SQL Server. If you learn this SQL Server interview questions can crack any kind of SQL interviews in the IT sector. So now let’s have a deep dive into these interview questions to gain lots of knowledge on SQL Server.

SQL Server is one of the most popular as well as frequently used databases till today, and therefore there is a huge demand for SQL Server DBAs across the world. Here in this blog, I have come up with a few SQL Server Interview Questions and Answers that are frequently asked. If are in a search of a perfect guide then you are at the right place to learn all the concepts related to Oracle, SQL, MySQL, and MS SQL Server databases. These SQL Interview questions and answers are designed for both beginners as well as professionals to reap the maximum benefit from this blog. I hope our SQL server interview questions and Answers would boost your interview preparation and help you crack the interview better.

Q1. What is DBMS?

The database management system is a collection of programs that enables the user to store, retrieve, update and delete information from a database.

Q2. What is RDBMS?

Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model. Data from the relational database can be accessed or reassembled in many different ways without having to reorganize the database tables. Data from the relational database can be accessed using an API, Structured Query Language (SQL).

Q3. What is SQL?

Structured Query Language(SQL) is a language designed specifically for communicating with databases. SQL is an ANSI (American National Standards Institute) standard.

Q4. What are the different type of SQL’s statements ?

This is one of the most frequently asked SQL Interview Questions for freshers. SQL statements are broadly classified into three. They are

• DDL – Data Definition LanguageDDL is used to define the structure that holds the data. For example, Create, Alter, Drop and Truncate table.
• DML– Data Manipulation LanguageDML is used for manipulation of the data itself. Typical operations are Insert, Delete, Update and retrieving the data from the table. The Select statement is considered as a limited version of the DML since it can’t change the data in the database. But it can perform operations on data retrieved from the DBMS before the results are returned to the calling function.
• DCL– Data Control Language DCL is used to control the visibility of data like granting database access and set privileges to create tables, etc. Example – Grant, Revoke access permission to the user to access data in the database.

Q5. What are the Advantages of SQL?

• SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmers to interact with any database like ORACLE, SQL, MYSQL etc.
• SQL is easy to learn. The statements are all made up of descriptive English words, and there aren’t that many of them.
• SQL is actually a very powerful language and by using its language elements you can perform very complex and sophisticated database operations.

Q6. what is a field in a database ?

A field is an area within a record reserved for a specific piece of data. Examples: Employee Name, Employee ID, etc.

Q7. What is a Record in a database ?

A record is the collection of values / fields of a specific entity: i.e. an Employee, Salary etc.

Q8. What is a Table in a database ?

A table is a collection of records of a specific type. For example, employee table, salary table etc

Q9. What is a primary key?

A primary key is a column whose values uniquely identify every row in a table. Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. To define a field as primary key, following conditions had to be met :

• No two rows can have the same primary key value.
• Every row must have a primary key value.
• The primary key field cannot be null.
• Value in a primary key column can never be modified or updated, if any foreign key refers to that primary key.

Q10. What is a Composite Key ?

A Composite primary key is a type of candidate key, which represents a set of columns whose values uniquely identify every row in a table.
For example – if “Employee_ID” and “Employee Name” in a table is combined to uniquely identify a row its called a Composite Key.

Q11. What is a Composite Primary Key ?

A Composite primary key is a set of columns whose values uniquely identify every row in a table. What it means is that, a table which contains composite primary key will be indexed based on the columns specified in the primary key. This key will be referred in Foreign Key tables.
For example – if the combined effect of columns, “Employee_ID” and “Employee Name” in a table is required to uniquely identify a row, its called a Composite Primary Key. In this case, both the columns will be represented as primary key.

Q12. What is a Foreign Key ?

When a “one” table’s primary key field is added to a related “many” table in order to create the common field which relates the two tables, it is called a foreign key in the “many” table.
For example, the salary of an employee is stored in salary table. The relation is established via foreign key column “Employee_ID_Ref” which refers “Employee_ID” field in the Employee table.

Q13. What is a Unique Key ?

Unique key is same as primary with the difference being the existence of null. Unique key field allows one value as NULL value.

Q14. What is SQL Server?

Answer: SQL Server is a software designed by Microsoft and it is implemented by the specifications of the relational database management system. Moreover, it is called as MS SQL Server. It is a highly scalable product that can run on anything through a single laptop to a huge network of cloud servers.

Q15. What is the major difference between MySQL and SQL Server?

Answer:

Q16. What are the features of MS SQL Server?

Answer:The following are the features of MS SQL Server, and they are:

• It is highly scalable
• MS SQL Server is platform-independent
• It is both command and GUI based software
• It also supports SQL language which is an IBM product

Q17. What is the instance of  SQL Server?

Answer: The instance of SQL Server is the installation of MS SQL Server.

Q14. List out the authentication modes present in the SQL Server?

Answer: The authentication modes can be changed by selecting the tools menu in the SQL Server configuration properties and then choose the security page.
There are two types of authentication modes available in the SQL Server, and they are:

• Mixed Mode
• Windows Mode

Q18. Define SQL Profiler?

Answer: SQL Server Profiler is an interface that is used to manage and create traces and also analyze the trace results. Events of SQL Server are present in the trace files that can be later used and analyzed to replay certain steps while trying to diagnose a problem. Moreover, SQL Server Profiler acts as a GUI to monitor an instance of the database engine. By using SQL Server Profiler you can capture and save data regarding each event to the table and analyze it.

Q19. What are the different activities that are performed by SQL Server Profiler?

Answer: The activities that are performed by SQL Server Profiler are:

• Diagnosing and finding slow-running queries.
• Monitoring the performance of SQL Server to tune the workloads.
• Performs correlation operations or counters to diagnose the problems.
• Analyzing problem queries to find the solution to the cause of the problem.

Q20. Can SQL Servers be linked to other servers?

Answer: Yes, SQL Server can be connected to any database that has OLE-DB provider to connect to a link. Examples of SQL Servers which can be linked to other servers are as follows:

• Oracle has OLE-DB provider
• Transact-SQL

These servers allow SQL servers to execute the SQL script against OLE-DB sources of data on remote servers using OLE-DB providers.

Q21. What are the different types of backups available in SQL Server?

Answer: The different types of backups available in SQL Server are as follows:

• Transactional Log Backup
• Copy Only Backup
• Differential Backup
• Full Backup
• Filegroup and File Backup

Q22. What is the SQL Server Management Studio?

Answer: SQL Server Management Studio is defined as a client tool or a workstation used to manage or connect to your SQL Server from the graphical server instead of using the command line. Mainly in order to connect to an instance of an SQL Server, we need to have this software installed on our systems.

Q23. What is the Microsoft visual studio?

Answer: Microsoft visual studio is an Integrated Development Environment (IDE) that is mainly used to develop a program on the computer for Microsoft Windows, web applications, web services, and websites. This Microsoft visual studio is supported by different programming languages such as .NET, C#, and so on.

Q24. What are the different data types present in SQL Server?

Answer: Data types in SQL Server are divided into three types, and they are:

• Number types
• Date types
• String types

Q25. Define recursive procedure in SQL Server?

Answer: Recursive procedure in SQL Server is defined as a procedure or a method that finds a solution to the problem and displays the solution repeatedly. SQL Server calls the recursive procedure by itself as it supports this procedure.

Q26. What is the difference between global and local temporary tables?

Answer: The difference between the global and local temporary tables are as follows:
Global temporary tables: Global temporary tables in SQL Server are visible to all users and they are discarded or deleted when the connection established is closed or ended.
The syntax for the global temporary table is as follows:
CREATE TABLE ##<tablename>
Local temporary tables: Local temporary tables in the SQL server are visible to users until there is a connection and they are deleted or discarded when the connection is closed or ended.
The syntax for the local temporary table is as follows:
CREATE TABLE #<tablename>

Q27. Define CHECK Constraint in SQL Server?

Answer: CHECK Constraint in the SQL Server database is mainly applied to columns in the table in order to limit values in the columns. Moreover, this CKECK Constraint is used to enforce integrity.
Suppose if we define a CHECK Constraint for a single column it allows only some values for a particular column.

Q28. Explain in detail the relationships present in SQL Server?

Answer: There are mainly three types of relationships present in SQL Server, and they are:

• One-to-one
• One-to-many
• Many-to-many

One-to-one Relationship:
This kind of relationship is implemented mostly on a single table, in rare cases, it is implemented on two tables. For each and every instance of the first entity, there are is only one to one entity in the second table and vice versa.
One-to-many relationship:
For each and every instance in the first entity, there can be one or more matches in the second-most entity. But for the second-most entity, there can be one and only one instance in the first entity.
Many-to-many relationships:
In a many-to-many relationships, there can be one or more instances for the first entity which are present in the second-most entity. Furthermore, for each and every instance present in the second-most entity, there will be one or more instances in the first entity.

Q29. What role does database design have to play to increase the performance of the SQL Server based applications?

Answer: Database design plays a very major role in enhancing the performance. Suppose let us consider the situation like when we build a new system or modify the existing system, the design which we make must be correct and accurate. We must ensure that we place correct data in the appropriate tables. Which makes correct and exact relationships between the existing tables and also even eliminate the data redundancy. The ultimate goal called data redundancy is also eliminated. Planning a design comes under the iterative process that can be constantly reviewed as an application. It is possible only in rare cases and we will try to achieve it everytime. Therefore, a designer must be so accurate and perform the operations to ensure that the design of the database need to be more effective and reliable.

Q30. How can a developer help while designing logical and physical phases of database and how will it impact on performance of SqL Server application?

Answer: The developer must research more on huge volumes of data such as checking quality information and type of data to be accessed. Suppose if we are dealing with the existing system or you want to upgrade the present system, we need to analyze the present data. Moreover, we need to identify the defects and changes that need to be made. Solving all these problems will produce huge amount of efficient data and more reliable design.
Whenever we consider a new system, we need to analyze very deeply and provide good information. Finding exact data and user based information will increase the performance of SQL Server. Developer while designing provide effective data to design carefully. So, there will not be any problem from the developer side to increase the performance of SQL Server.

Q31. Define sub-query with its properties?

Answer: A subquery is the query that is present inside the main query or a subquery is a query that is nested inside the main query. Examples of sub-queries include Select, Delete, and many more. This subqueries can also be used when the expressions are allowed.
Properties of the sub-query are:
Always the subquery must be enclosed inside the parenthesis
One or more than one subquery can be added or included
A sub-query should not contain any order by clause

Q32. What are the different types of subqueries present in SQL Server?

Answer: There are three types of subqueries present in SQL Server, and they are:

• A multi-column subquery that returns multiple columns to the main query
• A multi-row subquery that returns multiple rows
• A single-row subquery that returns only one row

Q33. Define COALESCE in SQL Server?

Answer: COALESCE in SQL Server is mainly used to return the foremost null expression within the arguments. Moreover, this function in the SQL Server is also used to return a non-null attributes from more than one column in the arguments.
Example for the COALESCE in SQL Server is as follows:
Select COALESCE(empid, empname, income) from employee;

Q34. How do the exceptions are handled in the SQL Server Programming?

Answer: Exceptions in the SQL Server Programming can b handled using TRY and CATCH constructs and they are handled by writing the script inside the TRY block and by performing the error-handling in the CATCH block.

Q35. What is the purpose of FLOOR function in the SQL Server?

Answer: To round up or mark up the non-integer value to the previous or before least integer we use FLOOR function in SQL Server.
Example for FLOOR function is as follows:
FLOOR(6.8)

Q36. Define Trigger in SQL Server?

Answer: Triggers are mostly used in SQL Server to execute or run a batch or sequence of SQL code such as when to delete or update commands execution against a table. Triggers are automatically executed or triggered when the data is changed or modified. Furthermore, the trigger can be executed upon performing the operations such as update, delete, and insert.

Q37. What are the different types of triggers available in SQL Server?

Answer: Four types of triggers are available in the SQL Server, and they are:

• Instead of
• Update
• Insert
• delete

Q38. Define Collation in SQL Server?

Answer: Collation in the SQL Server is mainly defined to point the sorting order in the particular table. There are three types of sorting orders present in Collation, and they are:

• Binary
• Case sensitive
• Case insensitive

Q39. Which command is used to get the version of the SQL Server?

Answer: The following is the command used to get the version of the SQL Server
Select SERVERPROPERTY(‘productversion’)

Q40. Define SQL Server Magic Tables?

Answer: SQL Server creates the Magic Tables while performing the DML operations such as Delete, Insert, and Update to hold the values for the particular attribute. Magic tables are mainly used inside the trigger for the process of data transactions.

Q41. Name the command which is used to recompile the stored sequence or procedure at runtime?

Answer: Stored PROCEDURE is the command that can be executed with the help of recompile keyword
Example:
Exe <SPName>  WITH RECOMPILE

Q42. How to delete duplicate rows in SQL Server?

Answer: In SQL Server the duplicate or unwanted rows can be deleted using ROW NUMBER and CTE feature present in SQL Server.

Q43. What is the full form of SQL?

Answer: SQL means structured query language which is widely used in most popular Relational Database Management System (RDBMS).

 Q44. What are functions in the SQL Server?

Answer: The functions are the line of the statements that accepts the inputs and process them to perform a specific task to provide an output. The functions must have a meaningful name but should not start with a special character like at the rate (@), persentile (%), and hasg (#).

Q45. On what port does SQL Server run?

Answer: By default, the SQL Server runs on port number 1433.

Q46. Define a database table?

Answer: Database table is a tabular form of data that contains a set of records in the form of rows and columns. The following is the syntax used to create a table:
Syntax: Create table [Table_name]

Q47. Differentiate between DELETE and TRUNCATE statements?

Answer: The following are the differences between DELETE and TRUNCATE

Q48. What are joins in SQL?

Answer: The “Joins” is a clause used in SQL to merge rows from more than two tables, depending on the common column attributes between them. There are particularly used to combine or retrieve data from two tables. There are namely four types of joins in SQL:

• Full Join
• Inner Join
• Right Join
• Left Join

Q49. What are Constraints?

Answer: The constraints in the SQL database are used to impose a limit on the data types of a table. This can be specified while altering or creating the database table statement. The following are a few samples of constraints:

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

Q50. What is a Foreign key?

Answer: Foreign key is used to maintain the referential links between two tables within the same database to enforce integrity between the data present in the two tables.
The primary key in the parent table refers to the foreign key in the child table.
The foreign key prevents any action that would destroy the links between the parent and child tables.

Q51. What are the Entities in SQL Server?

Answer: Entities may be either a place, person or a thing in the existing world that can be stored in a database. One form of entity is represented by Table. For example, A college database has a student table to store student’s information. students table stores this information as a set of entities in columns for each student.

 Q52. What are the Relationships in SQL Server?

Answer: The link or relation between entities that connect with each other. For example, the student’s name is related to the department and colleger, which might reside in the same table. Relationships might exist between separate tables.

Q53. What is an Index in SQL Server?

Answer: The performance tuning method used to retrieve the records at lightning-fast speed form a table is called indexing. To achieve the speed an index created for every value entered.

Q54. Explain different types of indexes?

Answer: There are three types of index namely:

• Clustered Index
• Non-Clustered Index
• Unique Index

Q55. Is it possible to take backup for Resource DB?

Answer: There is no direct way to take the backup from resource DB, the only way to get a backup is by using windows backup option for resource files like LDF and MDF.

Q56. What is database normalization?

Answer: The process of organizing the tables and attributes of a relational database to minimize dependency and redundancy is called Database normalization. It is generally a bottom-up approach used for dividing large tables into less redundant tables and define relationships among entities.

Q57. How many normalizations are present in SQL database

Answer: The following are the few normalizations present in an SQL database:
1NF
2NF
3NF
Boyce-Codd Normal Form (BCNF)
4th NF
5th NF
6th NF

Q58. Define De-normalization?

Answer: De-normalization is a process of adding depulicate data to a database in order to enhance the performance of the data. This technic is used to move the normal forms of database modeling from higher to lower to increase the access speed of the database.

Q59. What is a stored procedure? How to create a Stored Procedure

Answer: A Stored Procedure is a group of Transaction-SQL statements that contains a  precompiled set of either one or several statements stored together in one database to reduce the network load. The following query is used to create a stored procedure.
Query:  “Create proc” [statement] Example:

CREATE PROCEDURE samplestudents  
AS   
BEGIN  
SELECT studentId, Name, Gender, DepartmentName  
FROM tblstudent  
INNER JOIN tblDepartments  
ON tblstudents.studentsDepartmentId = tblDepartments.DepartmentId  
END

Q50. What is a recovery model? List a few recovery model in SQL Server?

Answer: A database can have only one recovery model and hepls the SQL Server to store data in the transaction log file and and also the duration of storage. It checks whether the backup is available for particularly selected recovery model. The following are the three different types of recovery models:

• Full
• Simple
• Bulk-Logged

Q51. What are different backups available in SQL Server?

Answer: The following are the different backups available in SQL Server:

• Transactional Log Backup
• Full backup
• Differential Backup
• File and Filegroup backup
• Copy Only Backup

Q52. What is OLTP?

Ans. OLTP stands for Online Transaction Processing, that follows data normalization rules to ensure data integrity and resolves complex information by breaking it down into a most simpler structures.

Q53. Mention the properties of the Relational tables?

Ans. The following are the six properties of Relational tables:
Each row and column must have a unique name.
The sequence of rows and columns is insignificant.
Column values are of the same kind.
Values are atomic.

Q54. What is Mirroring?

Ans. Mirroring method is to maintain a highly available standby server solution with a consistent primary transaction server. Transaction history is sent from the principal server to a secondary server and keeps the secondary server up to date with respect to the principal server.

Q55. How to check the SQL Server version?

Ans. To check the SQL Server version by running the following command:
SELECT @@Version

Q56. What is an SQL Server Agent?

Answer: The purpose of SQL Server agent is to schedule the scripts and jobs and helps in implementing the day to day administrator activites by automatically executing the scheduled tasks.

Q57. What are SQL Injections? And How to Prevent SQL Injection Attacks?

Answer: The method of retrieving secure data from the database. The following are a few SQL injection attacks:
The proper validations for input fields.
Store procedures
Parameterised queries
Display database error messages in frontend
Frequent code reviews
An SQL injection is a code injection technique is used to attack data-driven applications.

Q58. Differentiate between Lob and Long datatypes?

Answer: The following are the difference between Lob and Long datatypes:

 Q59. Answer the output of the following query?

Query: Select * from (select ‘a’ union all select ‘b’) Q;
Answer: It will throw an error because no values are selected in Subquery.
Error code-ORA-00923 from keyword not found expected values.

Q60. Differentiate between ‘Between’ operator and ‘In’ operator?

Answer: The following are the difference between “BETWEEN” operator and “IN” operator:

Q61. How can you select all the even number of records from a table? All the odd number of records?

Answer: The following are the queries required to select odd and even numbers from a table:
Select * from table where id % 2 = 0  to select even numbers
Select * from table where id % 2 != 0 to select odd number

Q62. Differentiate between the WHERE and HAVING clauses?

Answer: When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

SQL Insert, Update and Delete Commands Interview Questions

Q1. Define SQL Insert Statement ?

SQL INSERT statement is used to add rows to a table. For a full row insert, SQL Query should start with “insert into “ statement followed by table name and values command, followed by the values that need to be inserted into the table. The insert can be used in several ways:

• To insert a single complete row.
• To insert a single partial row.

Q2. Define SQL Update Statement ?

SQL Update is used to update data in a row or set of rows specified in the filter condition.
The basic format of an SQL UPDATE statement is, Update command followed by table to be updated and SET command followed by column names and their new values followed by filter condition that determines which rows should be updated.

Q3. Define SQL Delete Statement ?

SQL Delete is used to delete a row or set of rows specified in the filter condition.
The basic format of an SQL DELETE statement is, DELETE FROM command followed by table name followed by filter condition that determines which rows should be updated.

Q4. What are wild cards used in database for Pattern Matching ?

SQL Like operator is used for pattern matching. SQL ‘Like’ command takes more time to process. So before using “like” operator, consider suggestions given below on when and where to use wild card search.

• Don’t overuse wild cards. If another search operator will do, use it instead.
• When you do use wild cards, try not to use them at the beginning of the search pattern, unless absolutely necessary. Search patterns that begin with wild cards are the slowest to process.
• Pay careful attention to the placement of the wild card symbols. If they are misplaced, you might not return the data you intended.

SQL Joins Interview Questions and answers

Q1. Define Join and explain different type of joins?

Another frequently asked SQL Interview Questions on Joins. In order to avoid data duplication, data is stored in related tables. Join keyword is used to fetch data from related tables. “Join” return rows when there is at least one match in both table. Type of joins are

• Right Join:Return all rows from the right table, even if there are no matches in the left table.
• Left Join:Return all rows from the left table, even if there are no matches in the right table.
• Full Join:Return rows when there is a match in one of the tables.

Q2. What is Self-Join?

Self-join is query used to join a table to itself. Aliases should be used for the same table comparison.

Q3. What is Cross Join?

Cross Join will return all records where each row from the first table is combined with each row from the second table.

Database Views Interview Questions

Q1. What is a view?

The views are virtual tables. Unlike tables that contain data, views simply contain queries that dynamically retrieve data when used.

Q2. What is a materialized view?

Materialized views are also a view but are disk based. Materialized views get updates on specific duration, base upon the interval specified in the query definition. We can index materialized view.

Q3. What are the advantages and disadvantages of views in a database?

Advantages:

• Views don’t store data in a physical location.
• The view can be used to hide some of the columns from the table.
• Views can provide Access Restriction, since data insertion, update and deletion is not possible with the view.

Disadvantages:

• When a table is dropped, associated view become irrelevant.
• Since the view is created when a query requesting data from view is triggered, its a bit slow.
• When views are created for large tables, it occupies more memory.

Q4. What is a stored procedure?

Stored Procedure is a function which contains a collection of SQL Queries. The procedure can take inputs , process them and send back output.

Q5. What are the advantages of a stored procedure?

Stored Procedures are precomplied and stored in the database. This enables the database to execute the queries much faster. Since many queries can be included in a stored procedure, round trip time to execute multiple queries from source code to database and back is avoided.

Q6. What is a trigger?

Database triggers are sets of commands that get executed when an event(Before Insert, After Insert, On Update, On delete of a row) occurs on a table, views.

Q7. Explain the difference between DELETE , TRUNCATE and DROP commands?

Once delete operation is performed, Commit and Rollback can be performed to retrieve data.
Once the truncate statement is executed, Commit and Rollback statement cannot be performed. Where condition can be used along with delete statement but it can’t be used with truncate statement.

Drop command is used to drop the table or keys like primary,foreign from a table.

Q7. What is the difference between Cluster and Non cluster Index?

A clustered index reorders the way records in the table are physically stored. There can be only one clustered index per table. It makes data retrieval faster.
A non clustered index does not alter the way it was stored but creates a completely separate object within the table. As a result insert and update command will be faster.

Q8. What is Union, minus and Interact commands?

MINUS operator is used to return rows from the first query but not from the second query. INTERSECT operator is used to return rows returned by both the queries.