SQL Exercises, Practice, Solution - exercises on employee Database
SQL employee Database [115 Exercise with Solution]
[An editor is available at the bottom of the page to write and execute the scripts.]
Structure of employee Database:
1. From the following table return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 ....
2. From the following table, write a SQL query to find the salaries of all employees. Return salary. Go to the editor
Sample table: employees
Sample Output:
salary --------- 6000.00 2750.00 2550.00 2957.00 ....
3. From the following table, write a SQL query to find the unique designations of the employees. Return job name. Go to the editor
Sample table: employees
Sample Output:
job_name ----------- CLERK SALESMAN MANAGER PRESIDENT ANALYST (5 rows)
4. From the following table, write a SQL query to list the employees’ name, increased their salary by 15%, and expressed as number of Dollars. Go to the editor
Sample table: employees
Sample Output:
emp_name | Salary ----------+---------- KAYLING | $ 6,900 BLAZE | $ 3,163 CLARE | $ 2,933 JONAS | $ 3,401 SCARLET | $ 3,565 ....
5. From the following table, write a SQL query to list the employee's name and job name as a format of "Employee & Job". Go to the editor
Sample table: employees
Sample Output:
Employee & Job --------------------- KAYLING PRESIDENT BLAZE MANAGER CLARE MANAGER JONAS MANAGER SCARLET ANALYST ....
6. Write a query in SQL to produce the output of employees as follows. Go to the editor
Employee
JONAS(manager).
Sample table: employees
Sample Output:
Employee -------------------- KAYLING(president) BLAZE(manager) CLARE(manager) JONAS(manager) SCARLET(analyst) ....
7. From the following table, write a SQL query to find those employees with hire date in the format like February 22, 1991. Return employee ID, employee name, salary, hire date. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | salary | to_char --------+----------+---------+------------------- 68319 | KAYLING | 6000.00 | NOVEMBER 18,1991 66928 | BLAZE | 2750.00 | MAY 01,1991 67832 | CLARE | 2550.00 | JUNE 09,1991 65646 | JONAS | 2957.00 | APRIL 02,1991 67858 | SCARLET | 3100.00 | APRIL 19,1997 ....
8. From the following table, write a SQL query to count the number of characters except the spaces for each employee name. Return employee name length. Go to the editor
Sample table: employees
Sample Output:
length
--------
7
5
5
5
7
....
9. From the following table, write a SQL query to find the employee ID, salary, and commission of all the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | salary | commission --------+---------+------------ 68319 | 6000.00 | 66928 | 2750.00 | 67832 | 2550.00 | 65646 | 2957.00 | 67858 | 3100.00 | ....
10. From the following table, write a SQL query to find the unique department with jobs. Return department ID, Job name. Go to the editor
Sample table: employees
Sample Output:
dep_id | job_name --------+----------- 3001 | MANAGER 2001 | ANALYST 3001 | SALESMAN 1001 | MANAGER 1001 | PRESIDENT ...
11. From the following table, write a SQL query to find those employees who do not belong to the department 2001. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 64989 | ADELYN | SALESMAN | 66928 | 1991-02-20 | 1700.00 | 400.00 | 3001 ....
12. From the following table, write a SQL query to find those employees who joined before 1991. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+--------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 (1 row)
13. From the following table, write a SQL query to compute the average salary of those employees who work as ‘ANALYST’. Return average salary. Go to the editor
Sample table: employees
Sample Output:
avg ----------------------- 3100.0000000000000000 (1 row)
14. From the following table, write a SQL query to find the details of the employee ‘BLAZE’. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 (1 row)
15. From the following table, write a SQL query to find those employees whose commission is more than their salary. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66564 | MADDEN | SALESMAN | 66928 | 1991-09-28 | 1350.00 | 1500.00 | 3001 (1 row)
16. From the following table, write a SQL query to find those employees whose salary exceeds 3000 after giving 25% increment. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 ....
17. From the following table, write a SQL query to find the names of the employees whose length is six. Return employee name. Go to the editor
Sample table: employees
Sample Output:
emp_name ---------- ADELYN MADDEN TUCKER ADNRES JULIUS MARKER (6 rows)
18. From the following table, write a SQL query to find those employees who joined in the month January. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 (1 row)
19. From the following table, write a SQL query to find the name of employees and their manager separated by the string 'works for'. Go to the editor
Sample table: employees
Sample Output:
?column? -------------------------- BLAZE works for KAYLING CLARE works for KAYLING JONAS works for KAYLING SCARLET works for JONAS FRANK works for JONAS .....
20. From the following table, write a SQL query to find those employees whose designation is ‘CLERK’. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 (4 rows)
21. From the following table, write a SQL query to find those employees whose experience is more than 27 years. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 64989 | ADELYN | SALESMAN | 66928 | 1991-02-20 | 1700.00 | 400.00 | 3001 65271 | WADE | SALESMAN | 66928 | 1991-02-22 | 1350.00 | 600.00 | 3001 65679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 (3 rows)
22. From the following table, write a SQL query to find those employees whose salaries are less than 3500. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 ....
23. From the following table, write a SQL query to find the employee whose designation is ‘ANALYST’. Return employee name, job name and salary. Go to the editor
Sample table: employees
Sample Output:
emp_name | job_name | salary ----------+----------+--------- SCARLET | ANALYST | 3100.00 FRANK | ANALYST | 3100.00 (2 rows)
24. From the following table, write a SQL query to find those employees who have joined in the year 1991.Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 ....
25. From the following table, write a SQL query to find those employees who joined before 1st April 1991. Return employee ID, employee name, hire date and salary. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | hire_date | salary --------+----------+------------+--------- 63679 | SANDRINE | 1990-12-18 | 900.00 64989 | ADELYN | 1991-02-20 | 1700.00 65271 | WADE | 1991-02-22 | 1350.00 (3 rows)
26. From the following table, write a SQL query to find those employees who are not working under a manager. Return employee name, job name. Go to the editor
Sample table: employees
Sample Output:
emp_name | job_name --------------+-------------- KAYLING | PRESIDENT
27. From the following table, write a SQL query to find those employees who joined on 1st May 91. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 (1 row)
28. From the following table, write a SQL query to find those employees working under the manger whose ID is 68319. Return employee ID, employee name, salary, and age. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | salary | Experience --------+----------+---------+------------------------- 66928 | BLAZE | 2750.00 | 26 years 8 mons 29 days 67832 | CLARE | 2550.00 | 26 years 7 mons 21 days 65646 | JONAS | 2957.00 | 26 years 9 mons 28 days (3 rows)
29. From the following table, write a SQL query to find those employees who earn more than 100 as daily salary. Return employee ID, employee name, salary, and age. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | salary | Experience --------+----------+---------+------------------------- 68319 | KAYLING | 6000.00 | 26 years 2 mons 12 days 67858 | SCARLET | 3100.00 | 20 years 9 mons 11 days 69062 | FRANK | 3100.00 | 26 years 1 mon 27 days (3 rows)
30. From the following table, write a SQL query to find those employees who retired after 31-Dec-99, completion of 8 years of service period. Return employee name. Go to the editor
Sample table: employees
Sample Output:
emp_name -------------- ADNRES MARKER SCARLET (3 rows)
31. From the following table, write a SQL query to find those employees whose salary is an odd value. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 (1 row)
32. From the following table, write a SQL query to find those employees whose salary contains only three digits. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001
33. From the following table, write a SQL query to find those employees who joined in the month of APRIL. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 (2 rows)
34. From the following table, write a SQL query to find those employees who joined in the company before 19th of a month. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 ...
35. From the following table, write a SQL query to find those employees who are SALESMAN and experience more than 10 months. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 64989 | ADELYN | SALESMAN | 66928 | 1991-02-20 | 1700.00 | 400.00 | 3001 65271 | WADE | SALESMAN | 66928 | 1991-02-22 | 1350.00 | 600.00 | 3001 (2 rows)
36. From the following table, write a SQL query to find those employees of department id 3001 or 1001 and joined in the year 1991. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 64989 | ADELYN | SALESMAN | 66928 | 1991-02-20 | 1700.00 | 400.00 | 3001 ....
37. From the following table, write a SQL query to find those employees who are working for the department ID 1001 or 2001.Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 ....
38. From the following table, write a SQL query to find those employees whose designation is ‘CLERK’ and work in the department ID 2001. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 (2 rows)
39. From the following table, write a query in SQL to find those employees where - Go to the editor
1. the employees receive some commission which should not be more than the salary and annual salary including commission is below 34000.
2. Designation is ‘SALESMAN’ and working in the department ‘3001’.
Return employee ID, employee name, salary and job name.
Sample table: employees
Sample Output:
emp_id | emp_name | salary | job_name --------+----------+---------+---------- 64989 | ADELYN | 1700.00 | SALESMAN 65271 | WADE | 1350.00 | SALESMAN 68454 | TUCKER | 1600.00 | SALESMAN (3 rows)
40. From the following table, write a SQL query to find those employees who are either CLERK or MANAGER. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 ....
41. From the following table, write a SQL query to find those employees who joined in any year except the month of February. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 ....
42. From the following table, write a SQL query to find those employees who joined in the year 91. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 ....
43. From the following table, write a SQL query to find those employees who joined in the month of June 1991. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 (1 row)
44. From the following table, write a SQL query to find all the employees whose annual salary is within the range 24000 and 50000 (Begin and end values are included.). Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 (5 rows)
45. From the following table, write a SQL query to find all those employees who have joined on 1st May, 20th Feb, and 3rd Dec in the year 1991. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id |emp_name |job_name |manager_id |hire_date |salary |commission |dep_id | -------|---------|---------|-----------|-----------|--------|-----------|-------| 66928 |BLAZE |MANAGER |68319 |1991-05-01 |2750.00 | |3001 | 64989 |ADELYN |SALESMAN |66928 |1991-02-20 |1700.00 |400.00 |3001 | 69000 |JULIUS |CLERK |66928 |1991-12-03 |1050.00 | |3001 | 69062 |FRANK |ANALYST |65646 |1991-12-03 |3100.00 | |2001 |
46. From the following table, write a SQL query to find those employees working under the managers 63679 or 68319 or 66564 or 69000. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 (3 rows)
47. From the following table, write a SQL query to find those employees who joined after the month JUNE in the year 1991 and within this year. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 66564 | MADDEN | SALESMAN | 66928 | 1991-09-28 | 1350.00 | 1500.00 | 3001 68454 | TUCKER | SALESMAN | 66928 | 1991-09-08 | 1600.00 | 0.00 | 3001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 (5 rows)
48. From the following table, write a SQL query to find those employees who joined in 90's. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 .....
49. From the following table, write a SQL query to find those managers who are in the department 1001 or 2001. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 (2 rows)
50. From the following table, write a SQL query to find those employees who joined in the month FEBRUARY with a salary range between 1001 to 2000 (Begin and end values are included.). Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 64989 | ADELYN | SALESMAN | 66928 | 1991-02-20 | 1700.00 | 400.00 | 3001 65271 | WADE | SALESMAN | 66928 | 1991-02-22 | 1350.00 | 600.00 | 3001 (2 rows)
51. From the following table, write a SQL query to find those employees who joined before or after the year 1991. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 (4 rows)
52. From the following tables, write a SQL query to find employees along with department name. Return employee ID, employee name, job name, manager ID, hire date, salary, commission, department ID, and department name. Go to the editor
Sample table: employees
Sample table: department
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id | dep_name --------+----------+-----------+------------+------------+---------+------------+--------+---------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 | FINANCE 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 | MARKETING 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 | FINANCE 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 | AUDIT ....
53. From the following tables, write a SQL query to find those employees who earn 60000 in a year or not working as an ANALYST. Return employee name, job name, (12*salary) as Annual Salary, department ID, and grade. Go to the editor
Sample table: employees
Sample table: department
Sample table: salary_grade
Sample Output:
emp_name | job_name | Annual Salary | dep_id | dep_name | grade ----------+-----------+---------------+--------+-----------+------- SANDRINE | CLERK | 10800.00 | 2001 | AUDIT | 1 ADNRES | CLERK | 14400.00 | 2001 | AUDIT | 1 JULIUS | CLERK | 12600.00 | 3001 | MARKETING | 1 WADE | SALESMAN | 16200.00 | 3001 | MARKETING | 2 MADDEN | SALESMAN | 16200.00 | 3001 | MARKETING | 2 ....
54. From the following table, write a SQL query to find those employees whose salary is higher than the salary of their managers. Return employee name, job name, manager ID, salary, manager name, manager's salary. Go to the editor
Sample table: employees
Sample Output:
emp_name | job_name | manager_id | salary | Manager | emp_id | Manager_Salary ----------+----------+------------+---------+---------+--------+---------------- SCARLET | ANALYST | 65646 | 3100.00 | JONAS | 65646 | 2957.00 FRANK | ANALYST | 65646 | 3100.00 | JONAS | 65646 | 2957.00 (2 rows)
55. From the following table, write a SQL query to find those employees whose salary is between 2000 and 5000 (Begin and end values are included.) and location is PERTH. Return employee name, department ID, salary, and commission. Go to the editor
Sample table: employees
Sample table: department
Sample Output:
emp_name | dep_id | salary | commission ----------+--------+---------+------------ BLAZE | 3001 | 2750.00 | (1 row)
56. From the following table, write a SQL query to find those employees whose department ID is 1001 or 3001 and salary grade is not 4. They joined the company before 1992-12-31. Return grade, employee name. Go to the editor
Sample table: employees
Sample Output:
grade | emp_name
-------+----------
1 | JULIUS
2 | WADE
2 | MADDEN
2 | MARKER
3 | ADELYN
3 | TUCKER
5 | KAYLING
(7 rows)
Sample table: salary_grade
57. From the following table, write a SQL query to find those employees whose manager name is JONAS. Return employee id, employee name, job name, manager ID, hire date, salary, department ID, employee name. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | dep_id | emp_name --------+----------+----------+------------+------------+---------+--------+---------- 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | 2001 | JONAS 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | 2001 | JONAS (2 rows)
58. From the following table, write a SQL query to find the name and salary of the employee FRANK. Salary should be equal to the maximum salary within his or her salary group. Go to the editor
Sample table: employees
Sample table: salary_grade
Sample Output:
emp_name | salary ----------+--------- FRANK | 3100.00 (1 row)
59. From the following table, write a SQL query to find those employees who are working either as a MANAGER or an ANALYST with a salary in the range 2000, 5000 (Begin and end values are included.) without any commission. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 (5 rows)
60. From the following table, write a SQL query to find those employees working at PERTH, or MELBOURNE with an experience over 10 years. Return employee ID, employee name, department ID, salary, and department location. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | dep_id | salary | dep_location --------+----------+--------+---------+-------------- 64989 | ADELYN | 3001 | 1700.00 | PERTH 65271 | WADE | 3001 | 1350.00 | PERTH (2 rows)
Sample table: department
61. From the following table, write a SQL query to find those employees whose department location is SYDNEY or MELBOURNE with a salary range of 2000, 5000 (Begin and end values are included.) and joined in 1991. Return employee ID, employee name, department ID, salary, and department location. Go to the editor
Sample table: employees
Sample table: department
Sample Output:
emp_id | emp_name | dep_id | salary | dep_location --------+----------+--------+---------+-------------- 67832 | CLARE | 1001 | 2550.00 | SYDNEY 65646 | JONAS | 2001 | 2957.00 | MELBOURNE 69062 | FRANK | 2001 | 3100.00 | MELBOURNE (3 rows)
62. From the following table, write a SQL query to find those employees of MARKETING department come from MELBOURNE or PERTH within the grade 3 ,4, and 5 and experience over 25 years. Return department ID, employee ID, employee name, salary, department name, department location and grade. Go to the editor
Sample table: employees
Sample Output:
dep_id |emp_id |emp_name |salary |dep_name |dep_location |grade | -------|-------|---------|--------|----------|-------------|------| 3001 |66928 |BLAZE |2750.00 |MARKETING |PERTH |4 | 3001 |64989 |ADELYN |1700.00 |MARKETING |PERTH |3 | 3001 |68454 |TUCKER |1600.00 |MARKETING |PERTH |3 |
Sample table: salary_grade
Sample table: department
63. From the following table, write a SQL query to find those employees who are senior to their manager. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id | emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+--------+--------+----------+-----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 | 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 | 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 | 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 | 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 64989 | ADELYN | SALESMAN | 66928 | 1991-02-20 | 1700.00 | 400.00 | 3001 | 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 65271 | WADE | SALESMAN | 66928 | 1991-02-22 | 1350.00 | 600.00 | 3001 | 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 (6 rows)
64. From the following tables, write a SQL query to find those employees whose grade is 4 and salary between minimum and maximum salary. Return all information of each employees and their grade and salary related details. Go to the editor
Sample table: employees
Sample table: salary_grade
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id | grade | min_sal | max_sal --------+----------+----------+------------+------------+---------+------------+--------+-------+---------+--------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 | 4 | 2101 | 3100 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 | 4 | 2101 | 3100 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 | 4 | 2101 | 3100 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 | 4 | 2101 | 3100 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 | 4 | 2101 | 3100 (5 rows)
65. From the following tables, write a SQL query to find those employees, excluding MARKER or ADELYN of the department PRODUCTION or AUDIT and joined after 1991. Return employee name. Go to the editor
Sample table: employees
Sample table: department
Sample table: salary_grade
Sample Output:
emp_name ---------- ADNRES SCARLET (2 rows)
66. From the following table, write a SQL query to find the employees and their salaries. Sort the result-set in ascending order by salaries. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 65271 | WADE | SALESMAN | 66928 | 1991-02-22 | 1350.00 | 600.00 | 3001 ....
67. From the following table, write a SQL query to list employees in ascending order on department ID and descending order on jobs. Return complete information about the employees. Go to the editor
Sample table: employees
68. From the following table, write a SQL query to find the entire unique jobs in descending order. Return job name. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 ....
69. From the following table, write a SQL query to find the employees in the ascending order of their annual salary. Return employee ID, employee name, monthly salary, salary/30 as Daily_Salary, and 12*salary as Anual_Salary. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | monthly_salary | daily_salary | anual_salary --------+----------+----------------+----------------------+-------------- 63679 | SANDRINE | 900.00 | 30.0000000000000000 | 10800.00 69000 | JULIUS | 1050.00 | 35.0000000000000000 | 12600.00 68736 | ADNRES | 1200.00 | 40.0000000000000000 | 14400.00 65271 | WADE | 1350.00 | 45.0000000000000000 | 16200.00 66564 | MADDEN | 1350.00 | 45.0000000000000000 | 16200.00 ....
70. From the following table, write a SQL query to find those employees who are either 'CLERK' or 'ANALYST’. Sort the result set in descending order on job_name. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 ....
71. From the following table, write a SQL query to find the department location of employee ‘CLARE’. Return department location. Go to the editor
Sample table: employees
Sample Output:
dep_location -------------- SYDNEY (1 row)
Sample table: department
72. From the following table, write a SQL query to find those employees who joined on 1-MAY-91, or 3-DEC-91, or 19-JAN-90. Sort the result-set in ascending order by hire date. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 (3 rows)
73. From the following table, write a SQL query to find those employees who draw salary less than 1000. Sort the result-set in ascending order by salary. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+--------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 (1 row)
74. From the following table, write a SQL query to list the employees in ascending order on the salary. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 65271 | WADE | SALESMAN | 66928 | 1991-02-22 | 1350.00 | 600.00 | 3001 ....
75. From the following table, write a SQL query to list the employees in the ascending order on job name and descending order on employee id. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 ....
76. From the following table, write a SQL query to list the unique jobs of department 2001 and 3001 in descending order. Return job name. Go to the editor
Sample table: employees
Sample Output:
job_name ---------- SALESMAN MANAGER CLERK ANALYST (4 rows)
77. From the following table, write a SQL query to list all the employees except PRESIDENT and MANAGER in ascending order of salaries. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 66564 | MADDEN | SALESMAN | 66928 | 1991-09-28 | 1350.00 | 1500.00 | 3001 ....
78. From the following table, write a SQL query to find the employees whose annual salary is below 25000. Sort the result set in ascending order of the salary. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 65271 | WADE | SALESMAN | 66928 | 1991-02-22 | 1350.00 | 600.00 | 3001 66564 | MADDEN | SALESMAN | 66928 | 1991-09-28 | 1350.00 | 1500.00 | 3001 ....
79. From the following table, write a SQL query to list the employees who works as a SALESMAN. Sort the result set in ascending order of annual salary. Return employee id, name, annual salary, daily salary of all the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | Annual Salary | Daily Salary --------+----------+---------------+--------------------- 65271 | WADE | 16200.00 | 44.3835616438356164 66564 | MADDEN | 16200.00 | 44.3835616438356164 68454 | TUCKER | 19200.00 | 52.6027397260273973 64989 | ADELYN | 20400.00 | 55.8904109589041096 (4 rows)
80. From the following table, write a SQL query to list the employee ID, name, hire date, current date and experience of the employees in ascending order on their experiences. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | hire_date | date | exp --------+----------+------------+------------+------------------------- 68736 | ADNRES | 1997-05-23 | 2018-02-01 | 20 years 8 mons 9 days 67858 | SCARLET | 1997-04-19 | 2018-02-01 | 20 years 9 mons 12 days 69324 | MARKER | 1992-01-23 | 2018-02-01 | 26 years 9 days 69062 | FRANK | 1991-12-03 | 2018-02-01 | 26 years 1 mon 29 days 69000 | JULIUS | 1991-12-03 | 2018-02-01 | 26 years 1 mon 29 days ....
81. From the following table, write a SQL query to list the employees in ascending order of designations of those joined after the second half of 1991. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66564 | MADDEN | SALESMAN | 66928 | 1991-09-28 | 1350.00 | 1500.00 | 3001 68454 | TUCKER | SALESMAN | 66928 | 1991-09-08 | 1600.00 | 0.00 | 3001 (5 rows)
82. From the following tables, write a SQL query to find the location of all the employees working in FINANCE or AUDIT department. Sort the result-set in ascending order by department ID. Return complete information about the employees. Go to the editor
Sample table: employees
Sample table: department
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id | dep_id | dep_name | dep_location --------+----------+-----------+------------+------------+---------+------------+--------+--------+----------+-------------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 | 1001 | FINANCE | SYDNEY 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 | 1001 | FINANCE | SYDNEY 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 | 1001 | FINANCE | SYDNEY 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 | 2001 | AUDIT | MELBOURNE ....
83. From the following tables, write a SQL query to find the employees along with grades in ascending order. Return complete information about the employees. Go to the editor
Sample table: employees
Sample table: salary_grade
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id | grade | min_sal | max_sal --------+----------+-----------+------------+------------+---------+------------+--------+-------+---------+--------- 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 | 1 | 800 | 1300 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 | 1 | 800 | 1300 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 | 1 | 800 | 1300 65271 | WADE | SALESMAN | 66928 | 1991-02-22 | 1350.00 | 600.00 | 3001 | 2 | 1301 | 1500 ....
84. From the following table, write a SQL query to find the employees according to the department in ascending order. Return name, job name, department, salary, and grade. Go to the editor
Sample table: employees
Sample table: department
Sample table: salary_grade
Sample Output:
emp_name | job_name | dep_name | salary | grade ----------+-----------+-----------+---------+------- KAYLING | PRESIDENT | FINANCE | 6000.00 | 5 CLARE | MANAGER | FINANCE | 2550.00 | 4 MARKER | CLERK | FINANCE | 1400.00 | 2 SANDRINE | CLERK | AUDIT | 900.00 | 1 SCARLET | ANALYST | AUDIT | 3100.00 | 4 ....
85. From the following tables, write a SQL query to find all employees except CLERK and sort the result-set in descending order by salary. Return employee name, job name, salary, grade and department name. Go to the editor
Sample table: employees
Sample table: department
Sample table: salary_grade
Sample Output:
emp_name | job_name | salary | grade | dep_name ----------+-----------+---------+-------+----------- KAYLING | PRESIDENT | 6000.00 | 5 | FINANCE FRANK | ANALYST | 3100.00 | 4 | AUDIT SCARLET | ANALYST | 3100.00 | 4 | AUDIT JONAS | MANAGER | 2957.00 | 4 | AUDIT ....
86. From the following table, write a SQL query to find those employees work in the department 1001 or 2001. Return employee ID, name, salary, department, grade, experience, and annual salary. Go to the editor
Sample table: employees
Sample table: department
Sample table: salary_grade
87. From the following table, write a SQL query to list the details of the employees along with the details of their departments. Go to the editor
Sample table: employees
Sample table: department
88. From the following table, write a SQL query to list the employees who are senior to their MANAGERS. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id | emp_id | emp_name | job_name | manager_id | hire_date | salary | commission| dep_id --------+----------+----------+------------+------------+---------+------------+--------+--------+----------+-----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 | 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 | 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 | 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 63679 | SANDRINE | CLERK | 69062 | 1990-12-18 | 900.00 | | 2001 | 69062 | FRANK | ANALYST | 65646 | 1991-12-03 | 3100.00 | | 2001 ....
89. From the following table, write a SQL query to find those employees who work in the department 1001. Sort the result-set in ascending order by salary. Return employee ID, employee name, salary and department ID. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | salary | dep_id --------+----------+---------+-------- 69324 | MARKER | 1400.00 | 1001 67832 | CLARE | 2550.00 | 1001 68319 | KAYLING | 6000.00 | 1001 (3 rows)
90. From the following table, write a SQL query to find the highest salary. Return highest salary. Go to the editor
Sample table: employees
Sample Output:
max --------- 6000.00 (1 row)
91. From the following table, write a SQL query to find the average salary and average total remuneration (salary and commission) for each type of job. Return name, average salary and average total remuneration. Go to the editor
Sample table: employees
Sample Output:
job_name | avg | avg -----------+-----------------------+----------------------- CLERK | 1137.5000000000000000 | SALESMAN | 1500.0000000000000000 | 2125.0000000000000000 MANAGER | 2752.3333333333333333 | PRESIDENT | 6000.0000000000000000 | ANALYST | 3100.0000000000000000 | (5 rows)
92. From the following table, write a SQL query to compute the total annual salary distributed against each job in the year 1991. Return job name, total annual salary. Go to the editor
Sample table: employees
Sample Output:
job_name | sum -----------+---------- CLERK | 12600.00 PRESIDENT | 72000.00 SALESMAN | 72000.00 ANALYST | 37200.00 MANAGER | 99084.00 (5 rows)
93. From the following table, write a SQL query to list the employee id, name, department id, location of all the employees. Go to the editor
Sample table: employees
Sample table: department
Sample Output:
emp_id | emp_name | dep_id | dep_location --------+----------+--------+-------------- 68319 | KAYLING | 1001 | SYDNEY 66928 | BLAZE | 3001 | PERTH 67832 | CLARE | 1001 | SYDNEY 65646 | JONAS | 2001 | MELBOURNE 67858 | SCARLET | 2001 | MELBOURNE ....
94. From the following table, write a SQL query to find those employees who work in the department ID 1001 or 2001. Return employee ID, employee name, department ID, department location, and department name. Go to the editor
Sample table: employees
Sample table: department
Sample Output:
emp_id | emp_name | dep_id | dep_location | dep_name --------+----------+--------+--------------+---------- 68319 | KAYLING | 1001 | SYDNEY | FINANCE 67832 | CLARE | 1001 | SYDNEY | FINANCE 65646 | JONAS | 2001 | MELBOURNE | AUDIT 68736 | ADNRES | 2001 | MELBOURNE | AUDIT ....
95. From the following table, write a SQL query to find those employees whose salary is in the range minimum and maximum salary (Begin and end values are included.). Return employee ID, name, salary and grade. Go to the editor
Sample table: employees
Sample table: salary_grade
Sample Output:
emp_id | emp_name | salary | grade --------+----------+---------+------- 63679 | SANDRINE | 900.00 | 1 68736 | ADNRES | 1200.00 | 1 69000 | JULIUS | 1050.00 | 1 65271 | WADE | 1350.00 | 2 66564 | MADDEN | 1350.00 | 2 ....
96. From the following table, write a SQL query to list the managers and number of employees work under them. Sort the result set in ascending order on manager. Return manager ID and number of employees under them. Go to the editor
Sample table: employees
Sample Output:
manager_id | count
------------+-------
65646 | 2
66928 | 5
67832 | 1
67858 | 1
68319 | 3
69062 | 1
(6 rows)
97. From the following table, write a SQL query to count the number of employees of each designation in each department. Return department id, job name and number of employees. Go to the editor
Sample table: employees
Sample Output:
dep_id | job_name | count --------+-----------+------- 3001 | MANAGER | 1 2001 | ANALYST | 2 3001 | SALESMAN | 4 1001 | MANAGER | 1 ....
98. From the following table, write a SQL query to find those departments where at least two employees work. Return department id, number of employees. Go to the editor
Sample table: employees
Sample Output:
dep_id | count --------+------- 3001 | 6 1001 | 3 2001 | 5 (3 rows)
99. From the following table, write a SQL query to list the grade, number of employees, and maximum salary of each grade. Go to the editor
Sample table: employees
Sample table: salary_grade
Sample Output:
grade | count | max
-------+-------+---------
4 | 5 | 3100.00
1 | 3 | 1200.00
5 | 1 | 6000.00
3 | 2 | 1700.00
2 | 3 | 1400.00
(5 rows)
100. From the following table, write a SQL query to find those departments where at least two employees work as a SALESMAN in each grade. Return department name, grade and number of employees. Go to the editor
Sample table: employees
Sample table: department
Sample table: salary_grade
Sample Output:
dep_name | grade | count -----------+-------+------- MARKETING | 2 | 2 MARKETING | 3 | 2 (2 rows)
101. From the following table, write a SQL query to find those departments where less than four employees work. Return department ID, number of employees. Go to the editor
Sample table: employees
Sample Output:
dep_id | count --------+------- 1001 | 3 (1 row)
102. From the following tables, write a SQL query to find those departments where at least two employees work. Return department name, number of employees. Go to the editor
Sample table: employees
Sample table: department
Sample Output:
dep_name | count -----------+------- FINANCE | 3 MARKETING | 6 AUDIT | 5 (3 rows)s
103. From the following table, write a SQL query to check whether the employees ID are unique or not. Return employee id, number of employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | count --------+------- 69324 | 1 69062 | 1 63679 | 1 67858 | 1 66564 | 1 .....
104. From the following table, write a SQL query to find number of employees and average salary. Group the result set on department id and job name. Return number of employees, average salary, department ID, and job name. Go to the editor
Sample table: employees
Sample Output:
count | avg | dep_id | job_name
-------+-----------------------+--------+-----------
1 | 2750.0000000000000000 | 3001 | MANAGER
2 | 3100.0000000000000000 | 2001 | ANALYST
4 | 1500.0000000000000000 | 3001 | SALESMAN
1 | 2550.0000000000000000 | 1001 | MANAGER
....
105. From the following table, write a SQL query to find those employees whose name start with 'A' and six characters in length. Return employee name. Go to the editor
Sample table: employees
Sample Output:
emp_name ---------- ADELYN ADNRES (2 rows)
106. From the following table, write a SQL query to find those employees whose name is six characters in length and the third character must be 'R'. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 (1 row)
107. From the following table, write a SQL query to find those employees whose name is six characters in length, starting with 'A' and ending with 'N'. Return number of employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 64989 | ADELYN | SALESMAN | 66928 | 1991-02-20 | 1700.00 | 400.00 | 3001 (1 row)
108. From the following table, write a SQL query to find those employees who joined in the month of where the second letter is 'a'. Return number of employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 (3 rows)
109. From the following table, write a SQL query to find those employees whose names contain the character set 'AR' together. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 (3 rows)
110. From the following table, write a SQL query to find those employees who joined in 90's. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 ....
111. From the following table, write a SQL query to find those employees whose ID not start with the digit 68. Return employee ID, employee ID using trim function. Go to the editor
Sample table: employees
Sample Output:
emp_id | btrim --------+------- 66928 | 66928 67832 | 67832 65646 | 65646 67858 | 67858 ....
112. From the following table, write a SQL query to find those employees whose names contain the letter 'A’. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+-----------+------------+------------+---------+------------+-------- 68319 | KAYLING | PRESIDENT | | 1991-11-18 | 6000.00 | | 1001 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 67832 | CLARE | MANAGER | 68319 | 1991-06-09 | 2550.00 | | 1001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 ....
113. From the following table, write a SQL query to find those employees whose name ends with 'S' and six characters long. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 69000 | JULIUS | CLERK | 66928 | 1991-12-03 | 1050.00 | | 3001 (2 rows)
114. From the following table, write a SQL query to find those employees who joined in any month, but the month name contain the character ‘A’. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 65646 | JONAS | MANAGER | 68319 | 1991-04-02 | 2957.00 | | 2001 67858 | SCARLET | ANALYST | 65646 | 1997-04-19 | 3100.00 | | 2001 64989 | ADELYN | SALESMAN | 66928 | 1991-02-20 | 1700.00 | 400.00 | 3001 ....
115. From the following table, write a SQL query to find those employees who joined in any month, but the name of the month contain the character ‘A’ in second position. Return complete information about the employees. Go to the editor
Sample table: employees
Sample Output:
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id --------+----------+----------+------------+------------+---------+------------+-------- 66928 | BLAZE | MANAGER | 68319 | 1991-05-01 | 2750.00 | | 3001 68736 | ADNRES | CLERK | 67858 | 1997-05-23 | 1200.00 | | 2001 69324 | MARKER | CLERK | 67832 | 1992-01-23 | 1400.00 | | 1001 (3 rows)
Practice Online
More to Come!
Structure of employee Database:
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework