A Refreshing SQL

So becoming a part of a bigger project has induced the need for me to re-learn some old skills.

I have not written my own SQL queries since my DATA 210 class 2½ years ago so a crash-course was needed. I started by visiting the w3schools tutorial which worked great for me since I am re-learning as opposed to learning from scratch. Additionally, I needed to review regular expressions and how to actually do coding with them (I have done regex in the past by hand, but I never actually programmed them myself). I found a good reference site through Oracle.

Some of the main things that I took away were as follows:

• SQL Joins

Joins are probably one of the harder techniques for people to grasp when it comes to query languages. Inner joins are the most common form of joins (in SQL the JOIN command defaults to inner join). An inner join on 2 tables will result in a single table with data from both tables. The join requires one field from each of the tables to be equivalent as that would be the feature which is being joined "ON". So if you have a foreign key in a table, then you know that those two tables can be joined upon because of the constraints imposed upon foreign/primary keys. Outer joins differ from inner joins in that there will be a lot of empty data if an outer join is performed. There are different types of outer joins: left, right, and full. A left join will use the "left" table as the primary table being used while right corresponds to "right". So let's say we have a Persons table and an Amazon Orders table. We could easily have people in the Persons table that have not placed an Amazon Order. So the people in the Amazon Orders table will all be encapsulated by the Persons table. If we do a left join, then we will have one big table that has Persons listed with their Amazon Orders attributes added on to the end. For people that did not have Amazon Orders, there would be a NULL or empty value for those attributes.

• Altering Tables

Altering tables is a simple, yet vital functionality in database usage and administration. This can range from dropping tables or deleting attributes to designating new keys and features for a table. There is not much explanation needed to understand this.

• Populating Tables with the INSERT TO command

Creating new tables is insanely important when dealing with databases. Typically, though, you are not creating a table from scratch. You may want to take features from many tables, perhaps unstructured with a bunch of redundancies, and create a new, normalized table that prevents disk space waste and allows for easy querying. With the INSERT TO command you designate a table from which you want to grab information (as well as selecting the specific attributes you desire, if not all of them) and select a new table for which to send the information. 

Music listened to while blogging: Schoolboy Q

I'm going to start linking the artist's name directly to a song of theirs, to add a little interactivity to my music updates.