CREATE SCHEMA IF NOT EXISTS practice;
CREATE TABLE practice.users (
  user_id    SERIAL PRIMARY KEY,
  username   VARCHAR(50) NOT NULL,
  email      VARCHAR(100) NOT NULL
);

-- Valid write: all required columns provided:
INSERT INTO practice.users (username, email) VALUES ('aarav', '[email protected]'); -- OK!
SELECT * FROM practice.users LIMIT 10;

-- FAILS: username is omitted and has no default:
INSERT INTO practice.users (email) VALUES ('[email protected]');
-- 💥 ERROR: null value in column "username" violates not-null constraint
-- DETAIL: Failing row contains (2, null, [email protected]).

-- In-Place Schema Evolution on Existing Tables:
-- Step 1: Pre-clean dirty rows (otherwise SET NOT NULL aborts!)
UPDATE practice.users SET username = 'anonymous' WHERE username IS NULL;
-- Step 2: Lock the column against future NULL writes:
ALTER TABLE practice.users ALTER COLUMN username SET NOT NULL;

CREATE SCHEMA IF NOT EXISTS practice;
CREATE TABLE practice.web_orders (
  order_id      SERIAL PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  order_status  VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Provide ONLY the required column; defaults fill the rest automatically:
INSERT INTO practice.web_orders (customer_name) VALUES ('Priya');
-- order_status='pending', created_at=now() auto-populated!

-- Explicit override is still permitted:
INSERT INTO practice.web_orders (customer_name, order_status)
VALUES ('Aarav', 'shipped'); -- order_status becomes 'shipped'

-- Managing defaults on existing tables:
ALTER TABLE practice.web_orders ALTER COLUMN order_status SET DEFAULT 'placed';
ALTER TABLE practice.web_orders ALTER COLUMN order_status DROP DEFAULT;

-- See what the defaults filled in:
SELECT order_id, customer_name, order_status, created_at FROM practice.web_orders LIMIT 10;

CREATE SCHEMA IF NOT EXISTS practice;
CREATE TABLE practice.order_items (
  item_id    SERIAL PRIMARY KEY,
  item_name  VARCHAR(100) NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price > 0),
  quantity   INT NOT NULL CHECK (quantity >= 1),
  status     VARCHAR(20) NOT NULL
    CHECK (status IN ('pending', 'packed', 'dispatched', 'delivered'))
);

INSERT INTO practice.order_items (item_name, unit_price, quantity, status)
VALUES ('Wireless Mouse', 799.00, 2, 'packed'); -- OK

-- Also try (FAILS): status 'in-transit' is not in the approved whitelist
-- INSERT INTO practice.order_items (item_name, unit_price, quantity, status)
-- VALUES ('Keyboard', 1299.00, 1, 'in-transit');
-- ERROR: new row violates check constraint "order_items_status_check"

-- FAILS: price cannot be zero or negative
INSERT INTO practice.order_items (item_name, unit_price, quantity, status)
VALUES ('Bad Item', -50.00, 1, 'packed');
-- ERROR: new row violates check constraint "order_items_unit_price_check"

CREATE SCHEMA IF NOT EXISTS practice;
-- 1. Single-column UNIQUE (email must be unique across all users):
CREATE TABLE practice.accounts (
  account_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      VARCHAR(150) UNIQUE NOT NULL
);

-- 2. Multi-column composite UNIQUE (slug unique within the same tenant):
CREATE TABLE practice.tenant_workspaces (
  workspace_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id    INT NOT NULL,
  slug         VARCHAR(50) NOT NULL,
  CONSTRAINT uq_tenant_slug UNIQUE (tenant_id, slug)
);

-- 3. PostgreSQL 15+ (allows at most ONE null row instead of unlimited NULL duplicates):
CREATE TABLE practice.tax_filings (
  filing_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tax_id     VARCHAR(20) UNIQUE NULLS NOT DISTINCT
);

-- Same slug in two different tenants is fine:
INSERT INTO practice.tenant_workspaces (tenant_id, slug) VALUES (1, 'sales'), (2, 'sales');
SELECT tenant_id, slug FROM practice.tenant_workspaces LIMIT 10;
-- Now try (FAILS with Error 23505): the same slug twice in tenant 1
-- INSERT INTO practice.tenant_workspaces (tenant_id, slug) VALUES (1, 'sales');

CREATE SCHEMA IF NOT EXISTS practice;
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE practice.doctor_schedules (
  booking_id  SERIAL PRIMARY KEY,
  doctor_id   INT NOT NULL,
  slot_window TSRANGE NOT NULL,
  -- For any two rows, if doctor_id is EQUAL (=), slot_window CANNOT OVERLAP (&&):
  CONSTRAINT no_double_booking
    EXCLUDE USING gist (doctor_id WITH =, slot_window WITH &&)
);

-- Doctor 101 booked 10:00 to 11:00
INSERT INTO practice.doctor_schedules (doctor_id, slot_window)
VALUES (101, tsrange('2026-10-10 10:00', '2026-10-10 11:00'));

-- FAILS: 10:30 to 11:30 collides with 10:00 to 11:00!
INSERT INTO practice.doctor_schedules (doctor_id, slot_window)
VALUES (101, tsrange('2026-10-10 10:30', '2026-10-10 11:30'));
-- ERROR: conflicting key value violates exclusion constraint "no_double_booking"
-- DETAIL: Key (doctor_id, slot_window)=(101, ["2026-10-10 10:30:00","2026-10-10 11:30:00"))
-- conflicts with existing key (101, ["2026-10-10 10:00:00","2026-10-10 11:00:00")).

CREATE SCHEMA IF NOT EXISTS practice;
CREATE TABLE practice.taxpayers (
  -- 1. SURROGATE PRIMARY KEY: the chosen candidate key (synthetic 8-byte anchor)
  taxpayer_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  -- 2. NATURAL ALTERNATE KEY: real-world business identifier (immutable)
  pan_number   VARCHAR(10) UNIQUE NOT NULL,

  -- 3. SECONDARY ALTERNATE KEY: candidate key enforced via UNIQUE NOT NULL
  email        VARCHAR(150) UNIQUE NOT NULL,

  -- 4. NON-KEY ATTRIBUTE: descriptive domain payload
  full_name    TEXT NOT NULL
);

INSERT INTO practice.taxpayers (pan_number, email, full_name)
VALUES ('ABCDE1234F', '[email protected]', 'Priya Sharma');
SELECT taxpayer_id, pan_number, email, full_name FROM practice.taxpayers LIMIT 10;

-- 💡 The Golden Rule: use surrogate keys for joins and primary keys;
-- enforce natural keys using alternate keys (UNIQUE NOT NULL)!

CREATE SCHEMA IF NOT EXISTS practice;
-- 1. Modern single-column surrogate primary key:
CREATE TABLE practice.shop_orders (
  order_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_date DATE NOT NULL DEFAULT CURRENT_DATE,
  total      NUMERIC(10,2) NOT NULL CHECK (total >= 0)
);

-- 2. Composite primary key on a junction / line-items table:
CREATE TABLE practice.shop_order_items (
  order_id   BIGINT REFERENCES practice.shop_orders(order_id),
  item_id    INT,
  quantity   INT NOT NULL CHECK (quantity > 0),
  CONSTRAINT pk_shop_order_items PRIMARY KEY (order_id, item_id)
);

INSERT INTO practice.shop_orders (total) VALUES (2499.00);                -- order_id 1 is generated
INSERT INTO practice.shop_order_items VALUES (1, 1, 2), (1, 2, 1);        -- same order, different items: OK
SELECT order_id, item_id, quantity FROM practice.shop_order_items LIMIT 10;
-- Now try (FAILS with Error 23505): the pair (1, 1) already exists
-- INSERT INTO practice.shop_order_items VALUES (1, 1, 5);

-- Step 1: Parent table must exist with a unique identifier
CREATE SCHEMA IF NOT EXISTS practice;
CREATE TABLE practice.customers (
  cust_id    INT PRIMARY KEY,
  full_name  VARCHAR(100) NOT NULL
);

-- Step 2: Child table enforces referential relationship
CREATE TABLE practice.orders (
  order_id   SERIAL PRIMARY KEY,
  cust_id    INT NOT NULL,
  amount     NUMERIC(10,2) NOT NULL CHECK (amount >= 0),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (cust_id) REFERENCES practice.customers(cust_id)
);

-- Valid write: customer 101 exists in parent table
INSERT INTO practice.customers (cust_id, full_name) VALUES (101, 'Aarav Sharma');
INSERT INTO practice.orders (cust_id, amount) VALUES (101, 2499.00); -- OK!

-- Blocked write: customer 999 does not exist (Orphan Shield fires)
INSERT INTO practice.orders (cust_id, amount) VALUES (999, 499.00);
-- 💥 ERROR: insert or update on table "orders" violates foreign key constraint "fk_orders_customer"
-- DETAIL: Key (cust_id)=(999) is not present in table "customers".

-- Parents
CREATE SCHEMA IF NOT EXISTS practice;
CREATE TABLE practice.blog_posts  (post_id INT PRIMARY KEY, title VARCHAR(100) NOT NULL);
CREATE TABLE practice.managers    (manager_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
CREATE TABLE practice.departments (dept_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL);
-- Children: one ON DELETE rule each
CREATE TABLE practice.comments (comment_id SERIAL PRIMARY KEY,
  post_id INT REFERENCES practice.blog_posts(post_id) ON DELETE CASCADE);   -- disposable: delete them too
CREATE TABLE practice.staff (staff_id SERIAL PRIMARY KEY,
  manager_id INT REFERENCES practice.managers(manager_id) ON DELETE SET NULL, -- reassignable: unlink (must allow NULL)
  dept_id INT NOT NULL REFERENCES practice.departments(dept_id) ON DELETE RESTRICT); -- critical: block it
INSERT INTO practice.blog_posts VALUES (1, 'Intro to SQL');
INSERT INTO practice.comments (post_id) VALUES (1), (1);
INSERT INTO practice.managers VALUES (7, 'Priya');
INSERT INTO practice.departments VALUES (10, 'Tech');
INSERT INTO practice.staff (manager_id, dept_id) VALUES (7, 10);
DELETE FROM practice.blog_posts WHERE post_id = 1;    -- CASCADE: both comments are deleted too
DELETE FROM practice.managers WHERE manager_id = 7;   -- SET NULL: staff row stays, manager_id becomes NULL
SELECT staff_id, manager_id, dept_id FROM practice.staff LIMIT 10;
-- Now try RESTRICT: remove the two dashes below and run again. It is blocked with Error 23503.
-- DELETE FROM practice.departments WHERE dept_id = 10;