The practice schema and dialect basics

Oracle · schema/schema_oracle.sql

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1. Client: SQL*Plus or SQLcl.
-- MySmartCoPilot's practice schema: five tables whose keys, checks and references all carry names, loaded with
-- made-up data (no real people, phone numbers or account numbers). Amounts are rupees in NUMBER(14,2).
SET FEEDBACK OFF

-- Children first: drop the five tables if an earlier run left them, so the script can run again at any time.
DROP TABLE IF EXISTS orders PURGE;
DROP TABLE IF EXISTS ledger PURGE;
DROP TABLE IF EXISTS stock PURGE;
DROP TABLE IF EXISTS accounts PURGE;
DROP TABLE IF EXISTS customers PURGE;

CREATE TABLE customers (
  customer_id NUMBER(10) GENERATED ALWAYS AS IDENTITY CONSTRAINT customers_pk PRIMARY KEY,
  full_name   VARCHAR2(80)  NOT NULL,
  city        VARCHAR2(40)  NOT NULL,
  email       VARCHAR2(120) CONSTRAINT customers_email_uq UNIQUE
);

CREATE TABLE accounts (
  account_id  NUMBER(10) GENERATED ALWAYS AS IDENTITY CONSTRAINT accounts_pk PRIMARY KEY,
  customer_id NUMBER(10)    NOT NULL CONSTRAINT accounts_customer_fk REFERENCES customers (customer_id),
  kind        VARCHAR2(10)  NOT NULL CONSTRAINT accounts_kind_ck CHECK (kind IN ('savings', 'current')),
  balance     NUMBER(14, 2) DEFAULT 0 NOT NULL CONSTRAINT accounts_balance_ck CHECK (balance >= 0)
);

CREATE TABLE ledger (
  entry_id   NUMBER(10) GENERATED ALWAYS AS IDENTITY CONSTRAINT ledger_pk PRIMARY KEY,
  account_id NUMBER(10)    NOT NULL CONSTRAINT ledger_account_fk REFERENCES accounts (account_id),
  amount     NUMBER(14, 2) NOT NULL CONSTRAINT ledger_amt_nonzero CHECK (amount <> 0),
  posted_at  DATE          NOT NULL,
  memo       VARCHAR2(60)
);

CREATE TABLE stock (
  sku        VARCHAR2(12)  CONSTRAINT stock_pk PRIMARY KEY,
  item_name  VARCHAR2(60)  NOT NULL,
  unit_price NUMBER(10, 2) NOT NULL CONSTRAINT stock_price_ck CHECK (unit_price > 0),
  on_hand    NUMBER(6)     NOT NULL CONSTRAINT stock_on_hand_ck CHECK (on_hand >= 0)
);

CREATE TABLE orders (
  order_id    NUMBER(10) GENERATED ALWAYS AS IDENTITY CONSTRAINT orders_pk PRIMARY KEY,
  customer_id NUMBER(10)   NOT NULL CONSTRAINT orders_customer_fk REFERENCES customers (customer_id),
  sku         VARCHAR2(12) NOT NULL CONSTRAINT orders_sku_fk REFERENCES stock (sku),
  qty         NUMBER(4)    NOT NULL CONSTRAINT orders_qty_ck CHECK (qty > 0),
  ordered_on  DATE         NOT NULL,
  status      VARCHAR2(10) DEFAULT 'new' NOT NULL
              CONSTRAINT orders_status_ck CHECK (status IN ('new', 'paid', 'shipped', 'cancelled'))
);

INSERT INTO customers (full_name, city, email) VALUES ('Asha Rao', 'Pune', 'asha.rao@example.com');
INSERT INTO customers (full_name, city, email) VALUES ('Vikram Nair', 'Kochi', 'vikram.nair@example.com');
INSERT INTO customers (full_name, city, email) VALUES ('Farida Shaikh', 'Lucknow', NULL);
INSERT INTO customers (full_name, city, email) VALUES ('Meera Iyer', 'Chennai', 'meera.iyer@example.com');

INSERT INTO accounts (customer_id, kind, balance) VALUES (1, 'savings', 2500.00);
INSERT INTO accounts (customer_id, kind, balance) VALUES (1, 'current', 18000.00);
INSERT INTO accounts (customer_id, kind, balance) VALUES (2, 'savings', 760.25);
INSERT INTO accounts (customer_id, kind, balance) VALUES (4, 'savings', 41250.50);

INSERT INTO ledger (account_id, amount, posted_at, memo) VALUES (1, 2500.00, DATE '2026-03-02', 'opening deposit');
INSERT INTO ledger (account_id, amount, posted_at, memo) VALUES (2, 18000.00, DATE '2026-03-02', 'opening deposit');
INSERT INTO ledger (account_id, amount, posted_at, memo) VALUES (3, 1000.00, DATE '2026-03-05', 'opening deposit');
INSERT INTO ledger (account_id, amount, posted_at, memo) VALUES (3, -239.75, DATE '2026-03-09', 'card payment');
INSERT INTO ledger (account_id, amount, posted_at, memo) VALUES (4, 41250.50, DATE '2026-03-11', 'opening deposit');

INSERT INTO stock (sku, item_name, unit_price, on_hand) VALUES ('TEA-250', 'Assam tea 250 g', 180.00, 40);
INSERT INTO stock (sku, item_name, unit_price, on_hand) VALUES ('RICE-5K', 'Sona masoori rice 5 kg', 420.00, 25);
INSERT INTO stock (sku, item_name, unit_price, on_hand) VALUES ('GHEE-500', 'Ghee 500 g', 420.00, 0);

INSERT INTO orders (customer_id, sku, qty, ordered_on, status) VALUES (1, 'TEA-250', 2, DATE '2026-03-12', 'paid');
INSERT INTO orders (customer_id, sku, qty, ordered_on, status) VALUES (2, 'RICE-5K', 1, DATE '2026-03-12', 'new');
INSERT INTO orders (customer_id, sku, qty, ordered_on, status) VALUES (4, 'TEA-250', 5, DATE '2026-03-13', 'shipped');
COMMIT;

SET FEEDBACK ON
SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL SELECT 'accounts', COUNT(*) FROM accounts
UNION ALL SELECT 'ledger', COUNT(*) FROM ledger
UNION ALL SELECT 'stock', COUNT(*) FROM stock
UNION ALL SELECT 'orders', COUNT(*) FROM orders;

SQL Server · schema/schema_sqlserver.sql

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab at compatibility level 170, default collation
-- SQL_Latin1_General_CP1_CI_AS. Client: sqlcmd. MySmartCoPilot's practice schema: five tables whose keys, checks and
-- references all carry names, loaded with made-up data. Amounts are rupees in decimal(14,2).
SET NOCOUNT ON;

-- Children first: drop the five tables if an earlier run left them, so the script can run again at any time.
DROP TABLE IF EXISTS dbo.orders, dbo.ledger, dbo.stock, dbo.accounts, dbo.customers;

CREATE TABLE dbo.customers (
  customer_id int IDENTITY(1,1) NOT NULL CONSTRAINT customers_pk PRIMARY KEY,
  full_name   nvarchar(80)  NOT NULL,
  city        nvarchar(40)  NOT NULL,
  email       nvarchar(120) NULL CONSTRAINT customers_email_uq UNIQUE
);

CREATE TABLE dbo.accounts (
  account_id  int IDENTITY(1,1) NOT NULL CONSTRAINT accounts_pk PRIMARY KEY,
  customer_id int           NOT NULL CONSTRAINT accounts_customer_fk REFERENCES dbo.customers (customer_id),
  kind        nvarchar(10)  NOT NULL CONSTRAINT accounts_kind_ck CHECK (kind IN (N'savings', N'current')),
  balance     decimal(14,2) NOT NULL CONSTRAINT accounts_balance_df DEFAULT 0
                                     CONSTRAINT accounts_balance_ck CHECK (balance >= 0)
);

CREATE TABLE dbo.ledger (
  entry_id   int IDENTITY(1,1) NOT NULL CONSTRAINT ledger_pk PRIMARY KEY,
  account_id int           NOT NULL CONSTRAINT ledger_account_fk REFERENCES dbo.accounts (account_id),
  amount     decimal(14,2) NOT NULL CONSTRAINT ledger_amt_nonzero CHECK (amount <> 0),
  posted_at  datetime2(0)  NOT NULL,
  memo       nvarchar(60)  NULL
);

CREATE TABLE dbo.stock (
  sku        varchar(12)   NOT NULL CONSTRAINT stock_pk PRIMARY KEY,
  item_name  nvarchar(60)  NOT NULL,
  unit_price decimal(10,2) NOT NULL CONSTRAINT stock_price_ck CHECK (unit_price > 0),
  on_hand    int           NOT NULL CONSTRAINT stock_on_hand_ck CHECK (on_hand >= 0)
);

CREATE TABLE dbo.orders (
  order_id    int IDENTITY(1,1) NOT NULL CONSTRAINT orders_pk PRIMARY KEY,
  customer_id int          NOT NULL CONSTRAINT orders_customer_fk REFERENCES dbo.customers (customer_id),
  sku         varchar(12)  NOT NULL CONSTRAINT orders_sku_fk REFERENCES dbo.stock (sku),
  qty         int          NOT NULL CONSTRAINT orders_qty_ck CHECK (qty > 0),
  ordered_on  date         NOT NULL,
  status      nvarchar(10) NOT NULL CONSTRAINT orders_status_df DEFAULT N'new'
                                    CONSTRAINT orders_status_ck CHECK (status IN (N'new', N'paid', N'shipped', N'cancelled'))
);

INSERT INTO dbo.customers (full_name, city, email) VALUES
  (N'Asha Rao', N'Pune', N'asha.rao@example.com'),
  (N'Vikram Nair', N'Kochi', N'vikram.nair@example.com'),
  (N'Farida Shaikh', N'Lucknow', NULL),
  (N'Meera Iyer', N'Chennai', N'meera.iyer@example.com');

INSERT INTO dbo.accounts (customer_id, kind, balance) VALUES
  (1, N'savings', 2500.00), (1, N'current', 18000.00), (2, N'savings', 760.25), (4, N'savings', 41250.50);

INSERT INTO dbo.ledger (account_id, amount, posted_at, memo) VALUES
  (1, 2500.00, '2026-03-02', N'opening deposit'),
  (2, 18000.00, '2026-03-02', N'opening deposit'),
  (3, 1000.00, '2026-03-05', N'opening deposit'),
  (3, -239.75, '2026-03-09', N'card payment'),
  (4, 41250.50, '2026-03-11', N'opening deposit');

INSERT INTO dbo.stock (sku, item_name, unit_price, on_hand) VALUES
  ('TEA-250', N'Assam tea 250 g', 180.00, 40),
  ('RICE-5K', N'Sona masoori rice 5 kg', 420.00, 25),
  ('GHEE-500', N'Ghee 500 g', 420.00, 0);

INSERT INTO dbo.orders (customer_id, sku, qty, ordered_on, status) VALUES
  (1, 'TEA-250', 2, '2026-03-12', N'paid'),
  (2, 'RICE-5K', 1, '2026-03-12', N'new'),
  (4, 'TEA-250', 5, '2026-03-13', N'shipped');

SELECT 'customers' AS table_name, COUNT(*) AS row_count FROM dbo.customers
UNION ALL SELECT 'accounts', COUNT(*) FROM dbo.accounts
UNION ALL SELECT 'ledger', COUNT(*) FROM dbo.ledger
UNION ALL SELECT 'stock', COUNT(*) FROM dbo.stock
UNION ALL SELECT 'orders', COUNT(*) FROM dbo.orders;
GO

Oracle · schema_oracle_session.py

"""schema_oracle.sql in the lab: SQL*Plus runs it as LAB, with nothing in its schema yet."""

from oracle_session import Lab

with Lab(schema=False) as lab:
    lab.sqlplus("schema/schema_oracle.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @schema/schema_oracle.sql
TABLE_NAM  ROW_COUNT
--------- ----------
customers          4
accounts           4
ledger             5
stock              3
orders             3

5 rows selected.

SQL Server · schema_sqlserver_session.py

"""schema_sqlserver.sql in the lab: sqlcmd runs it as sa in the empty database lab."""

from mssql_session import Lab

with Lab(schema=False) as lab:
    lab.sqlcmd("schema/schema_sqlserver.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i schema/schema_sqlserver.sql
table_name row_count
---------- ---------
customers          4
accounts           4
ledger             5
stock              3
orders             3

Oracle · traps/traps_oracle.sql

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1, after schema_oracle.sql.
-- Client: SQL*Plus or SQLcl. Six questions whose answers differ between Oracle and SQL Server.
SET SERVEROUTPUT ON
SET NULL '(null)'

-- 1. An empty string
SELECT LENGTH('') AS len_empty,
       NVL2('', 'not null', 'null') AS empty_is,
       COALESCE(NULL, '', 'x') AS first_non_null
  FROM dual;

-- 2. Upper and lower case
SELECT CASE WHEN 'abc' = 'ABC' THEN 'equal' ELSE 'different' END AS case_test FROM dual;

-- 3. Trailing spaces: two literals, then VARCHAR2 and CHAR variables
SELECT CASE WHEN 'a' = 'a   ' THEN 'equal' ELSE 'different' END AS literals FROM dual;
DECLARE
  v1 VARCHAR2(10) := 'a';
  v2 VARCHAR2(10) := 'a   ';
  c1 CHAR(4)      := 'a';
  c2 CHAR(4)      := 'a   ';
BEGIN
  DBMS_OUTPUT.PUT_LINE('VARCHAR2: ' || CASE WHEN v1 = v2 THEN 'equal' ELSE 'different' END);
  DBMS_OUTPUT.PUT_LINE('CHAR:     ' || CASE WHEN c1 = c2 THEN 'equal' ELSE 'different' END);
END;
/

-- 4. Dividing two whole numbers
SELECT 7 / 2 AS seven_by_two, TRUNC(7 / 2) AS truncated FROM dual;

-- 5. A date plus one twenty-fourth
SELECT TO_CHAR(DATE '2026-03-15' + 1 / 24, 'YYYY-MM-DD HH24:MI:SS') AS plus_one_hour FROM dual;

-- 6. The most expensive item, with ties
SELECT sku, unit_price FROM stock ORDER BY unit_price DESC FETCH FIRST 1 ROW WITH TIES;

SQL Server · traps/traps_sqlserver.sql

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab (compatibility level 170, collation
-- SQL_Latin1_General_CP1_CI_AS), after schema_sqlserver.sql. Client: sqlcmd. The same six questions as the Oracle
-- script, in T-SQL.
SET NOCOUNT ON;

-- 1. An empty string
SELECT LEN(N'') AS len_empty,
       CASE WHEN N'' IS NULL THEN N'null' ELSE N'not null' END AS empty_is,
       DATALENGTH(COALESCE(NULL, N'', N'x')) AS bytes_of_first_non_null;

-- 2. Upper and lower case, under the database's default collation
SELECT CASE WHEN 'abc' = 'ABC' THEN 'equal' ELSE 'different' END AS case_test;

-- 3. Trailing spaces: two literals, then two varchar variables
SELECT CASE WHEN 'a' = 'a   ' THEN 'equal' ELSE 'different' END AS literals;
DECLARE @v1 varchar(10) = 'a', @v2 varchar(10) = 'a   ';
SELECT CASE WHEN @v1 = @v2 THEN 'equal' ELSE 'different' END AS varchar_test,
       DATALENGTH(@v2) AS bytes_in_v2;

-- 4. Dividing two whole numbers
SELECT 7 / 2 AS seven_by_two, 7 / 2.0 AS with_a_decimal;

-- 5. A date plus one hour
SELECT DATEADD(hour, 1, CAST('2026-03-15' AS datetime2(0))) AS plus_one_hour;

-- 6. The most expensive item, with ties
SELECT TOP (1) WITH TIES sku, unit_price FROM dbo.stock ORDER BY unit_price DESC;
GO

Oracle · traps_oracle_session.py

"""traps_oracle.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("traps/traps_oracle.sql")

$ sqlplus -s lab@//localhost/FREEPDB1 @traps/traps_oracle.sql
 LEN_EMPTY EMPT F
---------- ---- -
(null)     null x


CASE_TEST
---------
different


LITERALS
---------
different

VARCHAR2: different
CHAR:     equal

PL/SQL procedure successfully completed.


SEVEN_BY_TWO  TRUNCATED
------------ ----------
         3.5          3


PLUS_ONE_HOUR
-------------------
2026-03-15 01:00:00


SKU          UNIT_PRICE
------------ ----------
RICE-5K             420
GHEE-500            420

SQL Server · traps_sqlserver_session.py

"""traps_sqlserver.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("traps/traps_sqlserver.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i traps/traps_sqlserver.sql
len_empty empty_is bytes_of_first_non_null
--------- -------- -----------------------
        0 not null                       0
case_test
---------
equal
literals
--------
equal
varchar_test bytes_in_v2
------------ -----------
equal                  4
seven_by_two with_a_decimal
------------ --------------
           3       3.500000
plus_one_hour
-------------------
2026-03-15 01:00:00
sku      unit_price
-------- ----------
GHEE-500     420.00
RICE-5K      420.00

Oracle · errors/errors_oracle.sql

-- Engine: Oracle AI Database 26ai Free, Release 23.26.3.0.0; user LAB in FREEPDB1, after schema_oracle.sql.
-- Client: SQL*Plus or SQLcl, which goes on after an error. Two inserts that break named constraints: the error
-- messages name LEDGER_AMT_NONZERO and CUSTOMERS_EMAIL_UQ, the same on every rebuild of the schema.
INSERT INTO ledger (account_id, amount, posted_at, memo) VALUES (1, 0, DATE '2026-03-15', 'zero entry');

INSERT INTO customers (full_name, city, email) VALUES ('Asha R.', 'Pune', 'asha.rao@example.com');

ROLLBACK;

SQL Server · errors/errors_sqlserver.sql

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab, after schema_sqlserver.sql. Client: sqlcmd without -b,
-- which goes on after an error. Two inserts that break named constraints: the messages name ledger_amt_nonzero and
-- customers_email_uq, the same on every rebuild of the schema.
SET NOCOUNT ON;

INSERT INTO dbo.ledger (account_id, amount, posted_at, memo) VALUES (1, 0, '2026-03-15', N'zero entry');

INSERT INTO dbo.customers (full_name, city, email) VALUES (N'Asha R.', N'Pune', N'asha.rao@example.com');
GO

Oracle · errors_oracle_session.py

"""errors_oracle.sql in the lab: SQL*Plus runs it as LAB on a freshly loaded practice schema."""

from oracle_session import Lab

with Lab() as lab:
    lab.sqlplus("errors/errors_oracle.sql", errors=2)

$ sqlplus -s lab@//localhost/FREEPDB1 @errors/errors_oracle.sql
INSERT INTO ledger (account_id, amount, posted_at, memo) VALUES (1, 0, DATE '2026-03-15', 'zero entry')
*
ERROR at line 1:
ORA-02290: check constraint (LAB.LEDGER_AMT_NONZERO) violated
Help: https://docs.oracle.com/error-help/db/ora-02290/


INSERT INTO customers (full_name, city, email) VALUES ('Asha R.', 'Pune', 'asha.rao@example.com')
*
ERROR at line 1:
ORA-00001: unique constraint (LAB.CUSTOMERS_EMAIL_UQ) violated on table
LAB.CUSTOMERS columns (EMAIL)
ORA-03301: (ORA-00001 details) row with column values
(EMAIL:'asha.rao@example.com') already exists
Help: https://docs.oracle.com/error-help/db/ora-00001/



Rollback complete.

SQL Server · errors_sqlserver_session.py

"""errors_sqlserver.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("errors/errors_sqlserver.sql", errors=2)

$ sqlcmd -S localhost -U sa -C -d lab -i errors/errors_sqlserver.sql
Msg 547, Level 16, State 1, Server lab-mssql, Line 6
The INSERT statement conflicted with the CHECK constraint "ledger_amt_nonzero". The conflict occurred in database "lab", table "dbo.ledger", column 'amount'.
The statement has been terminated.
Msg 2627, Level 14, State 1, Server lab-mssql, Line 8
Violation of UNIQUE KEY constraint 'customers_email_uq'. Cannot insert duplicate key in object 'dbo.customers'. The duplicate key value is (asha.rao@example.com).
The statement has been terminated.