CREATE TABLE

Every IPL database starts empty. Before you can record players, matches or scores, you need tables to hold them. CREATE TABLE is the command that builds a new table from nothing.

Definition

CREATE TABLE is a DDL statement that creates a new, empty table in the database by defining its name, its columns, the data type of each column and, optionally, constraints (rules) on those columns.

• New — the table must not already exist; otherwise SQL Server raises "There is already an object named 'players' in the database."
• Empty — a freshly created table has 0 rows. CREATE builds the container only; you fill it later with INSERT.
• Columns + data types — every column needs a name and a type.
• Constraints — optional rules like PRIMARY KEY and NOT NULL that protect the data.

Syntax

CREATE TABLE table_name (
    column1 data_type [constraint],
    column2 data_type [constraint],
    ...
    [CONSTRAINT constraint_name PRIMARY KEY (column)]
);

Step by step

1. Name the table — players
2. List the columns + data types — id INT, name VARCHAR(50), …
3. Add rules (constraints) — NOT NULL, PRIMARY KEY

Example — the course's players table

This is the exact statement from the course slides:

CREATE TABLE players (
    id      INT         NOT NULL,
    name    VARCHAR(50) NOT NULL,
    team    VARCHAR(10),
    country VARCHAR(30),
    runs    INT,
    CONSTRAINT pk_players PRIMARY KEY (id)
);

Result — a brand-new, empty table:

How it works:

• id and name are NOT NULL — every player must have them.
• team, country and runs have no rule, so they may be left empty (NULL).
• CONSTRAINT pk_players PRIMARY KEY (id) makes id the primary key (the 🔑): every player gets a unique id. pk_players is just the name we gave to this rule.

Note: In the playground, a players table already exists (it's part of the IPL practice database). So the runnable examples below create new tables like matches and players_new. If you really want to rebuild players, DROP it first.

Example — a matches table

CREATE TABLE matches (
    match_id   INT         NOT NULL,
    match_date DATE        NOT NULL,
    home_team  VARCHAR(10) NOT NULL,
    away_team  VARCHAR(10) NOT NULL,
    venue      VARCHAR(50),
    CONSTRAINT pk_matches PRIMARY KEY (match_id)
);

INSERT INTO matches (match_id, match_date, home_team, away_team, venue)
VALUES (1, '2025-03-22', 'KKR', 'RCB', 'Eden Gardens');

SELECT * FROM matches;

How it works: CREATE TABLE built the empty structure, INSERT added one row and SELECT read it back.

Naming good tables and columns

• Use lowercase with underscores (snake_case): player_profiles, match_date.
• Use clear, meaningful names: runs, not r.
• Avoid spaces and reserved words (order, user, date, table) — they need [square brackets] in SQL Server and cause confusion.
• Be consistent: either always singular (player) or always plural (players).

Common mistakes

• ❌ A comma after the last column: runs INT, ) → syntax error. ✅ No comma before the closing bracket.
• ❌ Forgetting the data type: name NOT NULL. ✅ name VARCHAR(50) NOT NULL.
• ❌ Creating a table that already exists → "There is already an object named …". ✅ Pick a new name or DROP TABLE first.
• ❌ Expecting rows after CREATE TABLE. ✅ A new table is always empty — use INSERT.
• ❌ Using "double quotes" around text values later. ✅ Text values use single quotes: 'RCB'.

SQL Server vs MySQL / PostgreSQL

"Create only if it doesn't exist" is written differently:

SQL Server tables also live inside a schema — the default is dbo, so players is really dbo.players.

Interview tip

Q: You run CREATE TABLE and then SELECT * from the new table. What do you see? A: Only the column headers and zero rows. CREATE TABLE defines the structure; it never inserts data.