SQL Database Operations Studio

Lesson 01 - Orientation

A persistent relational database inside one HTML file

The workbench models tables, rows, columns, keys, indexes, views, grants, transactions, query history, and a practical subset of both Microsoft SQL Server and Oracle syntax. Run statements, inspect results, and preserve state without installing a database server.

Choose a SQL dialectRun selection or scriptInspect schema stateReset or export work

1

What the lab executes

Statements operate on durable in-browser relational objects

DDL

Schema objects

Create, alter, describe, index, query, and drop tables and views in the simulated catalog.

DML

Row state

Insert, update, and delete rows; transactions can commit or restore a previous snapshot.

SQL

Query processing

Filter, join, group, aggregate, sort, calculate expressions, and limit result sets.

Simulation boundary

No SQL is sent to Oracle, SQL Server, a network service, or the student computer. The parser implements a deliberately useful teaching subset, so unsupported vendor features return a clear lab message.

2

First five minutes

Inventory the schema before writing a complex query

01

List relational objects

The simulator provides a concise metadata command in both dialect modes.

SHOW TABLES;

02

Describe a table

Column names, types, nullability, and key roles define valid operations.

DESC customers;

03

Run a small sample

SQL Server uses TOP; Oracle commonly uses FETCH FIRST. The dialect selector changes guidance but the lab accepts both for comparison.

SELECT TOP 5 customer_id, customer_name, region FROM customers ORDER BY customer_id;

04

Inspect query history

Every executed statement records text, outcome, and simulated elapsed time in the inspector.

SELECT @@VERSION;

Knowledge check

What is the safest first step after receiving access to an unfamiliar database?

Lesson 02 - Relational model

Reason about tables, keys, rows, and relationships

A relational design represents entities in tables and connects related rows through keys. Query correctness depends on understanding grain: what one row means in each table before joining or aggregating.

Identify table grainRecognize primary keysFollow foreign keysAvoid duplicate multiplication

1

Included schemas

SALES and HR provide related teaching data

Table	One row represents	Key or relationship
`CUSTOMERS`	One customer organization	`CUSTOMER_ID` primary key
`ORDERS`	One customer order	`ORDER_ID`; `CUSTOMER_ID` relates to customers
`ORDER_ITEMS`	One product line on an order	`ORDER_ID` and `PRODUCT_ID` identify relationships
`PRODUCTS`	One catalog product	`PRODUCT_ID` primary key
`EMPLOYEES`	One employee	`DEPARTMENT_ID` relates to departments

2

Explore table grain

Use counts and samples together

01

Count customers

COUNT(*) reports the number of rows after FROM and WHERE processing.

SELECT COUNT(*) AS customer_count FROM customers;

02

Inspect order keys

A small ordered projection reveals the relationship columns without returning every attribute.

SELECT TOP 6 order_id, customer_id, order_date, status FROM orders ORDER BY order_id;

03

Compare order and line counts

More item rows than order rows is expected because one order can contain multiple lines.

SELECT COUNT(*) AS order_count FROM orders;
SELECT COUNT(*) AS line_count FROM order_items;

Grain before joins

State in plain language what one row means in every source. A one-to-many join legitimately repeats the one-side values; an incorrect key can multiply rows unintentionally.

Knowledge check

Why can joining ORDERS to ORDER_ITEMS increase the number of rows?

Lesson 03 - SELECT

Project the columns and rows needed for a question

SELECT describes the result shape. Begin with named columns, readable aliases, and a deliberate row limit while exploring; use SELECT * only when the complete shape is genuinely useful.

Select named columnsCreate aliasesUse DISTINCTLimit exploratory results

1

Logical query shape

Projection is only one stage of query processing

query skeletonSELECT [DISTINCT] expression AS alias
FROM relation
WHERE row_condition
ORDER BY expression;

2

Projection practice

Build a result one decision at a time

01

Select explicit customer columns

Explicit projections document what downstream users should receive.

SELECT customer_id, customer_name, region, tier FROM customers;

02

Create readable aliases

AS labels an output expression without renaming the underlying column.

SELECT customer_name AS customer, region AS sales_region FROM customers;

03

Return distinct regions

DISTINCT removes duplicate projected rows after expression evaluation.

SELECT DISTINCT region FROM customers ORDER BY region;

04

Compare dialect row limits

Use the dialect selector, then try the SQL Server and Oracle forms.

SELECT TOP 3 customer_id, customer_name FROM customers ORDER BY customer_id;

SELECT customer_id, customer_name FROM customers ORDER BY customer_id FETCH FIRST 3 ROWS ONLY;

Knowledge check

What does an output alias change?

Lesson 04 - Filtering

Express precise row conditions with three-valued logic in mind

WHERE keeps rows for which the predicate evaluates true. Comparisons, ranges, lists, patterns, null tests, AND, OR, and parentheses let you translate a question into explicit conditions.

Use comparisonsCombine predicatesMatch patternsTest NULL correctly

1

Predicate families

Choose semantics that match the question

Purpose	Operator	Example
Comparison	`=, <>, >, >=, <, <=`	`unit_price >= 250`
Set membership	`IN`	`region IN ('NORTH', 'EAST')`
Range	`BETWEEN`	`unit_price BETWEEN 150 AND 500`
Pattern	`LIKE`	`customer_name LIKE '%Labs%'`
Missing value	`IS NULL`	`email IS NULL`

2

Filtering practice

Start broad, then add constraints

01

Filter one category

Text literals use single quotes.

SELECT product_name, category, unit_price FROM products WHERE category = 'SOFTWARE';

02

Combine conditions

AND requires both predicates to be true.

SELECT product_name, unit_price FROM products WHERE active = 'Y' AND unit_price >= 250;

03

Use a list and a range

IN and BETWEEN often communicate intent better than repeated comparisons.

SELECT customer_name, region, tier FROM customers WHERE region IN ('NORTH', 'EAST') AND tier IN ('GOLD', 'PLATINUM');

SELECT product_name, unit_price FROM products WHERE unit_price BETWEEN 150 AND 500;

04

Find missing email addresses

NULL is tested with IS NULL, not equality.

SELECT customer_id, customer_name FROM customers WHERE email IS NULL;

Parentheses communicate intent

When AND and OR appear together, use parentheses to make grouping explicit. Correct logic is more important than a shorter predicate.

Knowledge check

Which predicate correctly identifies missing email values?

Lesson 05 - Ordering

Make result order and pagination deliberate

Rows in a relational result have no guaranteed presentation order unless ORDER BY states one. Sort by business meaning, include deterministic tie-breakers, and apply limits only after defining the desired order.

Sort ascending or descendingUse multiple keysAdd tie-breakersLimit after ordering

1

Deterministic ordering

The leftmost sort key has highest priority

01

Sort products by price

DESC places the largest numeric values first.

SELECT product_name, category, unit_price FROM products ORDER BY unit_price DESC;

02

Sort by region and customer

The second expression orders rows that share the first value.

SELECT region, customer_name, tier FROM customers ORDER BY region ASC, customer_name ASC;

03

Return the latest orders

Order before applying TOP or FETCH FIRST.

SELECT TOP 5 order_id, customer_id, order_date, status FROM orders ORDER BY order_date DESC, order_id DESC;

04

Use Oracle-style limiting

FETCH FIRST follows ORDER BY in this form.

SELECT order_id, order_date, status FROM orders ORDER BY order_date DESC FETCH FIRST 5 ROWS ONLY;

TOP without ORDER BY is not “latest”

A row limit answers how many rows to return. ORDER BY answers which rows belong in that limited result.

Knowledge check

What makes a “top five most recent orders” query meaningful?

Lesson 06 - Expressions

Transform values and handle missing data explicitly

SQL expressions calculate output values without changing stored data. String functions, numeric functions, arithmetic, null-handling functions, and CASE logic make query results easier to interpret.

Call scalar functionsCalculate arithmetic expressionsHandle NULLClassify with CASE

1

Cross-dialect function map

Some functions share semantics but use different names

Purpose	Portable or common	Dialect variants
Uppercase	`UPPER(value)`	Same in both
Round numeric	`ROUND(value, 2)`	Same in both
First non-null	`COALESCE(a,b)`	`ISNULL` in SQL Server; `NVL` in Oracle
String length	`LENGTH` in Oracle	`LEN` in SQL Server
Current date/time	`CURRENT_DATE`	`GETDATE()`; `SYSDATE`

2

Expression practice

Calculate values in the result

01

Normalize display text

Functions can be nested around a column expression.

SELECT customer_id, UPPER(customer_name) AS customer_upper, LOWER(region) AS region_lower FROM customers;

02

Replace missing email values

COALESCE chooses the first non-null argument.

SELECT customer_name, COALESCE(email, 'not provided') AS contact_email FROM customers ORDER BY customer_name;

03

Calculate line totals

Arithmetic expressions can combine several columns.

SELECT order_id, product_id, quantity, unit_price, ROUND(quantity * unit_price * (1 - discount), 2) AS net_line_total FROM order_items ORDER BY order_id;

04

Classify prices

CASE converts a condition into a labeled result.

SELECT product_name, unit_price, CASE WHEN unit_price >= 400 THEN 'HIGH' ELSE 'STANDARD' END AS price_band FROM products ORDER BY unit_price DESC;

Knowledge check

What does COALESCE(email, 'not provided') return when email is NULL?

Lesson 07 - Joins

Combine related tables using explicit keys and aliases

A join creates a row set from multiple relations. Table aliases keep qualified column references readable; the ON predicate defines how rows correspond; the join type controls preservation of unmatched rows.

Use table aliasesJoin on related keysCompare INNER and LEFT joinsDetect row multiplication

1

Join types

Preservation rules matter

IN

INNER JOIN

Returns only combinations that satisfy the ON condition.

LEFT

LEFT JOIN

Preserves every row from the left source and supplies NULL for unmatched right-side columns.

KEY

Qualified keys

References such as o.customer_id make source and relationship intent explicit.

2

Join practice

Begin with two tables, then add a third

01

Join orders to customers

The foreign key on ORDERS matches the primary key on CUSTOMERS.

SELECT o.order_id, o.order_date, c.customer_name, o.status FROM orders o JOIN customers c ON o.customer_id = c.customer_id ORDER BY o.order_id;

02

Preserve customers without orders

LEFT JOIN retains customers even when no matching order exists.

SELECT c.customer_id, c.customer_name, o.order_id, o.status FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id ORDER BY c.customer_id, o.order_id;

03

Add order-item detail

A third relation changes the grain to one row per order line.

SELECT o.order_id, c.customer_name, oi.product_id, oi.quantity FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_items oi ON o.order_id = oi.order_id ORDER BY o.order_id, oi.product_id;

04

Join products for names

Follow the product key from each line to the catalog.

SELECT o.order_id, c.customer_name, p.product_name, oi.quantity FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id ORDER BY o.order_id;

Knowledge check

Which join preserves every customer even when that customer has no order?

Lesson 08 - Aggregation

Summarize many rows into meaningful groups

Aggregate functions calculate across a set of rows. GROUP BY defines independent sets, HAVING filters completed groups, and WHERE filters input rows before aggregation.

Use COUNT, SUM, AVG, MIN, MAXDefine grouping keysFilter rows with WHEREFilter groups with HAVING

1

Logical distinction

WHERE and HAVING act at different stages

Clause	Operates on	Example question
`WHERE`	Individual input rows	Include only shipped orders
`GROUP BY`	Grouping keys	Create one group per customer
Aggregate	Rows within each group	Calculate revenue per customer
`HAVING`	Completed groups	Keep customers with revenue above a threshold

2

Aggregation practice

Build summaries from simple to relational

01

Count orders by status

Every selected nonaggregate column belongs in GROUP BY.

SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status ORDER BY order_count DESC;

02

Summarize product prices

Multiple aggregate functions can describe the same group.

SELECT category, COUNT(*) AS products, ROUND(AVG(unit_price), 2) AS avg_price, MIN(unit_price) AS min_price, MAX(unit_price) AS max_price FROM products GROUP BY category ORDER BY category;

03

Calculate customer revenue

Aggregate calculated line totals after joining the necessary tables.

SELECT c.customer_name, ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount)), 2) AS revenue FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_items oi ON o.order_id = oi.order_id GROUP BY c.customer_name ORDER BY revenue DESC;

04

Filter completed groups

HAVING can evaluate an aggregate expression.

SELECT c.customer_name, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name HAVING COUNT(o.order_id) >= 2 ORDER BY order_count DESC;

Knowledge check

Which clause filters groups after COUNT or SUM has been calculated?

Lesson 09 - DML

Insert, update, and delete rows with verifiable scope

Data manipulation changes durable row state. Use explicit column lists, key-based predicates, pre-change SELECT statements, transactions, row-count review, and post-change verification.

Insert explicit valuesUpdate targeted rowsDelete deliberatelyVerify every change

DML safety sequence

Begin a transaction, SELECT the intended rows, execute the change, inspect the row count and resulting rows, then choose COMMIT or ROLLBACK.

1

Controlled DML exercise

Use customer 8 as a reversible example

01

Inspect the target row

The same predicate should be reusable in the UPDATE.

SELECT customer_id, customer_name, tier FROM customers WHERE customer_id = 8;

02

Begin and update

The simulator snapshots database state at BEGIN.

BEGIN TRANSACTION;
UPDATE customers SET tier = 'SILVER' WHERE customer_id = 8;

03

Verify pending state

A SELECT in the same session sees the uncommitted simulation state.

SELECT customer_id, customer_name, tier FROM customers WHERE customer_id = 8;

04

Roll back the exercise

Restore the snapshot and verify the original value.

ROLLBACK;
SELECT customer_id, customer_name, tier FROM customers WHERE customer_id = 8;

2

INSERT and DELETE forms

Explicit columns and predicates improve reviewability

DML patternsINSERT INTO lab_notes (note_id, topic, note_text)
VALUES (1, 'safety', 'verify before commit');

DELETE FROM lab_notes
WHERE note_id = 1;

Knowledge check

What is the greatest risk of an UPDATE statement without a WHERE clause?

Lesson 10 - DDL

Define tables and constraints that communicate data intent

Data definition establishes object names, columns, types, nullability, keys, and indexes. Good schemas make invalid states harder to represent and common access paths easier to support.

Create a tableChoose column typesRequire essential valuesAlter and inspect schema

1

Create a lab table

Build an object without changing the seeded business tables

01

Create LAB_NOTES

The primary key identifies each row; NOT NULL requires a topic.

CREATE TABLE lab_notes (
  note_id INTEGER PRIMARY KEY,
  topic VARCHAR(40) NOT NULL,
  note_text VARCHAR(200),
  created_at DATE
);

02

Describe the new table

Metadata verification is part of successful DDL.

DESC lab_notes;

03

Add a severity column

ALTER TABLE changes the persistent table definition.

ALTER TABLE lab_notes ADD severity VARCHAR(12) DEFAULT 'INFO';

04

Insert and query rows

Use explicit columns so the statement remains understandable after schema changes.

INSERT INTO lab_notes (note_id, topic, note_text, created_at, severity) VALUES
(1, 'joins', 'Match related keys', DATE '2026-09-03', 'INFO'),
(2, 'transactions', 'Verify before commit', DATE '2026-09-03', 'HIGH');
SELECT * FROM lab_notes ORDER BY note_id;

DROP and TRUNCATE are data operations

In a real database, DDL transaction behavior varies by platform and command. Confirm environment, dependencies, backups, permissions, and recovery procedures before destructive schema work.

Knowledge check

What does NOT NULL express?

Lesson 11 - Transactions

Treat related changes as one recoverable unit of work

Transactions support atomicity: a set of changes can be committed together or rolled back together. Savepoints provide intermediate recovery points inside a larger unit of work.

Begin a transactionVerify pending changesUse savepointsCommit or roll back

1

Transaction states

A decision remains pending until COMMIT or ROLLBACK

transaction patternBEGIN TRANSACTION;
UPDATE customers SET tier = 'SILVER' WHERE customer_id = 8;
SAVEPOINT after_customer;
-- additional work
ROLLBACK TO after_customer;
COMMIT;

2

Savepoint exercise

Restore only the work after a named checkpoint

01

Start and create a savepoint

The savepoint stores an intermediate simulation snapshot.

BEGIN;
UPDATE customers SET tier = 'SILVER' WHERE customer_id = 8;
SAVEPOINT tier_changed;

02

Make another change

This second update occurs after the savepoint.

UPDATE customers SET region = 'CENTRAL' WHERE customer_id = 8;
SELECT customer_id, region, tier FROM customers WHERE customer_id = 8;

03

Roll back to the savepoint

The region change is undone while the tier change remains pending.

ROLLBACK TO tier_changed;
SELECT customer_id, region, tier FROM customers WHERE customer_id = 8;

04

Roll back the entire exercise

A final ROLLBACK restores the pre-transaction database.

ROLLBACK;

Knowledge check

What does COMMIT do?

Lesson 12 - Views and indexes

Encapsulate query logic and support common access paths

A view exposes a named query as a relational source. An index records a search structure associated with one or more columns. Views improve reuse and access boundaries; indexes may improve reads but add storage and write cost.

Create a reporting viewQuery a viewCreate an indexRead a simulated plan

1

Build reusable reporting logic

Name the query instead of copying it into every report

01

Create a customer order summary

LEFT JOIN retains customers with zero orders.

CREATE VIEW customer_order_summary AS
SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

02

Query the view

The simulator evaluates the saved SELECT whenever the view is queried.

SELECT * FROM customer_order_summary ORDER BY order_count DESC, customer_name;

03

Create a focused index

Choose columns based on real predicates and joins, not a desire to index everything.

CREATE INDEX ix_orders_status_date ON orders(status, order_date);

04

Read an educational plan

Real execution plans depend on optimizer statistics, data distribution, parameters, platform, and configuration.

EXPLAIN PLAN FOR SELECT order_id, order_date FROM orders WHERE status = 'SHIPPED' ORDER BY order_date;

An index is not free

Indexes consume storage and must be maintained during INSERT, UPDATE, and DELETE. Measure workload behavior before and after indexing in a real system.

Knowledge check

Which statement best describes a view?

Lesson 13 - Security

Apply least privilege and resist unsafe query construction

Database security combines authentication, authorization, encryption, auditing, secure configuration, patching, backup protection, and safe application query construction. SQL permissions should be narrowly scoped to business need.

Grant minimal privilegesSeparate rolesUse parametersProtect sensitive output

1

Least-privilege exercise

Grant reporting access to a view instead of broad table modification

01

Create the reporting view if needed

The view exposes only the intended summary columns.

CREATE OR REPLACE VIEW customer_order_summary AS SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name;

02

Grant SELECT only

The simulated grant is recorded in the inspector.

GRANT SELECT ON customer_order_summary TO analyst_role;

03

Query the authorized surface

Applications and analysts should use the narrow object intended for them.

SELECT * FROM customer_order_summary ORDER BY order_count DESC;

04

Practice revocation

Remove access when a role no longer requires it.

REVOKE SELECT ON customer_order_summary FROM analyst_role;

2

Application query safety

Separate SQL structure from untrusted values

parameterization concept-- Unsafe string construction:
sql = "SELECT * FROM users WHERE email = '" + input + "'";

-- Safer parameterized intent:
SELECT * FROM users WHERE email = @email;   -- SQL Server
SELECT * FROM users WHERE email = :email;   -- Oracle

The simulator does not connect to user data

Its security lesson demonstrates principles and syntax. Real systems need platform-specific identity, roles, auditing, encryption, secret management, network restrictions, backups, and monitoring.

Knowledge check

What is the primary defense against SQL injection in application queries?

Lesson 14 - Capstone

Build a controlled sales reporting feature from schema to access

The capstone integrates discovery, joins, expressions, aggregation, DDL, DML, transactions, a view, an index, a plan, least-privilege access, validation, and exportable evidence.

Translate requirements into SQLVerify each state changePublish a reusable viewDocument evidence

1

Capstone scenario

Regional Revenue Reporting

Regional Revenue Reporting Feature

Create a durable but reversible reporting feature for sales analysts.

Inventory and describe CUSTOMERS, ORDERS, and ORDER_ITEMS.
Write a validation query that joins the three tables and calculates each net line total.
Aggregate order count and net revenue by customer region; sort revenue descending.
Create REGIONAL_REVENUE as a view containing region, order count, and rounded revenue.
Create an index on ORDERS(STATUS, ORDER_DATE) and inspect an explanatory plan for shipped orders.
Grant SELECT on the view to ANALYST_ROLE.
Begin a transaction, insert a lab customer and order, verify how the view changes, then roll back.
Query the final view and export the result as CSV plus the transcript as SQL.

2

Suggested reporting query

Use this as evidence or design your own equivalent

capstone coreSELECT c.region,
       COUNT(DISTINCT o.order_id) AS order_count,
       ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount)), 2) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.region
ORDER BY revenue DESC;

3

Primary references for real systems

Move from simulation to vendor documentation

Knowledge check

Which capstone sequence is most defensible?