Division Operator in SQL

The division operator is used when we have to evaluate queries which contain the keyword ALL.

Some instances where division operator is used are:

1. Which person has account in all the banks of a particular city?
2. Which students have taken all the courses required to graduate?

In above specified problem statements, the description after the keyword 'all' defines a set which contains some elements and the final result contains those units which satisfy these requirements.

Another way how you can identify the usage of division operator is by using the logical implication of if...then. In context of the above two examples, we can see that the queries mean that,

1. If there is a bank in that particular city, that person must have an account in that bank.
2. If there is a course in the list of courses required to be graduated, that person must have taken that course.

Do not worry if you are not clear with all this new things right away, we will try to expain as we move on with this tutorial.

We shall see the second example, mentioned above, in detail.

Table 1: Course_Taken → It consists of the names of Students against the courses that they have taken.

Table 2: Course_Required → It consists of the courses that one is required to take in order to graduate.

Using Division Operator

So now, let's try to find out the correct SQL query for getting results for the first requirement, which is:

Query: Find all the students who can graduate. (i.e. who have taken all the subjects required for one to graduate.)

Unfortunately, there is no direct way by which we can express the division operator. Let's walk through the steps, to write the query for the division operator.

1. Find all the students

Create a set of all students that have taken courses. This can be done easily using the following command.

CREATE TABLE AllStudents AS SELECT DISTINCT Student_Name FROM Course_Taken

This command will return the table AllStudents, as the resultset:

2. Find all the students and the courses required to graduate

Next, we will create a set of students and the courses they need to graduate. We can express this in the form of Cartesian Product of AllStudents and Course_Required using the following command.

CREATE table StudentsAndRequired AS 
SELECT AllStudents.Student_Name, Course_Required.Course
FROM AllStudents, Course_Required

Now the new resultset - table StudentsAndRequired will be:

3. Find all the students and the required courses they have not taken

Here, we are taking our first step for finding the students who cannot graduate. The idea is to simply find the students who have not taken certain courses that are required for graduation and hence they wont be able to graduate. This is simply all those tuples/rows which are present in StudentsAndRequired and not present in Course_Taken.

CREATE  table StudentsAndNotTaken AS 
SELECT * FROM StudentsAndRequired WHERE NOT EXISTS 
(Select * FROM Course_Taken WHERE StudentsAndRequired.Student_Name = Course_Taken.Student_Name 
AND StudentsAndRequired.Course = Course_Taken.Course)

The table StudentsAndNotTaken comes out to be:

4. Find all students who cannot graduate

All the students who are present in the table StudentsAndNotTaken are the ones who cannot graduate. Therefore, we can find the students who cannot graduate as,

CREATE table CannotGraduate AS SELECT DISTINCT Student_Name FROM StudentsAndNotTaken

5. Find all students who can graduate

The students who can graduate are simply those who are present in AllStudents but not in CannotGraduate. This can be done by the following query:

CREATE Table CanGraduate AS SELECT * FROM AllStudents 
WHERE NOT EXISTS 
(SELECT * FROM CannotGraduate WHERE 
    CannotGraduate.Student_name = AllStudents.Student_name)

The results will be as follows:

Hence we just learned, how different steps can lead us to the final answer. Now let us see how to write all these 5 steps in one single query so that we do not have to create so many tables.

SELECT DISTINCT  x.Student_Name FROM Course_Taken AS x WHERE NOT 
EXISTS(SELECT * FROM Course_Required AS y WHERE NOT 
EXISTS(SELECT * FROM Course_Taken AS z 
    WHERE z.Student_name = x.Student_name 
    AND z.Course = y.Course ))

This gives us the same result just like the 5 steps above.