SQL Tables: Deep Dive

1. Table Overview

An SQL table stores rows and columns. Each column has a datatype and constraints; each row is a record.

1. Column: Attribute definition
2. Row: Data entry
3. Schema: Structure of columns
4. Constraints: Rules (PK, FK, UNIQUE, CHECK)

2. Creating Tables

CREATE TABLE employees (
  employee_id   INT            PRIMARY KEY,
  first_name    VARCHAR(50)    NOT NULL,
  last_name     VARCHAR(50)    NOT NULL,
  hire_date     DATE           DEFAULT CURRENT_DATE,
  salary        DECIMAL(10,2)  CHECK (salary >= 0)
);

Explanation: Defines five columns; PRIMARY KEY ensures uniqueness; NOT NULL forbids nulls; DEFAULT auto-populates; CHECK enforces positive salary.

3. Inserting Data

INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (1, 'Alice', 'Wong', 75000.00);

INSERT INTO employees
VALUES
  (2, 'Bob', 'Patel', '2025-05-17', 80000.00),
  (3, 'Cathy', 'Lee', '2025-05-17', 62000.50);

First: specify columns explicitly. Second: rely on column order; include all columns.

4. Querying Tables

SELECT * FROM employees;

Use ORDER BY, WHERE, and LIMIT to refine results.

5. Altering Structure

5.1 Add Column

ALTER TABLE employees
  ADD department VARCHAR(30) DEFAULT 'General';

Sets a new department column; default value applied.

5.2 Modify Column

ALTER TABLE employees
  ALTER COLUMN department SET NOT NULL;

Enforces every row must have a department value.

5.3 Drop Column

ALTER TABLE employees
  DROP COLUMN department;

6. Constraints Explained

Constraints enforce rules at the table level to maintain data integrity. Below are the most common types:

PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each row in a table. A table can have only one primary key, which can consist of one or multiple columns (composite key).

CREATE TABLE projects (
  project_id INT,
  name       VARCHAR(100),
  PRIMARY KEY(project_id)
);

FOREIGN KEY

The FOREIGN KEY constraint ensures referential integrity by linking a column (or columns) in one table to the primary key of another table. This prevents orphaned records.

CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  name    VARCHAR(50)
);

ALTER TABLE employees
  ADD dept_id INT,
  ADD FOREIGN KEY(dept_id) REFERENCES departments(dept_id);

UNIQUE & CHECK

The UNIQUE constraint disallows duplicate values in the specified column(s), ensuring all entries are distinct but allowing one NULL.

The CHECK constraint enforces a Boolean expression on one or more columns. Rows that do not satisfy the check condition are rejected.

ALTER TABLE employees
  ADD CONSTRAINT unique_name UNIQUE(first_name, last_name),
  ADD CONSTRAINT chk_salary CHECK(salary BETWEEN 30000 AND 200000);

7. Copying & Temporary Tables

Create as Select

CREATE TABLE high_earners AS
  SELECT * FROM employees WHERE salary > 70000;

Temporary Table

CREATE TEMPORARY TABLE temp_logs (
  log_id SERIAL PRIMARY KEY,
  message TEXT,
  created TIMESTAMP DEFAULT NOW()
);

8. Truncate & Drop

TRUNCATE TABLE employees;  -- fast remove all rows
DROP TABLE employees;        -- removes table entirely

9. Example Schema: Students & Courses

CREATE TABLE students (
  student_id SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
  course_id SERIAL PRIMARY KEY,
  title     VARCHAR(200) NOT NULL
);

CREATE TABLE enrollments (
  student_id INT,
  course_id  INT,
  grade      CHAR(2),
  PRIMARY KEY(student_id, course_id),
  FOREIGN KEY(student_id) REFERENCES students(student_id),
  FOREIGN KEY(course_id)  REFERENCES courses(course_id)
);

INSERT INTO students(name) VALUES ('Eve Adams'), ('Frank Miller');
INSERT INTO courses(title) VALUES ('Intro to SQL'), ('Advanced Databases');
INSERT INTO enrollments VALUES (1,1,'A'), (1,2,'B'), (2,1,'A+');

SELECT s.name, c.title, e.grade
  FROM students s
  JOIN enrollments e ON s.student_id=e.student_id
  JOIN courses c    ON e.course_id=c.course_id
  ORDER BY s.name;

10. Best Practices

Experiment with these commands to master SQL table design and usage!