Sql Functions In Dbms

sql aggregate function slide presentation


SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system, or for stream processing in a relational data stream management system

SQL stands for Structured Query Language. A query language is a kind of programming language that’s designed to facilitate retrieving specific information from databases, and that’s exactly what SQL does. To put it simply, SQL is the language of databases

What is SQL and why it is used?

Structured Query Language (SQL) is the standard and most widely used programming language for relational databases. It is used to manage and organize data in all sorts of systems in which various data relationships exist.

RDBMS (Relational Database Management System)

A database management system (DBMS) that incorporates the relational-data model, normally including a Structured Query Language (SQL) application programming interface. It is a DBMS in which the database is organized and accessed according to the relationships between data items. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence.

Features of relational database management systems

Elements of the relational database management system that overarch the basic relational database are so intrinsic to operations that it is hard to dissociate the two in practice.

The most basic RDBMS functions are related to create, read, update and delete operations — collectively known as CRUD They form the foundation of a well-organized system that promotes consistent treatment of data.

The RDBMS typically provides  DATA DICTIONARIES and metadata collections that are useful in data handling. These programmatically support well-defined data structures and relationships. Data storage management is a common capability of the RDBMS, and this has come to be defined by data objects that range from binary large object — or blob– strings to stored procedures. Data objects like this extend the scope of basic relational database operations and can be handled in a variety of ways in different RDBMSes.

The most common means of data access for the RDBMS is SQL. Its main language components comprise data manipulation language and data definition language statements. Extensions are available for development efforts that pair SQL use with common programming languages, such as the Common Business-Oriented Language (COBOL), Java and .NET.

RDBMSes use complex algorithms  that support multiple concurrent user access to the database while maintaining data integrity. Security management, which enforces policy-based access, is yet another overlay service that the RDBMS provides for the basic database as it is used in enterprise settings.

RDBMSes support the work of database administrators (DBAs) who must manage and monitor database activity. Utilities help automate data loading and database backup. RDBMSes manage log files that track system performance based on selected operational parameters. This enables measurement of database usage, capacity and performance, particularly query performance. RDBMSes provide graphical interfaces that help DBAs visualize database activity.

While not limited solely to the RDBMS, ACID compliance is an attribute of relational technology that has proved important in enterprise computing. These capabilities have particularly suited RDBMSes for handling business transactions.

As RDBMSes have matured, they have achieved increasingly higher levels of query optimization, and they have become key parts of reporting, analytics and data warehousing applications for businesses as well. RDBMSes are intrinsic to operations of a variety of enterprise applications and are at the center of most master data management systems.

SQL Functions

SQL has many built-in functions for performing calculations on data.

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

  • AVG() – Returns the average value
  • COUNT() – Returns the number of rows
  • FIRST() – Returns the first value
  • LAST() – Returns the last value
  • MAX() – Returns the largest value
  • MIN() – Returns the smallest value
  • SUM() – Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

  • UCASE() – Converts a field to upper case
  • LCASE() – Converts a field to lower case
  • MID() – Extract characters from a text field
  • LEN() – Returns the length of a text field
  • ROUND() – Rounds a numeric field to the number of decimals specified
  • NOW() – Returns the current system date and time
  • FORMAT() – Formats how a field is to be displayed

SQL AVG() Function

The AVG() Function

The AVG() function returns the average value of a numeric column.

SQL AVG() Syntax

SELECT AVG(column_name) FROM table_name

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Products” table:

1Chais1110 boxes x 20 bags18
2Chang1124 – 12 oz bottles19
3Aniseed Syrup1212 – 550 ml bottles10
4Chef Anton’s Cajun Seasoning2248 – 6 oz jars21.35
5Chef Anton’s Gumbo Mix2236 boxes25

SQL AVG() Example

The following SQL statement gets the average value of the “Price” column from the “Products” table:


SELECT AVG(Price) AS PriceAverage FROM Products;

The following SQL statement selects the “ProductName” and “Price” records that have an above average price:


SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);

SQL COUNT() Function

The COUNT() function returns the number of rows that matches a specified criteria.

SQL COUNT(column_name) Syntax

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:SELECT COUNT(column_name) FROM table_name;

SQL COUNT(*) Syntax

The COUNT(*) function returns the number of records in a table:SELECT COUNT(*) FROM table_name;

SQL COUNT(DISTINCT column_name) Syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:SELECT COUNT(DISTINCT column_name) FROM table_name;

Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Orders” table:


SQL COUNT(column_name) Example

The following SQL statement counts the number of orders from “CustomerID”=7 from the “Orders” table:


SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;

SQL COUNT(*) Example

The following SQL statement counts the total number of orders in the “Orders” table:


SELECT COUNT(*) AS NumberOfOrders FROM Orders;
Try it Yourself »

SQL COUNT(DISTINCT column_name) Example

The following SQL statement counts the number of unique customers in the “Orders” table:


SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;

Drop your comment


Author: refuge_2020

Leave a Reply

Your email address will not be published. Required fields are marked *