12. Display the department numbers of all departments employing a clerk.
(B) Group SELECT Command Questions
1. Display the maximum, minimum and average salary and commission earned.
2. Display the department number, total salary payout and total commission payout for each department.
3. Display the department number, total salary payout and total commission payout for each department
that pays at least one employee commission.
4. Display the department number and number of clerks in each department.
5. Display the department number and total salary of employees in each department that employs four
or more people.
6. Display the employee number of each employee who manages other employees with the number of
people he or she manages.
(C) Join SELECT Command Questions
1. Display the name of each employee with his department name.
2. Display a list of all departments with the employees in each department.
4. Display the names of each employee with the name of his/her boss.
5. Display the names of each employee with the name of his/her boss with a blank for the boss of the
president.
6. Display the employee number and name of each employee who manages other employees with the
number of people he or she manages.
7. Repeat the display for the last question, but this time display the rows in descending order of the
number of employees managed.
1. Display the names and job titles of all employees with the same job as Jones.
2. Display the names and department name of all employees working in the same city as Jones.
3. Display the name of the employee whose salary is the lowest.
4. Display the names of all employees except the lowest paid.
5. Display the names of all employees whose job title is the same as anyone in the sales dept.
6. Display the names of all employees who work in a department that employs an analyst.
7. Display the names of all employees with their job title, their current salary and their salary following
a 10% pay rise for clerks and a 7% pay rise for all other employees.
8. Display the names of all employees with their salary and commission earned. Employees with a null
commission
eld should have 0 in the commission column.
9. Display the names of ALL employees with the total they have earned (ie. salary plus commission).
10. Repeat the display for the last question but this time display in descending order of earnings.
Second highes salary
select sal from
(select rownum n,a.* from
( select distinct sal from emp order by sal desc) a)
where n = 2;
Windows function
Function Purpose
ROW_NUMBER Sequential numbering
RANK Ranking
DENSE_RANK Dense ranking
LEAD Access next row
LAG Access previous row
SUM OVER Running totals
AVG OVER Partition averages
Rank and Dense rank
select ename, deptno,sal, RANK() OVER (ORDER BY SAL desc) RANK1 FROM emp;
select ename, deptno,sal, RANK() OVER (PARTITION BY deptno ORDER BY SAL desc) RANK1
FROM emp;
select ename, deptno,sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY SAL desc) RANK1
FROM emp;
Windows function
SELECT
ename,
deptno,
sal,
LAG(sal) OVER (
partition by deptno
ORDER BY sal) as prev_sal
FROM emp;
SELECT
ename,
deptno,
sal,
Lead(sal) over (
partition by deptno
ORDER BY sal) as next_sal
FROM emp;
SELECT
ename,
deptno,sal,
AVG(sal) OVER (partition by deptno) AS overall_avg
FROM emp;
SELECT
ename,
deptno,sal,
AVG(sal) OVER (partition by deptno) AS overall_avg
FROM emp;
SELECT
ename,
deptno,sal,
sum(sal) OVER (order by sal desc) AS overall_avg
FROM emp;
WITH emp1 AS (SELECT deptno, avg(sal) as avg_sal FROM emp group by deptno
) SELECT * FROM emp1 WHERE avg_sal > 1500;
select * from (select deptno, avg(sal) as avg_sal from
emp group by deptno) where avg_sal>1500;
SELECT ename from emp
ORDER BY dbms_random.value FETCH FIRST 5 ROWS ONLY
Advanced sql
Advanced SQL Topic One-line Description
Window Functions Perform analytics across related rows without collapsing result sets.
CTE (WITH Clause) Create temporary named query blocks for cleaner complex SQL.
Recursive CTE Process hierarchical/tree-like data recursively.
PIVOT / UNPIVOT Convert rows to columns and vice versa for reporting.
MERGE Perform INSERT + UPDATE in single statement (upsert).
Regular Expressions (REGEXP) Advanced pattern matching and text validation.
Analytic Functions Functions like RANK, DENSE_RANK, LEAD, LAG for reporting analytics.
CASE Statement Conditional logic directly inside SQL queries.
Advanced SQL Topic One-line Description
EXISTS / NOT EXISTS Efficient existence-based filtering using subqueries.
Correlated Subqueries Subqueries dependent on outer query rows.
Hierarchical Queries Parent-child traversal using CONNECT BY or recursive logic.
Materialized Views Precomputed query results stored physically for performance.
Partitioning Split huge tables into logical partitions for scalability.
Indexing Concepts Improve query performance using optimized access paths.
Execution Plans Analyze how database executes SQL internally.
Dynamic SQL Construct and execute SQL statements programmatically.
Stored Procedures Reusable database-side procedural programs.
Functions Reusable SQL logic returning calculated values.
Triggers Automatically execute logic during INSERT/UPDATE/DELETE events.
Transactions Control COMMIT, ROLLBACK, and consistency of operations.
Set Operators UNION, INTERSECT, MINUS/EXCEPT for combining result sets.
Temporary Tables Store intermediate processing data temporarily.
Views Logical virtual tables built from SQL queries.
JSON/XML Functions Process semi-structured data inside SQL databases.
Parallel Query Execute large queries using multiple processors simultaneously.
Query Optimization Techniques to improve SQL performance and reduce cost.
Star Schema Queries SQL patterns used in data warehousing and BI systems.
OLAP Functions Advanced multidimensional analytical operations.
Sampling Retrieve representative subset of large datasets.
Sequence Objects Generate incremental unique numbers automatically.
MERGE INTO emp e
USING emp_stage s
ON ([Link] = [Link])
WHEN MATCHED THEN
Advanced SQL Topic One-line Description
UPDATE
SET [Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (
empno,
ename,
sal
)
VALUES (
[Link],
[Link],
[Link]
);
Case statement
SELECT
ename,
sal,
CASE
WHEN sal >= 4000 THEN 'HIGH'
WHEN sal >= 2000 THEN 'MEDIUM'
ELSE 'LOW'
END AS salary_grade
FROM emp;
Pivot
SELECT *
FROM (
SELECT
name,
Advanced SQL Topic One-line Description
acc_no,
amount,
debit_credit
FROM bank_stmt
)
PIVOT (
SUM(amount)
FOR debit_credit IN (
'DEBIT' AS debit,
'CREDIT' AS credit
)
);
CREATE GLOBAL TEMPORARY TABLE temp_emp (
empno NUMBER,
ename VARCHAR2(100)
ON COMMIT PRESERVE ROWS;
CREATE GLOBAL TEMPORARY TABLE temp_emp (
empno NUMBER,
ename VARCHAR2(100)
)
ON COMMIT delete rows;
Parallel query
SELECT /*+ PARALLEL(emp,8) */
deptno,
SUM(sal)
FROM emp
GROUP BY deptno;
ALTER TABLE emp PARALLEL 8;
Table altered.
SQL> ALTER TABLE emp non PARALLEL;
ALTER TABLE emp non PARALLEL
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> ALTER TABLE emp nonPARALLEL;
ALTER TABLE emp nonPARALLEL
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> ALTER TABLE emp noPARALLEL;
Table altered.
SQL>