Databases And Tables In SQL: A Comprehensive Guide

In SQL, databases and tables are the core elements for storing, organizing, and managing data. This section will explore the SQL commands used for creating, deleting, and modifying databases and tables. Additionally, we’ll discuss methods for backing up databases and understanding the differences between various table operations like DELETE, TRUNCATE, and DROP.

Databases

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;

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.

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

In SQL, databases and tables are fundamental components for organizing and managing data. By using the appropriate commands such as CREATE, DROP, ALTER, DELETE, and TRUNCATE, you can efficiently manage the structure and data within a database. It’s important to understand when and how to use these commands to avoid accidental data loss and ensure the proper functioning of your database.

Knowledge Check