SQL Basics

SQL basics focus on understanding how SQL commands are organized, how databases and tables are created, and how data is defined and stored. These concepts form the foundation for writing effective SQL queries and working with real-world databases.

1. DDL – Data Definition Language

Data Definition Language (DDL) commands are used to define, create, modify, or delete the structure of database objects such as databases and tables. These commands do not deal with actual data; instead, they define how data will be stored.

Common DDL Commands:

CREATE
Used to create new database objects such as databases and tables.

ALTER
Used to modify the structure of an existing table, such as adding or removing columns.

DROP
Used to permanently delete a database or table from the system.

TRUNCATE
Used to remove all records from a table very quickly, without deleting the table structure.

Important note:
DDL commands are auto-committed, meaning changes cannot be rolled back once executed.

2. DML – Data Manipulation Language

Data Manipulation Language (DML) commands are used to work with the actual data stored in tables. These commands allow users to insert new records, modify existing records, delete records, and retrieve data.

Common DML Commands:

SELECT
Used to retrieve data from one or more tables.

INSERT
Used to add new records into a table.

UPDATE
Used to modify existing records.

DELETE
Used to remove specific records from a table.

DML commands affect data, not structure, and they can usually be rolled back if transactions are used.

3. DCL – Data Control Language

Data Control Language (DCL) commands are used to control access and permissions in the database. These commands are mainly used by database administrators to manage security.

Common DCL Commands:

GRANT
Used to give specific permissions to users, such as read or write access.

REVOKE
Used to remove previously given permissions.

4. TCL – Transaction Control Language

Transaction Control Language (TCL) commands are used to manage transactions, which are groups of SQL statements executed together as a single unit.

Common TCL Commands:

COMMIT
Saves all changes made during the transaction permanently.

ROLLBACK
Reverts changes made during the transaction if an error occurs.

SAVEPOINT
Creates a point within a transaction to which you can roll back.

TCL ensures data consistency and safety, especially in real-time applications like banking systems.

Creating Your First Database

Before storing any data, we need a database. A database acts as a container that holds tables and other database objects.

CREATE DATABASE company_db;

This command creates a new database named company_db. At this stage, the database is empty and contains no tables.

To start working inside this database, we must select it:

USE company_db;

Once this command is executed, all future SQL commands will apply to the company_db database.

Creating Your First Table

After selecting a database, the next step is to create tables to store data.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    salary DECIMAL(10, 2),
    department VARCHAR(50)
);

Explanation of the Table Structure

This command creates a table named employees that stores employee-related information.

employee_id stores a unique number for each employee and acts as the primary key.
first_name and last_name store employee names.
email stores the employee’s email address.
hire_date stores the date the employee joined the company.
salary stores the employee’s salary with decimal precision.
department stores the department name.

Each column is assigned a data type that defines what kind of data it can store. This helps maintain data accuracy and consistency.

Common SQL Data Types

Choosing the correct data type is very important because it affects storage, performance, and data validation.

INT

Used to store whole numbers.
Example: employee IDs, age, quantity.

VARCHAR(n)

Used to store variable-length text up to n characters.
Example: names, email addresses.
It saves space because it only uses required memory.

CHAR(n)

Used to store fixed-length text.
If the value is shorter, remaining space is filled automatically.
Example: country codes like “IN”, “US”.

DATE

Used to store date values in YYYY-MM-DD format.
Example: date of birth, hire date.

DATETIME

Used to store both date and time.
Example: login time, transaction timestamp.

DECIMAL(p, s)

Used to store exact decimal values.

p = total number of digits
s = digits after decimal point
Example: salary, price, account balance.

BOOLEAN

Used to store true or false values.
Example: active/inactive status.

TEXT

Used to store long text data.
Example: descriptions, comments, feedback.

Log In

Sign Up