Playing Columbo With SQL

26 Mar, 05:23 PM

SQLColumbo

Since March 1st I have had the opportunity to successfully learn SQL by Brewster Knowlton’s course offered on Udemy. His course is excellent to say the least. He takes great care to explain the intricacies of SQL and appeals to a broadscale audience; from those who already have technical experience to those who are green to the tech industry. He makes his videos in such a way that you can notice your progress without feeling left behind. He takes the opportunity to stop and offer challenges to the viewer to entice them to interact and learn hands-on with the SQL Server Management Studio application. I am impressed at the scope of this course and by how efficient these concepts are explained.

FIRST DAY:

The First day was devoted to setting up and creating a strong foundation for the rest of the course. We started out with the downloading and installation of SQL Server and SQL Server Management Studio. Once that was completed we moved onto SELECT statements. I learned the difference between literal SELECT statements and Basic SELECT statements, but most importantly how to access and retrieve information in the database using a SELECT statement. He then set up challenges for me to test my knowledge and work with the application myself and become comfortable with simple SQL statements. The goal was to retrieve everything, otherwise referred to as “*”, from a table and then to only retrieve specific columns from that table.

SECOND DAY:

Once I was comfortable with SELECT statements we moved on to filtering data with the WHERE clause. This clause made the query more dynamic and added specificity to the program making data retrieval efficient and user friendly. My experience was then elevated by the ORDER BY clause where information could be organized as well as filtered and retrieved. The process of testing my knowledge while following along to making mistakes that I now know how to solve on my own was encouraging to say the least.

THIRD DAY:

The most interesting part of the course was easily creating queries using multiple tables through the JOIN clause. He goes through great detail in order to properly explain the differences between an INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

  • An INNER JOIN is the culmination of all information shared between two tables. Think of the intersection between two circles in a Venn diagram.
  • A LEFT OUTER JOIN is the entirety of the table you are querying from with the addition of the correlating information from a secondary table. This would be one side of the Venn diagram plus the overlapping middle portion, a full circle.
  • A RIGHT OUTER JOIN is the exact same thing except you are taking the entirety of the joining table and implementing the correlating information from the table you are querying from. This would be the opposing side of the Venn diagram plus the overlapping middle portion, the secondary circle.
  • A FULL OUTER JOIN has to do with all the information from both tables is used and no information is hidden from the resulting query. This would be both circles in the Venn diagram.

After those explanations were made clear and we had the chance to test the new information ourselves, he showed us how to join multiple tables together in a single query using table aliases and linking tables together to create a more readable table.

LAST DAY:

Mr. Knowlton begins the conclusion of his class with a quick introduction to aggregate functions. He explains the uses for SUM, AVG, and COUNT while also specifying the ability to use DISTINCT in your COUNT function and gives examples for when you would want to use it. His dedication to providing real examples for not only how to use the many parts of the SQL server engine, but when and why as well made this course highly valuable to me. He goes on to use aggregate functions to introduce the GROUP BY clause. The GROUP BY clause shows the relations between a specific column and the information connected to those relations in a separate column whether that data be contained within the same table or not. After thoroughly explaining the GROUP BY clause he finishes his course with the HAVING clause. He uses the relationship the WHERE clause has with the SELECT clause to help give insight to the relationship between the HAVING and the GROUP BY clauses. He explains that while the WHERE clause filters out rows of data based on column values the HAVING clause will filter out groups based on aggregate functions.

Tags: