EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials SQL Tutorial SQL Ceiling

SQL Ceiling

Payal Udhani
Article byPayal Udhani
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated March 10, 2023

SQL Ceiling

Introduction to SQL Ceiling

SQL Ceiling function is the mathematical function available in SQL that is used for the numeric type of values. These numeric values can be either integers or floating-point numbers. If the numeric value is formatted inside the string type of value as parameter then that value is also allowed for Ceiling function. In short, any value or expression that can be deduced to the numeric value can be used as a parameter to the Ceiling() mathematical function in SQL. This function helps us retrieve the minimum integer value that is greater or equivalent to the passed value.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

In this article, we will learn about how Ceiling function can be used to retrieve the integral value not less than the passed numeric value or expression, its syntax, and some of the examples that can help to make the implementation of the Ceiling() function easy and understandable.

Syntax:

The below is the syntax for the mathematical Ceiling() function in SQL –

Ceiling(expressionOrNumber);
  • expressionOrNumber: expressionOrNumber can be any integer or floating-point value or even a decimal value. In case if this numeric value is wrapped into strings then these string values are also acceptable. Other than the direct specification of numeric value any expression that will ultimately deduce to a numeric value is also allowed as a parameter to the Ceiling() function.
  • Return value: The return value of the Ceiling() function is the smallest integer value that is greater than or equal to the value that is passed as the parameter to the function. The type of the return value is dependent on the data type of the value that is passed as the parameter to the Ceiling() function. In case if the parameter is or integer data type then the return value is also of integer type. While in other cases if the deduced value of the parameter of the direct specification of the value of the parameter is of floating-point type then the datatype of the return value is of the floating-point data type itself.

Examples of SQL Ceiling

Following are the examples are given below:

1. Using Ceiling() function with Positive Values

We will consider the positive numeric value say 3.59 and then use the Ceiling function to retrieve the smallest integer value that is greater than or equivalent to the 3.59 value. Let us execute the following SQL query statement and observe the output –

SELECT Ceiling(3.59);

Output:

SQL Ceiling-1.1

Let us consider one more example of positive value. But in this example, we will use an expression that will evaluate to the value that is of numeric type. Simply consider the expression 5 * 1.65 whose actual value is 8.25 and use this expression in Ceiling() function to retrieve minimum integral value greater than passed value using following query statement –

SELECT Ceiling(5 * 1.65);

Output:

SQL Ceiling-1.2

Now, we will consider a positive number wrapped as a string and use it as a parameter to Ceiling() function. For example, consider “56.569” value that is used in the following manner –

SELECT Ceiling(56.569);

Output:

SQL Ceiling-1.3

2. Using Ceiling() Function with Negative Values

We will consider the negative numeric value say -65.55 and then use the Ceiling function to retrieve the smallest integer value that is greater than or equivalent to the -65.55 value. Let us execute the following SQL query statement and observe the output –

SELECT Ceiling(-65.55);

Output:

SQL Ceiling-1.4

Let us consider one more example of a negative value. But in this example, we will use an expression that will evaluate to the value that is of numeric type. Simply consider the expression 8 * -91.65 whose actual value is −733.2 and use this expression in Ceiling() function to retrieve minimum integral value greater than passed value using following query statement –

SELECT Ceiling(8 * -91.65);

Output:

SQL Ceiling-1.5

Now, we will consider a negative number wrapped as a string and use it as a parameter to Ceiling() function. For example, consider “-98.154″value that is used in the following manner –

SELECT Ceiling(-98.154);

Output:

SQL Ceiling-1.6

3. Using Ceiling() Function with Values in The Table

Let us now see how we can use the Ceiling() function in the query statements on the values of the columns of the table. We will create one table named workers using following query statement –

CREATE TABLE `workers` (
`developer_id` int(11) NOT NULL AUTO_INCREMENT,
`team_id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`position` varchar(100) DEFAULT NULL,
`technology` varchar(100) DEFAULT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`developer_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

Output:

SQL Ceiling-1.7

Now, we will insert some records in workers table using the following query statements –

INSERT INTO `workers` VALUES
(1,1,'Payal','Developer','Angular',30000),
(2,1,'Heena','Developer','Angular',10000),
(3,3,'Vishnu','Manager','Maven',25000),
(4,3,'Rahul','Support','Digital Marketing',15000),
(5,3,'Siddhesh','Tester','Maven',20000),
(6,7,'Siddharth','Manager','Java',25000),
(7,4,'Brahma','Developer','Digital Marketing',30000),
(8,1,'Arjun','Tester','Angular',19000),
(9,2,'Nitin','Developer','SQL',20000),
(10,2,'Ramesh','Administrator','SQL',30000),
(11,2,'Rohan','Admin',NULL,20000),
(12,2,'Raj','Designer',NULL,30000);

Output:

SQL Ceiling-1.8

Now we will calculate the average salary using the following query statement –

SELECT avg(salary) from workers;

Output:

SQL Ceiling-1.9

If we want to retrieve the average salary in integer format with the greatest value that is greater than or equivalent to the average value using Ceiling() function using the following query statement –

SELECT Ceiling (avg(salary)) from workers;

Output:

SQL Ceiling-1.10

Conclusion

Mathematical function Ceiling() is used rounding numeric values in SQL. We can retrieve the minimum value in integer format that is greater or equivalent to the passed numeric number or expression whose results can be either floating value of an integer or decimal value. The working of Ceiling() function is exactly different than Floor() function. Though both of them are used for rounding. Ceiling() funtion rounds up while Floor() function rounds down the numeric value.

Recommended Articles

We hope that this EDUCBA information on “SQL Ceiling” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Link
  2. SQL DELETE
  3. MySQL Create Function
  4. SQL DELETE ROW
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW