Creating Databases and Tables
SQL Basics Course
Topic 6: Creating Databases and Tables
Learn how to create, modify and manage databases and tables using SQL.
Learning Objectives
- Create databases
- Select databases
- Create tables
- Understand data types
- Modify table structures
- Delete databases and tables safely
Creating a Database
A database is created using the CREATE DATABASE statement.
CREATE DATABASE school_db;
Viewing Existing Databases
SHOW DATABASES;
This command lists all databases available on the server.
Selecting a Database
USE school_db;
After selecting a database, all tables will be created inside it.
Creating a Table
CREATE TABLE students (
id INT,
name VARCHAR(100),
city VARCHAR(100)
);
The table contains three columns: id, name and city.
Common SQL Data Types
| Data Type | Description |
|---|---|
| INT | Whole numbers |
| VARCHAR | Variable length text |
| CHAR | Fixed length text |
| DATE | Date values |
| DECIMAL | Decimal numbers |
| BOOLEAN | True/False values |
Adding a New Column
ALTER TABLE students
ADD email VARCHAR(255);
ALTER TABLE changes the structure of an existing table.
Modifying a Column
ALTER TABLE students
MODIFY name VARCHAR(150);
Removing a Column
ALTER TABLE students
DROP COLUMN email;
Deleting a Table
DROP TABLE students;
DROP TABLE permanently removes the table and all its data.
Removing All Records
TRUNCATE TABLE students;
TRUNCATE removes all rows but keeps the table structure.
Deleting a Database
DROP DATABASE school_db;
Use carefully because the database and all tables are deleted.
Complete Example
CREATE DATABASE school_db;
USE school_db;
CREATE TABLE students (
id INT,
name VARCHAR(100),
city VARCHAR(100)
);
Best Practices
- Use meaningful database names.
- Use meaningful table names.
- Select appropriate data types.
- Backup before using DROP commands.
- Test changes in development environments first.
Practice Exercises
- Create a database named company_db.
- Create an employees table.
- Add a salary column.
- Remove a column from the table.
- Delete all records using TRUNCATE.
Quiz
1. Which command creates a database?
2. Which command selects a database?
3. Which statement creates a table?
4. What does ALTER TABLE do?
5. What is the difference between DROP and TRUNCATE?
Summary
- CREATE DATABASE creates databases.
- USE selects a database.
- CREATE TABLE creates tables.
- ALTER TABLE modifies table structure.
- DROP removes objects permanently.
- TRUNCATE removes data while keeping the table.