ORACLE SQL
Oracle SQL Practice
Oracle SQL Practice
Assumed Table Structure (Standard Oracle)
EMP: EMPNO, ENAME. JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
DEPT: DEPTNO, DNAME, LOC
SELECT QUERY (1–10)
- Display all columns of the EMP table.
- Display EMPNO, ENAME, and SAL of all employees.
- Display unique job titles from EMP.
- Display employee names along with their department numbers.
- Display all department details.
- Display distinct department numbers from EMP.
- Display employee name and annual salary (SAL × 12).
- Display employee name, salary, and commission.
- Display job and salary of all employees.
- Display employees along with their manager numbers.
WHERE CLAUSE (1–60)
- Display employees whose salary is greater than 3000.
- Display employees working in department 10.
- Display employees whose job is 'CLERK'.
- Display employees hired after 01-JAN-81.
- Display employees whose salary is less than 1500.
-
Display employees whose salary is > 2000 and job is 'MANAGER'.
- Display employees working in department 10 or 20.
- Display employees whose job is 'SALESMAN' and salary > 1500.
- Display employees who are either CLERK or ANALYST.
-
Display employees hired before 1982 and working in department 30.
- Display employees working in departments 10, 20, or 30.
- Display employees whose job is not MANAGER or CLERK.
- Display employees whose department is not 10.
- Display employees whose salary is between 1000 and 3000.
- Display employees hired between 01-JAN-81 and 31-DEC-81.
- Display employees whose name starts with 'A'.
- Display employees whose name ends with 'N'.
- Display employees whose name contains 'LL'.
- Display employees whose name has exactly 5 characters.
- Display employees whose second character is 'A'.
- Display all employees ordered by salary in ascending order.
- Display all employees ordered by salary in descending order.
- Display employees ordered by hire date.
- Display employees ordered by department number and salary.
-
Display employees ordered by job (ascending) and salary (descending).
- Display employee names ordered alphabetically.
- Display employees of department 30 ordered by salary.
- Display employees ordered by annual salary.
- Display employees ordered by commission.
- Display employees ordered by job and name.
- Display employee name as `Employee_Name`.
- Display salary as `Monthly_Salary`.
- Display salary × 12 as `Annual_Salary`.
- Display employee name and job with aliases.
- Display department number as `Dept_No`.
- Display employee name and salary with meaningful aliases.
- Display commission as `Incentive`.
- Display hire date as `Joining_Date`.
- Display employee name and department number with aliases.
- Display job and salary using aliases.
- Display employees whose commission is NULL.
- Display employees who are getting commission.
- Display employees who do not have a manager.
- Display employees whose manager is not NULL.
-
Display employees whose commission is NULL and salary > 2000.
- Display employees whose commission is NULL or salary > 3000.
-
Display employees whose commission is NOT NULL and department is 30.
-
Display employees who do not earn commission and work in department
20.
- Display employees whose manager is NULL and salary > 4000.
- Display employees whose commission is NULL but job is SALESMAN.
-
Display employee name and annual salary, ordered by annual salary.
-
Display employees whose commission is NULL, ordered by salary
descending.
-
Display employee name as Name and salary as Pay for department 10.
-
Display employees hired before 1982 with aliases, ordered by hire
date.
-
Display employees whose name starts with 'S', ordered by salary.
-
Display employee name and department, ordered by department and name.
-
Display employees whose salary is between 1500 and 3000, ordered by
salary.
-
Display employees whose commission is NOT NULL, ordered by commission.
-
Display employees of department 30 whose salary > 1500, ordered by
salary.
-
Display employee name, job, and salary with aliases, ordered by job.
LIKE OPERATOR (1–60)
- Display employees whose name starts with
A.
- Display employees whose name starts with
S.
- Display employees whose name ends with
N.
- Display employees whose name ends with
R.
- Display employees whose name contains
LL.
- Display employees whose name contains
AR.
- Display employees whose job starts with
M.
- Display employees whose job ends with
AN.
- Display employees whose job contains
CL.
- Display departments whose location starts with
N.
-
Display employees whose name has exactly
4 characters.
-
Display employees whose name has exactly
5 characters.
-
Display employees whose name has
A as the
second character.
-
Display employees whose name has
L as the
third character.
-
Display employees whose job has exactly 6 characters.
-
Display employees whose name starts with any letter but ends with
N.
-
Display employees whose job starts with any one character and ends
with
ER.
-
Display employees whose name has
A as the first and
N as the last character.
-
Display employees whose name has exactly
2 characters before 'E'.
-
Display employees whose name has exactly
1 character after 'A'.
-
Display employees whose name starts with
A and ends with
N.
- Display employees whose name contains
E anywhere.
- Display employees whose name does not contain
A.
- Display employees whose job contains
MAN.
- Display employees whose job does not start with
C.
-
Display employees whose name contains two
L
consecutively.
-
Display employees whose name starts with
J and has
S anywhere.
-
Display employees whose job starts with
S and ends with
AN.
-
Display employees whose name contains
A as the second
last character.
-
Display employees whose name contains exactly one character between
A and E.
-
Display employees whose name starts with
S and salary
> 2000.
-
Display employees whose job ends with
MAN and department
is 20.
-
Display employees whose name contains
A and commission is
NULL.
-
Display employees whose job starts with
C and salary <
1500.
-
Display employees whose name ends with
S and hired after
1981.
-
Display employees whose name starts with
M and department
is not 10.
-
Display employees whose job contains
ER and salary
between 2000 and 3000.
-
Display employees whose name starts with
A or job starts
with S.
-
Display employees whose name contains
I and manager is
NOT NULL.
-
Display employees whose name ends with
N and commission
is NOT NULL.
-
Display employee name as
Name whose name starts with
S, ordered alphabetically.
-
Display employee name and salary as
Pay whose name
contains A, ordered by salary.
-
Display employees whose job starts with
M, ordered by job
and salary.
-
Display employees whose name ends with
E, ordered by hire
date.
-
Display employee name and annual salary for employees whose name
starts with
J.
-
Display employees whose name contains
R, ordered by
department number.
-
Display employee name as
Employee whose job ends with
AN.
-
Display employees whose name starts with
K, ordered by
salary descending.
-
Display employees whose name contains
L, ordered by name
and salary.
-
Display employees whose job starts with
C, ordered by
job.
-
Display employees whose name has exactly
6 characters and ends with
R.
-
Display employees whose name has
A as the
third character from start.
-
Display employees whose name has
E as the
second character from end.
-
Display employees whose name contains at least
two characters before 'A'.
-
Display employees whose job contains exactly one 'A'.
- Display employees whose name does not start with a vowel.
- Display employees whose job does not contain
ER.
-
Display employees whose name starts and ends with the same character.
-
Display employees whose name contains
A and
E in any order.
-
Display employees whose name contains
A but not as the
first character.
GROUP FUNCTION, GROUP BY, HAVING (1–70)
- Display the total number of employees.
- Display the total salary of all employees.
- Display the average salary of all employees.
- Display the highest salary.
- Display the lowest salary.
- Display the total number of departments in EMP table.
- Display the number of employees who are managers.
- Display the total commission paid to employees.
- Display the average commission.
- Display the number of employees who are getting commission.
- Display department-wise total salary.
- Display department-wise average salary.
- Display department-wise number of employees.
- Display job-wise number of employees.
- Display job-wise total salary.
- Display job-wise maximum salary.
- Display job-wise minimum salary.
- Display department-wise maximum salary.
- Display department-wise minimum salary.
- Display department-wise average commission.
- Display department-wise job-wise number of employees.
- Display department-wise job-wise total salary.
- Display department-wise job-wise average salary.
- Display job-wise department-wise maximum salary.
- Display job-wise department-wise minimum salary.
- Display department and job having highest total salary.
- Display department and job having lowest average salary.
- Display department-wise job-wise total commission.
- Display department-wise job-wise employee count.
- Display department and job having maximum number of employees.
-
Display department-wise total salary for department numbers greater
than 10.
- Display job-wise employee count where salary > 2000.
-
Display department-wise average salary for employees hired after 1981.
- Display job-wise total salary for CLERK and MANAGER.
-
Display department-wise number of employees where commission is NOT
NULL.
- Display job-wise average salary where department is 20.
- Display department-wise total salary excluding department 30.
- Display job-wise max salary where salary > 1500.
- Display department-wise employee count where job is SALESMAN.
- Display job-wise total commission where commission is NOT NULL.
- Display departments having more than 3 employees.
- Display jobs having average salary greater than 2500.
- Display departments whose total salary is greater than 10000.
- Display jobs having more than 2 employees.
- Display departments having maximum salary greater than 4000.
- Display jobs whose minimum salary is less than 1000.
-
Display department-wise total salary where employee count > 4.
- Display job-wise average salary where total salary > 5000.
- Display departments having employees with commission.
- Display jobs having more than one manager.
- Display department-wise count of employees getting commission.
- Display job-wise total commission.
- Display department-wise average commission excluding NULLs.
- Display total commission paid (ignore NULLs).
- Display number of employees without commission.
-
Display department-wise count of employees where manager is NULL.
- Display job-wise average commission.
- Display departments where all employees get commission.
- Display departments where no employee gets commission.
- Display job-wise count of employees not getting commission.
-
Display department number and total salary where average salary is
greater than overall average salary.
- Display job having highest average salary.
- Display department having minimum total salary.
-
Display jobs where max salary is equal to department 20 max salary.
- Display departments having same number of employees.
-
Display jobs whose total salary is more than salary of all CLERKs
combined.
- Display department having highest number of employees.
-
Display jobs having average salary greater than department-wise
average salary.
- Display department where sum of salaries is maximum.
- Display job having minimum average salary.
SUBQUERIES (1–70)
-
Display employees whose salary is greater than JONES.
-
Display employees earning the same salary as SCOTT.
- Display employees hired after ALLEN.
-
Display employees working in the same department as
BLAKE.
-
Display employees having the same job as MILLER.
-
Display employees earning less than the
average salary.
-
Display employees earning more than the
maximum salary of CLERKs.
- Display employee(s) with the minimum salary.
- Display employee(s) with the maximum salary.
-
Display employees working in the department named
ACCOUNTING.
-
Display employees working in departments located in
NEW YORK.
-
Display employees whose salary matches
any salary in department 30.
-
Display employees earning more than all CLERKs.
-
Display employees earning less than any MANAGER.
- Display employees whose job appears in department 20.
-
Display employees whose department has at least one
SALESMAN.
-
Display employees earning the
same salary as any CLERK.
-
Display employees who are not working in departments of
SALESMAN.
-
Display employees earning more than
all employees in department 10.
-
Display employees whose department number is among departments with
more than 3 employees.
-
Display employees earning more than the
department average salary.
-
Display employees earning the
highest salary in each department.
-
Display employees earning the
lowest salary in each job.
-
Display departments having
total salary greater than department 20.
-
Display employees whose salary equals the
maximum salary of their job.
-
Display employees earning more than the
overall average salary.
-
Display department(s) having the
maximum number of employees.
- Display jobs having minimum average salary.
-
Display employees earning more than the
average salary of CLERKs.
-
Display department(s) where
average salary is highest.
-
Display employees earning more than the
average salary of their own department.
-
Display employees earning the
maximum salary in their department.
-
Display employees earning the
minimum salary in their department.
-
Display employees whose salary is greater than the
average salary of their job.
-
Display employees who are the
only employee in their department.
-
Display departments having employees earning more than
5000.
-
Display employees whose department has
more than 3 employees.
-
Display employees whose job has
more than 2 employees.
-
Display employees working in departments where
no one gets commission.
-
Display employees whose department has
at least one manager.
- Display departments that have employees.
- Display departments that have no employees.
- Display employees who are managers (have subordinates).
- Display employees who are not managers.
-
Display employees working in departments where
SALESMAN exists.
- Display departments where no SALESMAN exists.
-
Display employees whose department has
employees with commission.
-
Display departments where
all employees get commission.
-
Display employees whose department has no CLERKs.
-
Display employees who work in departments located in
CHICAGO.
-
Display department number and average salary of employees whose salary
is greater than 2000.
-
Display job and total salary of employees earning more than the
department average.
-
Display departments having average salary greater than overall average
salary.
-
Display job-wise employee count from employees earning more than 1500.
-
Display departments having more than 2 employees earning commission.
- Display job and maximum salary from employees hired after 1981.
- Display department-wise total salary excluding department 30.
-
Display departments having total salary greater than overall total
salary average.
-
Display job-wise average salary where department has more than 3
employees.
- Display department and job having maximum total salary.
-
Display employees earning the second highest salary.
-
Display employees earning the third highest salary.
-
Display employees whose salary is greater than
50% of employees.
-
Display employees earning more than the
median salary.
-
Display employees whose salary is equal to department-wise
second highest salary.
-
Display employees whose salary is less than
all MANAGERS but more than any CLERK.
-
Display departments where
maximum salary equals minimum salary.
-
Display employees earning more than the
average salary of departments located in DALLAS.
-
Display jobs where average salary is greater than
overall average salary.
-
Display employees whose salary is greater than salary of their
manager.