SQL Basics
Beginner Level
1. SELECT
Retrieves data from a database.
SELECT first_name, last_name FROM employees;
Retrieves the first_name and last_name columns from the employees table.
2. WHERE
Filters records based on a specified condition.
SELECT * FROM employees WHERE department = 'Sales';
Retrieves all columns from the employees table where the department is ‘Sales’.
3. ORDER BY
Sorts the result set in ascending or descending order.
SELECT first_name, last_name FROM employees ORDER BY last_name ASC;
Retrieves first_name and last_name columns sorted by last_name in ascending order.
Sample Data (Beginner Level)
CREATE TABLE employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000),
(2, 'Jane', 'Smith', 'Engineering', 75000),
(3, 'Alice', 'Johnson', 'HR', 50000),
(4, 'Bob', 'Brown', 'Sales', 65000);
Intermediate Level
1. JOIN
Combines rows from two or more tables based on a related column.
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
Retrieves the first and last names from the employees table and the department name from the departments table where the department_id matches.
2. GROUP BY
Groups rows that have the same values into summary rows.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
Retrieves the number of employees in each department.
3. HAVING
Filters groups based on a specified condition.
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;
Retrieves departments with more than one employee.
Sample Data (Intermediate Level)
CREATE TABLE departments (
department_id INT,
department_name VARCHAR(50)
);
INSERT INTO departments (department_id, department_name) VALUES
(1, 'Sales'),
(2, 'Engineering'),
(3, 'HR');
ALTER TABLE employees ADD department_id INT;
UPDATE employees SET department_id = 1 WHERE department = 'Sales';
UPDATE employees SET department_id = 2 WHERE department = 'Engineering';
UPDATE employees SET department_id = 3 WHERE department = 'HR';
Advanced Level
1. Subqueries
A query within another query.
SELECT first_name, last_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Retrieves the first and last names of employees who earn more than the average salary.
2. Window Functions
Perform calculations across a set of table rows related to the current row.
SELECT first_name, last_name, salary,
AVG(salary) OVER (PARTITION BY department) AS avg_department_salary
FROM employees;
Retrieves the first and last names, salary, and the average salary of their department.
3. CTE (Common Table Expressions)
A temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.
WITH DepartmentSalary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT e.first_name, e.last_name, ds.avg_salary
FROM employees e
JOIN DepartmentSalary ds ON e.department = ds.department;
Retrieves first and last names along with the average salary of their department.
Sample Data (Advanced Level)
CREATE TABLE salaries (
employee_id INT,
salary DECIMAL(10, 2),
salary_date DATE
);
INSERT INTO salaries (employee_id, salary, salary_date) VALUES
(1, 60000, '2023-01-01'),
(2, 75000, '2023-01-01'),
(3, 50000, '2023-01-01'),
(4, 65000, '2023-01-01');
Fintech Examples
1. Calculating Interest
Calculate the interest for each customer account.
SELECT account_id, balance, balance * interest_rate AS interest
FROM accounts;
Retrieves the account_id, balance, and calculated interest for each account.
2. Risk Assessment
Calculate the risk score based on account activity.
SELECT account_id,
SUM(transaction_amount) AS total_transactions,
CASE
WHEN SUM(transaction_amount) > 100000 THEN 'High Risk'
WHEN SUM(transaction_amount) BETWEEN 50000 AND 100000 THEN 'Medium Risk'
ELSE 'Low Risk'
END AS risk_level
FROM transactions
GROUP BY account_id;
Retrieves the account_id, total transactions, and assigns a risk level based on the total transaction amount.
3. Loan Eligibility
Determine loan eligibility based on credit score and income.
SELECT customer_id, income, credit_score,
CASE
WHEN credit_score >= 700 AND income >= 50000 THEN 'Eligible'
ELSE 'Not Eligible'
END AS loan_status
FROM customers;
Retrieves the customer_id, income, credit_score, and determines loan eligibility.
Sample Data (Fintech)
CREATE TABLE accounts (
account_id INT,
balance DECIMAL(10, 2),
interest_rate DECIMAL(5, 4)
);
INSERT INTO accounts (account_id, balance, interest_rate) VALUES
(1, 10000, 0.03),
(2, 50000, 0.02),
(3, 75000, 0.025);
CREATE TABLE transactions (
transaction_id INT,
account_id INT,
transaction_amount DECIMAL(10, 2),
transaction_date DATE
);
INSERT INTO transactions (transaction_id, account_id, transaction_amount, transaction_date) VALUES
(1, 1, 2000, '2023-01-01'),
(2, 2, 30000, '2023-01-02'),
(3, 3, 50000, '2023-01-03');
CREATE TABLE customers (
customer_id INT,
income DECIMAL(10, 2),
credit_score INT
);
INSERT INTO customers (customer_id, income, credit_score) VALUES
(1, 60000, 720),
(2, 45000, 680),
(3, 80000, 750);