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

Power BI Journey: Blog #6

In this lesson, the focus is on "Conditional Formatting" which is very much similar to the conditional formatting in MS Excel which I could relate to following the making of the joint reporting system for personnel during my second job. Basically, we click the data columns that we want to be displayed in a tabular visualization.  Next, we select the columns that we will be applying conditional formatting to > Right-click on that column > Select Conditional Formatting > Select from among the options which is more appropriate for your application In this particular exercise, we utilized Background conditional formatting using gradient (applied on the first column of the first table) and rules (IF ELSE which was applied on the second column of the first table), Icons conditional formatting (applied on the second column of the first table), Data bars conditional formatting (applied on the second column of the first table and the fourth column of the second table). In the...
Post a Comment
Read more

SQL Journey: Blog #3

I have reached the end of lesson 2. The final project is entitled "Data dig" where we are given a set of interesting  data sets: NASA astronauts , Superbowl results , Pokemon stats , NBA players , Top movies , Top countries by population , Solar system objects by size , Marvel characters , Furniture store sales , Earned KA badges , Winston's donut logs , Card game results , and NFL draft picks . We are to pick one of those data sets and use advanced SELECT queries to discover things about the data. What sort of questions might one have about that data, like if they were using it for an app or a business idea? Here are some ideas:What are average, max, and min values in the data? What about those numbers per category in the data (using HAVING)? What ways are there to group the data values that don’t exist yet (using CASE)? What interesting ways are there to filter the data (using AND/OR)? Basically, the above given questions will serve as a guide on what we could dig from ...
Post a Comment
Read more