Wednesday, 27 January 2016

Top 10 differences between Functions and Procedures in SQL

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


Wednesday, 20 January 2016

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 %

Sunday, 17 January 2016

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 %


Sunday, 10 January 2016

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.

Wednesday, 6 January 2016

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

Sunday, 3 January 2016

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

Wednesday, 30 December 2015

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

Sunday, 27 December 2015

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.

Wednesday, 23 December 2015

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.

Saturday, 19 December 2015

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)



Wednesday, 16 December 2015

PRIMARY KEY in SQL

PRIMARY KEY is a combination UNIQUE and NOT NULL which does not allow the duplicate and NULL values in to columns on which it is imposed or applied.
Table can be imposed with any number of UNIQUE or NOT NULL constraints but can have only a single PRIMARY KEY constraint which can be imposed either on a single column or multiple columns (COMPOSITE PRIMARY KEY).
As per specification of Dr.F.C.Codd, a table can have only one key or identity value for uniquely identifying or picking the complete row. The column or columns on which you imposed the PRIMARY KEY will be considered as identity of your table.
Example:
Create Table BANK (CustomerId Int PRIMARY KEY, Cname Varchar (50), Balance Decimal (7,2) NOT NULL)
We can also give a constraint name at column or table level.
Example-constraint name at column level:
Create Table BANK (CustomerId Int CONSTRAINT CID_PK PRIMARY KEY, Cname Varchar (50), Balance Decimal (7, 2) NOT NULL)
Example-constraint name at table level:
Create Table BANK (CustomerId Int, Cname Varchar (50), Balance Decimal (7, 2) NOT NULL, CONSTRAINT CID_PK PRIMARY KEY)

Wednesday, 9 December 2015

UNIQUE key in SQL

If UNIQUE constraint is imposed on a column or column’s the column will not allow duplicate values in to it.

Syntax:
Create Table <Table_name> <column 1><data type> [width] [UNIQUE],… <column N><data type> [width] [UNIQUE]

Example:
Create Table BANK (Customerid Int UNIQUE, Cname varchar (50), Balance Decimal (7,2) NOT NULL)

Note:
a) When a UNIQUE constraint is imposed on a column the column will not allow duplicate value but allow NULL value.

b) UNIQUE constraint in MS-SQL Server allows to store single NULL value in it but Oracle allows multiple NULL values.
UNIQUE constraint can be applied on multiple columns combined or separately also, NOT NULL can be applied on multiple columns separately.

UNIQUE key on multiple column's Example:
Create Table BranchDetails (Citycode varchar (10), Branchcode varchar (10), Location varchar (20), constraint CC_BC_UQ UNIQUE (Citycode, Branchcode)


Sunday, 6 December 2015

NOT NULL constraint

A constraint is a restriction or business rule which can be imposed on the data for managing data integrity. When a business rule is imposed on the data, the data cannot violate the rule.
When NOT NULL constraint is imposed on a column the column will not allow NULL values into it.
Syntax:
Create Table <table_name> <column 1><data type> [width][NOT NULL],…. <column N>< data type>[width][NOT NULL]
Example:
Create Table BANK (Customerid Int NOT NULL, Cname varchar (50), Balance Decimal (7, 2) NOT NULL)
Note: When a NOT NULL constraint is imposed on a column the column will not allow NULL values but allows duplicate values.