MainMenu

Home Java Overview Maven Tutorials

Wednesday 25 January 2017

Selenium Practice Page

This WebPage is designed for selenium practice HTML WebPage for selenium Practice Text :

This page is designed to access different elements of Web Page in Selenium

Link:Software Testing Stuffs

ChandanSinghChauhan
112364736474
SQAUsefull_thingsTesting stuff
Selenium testTest1Test2


First Name :

Last Name :



Male
Female



Who is more beautiful
Daisy : I am beautiful
Alisa : I am more beautiful









Drag the way2testing image into the rectangle:

Tuesday 24 January 2017

Configure Selenium and TestNG in Eclipse

Video for this tutorial:

Click here


For Video Tutorial : Move on Youtube Channel




Note : Select the playlist as per your need & move with number sequence


How to Download and configure selenium ?
click on the below link to download selenium
Click here
Now download Apache POI from below link
Click Here
Click the download link from left corner & Click on The latest Stable Release is apache POI .........

Now go to the eclipse


click on file, select New then Java Project






click on Next Button, then select Libraries from header tab


click on Add External Jars Now select the selenium from your local computer
select all the JARs & click open button







Click the finish button
CongratulationYour selenium is configured with eclipse.


How to configure Testng within Eclipse?

So we have Two ways to configure TestNG in eclipse

First way : From Eclipse Market Place

Open Eclipse

==>> click on Help
==>> select Eclipse MarketPlaceOption




In Search box Enter

TestNG

click on Enter or Go button ==>>

Uncheck the

TestNG M2E (Maven) Integration

checkbox and click on Confirmbutton ==>>

Accept the licence and click on

Finish

button


Accept the risk

click the button "i Accept the risk"

Restart the eclipse



Second way : From Eclipse Install New Software

Open Eclipse
click on Help
select Install New SoftwareOption








Click on Add.. button
In the dialog box fill the below value :
Name :- Testng
and for location , go to website , "https://testng.org/testng-eclipse-update-site/"

and select a version like https://testng.org/testng-eclipse-update-site/7.8.0

now same will be entered in Location

Location :-https://testng.org/testng-eclipse-update-site/7.8.0





Click on Next button

Click on OK button
Under Grid TestNG option with check box will appear
Click the Check Box & the Click Nextbuttoon
It will take little time to get download
CongratulationYour eclipse is configured with Testng.
Tag:

How To Download & install selenium?,

how to download & install TestNg?,

How to configure eclipse with selenium TestNG?

Sunday 22 January 2017

TEST CASE, TEST PLAN and RTM(Requirement Tractability Matrix)



How to write the test cases?
Test cases are written based on documents(adopted by the organization) like Business Requirement Specification(BRS) , Functional Specification(FS) or any other document which functionally describe the application.
It is good practice to write the test cases in early phase of development.
An ideal Test cases cover three section
  1. History of Test cases (Written by, Reviewed by, Approved by).

  2. Scenarios

  3. Scenario description & steps to achieve that scenario.


And the possible result of scenario/test steps execution can be : PASS, FAIL & PENDING(Due to defect, due to environment, Due to query etc.)
When to start test case execution ?
Execution of test cases can be started after successful completion of Smoke test.
Key Points :
  1. Every Test cases must be discrete.

  2. Test cases must be tune with the flow of application & should be in simple language.

  3. Test case should cover most of the functionality.


Test Case Design technique :
1. Test cases can be derived directly from a requirement specification or black box test design technique. These techniques are :
> Boundary Value Analysis
> Equivalence Partitioning
> Decision Table Testing
> State Transition Diagrams
> Use case Testing

2. Test case can also be derived from structure of System These techniques are :
> Statement Coverage
> Branch coverage
> Path Coverage
> LCSAJ Testing
3. Test cases can also be derived from tester experience on similar systems These techniques are :
> Error Guessing
> Exploratory Testing
Equivalence class partitioning :  Divide all possible inputs into classes (partitions) such that
                                                         • There is a finite number of input equivalence classes
                                                         • You may reasonably assume that
                                                                     • The program behaves analogously for inputs in the same class
                                                                     • A test with a representative value from a class is sufficient
                                                                     • if representative detects fault then other class members will detect the                                                                        same fault Below is an example with test case for Equivalence when a finance account will accept age only for 18-40  :
Test case :
SR Objective Steps Test data Expected Actual Status
1 Verify the age input box functionality input the numeric value in Age input box 0 to 18 Message " You are not eligible to open the account"
Input the invalid numeric value in input box(optional) -1 Message "Please enter valid data"
Input the valid numeric value in input box 18-40 green confirmation message should appear
input the value in numeric to input box More than 40 Message "You can not apply to open account"
Boundary Value Analysis : In any application it is most likely common that error occurs at boundaries.
                                                 Testing boundary conditions of eq. classes is more effective
                                                 i.e. values directly on, above, and beneath edges of eq. classes
                                                 • Choose input boundary values as tests in input eq. classes
                                                    instead of, or additional to arbitrary values
                                                 • Choose also inputs that invoke
                                                    output boundary values
                                                   ( values on the boundary of output classes ) Below is an example of test case for Boundary value :
SR Objective Steps Test data Expected Actual Status
1 Verify the age input box functionality input the numeric value in Age input box -1 Message "Please enter valid data"
Input the invalid numeric value in input box 0 Message " You are not eligible to open the account"
Input the valid numeric value in input box 17 Message " You are not eligible to open the account"
input the value in numeric to input box 18 green confirmation message should appear
input the numeric value in Age input box 19 green confirmation message should appear
input the numeric value in Age input box 40 green confirmation message should appear
input the numeric value in Age input box 41 Message "You can not apply to open account"

Decision Table Testing :

if different combinations of inputs result in different actions then it is difficult to to show using equivalence partitioning and boundary value analysis.
Example :  If an web page have two input box A & B where A is mandatory & if A or both are enable than only submit button will enable :
Condition                                                                                 Rule 1                                    Rule 2                       Rule 3
Input box A    (Mandatory)                                                            F                                             T                          F
input box B                                                                                      F                                             F                           T
Expected : Submit button                                                             F                                             T                           F

Use case testing technique :  Test case can also be derived from usecase documents . 


Test Management tools:
  1. QTest

  2. PractiTest

  3. ZEPHYR

  4. Test collab

  5. Xqual

  6. Test Rail

  7. Test Lodge

  8. Jira (Also a bug tracking tool)

  9. IBM Rational Quality Manager

  10. HP Quality Center

   

Test Plan

A Test plan is a document which is very similar to project plan but mainly focusing on testing which would be done.
It have the various section inside it, which explain how, when & what should be tested in a particular project.
It varies from organization to organization & project to Project.

The effectiveness of testing depends on robustness of test plan.

Test Plan Definition :A Software test Plan is a document that describes testing activities, scope, strategy, testing tools, Environment etc
ISTQB Definition: A document describing the scope, approach, resources and schedule of intended test activities. It identifies amongst others test items, the features to be tested, the testing tasks, who will do each task, degree of tester independence, the test environment, the test design techniques and entry and exit criteria to be used, and the rationale for their choice,and any risks requiring contingency planning. It is a record of the test planning process.

Test Plan TYPES :
Test plan can be divided into three major categories :
Master Test Plan : This test plan set a bench mark or parameter how other test plan should be created.
Testing Level Specific Test Plan : Like Unit testing Test Plan, Integration Testing Test Plan etc.
Testing Types specific Test Plan: Like Penetration Testing Test Plan, Security Testing Test Plan etc.
Here i have uploaded a sample TEST Plan Which can be downloaded by click the below link.
This sample test plan cover almost features of a test plan.



Click here to download sample of Test Plan

Tags :What is Test Plan?,What are main components of test plan?, how to create test plan?, Download Sample test plan



RTM : RTM stands for REQUIREMENT TRACEBILITY MATRIX and it is a document which Map functionalities to test cases.

Key Points :
>> In General, Rows have functionalities & column have Test case ID.
>> It ensure that each functionality have been covered in test cases.
>> When a Requirement will change then Associated test case will change & Changes can be traced easily.
>> Its life start with the project & and ends with the project.

Tags :-

What is test plan?

How to create test plan?

What is RTM?

Download sample test plan

Example of RTM

What are test cases?

How to design test cases?

Test Plan

Test Plan

A Test plan is a document which is very similar to project plan but mainly focusing on testing which would be done.
It have the various section inside it, which explain how, when & what should be tested in a particular project.
It varies from organization to organization & project to Project.

The effectiveness of testing depends on robustness of test plan.

Test Plan Definition :A Software test Plan is a document that describes testing activities, scope, strategy, testing tools, Environment etc
ISTQB Definition: A document describing the scope, approach, resources and schedule of intended test activities. It identifies amongst others test items, the features to be tested, the testing tasks, who will do each task, degree of tester independence, the test environment, the test design techniques and entry and exit criteria to be used, and the rationale for their choice,and any risks requiring contingency planning. It is a record of the test planning process.

Test Plan TYPES :
Test plan can be divided into three major categories :
Master Test Plan : This test plan set a bench mark or parameter how other test plan should be created.
Testing Level Specific Test Plan : Like Unit testing Test Plan, Integration Testing Test Plan etc.
Testing Types specific Test Plan: Like Penetration Testing Test Plan, Security Testing Test Plan etc.
Here i have uploaded a sample TEST Plan Which can be downloaded by click the below link.
This sample test plan cover almost features of a test plan.



Click here to download sample of Test Plan

Tags :What is Test Plan?,What are main components of test plan?, how to create test plan?, Download Sample test plan

Sunday 15 January 2017

Large data Data Generator for testing, validate Email

In most  times, it have been seen that a large amount of data required in our data base to test application THIS PART IS SLIGHTLY DIFFERENT FROM PERFORMANCE VOLUME TESTING.
Data Generator : It is an open source tool and GS Data Generator is an automated testing and data generation tool,
which enables you to create test data for software quality assurance testing (QA testing), performance testing,
usability testing and database load testing. GS Data Generator is a computerized testing application specifically
designed to generate random test data, meaningful test data and business intelligent test data for system integration testing, ERP, CRM and data warehouse development, and software marketing.
                                   DOWNLOAD HERE
  -------------------------------------------------------------------------------------------------------

How to Validate Email Address

In most of the application, Validation is implemented on two attribute "@" & "." only but
If we send an email & it is not received by user then there can be two things:
Softbounce : A temporary reason that email can not be delivered due to one of the below reason :
        > Mailbox is full (over quota).
        > Recipient email server is down or offline.
        > Email message is too large.
Hardbounce : A permanent reason that email can not be delivered  due to one of the below reson :
            > Recipient email address does not exist.
            > Domain name does not exist.
            > Recipient email server has completely blocked delivery.
Free tools available to validate email address :

Difference between Bug leakage & Bug release, Retesting & Regression Testing



Bug Leakage : If the bug is found at customer end  or at production environment and this bug should be detected by testing team, then it is known as bug leakage.
Key Reason : 1. Requirement is not captured completely.

2. Functionality was in scope but it is unknown for testing team.
3. Testing team/tester made some own assumptions.
  Bug Release : Build/Application which is going to release have some known bugs, but these bugs under low severity & low priority.
  ------------------------------------------------------------------------------------------------------- They are completely different from each other and key difference is listed below :
  1. Retesting :  To recheck the bug which was logged & valid with in same environment.
Regression : To check whether any surroundings(bug related area) is/are effected or not.

2. Retesting : Old data can b used to retest the bug as well as with new data. Regression :  Generally done with the new data only.

3.  Retesting : In one iteration/cycle there is two retesting round (Execution --> retesting --> retesting for                                       reopened bugs) Regression : In one iteration/cycle there is only one regression round after that retesting (Sanity: narrow                                    regression) is done.
4. Retesting : Generally build number will not change, just build with fixed bugs will be deployed on testing                                 server.
Regression : Build number should be changed, build which passed many testing rounds   have some enhancement/modification is deployed on testing environment.
5. Retesting : only failed test cases are executed.
Regression : Test cases are fetched from main test cases which are effected due to bug, and enhanced due to bug and they are joined in an effective manner to make a robust regression suite after that they are executed.

RAG , Error Guessing and Error Seeding

 

RAG : Stands for RED, AMBER & amp, GREEN very similar to traffic light.

RAG  Definition :
RED: Project have some significant issue & corrective actions required soon to meet business requirement.
AMBER :  There is some bugs in project which have negative effect but they can be dealt & tester move ahead                    with testing.
GREEN : Every thing is Okey & working as expected.
and when these terms are added in a report then it is known as RAG Report.

Tag: What is RAG Report?


Error guessing :  It is a test case design technique in which a tester guess that what error might occur and tester will add the test cases/test steps in his test suite.
Error seeding : It is a process in which a known fault is intentionally added to the program to get its effect in application.  

What are the test deliverables ? & REQUIREMENT TRACEBILITY MATRIX(RTM)

These are the document which a Test Lead/Manager need to deliver after completion of project :
In very rare case tester need to deliver test cases/check list.
In general below is the list of test deliverables :
> Effort Estimation/billing hour
> Bug Report/defect coverage report
> Known Issues
> Test Data (In case of finance it is mandatory)
> Test Plan & Test Strategy
------------------------------------------------------------------------ RTM : RTM stands for REQUIREMENT TRACEBILITY MATRIX and it is a document which Map functionalities to test cases. Key Points :
>> In General, Rows have functionalities & column have Test case ID.
>> It ensure that each functionality have been covered in test cases.
>> When a Requirement will change then Associated test case will change & Changes can be traced easily.
>> Its life start with the project & and ends with the project.

Usability Testing & its key point

Usability testing is a black box testing technique and the aim is to observe people using the product to erupt errors.
Key points of usability testing are :
Performance : How much time & how many steps required to complete basic task ?
Accuracy : How many mistake did people make?
Recall : How much does the person remember afterwards?
Emotional Response : How does the person feel about the task completed? would this person recommend this system to other?

What is GUI/UI testing?

GUI testing includes how the application handles keyboard and mouse event , how different GUI components like menubars, toolbar, dialog box, button, edit fields, list control, image etc reacts to user input and whether or not it performs in desired manner.
GUI testing mainly consider how data looks and how user feel while interacting with that particular application.
In most of the situation TEST CASE INCLUDES THE GUI BEHAVIOR of application.

What is the difference between Quality Assurance(QA) and Quality Control (QC)?

Difference between quality control & Quality assurance


1. QA deals with process and QC deals with actual product testing.
2. QA make sure that you are doing right thing in right way (means Verification) where QC Make sure that the actual result of application & expected result(means validation).
3. QA focus on quality building and hence it is preventing defect although Qc focuses on testing for quality and hence detecting defects.
4. Quality assurance meant for developing, organizing the best quality process and Quality control meant for implementing the process developed by former team.
5. QA Items are Plans, Requirement Specs, Design Specs, Code, Test Cases and QC have only actual product.
6. QA have activities :
Reviews
Walkthroughs
Inspections
QC Activity : Testing Activities

Tag: what is difference between QA & QC?

Difference between preventive & reactive approach in testing ?

Preventive Approach : It is the way to find many ways to test the application & break it before the it goes to production.
Reactive Approach : To test the application after the production & to design test suite will be based on bug found in production.
Reactive approach is more costly than proactive approach.

Que : What is DDL, DML & DCL ?

Ans :

Different between DDL & DML

Data Definition Language (DDL): statements are used to define the database structure or schema.
Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
Data Manipulation Language (DML): statements are used for managing data within schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
Data Control Language (DCL) statements. Some examples:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

Tag:What is DDL, what is DML

What is primary key, composite key, foreign key & Unique Constraint?



Que : What is primary key and role of primary key ?

Ans :
Primary Key : The PRIMARY KEY constraint uniquely identifies each record in a database table.
1.Primary keys must contain unique values.
2.A primary key column cannot contain NULL values.
3.Each table should have a primary key, and each table can have only ONE primary key.
4.A primary key is a field in a table which uniquely identifies the each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.
5.A table can have only one primary key which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.


CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

PRIMARY Key Example :


In MySql :
Create Table Employee
(
Emp_id int Not Null,
Emp_name varchar(255),
Emp_designation varchar(255),
PRIMARY_KEY(Emp_id)
)
SQL Server
Create Table Employee
(
Emp_id int Not Null PRIMARY KEY,
Emp_name varchar(255),
Emp_designation varchar(255)
)

Que : What is composite key ?


Ans.: A composite key, in the context of relational databases, is a combination of two or more columns in a table that can be used to uniquely identify each row in the table.

Que : What is foreign key and role of foreign key ?


Ans :
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Let's illustrate the foreign key with an example. Look at the following two tables:
The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

Foreign Key Example:


In Mysql
Create Table Employee
(
Emp_reg char(50) Not Null,
Emp_id int Not Null,
Emp_name varchar(255),
Emp_salary Decimal(10,2),
PRIMARY_KEY(Emp_reg),
FOREIGN KEY (Emp_id) REFERENCES Salary(Emp_id)
)
SQL Server
Create Table Employee
(
Emp_reg int Not Null PRIMARY KEY,
Emp_id Int FOREIGN KEY REFERENCES Salary(Emp_id)
Emp_name varchar(255),
Emp_designation varchar(255)
)

Que : What is the unique constraints?


Ans: The UNIQUE constraint uniquely identifies each record in a database table.
The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

For example : CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

Please note: 1.

You can only have one primary key per table, but multiple unique constraints (249, I believe).

2.

A PK can not have a NULL value - if you try to make a NULLABlE column as your PK, SQL Server will change it to NOT NULL first, and then create the PK. A Unique constraint can have one NULL value.


Tag :Difference between Primary key and Foreign Key with example, what is unique contraint, what is composite key

what is SQL, My SQl

What is the difference between SQL and MySQL or SQL Server?


SQL or Structured Query Language is a language; language that communicates with a relational database thus providing ways of manipulating and creating databases. MySQL and Microsoft’s SQL Server both are relational database management systems that use SQL as their standard relational database language.

Is a NULL value same as zero or a blank space? If not then what is the difference?


A NULL value is not same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.

How to add Multiple rows in a table?

Add multiple rows in a table



Syntax:


Insert Into Database_name.Table_name (Column_name 1, column_name 2, column_name 3)
Values ("value_1", "value_2", "value_3"), (value_1, value_2, value_3)

So you can add multiple rows in your table
Note : If you will not give the column name then it will enter the record from first column and so on.
For example :
Suppose that there is a database "Akshat" & table of name SALARY with columns :
Emp_id int(10)
Emp_name varchar(50)
Emp_reg varchar(50)
Emp_salary int(6)
and you wish to enter four employee record at one time then your query will be
Insert Into Akshat.Salary (Emp_id, Emp_name, Emp_reg, Emp_salary) Values (01, "chandan", "E01", "50,000"), (02, "adhiraj", "E02", "40,000"), (03, "akshat", "E03", "60,000")

OR
Insert Into Akshat.Salary
Values (01, "chandan", "E01", "50,000"), (02, "adhiraj", "E02", "40,000"), (03, "akshat", "E03", "60,000")

Now three rows will be added in your table.
Note : If you will miss any column value then Error message will appear.

Tag : Use of Insert Into statement, Add multiple rows in a table, query to add multiple rows in table

SQL query to create database and table.

SQL Query to create database.

Syntax :

Create Database database_Name


Example : Create Database Chandan
Now a database of name Chandan will be created.

SQl Query to create table in database.


Syntax :

Create Table database_name.table_name
(Column_name data_type,
Column_name data_type,
Column_name data_type
)

Example :
Create Table Chandan.Salary
(
Emp_id Int(10) PRIMARY KEY,
Emp_name Varchar(50),
Emp_salary Int(20)
)
Now a table of name Salary will be created with in Chandan database
Note : if you are inside the data base then in query database name is not required. if you are already with in Chandan database then
Example :
Create Table Salary
(
Emp_id Int(10) PRIMARY KEY,
Emp_name Varchar(50),
Emp_salary Int(20)
)

Tag :How to create database with example?,How to create table with example?

SQL And & OR keyword with example

And, OR Keyword

Used to get more filtered record
Table: <
Emp_idEmp_nameEmp_regEmp_salary
01chandanE_0125000
02adhirajE_0230000
03raghuE_0335000
04AlaxE_0440000
you need to find the Emp_name which have Emp_reg = R01 & Emp_id = 1
Select Emp_name from Salary Where Emp_reg = "R01" and Emp_id = 1
RESULT:
chandan Consider above table again & suppose you need to find the Emp_name which have Emp_reg = "R01" or Emp_id = 2 Select Emp_name from Salary Where Emp_reg = "R01" OR Emp_id = 2
RESULT:
chandan
adhiraj

Tag:SQL And, OR query with example, Use of SQL AND OR with example

SQL ORDER BY with example

SQL ORDER BY with example

ORDER BY

Used to sort records, and by default it will sort records in AESC order


Table : Salary <
Emp_idEmp_nameEmp_regEmp_salary
01chandanE_0125000
02adhirajE_0230000
03raghuE_0335000
04AlaxE_0440000

Suppose you need to find the Emp_name which have Emp_reg = "R01" & Emp_id = 1

Select Emp_name, Emp_salary from Salary ORDERY BY Emp_salary RESULT:
Emp_nameEmp_salary
chandan25000
adhiraj30000
raghu35000
Alax40000

Consider above table again & suppose you need to short the record in desc order


Select Emp_name, Emp_salary from Salary ORDERY BY Emp_salary DESC RESULT:
Emp_nameEmp_salary
Alax40000
raghu35000
adhiraj30000
chandan25000

Tag:SQL order by with example, Use of SQL ORDER BY Keyword with example

Use of SQL Update Keyword with Example

UPDATE Keyword

update keyword used to update the record in the table.

Syntax : UPDATE table_name Set column@ = value@, column$ = value$ Where column1 = value1 Example : Consider the following table Table Salary: <
Emp_idEmp_nameEmp_regEmp_salary
01chandanE_0125000
02adhirajE_0230000
03raghuE_0335000
04AlaxE_0440000

Suppose you need to update the salary of Adhiraj then

Update salary Set Emp_salary = 32000 Where Emp_name = "adhiraj" Note :Where clause is used to specify that which record in the table should be updated and if it is not used then all the record will be updated. Result: <
Emp_idEmp_nameEmp_regEmp_salary
01chandanE_0125000
02adhirajE_0232000
03raghuE_0335000
04AlaxE_0440000

Tag: SQL Update keyword with example

SQL Wildcards with Example

To fetch the result without using complete string. Better will be understood with below examples :

Example : Consider the following table Table: Salary
<
Emp_idEmp_nameEmp_regEmp_salary
01chandanE_0125000
02adhirajE_0230000
03raghuE_0335000
04raghunathE_0436000
05AlaxE_0440000
% wildcard:

Select * from Salary where Emp_name like "%ndan"

Result: Records starting from any character but followed by 'ndan' will appear Table: Salary
Emp_idEmp_nameEmp_regEmp_salary
01chandanE_0125000
wildcard %:

Select * from Salary where Emp_name like "ra%"

Result: All the records staring from 'ra' character will appear Table: Salary
Emp_idEmp_nameEmp_regEmp_salary
03raghuE_0335000
04raghunathE_0436000
_wildcard:

Select * from Salary where Emp_name like "-raj"

Result: Table: Salary
<
Emp_idEmp_nameEmp_regEmp_salary
02adhirajE_0230000
05AlaxE_0440000
[a-c]%wildcard:

Select * from Salary where Emp_name like "[a-c]%"

Result: Records starting from from any 'a, b, c' charcater will appear. Table: Salary
<
Emp_idEmp_nameEmp_regEmp_salary
01chandanE_0125000
02adhirajE_0230000
05AlaxE_0440000
[!]%wildcard :

Select * from Salary where Emp_name = "[!ar]%"

Result: All the records starting from letter a, r will NOTappear Table: Salary
Emp_idEmp_nameEmp_regEmp_salary
01chandanE_0125000

Tag : What is Wildcard in SQL ?

Alter Keyword with example

Use of Alter Keyword

Syntax : Alter Table Table_name ADD column_name datatype Example : Consider the following table Table: Salary

Emp_idEmp_nameEmp_regEmp_salary
01chandanE_0125000
02adhirajE_0230000
03raghuE_0335000
04raghunathE_0436000
05AlaxE_0540000
and it is required to add new column(city) in table then we will use alter keyword Query : Alter table Salary ADD column city varchar(50) Result:
Emp_idEmp_nameEmp_regEmp_salarycity
01chandanE_0125000Null
02adhirajE_0230000Null
03raghuE_0335000Null
04raghunathE_0436000Null
05AlaxE_0540000Null

Query to update column datatype :

In Mysql : ALTER TABLE table_name MODIFY COLUMN column_name datatype In sql : ALTER TABLE table_name ALTER COLUMN column_name datatype

Query to drop a column in Table

Syntax : ALTER TABLE tablename DROP COLUMN columnname Example : ALTER TABLE tablename DROP COLUMN city Now column city will be droped from table salary

Tag :Alter keyword with example, use of alter keyword in SQL

Difference between IN and Between statement

SQL IN and BETWEEN Statement

IN operator used to select the record which are matching within IN condition. where as Between operator used to specify a range to select records.
Consider the below table : Salary
Emp_idEmp_nameEmp_regEmp_salarycity
01chandanE_0125000Noida
02adhirajE_0230000noida
03raghuE_0335000Noida
04raghunathE_0436000Noida
05AlaxE_0540000Noida
IN Query Syntax :

Select column_name, column_name from table_name Where column_name IN("value1","value2", "Value3")


Select * from salary Where Emp_salary IN(20000, 30000) Result :

Only records which are matching under IN condition will appear

Emp_idEmp_nameEmp_regEmp_salarycity
02adhirajE_0230000noida
Between Query Syntax :

Select column_name, column_name from table_name Where column_name between "value1" and "value2"


Select * from salary Where Emp_salary BETWEEN 20000 and 30000 Result :

All the records which are matching within BETWEEN condition will appear

Emp_idEmp_nameEmp_regEmp_salarycity
01chandanE_0125000Noida
02adhirajE_0230000noida

Tag :SQL IN with Example, SQL Between with Example

Difference between Delete, Drop and Truncate

Difference between Delete , Drop and Truncate



There is thin line difference between Delete, Drop & Truncate Delete : Delete command is used to remove the rows of the table or data with-in rows, it is used with where clause.
If no where clause is used then all the records will be deleted.
But it does not free the space containing the table.
also this action can be roll back.

Truncate : The SQL TRUNCATE command is used to delete all the rows from the table and all space occupied by the table will become free.
Also this operation can't be rolled backed.
No triggers will be fired.

Drop : Drop command is used to remove the table permanently.
All the rows with stable structure will be removed.
Also when the table is dropped we can not get it back.
No DML trigger will be fired.

Example of Delete, drop & truncate :

Example: Consider below table : Salary
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

Now it is required to delete a row from Table then

Query of DELETE:


Delete
from Salary
Where Emp_id = 01
Result:
Emp_idEmp_salaryEmp_heading
0230000Senior Software Eng
0325000Software Analyst

Example of delete, without where clause
Delete
from Salary

Result: If where clause is not used then all the records will be deleted.

Emp_idEmp_salaryEmp_heading


Tag : What is Delete, Drop & Truncate in SQL?, difference between drop & delete

SQL Query to find second highest salary

Query to get second highest salary from table.


Consider the below Table : Salary
Emp_idEmp_Salary
0125000
0230000
0335000

Query 1 :Using a function Select MAX(Emp_salary) from Salary Where Emp_salary < (Select MAX(Emp_salary) from Salary)
Result :

Emp_Salary
30000


Query 2 :Using Limit Select Emp_salary from Salary order by Emp_salary desc Limit 1,1 Result :

Emp_Salary
30000


Note : Limit a,b means left a rows from top and show b number of rows.

Query to get 3rd highest salary from Employee table.

Consider the below Table : Salary:
Emp_idEmp_Salary
0125000
0230000
0335000

Query 1 :Using a function Select MAX(Emp_salary) from Salary Where Emp_salary < (Select MAX(Emp_salary) from Salary Where Emp_salary < (Select MAX(Emp_salary) from Salary) ) Result :

Emp_Salary
25000


Query 2 :Using Limit Select Emp_salary from Salary order by Emp_salary desc Limit 2,1 Result :

Emp_Salary
25000


Tag : Query to get second highest salary without function,Query to get 3rd highest salary from employee table

Left Join, Right Join, Inner Join with example

Inner Join with example

It will return only the matching records of both the table.
Syntax :

Select Column_Name1,Column_Name2
from Table1
Inner join Table2
On table1.column_name = table2.column_name


consider the following two tables : Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

Employee_detail:
Emp_idEmp_cityEmp_gender
01PuneMale
02DelhiFemale
03NoidaMale
04GurgaonFemale

Query for inner Join Select *
from Salary Inner join Employee_detail
on salary.Emp_id = Employee_detail.Emp_id
Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_cityEmp_gender
0125000Software Eng01PuneMale
0230000Senior Software Eng02DelhiFemale
0325000Software Analyst03NoidaMale
------------------------------------------------------------------------------------------------------------------

Left Join with example

All the records of first table will appear & only matching record of second table will appear.
Syntax :

Select Column_Name1,Column_Name2
from Table1
Left join Table2 On table1.column_name = table2.column_name


consider the following two tables : 1.Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

2.Employee_detail:
Emp_idEmp_cityEmp_gender
01PuneMale
02DelhiFemale

Query for inner Join Select *
from Salary
Left join Employee_detail
on salary.Emp_id = Employee_detail.Emp_id
Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_cityEmp_gender
0125000Software Eng01PuneMale
0230000Senior Software Eng02DelhiFemale
0325000Software AnalystNULLNULLNULL
-----------------------------------------------------------------------------------------------------------------

Right Join with example

All the records of second table will appear & only matching record of first table will appear.
Syntax :

Select Column_Name1,Column_Name2
from Table1
Right join Table2 On table1.column_name = table2.column_name


consider the following two tables : 1.Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

2.Employee_detail:
Emp_idEmp_cityEmp_gender
01PuneMale
02DelhiFemale
03NoidaFemale
04GurgaonMale

Query for right Join Select *
from Salary
right join Employee_detail
on salary.Emp_id = Employee_detail.Emp_id
Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_cityEmp_gender
0125000Software Eng01PuneMale
0230000Senior Software Eng02DelhiFemale
0325000Software Analyst03NoidaFemale
NullNullNull04GurgaonMale

Tag: Inner Join with example, what is inner join?, Left join with example,What is left join?,Right join with example,what is right join?

Self Join and full outer join with example

Query for Self Join Table will be joined by itself, first we will give two different name of a table after that i will join these two table by inner join.
Syntax :

Select Column_Name1,Column_Name2
from Table1 as A
Inner join Table2 as B
On A.column_name = B.column_name


consider the following table : Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

Query of self join:

select *
from salary as a
inner join salary as b
on a.Emp_id = b.Emp_id

Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_salaryEmp_desig
0125000Software Eng0125000Software Eng
0230000Senior Software Eng0230000Senior Software Eng
0325000Software Analyst0325000Software Analyst
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Query for Full Outer Join It is the combination of left join and right join means all the reocrds from both the table will appear.
Syntax :

Select Column_Name1,Column_Name2
from Table1
Full outer join Table2
On Table1.column_name = Table2.column_name


consider the following table : Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst
0535000Software lead

Employee_detail:
Emp_idEmp_cityEmp_gender
01PuneMale
02DelhiFemale
03NoidaMale
04GurgaonFemale

Query of self join:

select *
from salary as a
inner join salary as b
on a.Emp_id = b.Emp_id

Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_cityEmp_gender
0125000Software Eng01PuneMale
0230000Senior Software Eng02DelhiFemale
0325000Software Analyst03NoidaMale
NullNullNull04GurgaonFemale
0535000Software leadNullNullNull

Tag:Outer Join with example, Self join with example

Cross join with example

What is cross Join ? Each row of first table will be joined with each row of second table.

Syntax :

Select table1.Column_Name1,table2.Column_Name1
from Table1
Cross join Table2


Consider the following two tables : Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

Employee_record:
Emp_idEmp_cityEmp_lastname
01noidachauhan
02gurgaonsingh

Query of cross join:

select *
from salary
cross join Employee_record

Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_cityEmp_lastname
0125000Software Eng01noidachauhan
0125000Software Eng02gurgaonsingh
0230000Senior Software Eng01noidachauhan
0230000Senior Software Eng02gurgaonsingh
0325000Software Analyst01noidachauhan
0325000Software Analyst02gurgaonsingh

Tag:Cross join in sql with example,What is cross join in sql?

Union and Intersection with Example

Sample Query for UNION Here two select statement result will be combined whether records are identical or not also first select statement result will appear and below it second select statement result will appear.
Syntax :

Select table1.Column_Name1,table1.Column_Name2
from Table1
UNION
select table2.Column_Name1, table2.column_name2
from Table2


Consider the following table : Salary:
Emp_idEmp_salaryEmp_name
0125000Chandan
0230000Adhiraj
0325000Akshat

Employee_record:
Emp_idEmp_cityEmp_name
201noidaSara
202gurgaonfatima

Query of Union:

select Emp_id, Emp_name
from salary
UNION
select Emp_id, Emp_name
from designation

Result :
Emp_idEmp_name
01Chandan
02Adhiraj
03Akshat
201Sara
202Fatima

--------------------------------------------------------------------------------------------------------------- Sample Query for Intersection Here two select statement result will be combined but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement also first select statement result will appear and below it second select statement result will appear.
Syntax :

Select table1.Column_Name1,table1.Column_Name2
from Table1
Intersection
select table2.Column_Name1, table2.column_name2
from Table2


Consider the following table : Salary:
Emp_idEmp_salaryEmp_name
0125000Chandan
0230000Adhiraj
0325000Akshat

Employee_record:
Emp_idEmp_cityEmp_name
01noidachandan
04gurgaonfatima

Query of Union:

select Emp_id, Emp_name
from salary
INTERSECT
select Emp_id, Emp_name
from designation

Result :
Emp_idEmp_name
01Chandan

Tag:UNION in SQl, UNION query with example, Intersection in SQL, Intersection with example, Difference between union and intersection

Query to join three table with example

Sample Query to join three table As like two table, three table can also be joined using INNER JOIN but they must have one column in common.
Syntax :

Select table1.Column_Name1,table1.Column_Name2
from Table1 as a
Inner Join Table2 as b
On a.column_name = b.column_name
Inner Join Table3 as c
On b.column_name = c.column_name


Consider the following tables : Salary:
Emp_idEmp_salaryEmp_name
0125000Chandan
0230000Adhiraj
0325000Akshat

Employee_record:
Emp_idEmp_cityEmp_name
201noidaSara
202gurgaonfatima

Employee_details:
Emp_idEmp_lastname
201Chauhan
202Singh

Query to join three tables :

select *
from salary as a
Inner Join Employee_record as b
On a.Emp_id = b.Emp_id
Inner Join Employee_details as c
on b.Emp_id = c.Emp_id


Result :
Emp_idEmp_salaryEmp_nameEmp_idEmp_cityEmp_nameEmp_idEmp_lastname
0125000Chandan201noidaSara201Chauhan
0230000Adhiraj202gurgaonfatima202Singh

Tag : How to Join three table?, SQL query to join three table with example

Important & Useful functions in SQL

SQL function with example

Date : It is used to get date.

Date function In Mysql :


CURDATE() : To get date only
Now() : To get date with time
Example :
Query :
Select Now()
Result :
Now()
2016-03-25 23:28:11

Date function In SQL

GETDATE()
Example :
Query :
Select GETDATE()
Result :
No Column
2016-03-25 23:28:11

Count function In SQL


Count function is used to count the number of rows in a table.
COUNT()
Example :
Consider the below table : Salary:
Emp_idEmp_salaryEmp_name
0125000Chandan
0230000Adhiraj
0325000Akshat

Query :
Select COUNT(Emp_id)
from Salary
Result :
Emp_id
3

Tag :sql Function to get date,SQL function to count Rows

Difference between Group by and Having Clause With Example

Difference between Group by and Having Clause

Group By:

The GROUP BY clause will gather all of the rows together that contain data in the specified column(s) and will allow aggregate functions to be performed on the one or more columns.
Aggregate functions are like SUM,COUNT,MIN,MAX and AVG.


Example :

Consider the folowing table of Appriasal


Table Name : Appraisal
Emp_idAmount
E012000
E024000
E035000
E026000
E012000


Query :

So if you want to get employee record who got total appraisal in one year then your query will be :
Select Emp_id, SUM(Amount)
from Appraisal
Group by Emp_id.

Result :
Emp_idAmount
E014000
E0210000
E035000

Having: The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.

Group functions cannot be used in where.To restrict row by group function having clause is used.

Example :

Consider the folowing table of Appriasal


Table Name : Appraisal
Emp_idAmount
E012000
E024000
E035000
E026000
E012000


Query :

So Now if you want to get employee record who got total appraisal in one year more than 5000 then your query will be :
Select Emp_id, SUM(Amount)
from Appraisal
Group by Emp_id Having SUM(Amount)>5000.

Result :

Emp_idAmount
E0210000
Thanks :)

Tag:Difference between Group by and Having clause, Group by in sql with example, Having clause in Sql with example