Databases And Tables In SQL

What are SQL Databases and Tables?

A SQL database is a structured collection of related data stored electronically. Inside every database, information is organized into tables, where data is stored in rows and columns. SQL (Structured Query Language) provides commands to create, modify, retrieve, and manage databases and tables efficiently.

Databases and tables are the foundation of relational database systems such as MySQL, SQL Server, PostgreSQL, and Oracle. Understanding how they work is essential before learning advanced SQL topics like joins, views, stored procedures, and transactions.

Database

A database is a collection of data organized to allow easy access, management, and updating. SQL provides specific commands for managing databases, such as creating, deleting, and backing up databases.

Creating a Database:

Syntax:

CREATE DATABASE database_name;

Example:

 CREATE DATABASE company_db;

The CREATE DATABASE statement creates a new database where tables and other database objects can be stored. Before creating a database, ensure the database name does not already exist. Some database systems also support IF NOT EXISTS to avoid errors.

Dropping a Database:

Permanently deletes an entire database along with all the data and tables it contains.

Syntax:

DROP DATABASE database_name;

Example:

 DROP DATABASE company_db;

Backing Up a Database:

To back up your database to avoid data loss due to deletions or failures.

Syntax:

BACKUP DATABASE database_name TO DISK = 'file_path';

Example:

 BACKUP DATABASE company_db TO DISK = 'C:/backup/company_db.bak';

Note: Specific database systems like SQL Server support the BACKUP command. In MySQL, you may need to use external tools like mysqldump.

Tables

Tables are where data is stored inside the database. They consist of rows and columns, with each column representing a different data attribute. SQL commands allow you to create, modify, and delete tables as per your requirements.

Syntax:

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

Example:

CREATE TABLE employees (
    			id INT PRIMARY KEY,
    			name VARCHAR(100),
    			department VARCHAR(50),
    			salary DECIMAL(10, 2)
                        );

Dropping a Table:

Permanently deletes the table and all the data it contains.

Syntax:

 DROP TABLE table_name;

Example:

 DROP TABLE employees;

Altering a Table:

The ALTER TABLE command is used to modify the structure of an existing table. You can add new columns, modify existing columns, or remove columns.

Add a Column:

Syntax:

ALTER TABLE table_name ADD column_name datatype;

Example:

ALTER TABLE employees ADD hire_date DATE;

Modify a Column:

The syntax for modifying a column differs slightly between SQL Flavors.

In MySQL & PostgreSQL:

ALTER TABLE table_name MODIFY column_name datatype;

Example:

ALTER TABLE employees MODIFY salary DECIMAL(12, 2);

In MSSQL:

ALTER TABLE table_name ALTER COLUMN column_name datatype;

Example:

ALTER TABLE employees ALTER COLUMN salary DECIMAL(12, 2);

Drop a Column:

Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE employees DROP COLUMN department;

Truncating a Table:

The TRUNCATE TABLE command removes all rows from a table without affecting the table’s structure. It is faster than DELETE because it does not log individual row deletions.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE employees;

Deleting Data from a Table:

The DELETE command removes rows from a table based on a condition. Unlike TRUNCATE, DELETE allows for more granular deletion of specific rows and can be rolled back if required.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM employees WHERE department = 'HR';

Difference Between DELETE, TRUNCATE, and DROP:

• DELETE: Removes specific rows based on a condition; can be rolled back using a transaction.
• TRUNCATE: Removes all rows from a table quickly; cannot be rolled back.
• DROP: Completely deletes a table along with its structure and data; cannot be undone.

Command	Deletes Data	Deletes Structure	Can Use WHERE	Rollback Support*
DELETE	✅	❌	✅	Usually Yes
TRUNCATE	✅	❌	❌	Depends on DBMS
DROP	✅	✅	❌	No

*Rollback behavior varies by database system and transaction settings.

Sample Data for next topics:

Run the below queries to get the sample data to practice.

CREATE DATABASE company_db;

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2),
    HireDate DATE,
    Email VARCHAR(100)
);

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, HireDate, Email) 
VALUES
(1, 'John', 'Doe', 'HR', 50000, '2020-01-15', 'john.doe@example.com'),
(2, 'Jane', 'Smith', 'IT', 60000, '2019-03-12', 'jane.smith@example.com'),
(3, 'Emily', 'Davis', 'IT', 60000, '2021-07-20', 'emily.davis@example.com'),
(4, 'Robert', 'Johnson', 'Finance', 70000, '2018-05-25', 'robert.j@example.com'),
(5, 'Michael', 'Brown', 'Marketing', NULL, '2022-09-10', 'michael.brown@example.com'),
(6, 'Sarah', 'Wilson', NULL, 55000, '2023-01-10', 'sarah.wilson@example.com'),
(7, 'David', 'Lee', 'IT', 65000, '2020-11-30', 'david.lee@example.com'),
(8, 'Chris', 'Turner', 'HR', 52000, '2021-06-18', 'chris.turner@example.com'),
(9, 'Jessica', 'White', 'Finance', 72000, '2019-12-01', 'jessica.white@example.com'),
(10, 'Daniel', 'Harris', 'Marketing', 48000, '2022-03-15', 'daniel.harris@example.com');

Conclusion

SQL databases and tables are the backbone of every relational database system. By understanding commands such as CREATE DATABASE, CREATE TABLE, ALTER TABLE, DELETE, TRUNCATE, and DROP, you can efficiently organize, manage, and maintain structured data. Learning these fundamentals will make it easier to work with advanced SQL concepts such as joins, views, stored procedures, indexes, and transactions.

Knowledge Check

Frequently Asked Questions