https://merginit.com/_app/immutable/chunks/C9DxM7OK.js

1import{a as f,f as O}from"./SDtLGXl7.js";import"./Bom5CQ_t.js";import{s,f as _,r as a,n as I}from"./BhzG5ji-.js";import{h as n}from"./DQsLVjXp.js";const M={title:"Oracle DML Guide: INSERT, UPDATE, DELETE, MERGE Explained",description:"Master Oracle DML with examples of INSERT, UPDATE, DELETE and MERGE, plus advanced features: INSERT ALL, RETURNING, error logging, and performance tips.",date:"2025-06-25",categories:["database","tutorial"],published:!0,author:"Jonas Fröller",readingTime:"15 min",tags:["Oracle SQL","Database","DML","Data Manipulation"]},{title:q,description:F,date:G,categories:x,published:H,author:W,readingTime:B,tags:Y}=M;var D=O(`<h1 id="oracle-sql-dml-complete-guide-to-data-manipulation-language"><a tabindex="-1" href="#oracle-sql-dml-complete-guide-to-data-manipulation-language">Oracle SQL DML: complete guide to Data Manipulation Language</a></h1> <p>DML, short for Data Manipulation Language, is the part of Oracle SQL that works with the data inside your tables. Once you’re comfortable with INSERT, UPDATE, and DELETE, most database work gets faster, whether you’re building enterprise systems or tuning existing applications.</p> <p>INSERT, UPDATE, and DELETE are the commands every Oracle database professional reaches for daily, so they’re worth knowing well. This guide covers them with practical examples, then moves on to advanced features like INSERT ALL, RETURNING, and error logging.</p> <h2 id="understanding-dml-the-foundation-of-data-operations"><a tabindex="-1" href="#understanding-dml-the-foundation-of-data-operations">Understanding DML: the foundation of data operations</a></h2> <p>DML commands work on rows in tables that already exist. DDL (Data Definition Language), by contrast, defines the structure of the database. The three main DML commands are:</p> <ul><li>INSERT: adds new records to tables</li> <li>UPDATE: modifies existing records</li> <li>DELETE: removes records from tables</li></ul> <p>Every DML statement can fire database triggers, and they all support the <code>RETURNING</code> clause for capturing changed values into variables.</p> <h2 id="insert-adding-data-to-your-database"><a tabindex="-1" href="#insert-adding-data-to-your-database">INSERT: adding data to your database</a></h2> <p>The INSERT statement adds new rows to a table. Besides the basic single-row insert, Oracle has several variants worth knowing.</p> <h3 id="basic-single-row-insertion"><a tabindex="-1" href="#basic-single-row-insertion">Basic single row insertion</a></h3> <pre class="language-sql"></pre> <h3 id="multiple-row-insertion"><a tabindex="-1" href="#multiple-row-insertion">Multiple row insertion</a></h3> <p>For inserting multiple records efficiently, Oracle offers several approaches:</p> <pre class="language-sql"></pre> <h3 id="advanced-insert-techniques"><a tabindex="-1" href="#advanced-insert-techniques">Advanced INSERT techniques</a></h3> <h4 id="insert-all-conditional-multi-table-insertions"><a tabindex="-1" href="#insert-all-conditional-multi-table-insertions">INSERT ALL: conditional multi-table insertions</a></h4> <p><code>INSERT ALL</code> inserts rows into several tables from one query:</p> <pre class="language-sql"></pre> <h4 id="insert-first-first-match-logic"><a tabindex="-1" href="#insert-first-first-match-logic">INSERT FIRST: first-match logic</a></h4> <p>Unlike <code>INSERT ALL</code> which evaluates all conditions, <code>INSERT FIRST</code> stops at the first matching condition:</p> <pre class="language-sql"></pre> <h4 id="bulk-insert-with-with-clause"><a tabindex="-1" href="#bulk-insert-with-with-clause">BULK INSERT with WITH clause</a></h4> <p>For complex data preparation before insertion:</p> <pre class="language-sql"></pre> <h3 id="insert-limitations-and-best-practices"><a tabindex="-1" href="#insert-limitations-and-best-practices">INSERT limitations and best practices</a></h3> <p>When using <code>INSERT ALL</code>, be aware of these limitations:</p> <ul><li>Maximum 15 rows for tables with 4096 columns</li> <li>Maximum 65 rows for tables with 1000 columns</li> <li>Only works with physical tables (no views or materialized views)</li> <li>No parallel processing with index-organized tables</li> <li>Cannot use sequences in subqueries</li> <li>Not applicable to remote tables</li></ul> <h2 id="update-modifying-existing-data"><a tabindex="-1" href="#update-modifying-existing-data">UPDATE: modifying existing data</a></h2> <p>The UPDATE statement modifies one or more rows in a table. The WHERE clause determines which rows are affected.</p> <h3 id="basic-update-operations"><a tabindex="-1" href="#basic-update-operations">Basic UPDATE operations</a></h3> <pre class="language-sql"></pre> <h3 id="update-with-returning"><a tabindex="-1" href="#update-with-returning">UPDATE with RETURNING</a></h3> <p>Capture the updated values for further processing:</p> <pre class="language-sql"></pre> <h3 id="setting-default-values"><a tabindex="-1" href="#setting-default-values">Setting Default Values</a></h3> <pre class="language-sql"></pre> <h2 id="delete-removing-data-safely"><a tabindex="-1" href="#delete-removing-data-safely">DELETE: removing data safely</a></h2> <p>DELETE removes rows from a table based on the conditions you specify. It’s worth knowing how DELETE and TRUNCATE differ before you pick one.</p> <h3 id="basic-delete-operations"><a tabindex="-1" href="#basic-delete-operations">Basic DELETE operations</a></h3> <pre class="language-sql"></pre> <h3 id="delete-vs-truncate"><a tabindex="-1" href="#delete-vs-truncate">DELETE vs TRUNCATE</a></h3> <p>

1The main differences between DELETE and TRUNCATE:</p> <p>DELETE (DML):</p> <ul><li>Selective deletion with WHERE clause</li> <li>Triggers can be fired</li> <li>Operations logged in transaction log</li> <li>Can be rolled back</li></ul> <p>TRUNCATE (DDL):</p> <ul><li>Removes all rows at once</li> <li>Faster performance</li> <li>Cannot fire triggers</li> <li>Minimal logging</li> <li>Cannot be rolled back easily</li></ul> <h2 id="merge-the-swiss-army-knife-of-dml"><a tabindex="-1" href="#merge-the-swiss-army-knife-of-dml">MERGE: the Swiss Army Knife of DML</a></h2> <p>MERGE is Oracle’s “upsert” statement: it inserts a row when no match is found and updates it when a match exists. It’s a practical choice for data synchronization.</p> <pre class="language-sql"></pre> <h2 id="advanced-dml-features"><a tabindex="-1" href="#advanced-dml-features">Advanced DML Features</a></h2> <h3 id="error-logging"><a tabindex="-1" href="#error-logging">Error Logging</a></h3> <p>If some rows in a DML statement fail, Oracle can log the errors instead of stopping the whole operation:</p> <pre class="language-sql"></pre> <h3 id="remote-operations"><a tabindex="-1" href="#remote-operations">Remote Operations</a></h3> <p>Execute DML operations on remote databases:</p> <pre class="language-sql"></pre> <h2 id="understanding-dql-the-query-component"><a tabindex="-1" href="#understanding-dql-the-query-component">Understanding DQL: the query component</a></h2> <p>While SELECT is technically part of DQL (Data Query Language), it’s often considered alongside DML when it involves data manipulation through subqueries or when used with INSERT statements.</p> <blockquote><p>According to Wikipedia: “Although often considered part of DML, the SQL SELECT statement is strictly speaking part of the DQL SQL subcategory.”</p></blockquote> <h3 id="join-operations"><a tabindex="-1" href="#join-operations">JOIN Operations</a></h3> <p>Oracle supports various JOIN types for combining data from multiple tables:</p> <p>Basic JOIN Types:</p> <pre class="language-sql"></pre> <h3 id="subquery-types-and-applications"><a tabindex="-1" href="#subquery-types-and-applications">Subquery types and applications</a></h3> <p>Oracle supports various subquery types:</p> <p>Scalar Subqueries:</p> <pre class="language-sql"></pre> <p>EXISTS Subqueries:</p> <pre class="language-sql"></pre> <p>IN/ANY/ALL Subqueries:</p> <pre class="language-sql"></pre> <p>Subquery in FROM clause:</p> <pre class="language-sql"></pre> <h3 id="group-by-and-aggregation-functions"><a tabindex="-1" href="#group-by-and-aggregation-functions">GROUP BY and aggregation functions</a></h3> <p>Basic GROUP BY:</p> <pre class="language-sql"></pre> <p>GROUP BY with HAVING:</p> <pre class="language-sql"></pre> <p>The GROUP BY Rule:
2When using GROUP BY, group by every column in the SELECT list that is not inside an aggregate function such as SUM, AVG, or COUNT. This keeps the aggregation results correct.</p> <h3 id="oracles-comprehensive-aggregation-functions-an-overview"><a tabindex="-1" href="#oracles-comprehensive-aggregation-functions-an-overview">Oracle’s comprehensive aggregation functions: an overview</a></h3> <p>Oracle groups its aggregation functions into these categories:</p> <ol><li>Basic Statistical Functions: AVG, COUNT, MAX, MEDIAN, MIN, SUM, VARIANCE, STDDEV</li> <li>Extended Statistical Functions: CORR, COVAR_POP, COVAR_SAMP, KURTOSIS_POP, KURTOSIS_SAMP, SKEWNESS_POP, SKEWNESS_SAMP</li> <li>Ranking Functions: DENSE_RANK, PERCENT_RANK, RANK, CUME_DIST</li> <li>Approximation Functions: APPROX_COUNT, APPROX_COUNT_DISTINCT, APPROX_MEDIAN, APPROX_PERCENTILE, APPROX_RANK, APPROX_SUM</li> <li>Grouping Functions: GROUP_ID, GROUPING, GROUPING_ID</li> <li>First/Last Values: FIRST, LAST</li> <li>Logical Aggregations: EVERY, BOOLEAN_AND_AGG, BOOLEAN_OR_AGG</li> <li>Bitwise Aggregations: BIT_AND_AGG, BIT_OR_AGG, BIT_XOR_AGG</li> <li>JSON Aggregations: JSON_ARRAYAGG, JSON_OBJECTAGG</li> <li>Statistical Test Functions: STATS_BINOMIAL_TEST, STATS_CROSSTAB, STATS_F_TEST, STATS_KS_TEST, STATS_MW_TEST, STATS_ONE_WAY_ANOVA, STATS_T_TEST*, STATS_WSR_TEST</li> <li>Regression Functions: REGR_* (Linear regression functions)</li> <li>Collection Functions: COLLECT, LISTAGG</li> <li>XML Aggregations: SYS_XMLAGG, XMLAGG</li> <li>Special Functions: ANY_VALUE, CHECKSUM, SYS_OP_ZONE_ID, TO_APPROX_COUNT_DISTINCT, TO_APPROX_PERCENTILE</li></ol> <h3 id="hierarchical-queries-with-connect-by"><a tabindex="-1" href="#hierarchical-queries-with-connect-by">Hierarchical queries with CONNECT BY</a></h3> <p>Oracle’s hierarchical query capability allows you to work with tree-structured data:</p> <pre class="language-sql"></pre> <h3 id="set-operators-combining-query-results"><a tabindex="-1" href="#set-operators-combining-query-results">Set operators: combining query results</a></h3> <p>

2Set operators combine results from multiple SELECT statements, similar to JOINs but working with rows rather than columns:</p> <pre class="language-sql"></pre> <p>Set Operator Types:</p> <table><thead><tr><th>Operator</th><th>Returns</th></tr></thead><tbody><tr><td>UNION</td><td>All unique rows</td></tr><tr><td>UNION ALL</td><td>All rows, including duplicates</td></tr><tr><td>INTERSECT</td><td>All unique rows selected by both queries</td></tr><tr><td>INTERSECT ALL</td><td>All rows selected by both queries, including duplicates</td></tr><tr><td>MINUS</td><td>All unique rows from first query but not second (like EXCEPT in other databases)</td></tr><tr><td>MINUS ALL</td><td>All rows from first query but not second, including duplicates</td></tr></tbody></table> <p>Requirements for Set Operators:</p> <ul><li>Number of columns must match between queries</li> <li>Data types of corresponding columns must be compatible</li> <li>ORDER BY must be placed at the end of the final query</li></ul> <h3 id="joins-vs-set-operators-understanding-the-difference"><a tabindex="-1" href="#joins-vs-set-operators-understanding-the-difference">JOINs vs set operators: understanding the difference</a></h3> <p>JOINs:</p> <ul><li>Work with relationships between tables</li> <li>Flexible combination based on conditions</li> <li>Connect rows from different tables</li> <li>Focus on columns</li></ul> <p>Set Operators:</p> <ul><li>No relationships required</li> <li>Require compatible table structures</li> <li>Combine entire result sets</li> <li>Focus on rows</li></ul> <h3 id="explain-plan-for-performance-analysis"><a tabindex="-1" href="#explain-plan-for-performance-analysis">EXPLAIN PLAN for performance analysis</a></h3> <p>When you need to tune a DML statement, the execution plan is where you start:</p> <pre class="language-sql"></pre> <h2 id="performance-considerations-and-best-practices"><a tabindex="-1" href="#performance-considerations-and-best-practices">Performance considerations and best practices</a></h2> <h3 id="efficient-data-operations"><a tabindex="-1" href="#efficient-data-operations">Efficient Data Operations</a></h3> <ol><li>Use BULK operations when inserting multiple rows</li> <li>Use MERGE for upsert scenarios instead of separate INSERT and UPDATE logic</li> <li>Index the columns used in WHERE clauses</li> <li>Use the RETURNING clause to minimize round trips to the database</li> <li>Consider INSERT ALL for multi-table operations</li></ol> <h3 id="transaction-management"><a tabindex="-1" href="#transaction-management">Transaction Management</a></h3> <p>Always consider transaction boundaries when performing DML operations:</p> <ul><li>Use explicit transactions for related operations</li> <li>Implement proper error handling</li> <li>Consider the impact of triggers on performance</li> <li>Plan for rollback scenarios</li></ul> <h2 id="conclusion"><a tabindex="-1" href="#conclusion">Conclusion</a></h2> <p>Oracle DML covers the daily data work in a database: simple single-row inserts up to multi-table operations, with RETURNING, error logging, and EXPLAIN PLAN along the way. Knowing these commands and their options makes routine development and maintenance go smoother.</p> <p>Which variant you pick matters. Use a plain INSERT for occasional rows, INSERT ALL when rows need to land in several tables, MERGE when you’re synchronizing data, and RETURNING when you want the changed values without a second query. Getting those choices right is what keeps DML code efficient and easy to maintain.</p> <p>Whether you’re building a new application or keeping an existing one running, INSERT, UPDATE, DELETE, and MERGE are the commands you’ll reach for daily. The variations in this guide are what solve the awkward cases once the basics are second nature.</p>`,1);function J(A){var N=D(),e=s(_(N),20);n(e,()=>`<code class="language-sql"><span class="token comment">-- Adding a new author</span>
3<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> Authors <span class="token punctuation">(</span>Name<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">

3'J.K. Rowling'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
4
5<span class="token comment">-- Adding a new book with a subquery</span>
6<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> Books <span class="token punctuation">(</span>AuthorId<span class="token punctuation">,</span> Title<span class="token punctuation">,</span> Price<span class="token punctuation">,</span> PublicationYear<span class="token punctuation">)</span>
7<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> AuthorId <span class="token keyword">FROM</span> Authors <span class="token keyword">WHERE</span> Name <span class="token operator">=</span> <span class="token string">'J.K. Rowling'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
8        <span class="token string">'Harry Potter and the Philosopher''s Stone'</span><span class="token punctuation">,</span> <span class="token number">20.99</span><span class="token punctuation">,</span> <span class="token number">1997</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code>`,!0),a(e);var t=s(e,6);n(t,()=>`<code class="language-sql"><span class="token comment">-- Using UNION ALL for multiple authors</span>
9<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> Authors <span class="token punctuation">(</span>Name<span class="token punctuation">)</span>
10<span class="token keyword">SELECT</span> <span class="token string">'Friedrich Nietzsche'</span> <span class="token keyword">FROM</span> DUAL <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
11<span class="token keyword">SELECT</span> <span class="token string">'Plato'</span> <span class="token keyword">FROM</span> DUAL <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
12<span class="token keyword">SELECT</span> <span class="token string">'Aristotle'</span> <span class="token keyword">FROM</span> DUAL <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
13<span class="token keyword">SELECT</span> <span class="token string">'Immanuel Kant'</span> <span class="token keyword">FROM</span> DUAL <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
14<span class="token keyword">SELECT</span> <span class="token string">'Søren Kierkegaard'</span> <span class="token keyword">FROM</span> DUAL<span class="token punctuation">;</span></code>`,!0),a(t);var o=s(t,8);n(o,()=>`<code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">ALL</span>
15<span class="token keyword">WHEN</span> order_total <span class="token operator">&lt;=</span> <span class="token number">100000</span> <span class="token keyword">THEN</span>
16    <span class="token keyword">INTO</span> small_orders
17<span class="token keyword">WHEN</span> order_total <span class="token operator">></span> <span class="token number">100000</span> <span class="token operator">AND</span> order_total <span class="token operator">&lt;=</span> <span class="token number">200000</span> <span class="token keyword">THEN</span>
18    <span class="token keyword">INTO</span> medium_orders
19<span class="token keyword">ELSE</span>
20    <span class="token keyword">INTO</span> large_orders
21<span class="token keyword">SELECT</span> order_id<span class="token punctuation">,</span> order_total<span class="token punctuation">,</span> sales_rep_id<span class="token punctuation">,</span> customer_id
22<span class="token keyword">FROM</span> orders<span class="token punctuation">;</span></code>`,!0),a(o);var p=s(o,6);n(p,()=>`<code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">FIRST</span>
23<span class="token keyword">WHEN</span> ottl <span class="token operator">&lt;=</span> <span class="token number">100000</span> <span class="token keyword">THEN</span>
24    <span class="token keyword">

24INTO</span> small_orders <span class="token keyword">VALUES</span><span class="token punctuation">(</span>oid<span class="token punctuation">,</span> ottl<span class="token punctuation">,</span> sid<span class="token punctuation">,</span> cid<span class="token punctuation">)</span>
25<span class="token keyword">WHEN</span> ottl <span class="token operator">></span> <span class="token number">100000</span> <span class="token operator">AND</span> ottl <span class="token operator">&lt;=</span> <span class="token number">200000</span> <span class="token keyword">THEN</span>
26    <span class="token keyword">INTO</span> medium_orders <span class="token keyword">VALUES</span><span class="token punctuation">(</span>oid<span class="token punctuation">,</span> ottl<span class="token punctuation">,</span> sid<span class="token punctuation">,</span> cid<span class="token punctuation">)</span>
27<span class="token keyword">WHEN</span> ottl <span class="token operator">></span> <span class="token number">200000</span> <span class="token keyword">THEN</span>
28    <span class="token keyword">INTO</span> large_orders <span class="token keyword">VALUES</span><span class="token punctuation">(</span>oid<span class="token punctuation">,</span> ottl<span class="token punctuation">,</span> sid<span class="token punctuation">,</span> cid<span class="token punctuation">)</span>
29<span class="token keyword">SELECT</span> o<span class="token punctuation">.</span>order_id oid<span class="token punctuation">,</span> o<span class="token punctuation">.</span>customer_id cid<span class="token punctuation">,</span> o<span class="token punctuation">.</span>order_total ottl<span class="token punctuation">,</span>
30       o<span class="token punctuation">.</span>sales_rep_id sid
31<span class="token keyword">FROM</span> orders o<span class="token punctuation">,</span> customers c
32<span class="token keyword">WHERE</span> o<span class="token punctuation">.</span>customer_id <span class="token operator">=</span> c<span class="token punctuation">.</span>customer_id<span class="token punctuation">;</span></code>`,!0),a(p);var c=s(p,6);n(c,()=>`<code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> people <span class="token punctuation">(</span>person_id<span class="token punctuation">,</span> given_name<span class="token punctuation">,</span> family_name<span class="token punctuation">,</span> title<span class="token punctuation">)</span>
33<span class="token keyword">WITH</span> names <span class="token keyword">AS</span> <span class="token punctuation">(</span>
34    <span class="token keyword">SELECT</span> <span class="token number">4</span><span class="token punctuation">,</span> <span class="token string">'Ruth'</span><span class="token punctuation">,</span> <span class="token string">'Fox'</span><span class="token punctuation">,</span> <span class="token string">'Mrs'</span> <span class="token keyword">FROM</span> dual <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
35    <span class="token keyword">SELECT</span> <span class="token number">5</span><span class="token punctuation">,</span> <span class="token string">'Isabelle'</span><span class="token punctuation">,</span> <span class="token string">'Squirrel'</span><span class="token punctuation">,</span> <span class="token string">'Miss'</span> <span class="token keyword">FROM</span> dual <span class="token keyword">UNION</span> <span class="token keyword">ALL</span>
36    <span class="token keyword">SELECT</span> <span class="token number">6</span><span class="token punctuation">,</span> <span class="token string">'Justin'</span><span class="token punctuation">,</span> <span class="token string">

36'Frog'</span><span class="token punctuation">,</span> <span class="token string">'Master'</span> <span class="token keyword">FROM</span> dual
37<span class="token punctuation">)</span>
38<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> names
39<span class="token keyword">WHERE</span> family_name <span class="token operator">LIKE</span> <span class="token string">'F%'</span><span class="token punctuation">;</span></code>`,!0),a(c);var r=s(c,14);n(r,()=>`<code class="language-sql"><span class="token comment">-- Update book price</span>
40<span class="token keyword">UPDATE</span> Books
41<span class="token keyword">SET</span> Price <span class="token operator">=</span> <span class="token number">22.99</span>
42<span class="token keyword">WHERE</span> Title <span class="token operator">=</span> <span class="token string">'Harry Potter and the Philosopher''s Stone'</span><span class="token punctuation">;</span>
43
44<span class="token comment">-- Update multiple columns</span>
45<span class="token keyword">UPDATE</span> employees
46<span class="token keyword">SET</span> job_id <span class="token operator">=</span> <span class="token string">'SA_MAN'</span><span class="token punctuation">,</span>
47    salary <span class="token operator">=</span> salary <span class="token operator">+</span> <span class="token number">1000</span><span class="token punctuation">,</span>
48    department_id <span class="token operator">=</span> <span class="token number">140</span>
49<span class="token keyword">WHERE</span> last_name <span class="token operator">=</span> <span class="token string">'Jones'</span><span class="token punctuation">;</span></code>`,!0),a(r);var l=s(r,6);n(l,()=>`<code class="language-sql"><span class="token keyword">UPDATE</span> employees
50<span class="token keyword">SET</span> job_id <span class="token operator">=</span> <span class="token string">'SA_MAN'</span><span class="token punctuation">,</span> salary <span class="token operator">=</span> salary <span class="token operator">+</span> <span class="token number">1000</span><span class="token punctuation">,</span> department_id <span class="token operator">=</span> <span class="token number">140</span>
51<span class="token keyword">WHERE</span> last_name <span class="token operator">=</span> <span class="token string">'Jones'</span>
52<span class="token keyword">RETURNING</span> salary<span class="token operator">*</span><span class="token number">0.25</span><span class="token punctuation">,</span> last_name<span class="token punctuation">,</span> department_id
53<span class="token keyword">INTO</span> :bnd1<span class="token punctuation">,</span> :bnd2<span class="token punctuation">,</span> :bnd3<span class="token punctuation">;</span></code>`,!0),a(l);var i=s(l,4);n(i,()=>

53'<code class="language-sql"><span class="token keyword">UPDATE</span> table_name <span class="token keyword">SET</span> column_name <span class="token operator">=</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">WHERE</span> condition<span class="token punctuation">;</span></code>',!0),a(i);var k=s(i,8);n(k,()=>`<code class="language-sql"><span class="token comment">-- Delete a specific author</span>
54<span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> Authors
55<span class="token keyword">WHERE</span> Name <span class="token operator">=</span> <span class="token string">'J.K. Rowling'</span><span class="token punctuation">;</span>
56
57<span class="token comment">-- Clean up orphaned records</span>
58<span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> Books
59<span class="token keyword">WHERE</span> AuthorId <span class="token operator">NOT</span> <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> AuthorId <span class="token keyword">FROM</span> Authors<span class="token punctuation">)</span><span class="token punctuation">;</span></code>`,!0),a(k);var u=s(k,18);n(u,()=>`<code class="language-sql"><span class="token keyword">MERGE</span> <span class="token keyword">INTO</span> Books b
60<span class="token keyword">USING</span> <span class="token punctuation">(</span>
61    <span class="token keyword">SELECT</span> AuthorId<span class="token punctuation">,</span> <span class="token string">'New Book Title'</span> <span class="token keyword">AS</span> Title<span class="token punctuation">,</span> <span class="token number">19.99</span> <span class="token keyword">AS</span> Price<span class="token punctuation">,</span> <span class="token number">2025</span> <span class="token keyword">AS</span> PublicationYear
62    <span class="token keyword">FROM</span> Authors
63    <span class="token keyword">WHERE</span> Name <span class="token operator">=</span> <span class="token string">'J.K. Rowling'</span>
64<span class="token punctuation">)</span> src
65<span class="token keyword">ON</span> <span class="token punctuation">(</span>b<span class="token punctuation">.</span>Title <span class="token operator">=</span> src<span class="token punctuation">.</span>Title<span class="token punctuation">)</span>
66<span class="token keyword">WHEN</span> <span class="token keyword">MATCHED</span> <span class="token keyword">THEN</span>
67    <span class="token keyword">UPDATE</span> <span class="token keyword">SET</span> b<span class="token punctuation">.</span>Price <span class="token operator">=</span> src<span class="token punctuation">.</span>Price
68<span class="token keyword">WHEN</span> <span class="token operator">NOT</span> <span class="token keyword">MATCHED</span> <span class="token keyword">THEN</span>
69    <span class="token keyword">INSERT</span> <span class="token punctuation">(</span>AuthorId<span class="token punctuation">,</span> Title<span class="token punctuation">,</span> Price<span class="token punctuation">,</span> PublicationYear<span class="token punctuation">)</span>
70    <span class="token keyword">VALUES</span> <span class="token punctuation">(</span>src<span class="token punctuation">.</span>AuthorId<span class="token punctuation">,</span> src<span class="token punctuation">.</span>Title<span class="token punctuation">,</span> src<span class="token punctuation">.</span>Price<span class="token punctuation">,</span> src<span class="token punctuation">.</span>PublicationYear<span class="token punctuation">)</span><span class="token punctuation">;</span></code>`,!0),a(u);var d=s(u,8);n(d,()=>`<code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> raises
71<span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span> salary<span class="token operator">*</span><span class="token number">1.1</span> <span class="token keyword">FROM</span> employees
72<span class="token keyword">WHERE</span> commission_pct <span class="token operator">></span> <span class="token number">.2</span>
73LOG <span class="token keyword">ERRORS</span> <span class="token keyword">

73INTO</span> errlog <span class="token punctuation">(</span><span class="token string">'my_bad'</span><span class="token punctuation">)</span> REJECT <span class="token keyword">LIMIT</span> <span class="token number">10</span><span class="token punctuation">;</span></code>`,!0),a(d);var E=s(d,6);n(E,()=>`<code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employees<span class="token variable">@remote</span>
74<span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">8002</span><span class="token punctuation">,</span> <span class="token string">'Juan'</span><span class="token punctuation">,</span> <span class="token string">'Fernandez'</span><span class="token punctuation">,</span> <span class="token string">'[email protected]'</span><span class="token punctuation">,</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
75        TO_DATE<span class="token punctuation">(</span><span class="token string">'04-OCT-1992'</span><span class="token punctuation">,</span> <span class="token string">'DD-MON-YYYY'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">'SH_CLERK'</span><span class="token punctuation">,</span> <span class="token number">3000</span><span class="token punctuation">,</span>
76        <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token number">121</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code>`,!0),a(E);var y=s(E,14);n(y,()=>`<code class="language-sql"><span class="token comment">-- INNER JOIN with ON clause</span>
77<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees e
78<span class="token keyword">JOIN</span> departments d <span class="token keyword">ON</span> e<span class="token punctuation">.</span>dept_id <span class="token operator">=</span> d<span class="token punctuation">.</span>dept_id<span class="token punctuation">;</span>
79
80<span class="token comment">-- JOIN with USING clause</span>
81<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees
82<span class="token keyword">JOIN</span> departments <span class="token keyword">USING</span> <span class="token punctuation">(</span>dept_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
83
84<span class="token comment">-- NATURAL JOIN (implicit join on columns with same names)</span>
85<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees
86<span class="token keyword">NATURAL</span> <span class="token keyword">JOIN</span> departments<span class="token punctuation">;</span>
87
88<span class="token comment">-- Implicit JOIN (deprecated style)</span>
89<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> employees e<span class="token punctuation">,</span> departments d
90<span class="token keyword">WHERE</span> e<span class="token punctuation">.</span>dept_id <span class="token operator">=</span> d<span class="token punctuation">.</span>dept_id<span class="token punctuation">;</span>
91
92<span class="token comment">-- CROSS JOIN (Cartesian product)</span>
93<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> table1 <span class="token keyword">CROSS</span> <span class="token keyword">JOIN</span> table2<span class="token punctuation">;</span>
94<span class="token comment">-- or</span>
95<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> table1<span class="token punctuation">,</span> table2<span class="token punctuation">;</span></code>`,!0),a(y);var w=s(y,8);n(w,()=>`<code class="language-sql"><span class="token keyword">SELECT</span> BookId<span class="token punctuation">,</span> Title<span class="token punctuation">,</span> Price<span class="token punctuation">,</span> PublicationYear
96<span class="token keyword">FROM</span> Books
97<span class="token keyword">WHERE</span> AuthorId <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> AuthorId <span class="token keyword">FROM</span> Authors <span class="token keyword">WHERE</span> Name <span class="token operator">=</span> <span class="token string">'Alice Smith'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code>`,!0),a(w);var m=s(w,4);n(m,()=>`<code class="language-sql"><span class="token comment">-- Find departments with at least one employee</span>
98<span class="token keyword">SELECT</span> department_name
99<span class="token keyword">FROM</span> departments d
100<span class="token keyword">WHERE</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> employees e <span class="token keyword">WHERE</span>

100 e<span class="token punctuation">.</span>department_id <span class="token operator">=</span> d<span class="token punctuation">.</span>department_id<span class="token punctuation">)</span><span class="token punctuation">;</span>
101
102<span class="token comment">-- Find products that were never ordered</span>
103<span class="token keyword">SELECT</span> product_name
104<span class="token keyword">FROM</span> products p
105<span class="token keyword">WHERE</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token number">1</span> <span class="token keyword">FROM</span> order_items oi <span class="token keyword">WHERE</span> oi<span class="token punctuation">.</span>product_id <span class="token operator">=</span> p<span class="token punctuation">.</span>product_id<span class="token punctuation">)</span><span class="token punctuation">;</span></code>`,!0),a(m);var g=s(m,4);n(g,()=>`<code class="language-sql"><span class="token comment">-- Find employees in departments located at location 1700</span>
106<span class="token keyword">SELECT</span> employee_id<span class="token punctuation">,</span> first_name<span class="token punctuation">,</span> last_name
107<span class="token keyword">FROM</span> employees
108<span class="token keyword">WHERE</span> department_id <span class="token operator">IN</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> department_id <span class="token keyword">FROM</span> departments <span class="token keyword">WHERE</span> location_id <span class="token operator">=</span> <span class="token number">1700</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
109
110<span class="token comment">-- ANY: Higher than at least one product in category 1</span>
111<span class="token keyword">SELECT</span> product_name<span class="token punctuation">,</span> price
112<span class="token keyword">FROM</span> products
113<span class="token keyword">WHERE</span> price <span class="token operator">></span> <span class="token keyword">ANY</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> price <span class="token keyword">FROM</span> products <span class="token keyword">WHERE</span> category_id <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
114
115<span class="token comment">-- ALL: Higher than all products in category 2</span>
116<span class="token keyword">SELECT</span> product_name<span class="token punctuation">,</span> price
117<span class="token keyword">FROM</span> products
118<span class="token keyword">WHERE</span> price <span class="token operator">></span> <span class="token keyword">ALL</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> price <span class="token keyword">FROM</span> products <span class="token keyword">WHERE</span> category_id <span class="token operator">=</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code>`,!0),a(g);var h=s(g,4);n(h,()=>`<code class="language-sql"><span class="token comment">-- Top 10 orders with highest value</span>
119<span class="token keyword">SELECT</span> <span class="token operator">*</span>
120<span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> order_id<span class="token punctuation">,</span> <span class="token function">SUM</span><span class="token punctuation">(</span>quantity <span class="token operator">*</span> unit_price<span class="token punctuation">)</span> <span class="token keyword">AS</span> order_value
121      <span class="token keyword">FROM</span> order_items
122      <span class="token keyword">GROUP</span> <span class="token keyword">BY</span> order_id
123      <span class="token keyword">ORDER</span> <span class="token keyword">BY</span> order_value <span class="token keyword">DESC</span><span class="token punctuation">)</span> top_orders
124<span class="token keyword">WHERE</span> ROWNUM <span class="token operator">&lt;=</span> <span class="token number">10</span><span class="token punctuation">;</span></code>`,!0),a(h);

124var T=s(h,6);n(T,()=>`<code class="language-sql"><span class="token comment">-- Average selling price per book</span>
125<span class="token keyword">SELECT</span> b<span class="token punctuation">.</span>Title<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>b<span class="token punctuation">.</span>Price<span class="token punctuation">)</span> <span class="token keyword">AS</span> AvgPrice
126<span class="token keyword">FROM</span> Books b
127<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> b<span class="token punctuation">.</span>Title
128<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> AvgPrice <span class="token keyword">DESC</span><span class="token punctuation">;</span></code>`,!0),a(T);var b=s(T,4);n(b,()=>`<code class="language-sql"><span class="token comment">-- Books with average sale price over 30</span>
129<span class="token keyword">SELECT</span> b<span class="token punctuation">.</span>Title<span class="token punctuation">,</span> <span class="token function">AVG</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>Price<span class="token punctuation">)</span> <span class="token keyword">AS</span> AvgSalePrice
130<span class="token keyword">FROM</span> Books b
131<span class="token keyword">JOIN</span> Sales s <span class="token keyword">ON</span> b<span class="token punctuation">.</span>BookId <span class="token operator">=</span> s<span class="token punctuation">.</span>BookId
132<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> b<span class="token punctuation">.</span>Title
133<span class="token keyword">HAVING</span> <span class="token function">AVG</span><span class="token punctuation">(</span>s<span class="token punctuation">.</span>Price<span class="token punctuation">)</span> <span class="token operator">></span> <span class="token number">30</span>
134<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> AvgSalePrice <span class="token keyword">DESC</span><span class="token punctuation">;</span></code>`,!0),a(b);var R=s(b,14);n(R,()=>`<code class="language-sql"><span class="token keyword">SELECT</span> <span class="token keyword">LEVEL</span><span class="token punctuation">,</span>
135       LPAD<span class="token punctuation">(</span><span class="token string">'-'</span><span class="token punctuation">,</span> <span class="token number">6</span><span class="token operator">*</span><span class="token punctuation">(</span><span class="token keyword">LEVEL</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token operator">||</span> ename <span class="token keyword">AS</span> hierarchie<span class="token punctuation">,</span>
136       empno<span class="token punctuation">,</span>
137       mgr
138<span class="token keyword">FROM</span> emp
139<span class="token keyword">START</span> <span class="token keyword">WITH</span> mgr <span class="token operator">IS</span> <span class="token boolean">NULL</span>
140<span class="token keyword">CONNECT</span> <span class="token keyword">BY</span> PRIOR empno <span class="token operator">=</span> mgr
141<span class="token keyword">ORDER</span> SIBLINGS <span class="token keyword">BY</span> ename<span class="token punctuation">;</span></code>`,!0),a(R);var S=s(R,6);n(S,()=>`<code class="language-sql"><span class="token comment">-- Combine customers and employees</span>
142<span class="token keyword">SELECT</span> <span class="token string">'Customer'</span> <span class="token keyword">AS</span> record_type<span class="token punctuation">,</span> first_name<span class="token punctuation">,</span> last_name
143<span class="token keyword">FROM</span> customer
144<span class="token keyword">UNION</span>
145<span class="token keyword">SELECT</span> <span class="token string">'Employee'</span><span class="token punctuation">,</span> first_name<span class="token punctuation">,</span> last_name
146<span class="token keyword">FROM</span> employee<span class="token punctuation">;</span></code>`,!0),a(S);var L=s(S,24);n(L,()=>`<code class="language-sql"><span class="token keyword">EXPLAIN</span> <span class="token keyword">PLAN</span>
147<span class="token keyword">SET</span> STATEMENT_ID <span class="token operator">=</span> <span class="token string">'Raise in Tokyo'</span>
148<span class="token keyword">

148INTO</span> plan_table
149<span class="token keyword">FOR</span> <span class="token keyword">UPDATE</span> employees
150<span class="token keyword">SET</span> salary <span class="token operator">=</span> salary <span class="token operator">*</span> <span class="token number">1.10</span>
151<span class="token keyword">WHERE</span> department_id <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> department_id <span class="token keyword">FROM</span> departments <span class="token keyword">WHERE</span> location_id <span class="token operator">=</span> <span class="token number">1700</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
152
153<span class="token comment">-- View the execution plan</span>
154<span class="token keyword">SELECT</span> id<span class="token punctuation">,</span> LPAD<span class="token punctuation">(</span><span class="token string">' '</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token operator">*</span><span class="token punctuation">(</span><span class="token keyword">LEVEL</span><span class="token operator">-</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token operator">||</span>operation operation<span class="token punctuation">,</span> options<span class="token punctuation">,</span>
155       object_name<span class="token punctuation">,</span> object_alias<span class="token punctuation">,</span> position
156<span class="token keyword">FROM</span> plan_table
157<span class="token keyword">START</span> <span class="token keyword">WITH</span> id <span class="token operator">=</span> <span class="token number">0</span> <span class="token operator">AND</span> statement_id <span class="token operator">=</span> <span class="token string">'Raise in Tokyo'</span>
158<span class="token keyword">CONNECT</span> <span class="token keyword">BY</span> PRIOR id <span class="token operator">=</span> parent_id <span class="token operator">AND</span> statement_id <span class="token operator">=</span> <span class="token string">'Raise in Tokyo'</span>
159<span class="token keyword">ORDER</span> <span class="token keyword">BY</span> id<span class="token punctuation">;</span></code>`,!0),a(L),I(20),f(A,N)}export{J as default,M as metadata};