SQL_Sheet

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