DB Test-1

1. write a SQL query to find the unique salespeople ID from orders table in Inventory database.
Return salesman_id.
2. write a SQL query to find the orders, which are delivered by a salesperson of ID. 5001.
Return ord_no, ord_date, purch_amt. Table-orders, DB – inventory database
3. write a SQL query to find those employees whose salary is less than 6000. Return full name
(first and last name), and salary. Table-employees, DB- HR database
4. write a SQL query to find those employees who have no department number. Return
employee_id, first_name, last_name, email,phone_number,hire_date, job_id,
salary,commission_pct,manager_id and department_id. Table-employees, DB- HR database
5. write a SQL query to find those employees whose first name does not contain the letter ‘M’.
Sort the result-set in ascending order by department ID. Return full name (first and last
name together), hire_date, salary and department_id. Table- employees, DB- HR database
6. write a SQL query to find those employees who falls one of the following criteria :
1. whose salary is in the range of 8000, 12000 (Begin and end values are included.) and get
some commission.
2. : those employees who joined before ‘2003-06-05’ and not included in the department
number 40, 120 and 70. Return all fields
Table- employees, DB- HR database
7. write a SQL query to find those employees whose first name ends with the letter ‘m’. Return
the first and last name, and salary. Table- employees, DB- HR database
8. write a SQL query to find those employees who were hired during November 5th, 2007 and
July 5th, 2009. Return full name (first and last), job id and hire date. Table- employees, DB-
HR database
9. write a SQL query to find those employees whose managers hold the ID 120 or 103 or 145.
Return first name, last name, email, salary and manager ID. Table- employees, DB- HR
database
10. write a SQL query to find those employees whose first name contains the letters D, S, or N.
Sort the result-set in descending order by salary. Return all fields. Table- employees, DB- HR
database
11. write a SQL query to find those employees who earn above 11000 or the seventh character
in their phone number is 3. Sort the result-set in descending order by first name. Return full
name (first name and last name), hire date, commission percentage, email, and telephone
separated by '-', and salary. Table- employees, DB- HR database
12. write a SQL query to find those employees who are working in the departments, which are
not included in the department number 50 or 30 or 80. Return employee_id, first_name,
job_id, department_id. Table- employees, DB- HR databases
13. write a SQL query to find those employees who worked more than two jobs in the past.
Return employee id. Table- job_history, DB- HR databases
14. write a SQL query to count the number of employees, sum of all salary, and difference
between the highest salary and lowest salary by each job id. Return job_id, count, sum,
salary_difference. Table- employees, DB- HR databases
15. write a SQL query to find each job ids where two or more employees worked for more than
300 days. Table- job_history, DB- HR databases
16. write a SQL query to count the number of employees worked under each manager. Return
manager ID and number of employees. Table- employees, DB- HR databases
17. write a SQL query to calculate average salary of those employees for each department who
get a commission percentage. Return department id, average salary. Table- employees, DB-
HR databases
18. write a SQL query to find those departments where a manager can manage four or more
employees. Return department_id. Table- employees, DB- HR databases
19. write a SQL query to find those departments where more than ten employees work, who got
a commission percentage. Return department id. Table- employees, DB- HR databases
20. write a SQL query to find those employees who have completed their previous jobs. Return
employee ID, end_date. Table- employees, DB- HR databases
21. write a SQL query to compute the average salary of each job ID. Exclude those records
where average salary is higher than 8000. Return job ID, average salary. Table- employees,
DB- HR databases
22. write a SQL query to find the first name, last name, department, city, and state province for
each employee. Tables- employees, departments and locations, DB- HR databases
23. write a SQL query to find all those employees who work in department ID 80 or 40. Return
first name, last name, department number and department name. Tables- employees,
departments and locations, DB- HR databases
24. write a SQL query to find all departments including those without any employee. Return first
name, last name, department ID, department name. Tables- employees, departments, DB-
HR databases
25. write a SQL query to find those employees who earn less than the employee of ID 182.
Return first name, last name and salary. Tables- employees, DB- HR databases
26. write a SQL query to find the employees and their managers. Return the first name of the
employee and manager. Tables- employees, DB- HR databases
27. write a SQL query to find those employees who joined between 1st January 1993 and 31
August 1997. Return job title, department name, employee name, and joining date of the
job. Tables- employees, job_history, jobs,departments DB- HR databases
28. write a SQL query to find the difference between maximum salary of the job and salary of
the employees. Return job title, employee name, and salary difference. Tables- employees,
jobs DB- HR databases
29. write a SQL query to compute the average salary, number of employees received
commission in that department. Return department name, average salary and number of
employees. Tables- employees, departments DB- HR databases
30. write a SQL query to find the name of the country, city, and departments, which are running
there. Tables- countries, departments, locations DB- HR databases
31. write a SQL query to compute the average salary of employees for each job title. Tables-
employees, jobs DB- HR databases
32. write a SQL query to find those employees who earn $12000 and above. Return employee
ID, starting date, end date, job ID and department ID. Tables- employees, job_history DB- HR
databases
33. write a SQL query to find those departments where at least 2 employees work. Group the
result set on country name and city. Return country name, city, and number of departments.
Tables- employees, countries, locations DB- HR databases
34. write a SQL query to find the department name, department ID, and number of employees
in each department. Tables- employees, departments DB- HR databases
35. write a SQL query to find full name (first and last name), job title, starting and ending date of
last jobs of employees who worked without a commission percentage. Tables- employees,
jobs, job_history DB- HR databases
36. write a SQL query to find full name (first and last name), and salary of those employees who
work in any department located in 'London' city. Tables- employees, departments, locations
DB- HR databases.
37. write a SQL query to compute the number of days worked by employees in a department of
ID 80. Return employee ID, job title, number of days worked. Tables- jobs, job_history DB-
HR databases.