What is database testing?
Database testing is software testing that checks and validate the schema, tables, triggers of the database under test. It also checks data integrity and consistency. It is also helpful for API testing.
- validate existence of values in database table
- validate correctness of values in database table
- validate completeness of values in database table
select * from customers where contactLastName='kind'; -->validating kind
select * from offices where city='Paris';
..Select command basic - fetch data from tables:
select * from employees; --> * means all.. from employees
select * from orders;
select customerName, phone, city from customers;
Select data from table: data filtration by using where clause
select * from employees where employeeNumber='1286';
sql query not case sensetive..
select * from offices where city='Paris';
select * from customers;
select customerName, phone, city from customers;
select * from employees where employeeNumber='1286';
select * from employees where officeCode=6;
select * from employees where firstName='William';
select * from employees;
select lastName, firstName, email from employees where jobTitle='Sales Rep';
select * from employees where employeeNumber>1300;
select * from employees where employeeNumber<=1300;
select * from employees where firstName!='William';
select * from employees where officeCode!=6;
select data with filtration: where and or
select * from employees;
select * from employees where officeCode=1 or officeCode=4;
select * from employees where officeCode=1 or jobTitle='Sales Rep';
select * from employees where jobTitle!='Sales Rep'and employeeNumber>1100;
select * from employees where employeeNumber<1400 and employeeNumber>1100;
select data in List or Range | IN/BETWEEN
select * from customers;
select * from customers where country='France' or country='USA' or country='Gemany';
select * from customers where country In ('France', 'USA', 'Germany');
select * from customers where country NOT IN ('France', 'USA', 'Germany');
select * from customers where creditLimit > 10000 and creditLimit <90000;
select * from customers where creditLimit between 10000 and 90000;
select * from customers where creditLimit not between 10000 and 90000;
select data with sorting options:
select * from employees order by officeCode;
select * from employees order by firstName desc;
select * from employees where jobTitle='Sales Rep' order by lastName desc;
select * from customers;
select * from customers order by country desc, state asc;
select data with pattern matching
select * from customers where customerName like '_A%'; --> second character is A
select * from customers;
select * from customers where customerName like '_A';
select * from customers where customerName like 'c%o_';
select * from customers where contactFirstName like '_e%e_';
select * from customers where contactFirstName like '_____';
select * from customers where contactFirstName like '%_____%';
select unique data from table
select * from employees ;
select officeCode from employees;
select distinct officeCode from employees; # no repeat value, unique value
select jobTitle from employees;
select distinct jobTitle from employees;
describe employees;
select data with group functions
select * from customers;
select min(creditLimit) as "Minium Credit Value" from customers; #describe here ""
select max(creditLimit) as "Maximum Credit Value" from customers;
select avg(creditLimit) as "Average Credit Value" from customers;
select sum(creditLimit) as "Sum of Credit Value" from customers;
select count(creditLimit) as "Count of Credit Value" from customers;
select count(*) from employees;
select count(*) from customers;
select & group data group by having
select * from employees;
select jobTitle, count(*) as "Number of Employees" from employees group by jobTitle;
select officeCode, count(*) as "Total Employees" from employees group by officeCode;
select * from customers;
select country, count(*) as "Number of Customers " from customers group by country;
select country, max(creditLimit) as "Max Credit Limit " from customers group by country;
select country, avg(creditLimit) as "Avg Credit Limit " from customers group by country;
filter groups using having
select country, avg(creditLimit) as "Avg Credit Limit " from customers group by country;
select * from customers where creditLimit > 50000;
select country, avg(creditLimit) as "Avg Credit Limit " from customers where creditLimit>50000 group by country; # "where" is using before making group
select country, avg(creditLimit) as "Avg Credit Limit " from customers group by country having avg(creditLimit)>80000; # "having" is using after making group
select country, avg(creditLimit) as "Avg Credit Limit " from customers where creditLimit>50000 group by country having avg(creditLimit)>80000;
sub query - query inside a query
select * from customers;
select avg(creditLimit) from customers;
select * from customers where creditLimit > 67659.016393; # merging both queries
select * from customers where creditLimit > (select avg(creditLimit) from customers);
select * from payments;
select avg(amount) from payments;
select * from payments where amount > (select avg(amount) from payments); #subquery
select customerNumeber from payments where amount < (select avg(amount) from payments); # not working subquery check again
delete data
create table customer1 as select * from customers; # creates copy of customers
create table employees1 as select * from employees;
create table offices1 as select * from offices;
select * from employees1;
delete from employees1 where jobTitle='Sales Rep';
delete from employees1; # employees1 data structure is still in database but data is not there
select * from customer1;
truncate table customer1; # delete data but still structure is there
select * from offices1;
drop table offices1;
drop table customer1;
drop table employees1;
joins
cross joins/Cartesian join
select * from offices;
select * from productlines;
select * from offices cross join productlines; # cross join (multiply both)
inner join | equil join| simple join
select * from employees;
select * from offices;
select employeeNumber, firstName, lastName, email, city, state, employees.officeCode from employees
inner join offices on employees.officeCode = offices.officeCode; # officeCode is pulling from employees table
right join | right outer join
select * from employees;
select * from offices;
select employeeNumber, firstName, lastName, email, city, state from employees
left join offices on employees.officeCode = offices.officeCode;
select * from employees;
select * from offices;
select employeeNumber, firstName, lastName, email, city, state, country from employees
right join offices on employees.officeCode = offices.officeCode;
self join
select * from employees;
select A.firstName as "Emp Name", B.firstName as "Manager Name" from employees A inner join employees B
on A.reportsTo = B.employeeNumber;
create table
create table My_Office_Employees(
Id int(10),
EmployeeName varchar(30),
DOJ date); # create data
select * from my_office_employees;
desc my_office_employees; # describe table
create table My_Office_Employees_pk(
Id int(10) primary key, # making primary key
EmployeeName varchar(30),
DOJ date); # create data
desc My_Office_Employees_pk;
create table My_Office_Employees_nl(
Id int(10) primary key, # making primary key
EmployeeName varchar(30) not null, # always some value
DOJ date); # create data
desc My_Office_Employees_nl;
create table My_Office_Employees_df(
Id int(10) primary key, # making primary key
EmployeeName varchar(30) default "TestName", # if name is not given, write testname
DOJ date); # create data
desc My_Office_Employees_df;
insert data
desc my_office_employees; # check table
insert into my_office_employees values(1,'John', '2010-03-01'); # insert data
insert into my_office_employees (Id, EmployeeName, DOJ) values(2,'Rohn', '2013-03-01'); # or insert data in particular table
insert into my_office_employees (EmployeeName, ID, DOJ) values('Sohn', 3, '2013-03-01'); # or insert data as my order
insert into my_office_employees (EmployeeName, ID ) values('Ram', 4); # or insert data only partial
select * from my_office_employees; # check table
describe my_office_employees_pk;
insert into my_office_employees_pk values(1,'John', '2010-03-01'); # insert data
select * from my_office_employees_pk; # check table
describe my_office_employees_df;
insert into my_office_employees_df (EmployeeName,DOJ, id) values('Rak', '2014-03-01',1); # or insert data in particular table
insert into my_office_employees_df (DOJ, id) values('2013-03-01',3); # default Employee Name is TestName
select * from my_office_employees_df; # check table
update data
select * from my_office_employees;
update my_office_employees set employeeName='kelly' where id=2; # conditonally
update my_office_employees set ID=1; # withou condition , setiing employee id is 1 for all
update my_office_employees set DOJ='2019-03-09' where id=2 or 1;
desc my_office_employees_pk;
insert into my_office_employees_pk values (5, 'Test', '2019-03-09');
insert into my_office_employees_pk values (2, 'rest', '2014-03-09');
insert into my_office_employees_pk values (5, 'zest', '2015-03-09');
insert into my_office_employees_pk values (9, 'kest', '2010-03-09');
update my_office_employees_pk set id=1 where EmployeeName='Kera';
select * from my_office_employees_pk;
views
select * from employees;
create view Emp_View as select * from employees; # created virtual view data , it does not hold its own data
select * from Emp_View;
select * from customers;
create view emp_personal_data as select customerName, phone, addressLine1, city from customers;# virtual table
select * from emp_personal_data;
create view employee_count as select country, count(*) as "customer count" from customers group by country;
select * from employee_count;
drop view employee_count; # delete view
indexes
select * from customers;
select * from customers where country = 'USA';
create index customer_country on customers(country);
alter table customers drop index customer_country;
CONSTRAINS: not null
create table my_emp_nm(
id varchar(10),
emp_name varchar (20) not null,# name should not be null
salary int(10));
desc my_emp_nm;
insert into my_emp_nm values (10, 'testing', 2000);
insert into my_emp_nm values (null, 'testing', null);
insert into my_emp_nm values (null, null, null); # emp name null is not allowed
select * from my_emp_nm;
unique key
create table employee_unique1(
id int(5) unique,
emp_name varchar(20),
passport_name varchar(20)unique);
insert into employee_unique1 values (1, 'ramesh', 'abcd');
insert into employee_unique1 values (2, 'lamesh', 'abcd'); # dublicate password can not created
desc employee_unique1;
select * from employee_unique1;
drop table employee_unique; # delete table
primary key
create table new_employees_pk(
id int(5) primary key,
emp_name varchar(20),
salary int(5));
desc new_employees_pk;
insert into new_employees_pk values (1, 'ram', 100);
insert into new_employees_pk values (1, 'lam', 1200); # can not create dublicate of primary key
insert into new_employees_pk values (null, 'mam', 100); # can not null pk
select * from new_employees_pk;
create table new_employees_pk2(
id int(5),
emp_name varchar(20),
salary int(5),
primary key(id));
insert into new_employees_pk2 values (1, 'ram', 100);
insert into new_employees_pk2 values (1, 'lam', 1200); # can not create dublicate of primary key
insert into new_employees_pk2 values (null, 'mam', 100); # can not null pk
desc new_employees_pk2;
select * from new_employees_pk2;
create table new_employees_pk3(
id int(5),
emp_name varchar(20),
salary int(5),
primary key(id, emp_name)); # composite key ( more than two primary key present)
desc new_employees_pk3;
insert into new_employees_pk3 values (1, 'ram', 100);
insert into new_employees_pk3 values (1, 'ram', 1200); # can not create dublicate of primary key, id and emp_name are primary key
insert into new_employees_pk3 values (null, null, 100); # primary key can not null
select * from new_employees_pk3;
difference between unique and primary key
References:
No comments:
Post a Comment