Database Testing - SQL - Sub-Query Operators: (ALL,ANY,SOME,EXISTS)

Sub-Query operators: (ALL,ANY,SOME,EXISTS)

43. List out the employees who earn more than every employee in department 30.

Select * from employee where salary > (Select Max(salary) from employee where department_id=30)

44. List out the employees who earn more than the lowest salary in department 30.

Select * from employee where salary > (Select min(salary) from employee where department_id=30)

45. Find out whose department has no employees.

Select * from department d where not exists (Select department_id from employee e where d.department_id=e.department_id)

By Using Joins

select employee_id, d.department_id, Name, Location_id from employee e right join department d on d.department_id=e.department_id where e.employee_id is null and d.department_id is not null

46. Find out which department does not have any employees.

Select * from department d where not exists (Select department_id from employee e where d.department_id=e.department_id)

Co-Related Sub Queries:

47. Find out the employees who earn greater than the average salary for their department.

Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)

Database Testing - SQL - Sub Queries Examples

Sub-Queries

34. Display the employee who got the maximum salary.

Select * from employee where salary=(select max(salary) from employee)

35. Display the employees who are working in Sales department

Select * from employee where department_id = (select department_id from department where name='SALES')

36. Display the employees who are working as "Clerk".

Select * from employee where job_id in (select job_id from job where function1='CLERK')

37. Display the employees who are working in "Boston"

Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group='Boston'))

38. Find out no.of employees working in 'Sales" department.

Select * from employee where department_id=(select department_id from department where name='SALES' group by department_id)

39. Update the employees salaries, who are working as Clerk on the basis of 10%.

Update employee set salary=(salary*10/100) where job_id=(select job_id from job where function1='CLERK')

40. Delete the employees who are working in accounting department.

delete from employee where department_id=(select department_id from department where name='ACCOUNTING')

41. Display the second highest salary drawing employee details.

Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))

42. Display the 3rd highest salary drawing employee details

Select * from employee a where 3 = (select COUNT(distinct(salary)) from employee b where b.salary >=a.salary)

Database Testing - SQL - Group By & Having Clause Examples

Group By & Having Clause:

22. How many employees who are working in different departments wise in the organization

Select department_id, count(*), from employee group by department_id

23. List out the department wise maximum salary, minimum salary, average salary of the employees

Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id

24. List out the job wise maximum salary, minimum salary, average salaries of the employees.

Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id

25. List out no. of employees joined in every month in ascending order

Select datepart(month, hiredate) 'Month' , count(*) 'No of Employees' from employee group by datepart(month, hiredate) order by datepart(month, hiredate)

26. List out the no.of employees for each month and year, in the ascending order based on the year, month.

Select datepart(Year, hiredate) 'Year', datepart(month, hiredate) 'month', count(*) 'No of employees' from employee group by datepart(year, hiredate), datepart(month, hiredate)

27. List out the department id having atleast four employees.

Select department_id, count(*) 'no of employees' from employee group by department_id having count(*)>=4

28. How many employees in January month.

Select datepart(month, hiredate) 'month', count(*) 'no of employees' from employee group by datepart(month, hiredate) having datepart(month, hiredate)=1 --here 1 refers to January

29. How many employees who are joined in February or August month.

Select datepart(month, hiredate) 'month', count(*) 'No of Employees' from employee group by datepart(month, hiredate) having datepart(month, hiredate) in (2,8)

30. How many employees who are joined in 1985.

Select datepart(Year, hiredate) 'Year', count(*) 'No of Employees' from employee group by datepart(Year, hiredate)having datepart(Year, hiredate) =1985

31. How many employees joined each month in 1985.

Select datepart(year,hiredate )'Year', datepart(month, hiredate) 'Month', count(*) 'No of employees' from employee where datepart(year, hiredate)=1985 group by datepart(year, hiredate),datepart(month, hiredate)

32. How many employees who are joined in May 1985.

Select datepart(Year, hiredate) 'Year', datepart(Month, hiredate) 'Month', count(*) 'No of employees' from employee where datepart(Year, hiredate)=1985 and datepart(month, hiredate)=5 group by datepart(Year, hiredate),datepart(Month, hiredate)

33. Which is the department id, having less than or equal to 3 employees joined in April 1985.

Select department_id, count(*) 'No. of employees' from employee where datepart(Year, hiredate)=1985 and datepart(Month, hiredate) = 4 group by datepart(Year, hiredate), datepart(Month, hiredate), department_id having count(*)<=3

Database Testing - SQL - Order by clause

Order By Clause:

18. List out the employee id, last name in ascending order based on the employee id.

Select employee_id, last_name from employee order by employee_id

19. List out the employee id, name in descending order based on salary column

Select employee_id, last_name, salary from employee order by salary desc

20. list out the employee details according to their last_name in ascending order and salaries in descending order

Select employee_id, last_name, salary from employee order by last_name, salary desc

21. list out the employee details according to their last_name in ascending order and then on department_id in descending order.

Select employee_id, last_name, salary from employee order by last_name, department_id desc

Database Testing - SQL- Where Condition Examples

Where Conditions:

8. List the details about "SMITH"

Select * from employee where last_name='SMITH';

9. List out the employees who are working in department 20

Select * from employee where department_id=20

10. List out the employees who are earning salary between 3000 and 4500

Select * from employee where salary between 3000 and 4500

11. List out the employees who are working in department 10 or 20

Select * from employee where department_id in (10,20)

12. Find out the employees who are not working in department 10 or 30

Select last_name, salary, commission, department_id from employee where department_id not in (10,30)

13. List out the employees whose name starts with "S"

Select * from employee where last_name like 'S%'

14. List out the employees whose name start with "S" and end with "H"

Select * from employee where last_name like 'S%H'

15. List out the employees whose name length is 4 and start with "S"

Select * from employee where last_name like 'S___'

16. List out the employees who are working in department 10 and draw the salaries more than 3500

Select * from employee where department_id=10 and salary>3500

17. list out the employees who are not receiving commission.

Select * from employee where commission is Null

Database Testing - SQL - Simple Queries Examples

Simple Queries:

1. List all the employee details

Select * from employee;

2. List all the department details

Select * from department;

3. List all job details

Select * from job;

4. List all the locations

Select * from location;

5. List out first name,last name,salary, commission for all employees

Select first_name, last_name, salary, commission from employee;

6. List out employee_id,last name,department id for all employees and rename employee id as "ID of the employee", last name as "Name of the employee", department id as "department ID"

Select employee_id " of the employee", last_name "name", department id as "department id" from employee;

7. List out the employees annual salary with their names only.

Select last_name, salary*12 "annual salary" from employee

Database Testing - SQL - Insertion of Values in the Tables

To insert values in to these tables we use Data Manipulation Language Command called INSERT which allows us to insert values in to the columns of the table. Any string inserted should be enclosed within ''

Syntax:

Insert [tablename] value (col1, col2, coln)

To insert values in the Location table

Insert location
values (122, 'New York'),
(123, 'Dallas'),
(124, 'Chicago'),
(167, 'Boston')

To insert values in the department table

Insert department
values (10, 'Accounts', 122),
(20, 'Research', 124),
(30, 'Sales', 123),
(40, 'Operations', 167)

To insert values in the job table

Insert job
values (667, 'Clerk'),
(668, 'staff'),
(669, 'analyst'),
(670, 'salesperson'),
(671, 'manager'),
(672, 'president')

To insert values in the employee table

insert employee
values (7369,'Smith', 'John', 'Q', 667,7902,'17-Dec-84', 800,'Null' , 20)

insert employee
values (7499,'Allen', 'Kevin', 'J', 670,7698,'20-Feb-85', 1600, 300, 30)

insert employee
values (7505,'Doyle', 'Jean', 'K', 671,7839,'04-Apr-85', 2850, 'Null', 30)

insert employee
values (7506,'Dennis', 'Lynn', 'S', 671,7839,'15-May-85', 2750, 'Null', 30)

insert employee
values (7507,'Baker', 'Leslie', 'D', 671,7839,'10-Jun-85', 2200, 'Null', 40)

insert employee
values (7521,'Wark', 'Cynthia', 'D', 670,7698,'22-Feb-85', 1250, 500, 30)

Database testing - SQL - Creation of Database and Tables for practice

First of all let us create a database with tables so that we can use all the examples given in the next posts for practice and this will help us to have a hold on SQL.

To create a database we use the Data Definition Language Command called CREATE

Syntax:

Create database [Name of the database]

Example:

create database Practice

To create different tables we use the DDL command Create and for tables we need to use the keyword table. We also need to specify the column names and the data type the column holds

Syntax:

Create table [Name of the table]
(
Column 1 Datatype,
Column 2 Datatype,
Column n Datatype
)

Example:

To create a location table for our Practice Database

Create table location
(
location_id int,
regional_group varchar(100)
)

To create a department table for our Practice Database

Create table department
(
department_id int,
Name varchar(100),
location_id int
)

To create a job table for our Practice Database

Create table job
(job_id int,
function1 varchar(50)
)

To create a employee table for our Practice Database

Create table employee
(
employee_id int,
Last_name varchar(100),
First_name varchar(100),
Middle_name varchar(100),
Job_id int,
manager_id int,
hiredate datetime,
salary int,
comm varchar(100) ,
department_id int
)

VB Script Examples for Begineers

Here you will find many VB Script Examples for easy understanding and also when you are perfect in this then you can use the skill to create scripts for QTP 9.2. Vb script on prime numbers, VB script on Functions etc are found here.

1. Click on Script to Print the Greatest of Three Integers

2. Click on Script to Print different Arithmetic Operations using Select Statement

3. Click on Script to Print Prime Numbers

4. Click on Script to Generate a Fibonacci Series

Write a VB Script to check whether a given number is prime or not?

We can use a For Next Loop or Do While Loop to check whether a number given is a prime number or not in VB Script. The challenge lies in writing a VB script without using any built in functions of VB Script.

Method 1

a = cint(Inputbox("Enter a number to check whether it is a prime number or not"))
count = 0
b = 1
Do while b<=a
aaaif a mod b = 0 Then
aaaaaacount = count +1
aaaEnd If
b=b+1
Loop
If count = 2 Then
aaamsgbox "The number "&a& " is a prime number"
Else
aaamsgbox "The number "&a& " is not a prime number"
End if

Method 2

val=Inputbox("Please enter any number")
valprime = 0
For i=2 to Int(val/2)
aaaIf val mod I = 0 Then
aaamsgbox "The number"&val&" is not a prime number"
aaavalprime=1
aaaExit for
End If
Next
If valprime=0 Then
aaamsgbox "The number"&val&" is a prime number"
End If