Original subtitles

0 Hi everyone 1

Welcome to this new section. In this section we will be covering the mathematical functions 2

We have already covered 5 important mathematical functions 3

Those were also called aggregate functions 4

If you remember we covered sum count average min and max 5

Before covering group by 6

And we used all those five functions while doing group by 7

There are other mathematical functions also 8

These functions have Limited use cases 9

But it is better to know these so that you know the capabilities of SQL in terms of mathematical functions also 10

so let us cover them quickly 11

First is the 12

Ceil and floor functions 13

So ceil function 14

Returns the smallest integer value that is greater than or equal to the number that you have input 15

For example if you input a number like 4.5 16

The smallest integer value which is greater than this 4.5 will be 5 17

So ceil function gives you the 18

Ceiling of that decimal number that you have input 19

Any number 9.9 will be 10 20

11.1 will be 12 21

That is the ceiling of that decimal number 22

Floor of the same decimal number will be the 23

The largest integer value which is lower 24

or equal to that number 25

11.1 will have floor of 11 26

And ceiling of 12 27

4.5 will have 28

Floor of 4 29

And ceiling of 5 30

So these ceiling and floor values 31

Can be taken out using the ceil and floor function 32

Let us look at an example 33

So we have decimal values in the sales column of the sales table 34

If you want to round this up 35

To say the lower integer or the higher integer then that decimal value 36

You can use the ceil or floor functions 37

Let us 38

Write this query where we will select the order line and the sales value in another column we will put the ceiling 39

Ceiling value of that sales value 40

And in another column will put the floor value 41

Let us go and write this query 42

We will select order Line 43

the Sales value 44

Ceiling of sales 45

this is Ceil function 46

And the 47

Floor of sales 48

This is from the sales table 49

That's it. Let us 50

Run this query 51

So you can see 52

For order value of 261.96 53

Ceiling is 252 54

And floor is 261 55

For 731.94 56

Ceil is 732 57

Floor is 731 58

So you get the idea 59

This is the ceiling and floor function 60

Also note that 61

The function name is coming as the column name if you want to use some other name for this column 62

Use aliases 63

Which are covered in a separate class called AS