Database Querying using SQL – Functions, Join and Set Operation

Leave a Comment / Class 12 Informatics Practices - Notes / By /

Topics Covered:

.

Functions – Introduction

My SQL function performs a pre-defined task and returns a single value such as a numerical, string, or date/time value.

Functions operate on zero, one, two, or more values provided to them, these values are called parameters or arguments.

MySQL support number of built-in functions. To use any Function, we need to specify the column to which the function should be applied.

e.g. SELECT function(column name) FROM tablename;

Types of SQL Functions

SQL provides two types of functions, which are as blows :

(i) Single-row Functions This type of function work with a single row at a time. It returns a result for each row of the table, on which the query is performed. Examples of single-row functions include CHAR( ), CONCAT( ), INSTR( ), etc.

(ii) Multiple-row Functions This type of function work with data of multiple rows at a time and return a single output value. Examples of multiple-row functions include SUM(), AVG(), COUNT(), MAX(), MIN().

Mathematical Functions

MySQL provides a number of functions used for performing mathematical calculations on the database. Mathematical functions are very important in SQL to implement different mathematical concepts in queries.

Mathematical functions are explained with examples as follows:

FunctionDescriptionExample
MOD ( A,B)Return the remainder of A/BSELECT MOD(11,3) ; OUTPUT:- 2 SELECT MOD(10.5,3) ; OUTPUT:- 1.5
POWER ( A,B) or POW(A,B)Returns the value of A raised to the power BSELECT POWER(5,3) ; OUTPUT:- 125 SELECT POW(2,-2) ; OUTPUT:- 0.25 SELECT POWER(-2,3) ; OUTPUT:- -8 SELECT POWER(2.37,3.45) ; OUTPUT:- 19.6282….
ROUND (N,D )Return number rounded to D place after decimal, If D is not specified,N is rounded up to 0 digit of decimal and the result is an Integer. If the first digit after the decimal value is 5 or >5 then integer number is increased by 1 If D is +ve ,then N is rounded up to D digits after the decimal by checking D+1th digit,if it is 5or <5,then the dthdigit is increased by 1 If D is -ve , then N is rounded up to D digits before the decimal by checking Dth digit, if it is 5 or >5 then d-1th digit is increased by 1,all trailing dth digits are converted to 0 and the result is an integer value.SELECT ROUND(1.58) ; OUTPUT:- 2 SELECT ROUND(-1.23) ; OUTPUT:- -1 SELECT ROUND(1.298,0) ; OUTPUT:- 1 SELECT ROUND(-5.898,0) ;OUTPUT:- -6 SELECT ROUND(3.79867,3) ; OUTPUT:- 3.799   SELECT ROUND(23.298,-1) ; OUTPUT:- 20   SELECT ROUND(36567.78,-4) ; OUTPUT:- 40000
SIGN ( )Return the sign of the given numberSELECT SIGN(-10) ; OUTPUT:- -1 SELECT SIGN(10) ; OUTPUT:- 1
SQRT ( )Return the non-negative square root of the given numberSELECT SQRT(25) ; OUTPUT:- 5
TRUNCATE( x, d)Truncates x to the specified number of d. If d is 0,it removes all the decimal values and returns integer. If d is +ve, Truncates x to d digits right to the decimal point. If d is -ve, Truncates x to d digits left fo the decimal point.SELECT TRUNCATE(7.29, 0) ; OUTPUT:- 7   SELECT TRUNCATE(27.59, 1) ; OUTPUT:- 27.5   SELECT TRUNCATE(389.23, -2) ; OUTPUT:- 300
ABS (value )Returns the absolute value of the given value.SELECT ABS(-25) ; OUTPUT:- 25 SELECT ABS(-234.67) ; OUTPUT:- 234.67

String/Text Functions

The string/text functions of SQL are used to extract, change, format or alter character strings. They accept a character string as an input and provides character string or numeric values as an output.

String/text functions are explained with example as follows :

FunctionDescriptionExample
ASCII( )Returns the ASCII code of the character.SELECT ASCII (‘A’ ) FROM DUAL; OUTPUT:- 65 SELECT ASCII (‘1’ ) FROM DUAL; OUTPUT:- 49 SELECT ASCII (‘ABC’ ) FROM DUAL; OUTPUT:- 65
TRIM ( )Remove spaces from beginning and endingSELECT TRIM( ‘     APPLE ‘); OUTPUT:’APPLE’
LTRIM ()Returns string after removing left side of spaces.SELECT LTRIM( ‘     APPLE ‘); OUTPUT:’APPLE ’
RTRIM ()Returns string after removing right side of spaces.SELECT LTRIM( ‘     APPLE ‘); OUTPUT:’         APPLE’
INSTR( )It search one string in another string and returns position, if not found 0SELECT INSTR (‘COMPUTER’,’PUT’) ; OUTPUT: – 4 SELECT INSTR (‘COMPUTER’,’PY’) ; OUTPUT: – 0
LENGTH ( )Returns number of Characters in the string.SELECT LENGTH (‘COMPUTER’) ; OUTPUT: – 8
LEFT (S,N)Returns N characters of String S from beginningSELECT LEFT(‘COMPUTER’,3) ; OUTPUT:- COM
RIGHT(S,N)Returns N characters of String S from endingSELECT RIGHT(‘COMPUTER’,3) ; OUTPUT:- TER
SUBSTR ( )Returns the substring as specifiedSELECT SUBSTR(‘COMPUTER’,3,4) ; OUTPUT:- MPUT
CONCATE ()Returns concatenated StringSELECT CONCAT(‘COM’,’PUTER’) ; OUTPUT:- COMPUTER
LOWER() / LCASE()Converts string into lowercase.SELECT LOWER(‘COMPUTER’) ; OUTPUT:- computer
UPPER() / UCASE()Converts string into uppercase.SELECT UPPER(‘COMPUTER’) ; OUTPUT:- COMPUTER
REPLACE()Replace all occurrences of the second string in the first string with the third string.SELECT REPLACE (‘INFO COMPUTER’,’INFO’,’LATEST’) ; OUTPUT:- LATEST COMPUTER
REVERSE()Return reverse of the given string.SELECT REVERSE(‘COMPUTER’) ; OUTPUT:- RETUPMOC
REPEAT ()Returns a given string for a specified number of times.SELECT REPEAT(‘COM’,3) ; OUTPUT:- COMCOMCOM

Date/Time Functions

MySQL stores date in date/time format, representing the century, month, year, day and hours. The date and time functions are used to perform operations on the date/time data stored in the database. The default date format is YYYY-MM-DD in MySQL.

Date/time functions are explained with examples as follows:

FunctionDescriptionExample
CURDATE( )Returns the current DateSELECT CURDATE( ) ; OUTPUT:- 14/06/2021
DATE( )Returns the Date part onlySELECT DATE( ‘01/07/2020’) ; OUTPUT:- 01
MONTH ( )Returns the Month part onlySELECT MONTH (‘01/07/2020’ ) OUTPUT: – 07
YEAR ( )Returns the Year part onlySELECT YEAR (‘01/07/2020’ ) OUTPUT: – 2020
DAYNAME ( )Returns the name of the week daySELECT DAYNAME (‘01/07/2020’ ) OUTPUT: – WEDNESDAY
MONTHNAME ()Returns the name of the month.SELECT MONTHNAME (‘01/07/2020’ )                      OUTPUT: – JULY
DAYOFMONTH( )Returns the day of the month(1-31)SELECT DAYOFMONTH (‘01/07/2020’ ) OUTPUT: – 01
DAYOFWEEK ( )Returns the weekday index of the date.SELECT DAYOFWEEK (‘01/07/2020’ ) OUTPUT: – 04
DAYOFYEAR ( )Returns the day of the year(1-366).SELECT DAYOFYEAR (‘01/07/2020’ ) OUTPUT: – 183
NOW ( )Returns both current date & time at which the function execute.SELECT NOW ( );
SYSDATE ( )Returns the current date & time.SELECT NOW ( ) , SLEEP(2), NOW( ); OUTPUT: 01/07/2020 04:20:32 01/07/2020 04:20:32 SELECT SYSDATE( ), SLEEP ( 2 ), SYSDATE( ); OUTPUT: 01/07/2020 04:20:32 01/07/2020 04:20:34

Difference Between NOW() and SYSDATE()

NOW() function returns the date and time at which the function was executed even if we execute multiple NOW() functions with SELECT. whereas SYSDATE() will always return date and time at which each SYSDATE() function started execution.

For example:

mysql> Select now(), sleep(2), now();

Output: 2018-12-04 10:26:20, 0, 2018-12-04 10:26:20

mysql> Select sysdate(), sleep(2), sysdate();

Output: 2018-12-04 10:27:08, 0, 2018-12-04 10:27:10

Aggregate Functions

Aggregate functions are also called multiple-row functions. These functions work on a set of records as a whole and return a single value for each column of the records on which the function is applied.

Aggregate Functions is used to perform calculation on group of rows and return the calculated summary like sum of salary , average of salary.

1. AVG()                                 2. COUNT()                            3.MAX()

4 . MIN()                                  5. SUM( )

AVG() Function:

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

Syntax:         SELECT AVG(column_name) FROM table_name

SQL AVG() Example: We have the following “Orders” table:

O_IdOrderDateOrderPriceCustomer
12018/11/121000Vivek Kumar
22021/10/231600Nitesh Sharma
32020/09/02700Vivek Kumar
42019/09/03300Vivek Kumar
52020/08/302000Samarjeet Gupta
62020/10/04100Nitesh Sharma

Now we want to find the average value of the “OrderPrice” fields. We use the following SQL statement:

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

The result-set will look like this:

OrderAverage
950

Now we want to find the customers that have an OrderPrice value higher than the average OrderPrice value.

We use the following SQL statement:

SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders) ;

The result-set will look like this:

Customer
Vivek Kumar
Nitesh Sharma
Samarjeet Gupta

COUNT( ) Function:

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

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;

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

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

SELECT COUNT(DISTINCT column_name) FROM table_name COUNT(column_name) ;

Example

We have the following “Orders” table:

O_IdOrderDateOrderPriceCustomer
12018/11/121000Vivek Kumar
22021/10/231600Nitesh Sharma
32020/09/02700Vivek Kumar
42019/09/03300Vivek Kumar
52020/08/302000Samarjeet Gupta
62020/10/04100Nitesh Sharma

Now we want to count the number of orders from “Customer Nitesh Sharma”. We use the following SQL statement:

SELECT COUNT(Customer) AS CustomerNitesh Sharma FROM Orders WHERE Customer=’Nitesh Sharma’

The result of the SQL statement above will be 2, because the customer Nitesh Sharma has made 2 orders in total:

Customer Nitesh Sharma
2

Example:            

If we omit the WHERE clause, like this:

SELECT COUNT(*) AS NumberOfOrders FROM Orders;

The result-set will look like this:

NumberOfOrders
6

It returns the total number of rows in the table.

COUNT(DISTINCT column_name)

Example:

Now we want to count the number of unique customers in the “Orders” table. We use the following SQL statement:

SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders

The result-set will look like this:

NumberOfCustomers
3

which is the number of unique customers (Vivek Kumar, Nitesh Sharma, and Samarjeet Gupta) in the “Orders” table.

The MAX() Function

The MAX() function returns the largest value of the selected column.

Syntax: 

 SELECT MAX(column_name) FROM table_name;

Example:

We have the following “Orders” table:

O_IdOrderDateOrderPriceCustomer
12018/11/121000Vivek Kumar
22021/10/231600Nitesh Sharma
32020/09/02700Vivek Kumar
42019/09/03300Vivek Kumar
52020/08/302000Samarjeet Gupta
62020/10/04100Nitesh Sharma

Now we want to find the largest value of the “OrderPrice” column.

We use the following SQL statement:

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders;

The result-set will look like this:

LargestOrderPrice
2000

The MIN() Function

The MIN() function returns the smallest value of the selected column.

Syntax:

       SELECT MIN(column_name) FROM table_name ;

Example: We have the following “Orders” table:

O_IdOrderDateOrderPriceCustomer
12018/11/121000Vivek Kumar
22021/10/231600Nitesh Sharma
32020/09/02700Vivek Kumar
42019/09/03300Vivek Kumar
52020/08/302000Samarjeet Gupta
62020/10/04100Nitesh Sharma

Now we want to find the smallest value of the “OrderPrice” column.

We use the following SQL statement:

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders;

The result-set will look like this:

SmallestOrderPrice
100

The SUM() Function

The SUM() function returns the total sum of a numeric column.

Syntax:       SELECT SUM(column_name) FROM table_name

Example: We have the following “Orders” table

O_IdOrderDateOrderPriceCustomer
12018/11/121000Vivek Kumar
22021/10/231600Nitesh Sharma
32020/09/02700Vivek Kumar
42019/09/03300Vivek Kumar
52020/08/302000Samarjeet Gupta
62020/10/04100Nitesh Sharma

Now we want to find the sum of all “OrderPrice” fields”.

We use the following SQL statement:

The result-set will look like this:

OrderTotal
5700

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Syntax:

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

O_IdOrderDateOrderPriceCustomer
12018/11/121000Vivek Kumar
22021/10/231600Nitesh Sharma
32020/09/02700Vivek Kumar
42019/09/03300Vivek Kumar
52020/08/302000Samarjeet Gupta
62020/10/04100Nitesh Sharma

Now we want to find the total sum (total order) of each customer. We will have to use the GROUP BY statement to group the customers.

We use the following SQL statement:

SELECT Customer, SUM(OrderPrice) FROM Orders GROUP BY Customer;

The result-set will look like this:

CustomerSUM(OrderPrice)
Vivek Kumar2000
Nitesh Sharma1700
Samarjeet Gupta2000

Let’s see what happens if we omit the GROUP BY statement:

SELECT Customer, SUM(OrderPrice) FROM Orders;

The result-set will look like this:

CustomerSUM(OrderPrice)
Vivek Kumar5700
Nitesh Sharma5700
Vivek Kumar5700
Vivek Kumar5700
Samarjeet Gupta5700
Nitesh Sharma5700

The SELECT statement above has two columns specified (Customer and SUM(OrderPrice).

The “SUM(OrderPrice)” returns a single value (that is the total sum of the “OrderPrice” column), while “Customer” returns 6 values (one value for each row in the “Orders” table).

Due to this mismatch, the above query is not giving the correct output. To resolve this problem you need to use group by clause.

GROUP BY More Than One Column

We can also use the GROUP BY statement on more than one column, like this:

SELECT Customer, OrderDate, SUM(OrderPrice) FROM Orders GROUP BY Customer,OrderDate ;

The HAVING Clause

HAVING Clause in SQL is used to specify conditions on the rows with GROUP BY clause. The HAVING clause was added to MySQL because the WHERE keyword could not be used with aggregate functions.

Syntax:

SELECT column_name, aggregate_function(column_name) FROM table_name

WHERE column_name operator value GROUP BY column_name

HAVING aggregate_function(column_name) operator value;

Example: We have the following “Orders” table:

O_IdOrderDateOrderPriceCustomer
12018/11/121000Vivek Kumar
22021/10/231600Nitesh Sharma
32020/09/02700Vivek Kumar
42019/09/03300Vivek Kumar
52020/08/302000Samarjeet Gupta
62020/10/04100Nitesh Sharma

Now we want to find if any of the customers have a total order of less than 2000.

We use the following SQL statement:

SELECT Customer, SUM(OrderPrice) FROM Orders GROUP BY Customer

HAVING SUM(OrderPrice)<2000 ;

The result-set will look like this:

CustomerSUM(OrderPrice)
Nitesh Sharma1700

Now we want to find if the customers “Vivek Kumar” or “Samarjeet Gupta” have a total order of more than 1500.

We add an ordinary WHERE clause to the SQL statement:

SELECT Customer, SUM(OrderPrice) FROM Orders WHERE Customer=’Vivek Kumar’ OR Customer=’Samarjeet Gupta’ GROUP BY Customer HAVING SUM(OrderPrice)>1500 ;

The result-set will look like this:

CustomerSUM(OrderPrice)
Vivek Kumar2000
Samarjeet Gupta2000

Related Posts

Leave a Comment

You cannot copy content of this page

Scroll to Top