Selenium Practice Page

This WebPage is designed for selenium practice Text :

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

Link:Software Testing Stuffs

Chandan	Singh	Chauhan
1123	6473	6474
SQA	Usefull_things	Testing stuff
Selenium test	Test1	Test2

First Name :

Last Name :



Male
Female



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


Monday Tuesday wednesday Thursday Friday Saturday Sunday

Link 1 Link 2 Link 3

Drag the way2testing image into the rectangle:

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?

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

Share

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 :

> https://tools.verifyemailaddress.io

> mailtester.com

> www.email-validator.net/

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_id	Emp_name	Emp_reg	Emp_salary
01	chandan	E_01	25000
02	adhiraj	E_02	30000
03	raghu	E_03	35000
04	Alax	E_04	40000

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_id	Emp_name	Emp_reg	Emp_salary
01	chandan	E_01	25000
02	adhiraj	E_02	30000
03	raghu	E_03	35000
04	Alax	E_04	40000

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_name	Emp_salary
chandan	25000
adhiraj	30000
raghu	35000
Alax	40000

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_name	Emp_salary
Alax	40000
raghu	35000
adhiraj	30000
chandan	25000

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_id	Emp_name	Emp_reg	Emp_salary
01	chandan	E_01	25000
02	adhiraj	E_02	30000
03	raghu	E_03	35000
04	Alax	E_04	40000

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_id	Emp_name	Emp_reg	Emp_salary
01	chandan	E_01	25000
02	adhiraj	E_02	32000
03	raghu	E_03	35000
04	Alax	E_04	40000

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_id	Emp_name	Emp_reg	Emp_salary
01	chandan	E_01	25000
02	adhiraj	E_02	30000
03	raghu	E_03	35000
04	raghunath	E_04	36000
05	Alax	E_04	40000

% wildcard:

Select * from Salary where Emp_name like "%ndan"

Result: Records starting from any character but followed by 'ndan' will appear Table: Salary

Emp_id	Emp_name	Emp_reg	Emp_salary
01	chandan	E_01	25000

wildcard %:

Select * from Salary where Emp_name like "ra%"

Result: All the records staring from 'ra' character will appear Table: Salary

Emp_id	Emp_name	Emp_reg	Emp_salary
03	raghu	E_03	35000
04	raghunath	E_04	36000

_wildcard:

Select * from Salary where Emp_name like "-raj"

Result: Table: Salary
<

Emp_id	Emp_name	Emp_reg	Emp_salary
02	adhiraj	E_02	30000
05	Alax	E_04	40000

[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_id	Emp_name	Emp_reg	Emp_salary
01	chandan	E_01	25000
02	adhiraj	E_02	30000
05	Alax	E_04	40000

[!]%wildcard :

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

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

Emp_id	Emp_name	Emp_reg	Emp_salary
01	chandan	E_01	25000

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_id	Emp_name	Emp_reg	Emp_salary
01	chandan	E_01	25000
02	adhiraj	E_02	30000
03	raghu	E_03	35000
04	raghunath	E_04	36000
05	Alax	E_05	40000

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_id	Emp_name	Emp_reg	Emp_salary	city
01	chandan	E_01	25000	Null
02	adhiraj	E_02	30000	Null
03	raghu	E_03	35000	Null
04	raghunath	E_04	36000	Null
05	Alax	E_05	40000	Null

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_id	Emp_name	Emp_reg	Emp_salary	city
01	chandan	E_01	25000	Noida
02	adhiraj	E_02	30000	noida
03	raghu	E_03	35000	Noida
04	raghunath	E_04	36000	Noida
05	Alax	E_05	40000	Noida

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_id	Emp_name	Emp_reg	Emp_salary	city
02	adhiraj	E_02	30000	noida

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_id	Emp_name	Emp_reg	Emp_salary	city
01	chandan	E_01	25000	Noida
02	adhiraj	E_02	30000	noida

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_id	Emp_salary	Emp_desig
01	25000	Software Eng
02	30000	Senior Software Eng
03	25000	Software Analyst

Now it is required to delete a row from Table then

Query of DELETE:

Delete
from Salary
Where Emp_id = 01
Result:

Emp_id	Emp_salary	Emp_heading
02	30000	Senior Software Eng
03	25000	Software 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_id	Emp_salary	Emp_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_id	Emp_Salary
01	25000
02	30000
03	35000

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_id	Emp_Salary
01	25000
02	30000
03	35000

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_id	Emp_salary	Emp_desig
01	25000	Software Eng
02	30000	Senior Software Eng
03	25000	Software Analyst

Employee_detail:

Emp_id	Emp_city	Emp_gender
01	Pune	Male
02	Delhi	Female
03	Noida	Male
04	Gurgaon	Female

Query for inner Join Select *
from Salary Inner join Employee_detail
on salary.Emp_id = Employee_detail.Emp_id
Result :

Emp_id	Emp_salary	Emp_desig	Emp_id	Emp_city	Emp_gender
01	25000	Software Eng	01	Pune	Male
02	30000	Senior Software Eng	02	Delhi	Female
03	25000	Software Analyst	03	Noida	Male

------------------------------------------------------------------------------------------------------------------

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_id	Emp_salary	Emp_desig
01	25000	Software Eng
02	30000	Senior Software Eng
03	25000	Software Analyst

2.Employee_detail:

Emp_id	Emp_city	Emp_gender
01	Pune	Male
02	Delhi	Female

Query for inner Join Select *
from Salary
Left join Employee_detail
on salary.Emp_id = Employee_detail.Emp_id
Result :

Emp_id	Emp_salary	Emp_desig	Emp_id	Emp_city	Emp_gender
01	25000	Software Eng	01	Pune	Male
02	30000	Senior Software Eng	02	Delhi	Female
03	25000	Software Analyst	NULL	NULL	NULL

-----------------------------------------------------------------------------------------------------------------

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_id	Emp_salary	Emp_desig
01	25000	Software Eng
02	30000	Senior Software Eng
03	25000	Software Analyst

2.Employee_detail:

Emp_id	Emp_city	Emp_gender
01	Pune	Male
02	Delhi	Female
03	Noida	Female
04	Gurgaon	Male

Query for right Join Select *
from Salary
right join Employee_detail
on salary.Emp_id = Employee_detail.Emp_id
Result :

Emp_id	Emp_salary	Emp_desig	Emp_id	Emp_city	Emp_gender
01	25000	Software Eng	01	Pune	Male
02	30000	Senior Software Eng	02	Delhi	Female
03	25000	Software Analyst	03	Noida	Female
Null	Null	Null	04	Gurgaon	Male

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_id	Emp_salary	Emp_desig
01	25000	Software Eng
02	30000	Senior Software Eng
03	25000	Software Analyst

Query of self join:

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

Result :

Emp_id	Emp_salary	Emp_desig	Emp_id	Emp_salary	Emp_desig
01	25000	Software Eng	01	25000	Software Eng
02	30000	Senior Software Eng	02	30000	Senior Software Eng
03	25000	Software Analyst	03	25000	Software 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_id	Emp_salary	Emp_desig
01	25000	Software Eng
02	30000	Senior Software Eng
03	25000	Software Analyst
05	35000	Software lead

Employee_detail:

Emp_id	Emp_city	Emp_gender
01	Pune	Male
02	Delhi	Female
03	Noida	Male
04	Gurgaon	Female

Query of self join:

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

Result :

Emp_id	Emp_salary	Emp_desig	Emp_id	Emp_city	Emp_gender
01	25000	Software Eng	01	Pune	Male
02	30000	Senior Software Eng	02	Delhi	Female
03	25000	Software Analyst	03	Noida	Male
Null	Null	Null	04	Gurgaon	Female
05	35000	Software lead	Null	Null	Null

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_id	Emp_salary	Emp_desig
01	25000	Software Eng
02	30000	Senior Software Eng
03	25000	Software Analyst

Employee_record:

Emp_id	Emp_city	Emp_lastname
01	noida	chauhan
02	gurgaon	singh

Query of cross join:

select *
from salary
cross join Employee_record

Result :

Emp_id	Emp_salary	Emp_desig	Emp_id	Emp_city	Emp_lastname
01	25000	Software Eng	01	noida	chauhan
01	25000	Software Eng	02	gurgaon	singh
02	30000	Senior Software Eng	01	noida	chauhan
02	30000	Senior Software Eng	02	gurgaon	singh
03	25000	Software Analyst	01	noida	chauhan
03	25000	Software Analyst	02	gurgaon	singh

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_id	Emp_salary	Emp_name
01	25000	Chandan
02	30000	Adhiraj
03	25000	Akshat

Employee_record:

Emp_id	Emp_city	Emp_name
201	noida	Sara
202	gurgaon	fatima

Query of Union:

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

Result :

Emp_id	Emp_name
01	Chandan
02	Adhiraj
03	Akshat
201	Sara
202	Fatima

--------------------------------------------------------------------------------------------------------------- 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_id	Emp_salary	Emp_name
01	25000	Chandan
02	30000	Adhiraj
03	25000	Akshat

Employee_record:

Emp_id	Emp_city	Emp_name
01	noida	chandan
04	gurgaon	fatima

Query of Union:

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

Result :

Emp_id	Emp_name
01	Chandan

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_id	Emp_salary	Emp_name
01	25000	Chandan
02	30000	Adhiraj
03	25000	Akshat

Employee_record:

Emp_id	Emp_city	Emp_name
201	noida	Sara
202	gurgaon	fatima

Employee_details:

Emp_id	Emp_lastname
201	Chauhan
202	Singh

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_id	Emp_salary	Emp_name	Emp_id	Emp_city	Emp_name	Emp_id	Emp_lastname
01	25000	Chandan	201	noida	Sara	201	Chauhan
02	30000	Adhiraj	202	gurgaon	fatima	202	Singh

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_id	Emp_salary	Emp_name
01	25000	Chandan
02	30000	Adhiraj
03	25000	Akshat

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_id	Amount
E01	2000
E02	4000
E03	5000
E02	6000
E01	2000

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_id	Amount
E01	4000
E02	10000
E03	5000

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_id	Amount
E01	2000
E02	4000
E03	5000
E02	6000
E01	2000

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_id	Amount
E02	10000

Thanks :)

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