Top 10 differences between Functions and Procedures in SQL

Functions are Procedures are the most common concept used in advanced or complex applications to deal with data effectively and efficiently.

Please find the difference between Functions and Procedures as below:

Procedures	Functions
Procedures may or may not return a value	Functions always return a value
If Procedures returning a value, it can return one or N number of values	Functions return one and only one value
It can have parameters of type input or output	It has only input parameters
To specify the output parameters, we use output parameter	To specify output parameter, we use 'returns' clause
No need to use 'return' statement for returning the value. Assigning value to an output parameter is enough	To return a value we should use the 'return' statement
It allows Transaction management, error handling using Try-Catch and Error-Raising	None of the three are allowed
It allows call a procedure or a function from it	It allows to call functions but not procedures
A procedure cannot be embedded within a SELECT statement and executed	Function can be embedded directly under a SELECT statement and executed
To call Procedure 'EXECUTE' statement is used	To call Function 'SELECT' statement is used
It is used for defining the computer business logic	It is used for defining mathematical calculations

Top 7 differences between DELETE, DROP and TRUNCATE in SQL

Difference between Delete, Drop and Truncate is one of the most confusing concept for most of the people. However, this is the basic concept and easy to remember if understood correctly. Please find the differences with it's respective benefits while working for daily purpose as below:

1. DELETE operates on row-by-row whereas DROP and TRUNCATE works on table level.
2. Triggers get fired after execution of DELETE command whereas no triggers get fired in DROP and TRUNCATE.
3. DELETE and TRUNCATE can be COMMITTED and ROLL-BACKED if provided inside a transaction whereas DROP can’t be COMMITTED OR ROLL-BACKED.
4. TRUNCATE execute faster than DELETE as it works at table level whereas DELETE removes records row-by-row one at a time and an entry get recorded in the Transaction logs.
5. DELETE does not free the space containing the table whereas DROP and TRUNCATE free the space containing the table.
6. WHERE clause can be used in DELETE command whereas in DROP AND TRUNCATE can’t use it.
7. DELETE command keeps the lock over each row whereas TRUNCATE keeps the lock on table not on the entire row and DROP doesn’t keep lock neither at row nor table.

How to calculate Test Execution Productivity Improvement ?

Test Execution Productivity Improvement is the ratio of difference between test execution productivity of current year and last/prior year to test execution productivity of last year.Test Case Execution Productivity improvement is always measured in percentage (%).

How to calculate Test Execution Productivity Improvement:

Test Execution Productivity improvement = (Test Execution productivity of current year - Test Execution productivity of last year) / (Test Execution productivity of last year) * 100

Example:

Test Execution productivity of last year	Test Execution productivity of current year	Test Execution Productivity Improvement
4	5	25

Test Execution Productivity Improvement = (5-4) / 4 *100

                                                                     = 25 %

How to calculate Test Design Productivity Improvement ?

Test Design Productivity Improvement is the ratio of difference between test design productivity of current year and last/prior year to test design productivity of last year. If the Test Case Design Productivity is low due to changes in the requirement, it helps to factor the effort into the project plan and re-estimate the project plans. 
Test Design Productivity Improvement is measured in percentage (%).

How to calculate Test Design Productivity Improvement:

Test Design Productivity improvement = (Test Case productivity of current year - Test Case productivity of last year) / (Test Case productivity of last year) * 100

Example:

Test Design  productivity of last year	Test Design productivity of current year	Test Design Productivity improvement
8	10	25

Test Design Productivity Improvement = (10-8) / 8 *100

                                                               = 25 %

How to calculate Review Efficiency ?

Review Efficiency is the ratio of number of review defects to total number of defects in review and testing.  Review defects can be encountered in documents (e.g. test plan, test cases) as well as in code (e.g. unit, integration, system, function, procedures). Defects identified in review process costs lesser effort and amount to fixes it. It helps to decrease the defect leakage in subsequent later phases of testing stages.

It is measured at overall project level and stage level. It is measured in percentage (%).

How to calculate Review Efficiency:

Review Efficiency = (Total Number of Review defects) / (Total number of Review defects + Total number of Testing defects)*100

Example:

No. of Review defects	Total No. of Testing defects	Review Efficiency %
25	100	20

Review Efficiency = 25 / (25 + 100) * 100

                               = 20

Review Efficiency metric shows the efficiency of the review process in software testing. A higher ratio of Review Efficiency indicates better is the review process. If the ratio is low then is does not mean review process inadequate. When ratio is low then the project manager or team leader discuss with the team and work on Review Efficiency improvement.

Review Efficiency metric helps to know the review effectiveness and take action to improve the review process.

Note: The total number of Testing Defects includes all the defects including customer reported test defects.

DISTINCT keyword in SQL

When DISTINCT keyword in SQL is applied on a column it returns the values of column eliminating duplicates. It is used to display the distinct values.

SQL DISTINCT Syntax:

SELECT DISTINCT column1, column2…Column N from Table_name

Table STUDENT:

Roll number	First Name	Last Name	Department	Division	Address	Admission year	Grade
1	John	Jones	Coms	A	New York	2005	Pass
2	Mike	Decoza	IT	A	London	2006	Fail
3	Ravi	Sharma	EE	B	Mumbai	2007	Fail
4	Ajay	Singh	Civil	C	London	2005	Pass
5	Amenda	Jones	Telecom	A	Manchester	2005	Pass
6	David	Cena	Coms	B	Liverpool	2006	Pass
7	Harmeet	Patel	IT	C	Mumbai	2005	Pass

SQL DISTINCT Example:

SELECT DISTINCT Address from STUDENT

Above SQL query selects only the distinct values from the "Address" columns from the "STUDENT" table as below:

New York

London

Manchester

Liverpool

Mumbai

ORDER BY clause in SQL

ORDER BY clause in SQL is used for sorting the data either in ascending or descending order based on a specified condition.

ORDER BY clause syntax:

SELECT column-list FROM table_name [WHERE condition-optional] [ORDER BY column1, column2, .. columnN] [ASC |DESC];

Example-ORDER BY clause on single column:

Select * from BANK ORDER BY Salary ASC

Select Count (*) from EMP WHERE Job=’Clerk’ ORDER BY Salary ASC

Example-ORDER BY clause on multiple columns:

Select * from BANK ORDER BY Salary, CustomerId DESC

In the above query, if the two customers have the same salary then only it goes to CustomerId for ordering the data otherwise only ORDER BY salary is performed.

Note:

1. The ORDER BY clause sorts the records in ascending order by default
2. We can use the Ascending and Descending sorting criteria individually or in combination as well

Select * from BANK ORDER BY Salary DESC, CustomerId ASC

GROUP BY clause in SQL

GROUP BY clause in SQL is used data arrangement or sorting by specific condition. When GROUP BY clause is used, first the data gets divided into groups based on the column specified in the class then the group function gets applied once on each group to get the final result.

Example-GROUP BY clause on single column:

Select Deptnumber, Job, Min (Sal) from BANK GROUP BY Job

Above query retrieves least salary corresponding to each job.

Note: We can apply the GROUP BY clause on one or more columns.

Example-GROUP BY clause on multiple columns:

Select Deptnumber, Job, Max (Sal) from BANK GROUP BY Deptnumber, Job

Above query retrieves the highest salary corresponding to each job of each department.

In the above query, data first gets grouped based on the first column of the clause and then each group is sub-divided based on the second column of the clause. Now the group function gets applied on each inner group to get the final result.

While using the GROUP BY clause the select list can contain only the following unit:

1.        Group functions
2.        Columns that are present in GROUP BY clause
3.        Constants

Example:

Select GetDate (), Deptnumber, Max (Sal) from EMP GROUP BY Deptnumber

Select Count (*) from EMP GROUP BY Deptnumber

HAVING clause in SQL

HAVING clause in SQL used for arrangement or sorting by specific condition just like WHERE clause whereas these two clauses will be used as scenarios as following:

1. Use WHERE clause if restriction or data filtering is before grouping and use HAVING clause if the restriction or data filtering is after grouping.
2. Use WHERE clause for restricting a row and use HAVING clause for restricting a group.
3. WHERE clause can be used individually whereas HAVING clause can be used only in conjunction with GROUP BY clause.
4. WHERE clause could not be used with aggregate functions whereas HAVING can be used with aggregate functions.

Example:

1. Select Deptnumber, Count (*) from EMP GROUP BY Deptnumber HAVING Count (*) > 5
2. Select Count (*) from EMP WHERE Job=’Clerk’ GROUP BY Deptnumber Having Count (*) > 1

Query 1 retrieves the data of number of employees working in each department having count > 5.

Query 2 retrieves the data of number of Clerks working in each department having count > 1.

WHERE clause in SQL

The SQL WHERE clause is used to filter data records. It is used to fetch necessary records that fulfill a specified condition.

WHERE clause syntax:

SELECT column1, column2,…columnN FROM table_name WHERE column_name [operator value]

Example:

Select CustomerId, Cname from BANK where Salary >=7000

Select * from Bank where Salary >=7000

Select CustomerId, Cname from BANK where City=’Mumbai’

Query 1 retrieves all the rows for Customerid and Cname columns which satisfies the condition employee having Salary greater than or equal to 7000.

Query 2 retrieves all the rows for all the columns available in table which satisfies the condition employee having Salary greater than or equal to 7000.

Query 3 retrieves all the rows for Customerid and Cname columns which are from city Mumbai.

Note: The character or string field value need to be quoted in single quote.

We can use the =, <>, >, =>, <, <=, IN, BETWEEN, LIKE operators in WHERE clause.

CHECK constraint in SQL

CHECK constraint in SQL verifies the value in the column to be according to specification.

Example:

1. CHECK (Balance >= 1000)
2. CHECK (Balance BETWEEN 1000 and 7000)
3. CHECK (Balance IN (1000, 2000, 4000, 7000, 9000)

In Query 3, the Balance can have value 1000 or 2000 or 4000 or 7000 or 9000 only.

Note:

1.  We can impose one or more constraint on a column.
2.  Except NOT NULL constraint, UNIQUE, CHECK and FOREIGN KEY allow NULL values in to column. To restrict them you can explicitly impose a NOT NULL constraint on those columns.

CREATE Table BANK (CustomerId Int Primary Key, Cname Varchar (50), Balance Decimal (7,2) NOT NULL Constraint Bal_Chk  CHECK (Balance >=1000)