How to Create a Database and Tables in SQL

A database holds everything for one project. Inside it, each table stores one kind of thing, such as students, orders or sensors. The columns define what you record about that thing, and each row is one record.

Before writing any code, decide three things for every column: its name, the kind of data it holds, and the rules it must follow. Those rules are called constraints, and they prevent bad data from entering.

Creating a Database and a Table

Let’s build a small table for a coaching institute. First, create the database.

CREATE DATABASE institute;

Creating a database does not open it. In pgAdmin, right-click the new database, open a query window, and create the table.

CREATE TABLE students (
    student_id  SERIAL PRIMARY KEY,
    full_name   VARCHAR(100) NOT NULL,
    email       VARCHAR(120) UNIQUE,
    fee_paid    NUMERIC(8,2) CHECK (fee_paid >= 0),
    joined_on   DATE DEFAULT CURRENT_DATE
);

Code Explanation:

• SERIAL gives every new row the next number automatically, and PRIMARY KEY makes that number the unique identity of the row.
• VARCHAR(100) caps the name at 100 characters, and NOT NULL means a student cannot be saved without one.
• UNIQUE on email blocks two students from sharing the same address.
• NUMERIC(8,2) stores up to 8 digits with 2 after the decimal point, and CHECK rejects a negative fee.
• DEFAULT CURRENT_DATE fills in today’s date when no joining date is given.

MySQL users should replace SERIAL with INT AUTO_INCREMENT PRIMARY KEY.

Picking the Right Data Type

The data type tells the database what kind of value a column holds. Use VARCHAR for short text, INTEGER for whole numbers, DATE for calendar dates and BOOLEAN for true or false values.

For money, always use NUMERIC. Floating-point types can add tiny rounding errors, which you do not want in a fee or price column.

Linking Tables Together

Real databases rarely keep everything in one table. A FOREIGN KEY constraint links a column in one table to the primary key of another, for example, a course ID inside the students table. You will use this a lot when we get to joins.