How to Write Correlated Subqueries in Oracle SQL
Compare every row with its own group, compute per-row values in the select list, and see the analytic alternative to a correlated subquery.
Oracle SQL
Explore comprehensive Oracle SQL resources designed for database developers and administrators. Our guides cover everything from basic query construction to advanced techniques for complex joins, analytical functions, and hierarchical data manipulation. Learn practical approaches to performance optimization through execution plan analysis, smart indexing strategies, and efficient table partitioning. Discover best practices for data modeling, constraint management, and large dataset handling that solve real business challenges. Whether you're writing reports, building data warehouses, or maintaining enterprise databases, enhance your Oracle SQL proficiency with our actionable insights.
How to Use Subqueries in Oracle SQL
Nest one query inside another to supply a value, a list, pairs of values, or a whole table, and avoid ORA-01427 and the NOT IN trap.
How to Use GROUPING SETS in Oracle SQL
List exactly the groupings you need in one query, replace UNION ALL chains, and identify subtotal rows with GROUPING, GROUPING_ID, and GROUP_ID.
How to Summarize Every Combination with CUBE in Oracle SQL
Get detail rows, totals for every dimension, and a grand total in one query with CUBE, then filter or label the total rows with GROUPING_ID.
How to Add Subtotals with ROLLUP in Oracle SQL
Compute detail rows, subtotals, and a grand total in one grouped query with ROLLUP, and learn how to read, sort, and label the extra rows.
How to Filter Groups with HAVING in Oracle SQL
Keep only the groups you need by filtering on counts, sums, and other aggregates, and learn when a condition belongs in WHERE instead.
Oracle SQL Developer Guide
If you work with Oracle databases, you need a reliable tool that makes querying, building, and managing your data feel effortless. Oracle SQL Developer is exactly that tool, and it has been the go-to IDE for Oracle database professionals for…
CREATE TABLE in Oracle with Auto Increment ID
If you have worked with MySQL or SQL Server, you are probably used to AUTO_INCREMENT or IDENTITY columns that generate unique IDs automatically. Oracle does things a little differently, but once you understand how it works, it becomes just as…
Tricky Oracle SQL Queries for Interview: Top Questions and Answers
Oracle SQL interviews do not just test whether you know the syntax. They test whether you can think through a problem and construct the right query under pressure. Interviewers love tricky scenarios because they reveal how deeply you actually understand…
Top 50 Oracle SQL Interview Questions (2026 Edition)
Getting ready for an Oracle SQL interview can feel overwhelming, especially when the role covers everything from basic queries to advanced performance tuning. The good news is that most interviewers pull from the same core topics again and again. If…
Oracle JSON_DATAGUIDE Function
The JSON_DATAGUIDE function in Oracle SQL is a powerful aggregate function used for data analysis. Its job is not to extract data, but to analyze a collection of JSON documents and automatically discover their structure. Think of it as an…
Oracle JSON_TRANSFORM Function
The JSON_TRANSFORM function is Oracle's most powerful tool for modifying JSON data directly within a SQL query. While JSON_VALUE and JSON_QUERY are used to read data, JSON_TRANSFORM is used to change it. This function allows you to perform a series…
Oracle JSON_SERIALIZE Function
The JSON_SERIALIZE function in Oracle SQL is a conversion function that takes JSON data (stored as a JSON type, VARCHAR2, CLOB, or BLOB) and converts it back into a textual representation. This function is not for querying JSON (like JSON_VALUE…
Oracle JSON_SCALAR Function
The JSON_SCALAR function in Oracle SQL is a constructor function that converts a single SQL scalar value (like a NUMBER, VARCHAR2, or DATE) into its equivalent JSON scalar value, returned as the native JSON type. A "scalar" is a simple,…
Oracle JSON Type Constructor
Important Prerequisite: The JSON type constructor is a modern feature. To use it, your database compatible parameter must be set to 20 or higher. If you are on an older version (like 19c or 12c), you will get an error,…
Oracle JSON_OBJECTAGG Function
The JSON_OBJECTAGG function in Oracle SQL is an aggregate function. Its job is to take all the rows in a group and combine them into a single JSON object. This is the partner to JSON_ARRAYAGG (which aggregates rows into an…
Oracle JSON_OBJECT Function
The JSON_OBJECT function in Oracle SQL is a "constructor" function. Its job is the opposite of JSON_VALUE or JSON_QUERY. Instead of extracting data from JSON, JSON_OBJECT builds a new JSON object string from regular SQL key-value pairs. This is essential…
Oracle JSON_ARRAYAGG Function
The JSON_ARRAYAGG function in Oracle SQL is an aggregate function. Its job is to take all the values from a single column across multiple rows and combine (or "aggregate") them into a single JSON array. This is the "JSON version"…