DBMS SQL Queries

SQL Questions
Display the dept information from department table.
Display the details of all employees.
Display the name and job for all employees.
Display name and salary for all employees.
Display employee number and total salary for each employee.
Display employee name and annual salary for all employees.
Display the names of all employees who are working in department number 10.
Display the names of all employees working as clerks and drawing a salary more than
3000.
Display employee number and names for employees who earn commission.
Display names of employees who do not earn any commission.
Display the names of employees who are working as clerk, salesman, or analyst and
drawing a salary more than 3000.
Display the names of employees who are work in the company for the past years.
Display those who have joined the company before 30th June 90 or after 31 Dec 90.
Display current date.
Display employees who have joined the company after 30th June and before 31st Dec.
Display the minimum salary from emp table.
Display the average salary from emp table.
Display the maximum salary being paid to CLERK.
Display the maximum salary being paid in dept no 20.
Display the minimum salary being paid to any SALESMAN.
Display the average salary drawn by managers.
Display the total salary drawn by analyst working in dept no 40.
Display the names of employees in order of salary i.e. the name of the employee earning
lowest salary should appear first.
Display the names of employees in descending order of salary.
Display the details from emp table in order of emp name.
Display empno, ename, deptno, and sal. Sort the output first based on name and within
name by deptno and within deptno by sal.
Display the name of the employee along with t annual salary. The name of the employee
earni highest annual salary should appear first.
Display name, sal, hra, pf, da, total sal for each employee. The output should be in the
order of total sal.
Display dept numbers and total number of employees within each group.
Display the various jobs and total number of employees with each job group.
Display department numbers and total salary for each department.
Display department numbers and maximum salary, for each department.
Display the various jobs and total salary for each job.
Display each job along with minimum salary being paid in each job group.
Display the department numbers with more than three employees in each dept.
Display the various jobs along with total sal for each of the jobs where total salary is
greater than 40000.
Display the various jobs along with total number of employees in each job. The output
should contain only those jobs with more than three employees.
Display the name of emp who earns highest sal.
Display the employee number and name of employee working as CLERK and earning high
Display the names of the salesman earning salary more than the highest salary of any
clerk.
Display the names of clerks who earn salary more than that of James but less than that of
Scott.
Display the names of employees who earn a salary more than that of James but less than
that of Scott.
Display the names of the employees who earn highest salary in their respective
departments.
Display the names of employees who earn highest salaries in their respective job groups.
Display the employee names who are working in accounting dept.
Display the employee names who are working in Chicago.
Display the job groups having total salary greater than the maximum salary for managers.
Display the names of employees from department number 10 with salary greater than that
of any employee working in other departments.
Display the names of employee from department number 10 with salary greater than that
of all employee working in other departments.
Display the names of employees in upper case.
Display the names of employees in lower case.
Display the name of employees in proper case.
Find out the length of your name using appropriate function.
Display the length of all employees' names.
Display the name of the employee concatenate with EMP no.
Use appropriate function and extract 3 characters starting from 2 characters from the
following string 'Oracle'.
Find the first occurrence of character from the following string 'computer maintenance
corporation'.
Replace every occurrence of alphabet A with B in the string Allen's.
Display the information from EMP table. Wherever job 'manager' is found it should be
displayed as boss.
Display empno, ename, deptno from EMP table. Instead of display department numbers,
display the related department name.
Display your age in days.
Display your age in months.
Display current date as 15th August Friday nineteen forty seven.
Display a message for each row from EMP table as 'scott has joined the company on
Wednesday 13th August nineteen ninety'.
Find the date of nearest Saturday after current day.
Display current time.
Display the date three months before the current.
Display the common jobs from department number 10 and 20.
Display the jobs found in department number 10 and 20 eliminating duplicate jobs.
Display the jobs which are unique to dept no 10.
Display the details of those who do not have any person working under them.
Display the details of employees who are in sales dept and grade is 3.
Display those who are not managers and who are managers (union purpose).
Display those employees whose name contains not less than 4 chars.
Display those departments whose name start with 'S' while location name end with 'O'.
Display those employees whose manager name is JONES.
Display those employees whose salary is more than 3000 after giving 20% increment.
Display all employees with their dept name.
Display ename who are working in sales dept.
Display employee name, deptname, salary and comm for those salary in between 2000 to
5000 while location is Chicago.
Display those employees whose salary greater than his manager salary.
Display those employees who are working in the same dept where his manager is working.
Display those employees who are not working under any manager.
Display grade and employees name for the dept no 10 or 30 but grade is not 4, and joined
the company before 31-dec-82.
Update the salary of each employee by 10% increments that are not eligible for
commission.
Delete those employees who joined the company before 31-dec-82 while their dept
location is 'NEW YORK' or 'CHICAGO.
Display employee name, job, deptname, location for all who are working as managers.
Display those employees whose manager name is Jones, and also display their manager
name.
Display name and salary of Ford if Ford's salary is equal to highest salary of his grade.
Display employee name, his job, his dept name, his manager name, his grade and list
department wise.
List all the employees name, job, and salary grade and department name for everyone in
the company except 'CLERK'.
Display employee name, his job and his manager. Display also employees who are
without manager.
Find out the top 5 earners of company.
Display the name of those employees who are getting highest salary.
Display those employees whose salary is equa. average of maximum and minimum.
Display count of employees in each department where count greater than 3.
Display dname where at least 3 are working and display only dname.
Display name of those managers whose salary is more than average salary of company.
Display those managers whose salary is more than an average salary of his employees.
Display employee name, sal, comm and net pay for those employees whose net pay are
greater than or equal to any other employee salary of the company.
Display those employees whose salary is less than his manager but more than salary of
any other managers.
Display all employees names with total salary of company with employee name.
Find out the last 5 (least) earners of the company.
Find out the number of employees whose salary is greater than their manager salary.
Display those managers who are not working under president but they are working under
any other manager.
Delete those departments where no employee is working.
Delete those records from EMP table whose deptno not available in dept table.
Display those earners whose salary is out of the grade available in Sal grade table.
Display employee name, sal, comm and whose net pay is greater than any other in the
company.
Display name of those employees who are going to retire 31-dec-99. If the maximum job
period is 18 years.
Display those employees whose salary is odd value.
Display those employees whose salary contains at least 4 digits.
Display those employees who joined in the company in the month of DEC.
Display those employees whose name contains "A".
Display those employees whose deptno is available in salary.
Display those employees whose first 2 characters from hire date minus last 2 characters of
salary.
Display those employees whose 10% of salary is equal to the year of joining.
Display those employees who are working in sales or research.
Display the grade of Jones.
Display those employees who joined the company before 15th of the month.
Delete those records where no of employee in particular department is less than 3.
Delete those employees who joined the company 21 years back from today.
Display the department name the number of characters of which is equal to number of
employees in any other department.
Display those employees who are working as manager.
Count the number of employees who are working as manager.
Display the name of the department those employees who joined the company on the
same date.
Display those employees whose grade is equal to any number of salary but not equal to
first number of salary.
Display the manager who is having maximum number of employees working under him.
List out employees name and salary increased by 15% and expressed as whole number of
dollars.
Produce the output of the EMP table "EMPLOYEE_AND_JOB" for ename and job.
List all employees with hire date in the format 'June 4 1988'.
Print a list of employees displaying 'Less Salary' if less than 1500, 'Exact Salary' if exactly
1500, and 'More Salary' if greater than 1500.
Write query to calculate the length of employe has been with the company.
Given a string of the format 'nn/nn' verify that the first and last 2 characters are numbers
and that the middle character is 'y'. Print 'Yes' if valid, 'No' if not.
Employees hired on 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. Print a list of employees
with their hire date and first pay date.
Display those managers who are getting less than their employees' salary.
Print the details of all the employees who are subordinate to Blake.
Display those working as manager using correlated subquery.
Display those employees whose manager name is Jones and also with his manager
name.
Define variable representing the expressions used to calculate an employee's total annual
remuneration.
Use the variable to find all employees who can earn 30,000 a year or more.
Find out how many managers are there without listing them.
Find out the average salary and average total remuneration for each job type (remember
salesman earn commission).
Check whether all employee numbers are indeed unique.
List out the lowest paid employees working for each manager, exclude any groups where
min salary is less than 1000, sort the output by salary.
List ename, job, annual salary, deptno, dname and grade who earn 30000 per year and
who are not clerks.
Find out the job that was failed in the first half of 1983 and the same job that was failed
during the same period on 1984.
Find out all employees who joined before their manager.
List out all employees by name and number along with their manager's name and number;
also display 'No Manager' for those without a manager.
Find out the employees who earned the highest salary in each job type and sort in
descending salary order.
Find out the employees who earned the minimum salary for their job in ascending order.
Find out the most recently hired employees in each dept; order by hire date.
Display ename, salary and deptno for each employee who earn a salary greater than the
average of their department; order by deptno.
Display the departments where there are no employees.
Display the dept no with highest annual remuneration bill as compensation.
In which year did most people join the company? Display the year and number of
employees.
Display average salary figure for each department.
Write a query to display the row of the most recently hired employee; display ename, hire
date, and column max date.
Display employees who can earn more than the lowest salary in dept no 30.
Find employees who can earn more than every employee in dept no 30.
Select dept name, dept no, and sum of salary.
Find out average salary and average total remuneration for each job type.
Find all departments which have more than 3 employees.
If the pay day is next Friday after 15th and 30th of every month, what is the next pay day
from their hire date for employee in emp table?
If an employee is taken by you today in your organization and a review is after 9 months,
how many days from today your employee has to wait for a review?
Display employee name and his salary whose salary is greater than the highest average of
dept no.
Display the 10th record of EMP table (without using rowid).
Display half of the enames in upper case and the remaining lower case.
Display the 10th record of emp table without using group by and rowid.
Delete the 10th record of emp table.
Create a copy of emp table.
Select ename if ename exists more than once.
Display all enames in reverse order.
Display those employee whose joining of month and grade is equal.
Display those employee whose joining date is available in dept no.
Display those employee names as follows A ALLEN, B BLAKE.
List out the employees ename, salary, PF from emp.
Display RSPS from emp without using updating, inserting.
Create table emp with only one column empno.
Add column ename varchar(20) to emp table.
Add a primary key constraint.
Increase the length of ename column to 30 characters.
Add salary column to emp table.
Give a validation saying that salary cannot be greater than 10,000.
Disable the salary constraint.
Enable the salary constraint.
Add column called mgr to emp table.
Add a foreign key constraint for mgr column.
Add deptno column to emp table.
Add a foreign key constraint for deptno column.
Create table called newemp using single command as well as to get data.
Create table called newemp. This table should contain only empno, ename, dname.
Delete rows of employees who are working in company for more than 2 years.
Provide a commission to employees who are not earning any commission.
Increment commission by 10% of salary for employees who have commission.
Display employee name and department name for each employee.
Display employee number, name and location of the department in which they are
working.
Display ename, dname even if there are no employees working in a particular department
(use outer join).
Display employee name and his manager name.
Display the department name along with total salary in each department.
Display the department name and total number employees in each department.