A database is an organized collection of data that can be easily accessed, managed, and updated.
It allows users to store data in a structured manner, enabling efficient retrieval
and manipulation using a database management system (DBMS).
Common examples of databases include MySQL, PostgreSQL, Oracle, and MongoDB.
Key Components of a Database ->
Tables: A collection of related data, structured in rows (records) and columns (fields).
Rows: Represent individual records.
Columns: Represent the attributes of the data.
Primary Key: A unique identifier for each record in a table.
Relationships: Connections between tables, often created using keys.
Database Diagram ->
Here’s a simple database schema example with two tables: Users and Orders.
+---------------------+ +---------------------+
| Users | | Orders |
+---------------------+ +---------------------+
| UserID (Primary Key)|◄──┐ ┌──►| OrderID(Primary Key)|
| Name | │ │ | UserID (Foreign Key)|
| Email | │ │ | OrderDate |
+---------------------+ │ │ +---------------------+
│ │
One-to-Many Relationship
Database Workflow ->
Design Tables: Identify entities and relationships.
Create Tables: Write SQL to define the structure.
Insert Data: Populate the tables with records.
Query Data: Use SQL to retrieve and manipulate the data.
What is SQL?
SQL (Structured Query Language) is a standard programming language used for
managing and interacting with relational databases.
It allows users to perform operations like creating, retrieving,
updating, and deleting data within a database.
Here’s a list of common SQL queries categorized by functionality.
Types of SQL Statements ->
Data Definition Language (DDL): CREATE, ALTER, DROP Define and modify database structure.
Data Manipulation Language (DML): INSERT, UPDATE, DELETE Insert, modify, or delete data in the database.
Data Query Language (DQL): SELECT Retrieve data from a database.
Data Control Language (DCL): GRANT, REVOKE Manage user access and permissions.
Transaction Control Language (TCL): COMMIT, ROLLBACK, SAVEPOINT Manage database transactions.
SQL Workflow
Create Tables: Define the structure of your database.
Insert Data: Populate the database with records.
Retrieve Data: Use queries to fetch specific data.
Update Data: Modify existing data in the database.
Delete Data: Remove unnecessary records.
SQL Clause Execution Order
written SQL query follows this sequence:
SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
the execution happens logically in this order:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
SQL Basics Covered:
Creating and Managing Tables: CREATE, ALTER, DROP, TRUNCATE.
Basic Queries: SELECT, WHERE, ORDER BY, GROUP BY, HAVING.
Data Manipulation: INSERT, UPDATE, DELETE.
Clauses: DISTINCT, LIMIT, TOP.
Joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS.
Aggregate Functions: COUNT, SUM, AVG, MIN, MAX.
Subqueries: Scalar, Correlated, Nested.
Common Table Expressions (CTEs): Using WITH for temporary result sets.
Constraints: Primary Key, Foreign Key, Unique, Check, Default.
Transactions: COMMIT, ROLLBACK, SAVEPOINT.
These are useful for both beginners and professionals:
1. Basic Queries
Select All Columns
SELECT * FROM table_name;
Select Specific Columns
SELECT column1, column2 FROM table_name;
Rename Column in Results
SELECT column_name AS alias_name FROM table_name;
2. Filtering Data
Filter Rows (WHERE)
SELECT * FROM table_name WHERE column_name = 'value';
Filter with Multiple Conditions
SELECT * FROM table_name
WHERE column1 = 'value' AND column2 > 10;
Using OR Condition
SELECT * FROM table_name
WHERE column1 = 'value1' OR column2 = 'value2';
Filter with LIKE (Pattern Matching)
SELECT * FROM table_name
WHERE column_name LIKE '%pattern%'; -- Contains 'pattern'
Filter with IN
SELECT * FROM table_name
WHERE column_name IN ('value1', 'value2', 'value3');
Filter with BETWEEN
SELECT * FROM table_name
WHERE column_name BETWEEN 10 AND 20;
3. Sorting
Sort in Ascending Order (Default)
SELECT * FROM table_name ORDER BY column_name;
Sort in Descending Order
SELECT * FROM table_name ORDER BY column_name DESC;
4. Aggregation
Count Rows
SELECT COUNT(*) FROM table_name;
Find Maximum and Minimum
SELECT MAX(column_name), MIN(column_name) FROM table_name;
Calculate Average
SELECT AVG(column_name) FROM table_name;
Calculate Sum
SELECT SUM(column_name) FROM table_name;
Group By and Aggregate
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
Filter Grouped Data (HAVING)
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 5;
5. Joining Tables
Inner Join
SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b
ON a.common_column = b.common_column;
+---------+ +---------+
| Table A | | Table B |
|---------| |---------|
| ID | | ID |
| 1 |◄───►| 1 |
| 2 |◄───►| 2 |
| 3 | | 4 |
+---------+ +---------+
Result:
+---------+
| ID |
|---------|
| 1 |
| 2 |
+---------+
Left Join
SELECT a.column1, b.column2
FROM table1 a
LEFT JOIN table2 b
ON a.common_column = b.common_column;
+---------+ +---------+
| Table A | | Table B |
|---------| |---------|
| ID | | ID |
| 1 |◄───►| 1 |
| 2 |◄───►| 2 |
| 3 | | 4 |
+---------+ +---------+
Result:
+---------+---------+
| ID | ID |
|---------|---------|
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
+---------+---------+
Right Join
SELECT a.column1, b.column2
FROM table1 a
RIGHT JOIN table2 b
ON a.common_column = b.common_column;
+---------+ +---------+
| Table A | | Table B |
|---------| |---------|
| ID | | ID |
| 1 |◄───►| 1 |
| 2 |◄───►| 2 |
| 3 | | 4 |
+---------+ +---------+
Result:
+---------+---------+
| ID | ID |
|---------|---------|
| 1 | 1 |
| 2 | 2 |
| NULL | 4 |
+---------+---------+
Full Outer Join
SELECT a.column1, b.column2
FROM table1 a
FULL OUTER JOIN table2 b
ON a.common_column = b.common_column;
+---------+ +---------+
| Table A | | Table B |
|---------| |---------|
| ID | | ID |
| 1 |◄───►| 1 |
| 2 |◄───►| 2 |
| 3 | | 4 |
+---------+ +---------+
Result:
+---------+---------+
| ID | ID |
|---------|---------|
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
| NULL | 4 |
+---------+---------+
6. Modifying Data
Insert Data
INSERT INTO table_name (column1, column2)
VALUES ('value1', 'value2');
Update Data
UPDATE table_name
SET column1 = 'new_value'
WHERE column2 = 'condition';
Delete Data
DELETE FROM table_name
WHERE column_name = 'value';
7. Creating Tables
Create Table
CREATE TABLE table_name (
id INT PRIMARY KEY,
column1 VARCHAR(255),
column2 INT
);
Drop Table
DROP TABLE table_name;
8. Advanced Queries
Subquery
SELECT column_name
FROM table_name
WHERE column_name = (SELECT MAX(column_name) FROM table_name);
Union
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;
Exists
SELECT column_name
FROM table_name
WHERE EXISTS (SELECT 1 FROM another_table WHERE condition);
Case Statement
SELECT column_name,
CASE
WHEN condition THEN 'value1'
ELSE 'value2'
END AS alias_name
FROM table_name;
9. Index and Performance
Create Index
CREATE INDEX index_name ON table_name (column_name);
Drop Index
DROP INDEX index_name;
Here’s a complete set of SQL queries with examples
1. Creating Tables
-- Create a table for Employees
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10, 2),
JoiningDate DATE
);
-- Create a table for Departments
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);
2. Inserting Data
-- Insert data into Employees
INSERT INTO Employees (EmployeeID, Name, Department, Salary, JoiningDate)
VALUES
(1, 'John Doe', 'HR', 50000, '2023-01-10'),
(2, 'Jane Smith', 'IT', 80000, '2022-09-15'),
(3, 'Alice Johnson', 'Finance', 60000, '2021-03-12'),
(4, 'Bob Brown', 'IT', 75000, '2020-06-01');
-- Insert data into Departments
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance');
3. Selecting Data
-- Select all columns
SELECT * FROM Employees;
-- Select specific columns
SELECT Name, Department FROM Employees;
-- Rename column in results
SELECT Name AS EmployeeName, Salary AS MonthlySalary FROM Employees;
4. Filtering Data
-- Filter rows using WHERE
SELECT * FROM Employees WHERE Department = 'IT';
-- Filter rows with multiple conditions
SELECT * FROM Employees WHERE Department = 'IT' AND Salary > 70000;
-- Filter using OR
SELECT * FROM Employees WHERE Department = 'HR' OR Department = 'Finance';
-- Filter using LIKE (pattern matching)
SELECT * FROM Employees WHERE Name LIKE 'J%'; -- Names starting with 'J'
-- Filter using IN
SELECT * FROM Employees WHERE Department IN ('HR', 'Finance');
-- Filter using BETWEEN
SELECT * FROM Employees WHERE Salary BETWEEN 60000 AND 80000;
5. Sorting
-- Sort in ascending order
SELECT * FROM Employees ORDER BY Salary;
-- Sort in descending order
SELECT * FROM Employees ORDER BY Salary DESC;
6. Aggregation
-- Count rows
SELECT COUNT(*) AS TotalEmployees FROM Employees;
-- Maximum and Minimum salary
SELECT MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary FROM Employees;
-- Average salary
SELECT AVG(Salary) AS AvgSalary FROM Employees;
-- Total salary
SELECT SUM(Salary) AS TotalSalary FROM Employees;
-- Group by department
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
-- Filter grouped data using HAVING
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 60000;
7. Joining Tables
-- Inner join Employees and Departments
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.Department = d.DepartmentName;
-- Left join
SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.Department = d.DepartmentName;
-- Right join
SELECT e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d
ON e.Department = d.DepartmentName;
-- Full outer join (may not work in all SQL dialects)
SELECT e.Name, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d
ON e.Department = d.DepartmentName;
8. Modifying Data
-- Update salary for an employee
UPDATE Employees
SET Salary = 85000
WHERE EmployeeID = 2;
-- Delete an employee record
DELETE FROM Employees
WHERE EmployeeID = 4;
9. Advanced Queries
-- Subquery to find employees with salary above the average
SELECT * FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
-- Union to combine results from multiple tables
SELECT Name AS EntityName FROM Employees
UNION
SELECT DepartmentName AS EntityName FROM Departments;
-- Exists to check if a condition holds true
SELECT Name
FROM Employees
WHERE EXISTS (
SELECT 1 FROM Departments
WHERE DepartmentName = 'IT'
);
-- Case statement to create a calculated column
SELECT Name, Salary,
CASE
WHEN Salary > 70000 THEN 'High'
WHEN Salary BETWEEN 50000 AND 70000 THEN 'Medium'
ELSE 'Low'
END AS SalaryCategory
FROM Employees;
10. Index and Performance
-- Create an index on the Department column
CREATE INDEX idx_department ON Employees(Department);
-- Drop the index
DROP INDEX idx_department;
11. Dropping Tables
-- Drop Employees table
DROP TABLE Employees;
-- Drop Departments table
DROP TABLE Departments;
