Comprehensive Notes Database Query using SQL Class 12 – Aggregate Functions

In this article, we are going to discuss Database Query using SQL Class 12 or Database query using sql class 12. We have already discussed math functions and text functions. You can read this by following link:

MySQL Math and Text Functions

Database Query using SQL Class 12

In this section, we will discuss date functions, aggregate functions, and querying and manipulating data using group by, having, and order by.

MySQL Date Functions – Database Query using SQL Class 12

There are certain MySQL date functions, We will cover the following MySQL date functions as per the curriculum:

  1. now/sysdate
  2. curdate/current_date
  3. date
  4. month
  5. monthname
  6. year
  7. day
  8. dayname
  9. dayofmonth
  10. dayofweek
  11. dayofyear

now/sysdate

This function returns the current date and time in the output in ‘YYYY-MM-DD HH:MM:SS’ or ‘YYYYMMDDHHMMSS.uuuuuu’.

There is slight difference between these two functions that now() returns a constant time after execution. Sysdate() returns the exact time when the command is executed including seconds.

select now(), sleep(3), now();
Output:
2020-11-02 07:30:15 0 2020-11-02 07:30:15

select sysdate(), sleep(3), sysdate();
Output:
2020-11-02 07:30:15 0 2020-11-02 07:30:18

As you can observe the difference in the output of above statements. The output of now() functions returns the similar date and time after executing sleep where as sysdate() function changes the output and returns 3 seconds more in the time.

curdate()/current_date()/current_date

This function is used to display date value in ‘YYYY-MM-DD’ or ‘YYYYMMDD’ format.

select curdate();
Output:
2020-11-02

select current_date();
Output:
2020-11-02

select current_date;
Output:
2020-11-02

You can use different operators with these functions to manipulate data.

date()

It will display the date from the selected dates. The format will be ‘YYYY-MM-DD’ or ‘YYYY-MM-DD’.

select date(now());
Output:
2020-11-02

month()

It will display the month number from the specified date.

select month(now());
Output:
11

select month('2018-05-20')
Output:
5

monthname()

This function will return the name of month from the specified date.

select monthname(curdate());
Output:
November

select monthname('2018-05-20')
Output:
May

year()

This function returns year from the specified date.

select year(now());
Output:
2020

day()

This function will display day from the specified date. Supposed the date is ‘2020-08-17’ then the output will be 17. Observe the following command:

select day(curdate());
Output:
2

dayname()

This function returns the name of the day of the week of specified date.

select dayname(now());
Output:
Monday

dayofmonth()

This function returns the day number from the specified date.

select dayofmonth(current_date());
Output:
2

dayofweek()

It returns the day number of the week from the specified date. It starts with Sunday=1.

select dayofweek(now());
Output:
2

dayofyear()

It returns the day number from the year.

select dayofyear('2020-02-02');
Output:
33

Now in next section of Database Query using SQL Class 12, we will discuss aggregate functions.

Recommended Assignments

Aggregate functions – Database Query using SQL Class 12

You are now familiar with most of the functions which work upon multiple values from the rows of relation. Now in this section of Database Query SQL Class 12, we are going to discuss aggregate functions that return one value from the set of specified rows.

There are two keywords used to group data into columns:

  1. Distinct
  2. All

Distinct keyword

It avoids the duplicates data. Observe the following table named Departments:

DepartmentHOno_of_emp
AccountsAhmedabad300
SalesBaroda250
ITAhmedabad350
HRMAnand200
departments
select distinct HO from departments;
Output:
Ahmedabad
Baroda
Anand

So in the above command Ahmedabad is present two time in the HO column. When distinct keyword is used, it will consider the first value and ignore rest all similar values. As in above example we have Ahmedabad.

All Keyword

It will consider all the values including duplicates. It is by default option for all the queries if not specified the distinct key word.

We will cover the following aggregate functions in Database Query using SQL Class 12 as per your syllabus.

  1. avg
  2. count
  3. max
  4. min
  5. sum

avg()

It compute average of given values as parameters.

select avg(no_of_emp) from departments;
Output
275.0

count()

This function count the total no. of values from the given set of rows.

select count(*) from departments;
Output will be - 4

select count(distinct HO) from departments;
Output will be - 3

In above example, two variations of count() is used. When it is used with distinct keyword it eliminates the duplicate value in counting.

The count(*) will count all the values including null and where as count(column_name) will ignore null values.

max()

This function is used to return maximum value from the given set of values.

select max(no_of_emp) from departments;
Output will be - 350

min()

It will return the minimum value from given set of values. Just consider above given example and replace max with min.

sum()

It will return the addition of specified values. It is also similar like max and min. Write example yourself and see the results. This function can work with column as well with specific where condition.

That’s all from Database Query using SQL Class 12 – aggregate functions.

Thank you very much reading this article.

Don’t forget to comment you views/feedback/suggestions.

You can access the complete IP packed by clicking below link.

Informatics Practices Class 12

For more details click here.

Comment Your Views

%d bloggers like this: