Database & Table Basics

Lesson 0002 — SQL Commands, Types, Tables & Lifecycle — ~14 min read

SQL is how you talk to a database. Every instruction you write is an SQL command — a declarative sentence telling the database what you want, not how to get it.

What are SQL Commands?

SQL commands are instructions used to communicate with a database (which contains multiple tables). You tell the database what you want — it does the work (creates objects, inserts data, fetches rows, controls access, manages transactions).

Note: SQL keywords are typically case-insensitive (CREATE = create), but object names (database/table names) can be case-sensitive depending on settings/OS.

Database Setup Commands

A. Create Database

CREATE DATABASE tuf_sql_2;

CREATE DATABASE tuf_sql_2;

Database is created and appears in DB list.

B. Show Databases

SHOW DATABASES;

\l
-- or
SELECT datname FROM pg_database;

C. Use a Database

USE tuf_sql;

\c tuf_sql
-- or
SET search_path TO tuf_sql;

After USE, all your queries run inside that selected database.

D. Drop Database

DROP DATABASE tuf_sql_2;

DROP DATABASE tuf_sql_2;

Note: DROP DATABASE is irreversible in most cases. It removes the whole database.

E. Safe Create (IF NOT EXISTS)

CREATE DATABASE IF NOT EXISTS tuf_sql;

CREATE DATABASE tuf_sql; -- no IF NOT EXISTS in older PG; handle error in app

Prevents errors if the database already exists.

Common SQL Data Types

Data types define what kind of data a column can store.

Creating Tables and Basic Commands

Create Table

CREATE TABLE IF NOT EXISTS users (
  user_id INT,
  name VARCHAR(30),
  email VARCHAR(50),
  is_active BOOLEAN,
  account_balance DECIMAL(10,2),
  signup_datetime DATETIME,
  last_seen DATETIME
);

CREATE TABLE IF NOT EXISTS users (
  user_id INT,
  name VARCHAR(30),
  email VARCHAR(50),
  is_active BOOLEAN,
  account_balance DECIMAL(10,2),
  signup_datetime DATETIME,
  last_seen DATETIME
);

Show Tables

SHOW TABLES;

\dt
-- or
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

The database tuf_sql contains a single table named users, as shown under Tables_in_tuf_sql.

Drop Table

DROP TABLE users;
-- safe variant
DROP TABLE IF EXISTS users;

DROP TABLE users;
-- safe variant
DROP TABLE IF EXISTS users;

IF EXISTS prevents errors when the table is already missing.

INSERT and SELECT (Basics)

A. Insert a single row

INSERT INTO users (user_id, name, email)
VALUES (1, 'Raj', 'raj@gmail.com');

INSERT INTO users (user_id, name, email)
VALUES (1, 'Raj', 'raj@gmail.com');

B. Insert multiple rows

INSERT INTO users (user_id, name, email)
VALUES
  (2, 'Mark', 'mark@gmail.com'),
  (3, 'Albert', 'albert@gmail.com'),
  (4, 'Peter', 'peter@gmail.com');

INSERT INTO users (user_id, name, email)
VALUES
  (2, 'Mark', 'mark@gmail.com'),
  (3, 'Albert', 'albert@gmail.com'),
  (4, 'Peter', 'peter@gmail.com');

C. Select data

SELECT * FROM users;

SELECT * FROM users;

Note: Avoid SELECT * on huge tables in production. Prefer selecting only required columns.

D. Select specific columns + Alias

SELECT
  name,
  email AS mail_id
FROM users;

SELECT
  name,
  email AS mail_id
FROM users;

Primary Key, Foreign Key, Composite Primary Key

A. Primary Key

A Primary Key uniquely identifies each row. It must be unique and NOT NULL.

-- Way 1 (inline)
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(50)
);

-- Way 2 (at the end)
CREATE TABLE users (
  user_id INT,
  name VARCHAR(30),
  email VARCHAR(50), PRIMARY KEY (user_id)
);

-- Way 1 (inline)
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(50)
);

-- Way 2 (at the end)
CREATE TABLE users (
  user_id INT,
  name VARCHAR(30),
  email VARCHAR(50), PRIMARY KEY (user_id)
);

B. Foreign Key

A Foreign Key links one table to another table's primary key (creates a relationship).

CREATE TABLE orders (
  order_id INT PRIMARY KEY, user_id INT,
  amount DECIMAL(10,2),
  order_date DATE, FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE orders (
  order_id INT PRIMARY KEY, user_id INT,
  amount DECIMAL(10,2),
  order_date DATE, FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Note: If a table is referenced by a foreign key, you usually must drop the child table first (e.g. drop orders before users).

C. Composite Primary Key

Multiple columns together form the unique identifier.

CREATE TABLE enrollments (
  email VARCHAR(50), batch_id INT,
  enrolled_at DATETIME, PRIMARY KEY (email, batch_id)
);

CREATE TABLE enrollments (
  email VARCHAR(50), batch_id INT,
  enrolled_at DATETIME, PRIMARY KEY (email, batch_id)
);

Here, email alone can repeat across batches, but the pair (email, batch_id) must be unique.

Note: More columns in a composite key increases index size and may impact performance. Use only when needed.

Constraints

A. AUTO_INCREMENT

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  email VARCHAR(50)
);

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  -- or INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
  name VARCHAR(30),
  email VARCHAR(50)
);

INSERT INTO users (name, email)
VALUES ('Raj', 'raj@gmail.com');

INSERT INTO users (name, email)
VALUES ('Raj', 'raj@gmail.com');

Rule: In MySQL, AUTO_INCREMENT must be on a key column (typically PRIMARY KEY or UNIQUE).

B. NOT NULL

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  email VARCHAR(50) NOT NULL
);

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(50) NOT NULL
);

Now email is mandatory on insert.

C. UNIQUE

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  email VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(50) NOT NULL UNIQUE
);

Duplicate emails are blocked.

D. CHECK

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  email VARCHAR(50) NOT NULL UNIQUE, age INT,
  CONSTRAINT age_check CHECK (age >= 15 AND age <= 30)
);

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(50) NOT NULL UNIQUE, age INT,
  CONSTRAINT age_check CHECK (age >= 15 AND age <= 30)
);

Insert with age = 12 fails due to the check constraint.

E. DEFAULT

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30),
  email VARCHAR(50) NOT NULL UNIQUE, age INT,
  is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  name VARCHAR(30),
  email VARCHAR(50) NOT NULL UNIQUE, age INT,
  is_active BOOLEAN DEFAULT TRUE
);

If you don't pass is_active, DB stores TRUE automatically.

ALTER TABLE — Changing Structure After Creation

ALTER TABLE is DDL — it changes the shape of an existing table without dropping it. You use it to add, remove, or modify columns and constraints as requirements evolve.

A. Add a New Column

Add phone to users after the table already exists.

ALTER TABLE users ADD COLUMN phone VARCHAR(15);

ALTER TABLE users ADD COLUMN phone VARCHAR(15);

With a default for existing rows:

ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

B. Drop a Column

ALTER TABLE users DROP COLUMN phone;

ALTER TABLE users DROP COLUMN phone;

C. Modify / Change Column Type

Increase name from VARCHAR(30) to VARCHAR(100). Syntax differs slightly:

-- MySQL / SQL Server style
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);
-- or
ALTER TABLE users CHANGE COLUMN name name VARCHAR(100);

ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(100);

D. Rename a Column

ALTER TABLE users RENAME COLUMN name TO full_name;

ALTER TABLE users RENAME COLUMN name TO full_name;

E. Rename the Table

ALTER TABLE users RENAME TO app_users;

ALTER TABLE users RENAME TO app_users;

F. Add / Drop Constraints via ALTER

Add a UNIQUE or CHECK after creation, and drop it if needed:

-- add
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT age_check CHECK (age >= 15 AND age <= 30);
-- drop
ALTER TABLE users DROP CONSTRAINT email_unique;
ALTER TABLE users DROP CHECK age_check; -- MySQL 8.0.16+

-- add
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT age_check CHECK (age >= 15 AND age <= 30);
-- drop
ALTER TABLE users DROP CONSTRAINT email_unique;
ALTER TABLE users DROP CONSTRAINT age_check;

Tip: In production, prefer ALTER TABLE for small, fast changes. For large tables, adding a column with a default or changing a type can lock the table — plan for low-traffic windows.

NULL vs 0 vs Empty String

1. What is NULL?

NULL means “no value”, “unknown”, or “not applicable”. Not zero, not empty string. A blank space where a value could be but isn't.

Example: Middle Name

For id=1, middle_name = NULL — no middle name. Intuition: NULL = There is no value stored here.

2. What is 0 (zero)?

0 is a valid number — zero quantity, not “nothing”.

quantity = 0 means we know the quantity and it is exactly zero. Intuition: 0 = We know the value. It is exactly zero.

3. What is an Empty String ('')?

'' is a string with length 0 — still a valid text value, just no characters. Different from NULL and from ' ' (one space).

nickname = '' means the field exists but the user left it blank deliberately. Intuition: '' = We know the value. It's an empty piece of text.

DDL vs DML

DDL = shape of table. DML = data inside the table.

Query Lifecycle

When you run a query, the database follows three steps: Parse → Plan → Execute. You don't control them directly, but they explain performance.

1. Parse

Checks syntax and validity. Which tables? Which columns? Do they exist?

SELECT name, email
FROM customers
WHERE city = 'Mumbai';

SELECT name, email
FROM customers
WHERE city = 'Mumbai';

During parse it checks: valid SELECT ... FROM ... WHERE syntax? Does customers exist? Do name, email, city exist? If not → fail at parse.

Intuition: Parse = Can I understand this query?

2. Plan

Figures out the fastest way — which indexes to use, join order, scan vs index lookup. Output is an execution plan.

SELECT name, email FROM customers WHERE city = 'Mumbai';

SELECT name, email FROM customers WHERE city = 'Mumbai';

Planner thinks: Is there an index on city? If yes → index seek; if no → full table scan. How big is the table? How many rows match?

Intuition: Plan = What is the smartest way to get the answer?

3. Execute

Actually runs the plan — reads data from disk/memory, applies filters/joins/sorting, returns result set.

SELECT name, email FROM customers WHERE city = 'Mumbai';
-- plan says: index scan on city
-- for each match, pick name + email, return to client

SELECT name, email FROM customers WHERE city = 'Mumbai';
-- plan says: index scan on city
-- for each match, pick name + email, return to client

Intuition: Execute = Now do the work and return rows.

What is an Index in a Database?

An index is a special data structure (often a B-tree) built on one or more columns to make searching faster. Instead of scanning every row, the DB jumps directly to matching rows — like a book index.

Analogy: 1000-page book with no index → flip many pages to find “Binary Search Tree” (full table scan). With an index at the back → topics sorted alphabetically with page numbers → jump directly. That's a DB index.

Notes

Primary sources: MySQL CREATE DATABASE · MySQL Data Types · Postgres CREATE TABLE. Ask me anything that's unclear.

Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this applies to a specific query. Review the SQL glossary or syntax quick reference for a compressed overview.