Skip to main content

SQL Queries for Software Testing

SQL Queries

 Employee Tables:

LOCATION
Location_ID Regional_Group
122 CHENNAI
123 DELHI
124 HYDERABAD
167 MUMBAI

 

DEPARTMENT
DEPT_ID Name Location_ID
10 ACCOUNTING 122
20 RESEARCH 124
30 SALES 123
40 OPERATIONS 167

 

JOB
Job_ID Function
667 CLERK
668 STAFF
669 ANALYST
670 SALESPERSON
671 MANAGER
672 PRESIDENT

 

EMP

EMP_ID L_NAME F_NAME M_NAME JOB_ID MANAGER_ID HIREDATE SALARY COMM DEPT_ID
7369 RAO PRASAD Q 667 7902 17-DEC-84 800 NULL 20
7499 RANI SUDHA J 670 7698 20-FEB-85 1600 300 30
7505 KRISHNA MURALI K 671 7839 04-APR-85 2850 NULL 30
7506 SHARMA MANI S 671 7839 15-MAY-85 2750 NULL 30
7507 DEV RAM D 671 7839 10-JUN-85 2200 NULL 40
7521 GOUD MAHA D 670 7698 22-FEB-85 1250 500 30

Queries based on the above tables:

Simple Queries:

1. List all the EMP details
2. List all the department details
3. List all job details
4. List all the locations
5. List out first name,L name,salary, commission for all EMPs
6. List out EMP_id,L name,department id for all EMPs and rename EMP id as “ID of the EMP”, L name as “Name of the EMP”, department id as “department ID”
7. List out the EMPs anuual salary with their names only.
Where Conditions:

8. List the details about “RAO”
9. List out the EMPs who are working in department 20
10. List out the EMPs who are earning salary between 3000 and 4500
11. List out the EMPs who are working in department 10 or 20
12. Find out the EMPs who are not working in department 10 or 30
13. List out the EMPs whose name starts with “S”
14. List out the EMPs whose name start with “S” and end with “H”
15. List out the EMPs whose name length is 4 and start with “S”
16. List out the EMPs who are working in department 10 and draw the salaries more than 3500
17. list out the EMPs who are not receiving commission.

Order By Clause:

18. List out the EMP id, L name in ascending order based on the EMP id.
19. List out the EMP id, name in descending order based on salary column
20. list out the EMP details according to their L_name in ascending order and salaries in descending order
21. list out the EMP details according to their L_name in ascending order and then on DEPT_ID in descending order.
Group By & Having Clause:

22. How many EMPs who are working in different departments wise in the organization
23. List out the department wise maximum salary, minimum salary, average salary of the EMPs
24. List out the job wise maximum salary, minimum salary, average salaries of the EMPs.
25. List out the no.of EMPs joined in every month in ascending order.
26. List out the no.of EMPs for each month and year, in the ascending order based on the year, month.
27. List out the department id having atleast four EMPs.
28. How many EMPs in January month.
29. How many EMPs who are joined in January or September month.
30. How many EMPs who are joined in 1985.
31. How many EMPs joined each month in 1985.
32. How many EMPs who are joined in March 1985.
33. Which is the department id, having greater than or equal to 3 EMPs joined in April 1985.
Sub-Queries

34. Display the EMP who got the maximum salary.
35. Display the EMPs who are working in Sales department
36. Display the EMPs who are working as “Clerk”.
37. Display the EMPs who are working in “CHENNAI”
38. Find out no.of EMPs working in “Sales” department.
39. Update the EMPs salaries, who are working as Clerk on the basis of 10%.
40. Delete the EMPs who are working in accounting department.
41. Display the second highest salary drawing EMP details.
42. Display the Nth highest salary drawing EMP details
Sub-Query operators: (ALL,ANY,SOME,EXISTS)

43. List out the EMPs who earn more than every EMP in department 30.
44. List out the EMPs who earn more than the lowest salary in department 30.
45. Find out whose department has not EMPs.
46. Find out which department does not have any EMPs.

49.Display EMPs with their designations (jobs)

50.Display the EMPs with their department name and regional groups.

51.How many EMPs who are working in different departments and display with department name.

52.How many EMPs who are working in sales department.

53.Which is the department having greater than or equal to 5 EMPs and display the department names in ascending order.

54.How many jobs in the organization with designations.

55.How many EMPs working in “CHENNAI”.

 

Non – Equi Join:

56.Display EMP details with salary grades.

57.List out the no. of EMPs on grade wise.

58.Display the employ salary grades and no. of EMPs between 2000 to 5000 range of salary.

 

Self Join:

59.Display the EMP details with their manager names.

60.Display the EMP details who earn more than their managers salaries.

61.Show the no. of EMPs working under every manager.

Outer Join:

61.Display EMP details with all departments.

62.Display all EMPs in sales or operation departments.

Set Operators:

63.List out the distinct jobs in Sales and Accounting Departments.

64.List out the ALL jobs in Sales and Accounting Departments.

65.List out the common jobs in Research and Accounting Departments in ascending order.


Answers 

  1. SQL > Select * from EMP;
  2. SQL > Select * from department;
  3. SQL > Select * from job;
  4. SQL > Select * from loc;
  5. SQL > Select F_NAME, L_name, salary, commission from EMP;
  6. SQL > Select EMP_id “id of the EMP”, L_name “name”, department id as “department id” from EMP;
  7. SQL > Select L_name, salary*12 “annual salary” from EMP
  8. SQL > Select * from EMP where L_name=’RAO’;
  9. SQL > Select * from EMP where DEPT_ID=20
  10. SQL > Select * from EMP where salary between 3000 and 4500
  11. SQL > Select * from EMP where DEPT_ID in (20,30)
  12. SQL > Select L_name, salary, commission, DEPT_ID from EMP where DEPT_ID not in (10,30)
  13. SQL > Select * from EMP where L_name like ‘S%’
  14. SQL > Select * from EMP where L_name like ‘S%H’
  15. SQL > Select * from EMP where L_name like ‘S___’
  16. SQL > Select * from EMP where DEPT_ID=10 and salary>3500
  17. SQL > Select * from EMP where commission is Null
  18. SQL > Select EMP_id, L_name from EMP order by EMP_id
  19. SQL > Select EMP_id, L_name, salary from EMP order by salary desc
  20. SQL > Select EMP_id, L_name, salary from EMP order by L_name, salary desc
  21. SQL > Select EMP_id, L_name, salary from EMP order by L_name, DEPT_ID desc
  22. SQL > Select DEPT_ID, count(*), from EMP group by DEPT_ID
  23. SQL > Select DEPT_ID, count(*), max(salary), min(salary), avg(salary) from EMP group by DEPT_ID
  24. SQL > Select job_id, count(*), max(salary), min(salary), avg(salary) from EMP group by job_id
  25. SQL > Select to_char(hire_date,’month’)month, count(*) from EMP group by to_char(hire_date,’month’) order by month
  26. SQL > Select to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No. of EMPs” from EMP group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’)
  27. SQL > Select DEPT_ID, count(*) from EMP group by DEPT_ID having count(*)>=4
  28. SQL > Select to_char(hire_date,’mon’) month, count(*) from EMP group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’)=’jan’
  29. SQL > Select to_char(hire_date,’mon’) month, count(*) from EMP group by to_char(hire_date,’mon’) having to_char(hire_date,’mon’) in (‘jan’,’sep’)
  30. SQL > Select to_char(hire_date,’yyyy’) Year, count(*) from EMP group by to_char(hire_date,’yyyy’) having to_char(hire_date,’yyyy’)=1985
  31. SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of EMPs” from EMP where to_char(hire_date,’yyyy’)=1985 group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)
  32. SQL > Select to_char(hire_date,’yyyy’)Year, to_char(hire_date,’mon’) Month, count(*) “No. of EMPs” from EMP where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’mar’ group by to_char(hire_date,’yyyy’),to_char(hire_date,’mon’)
  33. SQL > Select DEPT_ID, count(*) “No. of EMPs” from EMP where to_char(hire_date,’yyyy’)=1985 and to_char(hire_date,’mon’)=’apr’ group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’), DEPT_ID having count(*)>=3
  34. SQL > Select * from EMP where salary=(select max(salary) from EMP)
  35. SQL > Select * from EMP where DEPT_ID IN (select DEPT_ID from department where name=’SALES’)
  36. SQL > Select * from EMP where job_id in (select job_id from job where function=’CLERK’
  37. SQL > Select * from EMP where DEPT_ID=(select DEPT_ID from department where location_id=(select location_id from location where regional_group=’CHENNAI’))
  38. SQL > Select * from EMP where DEPT_ID=(select DEPT_ID from department where name=’SALES’ group by DEPT_ID)
  39. SQL > Update EMP set salary=salary*10/100 wehre job_id=(select job_id from job where function=’CLERK’)
  40. SQL > delete from EMP where DEPT_ID=(select DEPT_ID from department where name=’ACCOUNTING’)
  41. SQL > Select * from EMP where salary=(select max(salary) from EMP where salary <(select max(salary) from EMP))
  42. SQL > Select distinct e.salary from EMP where & no-1=(select count(distinct salary) from EMP where sal>e.salary)
  43. SQL > Select * from EMP where salary > all (Select salary from EMP where DEPT_ID=30)
  44. SQL > Select * from EMP where salary > any (Select salary from EMP where DEPT_ID=30)
  45. SQL > Select EMP_id, L_name, DEPT_ID from EMP e where not exists (select DEPT_ID from department d where d.DEPT_ID=e.DEPT_ID)
  46. SQL > Select name from department d where not exists (select L_name from EMP e where d.DEPT_ID=e.DEPT_ID)
  47. SQL > Select EMP_id, L_name, salary, DEPT_ID from EMP e where salary > (select avg(salary) from EMP where DEPT_ID=e.DEPT_ID)
  48. SQL > Select EMP_id, L_name, name from EMP e, department d where e.DEPT_ID=d.DEPT_ID
  49. SQL > Select EMP_id, L_name, function from EMP e, job j where e.job_id=j.job_id
  50. SQL > Select EMP_id, L_name, name, regional_group from EMP e, department d, location l where e.DEPT_ID=d.DEPT_ID and d.location_id=l.location_id
  51. SQL > Select name, count(*) from EMP e, department d where d.DEPT_ID=e.DEPT_ID group by name
  52. SQL > Select name, count(*) from EMP e, department d where d.DEPT_ID=e.DEPT_ID group by name having name=’SALES’
  53. SQL > Select name, count(*) from EMP e, department d where d.DEPT_ID=e.DEPT_ID group by name having count (*)>=5 order by name
  54. SQL > Select function, count(*) from EMP e, job j where j.job_id=e.job_id group by function
  55. SQL > Select regional_group, count(*) from EMP e, department d, location l where e.DEPT_ID=d.DEPT_ID and d.location_id=l.location_id and regional_group=’CHENNAI’ group by regional_group
  56. SQL > Select EMP_id, L_name, grade_id from EMP e, salary_grade s where salary between lower_bound and upper_bound order by L_name
  57. SQL > Select grade_id, count(*) from EMP e, salary_grade s where salary between lower_bound and upper_bound group by grade_id order by grade_id desc
  58. SQL > Select grade_id, count(*) from EMP e, salary_grade s where salary between lower_bound and upper_bound and lower_bound>=2000 and lower_bound<=5000 group by grade_id order by grade_id desc
  59. SQL > Select e.L_name emp_name, m.L_name, mgr_name from EMP e, EMP m where e.manager_id=m.EMP_id
  60. SQL > Select e.L_name emp_name, e.salary emp_salary, m.L_name, mgr_name, m.salary mgr_salary from EMP e, EMP m where e.manager_id=m.EMP_id and m.salary<e.salary
  61. SQL > Select m.manager_id, count(*) from EMP e, EMP m where e.EMP_id=m.manager_id group by m.manager_id
  62. SQL > Select L_name, d.DEPT_ID, d.name from EMP e, department d where e.DEPT_ID(+)=d.DEPT_ID
  63. SQL > Select L_name, d.DEPT_ID, d.name from EMP e, department d where e.DEPT_ID(+)=d.DEPT_ID and d.DEPT_IDin (select DEPT_ID from department where name IN (‘SALES’,’OPERATIONS’))
  64. SQL > Select function from job where job_id in (Select job_id from EMP where DEPT_ID=(select DEPT_ID from department where name=’SALES’)) union Select function from job where job_id in (Select job_id from EMP where DEPT_ID=(select DEPT_ID from department where name=’ACCOUNTING’))
  65. SQL > Select function from job where job_id in (Select job_id from EMP where DEPT_ID=(select DEPT_ID from department where name=’SALES’)) union all Select function from job where job_id in (Select job_id from EMP where DEPT_ID=(select DEPT_ID from department where name=’ACCOUNTING’))
  66. SQL > Select function from job where job_id in (Select job_id from EMP where DEPT_ID=(select DEPT_ID from department where name=’RESEARCH’)) intersect Select function from job where job_id in (Select job_id from EMP where DEPT_ID=(select DEPT_ID from department where name=’ACCOUNTING’)) order by function