SQL - Basics

Data is everywhere and is often stored in different files and formats. As data grows, managing it using files alone becomes inefficient. This creates the need for a database.

A database is an organized collection of data that allows information to be stored, retrieved, and managed efficiently. Databases are beneficial because they provide:

1. Large and scalable storage
2. Secure and controlled access to data
3. Data consistency and integrity
4. Faster querying and data management

A Database Management System (DBMS) is a software system that allows users to create, manage, secure, and manipulate databases. Examples include MySQL, PostgreSQL, Oracle, SQL Server, and SQLite

Structured Query Language (SQL) is the standard language used to communicate with relational databases. SQL allows users to:

1. Create databases and tables
2. Insert, update, and delete data
3. Retrieve data using queries
4. Control access and permissions

Several types of databases exist, each designed for specific use cases:

1. Relational Database Stores data in predefined tables made up of rows and columns. Example: MySQL, PostgreSQL Best for structured data and complex queries.
2. Key-Value Database Stores data as key–value pairs. Example: Redis, DynamoDB Best for fast lookups and caching.
3. Column-Based (Column-Oriented) Database Stores data grouped by columns rather than rows. Example: Apache Cassandra, Google BigQuery Best for analytics and large-scale data processing.
4. Graph Database Stores data as nodes and relationships. Example: Neo4j Best for relationship-heavy data such as social networks.
5. Document Database Stores data in document formats such as JSON or BSON. Example:_ MongoDB_ Best for semi-structured or flexible data.

Databases exist within a structure.
A database server hosts and manages databases.
Within a database, data is organized using a schema.
A schema defines the structure of the database and contains:

1. Tables
2. Views
3. Indexes
4. Stored procedures and other objects

Tables consist of:

1. Rows (records): individual entries
2. Columns (fields): attributes of the data

Types of SQL Commands (With Examples)

1. DDL – Data Definition Language Used to define and modify database structures. CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(50), age INT );

ALTER TABLE students ADD email VARCHAR(100);

1. DML – Data Manipulation Language Used to insert, update, and delete data.

INSERT INTO students VALUES (1, 'Alice', 20);

UPDATE students
SET age = 21
WHERE student_id = 1;

DELETE FROM students
WHERE student_id = 1;

1. DQL – Data Query Language Used to retrieve data from the database.

SELECT * FROM students;

SELECT name, age
FROM students
WHERE age > 18;

1. DCL – Data Control Language Used to control access and permissions.

GRANT SELECT ON students TO user1;

REVOKE SELECT ON students FROM user1;

It is also worth noting the Coding Order in SQL (Logical Query Processing Order).
Although SQL is written in one order, it is executed logically in the following sequence:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT

Example:
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) > 3
ORDER BY department;