The division operator is used when we have to evaluate queries which contain the keyword
Some instances where division operator is used are:
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,
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.
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.
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:
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:
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:
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
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.