SQL Commands Demonstrative Note
✅ 1. SELECT Command
Purpose:
The SELECT statement is used to retrieve data from one or more
tables.
Syntax:
SELECT column1, column2, ... FROM table_name WHERE condition;
Examples:
-- Example 1: Retrieve all columns
SELECT * FROM Students;
-- Example 2: Retrieve specific columns
SELECT name, age FROM Students;
-- Example 3: Retrieve rows with a condition
SELECT name FROM Students WHERE age > 18;
-- Example 4: Retrieve unique values
SELECT DISTINCT course FROM Students;
-- Example 5: Use alias
SELECT name AS StudentName FROM Students;
✅ 2. GROUP BY Clause
Purpose:
GROUP BY groups rows that have the same values into summary
rows.
Syntax:
SELECT column, aggregate_function(column) FROM table_name
GROUP BY column;
Examples:
-- Example 1: Count students in each course
SELECT course, COUNT(*) FROM Students GROUP BY course;
-- Example 2: Average marks per course
SELECT course, AVG(marks) FROM Students GROUP BY course;
-- Example 3: Maximum marks per course
SELECT course, MAX(marks) FROM Students GROUP BY course;
-- Example 4: Minimum age per course
SELECT course, MIN(age) FROM Students GROUP BY course;
-- Example 5: Total marks per course
SELECT course, SUM(marks) FROM Students GROUP BY course;
✅ 3. Aggregate Functions Using GROUP BY, ORDER BY, LIKE, String
& Date Functions, Joins, Nested Query
🎯 Aggregate Functions (with GROUP BY & ORDER BY)
Examples:
-- Example 1: Total salary by department, highest first
SELECT dept, SUM(salary) FROM Employees GROUP BY dept ORDER
BY SUM(salary) DESC;
-- Example 2: Count employees by job title
SELECT job_title, COUNT(*) FROM Employees GROUP BY job_title;
-- Example 3: Average salary by department
SELECT dept, AVG(salary) FROM Employees GROUP BY dept;
-- Example 4: Maximum salary in each department
SELECT dept, MAX(salary) FROM Employees GROUP BY dept;
-- Example 5: Minimum salary in each department, ascending
SELECT dept, MIN(salary) FROM Employees GROUP BY dept ORDER
BY MIN(salary);
🎯 LIKE Operator
Purpose: Pattern matching.
Examples:
-- Example 1: Names starting with 'A'
SELECT * FROM Employees WHERE name LIKE 'A%';
-- Example 2: Names ending with 'n'
SELECT * FROM Employees WHERE name LIKE '%n';
-- Example 3: Names containing 'oh'
SELECT * FROM Employees WHERE name LIKE '%oh%';
-- Example 4: Names with second letter 'o'
SELECT * FROM Employees WHERE name LIKE '_o%';
-- Example 5: Names with exactly 5 letters
SELECT * FROM Employees WHERE name LIKE '_____';
🎯 String Functions
Examples:
-- Example 1: UPPER
SELECT UPPER(name) FROM Employees;
-- Example 2: LOWER
SELECT LOWER(name) FROM Employees;
-- Example 3: LENGTH
SELECT name, LENGTH(name) FROM Employees;
-- Example 4: CONCAT
SELECT CONCAT(name, ' works in ', dept) FROM Employees;
-- Example 5: SUBSTRING
SELECT SUBSTRING(name, 1, 3) FROM Employees;
🎯 Date Functions
Examples:
-- Example 1: Get current date
SELECT CURRENT_DATE;
-- Example 2: Extract year from hire_date
SELECT name, EXTRACT(YEAR FROM hire_date) FROM Employees;
-- Example 3: Extract month
SELECT name, EXTRACT(MONTH FROM hire_date) FROM Employees;
-- Example 4: Add 30 days
SELECT name, hire_date + INTERVAL '30 days' FROM Employees;
-- Example 5: Format date
SELECT TO_CHAR(hire_date, 'DD-MM-YYYY') FROM Employees;
🎯 Joining Two Tables
Example Tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
Examples:
-- Example 1: Inner Join
SELECT E.name, D.dept_name
FROM Employees E
JOIN Departments D ON E.dept_id = D.dept_id;
-- Example 2: Left Join
SELECT E.name, D.dept_name
FROM Employees E
LEFT JOIN Departments D ON E.dept_id = D.dept_id;
-- Example 3: Right Join
SELECT E.name, D.dept_name
FROM Employees E
RIGHT JOIN Departments D ON E.dept_id = D.dept_id;
-- Example 4: Join with WHERE
SELECT E.name, D.dept_name
FROM Employees E, Departments D
WHERE E.dept_id = D.dept_id;
-- Example 5: Self Join (Manager example)
SELECT E.name AS Employee, M.name AS Manager
FROM Employees E
JOIN Employees M ON E.manager_id = M.emp_id;
🎯 Nested Queries
Examples:
-- Example 1: Employees earning more than average salary
SELECT name FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
-- Example 2: Departments with more than 5 employees
SELECT dept_id FROM Departments
WHERE dept_id IN
(SELECT dept_id FROM Employees GROUP BY dept_id HAVING
COUNT(*) > 5);
-- Example 3: Get highest salary
SELECT MAX(salary) FROM Employees;
-- Example 4: Get employees with highest salary
SELECT name FROM Employees
WHERE salary = (SELECT MAX(salary) FROM Employees);
-- Example 5: Get employees hired after 'John'
SELECT name FROM Employees
WHERE hire_date > (SELECT hire_date FROM Employees WHERE
name = 'John');
