As the amount of the data grows exponentially and terms such as big data become mainstream, the need to handle all this data has increased. Database skills have become some of the most sought-after in the job market today. SQL, which stands for Structured Query Language and is used for relational databases (RDBMS), is one of those skills. In fact, according to Indeed, SQL is mentioned in more job postings than Python, Java, C++, or PHP.
Let’s take a look at the basics of SQL and its abilities. I used MySQL Workbench, which can be found here, and some NFL salary data to make it more understandable. If you want to practice online without installing software, you can use W3Schools.
SQL serves the four main functions of relational databases: create, read, update, and delete. First of all, we let’s make two tables: one for player information and another for salary information, using the “create” statement. We do this by specifying the column name, its data type (integer, variable character, etc), and its definition (i.e. null means the field can be empty).
Now that we have our tables, we need to add records to work with. Using the “insert” statement, I added some players to the player table, and some player salaries to the salary table. Notice how I specified the table, fields, and values we are inserting.
Let’s take a look at what our data looks like, finding this using the “select” statement. I combined two queries into one image for simplicity.
Sometimes we need to pull data from multiple tables, like matching players and their salaries; this is where joins come in handy. A left join returns all the rows in the left table, and matches it with corresponding values in the right table. In contrast, an inner join only returns rows where there is a match in both tables, meaning it will only return rows that are present in both the players and salary table.
Notice the difference: our left join returned all players, even though Chris Borland and Brett Favre are retired and do not have a matching salary. In contrast, they did not show up in our inner join.
Now let’s say we want to view the average salary by position. We can use the AVG function to average the salary, then “group by” position. And if we want to be able to reference this query later on, we can create a view, which saves the query as a virtual table.
Last but not least, if you must destroy everything, drop is the command for you.
Go forth and have no fear.