Learning of SQL Day 11

 

Day 11: Basic SQL Functions

Introduction:

• SQL functions allow you to perform calculations on data. They include aggregate functions, string functions, date functions, and more.

Key Concepts:

• Aggregate Functions: Perform calculations on a set of values and return a single value.

• String Functions: Manipulate string data.

• Date Functions: Manipulate date and time data.

SQL Commands and Examples:

1. Aggregate Functions:

   • 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

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

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

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

-- Find the minimum and maximum salary in the Employees table
SELECT MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary FROM Employees;

2. String Functions:

   • LENGTH(): Returns the length of a string.

   • LOWER(): Converts a string to lower-case.

   • UPPER(): Converts a string to upper-case.

   • CONCAT(): Concatenates two or more strings.

sql

-- Get the length of the CourseName string
SELECT CourseName, LENGTH(CourseName) AS NameLength FROM Courses;

-- Convert the FirstName to lower-case and upper-case
SELECT FirstName, LOWER(FirstName) AS LowerCaseName, UPPER(FirstName) AS UpperCaseName FROM Students;

-- Concatenate FirstName and LastName to create FullName
SELECT FirstName, LastName, CONCAT(FirstName, ' ', LastName) AS FullName FROM Students;

3. Date Functions:

   • NOW(): Returns the current date and time.

   • CURDATE(): Returns the current date.

   • CURTIME(): Returns the current time.

   • YEAR(), MONTH(), DAY(): Extracts the year, month, and day from a date.

sql

-- Get the current date and time
SELECT NOW() AS CurrentDateTime;

-- Get the current date and time separately
SELECT CURDATE() AS CurrentDate, CURTIME() AS CurrentTime;

-- Extract year, month, and day from the EventDate
SELECT EventDate, YEAR(EventDate) AS EventYear, MONTH(EventDate) AS EventMonth, DAY(EventDate) AS EventDay FROM DateTimeExample;

Practice Exercise:

1. Use the Courses and Students tables.

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

3. Write a query to find 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 convert the CourseName to upper-case.

6. Write a query to get the current date and time.

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;

-- Convert CourseName to upper-case
SELECT CourseName, UPPER(CourseName) AS UpperCaseName FROM Courses;

-- Get the current date and time
SELECT NOW() AS CurrentDateTime;

Mastering SQL functions will enhance your ability to perform complex data manipulations.