Pravanjan Kuanr | Portfolio

Welcome

Namaste and Welcome! Let’s learn SQL the easy way with clear steps and real examples.

SQL & NoSQL

SQL (Structured Query Language) databases are like spreadsheets. Imagine an Excel sheet with rows and columns — each column has a fixed type of data (like Name, Age, Salary), and every row must follow that structure. SQL databases are powerful for handling structured data, supporting joins between tables, and ensuring accuracy through transactions. Examples: MySQL, PostgreSQL, and Oracle.

NoSQL (Not Only SQL) databases are like a filing cabinet. Each document can have different fields, making them flexible for unstructured or changing data. Instead of strict rows and columns, they adapt to the data shape. Examples: MongoDB, Cassandra, Redis — often used in real-time, high-speed, and scalable applications.

Why is it important?

SQL is the standard language for interacting with relational databases, which power most applications we use daily. From banking and e-commerce to healthcare and social media, SQL ensures accuracy, consistency, and security. It’s the foundation of modern data management and a must-have skill for anyone working with technology.

SQL Installation

Windows:
Download from: MySQL Community Downloads
During setup, set a root password.

Mac:
Run this command in your terminal or command prompt for download:

brew install mysql

Run this command in your terminal or command prompt for download:

brew services start mysql

Set a root password during secure installation.

mysql_secure_installation

Linux:

sudo apt install mysql-server
sudo mysql_secure_installation

Set a root password during secure installation.

Login to MySQL:

mysql -u root -p

Terminology

Database: A collection of related data, e.g., an online store database.

Table: Stores data in rows & columns, like a spreadsheet.

Row (Record): A single entry, e.g., one customer’s details.

Column (Field): Defines the type of data, e.g., Name, Age.

Primary Key: Unique identifier for each row, e.g., Customer_ID.

Foreign Key: Links two tables, e.g., Orders → Customers.

Schema: Structure or design of the database.

Query: Request to retrieve or modify data.

Constraint: Rules applied to data, e.g., NOT NULL, UNIQUE.

Index: Speeds up data retrieval, like a book index.

SQL Data Types

INT: Whole numbers (e.g., 25, -100).

DECIMAL / NUMERIC: Fixed precision numbers, e.g., DECIMAL(10,2).

FLOAT / DOUBLE: Approximate decimal values.

VARCHAR(n): Variable-length text (up to n chars).

CHAR(n): Fixed-length text.

TEXT: Long text (paragraphs).

DATE: Stores dates (YYYY-MM-DD).

TIME: Stores time (HH:MM:SS).

DATETIME / TIMESTAMP: Stores date & time together.

BOOLEAN: TRUE/FALSE (1 or 0).

Getting Started

Check version

mysql --version

Create Database

CREATE DATABASE my_database;

Use Database

USE my_database;

Create Table Example

CREATE TABLE Customers (
  Customer_ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Email VARCHAR(100),
  Age INT
);

Inserting Data into a Table

INSERT INTO Customers (Customer_ID, Name, Email, Age)VALUES
  (1, 'Rahul Sharma', 'rahul.sharma@example.com', 28),
  (2, 'Priya Singh', 'priya.singh@example.com', 32);

Categorial SQL Commands

Category	Purpose	Key Commands
DDL(Data Definition Language)	Structure	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML(Data Manipulation Language)	Data	INSERT, UPDATE, DELETE
DQL(Data Query Language)	Query Data	SELECT
DCL	Permissions	GRANT, REVOKE
TCL	Transactions	COMMIT, ROLLBACK, SAVEPOINT

CRUD Operation

What is CRUD Operation?
CRUD stands for Create, Read, Update, and Delete — the four basic actions used to work with data in a database.

CREATE:
Used to add new data.

INSERT INTO Customers (Name, Email, Age)
VALUES ('Rahul Sharma', 'rahul.sharma@example.com', 28);

READ:
Used to view or fetch data.

SELECT * FROM Customers;

UPDATE:
Used to change existing data.

UPDATE Customers
SET Age = 30
WHERE Name = 'Rahul Sharma';

DELETE:
Used to remove data.

DELETE FROM Customers
WHERE Name = 'Rahul Sharma';

Alter Table

Easy shortcut to remember common ALTER TABLE operations.

ADD COLUMN (Add)
Used to add a new column to a table.

ALTER TABLE product
ADD COLUMN product_type VARCHAR(20) NOT NULL;

DROP COLUMN (Drop)
Used to remove a column from a table.

ALTER TABLE employee
DROP COLUMN channel;

RENAME TABLE (Rename)
Used to change the table name.

ALTER TABLE emp
RENAME TO employee;

MODIFY COLUMN (Modify)
Used to change a column's data type or nullability.

ALTER TABLE employee
MODIFY channel VARCHAR(30) NULL;

CHANGE COLUMN NAME (Change)
Used to rename a column (often allows changing type at the same time).

ALTER TABLE employee
CHANGE channels channel VARCHAR(30) NOT NULL;

Note: SQL commands can vary between databases — for example, MySQL, PostgreSQL, and SQL Server may use different keywords

Filtering and Sorting

WHERE: Used to filter rows based on a specific condition.

SELECT * FROM Customers
WHERE Customer_ID = 1;

GROUP BY: Groups rows that have the same values in one or more columns.

SELECT Age, COUNT(*) AS Total_Customers
FROM Customers
GROUP BY Age;

HAVING: Filters groups created by GROUP BY (like WHERE, but for groups).

SELECT Age, COUNT(*) AS Total_Customers
FROM Customers
GROUP BY Age
HAVING COUNT(*) > 1;

ORDER BY: Sorts the result set. Default order is ascending (ASC).

SELECT * FROM Customers
ORDER BY Customer_ID DESC;

LIMIT: Restricts the number of rows returned by a query.

SELECT * FROM Customers
LIMIT 5;

Keywords

Alias: Gives a temporary name to a column or table, making query results easier to read.

SELECT COUNT(Name) AS Total_Customers
FROM Customers;

DISTINCT: Returns only unique values from a column.

SELECT DISTINCT Name
FROM Customers;

Operators in SQL

Operators: Used for filtering, comparing, and performing logical operations in queries.

1. Comparison Operators: Compare values in columns.

SELECT * FROM Customers
WHERE Age > 25;      -- Greater than
SELECT * FROM Customers
WHERE Age <= 30;     -- Less than or equal
SELECT * FROM Customers
WHERE Age = 28;         -- Equal
SELECT * FROM Customers
WHERE Age <> 28;   -- Not equal

2. Logical Operators: Combine multiple conditions.

SELECT * FROM Customers
WHERE Age > 25 AND Age < 35;   -- AND
SELECT * FROM Customers
WHERE City = 'Delhi' OR City = 'Mumbai';  -- OR
SELECT * FROM Customers
WHERE NOT City = 'Delhi';           -- NOT

3. Pattern Matching Operators: Search for values matching a pattern.

SELECT * FROM Customers
WHERE Name LIKE 'R%';      -- Names starting with 'R'
SELECT * FROM Customers
WHERE Name LIKE '%son';    -- Names ending with 'son'
SELECT * FROM Customers
WHERE Name LIKE '%an%';    -- Names containing 'an'

4. NULL Handling Operators: Check for NULL values.

SELECT * FROM Customers
WHERE Email IS NULL;       -- Rows with NULL Email
SELECT * FROM Customers
WHERE Email IS NOT NULL;   -- Rows with non-NULL Email

5. Other Operators:

SELECT * FROM Customers
WHERE Age BETWEEN 20 AND 30;      -- Values within a range
SELECT * FROM Customers
WHERE City IN ('Delhi','Mumbai'); -- Values in a list
SELECT * FROM Customers
WHERE Age NOT BETWEEN 20 AND 30;  -- Values outside a range
SELECT * FROM Customers
WHERE City NOT IN ('Delhi','Mumbai'); -- Values not in the list

BETWEEN: Filters values within a specific range (inclusive of both ends).

SELECT * FROM Orders
WHERE OrderAmount BETWEEN 100 AND 500;

IN: Filters rows that match any value in a given list.

SELECT * FROM Customers
WHERE City IN ('Delhi', 'Mumbai', 'Bangalore');

Joins in SQL

What is a Join?
A JOIN in SQL is used to combine data from two or more tables based on a related column between them. It helps you see connected information — like customers and their orders — in one view.

1️⃣ INNER JOIN

Returns only the records that have matching values in both tables.


SELECT Customers.Name, Orders.Order_ID
FROM Customers
INNER JOIN Orders ON Customers.Customer_ID = Orders.Customer_ID;

2️⃣ LEFT JOIN

Returns all records from the left table (Customers), and matching records from the right table (Orders). If there’s no match, it shows NULL.


SELECT Customers.Name, Orders.Order_ID
FROM Customers
LEFT JOIN Orders ON Customers.Customer_ID = Orders.Customer_ID;

3️⃣ RIGHT JOIN

Returns all records from the right table (Orders), and matching records from the left table (Customers).


SELECT Customers.Name, Orders.Order_ID
FROM Customers
RIGHT JOIN Orders ON Customers.Customer_ID = Orders.Customer_ID;

4️⃣ FULL JOIN

Combines all records from both tables. If there’s no match, missing data is filled with NULL.


SELECT Customers.Name, Orders.Order_ID
FROM Customers
FULL JOIN Orders ON Customers.Customer_ID = Orders.Customer_ID;

Illustration: SQL Joins — INNER, LEFT, RIGHT, and FULL

Aggregation Functions: Create a section for this
Perform calculations on multiple rows to return a single value.


SELECT COUNT(*) AS Total_Customers FROM Customers;
SELECT SUM(Age) AS Total_Age FROM Customers;
SELECT AVG(Age) AS Average_Age FROM Customers;
SELECT MIN(Age) AS Youngest FROM Customers;
SELECT MAX(Age) AS Oldest FROM Customers;

Important SQL Syntax / Queries

Store Current Timestamp: Automatically records the current date and time when a new row is added.


ALTER TABLE Employees 
add startdate datetime not null default current_timestamp;

Insert Data: Adds new rows to a table.


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

Select Data: Retrieves data from a table.


SELECT column1, column2
FROM table_name
WHERE condition;

Update Data: Modifies existing rows.


UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Delete Data: Removes rows from a table.


DELETE FROM table_name
WHERE condition;

Grant Permissions: Gives access to a user.


GRANT SELECT, INSERT ON table_name TO user_name;

Rollback / Commit: Transaction control commands.


BEGIN TRANSACTION;
-- SQL commands
COMMIT;   -- Save changes
ROLLBACK; -- Undo changes