SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

Part 1: Basic Retrieval and Filtering

No. Question SQL Concept
1 Display all columns from EMP. SELECT *
2 Display only ENAME and SAL. Column selection
3 Display employees whose salary is greater than 2000. WHERE, >
4 Display employees working in department 20. WHERE, =
5 Display employees whose job is CLERK. WHERE, text filtering
6 Display employees whose salary is less than or equal to 1500. <=

Part 2: DISTINCT, AND, OR and NOT

No. Question SQL Concept
1 Display all unique job names. DISTINCT
2 Display employees working in department 10 or 20. OR
3 Display employees whose salary is greater than 1000 and less than 3000. AND
4 Display employees who are not managers. NOT / <>
5 Display employees in department 30 whose salary is greater than 1400. Multiple conditions
6 Display employees whose job is CLERK or ANALYST. OR

Part 3: BETWEEN, IN, LIKE and NULL

No. Question SQL Concept
1 Display employees whose salary is between 1000 and 3000, inclusive. BETWEEN
2 Display employees working in departments 10, 20 and 30. IN
3 Display employees whose names start with S. LIKE 'S%'
4 Display employees whose names end with N. LIKE '%N'
5 Display employees whose names contain A. LIKE '%A%'
6 Display employees whose names have A as the second character. LIKE '_A%'
7 Display employees who receive commission (COMM IS NOT NULL). IS NOT NULL
8 Display employees who do not have commission recorded (COMM IS NULL). IS NULL

Part 4: Sorting and Limiting Results

No. Question SQL Concept
1 Display employees in ascending order of salary. ORDER BY ASC
2 Display employees in descending order of salary. ORDER BY DESC
3 Display employees alphabetically by name. ORDER BY ENAME
4 Display employees by department and salary in descending order. Multiple sort columns
5 Display the three highest-paid employees. FETCH FIRST
6 Display the five lowest-paid employees. FETCH FIRST

Part 5: SQL Functions

No. Question SQL Concept
1 Display employee names in uppercase. UPPER()
2 Display employee names in lowercase. LOWER()
3 Display the length of each employee's name. LENGTH()
4 Display the first three characters of each employee's name. SUBSTR()
5 Display salary increased by 500. Arithmetic expressions
6 Display annual salary, assuming salary is monthly. SAL * 12
7 Display salary rounded to the nearest hundred. ROUND()
8 Display hire dates in DD-MON-YYYY format. TO_CHAR()
9 Display commission as zero when it is NULL. NVL()
10 Display the current date. SYSDATE

Part 6: Aggregate Functions, GROUP BY and HAVING

No. Question SQL Concept
1 Count the total number of employees. COUNT(*)
2 Display the total salary of all employees. SUM()
3 Display the average salary. AVG()
4 Display the highest salary. MAX()
5 Display the lowest salary. MIN()
6 Count employees in each department. GROUP BY
7 Display average salary for each job. GROUP BY
8 Display departments having more than three employees. HAVING
9 Display jobs whose average salary is greater than 2000. GROUP BY, HAVING
10 Display departments where total salary exceeds 5000. SUM(), HAVING

Part 7: SQL Joins

Use the EMP and DEPT tables.

No. Practice question SQL concept
1 Display employee names with their department names. INNER JOIN
2 Display all employees, including those without a matching department. LEFT JOIN
3 Display all departments, including those without employees. RIGHT JOIN
4 Display all employees and departments, whether matched or not. FULL OUTER JOIN
5 Display employees working in the SALES department. Join with WHERE
6 Display employees and their department locations. Join multiple columns
7 Display employee names with their managers' names. Self join
8 Count employees in each department, including departments with zero employees. LEFT JOIN, GROUP BY