SQL | DIVISION
FREE
Overview
The Division in SQL is employed for conducting integer division. It computes the result of a division operation as a quotient and omits any remainder, delivering a whole number outcome. This operator proves useful in situations that involve discrete quantities or require counting the number of times one value perfectly divides another. Suppose, In a retail store, you have a certain quantity of items in stock, and you want to determine how many can be sold in packs or bundles.
Example: If you have 25 widgets in stock and you sell them in packs of 5, you can use the DIV operator () to find that you can sell 5 packs with no leftover items.
What is a Division Operator in SQL?
The Division in SQL is used for integer division. It's used to divide one integer value by another and returns the quotient as an integer, discarding the remainder. Here's the basic syntax:
Syntax:
- dividend is the value you want to divide.
- divisor is the value you want to divide by.
- table_name is the name of the table you are querying.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
SQL Implementation of Division
Division in relational databases is a bit like a filter or a way to find information that matches specific criteria. Imagine you have two sets of data, like lists of people and their favourite colours. You want to find out which people have a favourite colour that matches every colour in another list. In database terms, you're looking for rows of data (people) that have a relationship with all the values in another table (colours).
Now, let's break down how you can do this:
Implementation 1:
Imagine you have two tables, R and S, where R has two columns, x and y, and S has one column, y. You want to find all the values of x in R that are associated with all the values of y in S.
- First, you combine every value of y in S with every value of x in R (cross join). This gives you a big list (r1) of all possible combinations of x and y.
- Next, you subtract the actual combinations from R (R(x, y)) from this big list (r1). What's left in r2 are the x values that are not associated with every value in S(y).
- Finally, you take the x values in R and remove the x values found in r2 to get the x values that are associated with all values in S(y).
Implementation 2:
This approach uses a correlated subquery, which means it checks the relationship between each row in R and S one at a time.
- For each row in R (as sx), it checks if there are any y values in S that are not in the corresponding row in R. If there are none, it means that x is associated with all values in S(y).
So, in simple terms, both implementations are trying to find those x values in R that are linked to all the y values in S.
Now, why is Implementation 2 more complex? It's because it checks each row individually and can be slower if you have a lot of data. Implementation 1 calculates all possible combinations first, which might be faster in some cases.
Example:
Let us take an example to understand it better:
Suppose we have two tables:
Table A: People
| PersonID | Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
| 4 | Dave |
| 5 | Eve |
Table B: Hobbies
| PersonID | Hobby |
|---|---|
| 1 | Reading |
| 1 | Swimming |
| 2 | Reading |
| 3 | Swimming |
| 4 | Reading |
| 4 | Swimming |
| 5 | Reading |
Now, we want to find people who have all the listed hobbies (Reading and Swimming). This is where the division operation comes in.
Division Query
We can express this as "People who have all the hobbies in the 'Hobbies' table."
Implementation 1: Using the first implementation:
Result
| PersonID | Name |
|---|---|
| 1 | Alice |
| 4 | Dave |
This query combines all possible combinations of people and hobbies and then removes those who don't have both Reading and Swimming as hobbies.
Implementation 2:
This query checks each person one by one and verifies if they have all the listed hobbies. The result is the same:
| PersonID | Name |
|---|---|
| 1 | Alice |
| 4 | Dave |
So, both implementations achieve the same result: they find the people who have all the specified hobbies, which is the essence of the division operation in relational databases.
Examples
Integer Division
Suppose you have a table named integers with two integer columns numerator and divisor, and you want to perform integer division on these values:
You can perform integer division using the DIV operator:
Result
| Numerator | Divisor | Result |
|---|---|---|
| 10 | 3 | 3 |
| 7 | 2 | 3 |
| 15 | 4 | 3 |
Change the Operands to a Decimal or Floating-Point Number
Suppose you have a table named sales with two columns: revenue and sales_count. Both columns are stored as integers, and you want to calculate the average revenue per sale as a decimal number:
You can change the operands to decimal numbers using CAST and then perform the calculation:
Output:
| sale_id | decimal_revenue | decimal_sales_count | avg_revenue_per_sale |
|---|---|---|---|
| 1 | 5000.0 | 10.0 | 500.0 |
| 2 | 7500.0 | 15.0 | 500.0 |
| 3 | 10000.0 | 8.0 | 1250.0 |
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Using CAST or CONVERT on Columns
Suppose you have a table named product with a column named price stored as a string (VARCHAR), and you want to convert it to both decimal and integer data types:
Applying CAST and CONVERT:
Output:
| id | name | decimal_price | integer_price |
|---|---|---|---|
| 1 | Widget A | 50.99 | 50 |
| 2 | Widget B | 75.49 | 75 |
| 3 | Widget C | 100.75 | 100 |
Conclusion
- The Division in SQL is used for performing integer division.
- The Division in SQL always truncates the result towards zero. This means it discards any fractional part without rounding. For example, results in 2, not 3.
- It's important to consider data types and potential division by zero errors when using the Division in SQL.
- In database systems that do not support the DIV operator, functions like FLOOR(), CEIL(), or CAST/CONVERT may be used to achieve integer division.
- The Division in SQL is not part of the SQL standard, so its availability and behaviour may differ between database management systems. Some databases, like MySQL, support it, while others may not.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more