All language subtitles for 1. CEIL & FLOOR

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