Database and Table Creation

Before storing data you create a database (a container), optionally schemas (namespaces inside the database), and tables (the structures that hold rows).

Concept

Before storing data you create a database (a container), optionally schemas (namespaces inside the database), and tables (the structures that hold rows). Tables evolve over time with ALTER TABLE.

Database (= schema) Database (usually one per instance, pluggable DBs in 12c+)

Namespace inside a DB (public default) Synonym for database Namespace (dbo default) Owned by a user (user = schema)

Server / Instance
 └── Database: sampleapp
      ├── Schema: public / dbo
      │    ├── Table: customers
      │    ├── Table: orders
      │    └── View: v_order_summary
      └── Schema: analytics
           └── Table: daily_sales

Why It Matters

A well-designed table structure enforces correctness automatically.

Schema changes in production are risky; knowing ALTER TABLE behavior (locks, defaults, NULL handling) avoids outages.

Syntax

-- Create a database
CREATE DATABASE sampleapp;

-- Switch to it
USE sampleapp;               -- MySQL, SQL Server
-- PostgreSQL: connect instead:  \c sampleapp   (psql meta-command)
-- SQLite: the database is the file you open: sqlite3 sampleapp.db
-- Oracle: connect to a user/schema or pluggable database

-- Create a table
CREATE TABLE table_name (
    column_name  data_type  [column_constraints],
    ...
    [table_constraints]
);

-- Create only if it does not exist
CREATE TABLE IF NOT EXISTS table_name (...);   -- PostgreSQL, MySQL, SQLite, Oracle 23ai+
-- SQL Server 2016+: DROP TABLE IF EXISTS exists; for CREATE use:
-- IF OBJECT_ID('dbo.table_name', 'U') IS NULL CREATE TABLE ...

Example

3.1 CREATE DATABASE and USE 3.2 CREATE TABLE — a complete example

student_id INT NOT NULL — column name, data type, column constraint.

DEFAULT 'Undeclared' — used when an INSERT omits the column.

CHECK (cgpa BETWEEN 0 AND 10) — rejects invalid values.

CONSTRAINT pk_students PRIMARY KEY (student_id) — named table-level constraint. Naming constraints makes error messages and later changes much easier.

3.3 CREATE TABLE AS (copy structure and data) Note: CREATE TABLE... AS SELECT copies column names and types but not primary keys, foreign keys, indexes, or most constraints.

3.4 ALTER TABLE — adding columns 3.5 ALTER TABLE — removing columns

A column cannot be dropped if it is used in a constraint, index, or view in some DBMSs; drop the dependent object first (PostgreSQL supports DROP COLUMN ... CASCADE).

3.6 ALTER TABLE — modifying columns (type, NULL, default) The syntax differs substantially:

3.7 ALTER TABLE — renaming columns 3.8 RENAME TABLE 3.9 Adding and dropping constraints on an existing table 3.10 TRUNCATE TABLE and DROP TABLE 3.11 Default values Login page crashes 3.12 Column constraints overview name VARCHAR(50) NOT NULL email VARCHAR(100) UNIQUE Unique + not null row identifier id INT PRIMARY KEY FOREIGN KEY / REFERENCES Must match a row in another table

dept_id INT REFERENCES departments(dept_id) CHECK (price >= 0) Value when none supplied DEFAULT 'OPEN' Module 5 covers constraints in depth.

CREATE DATABASE sampleapp;

-- MySQL: specify character set and collation
CREATE DATABASE sampleapp
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- PostgreSQL: specify encoding
CREATE DATABASE sampleapp ENCODING 'UTF8';

-- SQL Server
CREATE DATABASE sampleapp;
GO
USE sampleapp;
GO

-- Drop a database (dangerous!)
DROP DATABASE IF EXISTS sampleapp_test;

CREATE TABLE students (
    student_id     INT           NOT NULL,
    roll_no        VARCHAR(20)   NOT NULL,
    first_name     VARCHAR(50)   NOT NULL,
    last_name      VARCHAR(50)   NOT NULL,
    email          VARCHAR(120)  NOT NULL,
    date_of_birth  DATE,
    department     VARCHAR(50)   NOT NULL DEFAULT 'Undeclared',
    cgpa           DECIMAL(4,2)  CHECK (cgpa BETWEEN 0 AND 10),
    is_active      BOOLEAN       NOT NULL DEFAULT TRUE,   -- SQL Server: BIT DEFAULT 1; Oracle < 23ai: NUMBER(1)
    created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT pk_students        PRIMARY KEY (student_id),
    CONSTRAINT uq_students_roll   UNIQUE (roll_no),
    CONSTRAINT uq_students_email  UNIQUE (email)
);

-- PostgreSQL, MySQL, Oracle, SQLite
CREATE TABLE electronics_products AS
SELECT product_id, product_name, price
FROM products
WHERE category = 'Electronics';

-- SQL Server
SELECT product_id, product_name, price
INTO electronics_products
FROM products
WHERE category = 'Electronics';

-- Copy structure only (no rows): add a false condition
CREATE TABLE products_empty AS SELECT * FROM products WHERE 1 = 0;

-- Standard / PostgreSQL / MySQL / SQLite / Oracle (Oracle omits COLUMN keyword and uses parentheses optionally)
ALTER TABLE students ADD COLUMN phone VARCHAR(15);

-- SQL Server and Oracle
ALTER TABLE students ADD phone VARCHAR(15);

-- Add multiple columns
-- PostgreSQL / MySQL
ALTER TABLE students
    ADD COLUMN city VARCHAR(50),
    ADD COLUMN pincode VARCHAR(10);
-- SQL Server
ALTER TABLE students ADD city VARCHAR(50), pincode VARCHAR(10);
-- Oracle
ALTER TABLE students ADD (city VARCHAR2(50), pincode VARCHAR2(10));

-- Add a NOT NULL column to a table that already has rows: provide a DEFAULT
ALTER TABLE students ADD COLUMN batch_year INT NOT NULL DEFAULT 2026;

ALTER TABLE students DROP COLUMN pincode;   -- PostgreSQL, MySQL, SQL Server, Oracle, SQLite 3.35+

-- PostgreSQL
ALTER TABLE students ALTER COLUMN phone TYPE VARCHAR(20);
ALTER TABLE students ALTER COLUMN phone SET NOT NULL;
ALTER TABLE students ALTER COLUMN phone DROP NOT NULL;
ALTER TABLE students ALTER COLUMN department SET DEFAULT 'General';
ALTER TABLE students ALTER COLUMN department DROP DEFAULT;

-- MySQL (MODIFY redefines the entire column: repeat NULL/DEFAULT or they are lost)
ALTER TABLE students MODIFY COLUMN phone VARCHAR(20) NULL;
ALTER TABLE students ALTER COLUMN department SET DEFAULT 'General';

-- SQL Server
ALTER TABLE students ALTER COLUMN phone VARCHAR(20) NULL;
-- Defaults in SQL Server are constraints:
ALTER TABLE students ADD CONSTRAINT df_students_dept DEFAULT 'General' FOR department;

-- Oracle
ALTER TABLE students MODIFY (phone VARCHAR2(20) NOT NULL);

-- SQLite: very limited ALTER; changing a column type requires rebuilding the table

-- PostgreSQL, MySQL 8+, Oracle, SQLite 3.25+
ALTER TABLE students RENAME COLUMN phone TO mobile_no;

-- MySQL 5.7 and older (CHANGE requires full definition)
ALTER TABLE students CHANGE phone mobile_no VARCHAR(20);

-- SQL Server
EXEC sp_rename 'students.phone', 'mobile_no', 'COLUMN';

ALTER TABLE students RENAME TO learners;      -- PostgreSQL, SQLite, Oracle, MySQL
RENAME TABLE students TO learners;            -- MySQL
RENAME students TO learners;                  -- Oracle
EXEC sp_rename 'students', 'learners';        -- SQL Server

ALTER TABLE students ADD CONSTRAINT chk_students_email CHECK (email LIKE '%@%');
ALTER TABLE students DROP CONSTRAINT chk_students_email;   -- MySQL 8.0.19+: DROP CHECK / DROP CONSTRAINT

TRUNCATE TABLE students;                  -- remove all rows, keep structure
TRUNCATE TABLE students RESTART IDENTITY; -- PostgreSQL: also reset identity sequences

DROP TABLE students;                      -- remove table
DROP TABLE IF EXISTS students;            -- no error if missing (PostgreSQL, MySQL, SQLite, SQL Server 2016+, Oracle 23ai+)
DROP TABLE departments CASCADE;           -- PostgreSQL: also drops dependent FK constraints/views
DROP TABLE departments CASCADE CONSTRAINTS; -- Oracle

Expected Result

After creating students and running:

(department and is_active were filled by their DEFAULT values. SQL Server displays 1 for a BIT true value.)

INSERT INTO students (student_id, roll_no, first_name, last_name, email, cgpa)
VALUES (1, 'CS-2026-001', 'Ravi', 'Kumar', '[email protected]', 8.75);

SELECT student_id, roll_no, department, cgpa, is_active FROM students;

Common Mistakes

Forgetting to select the database (USE) and creating tables in the wrong place.

Adding a NOT NULL column without a DEFAULT to a table that already has rows — the ALTER fails.

Using MySQL MODIFY and forgetting to repeat NOT NULL/DEFAULT, silently changing the column to nullable.

Not naming constraints, which leads to system-generated names like SYS_C0012345 that differ between environments.

Using reserved words as identifiers (order, user, group, table) — requires quoting everywhere. Prefer orders, app_user.

Running DROP TABLE in production without a backup.

Expecting CREATE TABLE AS SELECT to copy keys and indexes.

Best Practices

Use snake_case, and pick a convention for plural (customers) vs singular (customer) and stick to it.

Always define a primary key.

Name constraints: pk_<table>, fk_<child>_<parent>, uq_<table>_<col>, chk_<table>_<rule>, df_<table>_<col>.

Add created_at / updated_at audit columns to business tables.

Apply schema changes through migration scripts under version control.

On large production tables, check whether an ALTER rewrites the table or takes long locks (e.g., changing a column type often rewrites the table).

Interview Relevance

Medium. Common: "How do you add a column?", "How do you rename a column in your DBMS?", "What happens when you add a NOT NULL column to a table with data?", "Difference between CREATE TABLE AS and CREATE TABLE LIKE?" (MySQL CREATE TABLE new LIKE old copies structure including indexes, but not data or foreign keys).

Practice Questions

Create a table courses with course_id (PK), course_code (unique, required), title (required), credits (1 to 6), and created_at defaulting to the current timestamp.

Add a column instructor VARCHAR(100) to courses.

Rename instructor to faculty_name in your DBMS.

Change title to allow up to 200 characters.

Write the SQL to create an empty copy of products with no rows.

Explain why ALTER TABLE courses ADD level INT NOT NULL; might fail.