Table of Contents
- Introduction
- What is a Database in SQL?
- What is a Table in SQL?
- Syntax of
CREATE DATABASE - Creating a Database in SQLite
- Creating a Database in MySQL
- Creating a Database in PostgreSQL
- Syntax of
CREATE TABLE - Choosing Good Column Names
- Understanding Data Types
- Defining Primary Keys
- Adding NOT NULL and UNIQUE Constraints
- Creating a Basic Table Example
- Inserting First Row of Data
- Verifying Table Creation with
SELECT - Dropping and Re-Creating Tables
- Creating Tables with Foreign Keys
- Best Practices for Table Design
- Common Mistakes to Avoid
- 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
emailinstead ofe - Avoid SQL keywords like
order,group
10. Understanding Data Types
| Data Type | Description | Common In |
|---|---|---|
INTEGER | Whole numbers | All |
TEXT | Variable-length string | SQLite |
VARCHAR(n) | String with max length n | MySQL, PG |
DATE | Date values | All |
BOOLEAN | True/False | PG, MySQL |
FLOAT/DECIMAL | Decimal numbers | All |
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 valuesUNIQUE: 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_TIMESTAMPsets 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
idas 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
dataorvalue - Using too general data types (e.g.,
TEXTfor 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.