Repository navigation
Expand file tree
/
Copy pathsql-playground.html
More file actions
52 lines (52 loc) · 31.6 KB
/
Copy pathsql-playground.html
File metadata and controls
52 lines (52 loc) · 31.6 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<meta name="theme-color" content="#f7f7f3"><title>SQL Fieldnotes — Learn SQL by doing</title>
<style>
:root{color-scheme:light;--ink:#17241f;--muted:#63716a;--paper:#f7f7f3;--card:#fff;--line:#dfe5dc;--green:#176b4b;--mint:#e5f2e8;--lime:#d9ef9a;--orange:#ef9f68;--shadow:0 14px 38px #1831240d}*{box-sizing:border-box}html{scroll-behavior:smooth}body{margin:0;background:var(--paper);color:var(--ink);font:15px/1.55 Inter,ui-sans-serif,system-ui,-apple-system,"Segoe UI",sans-serif}a{color:var(--green)}button,select,textarea{font:inherit}button,select{color:inherit}button:focus-visible,a:focus-visible,select:focus-visible,textarea:focus-visible{outline:3px solid #72a984;outline-offset:2px}.wrap{max-width:1380px;margin:auto;padding:0 28px}.topbar{height:72px;border-bottom:1px solid var(--line);display:flex;align-items:center}.brand{font-weight:850;letter-spacing:-.04em;display:flex;align-items:center;gap:10px}.mark{height:32px;width:32px;border-radius:10px;background:var(--green);color:#fff;display:grid;place-items:center;font:700 15px ui-monospace,monospace}.topnav{margin-left:auto;display:flex;gap:22px;font-size:13px;color:var(--muted)}.topnav a{text-decoration:none}.hero{padding:64px 0 43px;display:grid;grid-template-columns:1.25fr .75fr;gap:38px;align-items:end}.eyebrow{text-transform:uppercase;letter-spacing:.13em;font-size:11px;font-weight:800;color:var(--green)}h1{font-size:clamp(40px,6vw,67px);letter-spacing:-.065em;line-height:1.01;margin:15px 0 18px;max-width:690px}.hero p{font-size:17px;color:var(--muted);max-width:650px;margin:0}.hero-note{padding:20px 22px;border:1px solid #cdddcf;border-radius:15px;background:var(--mint)}.hero-note strong{display:block;font-size:14px;margin-bottom:5px}.hero-note p{font-size:13px;line-height:1.5}.pill{display:inline-flex;gap:7px;align-items:center;background:#fff;border:1px solid var(--line);padding:6px 10px;border-radius:99px;font-size:11px;font-weight:700;color:#46564c}.dot{width:7px;height:7px;border-radius:50%;background:#46966b}.layout{display:grid;grid-template-columns:minmax(0,1fr) 300px;gap:22px;align-items:start;padding-bottom:60px}.maincol,.sidecol{display:grid;gap:20px}.card{border:1px solid var(--line);border-radius:16px;background:var(--card);box-shadow:var(--shadow);overflow:hidden}.cardhead{padding:19px 22px 14px;display:flex;justify-content:space-between;gap:18px;align-items:center}.cardhead h2,.cardhead h3{margin:0;font-size:16px;letter-spacing:-.025em}.cardhead p{margin:3px 0 0;font-size:12px;color:var(--muted)}.editorbox{background:#19251f;margin:0 15px;border-radius:12px;overflow:hidden}.editorbar{padding:9px 13px;border-bottom:1px solid #33413a;display:flex;align-items:center;gap:8px;color:#bdc9c0;font-size:11px}.lights{display:flex;gap:5px;margin-right:6px}.lights i{width:7px;height:7px;background:#67806f;border-radius:50%}.editorbar .spacer{flex:1}#editor{width:100%;height:250px;resize:vertical;display:block;padding:17px 18px;border:0;background:#19251f;color:#e3efe6;outline:none;font:13px/1.7 ui-monospace,SFMono-Regular,Consolas,monospace;tab-size:2}.editorbar select{background:#26362d;border:1px solid #405248;color:#e2ede5;border-radius:6px;padding:5px 8px;font-size:11px}.actions{display:flex;align-items:center;gap:9px;padding:14px 15px}.btn{border:1px solid var(--line);border-radius:9px;padding:8px 13px;background:white;font-weight:700;font-size:12px;cursor:pointer}.btn.primary{background:var(--green);color:white;border-color:var(--green)}.btn:hover{filter:brightness(.96)}.hint{font-size:11px;color:var(--muted);margin-left:auto}.status{margin:0 15px 15px;border-radius:9px;padding:10px 12px;font-size:12px;display:none}.status.show{display:block}.status.good{background:#edf7ed;color:#245c35}.status.error{background:#fff0eb;color:#9d442a}.results{padding:0 15px 16px}.result-title{display:flex;align-items:center;justify-content:space-between;font-size:12px;font-weight:750;padding:3px 2px 9px}.tablewrap{overflow:auto;border:1px solid var(--line);border-radius:9px;max-height:290px}table{border-collapse:collapse;width:100%;font-size:12px;white-space:nowrap}th{position:sticky;top:0;background:#f1f4ee;text-align:left;font-size:10px;letter-spacing:.05em;text-transform:uppercase;color:#56645b}th,td{padding:9px 12px;border-bottom:1px solid #edf0eb}tbody tr:last-child td{border-bottom:0}.null{color:#89958d;font-style:italic}.empty{padding:17px;color:var(--muted);font-size:12px}.catalog{padding:20px 21px}.catalog h2{margin:0 0 5px;font-size:18px;letter-spacing:-.03em}.sub{font-size:12px;color:var(--muted);margin:0 0 20px}.feature-grid{display:grid;grid-template-columns:repeat(2,1fr);gap:10px}.feature{border:1px solid var(--line);border-radius:11px;padding:12px 13px;background:#fff}.feature h3{font-size:12px;margin:0 0 4px}.feature p{font-size:11px;color:var(--muted);margin:0;line-height:1.45}.tags{margin-top:8px;display:flex;gap:5px;flex-wrap:wrap}.tag{font-size:9px;background:#f0f4ee;border-radius:5px;padding:2px 6px;color:#496050}.tag.run{background:#e3f2e7;color:#206241}.tag.teach{background:#fff1df;color:#845319}.sidebody{padding:0 17px 17px}.sidebody p,.sidebody li{font-size:12px;color:var(--muted)}.sidebody ul{padding-left:18px;margin:8px 0}.step{display:flex;gap:10px;align-items:flex-start;padding:10px 0;border-top:1px solid #edf0eb}.step:first-of-type{border:0}.n{flex:none;width:23px;height:23px;display:grid;place-items:center;border-radius:7px;background:#e7f2e8;color:var(--green);font-size:11px;font-weight:800}.step strong{font-size:11px;display:block}.step span{font-size:10px;color:var(--muted);display:block;line-height:1.35}.table-buttons{display:grid;grid-template-columns:repeat(3,1fr);gap:7px;padding:0 17px 15px}.table-btn{background:white;border:1px solid var(--line);border-radius:8px;padding:9px 6px;cursor:pointer;font:11px ui-monospace,monospace}.table-btn[aria-pressed=true]{border-color:var(--green);background:#eaf4eb;color:var(--green)}.table-preview{padding:0 20px 20px}.table-browser .table-buttons{max-width:520px;padding:0 20px 15px}.table-browser #table-preview .tablewrap{max-height:430px}.table-browser table{font-size:13px}.table-browser th,.table-browser td{padding:11px 15px}.example-list{padding:0 14px 14px;display:grid;gap:6px}.example{width:100%;text-align:left;background:#fff;border:1px solid transparent;border-radius:8px;padding:9px 10px;cursor:pointer}.example:hover{border-color:var(--line);background:#fafbf8}.example strong{font-size:11px;display:block}.example span{font-size:10px;color:var(--muted)}.legend{display:flex;gap:12px;font-size:10px;color:var(--muted);padding:0 21px 16px}.legend b{color:#236341}.legend i{font-style:normal;color:#845319}footer{border-top:1px solid var(--line);padding:24px 0;color:var(--muted);font-size:11px}.sr{position:absolute;width:1px;height:1px;padding:0;margin:-1px;overflow:hidden;clip:rect(0,0,0,0);white-space:nowrap;border:0}@media(max-width:900px){.layout{grid-template-columns:1fr}.sidecol{grid-template-columns:repeat(2,minmax(0,1fr))}.hero{grid-template-columns:1fr;gap:22px;padding-top:45px}}@media(max-width:620px){.wrap{padding:0 15px}.topnav{gap:12px;font-size:11px}.hero{padding:38px 0 27px}.hero p{font-size:15px}.sidecol{grid-template-columns:1fr}.feature-grid{grid-template-columns:1fr}.hint{display:none}.cardhead{padding-left:16px;padding-right:16px}}
</style></head>
<body><header class="topbar"><div class="wrap" style="width:100%;display:flex;align-items:center"><a class="brand" href="#top" style="text-decoration:none;color:inherit"><span class="mark">{ }</span> SQL Fieldnotes</a><nav class="topnav" aria-label="Main navigation"><a href="#playground">Playground</a><a href="#catalog">SQL guide</a><a href="#how">How it works</a></nav></div></header>
<main id="top" class="wrap"><section class="hero"><div><div class="eyebrow">A hands-on introduction</div><h1>Make data make sense.</h1><p>Learn SQL by asking questions of a tiny shop database. Change a query, run it, and see how each clause shapes the answer.</p></div><aside class="hero-note"><strong><span class="pill"><span class="dot"></span> Local learning simulator</span></strong><p>Your queries run in this page against sample data. No server or database connection is used. PostgreSQL is the reference dialect; syntax differences are called out below.</p></aside></section>
<section class="card table-browser"><div class="cardhead"><div><h2>Browse the tables</h2><p>Explore all the sample rows and fields used by the lessons.</p></div><span class="pill">Live sample data</span></div><div id="table-buttons" class="table-buttons" role="group" aria-label="Choose a sample table"></div><div id="table-preview" class="table-preview"></div></section>
<div class="layout"><div class="maincol"><section id="playground" class="card" aria-labelledby="play-title"><div class="cardhead"><div><h2 id="play-title">Query playground</h2><p>Lessons move from the basics to combined queries, then data changes.</p></div><span class="pill">PostgreSQL style · subset</span></div><div class="editorbox"><div class="editorbar"><span class="lights"><i></i><i></i><i></i></span><label for="examples">START WITH</label><select id="examples" aria-label="Choose a SQL example"></select><span class="spacer"></span><span>SQL</span></div><label class="sr" for="editor">SQL query editor</label><textarea id="editor" spellcheck="false" autocapitalize="off" autocomplete="off" aria-describedby="editor-help"></textarea></div><div class="actions"><button id="run" class="btn primary">▶ Run query</button><button id="reset" class="btn">↺ Reset data</button><span id="editor-help" class="hint">Ctrl / ⌘ + Enter to run · Changes stay in this tab</span></div><div id="status" class="status" role="status" aria-live="polite"></div><div class="results"><div class="result-title"><span>Output</span><span id="result-meta" style="font-weight:400;color:var(--muted)">Ready when you are</span></div><div id="output" class="empty">Run an example to see rows or a data-change summary here.</div></div></section>
<section id="catalog" class="card catalog"><h2>SQL, one useful idea at a time</h2><p class="sub">Green labels run in this simulator. Amber labels are illustrative: learn the pattern, then try it in PostgreSQL.</p><div id="features" class="feature-grid"></div><div class="legend"><span><b>● Runs here</b></span><span><i>● Example only</i></span><span>Examples use PostgreSQL style unless noted.</span></div></section></div>
<aside class="sidecol">
<section id="how" class="card"><div class="cardhead"><div><h3>How it works</h3><p>A query is a set of instructions.</p></div></div><div class="sidebody"><p>This is a small teaching interpreter, not PostgreSQL itself. It supports a practical subset and reports unsupported syntax instead of silently guessing.</p><div class="step"><span class="n">1</span><div><strong>FROM & JOIN</strong><span>Choose rows and connect related tables.</span></div></div><div class="step"><span class="n">2</span><div><strong>WHERE</strong><span>Keep only rows that match.</span></div></div><div class="step"><span class="n">3</span><div><strong>GROUP & HAVING</strong><span>Summarize rows, then filter groups.</span></div></div><div class="step"><span class="n">4</span><div><strong>SELECT, sort, limit</strong><span>Shape the output and choose what to see first.</span></div></div></div></section>
<section class="card"><div class="cardhead"><div><h3>Lesson menu</h3><p>Click to load a query into the editor.</p></div></div><div id="lesson-list" class="example-list"></div></section></aside></div></main><footer><div class="wrap">SQL Fieldnotes · A self-contained, offline-friendly learning page. Data resets when you reload or press Reset.</div></footer>
<script>
'use strict';
const ORIGINAL={customers:[{customer_id:1,name:'Amina Patel',city:'Pune',email:'amina@example.com'},{customer_id:2,name:'Jon Bell',city:'Austin',email:'jon@example.com'},{customer_id:3,name:'Mei Chen',city:'Pune',email:null},{customer_id:4,name:'Luis Rivera',city:'Lisbon',email:'luis@example.com'},{customer_id:5,name:'Sam Okafor',city:null,email:'sam@example.com'}],products:[{product_id:10,product_name:'Notebook',category:'Stationery',price:8.5,stock:40},{product_id:11,product_name:'Desk lamp',category:'Home',price:32,stock:12},{product_id:12,product_name:'Travel mug',category:'Home',price:18,stock:0},{product_id:13,product_name:'Pencil set',category:'Stationery',price:5,stock:100}],orders:[{order_id:100,customer_id:1,product_id:10,quantity:3,order_date:'2026-03-02',status:'shipped'},{order_id:101,customer_id:1,product_id:11,quantity:1,order_date:'2026-03-08',status:'shipped'},{order_id:102,customer_id:2,product_id:12,quantity:2,order_date:'2026-03-10',status:'processing'},{order_id:103,customer_id:3,product_id:10,quantity:5,order_date:'2026-03-13',status:'shipped'},{order_id:104,customer_id:4,product_id:13,quantity:4,order_date:'2026-03-18',status:'cancelled'}]};let db=clone(ORIGINAL);function clone(x){return JSON.parse(JSON.stringify(x))}
const EXAMPLES=[
{name:'01 · First SELECT',group:'Level 1 · Basics',sql:'SELECT customer_id, name, city\nFROM customers\nORDER BY name;'},
{name:'02 · Filter rows + NULL check',group:'Level 1 · Filtering',sql:'SELECT name, city\nFROM customers\nWHERE city IS NOT NULL\nORDER BY city;'},
{name:'03 · DISTINCT + COALESCE',group:'Level 1 · Unique values',sql:'SELECT DISTINCT city, COALESCE(city, \'Not provided\') AS city_label\nFROM customers\nORDER BY city_label;'},
{name:'04 · Sort + limit',group:'Level 2 · Ordering',sql:'SELECT product_name, price\nFROM products\nORDER BY price DESC\nLIMIT 2;'},
{name:'05 · LIKE + BETWEEN',group:'Level 2 · Multiple filters',sql:"SELECT product_name, price\nFROM products\nWHERE product_name LIKE '%book%' OR price BETWEEN 10 AND 20\nORDER BY price;"},
{name:'06 · CASE expression',group:'Level 2 · Conditional values',sql:"SELECT product_name, stock, CASE WHEN stock = 0 THEN 'Out of stock' WHEN stock < 20 THEN 'Running low' ELSE 'In stock' END AS stock_note\nFROM products\nORDER BY stock;"},
{name:'07 · Filter + join',group:'Level 3 · Join tables',sql:"SELECT c.name, p.product_name, o.quantity, o.status\nFROM orders AS o\nINNER JOIN customers AS c ON o.customer_id = c.customer_id\nINNER JOIN products AS p ON o.product_id = p.product_id\nWHERE o.status = 'shipped'\nORDER BY c.name;"},
{name:'08 · LEFT JOIN + COUNT',group:'Level 3 · Keep unmatched rows',sql:'SELECT c.name, COUNT(o.order_id) AS order_count\nFROM customers c\nLEFT JOIN orders o ON o.customer_id = c.customer_id\nGROUP BY c.name\nORDER BY order_count DESC, c.name;'},
{name:'09 · GROUP BY + HAVING',group:'Level 4 · Grouped summaries',sql:'SELECT p.category, SUM(o.quantity) AS units, COUNT(*) AS order_lines\nFROM orders o\nJOIN products p ON p.product_id = o.product_id\nGROUP BY p.category\nHAVING SUM(o.quantity) >= 4\nORDER BY units DESC;'},
{name:'10 · Insert a customer',group:'Data changes · INSERT',sql:"INSERT INTO customers (customer_id, name, city, email)\nVALUES (6, 'Rae Kim', 'Seoul', NULL);"},
{name:'11 · Update a price',group:'Data changes · UPDATE',sql:'UPDATE products\nSET price = 9.25\nWHERE product_id = 10;'},
{name:'12 · Delete cancelled orders',group:'Data changes · DELETE',sql:"DELETE FROM orders\nWHERE status = 'cancelled';"}
];
const FEATURES=[
['Querying','SELECT, FROM','Choose columns from a table. Use * to inspect every column.','SELECT name FROM customers;',1],['Querying','WHERE, DISTINCT','Filter rows with AND / OR / NOT, comparisons, IS NULL, IN, BETWEEN, and LIKE. DISTINCT removes duplicates.','WHERE city IS NULL',1],['Querying','ORDER BY, LIMIT','Sort with ASC / DESC and cap results with LIMIT. PostgreSQL also supports OFFSET.','ORDER BY price DESC LIMIT 5',1],['Querying','GROUP BY, HAVING','Aggregate related rows, then use HAVING to filter the grouped results.','GROUP BY category HAVING SUM(stock) > 10',1],['Joins','INNER, LEFT JOIN','Match rows with ON. INNER keeps matches; LEFT also keeps unmatched rows from the left table. RIGHT and FULL are PostgreSQL features shown below.','LEFT JOIN orders ON …',1],['Joins','RIGHT, FULL, CROSS, SELF','PostgreSQL supports these joins. CROSS pairs every row; SELF JOIN relates a table to itself using aliases. These patterns are examples only here.','customers a JOIN customers b ON …',0],['Changes','INSERT, UPDATE, DELETE','Add, change, or remove rows in this page’s temporary sample data.','UPDATE products SET …',1],['Set operations','UNION, UNION ALL','Combine compatible query results. UNION removes duplicates; UNION ALL keeps them. Illustrative syntax; not executed here.','SELECT city … UNION ALL …',0],['Functions','COUNT, SUM, AVG, MIN, MAX','Summarize values. COUNT(column) ignores NULL; COUNT(*) counts rows.','SELECT AVG(price) FROM products',1],['Expressions','CASE, COALESCE','Choose a value conditionally or replace NULL with a fallback.','COALESCE(email, \'unknown\')',1],['PostgreSQL dialect','TOP, FETCH, EXCEPT / MINUS','PostgreSQL uses LIMIT (or FETCH FIRST). TOP is common in SQL Server. PostgreSQL uses EXCEPT; Oracle commonly uses MINUS.','EXCEPT (PostgreSQL) / MINUS (Oracle)',0],['Joins & queries','Subqueries, WITH (CTEs)','A subquery nests a query. WITH names a temporary result for one statement. Supported in PostgreSQL; teaching examples only here.','WITH totals AS (SELECT …)',0],['Windows','ROW_NUMBER, RANK, OVER','Window functions calculate across related rows without collapsing them. Add OVER (PARTITION BY … ORDER BY …).','ROW_NUMBER() OVER (ORDER BY price)',0],['Changes','MERGE, TRUNCATE','MERGE matches and changes rows; TRUNCATE empties a table. Syntax and transaction behavior vary.','MERGE INTO … / TRUNCATE …',0],['Schema','CREATE, ALTER, DROP, RENAME','Define, change, remove, or rename database objects. DDL behavior differs by database.','CREATE TABLE …',0],['Views & indexes','CREATE VIEW, CREATE INDEX','A view stores a query definition; an index can speed up lookups at a storage cost.','CREATE INDEX … ON …',0],['Constraints','PRIMARY KEY, FOREIGN KEY','Keys identify rows and connect tables. PostgreSQL enforces referenced-key relationships.','FOREIGN KEY (customer_id) REFERENCES …',0],['Constraints','UNIQUE, NOT NULL, CHECK','Rules prevent duplicate values, missing required values, or values outside a condition.','CHECK (price >= 0)',0],['Permissions','GRANT, REVOKE','Give or remove database privileges. Requires suitable database permissions.','GRANT SELECT ON …',0],['Transactions','BEGIN, COMMIT, ROLLBACK','Group changes so they can succeed or be undone together. SAVEPOINT marks a rollback point.','BEGIN; SAVEPOINT s1;',0],['Set operations','UNION / UNION ALL','Combine compatible results; UNION removes duplicates and UNION ALL keeps them. PostgreSQL also has INTERSECT and EXCEPT; Oracle commonly calls EXCEPT-style set difference MINUS.','SELECT city FROM customers UNION ALL SELECT category FROM products',0],['Set operations','INTERSECT, EXCEPT / MINUS','INTERSECT keeps shared rows. EXCEPT returns rows only in the first query (MINUS in Oracle). Each side needs matching column count and compatible types.','SELECT city FROM customers EXCEPT SELECT category FROM products',0],['Queries','RIGHT / FULL JOIN caveat','PostgreSQL supports both. RIGHT JOIN can usually be rewritten as LEFT JOIN by swapping table order.','… FULL OUTER JOIN …',0]
];
function esc(s){return String(s??'').replace(/[&<>"']/g,c=>({'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]))}function cell(v){return v===null?'<span class="null">NULL</span>':esc(v)}
function renderTable(rows,container){if(!rows.length){container.innerHTML='<div class="empty">No rows to show.</div>';return}const cols=Object.keys(rows[0]);container.innerHTML='<div class="tablewrap"><table><thead><tr>'+cols.map(c=>'<th scope="col">'+esc(c)+'</th>').join('')+'</tr></thead><tbody>'+rows.map(row=>'<tr>'+cols.map(c=>'<td>'+cell(row[c])+'</td>').join('')+'</tr>').join('')+'</tbody></table></div>'}
function status(msg,bad=false){const e=document.querySelector('#status');e.className='status show '+(bad?'error':'good');e.textContent=msg}function output(rows,label){document.querySelector('#result-meta').textContent=label;renderTable(rows,document.querySelector('#output'))}
function fillTables(){const b=document.querySelector('#table-buttons');b.innerHTML=Object.keys(db).map((t,i)=>'<button class="table-btn" aria-pressed="'+(!i)+'" data-table="'+esc(t)+'">'+esc(t)+'</button>').join('');b.querySelectorAll('button').forEach(x=>x.onclick=()=>{b.querySelectorAll('button').forEach(y=>y.setAttribute('aria-pressed','false'));x.setAttribute('aria-pressed','true');preview(x.dataset.table)});preview(Object.keys(db)[0])}function preview(t){renderTable(db[t],document.querySelector('#table-preview'))}
function setup(){const sel=document.querySelector('#examples');sel.innerHTML=EXAMPLES.map((x,i)=>'<option value="'+i+'">'+esc(x.name)+'</option>').join('');const pick=i=>{document.querySelector('#editor').value=EXAMPLES[i].sql;sel.value=i;document.querySelector('#editor').focus()};sel.onchange=()=>pick(+sel.value);document.querySelector('#lesson-list').innerHTML=EXAMPLES.map((x,i)=>'<button class="example" data-i="'+i+'"><strong>'+esc(x.name)+'</strong><span>'+esc(x.group)+'</span></button>').join('');document.querySelectorAll('.example').forEach(b=>b.onclick=()=>pick(+b.dataset.i));document.querySelector('#features').innerHTML=FEATURES.map(f=>'<article class="feature"><h3>'+esc(f[1])+'</h3><p>'+esc(f[2])+'</p><div class="tags"><span class="tag">'+esc(f[0])+'</span><span class="tag '+(f[4]?'run':'teach')+'">'+(f[4]?'Runs here':'Example only')+'</span></div></article>').join('');pick(0);fillTables()}
function stripComments(s){return s.replace(/--[^\n]*/g,'').trim().replace(/;\s*$/,'').trim()}
function splitTop(s,word){let out=[],start=0,depth=0,q=null,up=s.toUpperCase(),needle=' '+word.toUpperCase()+' ';for(let i=0;i<s.length;i++){let c=s[i];if(q){if(c===q&&s[i+1]===q)i++;else if(c===q)q=null;continue}if(c==="'"||c==='"'){q=c;continue}if(c==='(')depth++;else if(c===')')depth--;if(depth===0&&up.slice(i,i+needle.length)===needle){if(word.toUpperCase()==='AND'&&/\bBETWEEN\b/i.test(s.slice(start,i)))continue;out.push(s.slice(start,i));start=i+needle.length;i+=needle.length-1}}out.push(s.slice(start));return out}
function parseVal(x,row){x=x.trim();if(/^null$/i.test(x))return null;if(/^'.*'$/.test(x))return x.slice(1,-1).replace(/''/g,"'");if(/^".*"$/.test(x))return x.slice(1,-1);if(/^-?\d+(\.\d+)?$/.test(x))return Number(x);if(row&&Object.prototype.hasOwnProperty.call(row,x))return row[x];let k=x.replace(/^[\w]+\./,'');if(row&&Object.prototype.hasOwnProperty.call(row,k))return row[k];if(/^\w+(\.\w+)?$/.test(x))throw Error('Unknown column “'+x+'”. Check the column name in the table browser.');throw Error('Could not read value: '+x)}
function truth(cond,row){cond=cond.trim().replace(/^\((.*)\)$/,'$1').trim();let parts=splitTop(cond,'OR');if(parts.length>1)return parts.some(x=>truth(x,row));parts=splitTop(cond,'AND');if(parts.length>1)return parts.every(x=>truth(x,row));if(/^NOT\s+/i.test(cond))return !truth(cond.replace(/^NOT\s+/i,''),row);let m=cond.match(/^(.+?)\s+IS\s+(NOT\s+)?NULL$/i);if(m){let v=parseVal(m[1],row);return m[2]?v!==null:v===null}m=cond.match(/^(.+?)\s+(NOT\s+)?IN\s*\((.*)\)$/i);if(m){let v=parseVal(m[1],row),a=m[3].split(',').map(x=>parseVal(x,row));return m[2]?!a.some(x=>x===v):a.some(x=>x===v)}m=cond.match(/^(.+?)\s+(NOT\s+)?BETWEEN\s+(.+?)\s+AND\s+(.+)$/i);if(m){let v=parseVal(m[1],row),ok=v>=parseVal(m[3],row)&&v<=parseVal(m[4],row);return m[2]?!ok:ok}m=cond.match(/^(.+?)\s+(NOT\s+)?LIKE\s+'(.*)'$/i);if(m){let p=m[3].replace(/[.+^${}()|[\]\\]/g,'\\$&').replace(/%/g,'.*').replace(/_/g,'.');let ok=new RegExp('^'+p+'$','i').test(String(parseVal(m[1],row)??''));return m[2]?!ok:ok}m=cond.match(/^(.+?)\s*(=|<>|!=|>=|<=|>|<)\s*(.+)$/);if(!m)throw Error('Unsupported WHERE condition “'+cond+'”. Try comparisons, AND / OR, IN, BETWEEN, LIKE, or IS NULL.');let a=parseVal(m[1],row),b=parseVal(m[3],row);if(a===null||b===null)return false;switch(m[2]){case'=':return a==b;case'<>':case'!=':return a!=b;case'>':return a>b;case'<':return a<b;case'>=':return a>=b;case'<=':return a<=b}}
function aggregate(expr,rows){let m=expr.match(/^(COUNT|SUM|AVG|MIN|MAX)\s*\(\s*(\*|[\w.]+)\s*\)$/i);if(!m)return null;let vals=m[2]==='*'?rows:rows.map(r=>parseVal(m[2],r)).filter(v=>v!==null);switch(m[1].toUpperCase()){case'COUNT':return vals.length;case'SUM':return vals.reduce((a,b)=>a+Number(b),0);case'AVG':return vals.length?vals.reduce((a,b)=>a+Number(b),0)/vals.length:null;case'MIN':return vals.length?vals.reduce((a,b)=>a<b?a:b):null;case'MAX':return vals.length?vals.reduce((a,b)=>a>b?a:b):null}}
function select(sql){if(/\b(WITH|INTERSECT|EXCEPT|MINUS|OVER|ROW_NUMBER|RANK)\b/i.test(sql))throw Error('This is a PostgreSQL feature shown in the guide, but it is not implemented in this learning simulator yet. Try one of the runnable examples.');if(/\b(RIGHT|FULL|CROSS)\s+(OUTER\s+)?JOIN\b/i.test(sql))throw Error('This join type is covered in the guide but not executed by this simulator. INNER and LEFT JOIN are supported.');let m=sql.match(/^SELECT\s+(DISTINCT\s+)?([\s\S]+?)\s+FROM\s+([\s\S]+)$/i);if(!m)throw Error('Expected SELECT … FROM …. This simulator supports SELECT queries; see the guide for illustrative syntax.');let distinct=!!m[1],fields=m[2].trim(),tail=m[3].trim();let clauses={},marks=[];const clauseRe=/\b(WHERE|GROUP\s+BY|HAVING|ORDER\s+BY|LIMIT|OFFSET)\b/ig;let q=null,dep=0;for(let i=0;i<tail.length;i++){let ch=tail[i];if(q){if(ch===q)q=null;continue}if(ch==="'"){q=ch;continue}if(ch==='(')dep++;else if(ch===')')dep--;if(dep===0){clauseRe.lastIndex=i;let cm=clauseRe.exec(tail);if(cm&&cm.index===i){marks.push({key:cm[1].toUpperCase().replace(/\s+/g,' '),start:i,end:clauseRe.lastIndex});i=clauseRe.lastIndex-1}}}let fromEnd=marks.length?marks[0].start:tail.length,fromPart=tail.slice(0,fromEnd).trim();marks.forEach((x,i)=>clauses[x.key]=tail.slice(x.end,i+1<marks.length?marks[i+1].start:tail.length).trim());let baseMatch=fromPart.match(/^([\w]+)(?:\s+(?:AS\s+)?(\w+))?/i);if(!baseMatch||!db[baseMatch[1].toLowerCase()])throw Error('Unknown table. Available tables: '+Object.keys(db).join(', ')+'.');let alias=baseMatch[2]||baseMatch[1],rows=db[baseMatch[1].toLowerCase()].map(r=>({...r,...Object.fromEntries(Object.entries(r).map(([k,v])=>[alias+'.'+k,v]))}));let rest=fromPart.slice(baseMatch[0].length).trim(),joinRe=/\s*(?:(INNER|LEFT)\s+)?JOIN\s+([\w]+)(?:\s+(?:AS\s+)?(\w+))?\s+ON\s+([\s\S]*?)(?=\s+(?:(?:INNER|LEFT)\s+)?JOIN\s+|$)/ig,jm,consumed=0;while((jm=joinRe.exec(rest))){consumed=joinRe.lastIndex;let kind=(jm[1]||'INNER').toUpperCase(),tn=jm[2].toLowerCase(),al=jm[3]||jm[2],on=jm[4];if(!db[tn])throw Error('Unknown joined table “'+tn+'”.');let next=[];for(const r of rows){let matches=db[tn].filter(x=>truth(on,{...r,...x,...Object.fromEntries(Object.entries(x).map(([k,v])=>[al+'.'+k,v]))}));if(matches.length)matches.forEach(x=>next.push({...r,...x,...Object.fromEntries(Object.entries(x).map(([k,v])=>[al+'.'+k,v]))}));else if(kind==='LEFT'){let nil=Object.fromEntries(Object.keys(db[tn][0]||{}).map(k=>[k,null]));next.push({...r,...nil,...Object.fromEntries(Object.keys(nil).map(k=>[al+'.'+k,null]))})}}rows=next}if(rest&&consumed<rest.length)throw Error('Could not read the FROM / JOIN section. Each JOIN needs a table and ON condition.');
if(clauses.WHERE)rows=rows.filter(r=>truth(clauses.WHERE,r));const groups=clauses['GROUP BY']?clauses['GROUP BY'].split(',').map(x=>x.trim()):[];let specs=fields.split(',').map(x=>x.trim()),parseSpec=x=>{let z=x.match(/^(.+?)\s+(?:AS\s+)?([\w]+)$/i);return z&& !/^(CASE|END)$/i.test(z[1])?{expr:z[1].trim(),name:z[2]}:{expr:x,name:x.replace(/^.*\./,'')}};specs=specs.map(parseSpec);let groupRows;if(groups.length||specs.some(s=>aggregate(s.expr,[])!==null)){let map=new Map;for(const r of rows){let key=JSON.stringify(groups.map(g=>parseVal(g,r)));if(!map.has(key))map.set(key,[]);map.get(key).push(r)}groupRows=[...map.values()].map(rs=>({__group:rs,...rs[0]}));if(clauses.HAVING)groupRows=groupRows.filter(g=>{let h=clauses.HAVING.replace(/(COUNT|SUM|AVG|MIN|MAX)\s*\(([^)]+)\)/gi,(z)=>String(aggregate(z,g.__group)??'NULL'));return truth(h,g)})}else groupRows=rows;
function val(expr,r){let a=aggregate(expr,r.__group||[]);if(a!==null)return a;let cm=expr.match(/^COALESCE\((.*)\)$/i);if(cm){for(let p of cm[1].split(',')){let v=parseVal(p,r);if(v!==null)return v}return null}let ca=expr.match(/^CASE\s+WHEN\s+(.+?)\s+THEN\s+'([^']*)'\s+(?:WHEN\s+(.+?)\s+THEN\s+'([^']*)'\s+)?ELSE\s+'([^']*)'\s+END$/i);if(ca)return truth(ca[1],r)?ca[2]:ca[3]?(truth(ca[3],r)?ca[4]:ca[5]):ca[5];return parseVal(expr,r)}let projected=groupRows.map(r=>{let o={};if(specs.length===1&&specs[0].expr==='*')return Object.fromEntries(Object.entries(r).filter(([k])=>!k.includes('.')&&!k.startsWith('__group')));for(const s of specs)o[s.name]=val(s.expr,r);return o});if(distinct){let seen=new Set;projected=projected.filter(r=>{let k=JSON.stringify(r);if(seen.has(k))return false;seen.add(k);return true})}if(clauses['ORDER BY']){let order=clauses['ORDER BY'].split(',').map(x=>{let z=x.trim().match(/^(.+?)(?:\s+(ASC|DESC))?$/i);return[z[1].replace(/^[\w]+\./,''),z[2]||'ASC']});projected.sort((a,b)=>{for(const [k,d] of order){let x=a[k],y=b[k];if(x===y)continue;let n=x===null?1:y===null?-1:x<y?-1:1;return d.toUpperCase()==='DESC'?-n:n}return 0})}if(clauses.LIMIT)projected=projected.slice(0,Number(clauses.LIMIT));if(clauses.OFFSET)projected=projected.slice(Number(clauses.OFFSET));return projected}
function run(){const raw=document.querySelector('#editor').value.trim();if(!raw){status('Write a query or choose a lesson first.',true);return}try{const sql=stripComments(raw);let m;if(/^SELECT\b/i.test(sql)){let rows=select(sql);output(rows,rows.length+' row'+(rows.length===1?'':'s'));status('Query ran successfully. The sample data was not changed.')}else if((m=sql.match(/^INSERT\s+INTO\s+(\w+)\s*\(([^)]+)\)\s*VALUES\s*\(([^)]+)\)$/i))){let t=m[1].toLowerCase();if(!db[t])throw Error('Unknown table “'+t+'”.');let cols=m[2].split(',').map(x=>x.trim()),vals=m[3].split(',').map(x=>parseVal(x));if(cols.length!==vals.length)throw Error('The number of columns and values must match.');let row=Object.fromEntries(cols.map((c,i)=>[c,vals[i]]));if(db[t].some(x=>x[Object.keys(row)[0]]===row[Object.keys(row)[0]]))throw Error('That key already exists. Choose a new key value.');db[t].push(row);fillTables();output([row],'1 row inserted');status('Inserted one row into '+t+'. Changes are temporary; Reset restores the original data.')}else if((m=sql.match(/^UPDATE\s+(\w+)\s+SET\s+([\w]+)\s*=\s*(.+?)\s+WHERE\s+(.+)$/i))){let t=m[1].toLowerCase();if(!db[t])throw Error('Unknown table “'+t+'”.');let val=parseVal(m[3]),n=0;db[t]=db[t].map(r=>{if(truth(m[4],r)){n++;return{...r,[m[2]]:val}}return r});fillTables();output(db[t].filter(r=>truth(m[4],r)),n+' row'+(n===1?'':'s')+' updated');status('Updated '+n+' row(s) in '+t+'. Changes are temporary.')}else if((m=sql.match(/^DELETE\s+FROM\s+(\w+)\s+WHERE\s+(.+)$/i))){let t=m[1].toLowerCase();if(!db[t])throw Error('Unknown table “'+t+'”.');let before=db[t].length;db[t]=db[t].filter(r=>!truth(m[2],r));let n=before-db[t].length;fillTables();output([] ,n+' row'+(n===1?'':'s')+' deleted');status('Deleted '+n+' row(s) from '+t+'. Changes are temporary.')}else throw Error('This statement is shown as a learning example but does not run here. Executable statements: SELECT, INSERT, UPDATE, DELETE.');}catch(e){document.querySelector('#output').textContent='';document.querySelector('#result-meta').textContent='Needs a fix';status(e.message,true)}}
document.querySelector('#run').onclick=run;document.querySelector('#reset').onclick=()=>{db=clone(ORIGINAL);fillTables();document.querySelector('#output').textContent='';document.querySelector('#result-meta').textContent='Data restored';status('Sample tables restored to their original rows.');};document.querySelector('#editor').addEventListener('keydown',e=>{if((e.ctrlKey||e.metaKey)&&e.key==='Enter'){e.preventDefault();run()}});setup();
</script></body></html>