Here is a table filled with Oracle SQL queries, from most basic to most complex. There are descriptions, keywords, code comments and code examples.
| DOMAIN | DOMAIN NAME | CONCEPT | WHAT IT IS | WHEN TO USE | CODE EXAMPLE | KEYWORDS | CODE COMMENTS |
|---|---|---|---|---|---|---|---|
| 1 | Basic SELECT and Filtering | AND, OR, NOT | Combine multiple conditions using logical operators to create complex filters. | AND requires all conditions true, OR requires at least one true, NOT negates a condition. Use parentheses for complex logic. | SELECT * FROM employees WHERE (dept = 1 OR dept = 2) AND salary > 70000; | AND, OR, NOT, logical, combine conditions | Logical operators combine conditions: AND requires both true, OR requires at least one true, parentheses control order of operations |
| 1 | Basic SELECT and Filtering | BETWEEN, IN, LIKE | Filter rows using range checks (BETWEEN), list membership (IN), or pattern matching (LIKE). | BETWEEN for ranges (salary BETWEEN 50000 AND 80000), IN for lists (dept IN (1,2,3)), LIKE for patterns (name LIKE John%). | SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000; WHERE dept IN (1,2,3); WHERE name LIKE John%; | BETWEEN, IN, LIKE, wildcard, pattern, range | BETWEEN filters ranges (50000 to 80000), IN filters lists (dept 1, 2, or 3), LIKE finds patterns (% is wildcard for any characters) |
| 1 | Basic SELECT and Filtering | Basic operators | Compare values to filter rows using equality, inequality, and comparison operators. | Use these to filter data: = for exact match, != or <> for not equal, < or > for less or greater than. Example: WHERE salary > 50000. | SELECT * FROM employees WHERE salary > 50000 AND department = Sales; | operators, equals, greater than, less than, comparison | Comparison operators filter data: = means equals, > means greater than, < means less than, >= greater or equal, <= less or equal |
| 1 | Basic SELECT and Filtering | Column selection and aliases (AS) | Choose which columns to display and rename them for clarity in results. | Use AS to rename columns (example: SELECT salary AS annual_pay) to make results more readable and user-friendly. | SELECT salary AS annual_pay, bonus AS yearly_bonus FROM employees; | AS, alias, rename, column name | AS renames columns for display – salary becomes annual_pay, bonus becomes yearly_bonus – makes output more readable |
| 1 | Basic SELECT and Filtering | DISTINCT | Remove duplicate rows from query results to show only unique values. | Use DISTINCT when you want unique values only, like finding all unique departments: SELECT DISTINCT department FROM employees. | SELECT DISTINCT department FROM employees; | DISTINCT, unique, duplicates, remove | DISTINCT removes duplicate rows – if 5 employees are in Sales, this returns Sales only once instead of 5 times |
| 1 | Basic SELECT and Filtering | LIMIT results (FETCH FIRST, ROWNUM) | Restrict the number of rows returned by a query to a specific count. | In Oracle 12c+, use FETCH FIRST 10 ROWS ONLY. In older versions, use WHERE ROWNUM <= 10. Use when you need top N results. | SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY; | FETCH FIRST, ROWNUM, limit, top N, top 10 | FETCH FIRST limits how many rows return – FETCH FIRST 5 ROWS ONLY gives you just the first 5 results |
| 1 | Basic SELECT and Filtering | ORDER BY (ASC, DESC) | Sort query results in ascending (lowest to highest) or descending (highest to lowest) order. | Use ORDER BY to sort results. ASC is default (A-Z, 1-10), DESC reverses it (Z-A, 10-1). Example: ORDER BY salary DESC for highest earners first. | SELECT name, salary FROM employees ORDER BY salary DESC; | ORDER BY, sort, ASC, DESC, ascending, descending | ORDER BY sorts results – DESC means highest to lowest (descending), ASC means lowest to highest (ascending, default) |
| 1 | Basic SELECT and Filtering | SELECT, FROM, WHERE | Retrieve specific columns from a table with optional filtering conditions. | Use SELECT to choose columns, FROM to specify the table, and WHERE to filter rows based on conditions. | SELECT employee_name, salary FROM employees WHERE department = Sales; | SELECT, FROM, WHERE, filter, retrieve, query | SELECT picks columns to show, FROM specifies the table, WHERE filters which rows to include based on conditions |
| 2 | Working with NULL Values | COALESCE | Standard SQL function that returns the first non-NULL value from a list of expressions. | COALESCE checks multiple columns and returns first non-NULL. Example: COALESCE(email, phone, emergency_contact, No contact) for best available contact method. | SELECT COALESCE(email, phone, emergency_contact, No contact) AS contact FROM employees; | COALESCE, first non-null, multiple fallback | COALESCE checks multiple columns left to right, returns first non-NULL – tries email, then phone, then emergency_contact, finally No contact |
| 2 | Working with NULL Values | IS NULL, IS NOT NULL | Check if a column contains NULL (missing or unknown) or has a value. | Use IS NULL to find missing data, IS NOT NULL to find populated data. Never use = NULL because it will not work. Example: WHERE email IS NULL. | SELECT * FROM employees WHERE email IS NULL; WHERE phone IS NOT NULL; | IS NULL, IS NOT NULL, missing, empty, check null | IS NULL finds rows where email is missing (NULL), IS NOT NULL finds rows where phone has a value – never use = NULL |
| 2 | Working with NULL Values | NULLIF | Returns NULL if two expressions are equal, otherwise returns the first expression. | NULLIF converts specific values to NULL. Common uses: NULLIF(column, 0) to avoid division by zero, or NULLIF(column, empty string) to convert empty strings to NULL. | SELECT 100 / NULLIF(bonus, 0) AS ratio FROM employees; | NULLIF, convert to null, division by zero | NULLIF converts specific value to NULL – NULLIF(bonus, 0) makes 0 become NULL so 100 divided by 0 becomes 100 divided by NULL (which is NULL, not an error) |
| 2 | Working with NULL Values | NVL | Oracle function that replaces NULL with a specified default value (2 arguments only). | NVL(column, default) returns default if column is NULL. Example: NVL(bonus, 0) converts NULL bonuses to 0 for calculations. | SELECT employee_name, NVL(bonus, 0) AS bonus FROM employees; | NVL, null value, replace null, default | NVL replaces NULL with a default – NVL(bonus, 0) means if bonus is NULL, use 0 instead (2 arguments only) |
| 2 | Working with NULL Values | NVL2 | Oracle function that returns different values based on whether expression is NULL or not (3 arguments). | NVL2(expr, if_not_null, if_null) allows different handling. Example: NVL2(salary, salary*1.1, 0) gives 10% raise if salary exists, else 0. | SELECT NVL2(salary, salary * 1.1, 0) AS adjusted_salary FROM employees; | NVL2, null if not null, conditional null | NVL2 gives different values for NULL vs NOT NULL – NVL2(salary, salary*1.1, 0) means if salary exists multiply by 1.1, if NULL use 0 |
| 3 | Aggregate Functions and GROUP BY | COUNT(DISTINCT) | Count only unique (non-duplicate) values in a column. | Use COUNT(DISTINCT column) to count unique values. Example: COUNT(DISTINCT department_id) shows how many different departments have employees. | SELECT COUNT(DISTINCT department_id) AS unique_departments FROM employees; | COUNT DISTINCT, unique count, distinct count | COUNT(DISTINCT department_id) counts unique departments – if 20 employees work in 5 departments, returns 5 (not 20) |
| 3 | Aggregate Functions and GROUP BY | COUNT, SUM, AVG, MIN, MAX | Calculate summary statistics across multiple rows: count, total, average, minimum, maximum. | Use these to summarize data. COUNT(*) counts rows, SUM adds values, AVG calculates mean, MIN and MAX find extremes. Example: SELECT AVG(salary) FROM employees. | SELECT COUNT(*) AS total, SUM(salary) AS payroll, AVG(salary) AS avg_sal, MIN(salary), MAX(salary) FROM employees; | COUNT, SUM, AVG, MIN, MAX, aggregate, summary | Aggregates summarize data: COUNT counts rows, SUM adds values, AVG calculates average, MIN finds smallest, MAX finds largest |
| 3 | Aggregate Functions and GROUP BY | GROUP BY (single and multiple columns) | Organize rows into groups based on column values to calculate aggregates per group. | GROUP BY creates groups for aggregation. Single: GROUP BY department. Multiple: GROUP BY department, location. Each unique combination becomes a group. | SELECT department, COUNT(*) AS headcount, AVG(salary) FROM employees GROUP BY department; | GROUP BY, group, aggregate by, summarize | GROUP BY creates groups for aggregation – GROUP BY department makes one row per department with COUNT and AVG for each |
| 3 | Aggregate Functions and GROUP BY | HAVING clause | Filter groups AFTER aggregation (unlike WHERE which filters BEFORE aggregation). | Use HAVING to filter aggregated results. Example: HAVING COUNT(*) > 5 shows only departments with more than 5 employees. WHERE filters rows, HAVING filters groups. | SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; | HAVING, filter groups, filter aggregates | HAVING filters groups after aggregation – HAVING COUNT(*) > 5 shows only departments with more than 5 employees |
| 3 | Aggregate Functions and GROUP BY | WHERE vs HAVING | WHERE filters individual rows before grouping; HAVING filters groups after aggregation. | WHERE filters raw data first (WHERE salary > 50000), then GROUP BY runs. HAVING filters the grouped results (HAVING AVG(salary) > 70000). Order: WHERE then GROUP BY then HAVING. | SELECT dept FROM employees WHERE salary > 50000 GROUP BY dept HAVING AVG(salary) > 70000; | WHERE vs HAVING, filter before group, filter after group | WHERE filters individual rows first (salary > 50000), then GROUP BY groups them, then HAVING filters groups (AVG > 70000) |
| 4 | JOINs | FULL OUTER JOIN | Returns ALL rows from both tables, with NULL for non-matching rows on either side. | Use FULL OUTER JOIN to see everything from both tables. Shows all employees AND all departments, with NULLs where there is no match on either side. | SELECT e.name, d.dept_name FROM employees e FULL OUTER JOIN departments d ON e.dept_id = d.dept_id; | FULL OUTER JOIN, full join, keep both tables, all from both | FULL OUTER JOIN keeps everything from both tables – shows all employees AND all departments with NULLs where no match exists on either side |
| 4 | JOINs | INNER JOIN | Returns only rows where there is a match in BOTH tables. | Use INNER JOIN when you want only matching records. Example: employees INNER JOIN departments shows only employees who have a valid department assigned. | SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id; | INNER JOIN, join, matching rows, intersection | INNER JOIN returns only rows with matches in both tables – employee name gets matched with department name via dept_id |
| 4 | JOINs | JOIN conditions vs WHERE conditions | JOIN ON defines how tables relate; WHERE filters the final result. | Put relationship logic in ON clause (ON e.dept_id = d.dept_id), put filtering in WHERE clause (WHERE salary > 50000). Keeps queries clear and performant. | SELECT * FROM employees e JOIN departments d ON e.dept_id = d.dept_id WHERE e.salary > 70000; | ON vs WHERE, join condition, filter condition | ON clause defines table relationship (how to match), WHERE clause filters final results (which rows to keep) – keep them separate for clarity |
| 4 | JOINs | LEFT JOIN (LEFT OUTER JOIN) | Returns ALL rows from left table, plus matching rows from right table (NULL if no match). | Use LEFT JOIN to keep all records from the first table. Example: employees LEFT JOIN projects shows all employees even if they are not on any project (project columns = NULL). | SELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id; | LEFT JOIN, LEFT OUTER JOIN, keep left table, all from left | LEFT JOIN keeps all employees (left table) even if department (right table) is NULL – shows all employees, department name is NULL if no match |
| 4 | JOINs | Multiple table joins | Combine three or more tables in a single query to gather related data. | Chain multiple JOINs to combine many tables. Example: employees JOIN departments JOIN projects to show employee, their department, and projects they lead. | SELECT e.name, d.dept_name, p.project_name FROM employees e JOIN departments d ON e.dept_id = d.dept_id JOIN projects p ON e.emp_id = p.lead_id; | multiple joins, three table join, chain joins | Multiple JOINs chain together – first join employees to departments, then join result to projects – combines data from 3 tables |
| 4 | JOINs | RIGHT JOIN (RIGHT OUTER JOIN) | Returns ALL rows from right table, plus matching rows from left table (NULL if no match). | Use RIGHT JOIN to keep all records from the second table. Less common than LEFT JOIN. Example: employees RIGHT JOIN departments shows all departments even if empty. | SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id; | RIGHT JOIN, RIGHT OUTER JOIN, keep right table, all from right | RIGHT JOIN keeps all departments (right table) even if no employees (left table) – shows all departments, employee name is NULL if empty |
| 4 | JOINs | Self-joins | Join a table to itself, typically to show hierarchical relationships like employee-manager. | Use self-join when a table references itself. Example: employees e JOIN employees m ON e.manager_id = m.employee_id shows each employee with their manager name. | SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id; | self-join, self join, employee manager, hierarchy | Self-join joins table to itself using aliases (e for employee, m for manager) – e.manager_id = m.employee_id shows each employee with their manager name |
| 5 | Subqueries | Correlated subqueries | Subquery that references columns from the outer query, running once per outer row. | Use to compare each row to its own group. Example: WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id) finds above-department-average earners. | SELECT * FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id); | correlated subquery, correlated, references outer | Correlated subquery references outer query (e.dept_id) – runs once per row, compares each employee salary to their own department average |
| 5 | Subqueries | EXISTS and NOT EXISTS | Check if a subquery returns any rows (TRUE or FALSE) without caring about the actual values. | Use EXISTS when checking for presence. Example: WHERE EXISTS (SELECT 1 FROM projects WHERE lead_id = e.id) finds employees who lead at least one project. Faster than IN. | SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM projects p WHERE p.lead_id = e.emp_id); | EXISTS, NOT EXISTS, check existence, check presence | EXISTS checks if subquery returns any rows – finds employees where at least one project exists with matching lead_id (faster than IN) |
| 5 | Subqueries | IN with subqueries | Filter rows where a column value matches any value from a subquery result list. | Use IN when filtering against multiple values. Example: WHERE dept_id IN (SELECT dept_id FROM depts WHERE budget > 500000) finds employees in high-budget departments. | SELECT * FROM employees WHERE dept_id IN (SELECT dept_id FROM departments WHERE budget > 500000); | IN subquery, IN, filter list, match list | IN with subquery filters against list – subquery gets dept_ids where budget > 500000, then finds employees in those departments |
| 5 | Subqueries | Subqueries in FROM clause (inline views) | Treat a subquery as a temporary table for complex calculations or pre-filtering. | Use when you need to aggregate before joining or filtering. Example: SELECT * FROM (SELECT dept, AVG(salary) FROM emp GROUP BY dept) WHERE avg > 70000. | SELECT * FROM (SELECT dept, AVG(salary) AS avg_sal FROM employees GROUP BY dept) WHERE avg_sal > 70000; | inline view, subquery FROM, derived table | Subquery in FROM creates temporary table – first calculates AVG salary per dept, then filters to show only depts with avg > 70000 |
| 5 | Subqueries | Subqueries in SELECT clause (scalar subqueries) | Add a calculated column from another table or aggregation to your result. | Use to add single values per row. Example: SELECT name, (SELECT dept_name FROM depts WHERE id = e.dept_id) AS department FROM employees e. | SELECT name, (SELECT dept_name FROM departments WHERE dept_id = e.dept_id) AS department FROM employees e; | scalar subquery, subquery SELECT, calculated column | Subquery in SELECT adds calculated column – for each employee e, looks up department name from departments table where dept_id matches |
| 5 | Subqueries | Subqueries in WHERE clause | Use a nested query to filter rows based on results from another query. | Use when filtering needs calculated values. Example: WHERE salary > (SELECT AVG(salary) FROM employees) finds above-average earners. | SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); | subquery WHERE, nested query, filter subquery | Subquery in WHERE filters using calculated value – inner query calculates AVG(salary) first, then outer query filters salary > that average |
| 6 | Set Operators | Combining multiple set operations | Chain UNION, INTERSECT, and MINUS together for complex set logic. | Combine operators for complex queries. Example: (query1 UNION query2) INTERSECT query3. Use parentheses to control evaluation order. | (SELECT name FROM sales UNION SELECT name FROM marketing) INTERSECT SELECT name FROM high_performers; | combine set operators, chain operators, complex sets | Parentheses control order – first combines sales and marketing with UNION, then finds INTERSECT with high_performers |
| 6 | Set Operators | INTERSECT | Return only rows that appear in BOTH query results (the overlap). | Use INTERSECT to find commonality. Example: high earners INTERSECT project leads shows people who are both high earners AND leading projects. | SELECT emp_id FROM high_earners INTERSECT SELECT emp_id FROM project_leads; | INTERSECT, overlap, common rows, both queries | INTERSECT returns only rows appearing in BOTH queries – finds employee IDs that are both high earners AND project leads |
| 6 | Set Operators | MINUS (EXCEPT) | Return rows from first query that do NOT appear in second query (the difference). | Use MINUS to find what is in one set but not another. Example: all employees MINUS project leads shows employees not leading any projects. | SELECT emp_id FROM all_employees MINUS SELECT emp_id FROM project_leads; | MINUS, EXCEPT, difference, not in second | MINUS returns rows in first query NOT in second – finds all employees who are NOT project leads (in all_employees but not in project_leads) |
| 6 | Set Operators | UNION | Combine rows from two queries, removing duplicates (stacks vertically). | Use UNION to combine similar result sets into one list without duplicates. Example: current employees UNION former employees gives one unique list of all people. | SELECT name FROM current_employees UNION SELECT name FROM former_employees; | UNION, combine rows, remove duplicates, stack queries | UNION stacks two query results vertically and removes duplicates – combines current and former employees into one unique list |
| 6 | Set Operators | UNION ALL | Combine rows from two queries, keeping all duplicates (faster than UNION). | Use UNION ALL when you want all rows even if duplicates exist, or when you know there are no duplicates. Faster because it skips duplicate checking. | SELECT name FROM current_employees UNION ALL SELECT name FROM former_employees; | UNION ALL, keep duplicates, combine all, fast union | UNION ALL stacks results without removing duplicates – faster than UNION because it skips duplicate checking |
| 7 | Window Functions | FIRST_VALUE and LAST_VALUE | Get the first or last value within a partition or window frame. | Use to find first or last values in ordered sets. Example: FIRST_VALUE(hire_date) shows earliest hire date within each department. | SELECT dept, name, FIRST_VALUE(hire_date) OVER (PARTITION BY dept ORDER BY hire_date) AS first_hire FROM employees; | FIRST_VALUE, LAST_VALUE, first in group, last in group | FIRST_VALUE gets first value in partition – FIRST_VALUE(hire_date) shows earliest hire date within each department (one value repeated for all dept rows) |
| 7 | Window Functions | LAG and LEAD | Access data from previous (LAG) or next (LEAD) row without a self-join. | Use LAG to compare current row to previous, LEAD to compare to next. Example: LAG(salary) shows previous employee salary for month-over-month comparisons. | SELECT name, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees; | LAG, LEAD, previous row, next row, compare rows | LAG looks at previous row – LAG(salary) shows previous employee salary when ordered by hire_date, useful for comparing month-over-month |
| 7 | Window Functions | ORDER BY within window functions | Define the sequence of rows within each partition for ranking or running calculations. | ORDER BY controls row sequence in window. Example: RANK() OVER (ORDER BY salary DESC) ranks by highest salary first. Required for ROW_NUMBER, LAG, LEAD. | SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; | ORDER BY window, window order, sequence window | ORDER BY in window defines sequence – RANK() needs ORDER BY to know how to rank (by salary DESC means highest salary gets rank 1) |
| 7 | Window Functions | PARTITION BY | Divide data into groups (partitions) for separate window calculations per group. | Use PARTITION BY to reset calculations per group. Example: ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary) ranks employees within each department separately. | SELECT dept, name, salary, AVG(salary) OVER (PARTITION BY dept) AS dept_avg FROM employees; | PARTITION BY, partition, reset by group, group window | PARTITION BY divides into groups – AVG(salary) calculated separately per department, resets for each department group |
| 7 | Window Functions | ROWS BETWEEN and RANGE BETWEEN | Define exactly which rows to include in window calculations (window frame). | Use to specify moving windows. Example: AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) calculates 3-day moving average. | SELECT date, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales; | ROWS BETWEEN, RANGE BETWEEN, window frame, moving window | ROWS BETWEEN defines window size – ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means current row plus 2 before it (3-day moving average) |
| 7 | Window Functions | ROW_NUMBER, RANK, DENSE_RANK | Assign sequential numbers or rankings to rows within partitions. | ROW_NUMBER gives unique 1,2,3; RANK allows gaps (1,2,2,4); DENSE_RANK has no gaps (1,2,2,3). Use for rankings, top N per group, pagination. | SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees; | ROW_NUMBER, RANK, DENSE_RANK, ranking, row number | ROW_NUMBER assigns unique numbers (1,2,3), RANK allows gaps for ties (1,2,2,4), DENSE_RANK has no gaps (1,2,2,3) – ordered by salary descending |
| 7 | Window Functions | SUM OVER, AVG OVER, COUNT OVER | Calculate running totals, moving averages, or counts without collapsing rows. | Use aggregates with OVER to keep all detail rows while adding aggregate calculations. Example: SUM(sales) OVER (ORDER BY date) creates running total. | SELECT date, sales, SUM(sales) OVER (ORDER BY date) AS running_total FROM daily_sales; | SUM OVER, AVG OVER, running total, moving average, window aggregate | SUM OVER creates running total – keeps all detail rows but adds cumulative sum ordered by date (running total of sales) |
| 8 | Advanced Functions and CASE Logic | CASE statements | Implement IF-THEN-ELSE logic within SQL queries for conditional values. | Use CASE for conditional logic. Example: CASE WHEN salary > 100000 THEN High WHEN salary > 70000 THEN Medium ELSE Low END AS salary_band. | SELECT name, CASE WHEN salary > 100000 THEN High WHEN salary > 70000 THEN Medium ELSE Low END AS band FROM employees; | CASE, CASE WHEN, IF-THEN-ELSE, conditional | CASE creates IF-THEN-ELSE logic – checks salary, if > 100000 returns High, if > 70000 returns Medium, otherwise returns Low |
| 8 | Advanced Functions and CASE Logic | Conditional aggregation | Combine CASE with aggregates to count or sum only rows meeting specific conditions. | Use CASE inside aggregates for pivot-like reports. Example: COUNT(CASE WHEN status = Active THEN 1 END) counts only active records in one query. | SELECT dept, COUNT(CASE WHEN status = Active THEN 1 END) AS active_count FROM employees GROUP BY dept; | conditional aggregate, CASE aggregate, pivot count | CASE inside COUNT – counts only when status equals Active (THEN 1), ignores other rows – creates pivot-like report in one query |
| 8 | Advanced Functions and CASE Logic | Conversion functions | Convert between data types: string to number, number to string, string to date, general type casting. | TO_NUMBER converts to number, TO_CHAR converts to string (with formatting), TO_DATE converts to date, CAST is general conversion. Use for data type compatibility. | SELECT TO_NUMBER(emp_id_str), TO_CHAR(salary, 999,999), CAST(bonus AS VARCHAR2(10)) FROM employees; | conversion, TO_NUMBER, TO_CHAR, TO_DATE, CAST, convert | Conversion functions change data types – TO_NUMBER makes string into number, TO_CHAR formats number as string with commas, CAST is general conversion |
| 8 | Advanced Functions and CASE Logic | Date functions | Work with dates: get current date, convert formats, add or subtract time, extract parts, truncate. | SYSDATE = current date and time, TO_DATE converts strings, TO_CHAR formats dates, ADD_MONTHS adds months, MONTHS_BETWEEN calculates difference, EXTRACT pulls parts. | SELECT SYSDATE, TO_DATE(2024-01-15, YYYY-MM-DD), ADD_MONTHS(hire_date, 6) FROM employees; | date functions, SYSDATE, TO_DATE, ADD_MONTHS, date math | Date functions work with dates – SYSDATE is current date/time, TO_DATE converts string to date, ADD_MONTHS adds 6 months to hire_date |
| 8 | Advanced Functions and CASE Logic | Numeric functions | Perform math operations: round, truncate, ceiling, floor, modulo, absolute value. | ROUND rounds to decimals, TRUNC cuts off decimals, CEIL rounds up, FLOOR rounds down, MOD gets remainder, ABS gets absolute value. | SELECT ROUND(salary, 2), TRUNC(bonus, 0), CEIL(avg_score), MOD(employee_id, 10) FROM employees; | numeric functions, ROUND, TRUNC, CEIL, FLOOR, MOD | Numeric functions do math – ROUND rounds to 2 decimals, TRUNC cuts decimals, CEIL rounds up, MOD gets remainder (employee_id divided by 10) |
| 8 | Advanced Functions and CASE Logic | String functions | Manipulate text: extract substrings, concatenate, change case, trim, replace, find position, get length. | SUBSTR extracts parts, || concatenates, UPPER and LOWER change case, TRIM removes spaces, REPLACE swaps text, INSTR finds position, LENGTH counts characters. | SELECT UPPER(name), SUBSTR(email, 1, 5), name || email AS combined FROM employees; | string functions, SUBSTR, UPPER, LOWER, CONCAT, TRIM | String functions manipulate text – UPPER makes uppercase, SUBSTR extracts part (first 5 chars), || concatenates strings together |
| 9 | Data Manipulation and DDL | ALTER TABLE | Modify existing table structure: add or drop or modify columns, add or drop constraints. | ALTER TABLE changes table structure. ADD COLUMN adds new column, MODIFY changes data type, DROP COLUMN removes column. Use for schema evolution. | ALTER TABLE employees ADD email VARCHAR2(100); ALTER TABLE employees MODIFY salary NUMBER(12,2); | ALTER TABLE, alter, modify table, add column | ALTER TABLE changes table structure – ADD adds new column (email), MODIFY changes data type (salary from NUMBER(10,2) to NUMBER(12,2)) |
| 9 | Data Manipulation and DDL | CREATE TABLE | Define a new table structure with columns, data types, and constraints. | CREATE TABLE defines structure. Specify columns with data types and constraints. Example: CREATE TABLE emp (id NUMBER PRIMARY KEY, name VARCHAR2(50) NOT NULL). | CREATE TABLE employees (employee_id NUMBER PRIMARY KEY, name VARCHAR2(50) NOT NULL, salary NUMBER(10,2)); | CREATE TABLE, create, define table, DDL | CREATE TABLE defines new table structure – lists columns with data types (NUMBER, VARCHAR2) and constraints (PRIMARY KEY, NOT NULL) |
| 9 | Data Manipulation and DDL | CREATE TABLE AS SELECT (CTAS) | Create a new table and populate it with data from a query in one step. | CTAS copies structure and data: CREATE TABLE new_table AS SELECT * FROM old_table. Copies data but NOT constraints or indexes. Use for backups or creating subsets. | CREATE TABLE high_earners AS SELECT * FROM employees WHERE salary > 100000; | CTAS, CREATE TABLE AS SELECT, copy table, clone table | CREATE TABLE AS SELECT creates new table from query – copies both structure and data from employees where salary > 100000 |
| 9 | Data Manipulation and DDL | Constraints | Enforce data integrity rules: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL, DEFAULT. | PRIMARY KEY uniquely identifies rows, FOREIGN KEY links tables, UNIQUE prevents duplicates, CHECK validates values, NOT NULL requires value, DEFAULT sets automatic value. | CREATE TABLE emp (id NUMBER PRIMARY KEY, name VARCHAR2(50) NOT NULL, dept_id NUMBER, FOREIGN KEY (dept_id) REFERENCES departments(id)); | constraints, PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK | Constraints enforce rules – PRIMARY KEY uniquely identifies rows, FOREIGN KEY links to other table, NOT NULL requires value |
| 9 | Data Manipulation and DDL | DELETE | Remove rows from a table based on conditions. | DELETE FROM table WHERE condition removes matching rows. Always use WHERE unless deleting all rows. Can be rolled back (unlike TRUNCATE). | DELETE FROM employees WHERE termination_date < 2020-01-01; | DELETE, DELETE FROM, remove rows, delete data | DELETE removes rows – WHERE specifies which rows to delete (termination_date before 2020), can be rolled back |
| 9 | Data Manipulation and DDL | DROP TABLE | Permanently delete a table and all its data from the database. | DROP TABLE table_name removes table completely. Cannot be undone. Use TRUNCATE to empty table but keep structure, or DELETE to remove some rows. | DROP TABLE old_employees; | DROP TABLE, drop, delete table, remove table | DROP TABLE permanently deletes entire table and all data – cannot be undone, removes table completely from database |
| 9 | Data Manipulation and DDL | INSERT | Add new rows to a table (single row, multiple rows, or bulk insert). | INSERT INTO table VALUES adds one row. INSERT ALL adds multiple rows. INSERT INTO table SELECT copies from another table. Use for adding data. | INSERT INTO employees (id, name, salary) VALUES (1, John, 50000); | INSERT, INSERT INTO, add row, insert data | INSERT adds new rows – specifies table and columns in parentheses, then VALUES with actual data (id=1, name=John, salary=50000) |
| 9 | Data Manipulation and DDL | MERGE (upsert) | Insert new rows or update existing rows in a single operation based on a condition. | MERGE combines INSERT and UPDATE. Use when loading data that might already exist. Example: MERGE INTO target USING source ON (match_condition) WHEN MATCHED THEN UPDATE WHEN NOT MATCHED THEN INSERT. | MERGE INTO target USING source ON (target.id = source.id) WHEN MATCHED THEN UPDATE SET val = source.val WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.val); | MERGE, upsert, insert or update, merge into | MERGE does INSERT or UPDATE in one statement – matches target to source by id, updates if matched, inserts if not matched |
| 9 | Data Manipulation and DDL | TRUNCATE vs DELETE | TRUNCATE quickly removes all rows (cannot rollback); DELETE removes rows with WHERE (can rollback). | TRUNCATE is faster, resets identity, cannot be rolled back, removes all rows. DELETE is slower, can be rolled back, can use WHERE for selective removal. | TRUNCATE TABLE temp_data; — vs — DELETE FROM temp_data WHERE date < 2024-01-01; | TRUNCATE, TRUNCATE vs DELETE, remove all rows, fast delete | TRUNCATE quickly removes all rows (cannot rollback, resets identity), DELETE can use WHERE and rollback but slower |
| 9 | Data Manipulation and DDL | UPDATE | Modify existing data in table rows based on conditions. | UPDATE table SET column = value WHERE condition modifies existing rows. Always use WHERE unless updating all rows. Use for correcting or changing data. | UPDATE employees SET salary = 55000 WHERE employee_id = 1; | UPDATE, UPDATE SET, modify, change data | UPDATE changes existing data – SET specifies new value (salary = 55000), WHERE specifies which row (employee_id = 1) |
| 10 | Advanced Techniques and Optimization | Common Table Expressions (WITH clause) | Define temporary named result sets that can be referenced multiple times in a query. | WITH clause creates readable, reusable subqueries. Example: WITH high_earners AS (SELECT…) SELECT * FROM high_earners. Better than nested subqueries for readability. | WITH high_earners AS (SELECT * FROM employees WHERE salary > 100000) SELECT * FROM high_earners WHERE dept = Sales; | CTE, WITH clause, common table expression, WITH AS | WITH creates named temporary result set – defines high_earners once, then references it in main query (more readable than nested subqueries) |
| 10 | Advanced Techniques and Optimization | Execution plans (EXPLAIN PLAN) | View how Oracle executes a query to identify performance bottlenecks. | EXPLAIN PLAN shows query execution strategy. Use to find missing indexes, inefficient joins, full table scans. Helps optimize slow queries by revealing Oracle approach. | EXPLAIN PLAN FOR SELECT * FROM employees WHERE salary > 50000; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); | EXPLAIN PLAN, execution plan, query plan, performance | EXPLAIN PLAN shows how Oracle will execute query – reveals full table scans, index usage, join methods – helps optimize slow queries |
| 10 | Advanced Techniques and Optimization | Hierarchical queries | Navigate tree structures using CONNECT BY, START WITH, LEVEL, PRIOR in Oracle. | Oracle CONNECT BY traverses hierarchies. START WITH defines root, CONNECT BY defines relationship, PRIOR indicates parent. Use for org charts, bill of materials, categories. | SELECT name, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR emp_id = manager_id ORDER BY LEVEL; | hierarchical query, tree, org chart, parent child | Hierarchical query navigates tree – START WITH defines starting point, CONNECT BY defines relationship, LEVEL shows how many levels deep |
| 10 | Advanced Techniques and Optimization | Indexes | Create data structures that speed up data retrieval at the cost of slower writes. | Indexes speed up SELECT queries (like a book index). Create on columns frequently used in WHERE, JOIN, ORDER BY. Trade-off: faster reads, slower writes. | CREATE INDEX idx_emp_dept ON employees(department_id); CREATE INDEX idx_emp_sal ON employees(salary); | index, CREATE INDEX, performance, speed up | CREATE INDEX speeds up queries – creates index on department_id and salary columns so WHERE/JOIN on these columns run faster |
| 10 | Advanced Techniques and Optimization | Materialized views | Pre-computed query results stored as physical tables that refresh periodically. | Materialized views cache query results for faster repeated access. Refresh on schedule or on-demand. Use for expensive aggregations run frequently. | CREATE MATERIALIZED VIEW emp_dept_summary AS SELECT dept, COUNT(*), AVG(salary) FROM employees GROUP BY dept; | materialized view, MVIEW, cache, pre-compute | Materialized view stores query results as physical table – pre-calculates expensive GROUP BY once, refreshes on schedule, much faster for repeated queries |
| 10 | Advanced Techniques and Optimization | PIVOT and UNPIVOT | Transform rows to columns (PIVOT) or columns to rows (UNPIVOT) for reporting. | PIVOT converts row data to columns (e.g., months become columns). UNPIVOT does reverse. Use PIVOT for crosstab reports, showing data in spreadsheet-like format. | SELECT * FROM (SELECT dept, month, sales FROM sales_data) PIVOT (SUM(sales) FOR month IN (Jan, Feb, Mar)); | PIVOT, UNPIVOT, rows to columns, crosstab | PIVOT transforms rows to columns – months (Jan, Feb, Mar) become column headers with SUM(sales) as values (spreadsheet-like format) |
| 10 | Advanced Techniques and Optimization | Partitioning strategies | Divide large tables into smaller physical segments for better performance and management. | Partitioning splits huge tables by range (dates), list (regions), or hash. Improves query performance, enables partition pruning, easier archiving. Use for tables with millions of rows. | CREATE TABLE sales (id NUMBER, sale_date DATE, amount NUMBER) PARTITION BY RANGE (sale_date) (PARTITION p2023 VALUES LESS THAN (2024-01-01)); | partitioning, PARTITION BY RANGE, split table, large tables | Partitioning splits huge table into smaller chunks – PARTITION BY RANGE(sale_date) creates separate partitions by year, queries only read relevant partitions |
| 10 | Advanced Techniques and Optimization | Query optimization techniques | Improve query performance through better SQL structure, indexing, and resource usage. | Optimization techniques: use indexes, avoid SELECT *, use WHERE before aggregating, use EXISTS instead of IN, avoid functions on indexed columns, use UNION ALL over UNION when possible. | Use indexes on WHERE columns; Use EXISTS instead of IN; Avoid SELECT *; Use UNION ALL instead of UNION when possible; | optimization, performance tuning, faster queries, optimize | Optimization tips – use indexes on WHERE columns, avoid SELECT * (specify columns), use EXISTS instead of IN, use UNION ALL instead of UNION when possible |
| 10 | Advanced Techniques and Optimization | ROLLUP, CUBE, GROUPING SETS | Generate subtotals and grand totals automatically with GROUP BY extensions. | ROLLUP creates hierarchical subtotals, CUBE creates all combinations, GROUPING SETS specifies exact groups. Use for summary reports with totals at multiple levels. | SELECT dept, location, SUM(salary) FROM employees GROUP BY ROLLUP (dept, location); | ROLLUP, CUBE, GROUPING SETS, subtotals, grand total | ROLLUP creates hierarchical subtotals – GROUP BY ROLLUP(dept, location) shows totals per dept, per location, and grand total |
| 10 | Advanced Techniques and Optimization | Recursive queries | Query hierarchical data by repeatedly referencing the query result (CTEs or CONNECT BY). | Use CONNECT BY or recursive CTEs for hierarchies. Example: organizational charts (employee to manager to manager manager). Shows parent-child relationships of unlimited depth. | SELECT employee_id, manager_id, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR employee_id = manager_id; | recursive, CONNECT BY, hierarchy, START WITH, PRIOR | CONNECT BY traverses hierarchy – START WITH finds root (manager_id IS NULL), CONNECT BY PRIOR follows chain (employee to manager to manager manager), LEVEL shows depth |