Day 8: Advancing with SQL – Filtering, Sorting, Aggregation, Grouping, and Subqueries

Today marked a significant step forward in my structured SQL learning journey. Building on foundational concepts, I focused on writing queries that simulate real-world use cases: slicing data, computing business metrics, and summarizing information effectively using SQL's core capabilities.

This session involved a realistic dataset representing employee records, allowing hands-on experience with essential operations like WHERE, ORDER BY, GROUP BY, and HAVING, along with practical exposure to subqueries.

---

Database Setup

I created a new database and an employee table with fields designed to support business-level queries involving personnel, departments, locations, and compensation.

CREATE DATABASE IF NOT EXISTS demo1;
USE demo1;

CREATE TABLE employee (
    empid INT PRIMARY KEY,
    fname VARCHAR(20) NOT NULL,
    lname VARCHAR(20),
    gender CHAR(1),
    city VARCHAR(100) DEFAULT 'Jaipur',
    salary INT,
    department VARCHAR(50)
);

---

Sample Dataset

The dataset consists of 50+ realistic employee records across departments such as IT, Finance, HR, Marketing, Sales, Support, and Operations. Each row includes location, gender, and salary attributes.

Sample Records:

---

Querying the Data

1. Basic Retrieval

SELECT * FROM employee;
SELECT fname, lname, salary FROM employee;

2. Using DISTINCT

SELECT DISTINCT department FROM employee;

Output (departments): IT, HR, Finance, Sales, Marketing, Operations, Support

---

Filtering Data

Filter by department and gender:

SELECT * FROM employee
WHERE department = 'IT' AND gender = 'F';

Filter using BETWEEN, IN, LIKE

SELECT * FROM employee WHERE salary BETWEEN 50000 AND 60000;
SELECT * FROM employee WHERE city IN ('Delhi', 'Pune');
SELECT * FROM employee WHERE fname LIKE 'A%';

---

Sorting Data

SELECT * FROM employee
ORDER BY salary DESC
LIMIT 5;

Top 5 Highest Salaries:

---

Aggregations

SELECT COUNT(*) FROM employee; -- Total employees
SELECT SUM(salary) FROM employee WHERE department = 'Finance';
SELECT AVG(salary) FROM employee;
SELECT MIN(salary), MAX(salary) FROM employee;

Key Metrics:

• Total Employees: 50

• Average Salary: 57920

• Finance Total Salary: 812000

• Min-Max Salary Range: 45000 – 70000

---

Grouped Analytics

Average Salary by Department

SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
ORDER BY avg_salary DESC;

Employee Distribution by City

SELECT city, COUNT(*) AS employee_count
FROM employee
GROUP BY city
ORDER BY employee_count DESC
LIMIT 3;

Top 3 Cities by Employee Count:

---

Filtering Groups with HAVING

SELECT department, AVG(salary) AS avg_female_salary
FROM employee
WHERE gender = 'F'
GROUP BY department
HAVING avg_female_salary > 40000
ORDER BY avg_female_salary DESC;

Departments with Avg Female Salary > 40K:

---

Subqueries: Dynamic Filtering

Employees earning above average salary

SELECT * FROM employee
WHERE salary > (
    SELECT AVG(salary) FROM employee
);

Filtered Results: Employees earning more than ~₹57,920

---

Reflections & Key Takeaways

• Learned to efficiently filter, sort, and limit data using WHERE, ORDER BY, and LIMIT.

• Explored real-world analytics with GROUP BY, HAVING, and aggregation functions like AVG(), SUM(), and COUNT().

• Practiced subqueries for advanced comparisons and dynamic filters.

• Reinforced the importance of readable query structure and use of aliases for clarity.

These capabilities directly translate to tasks in DevOps (e.g., log querying, resource reporting), cloud operations (e.g., RDS, Athena analytics), and data-driven backend systems.