SQL QUERY PRACTICE
1. List all information about the employee in the EMP table.
2. List all the information about the departments in the dept table.
3. List all the employee number, name, Job title and Hiredate of EMP department 10.
4. Select name and salary of all the employees who are clerks.
5. List the name, job, salary of everyone hired in December 17,1980.
6. Select name and annual salary of all employees.
7. List the department name and department number for department with numbers greater than or equal to 20.
8. List all the salary whose salary between 1000 and 2000.
9. List names of employees whose salaries are less than 2500.
10. Select the name, salary and commission of employee whose commission is greater than their salary.
11. Display the name, monthly salary and daily salary and hourly salary for employees. Assume that the Sal column in the table is monthly salary, that there are 22 working days in the month and that there are 8 working hours in a day. Rename the columns as monthly, daily, hourly.
12. List the names and employers number of manager who earn more than 2600. Display in alphabetic order by name.
13. Display all employees names which have TH or LL in them.
14. List the details of employees in departments 10 and 20 in alphabetic order of name.
15. Select the Information about manager and the president from the coloum job in the table EMP. Order the result by department number.
16. List the employee name that do not end with “S”.
17. List the employee name start with “C”.
18. List the name, job and department of everyone whose names fulls in the Alphabetic range “C” and “P”.
19. Select employee details working 20,30 and 40.
20. List of employee whose names start with “T” and end with “R”.
21. Display all the employees who hired during 1983.
22. Display the details as shown below. Smith has held the position of clerk in department 20 since 19 June 1983.Allen has held the position of salesman in department 30 since 13 June 1983.Ward has held the position of clerk in department 30 since 13 June 1983.
23. List all the rows in the EMP table, by converting the null values in comm column to 0.
24. List all the managers and salesman with salaries over 1500.
25. Write a query that will accept a given job title and display all records according to the title. Accept salary prompt ‘enter value for salary’,
26. List of employee who do not get any commission.
27. Show what length names appear in the EMP table. Eliminate duplicates lengths from the returned.do not show the names themselves.
28. List the name and hiredate of the employees in dept 20.display hiredate formatted a‘12/03/84’.
29. How many months has the president worked for the company? Round to the nearest whole number of months.
30. List the names of all the employees whose hire date anniversary is in the month of December.
31. Give SQL command to find the average salary per job in each dept. This SQL figures in EMP table are for each month.
32. In one query, count the number of people in dept 30 who can receive a salary and the number of people
33. Who can receive a commission. Compute the average, minimum and maximum salaries of those group of employees having job clerk or manager.
34. Calculate the total compensation expence for each dept for one year , the SQL comm figures in the EMP table are for each month. Assume that employees who do not earn a commission receive non monectary that are worth $100 a month.
35. Do a case sensitive search for a list of employees with a job that the user enters (eg., clerk).
36. Produce the following output.
Employee
Smith(clerk).
37. Which employee earn less than 30% of the president’s salary.
38. Who was the last empoyee hired in each department. Create a view consisting of numbers of employees and their total sum of salary grouped by deptno.
39. Create a view consisting of columns from emp table and their corresponding records from dept table consisting of Dname and location.
40. How many employees work in Newyork.
41. Which employees work in Newyork.
42. Write a query to display as following
Ename Date-Hired
Smith June fourtheenth 1983
43. Print a list of employees displaying just salary if more than 1500.if exactly 1500,display “on target”.If less than 1500, display “below target”.
44. List the employee name and cities in which they work order by city.
45. Find the number of different employees and the number of different departments.
46. Determine the average salaries of employees.
47. List the department number, department name, location and local commission paid and total salary of each department.
48. Display only those jobs where the minimum salary is greater than or equal to 3000.
49. Display the average monthly salary bill for each job type within a department.
50. Find out the difference between the highest and lowest salaries.
51. Find all the departments which have more than 3 employees.
52. Check whether all employees numbers are indeed unique.
53. List lowest paid employees working for each manager.
54. Display all the empoyees name and their department name in department order.
55. Display all the empoyees name, department number and name of the department.
56. Display the department that has no employees.
57. Find all the employees who joined the company before their manager.
58. Find all the employees who have same job as blake.
59. Find all the employees who earn more than lowest salary in each department.
60. Display the employees who earn more than lowest salary in department 30.
61. Find all the employees who earn more than every employee in dept 30.
62. Find the job with the highest average salary.
63. Display the name, job, hiredate for employees whose salary is greater than the highest salary in the sales department.
64. Copy all the information on the department 10 into the d10 history table.
65. Delete all the information about department 10 from the employee table.
66. Create cluster table/cluster index on emp(deptno) and dept (deptno).
67. Create a synonym on emp table.
68. Create index on employee number on emp table.
69. List all the tables which are created on a particular date.
70. List all the indexes which are created on a particular date and table.
71. List all the views which are created for a particular date and table.
72. List all the synonyms which are created for a particular date and table.
73. List all the objects which are created for a particular date.
74. To display an Asterick against the row of the most recently hired employees. Display Ename hiredate and column name showing “*” and column name maxdate(for unique records).
75. Delete the rows from dept table of those dept number do not have matching rows in the emp table.
76. Select Ename, job, sal, mgr, deptno for a given job title.
77. List the employees name and salary increased by 15% and expressed as a whole number of dollars.
78. Display each employees name with hiredate and review date. Assume review date is one year after hiredate. Order the output in ascending review date order.
79. Write a query which will return the day of the week for any date entered in the formate DD MM YY.
80. Employees hired on or before the 15th of any month are paid on the last Friday of that month, those hired after 15th are paid the last Friday of the following month. /
81. Print a list of employees, their hiredate and first pay date. Sort on hiredate.
82. List out the minimum and maximum salary for each job type.
83. Find the average salary and average total remuneration for each job type. Remember salesmen earn commission.
84. Find all the departments which have more than 3 employees.
85. List the employee name, job, salary, grade and department name for everyone in the company except clerks. Sort on salary, displaying the highest salary first.
86. List the Ename, job, annual salary, deptno, Dname, grade of employees who earn $36000 a year or who are clerks.
87. Show name, salary, deptno for any employee who earns a salary greater than the average for their department sort in department order.
88. Who are top 3 earners in the company? Display their name and salary.
89. In which year did most people join in the company? Display their year and number of employees.
90. Find employees who earn a salary greater than the average salary for each department.
91. Find all employees whose department is not in the dept table.
92. It has been discovered that the sales people in department 30 are not males. Produce the following output.
ENAME DEPTNO JOB
Allen 30 Sales Person.
Ward 30 Sales Person.
Blake 30 Manager.
93. Find the employees who earns the minimum salary.
94. Display the name, job, hiredate, salary for employee whose salary is greater than the highest salary in sales department.
95. List employees with either the same job as Jones or a salary greater than or equal to Frod’s order by job and salary.
96. List employees in department 10 with the same job as anyone in the sales department.
97. List employees having the same job as employees located in Chicago.
98. List employees whose salaries equal to that of Scott or Ward.
99. Set salaries of all salesman equal to 1.1 times the average salary of salesman.
100.Delete all employees with the same job as Jones and Jones should not be deleted.
