Important skill sets in SQL

1. Cursors:

Reference here

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.

This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Implicit cursors

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed. 

Explicit cursors

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

2.Stored Procedure

reference

1. One of the main benefits of using the Stored procedure is that it reduces the amount of information sent to the database server. It can become a more important benefit when the bandwidth of the network is less. Since if we send the SQL query (statement) which is executing in a loop to the server through network and the network gets disconnected, then the execution of the SQL statement doesn't return the expected results, if the SQL query is not used between Transaction statement and rollback statement is not used.
2. Compilation step is required only once when the stored procedure is created. Then after it does not require recompilation before executing unless it is modified and reutilizes the same execution plan whereas the SQL statements need to be compiled every time whenever it is sent for execution even if we send the same SQL statement every time.
3. It helps in re usability of the SQL code because it can be used by multiple users and by multiple clients since we need to just call the stored procedure instead of writing the same SQL statement every time. It helps in reducing the development time.
4. Stored procedure is helpful in enhancing the security since we can grant permission to the user for executing the Stored procedure instead of giving permission on the tables used in the Stored procedure.
5. Sometimes, it is useful to use the database for storing the business logic in the form of stored procedure since it makes it secure and if any change is needed in the business logic, then we may only need to make changes in the stored procedure and not in the files contained on the web server.
   
   Example:

1. CREATE TABLE  tbl_Students
   
   (
       [Studentid] [int] IDENTITY(1,1) NOT NULL,
       [Firstname] [nvarchar](200) NOT  NULL,
       [Lastname] [nvarchar](200)  NULL,
       [Email] [nvarchar](100)  NULL
   )

2. Create  PROCEDURE Getstudentname(
   
   @studentid INT                   --Input parameter ,  Studentid of the student 
   
   )
   AS
   BEGIN
   SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid 
   END
   

3.Difference between function and stored procedure in sql

Basic Difference. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters .Functions can be called from Procedure where as Procedures cannot be called from Function.

4.difference between stored procedure and trigger

We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete, and update) is fired on the table on which the trigger is defined. Stored procedure can take input parameters, but we can't pass parameters as input to a trigger.

What is a Trigger

Refference

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.

Types Of Triggers

There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there are three types of triggers and hybrids that come from mixing and matching the events and timings that fire them. Basically, triggers are classified into two main types:

1. After Triggers (For Triggers)
2. Instead Of Triggers

(i) After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views. 
AFTER TRIGGERS can be classified further into three types as:

1. AFTER INSERT Trigger.
2. AFTER UPDATE Trigger.
3. AFTER DELETE Trigger.

Let’s create After triggers. First of all, let’s create a table and insert some sample data. Then, on this table, I will be attaching several triggers.