Left Outer Join & WildCards
Addition to the post, "Student Information", we will do some advanced tasks with the database:
Query the database for tuple of the form (StudentName, MajorName) where the last name of the specified student begins with the letter C.
For this goal, we are going to use Left Outer Join and WildCards. So, I will explain them first and show you how I used those to complete our goal!
1. Left Outer Join:
When we want to join two tables, there are two different ways to do it: Inner Join and Left Outer Join. We will talk about Inner Join in the post, "Advanced SQL - Inner Join, Group By & OrderBy". For this project, we will use Left Outer Join because we want to list all the students names although their MajorID is NULL.
The biggest difference between Inner Join and Left Outer Join is that Inner Join connects tables but without rows that contains NULL. On the other hand, Left Outer Join does contain NULL rows. Thus, when we Left Outer Join two tables such as StudentInfo and MajorInfo on MajorID, we get this result:
2. WildCards:
Have you used the short key, Ctrl+F? Ctrl+F gives you a small search engine on the top of your screen. When you search a key word in the search engine. For instance, I searched all the words with "list all". Likewise, we can search like this using wildcards. In wildcards, there are two types: % and _. % matches with any number of characters and _ matches any single character. For example, %Z_ matches any string of characters ending in a Z and then another character.
3. Application:
Now we are ready to use those concepts for the task!
First thing is to find out what you output you want. So, we will choose StudentName and MajorName after SELECT. Now, we want to get those information from a LEFT OUTER JOIN table out of StudentInfo and MajorInfo tables and they go right after FROM. We want to join them on MajorID attribute, so we put them after ON. Lastly, we want to filter the students by their last name starts with C. Therefore, we have WHERE clause using the wildcard, %____C%.
It sounds complicated when I explain but the code is way simpler.
As our result, you can see all the list of students and their major names who's last name starts with C.
*GitHub: https://github.com/KwakSukyoung/coding/blob/master/ACME/SQL2/sql2.py
Comments
Post a Comment