SQL (pronounced "Sequel") is a programming language which was created to manage data held in databases — think of lists of employees with their wages and departments, or grocery stores with their current stock's name, price, and quanity.
Here's a little anecdote to perhaps motivate you to want to learn SQL: I was not originally going to put this at such an early stage in the course (in fact, this was much, much farther down the list) but I began applying for jobs and noticed that nearly every job that I was interested in noted that knowledge of SQL was a plus — which is strange, since some of the jobs seemed to have little-to-nothing to do with data analysis. Even the lower-level jobs selling insurance noted that knowledge of SQL was a plus! Strange. I expected SQL to be a moster to set up (do I need a server? can I install it on Windows? do I have to buy software?) and even more painful to learn, but (luckily!) neither of these predictions proved to be accurate.
Setting Up SQL: Go into Ubuntu's terminal and sudo apt-get install mysql-server. (MySQL is actually installed by default in some distributions of Linux, so you may not even need to install anything).
We need to define a database first, then we have tables inside of it. An example would be something like: the database is called GroceryStore and the tables are Employees and Stock where Employees gives the full name, wage, and SSN of each employee and Stock gives the name, price, and quantity of each item in the store. Another example could be: a database called MyFavoriteThings and one table inside of it could be VideoGames detailing the rank and name of some video games you like and Foods listing the rank and name of some foods you like.
Consequently, we need to make a database first, then populate it with tables. Again from the terminal, we can type mysqladmin -u root -p create namefordatabase where you replace the namefordatabase with whatever you want the database to be called; for now, I called mine Test1. You can make as many databases as you'd like. Note that the command we just put into the terminal says, "run mysqladmin with the user (-u) root, and ask for a password (-p), then create the database." This is sort of how we "sudo" in mysql.
To Start MySQL: In the terminal, type mysql -u root -p. It will ask for the root password; give it. You can make other users in mysql and allow them to log-in and so forth, but for now root is fine for us. If this works, you'll see that you'll be typing after a little mysql>.
The best way I've found to figure SQL out is to just dive right in. Go to the first exercise, which will be The Computer Store and in the Contents box, click Table Creation Code. At the bottom of that section, it will say, ``Click to see MySQL Syntax.'' which is what we want, so click it. Copy-Paste or type all of that into MySQL and it will create two tables. Go to the Sample dataset and copy-paste or type in all of those commands.
You ought to check out and see what this did. The way to view the tables is Show Tables, and the way to view everything in a table is select * from (tablename) where the tablename is the name of the table you'd like to see.
For the rest of the commands, I highly suggest going through the Exercises (which is usually the second section) and looking at the Solution given. This might seem like cheating at first but you will eventually learn about the commands and how they look and act.
|⇐ Back to 3.2||Home||Onwards to 4.2 ⇒|