SQL | DIVISION

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.

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

Table B: Hobbies

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

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:

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

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:

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: