Use of Aggregate Functions

-
Use of Aggregate Functions Use of Aggregate Functions

SQL Aggregate Function - SQL aggregation function is used to perform calculations on multiple rows of a single column of a table. It returns a single value and is also used to summarize the data.
Types of Aggregate Function:
  1. Count Function - COUNT function is used to count the number of rows in a database table. It can work on both numeric and non-numeric data types. COUNT function uses COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.
  2. Sum Function - Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.
  3. AVG Function - The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.
  4. MAX Function - MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
  5. MIN Function - MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.
QUERY*
    create table Product(Product_name varchar(10), Company_name varchar(15), Quantity number(5), cost number(10));
    desc Products;
OUTPUT
Fig... 5.1

QUERY*
    insert into Product values('Milk', 'verka', 10, 100);
    insert into Product values('butter', 'amul', 15, 90);
    insert into Product values('curd', 'verka', 19, 50);
    insert into Product values('coffee', 'nestle', 45, 55);
    insert into Product values('lassi', 'amul', 10, 45);
    insert into Product values('Milk', 'amul', 6, 100);
    insert into Product values('butter', 'verka', 45, 77);
    insert into Product values('candies', 'nestle', 10, 100);
    insert into Product values('chocolate', 'nestle', 20, 110);
    insert into Product values('lassi', 'verka', 33, 75);
    Select count(*) from Product;
OUTPUT
Fig... 5.2
QUERY*
    select count(*) from Product where quantity < 20;
OUTPUT
Fig... 5.3

Comments

Post a Comment

Popular posts from this blog

SQL IS NOT NULL.

-
Image
SQL IS NOT NULL Explanation Understanding the SQL IS NOT NULL Condition The IS NOT NULL condition in SQL is used to check for non-null values in a specified column. It helps in filtering the data by excluding rows where the column's value is NULL. Go To Download Examples Consider a database table named Employees with the following columns: Id - Employee's ID Name - Employee's Name Department - Department where the employee works Email - Employee's Email Address To select all employees who have an email address (i.e., where the email address is not null), you would use the following SQL query: SELECT * FROM Employees WHERE Email IS NOT NULL; This query returns all records from the Employees table where the Email column does not contain NULL values. Combining Conditions You can combine IS NOT NULL with other conditions to make more spec
Read more

Database Management System

-
Image
Database Management System Write a query with a function of DDL commands such as create, alter and drop. DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. DDL is a set of SQL commands used to create, modify, and delete database structures but not data. List of DDL commands: CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers). QUERY: create table bca2 (stu_name varchar(6), stu_roll int, stu_marks number(7)); OUTPUT: DROP: This command is used to delete objects from the database. QUERY: drop table bca2; OUTPUT:
Read more

SQL BETWEEN Operator Example.

-
Image
SQL BETWEEN Operator Example SQL BETWEEN Operator Example The BETWEEN operator in SQL selects values within a given range. The values can be numbers, text, or dates. It is inclusive, which means it includes the beginning and ending values in the range. Example Usage Consider a table named Employees with the following data: ID Name Age 1 John Doe 28 2 Jane Smith 34 3 Emily Johnson 40 To find employees whose ages are between 30 and 35, you would use the following SQL query: SELECT * FROM Employees WHERE Age BETWEEN 30 AND 35; This query will return: ID Name Age 2 ABC Kumar 34
Read more