WHERE CLAUSE, ORDER BY CLAUSE AND ALIAS

54 programming challenges to help you master the fundamentals of DATABASE MANAGEMENT SYSTEM USING ORACLE.

1

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

  • Display employees whose salary is greater than 3000.

Start Solving
2

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees working in department 10.

Start Solving
3

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose job is 'CLERK'.

Start Solving
4

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees hired after 01-JAN-81.

Start Solving
5

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is less than 1500.

Start Solving
6

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is > 2000 and job is 'MANAGER'.

Start Solving
7

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees working in department 10 or 20.

Start Solving
8

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose job is 'SALESMAN' and salary > 1500.

Start Solving
9

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees who are either CLERK or ANALYST.

Start Solving
10

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees hired before 1982 and working in department 30.

Start Solving
11

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees working in departments 10, 20, or 30.

Start Solving
12

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose job is not MANAGER or CLERK.

Start Solving
13

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose department is not 10.

Start Solving
14

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is between 1000 and 3000.

Start Solving
15

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees hired between 01-JAN-81 and 31-DEC-81.

Start Solving
16

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display all employees ordered by salary in ascending order.

Start Solving
17

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display all employees ordered by salary in descending order.

Start Solving
18

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees ordered by hire date.

Start Solving
19

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees ordered by department number and salary.

Start Solving
20

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees ordered by job (ascending) and salary (descending).

Start Solving
21

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee names ordered alphabetically.

Start Solving
22

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees of department 30 ordered by salary.

Start Solving
23

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees ordered by annual salary.

Start Solving
24

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees ordered by commission.

Start Solving
25

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees ordered by job and name.

Start Solving
26

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name as Employee_Name.

Start Solving
27

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display salary as Monthly_Salary.

Start Solving
28

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and job with aliases.

Start Solving
29

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display department number as Dept_No.

Start Solving
30

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and salary with meaningful aliases.

Start Solving
31

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display commission as Incentive.

Start Solving
32

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name, job, and salary with aliases, ordered by job.

Start Solving
33

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees of department 30 whose salary > 1500, ordered by salary.

Start Solving
34

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose commission is NOT NULL, ordered by commission.

Start Solving
35

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose salary is between 1500 and 3000, ordered by salary.

Start Solving
36

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and department, ordered by department and name.

Start Solving
37

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose name starts with 'S', ordered by salary.

Start Solving
38

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees hired before 1982 with aliases, ordered by hire date.

Start Solving
39

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name as Name and salary as Pay for department 10.

Start Solving
40

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose commission is NULL, ordered by salary descending.

Start Solving
41

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and annual salary, ordered by annual salary.

Start Solving
42

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose commission is NULL but job is SALESMAN.

Start Solving
43

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose manager is NULL and salary > 4000.

Start Solving
44

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees who do not earn commission and work in department 20.

Start Solving
45

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose commission is NOT NULL and department is 30.

Start Solving
46

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose commission is NULL or salary > 3000.

Start Solving
47

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose commission is NULL and salary > 2000.

Start Solving
48

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose manager is not NULL.

Start Solving
49

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees who do not have a manager.

Start Solving
50

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees who are getting commission.

Start Solving
51

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employees whose commission is NULL.

Start Solving
52

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display job and salary using aliases.

Start Solving
53

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display employee name and department number with aliases.

Start Solving
54

EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

DEPT (DEPTNO, DNAME, LOC)

Display hire date as Joining_Date.

Start Solving