Creating Your First Database and Table

Table of Contents

1. Introduction
2. What is a Database in SQL?
3. What is a Table in SQL?
4. Syntax of CREATE DATABASE
5. Creating a Database in SQLite
6. Creating a Database in MySQL
7. Creating a Database in PostgreSQL
8. Syntax of CREATE TABLE
9. Choosing Good Column Names
10. Understanding Data Types
11. Defining Primary Keys
12. Adding NOT NULL and UNIQUE Constraints
13. Creating a Basic Table Example
14. Inserting First Row of Data
15. Verifying Table Creation with SELECT
16. Dropping and Re-Creating Tables
17. Creating Tables with Foreign Keys
18. Best Practices for Table Design
19. Common Mistakes to Avoid
20. Summary and What’s Next

1. Introduction

Now that you’ve set up your SQL environment, it’s time to create your first database and table. This module will guide you through syntax, conventions, and best practices using SQLite, MySQL, and PostgreSQL.

2. What is a Database in SQL?

A database is a container that holds all your tables, relationships, and metadata. Think of it as a folder on your system where all structured data for a project lives.

3. What is a Table in SQL?

A table is where actual data resides inside the database. Each table consists of columns (fields) and rows (records), similar to a spreadsheet.

4. Syntax of CREATE DATABASE

Basic SQL syntax:

CREATE DATABASE my_first_db;

Use this statement in MySQL or PostgreSQL. SQLite creates the database as a file, so it doesn’t need this command explicitly.

5. Creating a Database in SQLite

You create a new database by simply referencing a new file:

sqlite3 my_first_db.sqlite

You’re now inside the SQLite shell with a new database file created.

6. Creating a Database in MySQL

CREATE DATABASE my_first_db;
USE my_first_db;

MySQL requires you to switch to the newly created database using the USE command.

7. Creating a Database in PostgreSQL

CREATE DATABASE my_first_db;
\c my_first_db  -- switch to database

PostgreSQL also allows database creation using pgAdmin’s GUI interface.

8. Syntax of CREATE TABLE

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Example:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
);

9. Choosing Good Column Names

• Use snake_case: first_name, created_at
• Be descriptive: use email instead of e
• Avoid SQL keywords like order, group

10. Understanding Data Types

11. Defining Primary Keys

Primary keys uniquely identify a record. Usually assigned to id columns.

id INTEGER PRIMARY KEY

In MySQL:

id INT AUTO_INCREMENT PRIMARY KEY

In PostgreSQL:

id SERIAL PRIMARY KEY

12. Adding NOT NULL and UNIQUE Constraints

Constraints enforce data integrity:

name TEXT NOT NULL,
email TEXT UNIQUE

• NOT NULL: Prevents null values
• UNIQUE: Prevents duplicate values

13. Creating a Basic Table Example

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• AUTOINCREMENT (SQLite)
• DEFAULT CURRENT_TIMESTAMP sets automatic timestamp

14. Inserting First Row of Data

INSERT INTO users (name, email, age)
VALUES ('Anjali', '[email protected]', 24);

You can also insert multiple rows at once:

INSERT INTO users (name, email, age) VALUES
('Ravi', '[email protected]', 30),
('Neha', '[email protected]', 28);

15. Verifying Table Creation with SELECT

SELECT * FROM users;

This displays all columns and rows in the users table.

16. Dropping and Re-Creating Tables

To drop a table:

DROP TABLE users;

To recreate, just run the CREATE TABLE statement again. This is helpful during development.

17. Creating Tables with Foreign Keys

Foreign keys maintain relationships between tables.

Example:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    user_id INTEGER,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

18. Best Practices for Table Design

• Use id as the primary key for every table
• Use lowercase names and snake_case formatting
• Avoid NULL values unless they’re meaningful
• Index frequently searched columns
• Document each table with inline comments

19. Common Mistakes to Avoid

• Forgetting to define a primary key
• Using ambiguous column names like data or value
• Using too general data types (e.g., TEXT for dates)
• Not testing with actual sample inserts
• Forgetting to check constraints during inserts

20. Summary and What’s Next

You’ve now created your first database and table across SQLite, MySQL, and PostgreSQL environments. You learned the importance of syntax, constraints, column naming, and how to insert your first records.