Here is a high level view of Oracle SQL Concepts and Queries for general understanding.
| Category | Concept | Other Names/Databases | Plain English Description | Use Case | Keywords |
|---|---|---|---|---|---|
| Data Retrieval | SELECT Statement | Same in all SQL | The fundamental query command that retrieves data from tables. Allows you to specify which columns to display, which table to query from, and conditions to filter rows. Combined with FROM and WHERE clauses to create complete queries. | Use SELECT whenever you need to view or retrieve data from a database. This is the starting point for nearly all data analysis work. Can retrieve all columns with SELECT * or specific columns by name. | select, query, retrieve, from, where, basic query, data retrieval |
| Filtering and Sorting | WHERE and ORDER BY | Same in all SQL | WHERE filters rows based on conditions (like salary > 50000 or department = Sales) before returning results. ORDER BY sorts the final results in ascending (ASC) or descending (DESC) order. WHERE comes before ORDER BY in query execution. | Use WHERE to narrow down large datasets to relevant records. Use ORDER BY to organize results in a meaningful sequence, like highest to lowest salary or alphabetically by name. Essential for making data readable and focused. | where, filter, condition, order by, sort, asc, desc, organize |
| Pattern Matching | LIKE and Wildcards | Same in all SQL | LIKE operator allows pattern matching in text searches using wildcards: percent sign matches any number of characters, underscore matches exactly one character. Case-sensitive by default in Oracle. Use with WHERE clause to find partial text matches. | Use LIKE when you need to search for text patterns instead of exact matches. Examples: finding all names starting with John, emails containing gmail, or product codes matching a pattern. Critical for flexible text searching. | like, pattern, wildcard, percent, underscore, text search, partial match |
| NULL Handling | NULL Functions (NVL, NVL2, COALESCE, NULLIF) | IFNULL/ISNULL (MySQL/SQL Server), COALESCE (Standard SQL) | NULL represents missing or unknown data. NVL replaces NULL with a default (2 args). NVL2 returns different values for NULL vs NOT NULL (3 args). COALESCE returns first non-NULL from multiple options. NULLIF converts specific values to NULL. Use IS NULL or IS NOT NULL to check for NULL (never use = NULL). | Use NULL functions to handle missing data in calculations and reports. NVL prevents NULL from breaking math operations. COALESCE provides fallback values across multiple columns (email, then phone, then emergency contact). Essential for data quality and avoiding calculation errors. | null, nvl, nvl2, coalesce, nullif, is null, missing data, default value |
| Aggregation | Aggregate Functions (COUNT, SUM, AVG, MIN, MAX) | Same in all SQL | Functions that perform calculations across multiple rows to return a single summary value. COUNT counts rows, SUM adds numbers, AVG calculates average, MIN finds smallest, MAX finds largest. Used without GROUP BY for overall totals, or with GROUP BY for subtotals per category. | Use aggregates whenever you need to summarize data rather than view details. Examples: total revenue, average salary, number of employees, highest score, earliest date. Foundation of all reporting and analytics. COUNT(DISTINCT column) counts unique values only. | count, sum, avg, average, min, max, aggregate, total, summary, calculate |
| Grouping | GROUP BY and HAVING | Same in all SQL | GROUP BY organizes rows into groups based on column values (like department or region), then calculates aggregates for each group. HAVING filters groups after aggregation (unlike WHERE which filters before). Order: WHERE filters rows, then GROUP BY groups them, then HAVING filters groups, then ORDER BY sorts final results. | Use GROUP BY to create summary reports broken down by category: sales by region, headcount by department, average salary by job level. Use HAVING to filter aggregated results (only show departments with more than 5 employees). Essential for creating pivot-style reports and dashboards. | group by, having, group, summarize, subtotal, category, breakdown |
| Advanced Grouping | ROLLUP, CUBE, GROUPING SETS | Same in most SQL (Oracle, PostgreSQL, SQL Server) | Extensions to GROUP BY that automatically generate subtotals and grand totals. ROLLUP creates hierarchical subtotals (dept, then location, then grand total). CUBE creates all possible combinations. GROUPING SETS specifies exactly which grouping combinations you want. More efficient than multiple queries with UNION. | Use for complex reports requiring multiple levels of totals. ROLLUP for hierarchical reports (region > country > city > total). CUBE for multi-dimensional analysis. Common in financial reports, sales summaries, and executive dashboards requiring subtotals at multiple levels. | rollup, cube, grouping sets, subtotal, grand total, hierarchy, multi-dimensional |
| Joins | JOIN Operations (INNER, LEFT, RIGHT, FULL OUTER) | Same in all SQL | Combines columns from two or more tables based on related columns. INNER JOIN returns only matching rows. LEFT JOIN keeps all rows from left table (NULLs where no match in right). RIGHT JOIN keeps all from right table. FULL OUTER JOIN keeps all from both tables. Self-join joins table to itself (employee-manager relationships). | Use JOINs to combine related data from multiple tables: employees with their departments, orders with customer details, projects with assigned leads. INNER JOIN for required relationships, LEFT JOIN to keep all primary records even without matches. Foundation of relational database queries. | join, inner join, left join, right join, full outer join, self join, combine tables, relate |
| Subqueries | Subqueries and Nested Queries | Same in all SQL | A query within another query (nested query). Can appear in WHERE clause (filter based on calculated values), SELECT clause (add calculated column), or FROM clause (create temporary result set called inline view). Correlated subqueries reference the outer query and run once per row. EXISTS checks if subquery returns any rows. | Use subqueries when you need calculated values for filtering (WHERE salary > average) or when you need to pre-aggregate before joining. Scalar subqueries add calculated columns. EXISTS is faster than IN for checking presence. Common in complex business logic and multi-step analysis. | subquery, nested query, inline view, correlated subquery, exists, in, scalar subquery, derived table |
| Set Operations | UNION, INTERSECT, MINUS | UNION/INTERSECT same, EXCEPT instead of MINUS (PostgreSQL/SQL Server) | Combine results from multiple queries vertically (stacking rows). UNION removes duplicates, UNION ALL keeps all rows. INTERSECT returns only rows appearing in both queries (overlap). MINUS returns rows in first query not in second (difference). All queries must have same number of columns with compatible types. | Use UNION to combine similar datasets (current employees UNION former employees). Use INTERSECT to find commonality (high earners who are also project leads). Use MINUS to find gaps (all employees MINUS those on projects = employees without projects). Essential for comparing datasets and combining lists. | union, union all, intersect, minus, except, combine, overlap, difference, set operators |
| Window Functions | Window Functions and Analytics | Same in all SQL | Perform calculations across sets of rows related to the current row WITHOUT collapsing the data like GROUP BY does. Every detail row remains, but you add calculated columns. Includes ranking functions (ROW_NUMBER, RANK, DENSE_RANK), navigation (LAG, LEAD), and aggregates (SUM OVER, AVG OVER). PARTITION BY divides data into groups, ORDER BY defines sequence, ROWS/RANGE BETWEEN defines window frame. | Use window functions for rankings (top 5 per department), running totals (cumulative sales), moving averages (3-month average), and comparing rows (current vs previous month). Critical for time series analysis, percentile rankings, and comparing values within groups while keeping all detail rows visible. | window function, over, partition by, row_number, rank, dense_rank, lag, lead, running total, moving average, analytics |
| Conditional Logic | CASE Statements | Same in all SQL | Implements IF-THEN-ELSE logic within SQL queries. Simple CASE compares one expression to multiple values. Searched CASE evaluates multiple independent conditions. Returns different values based on which condition is true. Use with aggregates for conditional counting/summing (pivot-style reports). Ends with END keyword. | Use CASE to create calculated categories (salary bands: High/Medium/Low), conditional values (if status = Active then 1 else 0), or pivot reports (count by status in columns). Essential for categorization, data transformation, and creating dynamic calculated fields in queries. | case, case when, then, else, end, if-then, conditional, categorize, pivot |
| String Manipulation | String Functions | Similar across SQL (some syntax differences) | Functions to manipulate text: CONCAT or pipe pipe joins strings, SUBSTR extracts portions, LENGTH counts characters, UPPER/LOWER change case, TRIM removes spaces, REPLACE substitutes text, INSTR finds position. LPAD/RPAD add padding. Critical for cleaning, formatting, and transforming text data. | Use string functions to clean and standardize data (TRIM spaces, UPPER for consistency), extract parts (SUBSTR for first 3 characters), search within text (INSTR to find position), and format output (LPAD to add leading zeros). Essential for data quality and text processing. | concat, substr, substring, length, upper, lower, trim, replace, instr, lpad, rpad, string, text |
| Date Manipulation | Date Functions | Similar concepts, different syntax across databases | Functions for working with dates and times: SYSDATE gets current date/time, TO_DATE converts text to date, TO_CHAR formats date as string, ADD_MONTHS adds/subtracts months, MONTHS_BETWEEN calculates duration, TRUNC removes time portion, EXTRACT gets specific parts (year, month, day), LAST_DAY finds month end. | Use date functions for calculating tenure (MONTHS_BETWEEN hire_date and today), date arithmetic (ADD_MONTHS for future dates), formatting (TO_CHAR for display), and extracting components (EXTRACT year for grouping). Critical for time-based analysis, aging reports, and date calculations. | sysdate, to_date, to_char, add_months, months_between, trunc, extract, last_day, date, time, date math |
| Numeric Functions | Numeric and Math Functions | Same in all SQL | Functions for mathematical operations: ROUND rounds to decimals, TRUNC cuts off decimals, CEIL rounds up, FLOOR rounds down, MOD gets remainder, ABS removes negative sign, POWER raises to exponent, SQRT calculates square root. Used for precise numeric calculations and formatting. | Use numeric functions for financial calculations (ROUND for currency), mathematical operations (POWER for compound interest), and data processing (MOD for grouping, ABS for distances). Essential for accurate calculations and proper numeric formatting in reports. | round, trunc, ceil, floor, mod, abs, power, sqrt, numeric, math, calculate |
| Data Type Conversion | Conversion Functions | CAST works across databases, others vary | Functions to convert between data types: TO_NUMBER converts text to number, TO_CHAR converts number/date to formatted text, CAST is general conversion, TO_DATE converts text to date. Required when data types do not match for operations or comparisons. Oracle is strict about type matching. | Use conversion functions when combining different data types, formatting output (TO_CHAR with format masks), or parsing text data (TO_NUMBER for calculations on text fields). CAST is standard SQL, TO_* functions are Oracle-specific but offer more formatting control. | to_number, to_char, to_date, cast, convert, conversion, data type, format, parse |
| Data Modification | INSERT, UPDATE, DELETE | Same in all SQL | Commands to change data in tables: INSERT adds new rows (single or bulk with INSERT ALL), UPDATE modifies existing rows based on WHERE condition, DELETE removes rows. TRUNCATE quickly removes all rows but cannot be rolled back. MERGE combines INSERT and UPDATE in one operation (upsert). | Use INSERT to add new records, UPDATE to change existing data (salary increases, status changes), DELETE to remove records (with WHERE – always use WHERE unless deleting all). Use MERGE when loading data that might already exist (update if found, insert if not). Always test with SELECT before running UPDATE/DELETE. | insert, update, delete, truncate, merge, upsert, modify, add, remove, change data |
| Transaction Control | Transactions (COMMIT, ROLLBACK, SAVEPOINT) | Same in all SQL | Commands to manage database transactions: COMMIT permanently saves all changes since last commit, ROLLBACK undoes all changes since last commit, SAVEPOINT creates intermediate checkpoint for partial rollback. Transactions ensure data consistency and allow error recovery. | Use COMMIT after successful INSERT/UPDATE/DELETE to make changes permanent. Use ROLLBACK if errors occur to undo changes. Use SAVEPOINT for complex multi-step operations where you might need to undo only part of the work. Critical for data integrity and error handling in production systems. | commit, rollback, savepoint, transaction, save, undo, permanent, checkpoint |
| Table Management | DDL – CREATE, ALTER, DROP | Same in all SQL | Data Definition Language commands to manage table structure: CREATE TABLE defines new table with columns and data types, ALTER TABLE modifies structure (add/drop/modify columns), DROP TABLE deletes table completely, RENAME changes table name. COMMENT adds documentation to tables/columns. | Use CREATE TABLE to build new tables with appropriate data types and constraints. Use ALTER TABLE to evolve schema (add columns as requirements change). Use DROP TABLE to remove obsolete tables. Use COMMENT to document table/column purposes. Essential for database design and maintenance. | create table, alter table, drop table, rename, comment, ddl, schema, structure, define |
| Constraints | Data Integrity Constraints | Same in all SQL | Rules enforced by database to ensure data quality: PRIMARY KEY uniquely identifies each row, FOREIGN KEY links to another table, UNIQUE prevents duplicates, NOT NULL requires value, CHECK validates business rules, DEFAULT sets automatic value. Defined at table creation or added via ALTER TABLE. | Use constraints to enforce data quality at database level: PRIMARY KEY for unique identifiers, FOREIGN KEY to maintain relationships, NOT NULL for required fields, CHECK for business rules (salary >= 0), UNIQUE for alternate keys (email). Prevents bad data from entering system. | primary key, foreign key, unique, not null, check, default, constraint, data integrity, validation |
| Indexes | Performance Indexes | Same in all SQL | Data structures that speed up data retrieval at cost of slower INSERT/UPDATE. CREATE INDEX builds index on specified columns. Indexes work like book indexes – allowing database to find rows quickly without scanning entire table. Most effective on columns used in WHERE, JOIN, ORDER BY clauses. | Use indexes on columns frequently used in WHERE conditions, JOIN operations, and sorting. Dramatically speeds up SELECT queries on large tables. Indexes on foreign keys improve join performance. Trade-off: faster reads but slower writes. Monitor query performance and add indexes strategically. | index, create index, drop index, unique index, performance, speed up, optimization |
| Views | Views and Materialized Views | Same in all SQL | Virtual tables based on stored queries: CREATE VIEW saves query as reusable object (no data storage), CREATE MATERIALIZED VIEW stores actual results (cached data). Views simplify complex queries and provide security layer. Materialized views trade freshness for speed – must be refreshed to get latest data. | Use views to simplify complex queries for repeated use, hide complexity from users, and restrict data access for security. Use materialized views for expensive aggregations queried frequently – pre-computes results for instant access. Refresh materialized views on schedule or on-demand. | view, create view, materialized view, mview, virtual table, cached query, reusable query |
| Common Table Expressions | WITH Clause (CTE) | Same in most modern SQL | WITH clause creates named temporary result sets that exist only during query execution. Makes complex queries more readable by breaking them into logical steps. Can reference same CTE multiple times. Recursive CTEs allow self-referencing for hierarchical queries (org charts, bill of materials). | Use CTEs to make complex queries readable and maintainable – break multi-step logic into named pieces. Use recursive CTEs for hierarchical data (employee → manager → VP → CEO). Better than nested subqueries for readability. Useful in data transformation and step-by-step calculations. | with, cte, common table expression, recursive, temporary, named query, readable |
| Hierarchical Queries | CONNECT BY (Oracle Hierarchy) | Oracle-specific, WITH RECURSIVE (Standard SQL) | Oracle-specific syntax for traversing hierarchical data: START WITH defines root nodes, CONNECT BY defines parent-child relationship, PRIOR indicates parent reference, LEVEL shows depth in hierarchy. Used for organizational charts, category trees, bill of materials, and any parent-child relationships. | Use CONNECT BY for organizational hierarchies (employee reports to manager), category structures (product → subcategory → item), and any tree-like data. Shows unlimited levels of hierarchy in single query. LEVEL column indicates depth, useful for indenting display. Essential for org charts and multi-level structures. | connect by, start with, prior, level, hierarchy, tree, org chart, parent child, recursive |
| Pivot Operations | PIVOT and UNPIVOT | Oracle/SQL Server | PIVOT transforms rows into columns (spreadsheet-style crosstab reports): takes row data and makes distinct values become column headers with aggregated values. UNPIVOT does reverse – transforms columns back into rows (normalizing data). Creates reports similar to Excel pivot tables. | Use PIVOT to create crosstab reports: months as columns with sales figures, departments as columns with headcount. Makes data easier to read in spreadsheet format. Use UNPIVOT to normalize denormalized data for analysis. Common in executive dashboards and summary reports. | pivot, unpivot, crosstab, rows to columns, columns to rows, spreadsheet format, pivot table |
| String Aggregation | LISTAGG | STRING_AGG (PostgreSQL/SQL Server) | Aggregates multiple row values into single delimited string: combines values from multiple rows into comma-separated list (or other delimiter). Useful with GROUP BY to show all values per group in one cell. Can specify order of values and custom delimiter. | Use LISTAGG to combine related values into lists: all employees per department in one field, all products per category. Creates compact summary displays. Useful in reports where showing all related items in one cell is clearer than multiple rows. | listagg, string_agg, aggregate string, concatenate rows, comma separated, combine values |
| Query Analysis | EXPLAIN PLAN | EXPLAIN (PostgreSQL), SHOWPLAN (SQL Server) | Shows how Oracle will execute query – reveals execution plan with steps: table access methods (full scan vs index), join methods, estimated rows, cost. Use to identify performance bottlenecks: missing indexes, inefficient joins, unnecessary full table scans. | Use EXPLAIN PLAN to diagnose slow queries: identifies missing indexes, reveals inefficient operations, shows estimated vs actual rows. Run before optimizing queries to understand current execution path. Essential for performance tuning large queries and tables. | explain plan, execution plan, query plan, performance, optimization, analyze query |
| Statistics | ANALYZE and Statistics | ANALYZE (PostgreSQL), UPDATE STATISTICS (SQL Server) | Updates table statistics used by query optimizer: row counts, data distribution, index usage patterns. Optimizer uses statistics to choose best execution plan. Statistics can become stale as data changes, affecting query performance. | Run ANALYZE after significant data changes (large INSERT/DELETE/UPDATE operations) to refresh statistics. Helps optimizer choose best query plans. Many databases auto-analyze, but manual analysis useful after bulk loads or major changes. Important for maintaining query performance. | analyze, statistics, update stats, optimizer, query optimization, table stats |
| Sequences | Sequences (Auto-increment) | IDENTITY (SQL Server), SERIAL (PostgreSQL), AUTO_INCREMENT (MySQL) | Generates unique sequential numbers for primary keys: CREATE SEQUENCE defines generator, NEXTVAL gets next number, CURRVAL gets current value. Independent of tables – can be shared across tables or used for custom numbering schemes. | Use sequences for generating unique IDs (customer numbers, invoice numbers, primary keys). More flexible than auto-increment columns – can specify start value, increment, and share across tables. Standard method for unique ID generation in Oracle. | sequence, nextval, currval, auto-increment, identity, serial, unique id, generator |
| Oracle Utilities | DUAL and System Functions | Oracle-specific (not needed in other databases) | DUAL is special Oracle table with one row – used when SELECT needs FROM clause but no actual table (SELECT SYSDATE FROM dual). SYS_GUID generates unique identifiers. USER returns current username. Oracle-specific utilities for system functions and testing. | Use DUAL for calculations without table access (SELECT 2+2 FROM dual), testing functions, getting system values (SYSDATE, USER). Use SYS_GUID for unique identifiers (GUIDs). Common in testing queries and system value retrieval. | dual, sys_guid, user, current_user, system function, oracle utility, dummy table |
| Stored Procedures | Stored Procedures and Functions | Same concept across databases (syntax varies) | Reusable blocks of PL/SQL code stored in database: procedures perform actions (no return value), functions return single value. Accept parameters, contain logic (IF/THEN, loops), can call other procedures/functions. Compiled and stored for repeated execution. | Use stored procedures for complex business logic executed repeatedly: data validation, multi-step processing, calculations. Functions for reusable calculations returning values. Benefits: performance (pre-compiled), security (users call procedure vs direct table access), maintainability (logic in one place). Common in enterprise applications. | stored procedure, function, pl/sql, procedure, stored function, reusable code, business logic, parameter |
| Stored Programs | Packages | Oracle-specific (schemas in other databases) | Collections of related procedures, functions, variables, and cursors grouped together in named package. Has specification (public interface) and body (implementation). Provides encapsulation and modular design. Can have package-level variables shared across procedures. | Use packages to organize related procedures/functions into logical units: employee_pkg for all employee operations, finance_pkg for financial calculations. Promotes code organization, reusability, and maintainability. Standard Oracle development pattern for large applications. | package, pl/sql package, stored package, encapsulation, modular code, package body, package spec |
| Triggers | Database Triggers | Same in all SQL | Automatic procedures that execute in response to events: BEFORE/AFTER INSERT/UPDATE/DELETE on tables. Can validate data, maintain audit trails, enforce complex business rules, or cascade changes. Fire automatically without explicit calls. | Use triggers for automatic actions: audit logging (track who changed what when), data validation (enforce rules beyond simple constraints), derived column updates (auto-calculate totals), maintaining data consistency. Use carefully – can impact performance and make debugging harder. | trigger, before trigger, after trigger, automatic, event, audit, validation, cascade |