🚀 Day 5: Databases, Tables in SQL

Welcome back to Day 5 of the SQL tutorial series!

CREATE DATABASE – Start Fresh

Syntax:

CREATE DATABASE database_name;
-- Example: CREATE DATABASE college;

This creates a brand-new database named college.

After creating it:

You usually switch to it using:

USE college;

DROP DATABASE – Delete with Caution

Syntax:

DROP DATABASE database_name;
-- Example: DROP DATABASE college;

👉 This deletes the entire database, including all tables, data, views, and stored procedures inside it.

⚠️ Warning: This action is irreversible. Use it only when you’re sure you want to remove the entire dataset.

Best Practices

• Always double-check the name before running DROP DATABASE.

• In production environments, deletion permissions are usually restricted for safety.

• Use IF EXISTS to prevent errors if the DB doesn’t exist:

CREATE DATABASE IF NOT EXISTS college;

CREATE TABLE IF NOT EXISTS student (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT NOT NULL
);

DROP DATABASE IF EXISTS college;

DROP TABLE IF EXISTS student;

🧠 Common Errors & Fixes

---

How to Create Tables in SQL

Now that we have a database, it’s time to fill it with structure — starting with tables!

What is a Table?

• A table is the basic unit where your data lives inside a relational database.

• Each table consists of:

  • Columns – define the structure (e.g., Name, Age, Email)

  • Rows – store individual records (e.g., a student’s actual data)

Think of it like a spreadsheet with headers (columns) and rows of data.

CREATE TABLE – Define the Structure

Syntax:

CREATE TABLE table_name (
    columnname1 datatype constraints,
    columnname2 datatype constraints,
    ...
);

🧑‍🎓 Example: Student Table

CREATE DATABASE college;
USE college;
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT NOT NULL
);

---

INSERT INTO – Add Records to the Table

Basic Syntax:

INSERT INTO table_name VALUES (value1, value2, ...);

OR

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Insert one student:

INSERT INTO student (id, name, age)
VALUES (1, 'Alice', 20);

Insert multiple students:

INSERT INTO student (id, name, age)
VALUES 
(2, 'Bob', 21),
(3, 'Carol', 22),
(4, 'David', 19);

Complete table:

-- Create the database
CREATE DATABASE college;

--Use database
USE college;

--Create Table
CREATE TABLE student (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT NOT NULL
);

--Insert single student
INSERT INTO student VALUES (1, 'Alice', 21);

--Insert Multiple students
INSERT INTO student (id, name, age) VALUES 
(2, 'Bob', 23), 
(3, 'Carol', 27);

--View the data
SELECT * FROM student;