MySQL DataBase Queries for Selenium Automation Testing Interviews

Hi Guys ,

While working as an Automation Test Engineer  or in any Automation Testing Interviews,we have to deal  with database most of the time.
In this post I am sharing frequently asked MySQl Queries which will be very  usefull in Interview.


1-Create MySQL DataBase

create database Devtest;


2-Select database:

We need to select Database before using it.

use Devtest;


3-Create Table:

Table 1: Employees:

create table Employees(Employee_id  varchar(10) not null, First_name varchar(20),Last_name varchar(20), Salary int(10), Joining_date datetime , Department varchar(20) );

Table 2: incentives:

create table incentives(Employee_id  varchar(10) not null, First_name varchar(20),Last_name varchar(20), Salary int(10), Joining_date datetime , Department varchar(20) );


4-Insert Record to Tables:

Insert Record to Table1:

Insert into Employees (Employee_id,First_name,Last_name,Salary,Joining_Date,Department) 
Values(1,"John","Abraham",100000,"13-01-01 12:00:00","Banking"),
(2,"Michael","clarke",80000,"13-01-01 12:00:00","Insurance"),
(3,"Virat","Kohli",70000,"13-02-01 12:00:00","Banking"),
(4,"Anil","Kumble",60000,"13-02-01 12:00:00","Insurance"),
(5,"Bryan","Lara",65000,"13-02-01 12:00:00","Insurance"),
(6,"Amit","Kumar",75000,"13-01-01 12:00:00","Services");

Insert Record to Table2:

Insert into Incentive (Employee_ref_id,Incentive_date,Incentive_amount) 
Values(1,"13-02-01 12:00:00",5000),
(2,"13-02-01 12:00:00",3000),
(1,"13-01-01 12:00:00",4500),
(2,"13-01-01 12:00:00",3500),
(3,"13-02-01 12:00:00",4000);


5- Get All Records from Table

Table 1: Employees

select * from Employees ;

Table 2: Incentive

select * from Incentive ;

6-Get first name ,last name from Employees table

Select First_name,Last_name from Employees;

7-Get First_Name from Employees table in upper case

Select upper(First_name) from Employees;

8-Get First_Name from Employees table in lower case

Select lower(First_name) from employee;

9-Get unique DEPARTMENT from employee table

Select distinct department from Employees;

10- Get FIRST_NAME from Employees table after removing white spaces from right side

select rtrim(First_name) from Employees;

11-Get FIRST_NAME from Employees table after removing white spaces from Left side

select ltrim(First_name) from Employees;

12-Get first 3 characters of FIRST_NAME from Employees

select substring(First_name,1,3) from Employees;

13-Get length of FIRST_NAME from Employees table

select length(First_name) from Employees;

14-Get First_Name from Employees table after replacing 'o' with '$'

select replace(First_name,'o','$') from Employees;

15-Get First_Name and Last_Name as single column from Employees table separated by space.

select concat(first_name," ",last_name) as Full_name from Employees ;

16-Get FIRST_NAME ,Joining year , Joining Month and Joining Date from Employees table

select First_name,year(Joining_date),month(joining_date),day(joining_date) from Employees ;

17-Get all employee details from the Employees table order by First_Name Ascending

select first_name from Employees  order by First_name asc;

18-Get all employee details from the Employees table order by First_Name Descending

select first_name from Employees  order by first_name desc;

19-Get Employees details from employee table whose Employees name are “John” and “Roy”

select * from Employees where first_name not in ("john","roy");

20-Get employee details from Employees table whose first name starts with 'b'

select * from Employees  where First_name like "b%";

21-Get employee details from Employees table whose first name contains 'r'

select * from Employees where first_name like "%r%";

22-Get employee details from Employees table whose Salary greater than 70000

select * from Employees  where salary>70000;

23-Get employee details from Employees table whose Salary between 70000 and 90000

select * from Employees  where salary between 70000 and 90000;

24-Get employee details from Employees table whose name is 'John' and 'Michael

select * from Employees  where first_name in("john","michael");

25-Get employee details from Employees table whose joining year is “2013”

select * from Employees where year(Joining_date)="2013";

26-Get employee details from Employees table whose joining month is “January”

select * from Employees  where month(joining_date)="1";

27-Get difference between JOINING_DATE and INCENTIVE_DATE from Employees and incentives table

select first_name,JOINING_DATE - INCENTIVE_DATE  from Employees a inner join incentive b on a.employee_id=b.Employee_ref_id;

28-Get department,total salary with respect to a department from Employees table

select department ,sum(salary) as total_salary from Employees  group by department;

If you like my post , please don't forget to follow my blog.

Thanks for viewing this Post.