SQL (Structured Query Language): Guide from Basic to Real time Queries.
- Sivaranjani Subbarayan
- Jun 4
- 4 min read
SQL stands for Structured Query Language. It is the standard language used to communicate with databases. Think of a database as a digital storage system where information is organized into tables.
Few are some of the examples that SQL can helps us with:
Retrieve data
Insert new data
Update existing data
Delete unwanted data
Analyze information
Database:
A database is an organized collection of data stored electronically. Instead of keeping data in spreadsheets or paper files, databases provide a structured way to handle large amounts of information.
Relational database:
A relational database stores data in multiple tables that can be connected through relationships. Tables are linked using keys (Primary and Foreign Key). This structure makes it easier to maintain accurate and consistent data. it also helps to reduce duplication of data. Table Stores related data ( e.g, Customers table). Row represents a single record or entry (e.g, one customer). Column represents a specific attribute of the data (e.g, Name, Email, Phone Number).
Popular SQL databases:
Below are some of the popular SQL database,
MySQL
PostgreSQL
SQL Server
SQLite
SQL Basic Queries:
Retrieve Data:
SELECT – Retrieve data from one or more tables.
DISTINCT – Return unique values and remove duplicates.
Examples:
SELECT Name
FROM Employees;
SELECT DISTINCT Salary
FROM Employees;
Filtering Data:
WHERE – Filter records based on specific conditions.
LIKE – Search for data matching a pattern.
IN – Match values from a specified list.
BETWEEN – Filter values within a specified range.
IS NULL – Find records with missing (NULL) values.
IS NOT NULL – Find records that contain values.
AND / OR / NOT – Combine multiple conditions
Comparison operators (=, !=, >, <, >=, <=) – Used for condition checks
Examples:
SELECT *
FROM Employees
WHERE City IS NULL;
SELECT *
FROM Customers
WHERE Age > 18 AND Country = 'USA';
SELECT *
FROM Customers
WHERE City = 'New York';
Data Modification Commands:
INSERT INTO – Add new records to a table.
UPDATE – Modify existing records in a table.
DELETE – Remove records from a table.
Examples:
INSERT INTO Customers(name, salary)
VALUES ('John', 50000);
UPDATE Customers
SET Email = 'newemail@example.com'
WHERE CustomerID = 1;
DELETE FROM Customers
WHERE CustomerID = 1;
Sorting and Limiting Results:
ORDER BY – Sort query results in ascending or descending order.
LIMIT / TOP – Restrict the number of rows returned.
Examples:
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;
Aggregate Functions:
COUNT – Count the number of records.
AVG – Calculate the average value.
MAX – Return the highest value.
MIN – Return the lowest value.
SUM – Calculate the total of numeric values.
Examples:
SELECT AVG(salary)
FROM employees;
SELECT MAX(salary)
FROM employees;
SELECT MIN(salary)
FROM employees;
SELECT SUM(salary)
FROM employees;
GROUP BY and HAVING:
GROUP BY – Group rows with similar values for aggregation.
HAVING – Filter grouped data after aggregation.
Examples:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
Types of Joins:
JOIN – Combine data from multiple tables based on related columns.
INNER JOIN – only matching records
LEFT JOIN – all left table + matching right
RIGHT JOIN – all right table + matching left
FULL JOIN – all records from both tables
Examples:
SELECT Employees.Name, Departments.DeptName
FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.DeptID;
SELECT Employees.Name, Departments.DeptName
FROM Employees
LEFT JOIN Departments
ON Employees.DeptID = Departments.DeptID;
SELECT Employees.Name, Departments.DeptName
FROM Employees
RIGHT JOIN Departments
ON Employees.DeptID = Departments.DeptID;
SELECT Employees.Name, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DeptID = Departments.DeptID;
Combining Results:
UNION – Combine results from multiple SELECT statements, removes duplicate.
UNION ALL– Combine results from multiple SELECT statements, keeps duplicate.
Examples:
SELECT Name FROM Employees
UNION
SELECT Name FROM Customers;
Aliasing:
AS – Create temporary aliases for columns or tables.
Examples:
SELECT Name AS EmployeeName
FROM Employees;
SELECT e.Name, e.Salary
FROM Employees AS e;
SELECT e.Name, d.DeptName
FROM Employees AS e
JOIN Departments AS d
ON e.DeptID = d.DeptID;
Data Definition Commands:
CREATE TABLE – Create a new table in the database.
ALTER TABLE – Modify the structure of an existing table.
DROP TABLE – Delete a table and its data permanently.
TRUNCATE TABLE – Remove all rows while keeping the table structure.
Examples:
CREATE TABLE Employees (
ID INT,
Name VARCHAR(50),
Salary INT
);
ALTER TABLE Employees
ADD Department VARCHAR(50);
ALTER TABLE Employees
MODIFY Salary BIGINT;
DROP TABLE Employees;
Constraints (Rules on Data):
PRIMARY KEY – Uniquely identify each row in a table.
FOREIGN KEY – Create relationships between tables.
Examples:
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(50)
);
CREATE TABLE Orders (
OrderID INT,
EmployeeID INT,
FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);
Performance:
INDEX – Improve query performance by speeding up data retrieval.
Examples:
CREATE INDEX idx_name
ON Employees(Name);
Subqueries:
A subquery is a query written inside another query.It is also called a nested query. The inner query runs first, and its result is used by the outer query.
Exmples:
SELECT Name
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
SELECT Name
FROM Employees
WHERE DepartmentID IN (
SELECT DepartmentID
FROM Departments
WHERE Location = 'New York'
);
Installing PostgreSQL:
To start working with SQL in a real environment, PostgreSQL is one of the most popular open-source database systems.
Step 1: Download PostgreSQL from the official website
Step 2: once the file is downloaded, follow the setup wizard and during the installation setup the password and default port (5432)
Step 3: once installed, open the command prompt and verify the installation
Step 4: Open pgAdmin and connect using the password.
Step 5: Create your own database in the pgAdmin and run SQL queries.
Overall, SQL helps in retrieving, filtering, updating, and managing data efficiently.
Thank you for reading my blogs. Hope you had a great learning. All the best for your learning journey, and happy querying!
