{"appId":"sql-app","version":1551,"selectedAngularVersion":20,"item":{"id":"lesson-introduction-to-databases","conceptKey":"introduction-to-databases","subjectId":"subject-introduction-to-databases","title":"Introduction to Databases","summary":"A database stores related data in an organized form so applications can create, read, update, and protect it reliably.","baseContent":"

Introduction to Databases

A database stores related data in an organized form so applications can create, read, update, and protect it reliably.

Example

CREATE DATABASE shop;

Key point

Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.

Real-life example

An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist.

Advanced example

BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the Introduction to Databases operation, verify affected rows, then commit.\nCOMMIT;

Expected result

The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.

Production check

PicoStore practice database

Every lesson in this SQL course uses the same fictional e-commerce database: picostore. Create it once, then reuse its tables throughout the course.

CREATE DATABASE picostore;\n\nCREATE TABLE customers (\n  customer_id INTEGER PRIMARY KEY,\n  name VARCHAR(100) NOT NULL,\n  email VARCHAR(255) UNIQUE NOT NULL,\n  city VARCHAR(80),\n  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE products (\n  product_id INTEGER PRIMARY KEY,\n  name VARCHAR(120) NOT NULL,\n  category VARCHAR(60),\n  price DECIMAL(10, 2) CHECK (price >= 0),\n  stock INTEGER DEFAULT 0\n);\n\nCREATE TABLE orders (\n  order_id INTEGER PRIMARY KEY,\n  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),\n  status VARCHAR(20) DEFAULT 'pending',\n  total DECIMAL(12, 2) NOT NULL,\n  ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE order_items (\n  order_id INTEGER REFERENCES orders(order_id),\n  product_id INTEGER REFERENCES products(product_id),\n  quantity INTEGER CHECK (quantity > 0),\n  unit_price DECIMAL(10, 2) NOT NULL,\n  PRIMARY KEY (order_id, product_id)\n);\n\nCREATE TABLE employees (\n  employee_id INTEGER PRIMARY KEY,\n  manager_id INTEGER REFERENCES employees(employee_id),\n  name VARCHAR(100) NOT NULL,\n  department VARCHAR(60),\n  salary DECIMAL(12, 2)\n);\n\nCREATE TABLE accounts (\n  account_id INTEGER PRIMARY KEY,\n  customer_id INTEGER REFERENCES customers(customer_id),\n  balance DECIMAL(14, 2) NOT NULL\n);

Starter data

INSERT INTO customers (customer_id, name, email, city) VALUES\n  (1, 'Asha', 'asha@example.com', 'Pune'),\n  (2, 'Ravi', 'ravi@example.com', 'Mumbai'),\n  (3, 'Meera', 'meera@example.com', NULL);\n\nINSERT INTO products (product_id, name, category, price, stock) VALUES\n  (101, 'Keyboard', 'Accessories', 2499.00, 12),\n  (102, 'Monitor', 'Displays', 14999.00, 5),\n  (103, 'USB Cable', 'Accessories', 499.00, 0);\n\nINSERT INTO orders (order_id, customer_id, status, total, ordered_at) VALUES\n  (1001, 1, 'paid', 15498.00, '2026-01-10 10:00:00'),\n  (1002, 1, 'pending', 499.00, '2026-01-12 12:30:00'),\n  (1003, 2, 'shipped', 2499.00, '2026-02-02 09:15:00');\n\nINSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES\n  (1001, 102, 1, 14999.00), (1001, 103, 1, 499.00),\n  (1002, 103, 1, 499.00), (1003, 101, 1, 2499.00);

Another practical example

SELECT o.order_id, c.name AS customer, o.status, o.total\nFROM orders o\nJOIN customers c ON c.customer_id = o.customer_id\nWHERE o.total >= 1000\nORDER BY o.ordered_at DESC;

Check the result

Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.

Easy example

Start with a small customer table and retrieve active customers in a predictable order.

SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;

How to verify the easy example

Advanced example

Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.

WITH customer_revenue AS (\n  SELECT customer_id, SUM(total_amount) AS revenue\n  FROM orders\n  WHERE order_status = 'completed'\n  GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n       DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;

Advanced review

Additional practical guidance

Introduction to Databases: MySQL and PostgreSQL

Model the rule with keys and constraints instead of relying only on application code. MySQL and PostgreSQL support the core relational design, but generated-column, check-constraint, and alteration details can differ by server version.

Required verification

","detailId":"lesson-introduction-to-databases-698e3751","versions":[],"isActive":true,"detailIsActive":true,"lessonVersions":[],"selectedVersion":20,"content":"","updatedAt":"2026-08-01T09:43:04.474Z","details":[{"id":"lesson-introduction-to-databases","conceptKey":"introduction-to-databases","subjectId":"subject-introduction-to-databases","title":"Introduction to Databases","summary":"A database stores related data in an organized form so applications can create, read, update, and protect it reliably.","baseContent":"

Introduction to Databases

A database stores related data in an organized form so applications can create, read, update, and protect it reliably.

Example

CREATE DATABASE shop;

Key point

Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.

Real-life example

An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist.

Advanced example

BEGIN;\n-- Preview the exact target set first.\nSELECT id FROM orders WHERE status = 'pending';\n-- Apply the Introduction to Databases operation, verify affected rows, then commit.\nCOMMIT;

Expected result

The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query.

Production check

PicoStore practice database

Every lesson in this SQL course uses the same fictional e-commerce database: picostore. Create it once, then reuse its tables throughout the course.

CREATE DATABASE picostore;\n\nCREATE TABLE customers (\n  customer_id INTEGER PRIMARY KEY,\n  name VARCHAR(100) NOT NULL,\n  email VARCHAR(255) UNIQUE NOT NULL,\n  city VARCHAR(80),\n  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE products (\n  product_id INTEGER PRIMARY KEY,\n  name VARCHAR(120) NOT NULL,\n  category VARCHAR(60),\n  price DECIMAL(10, 2) CHECK (price >= 0),\n  stock INTEGER DEFAULT 0\n);\n\nCREATE TABLE orders (\n  order_id INTEGER PRIMARY KEY,\n  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),\n  status VARCHAR(20) DEFAULT 'pending',\n  total DECIMAL(12, 2) NOT NULL,\n  ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\nCREATE TABLE order_items (\n  order_id INTEGER REFERENCES orders(order_id),\n  product_id INTEGER REFERENCES products(product_id),\n  quantity INTEGER CHECK (quantity > 0),\n  unit_price DECIMAL(10, 2) NOT NULL,\n  PRIMARY KEY (order_id, product_id)\n);\n\nCREATE TABLE employees (\n  employee_id INTEGER PRIMARY KEY,\n  manager_id INTEGER REFERENCES employees(employee_id),\n  name VARCHAR(100) NOT NULL,\n  department VARCHAR(60),\n  salary DECIMAL(12, 2)\n);\n\nCREATE TABLE accounts (\n  account_id INTEGER PRIMARY KEY,\n  customer_id INTEGER REFERENCES customers(customer_id),\n  balance DECIMAL(14, 2) NOT NULL\n);

Starter data

INSERT INTO customers (customer_id, name, email, city) VALUES\n  (1, 'Asha', 'asha@example.com', 'Pune'),\n  (2, 'Ravi', 'ravi@example.com', 'Mumbai'),\n  (3, 'Meera', 'meera@example.com', NULL);\n\nINSERT INTO products (product_id, name, category, price, stock) VALUES\n  (101, 'Keyboard', 'Accessories', 2499.00, 12),\n  (102, 'Monitor', 'Displays', 14999.00, 5),\n  (103, 'USB Cable', 'Accessories', 499.00, 0);\n\nINSERT INTO orders (order_id, customer_id, status, total, ordered_at) VALUES\n  (1001, 1, 'paid', 15498.00, '2026-01-10 10:00:00'),\n  (1002, 1, 'pending', 499.00, '2026-01-12 12:30:00'),\n  (1003, 2, 'shipped', 2499.00, '2026-02-02 09:15:00');\n\nINSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES\n  (1001, 102, 1, 14999.00), (1001, 103, 1, 499.00),\n  (1002, 103, 1, 499.00), (1003, 101, 1, 2499.00);

Another practical example

SELECT o.order_id, c.name AS customer, o.status, o.total\nFROM orders o\nJOIN customers c ON c.customer_id = o.customer_id\nWHERE o.total >= 1000\nORDER BY o.ordered_at DESC;

Check the result

Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.

Easy example

Start with a small customer table and retrieve active customers in a predictable order.

SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;

How to verify the easy example

Advanced example

Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.

WITH customer_revenue AS (\n  SELECT customer_id, SUM(total_amount) AS revenue\n  FROM orders\n  WHERE order_status = 'completed'\n  GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n       DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;

Advanced review

Additional practical guidance

Introduction to Databases: MySQL and PostgreSQL

Model the rule with keys and constraints instead of relying only on application code. MySQL and PostgreSQL support the core relational design, but generated-column, check-constraint, and alteration details can differ by server version.

Required verification

","detailId":"lesson-introduction-to-databases-698e3751","versions":[],"isActive":true,"detailIsActive":true,"lessonVersions":[],"selectedVersion":20,"content":"","updatedAt":"2026-08-01T09:43:04.474Z"}]}}