# SQL Functions , Grouping Records, Joins in SQL – Notes

SQL Functions – Single Row and Aggregate

Group By , Having clause

Joins in SQL

### Functions in SQL

Function is used to perform some particular task and it returns zero or more values as a result. Functions can be applied to work on single or multiple records (rows) of a table.

Depending on their application in one or multiple rows, SQL functions are categorised as

1. Single Row functions and
2. Aggregate functions.

#### Single Row Functions

Single row functions are applied on a single value and return a single value. Also known as Scalar functions.

Types of Single Row Functions

#### (A) Math Functions

Math Functions accept numeric value as input and return a numeric value as a result.

Three commonly used numeric functions are POWER(), ROUND() and MOD().

POWER(X,Y) / POW(X,Y) : Calculates X to the power Y.

mysql> SELECT POWER(2,3);
Output:
8

ROUND(N,D)

Rounds off number N to D number of decimal places.

Note: If D=0, then it rounds off the number to the nearest integer.
mysql>SELECT ROUND(2912.564, 1);
Output:
2912.6
mysql> SELECT ROUND(283.2);
Output:
283

mysql> SELECT * FROM SALE WHERE Commission > 73000;
+—————+——–+———-+————–+———————-+———–+—————+——————-+
|invoiceno |carid | custid | saledate | paymentmode | empid | saleprice | Commission |
+—————+——–+———-+————–+———————-+———–+—————+——————-+
| I00001 |D001 | C0001 | 2019-01-24 | Credit Card | E004 | 613248.00 | 73589.64 |
| I00004 |D002 | C0001 | 2018-10-15 | Bank Finance| E007 | 659982.00 | 79198.84 |
| I00006 |S002 | C0002 | 2019-01-30 | Bank Finance | E007 | 620214.00 | 74425.68 |
+———-+——+——–+———–+————-+——-+———–+————+
3 rows in set (0.02 sec)

Example : Display InvoiceNo, SalePrice and Commission such that commission value is rounded off to 0.

mysql> SELECT InvoiceNo, SalePrice, Round(Commission,0) FROM SALE;
+—————-+—————+———————————+
| InvoiceNo | SalePrice | Round(Commission,0) |
+—————-+—————+———————————+
| I00001 | 613248.00 | 73590 |
| I00002 | 590321.00 | 70839 |
| I00003 | 604000.00 | 72480 |
| I00004 | 659982.00 | 79198 |
| I00005 | 369310.00 | 44317 |
| I00006 | 620214.00 | 74426 |
+———–+———–+———————+
6 rows in set (0.00 sec)

MOD(A, B) :

Returns the remain der after dividing number A by number B.
mysql> SELECT MOD(21, 2);
Output:
1

#### (B) String Functions

String functions can perform various operations on alphanumeric data which are stored in a table.

UCASE(string) OR UPPER(string) : Converts string into uppercase.

mysql> SELECT UCASE(“Informatics Practices”);
Output:
INFORMATICS PRACTICES

LOWER(string) OR LCASE(string) : Converts string into lowercase.

mysql> SELECT LOWER(“Informatics Practices”);
Output:
informatics practices

MID(string, pos, n) OR SUBSTRING(string, pos, n) OR SUBSTR(string, pos, n)
Returns a substring of size n starting from the specified position (pos) of the string. If n is not specified, it returns the substring from the position pos till end of the string.

mysql> SELECT MID(“Informatics”, 3, 4);

Output:
form

mysql> SELECT MID(‘Informatics’,7);
Output:
atics

LENGTH(string) : Return the number of characters in the specified string.

mysql> SELECT LENGTH(“Informatics”);
Output:
11

LEFT(string, N) : Returns N number of characters from the left side of the string.

mysql> SELECT LEFT(“Computer”, 4);
Output:
Comp

RIGHT(string, N) : Returns N number of characters from the right side of the string.

mysql> SELECT RIGHT(“SCIENCE”, 3);
Output:

NCE

INSTR(string, substring) : Returns the position of the first occurrence of the substring in the given string. Returns 0, if the substring is not present in the string.

mysql> SELECT INSTR(“Informatics”, “ma”);
Output:
6

LTRIM(string) : Returns the given string after removing leading white space characters.
mysql> SELECT LENGTH(“ DELHI”), LENGTH(LTRIM(“ DELHI”));
Output:
+—–+—–+
| 7 | 5 |
+—–+—–+
1 row in set (0.00 sec)

RTRIM(string) : Returns the given string after removing trailing white space characters.
mysql>SELECT LENGTH(“PEN “)LENGTH(RTRIM(“PEN “));
Output:
+—+—-+
| 5 | 3 |
+—+—-+
1 row in set (0.00 sec)

TRIM(string) : Returns the given string after removing both leading and trailing white space characters.
Output:
+—+—+
| 9 | 5 |
+—+—+
1 row in set (0.00 sec)

#### (C) Date and Time Functions

There are various functions that are used to perform operations on date and time data.

NOW()

It returns the current system date and time.

mysql> SELECT NOW();
Output:
2019-07-11 19:41:17

DATE() It returns the date part from the given date/time expression.

mysql> SELECT DATE(NOW());
Output:
2019-07-11

MONTH(date) : It returns the month in numeric form from the date.

mysql> SELECT MONTH(NOW());
Output:
7

MONTHNAME(date) : It returns the month name from the specified date.
mysql> SELECT MONTHNAME(“2003-11-28”);
Output:
November

YEAR(date) : It returns the year from the date. mysql> SELECT YEAR(“2003-10-03”);
Output:
2003

DAY(date) : It returns the day part from the date.
mysql> SELECT DAY(“2003-03-24”);
Output:
24

DAYNAME(date) : It returns the name of the day from the date.
mysql> SELECT DAYNAME(“2019-07-11”);
Output:
Thursday

### 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 in SQL

MAX(column) : Returns the largest value from the specified column.
mysql> SELECT MAX(Price) FROM INVENTORY;
Output:
673112.00

MIN(column) : Returns the smallest value from the specified column.
mysql> SELECT MIN(Price) FROM INVENTORY;
Output:
355205.00

AVG(column) : Returns the average of the values in the specified column.
mysql> SELECT AVG(Price) FROM INVENTORY;
Output:
576091.625000

SUM(column) : Returns the sum of the values for the specified column.
mysql> SELECT SUM(Price) FROM INVENTORY;
Output:
4608733.00

COUNT() : Returns the number of records in a table. Note: In order to display the number of records that matches a particular criteria in the table, we have to use COUNT() with WHERE clause.
mysql> SELECT COUNT() from MANAGER;

|count() |
+—+
| 4 |
+—+

COUNT(column) : Returns the number of values in the specified column ignoring the NULL values.
Note:
In this example, let us consider a MANAGER table having two attributes and four records.
mysql> SELECT * from MANAGER;
+——– +——————+
| MNO | MEMNAME |
+——– +——————+
| 1 | AMIT |
| 2 | KAVREET |
| 3 | KAVITA |
| 4 | NULL |
+———+—————–+
4 rows in set (0.00 sec)
mysql> SELECT COUNT(MEMNAME) FROM MANAGER;
+——————————+
| COUNT(MEMNAME) |
+——————————-+
| 3 |
+——————————-+
1 row in set (0.01 sec)

## Grouping Records

### GROUP BY Clause in SQL

Group by clause, groups the rows together that contains the same values in a specified column.

HAVING Clause in SQL is used to specify conditions on the rows with Group By clause.

We can use the aggregate functions (COUNT, MAX, MIN, AVG and SUM) to work on the grouped values.

Sales Table

a) Display the number of Cars purchased by each Customer from SALE table.
mysql> SELECT CustID, COUNT() “Number of Cars” FROM SALE GROUP BY CustID;

4 rows in set (0.00 sec)

b) Display the Customer Id and number of cars purchased if the customer purchased more than 1 car from SALE table.

mysql> SELECT CustID, COUNT() FROM SALE GROUP BY
CustID HAVING Count()>1;

c) Display the number of people in each category of payment mode from the table SALE.

mysql> SELECT PaymentMode, COUNT(PaymentMode)
FROM SALE GROUP BY Paymentmode ORDER BY Paymentmode;

4 rows in set (0.00 sec)

d) Display the PaymentMode and number of payments made using that mode more than once.

mysql> SELECT PaymentMode, Count(PaymentMode)
FROM SALE GROUP BY Paymentmode HAVING COUNT(*)>1
ORDER BY Paymentmode;

2 rows in set (0.00 sec)