Learning of SQL Day 15

 

Day 15: Aggregate Functions

Introduction:

• Aggregate functions perform calculations on a set of values and return a single value. They are useful for summarizing data.

Key Concepts:

• COUNT(): Returns the number of rows.

• SUM(): Returns the total sum of a numeric column.

• AVG(): Returns the average value of a numeric column.

• MIN(): Returns the smallest value in a set.

• MAX(): Returns the largest value in a set.

SQL Commands and Examples:

1. COUNT() Function:

   • Count the number of rows in a table.

sql

-- Count the number of students
SELECT COUNT(*) AS TotalStudents FROM Students;

2. SUM() Function:

   • Calculate the total sum of a numeric column.

sql

-- Calculate the total credits of all courses
SELECT SUM(Credits) AS TotalCredits FROM Courses;

3. AVG() Function:

   • Calculate the average value of a numeric column.

sql

-- Calculate the average age of students
SELECT AVG(Age) AS AverageAge FROM Students;

4. MIN() Function:

   • Find the smallest value in a set.

sql

-- Find the minimum salary in the Employees table
SELECT MIN(Salary) AS MinSalary FROM Employees;

5. MAX() Function:

   • Find the largest value in a set.

sql

-- Find the maximum salary in the Employees table
SELECT MAX(Salary) AS MaxSalary FROM Employees;

Practice Exercise:

1. Use the Courses and Students tables.

2. Write a query to count the number of courses.

3. Write a query to calculate the total and average credits of all courses.

4. Write a query to find the maximum and minimum age of students.

5. Write a query to find the maximum salary in the Employees table.

6. Write a query to count the number of employees in each department.

sql

-- Count the number of courses
SELECT COUNT(*) AS TotalCourses FROM Courses;

-- Calculate the total and average credits of all courses
SELECT SUM(Credits) AS TotalCredits, AVG(Credits) AS AverageCredits FROM Courses;

-- Find the maximum and minimum age of students
SELECT MAX(Age) AS MaxAge, MIN(Age) AS MinAge FROM Students;

-- Find the maximum salary in the Employees table
SELECT MAX(Salary) AS MaxSalary FROM Employees;

-- Count the number of employees in each department
SELECT Department, COUNT(*) AS TotalEmployees FROM Employees GROUP BY Department;

Understanding and utilizing aggregate functions allows you to summarize and analyze your data effectively.