Skip to main content

SQL Journey: Blog#4

I am now at Lesson 3. Yey! Glad that I was able to reach here despite being skeptical at first if I can sustain learning a new programming language considering I have Coursehero tutoring on the side.

I decided to write this blog since upon engaging with the lesson on "JOIN" clause, I can't seem to grasp the lesson. It talked about "cross join" and "inner join" and how inner join is more useful than the other. According to w3schools, the following is the difference between the two.



I am given the following tables:

QUERY RESULTS

"student_grades" table:

idstudent_idtestgrade
11Nutrition95
22Nutrition92
31Chemistry85
42Chemistry95
"students" table:

idfirst_namelast_nameemailphonebirthdate
1PeterRabbitpeter@rabbit.com555-66662002-06-24
2AliceWonderlandalice@wonderland.com555-44442002-07-04

If I do a cross-join, this will be the result.

Code:

SELECT * FROM student_grades, students;

Query results:

idstudent_idtestgradeidfirst_namelast_nameemailphonebirthdate
11Nutrition951PeterRabbitpeter@rabbit.com555-66662002-06-24
11Nutrition952AliceWonderlandalice@wonderland.com555-44442002-07-04
22Nutrition921PeterRabbitpeter@rabbit.com555-66662002-06-24
22Nutrition922AliceWonderlandalice@wonderland.com555-44442002-07-04
31Chemistry851PeterRabbitpeter@rabbit.com555-66662002-06-24
31Chemistry852AliceWonderlandalice@wonderland.com555-44442002-07-04
42Chemistry951PeterRabbitpeter@rabbit.com555-66662002-06-24
42Chemistry952AliceWonderlandalice@wonderland.com555-44442002-07-04

Basically, what happened was every row in the student_grades table was joined with the contents of the students table. Since there are 4 rows in the student_grades table and 2 rows in the students table, when we multiply 4 by 2, we have 8 which is equal to the number of rows when we cross-joined the two tables. If we observe, there are incorrect rows in this table. For example, on the second row, Alice did not score 95 on the test on Nutrition rather she scored 92. Therefore, using cross-join did not preserve the relationship of the data based on common columns. This is not our purpose of joining the two tables. What we need to do is to output a table where the name of the student, email address, test, and grade are displayed. Therefore, we need to use "inner join".

Code:

SELECT * FROM student_grades, students

    WHERE student_grades.student_id = students.id;

Query results:

idstudent_idtestgradeidfirst_namelast_nameemailphonebirthdate
11Nutrition951PeterRabbitpeter@rabbit.com555-66662002-06-24
22Nutrition922AliceWonderlandalice@wonderland.com555-44442002-07-04
31Chemistry851PeterRabbitpeter@rabbit.com555-66662002-06-24
42Chemistry952AliceWonderlandalice@wonderland.com555-44442002-07-04

Notice that we have only 4  rows in the output. Now, this is fit for our purpose. We have now mapped out the test, grade, names, and email addresses correctly. But notice that there are still redundant columns such as student_id and id, which refer to the same quantity. To improve this, we have:

Code:

SELECT first_name,last_name,email,test,grade FROM student_grades, students

    WHERE student_grades.student_id = students.id;

Query results:

first_namelast_nameemailtestgrade
PeterRabbitpeter@rabbit.comNutrition95
AliceWonderlandalice@wonderland.comNutrition92
PeterRabbitpeter@rabbit.comChemistry85
AliceWonderlandalice@wonderland.comChemistry95

Now, this is a lot better than the previous output tables. A much better way to do this is to use the "JOIN" clause.

Code:

SELECT first_name, last_name,email,test,grade FROM students

    JOIN student_grades

    ON student_grades.student_id = students.id

Query results:

first_namelast_nameemailtestgrade
PeterRabbitpeter@rabbit.comNutrition95
AliceWonderlandalice@wonderland.comNutrition92
PeterRabbitpeter@rabbit.comChemistry85
AliceWonderlandalice@wonderland.comChemistry95

Note: In instances where columns from the different tables have the same names but different meaning, we must specify the table from which we took the data. For example, it will be much better to write:


SELECT students.first_name, students.last_name,students.email,student_grades.test,student_grades.grade FROM students

    JOIN student_grades

    ON student_grades.student_id = students.id;

###

Labels:

Comments

Post a Comment

Popular posts from this blog

Privacy Policy of ShinStats: descriptives calc

Privacy Policy Shin Nix built the ShinStats app as an Ad Supported app. This SERVICE is provided by Shin Nix at no cost and is intended for use as is. This page is used to inform visitors regarding my policies with the collection, use, and disclosure of Personal Information if anyone decided to use my Service. If you choose to use my Service, then you agree to the collection and use of information in relation to this policy. The Personal Information that I collect is used for providing and improving the Service. I will not use or share your information with anyone except as described in this Privacy Policy. The terms used in this Privacy Policy have the same meanings as in our Terms and Conditions, which are accessible at ShinStats unless otherwise defined in this Privacy Policy. Information Collection and Use For a better experience, while using our Service, I may require you to provide us with certain personally identifiable information. The information that I request will be retaine...
Post a Comment
Read more

Gears Update

It has been a while since my last post and many things have happened since then. For one, I decided to upgrade my laptop as I saw it fit for the direction I am moving towards particularly on data analytics. It's been almost 10 years since I bought "Julian", my first work laptop, and there were so many milestones that we shared together. I bought my first laptop during my second job in Taguig City. It served as an extension of myself as I work to earn for my family particularly in helping my siblings with their education as I am the eldest and breadwinner of the family. That laptop was able to create a joint personnel reporting system excel file which was used by the Philippine Army to be able to account their personnel on a national level during my stint as an Engineer / Researcher in the aforementioned organization. Julian was my laptop when I finished my Master's degree at the Ateneo de Davao University where I also created the Programmable Logic Controller Trainer ...
Post a Comment
Read more

SQL Journey: Blog#10

So far, we are only "reading" from a given database or table using the SELECT command of SQL. In today's lesson, we will now start "writing" into a given database using the UPDATE and DELETE commands. Challenge: Dynamic Documents Given data: CREATE table documents (     id INTEGER PRIMARY KEY AUTOINCREMENT,     title TEXT,     content TEXT,     author TEXT);      INSERT INTO documents (author, title, content)     VALUES ("Puff T.M. Dragon", "Fancy Stuff", "Ceiling wax, dragon wings, etc."); INSERT INTO documents (author, title, content)     VALUES ("Puff T.M. Dragon", "Living Things", "They're located in the left ear, you know."); INSERT INTO documents (author, title, content)     VALUES ("Jackie Paper", "Pirate Recipes", "Cherry pie, apple pie, blueberry pie."); INSERT INTO documents (author, title, content)     VALUES ("Jackie Paper", "Boat Supplies...
Post a Comment
Read more