This January, I spent some time refreshing myself on SQL. I used SQL in graduate classes and an internship - in the context of PROC SQL statements in SAS. Time to learn more!
I've collected some resources and information in this blog post to help those getting started in SQL. Some are resources that I've found useful this month, and others are resources recommended by others. I also touch upon installing a database management system on your own computer at the end of the post.
What is SQL and why is it useful?
SQL (Structured Query Language) is a language used to create, manage, and access data stored in databases called Relational Database Management Systems (RDBMSs). Some of the more popular RDBMSs are MySQL, Oracle, PostgreSQL, and Microsoft SQL Server (MSSQL).
Data analysts and data scientists use SQL to clean, explore, and extract data from datasets that can exceed the capacity of R or other analytical software. SQL can be used in conjunction with other software to analyze data. A recent blog by Bob Muenchen plotted the popularity of different data tools based on the number of job advertisements mentioning each tool. SQL was more commonly mentioned than R, Python, and SAS.
Online tools to learn SQL
The Khan Academy SQL videos cover the basics of the SELECT statement. Short exercises let you practice writing statements using an online SQL interpreter.
SQLZoo guides you through a series of exercises on SQL using an online SQL interpreter. I find that SQLZoo covers more ground and has more examples than Khan Academy.
Mode SQL Tutorial
Though I have not started the Mode SQL Tutorial, I read several articles recommending it. Mode combines written explanations with exercises relating to each concept. Concepts are divided into the beginner, intermediate, or advanced categories.
TutorialsPoint provides a handy SQL reference with concise explanations.
Books on SQL
Learning MySQL: Get a Handle on Your Data
I read chapters 1-7 in this book. Unfortuately, the chapters on installing MySQL were outdated. The book provides an overview of RDBMSs and creating databases before diving into SQL in chapters 5-7. I found chapters 5-7 most useful, though the information in those chapters is freely available online.
SQL Practice Problems
This book provides 57 SQL exercises (varying in difficulty) for readers to solve in MSSQL. I am currently 2/3 of the way through the book, and find it useful for practicing SQL queries. It is written for beginner/intermediate users.
Installing a Relational Database Management System
There are a variety of options for installing a RDBMS on your computer to practice SQL outside of an online SQL interpreter.
Here are some options: MSSQL Express, PostgreSQL, and MariaDB (a variation of MySQL).
I have been practicing SQL with MSSQL Express and MariaDB. MSSQL Express has a fairly straightforward installation process (though I ran into some compatibility issues on Windows 10) and comes with a graphical user interface (GUI). There are a few quirky differences between MariaDB and MSSQL syntax, but the main ideas are the same!
If you decide to work with MariaDB, you might consider installing XAMPP. XAMPP allows you to run MariaDB by command line as well as through a GUI called phpMyAdmin. Apache, MariaDB, a PHP interpreter, and a Perl interpreter are included in the default installation of XAMPP.
Starting MariaDB in XAMPP
XAMPP is one of the software suites recommended in Learning MySQL: Get a Handle on Your Data. It took me awhile to figure out how to start MariaDB in XAMPP so that I could practice SQL on my own. Here is how to do it:
(These instructions are up to date as of 1/31/2018.)
1. First, open the XAMPP Control Panel and press Start on the MySQL module.
2. You have a couple options here. You can either run MariaDB (MySQL) via the command line in the MariaDB monitor, or through the phpMyAdmin GUI.
Running MariaDB through the command line
Running MariaDB via phpMyadmin
3. One note:
MariaDB supports different users. The root user has all privileges. phpMyAdmin logs on as the root user by default. You can log into the root account by typing mysql -u root -p on the command line, then pressing enter when pressed for a password (the root user has no password at the start).
For greater security, you can choose to change the password for the root user from the MariaDB monitor by typing
and replacing 'new password' with your desired password. For more information, see here.
If you do this, you will need to change the configuration file for phpMyAdmin to use MariaDB through phpMyAdmin. See here for a stackoverflow answer that addresses that problem.
4. To exit from MariaDB, type quit in the MariaDB monitor and stop active modules in the XAMPP control panel.
Please feel free to comment if you have any additional comments/suggestions!