Learn SQL

Course Progress

Week 1: Learn SQL in One Month

1. Welcome to Learn SQL at One Month!

Hi 👋! My name is Chris Castiglione, and I'm going to be your instructor for this One Month course! In addition to being a teacher at One Month, I'm an adjunct professor at Columbia Business School where I teach Digital Literacy. In this course, you are going to learn the fundamentals of SQL: how to create a SQL database from scratch, how to use SQL commands, how to fetch data from the database, write data to the database, edit data in the database as well as how to delete data from the database.

2. How Does SQL Work With a LAMP, PHP or Python Tech Stack?

When someone talks about a tech stack they are referring to the technology choices on the frontend, backend and the database. Don’t know what that means? Get ready, we’re going to learn together.

3. 6 Reasons to Learn SQL

SQL is one of the most popular coding languages in the world! It’s used by some of the world’s biggest companies, like Apple, Netflix, Google, Uber, and many others. And SQL is not just for tech companies and developers! Business analysts, product managers, CEOs, and data scientists all benefit from knowing SQL programming.

4. What is SQL?

SQL (pronounced "S-Q-L", or sometimes as "see-quel") stands for Structured Query Language. SQL is the standard language for managing data held in a relational database management system (RDBMS). SQL is probably best understood by looking at Microsoft Excel (or Google Spreadsheets). If you use Excel, you’ll immediately recognize many similarities between Excel and SQL databases. Whereas Excel has spreadsheets, a SQL database has “tables.” SQL outperforms Excel when handling massive amounts of data, as it can rapidly store and search through tens of millions of rows of data. In this lesson, I’ll show you a few examples of how SQL works, and how it improves upon Excel spreadsheets.

5. Getting Setup With a SQL Database

There are several SQL databases (e.g., MySQL, PostgreSQL, Oracle Database, SQLite, Microsoft SQLServer, etc.). In this course, we’ll use MySQL. MySQL is the most popular database on the internet. In this lesson, we’ll begin setting up our MySQL database. I’ll present you with a few options: a shared web hosting plan with Hostgator, a local setup using MAMP (a local Mac server), or a local setup using WAMP (a local Windows server). To manage our database, we’ll use phpMyAdmin, a free and open-source administration tool for MySQL.

6. How to Set Up Web Hosting and a MYSQL Database

In this lesson, we’ll go through the steps for setting up a shared web hosting plan before learning how to create and manage a MySQL database with the phpMyAdmin tool. Be sure to watch the video until the end for instructions on using a coupon code that will get you a month of hosting for only $0.01.

7. How to Use cPanel

In this lesson, I’ll give you a tour of cPanel, a graphical interface control panel that simplifies website and server management.

8. How to Create a New Database

In this lesson, we’ll go through the process of creating a database with the MySQL Databases tool found on the cPanel. We’ll then open the database in phpMyAdmin so that we can manage and interact with it.

WEEK 2: Basic SQL Commands

1. Download Course Files

Please download all the course files and put them on your desktop. You're going to need these in order to follow along with the rest of the course.

2. How to IMPORT Data into a Database

This week, you’ll get hands-on experience writing and executing SQL commands. We’ll start the first lesson of the week by getting familiar with the phpMyAdmin user interface. We’ll then import some data into our database. Along the way, I’ll show you how to troubleshoot one of the most common errors that you’ll likely run into when importing data.

3. How to EXPORT Data from a Database

In the last lesson, we imported data into our database. In this lesson, I’ll show you how to export data from a MySQL database. Knowing how to export data is useful if you want to back up your database, or if you want to use the same SQL commands for different applications.

4. SQL SELECT Statements

There are four things that you can do with data: you can create new data, read existing data, update data, and delete data. These four operations are commonly referred to as CRUD (Create, Read, Update and Delete), or in SQL talk: INSERT, SELECT, UPDATE and DELETE. If that sounds confusing, don’t worry. In this lesson, I’ll help you become a CRUD pro. Let’s get started with the SELECT statement — which retrieves data from a database.

5. Using a SELECT Statement on Big Data

When dealing with big data (i.e., enormous data sets), you need an efficient way to navigate the data. SQL clauses like FROM, WHERE, ORDER BY, LIMIT, GROUP BY, HAVING, DISTINCT make the process of getting to the data that you need much more efficient. In the next few lessons, we’ll look at how to use some of these clauses to filter, sort, and limit the size of returned data.

6. SQL SELECT LIMIT

In this lesson, we’ll look at the LIMIT, OFFSET, and ORDER BY clauses. LIMIT is used to retrieve a portion of rows returned by a query. Use the LIMIT keyword to restrict the number of results that are returned. You can also use the OFFSET command to specify the number of rows that should be skipped in the returned results.

7. SQL WHERE

The WHERE clause is used to filter records according to certain conditions. Conditions are statements that are either true or false. The database evaluates the conditions across all the rows and returns only the rows that fulfill the conditions. Conditional statements use different operators to evaluate data. Examples of these are = (equality), <> (non-equality), != (non-equality), < (less than), <= (less than or equal), > (greater than), >= (greater than or equal), !> (not greater than), BETWEEN, IS NULL, AND, and OR.

8. SQL LIKE

The LIKE keyword is used in a WHERE clause to search for a specified pattern in a column. You can use wildcards (for example, a % represents unknown characters) to search through lots of data to find patterns, words that are close to or similar to those you are searching for.

9. AGGREGATE OPERATORS in SQL

In this lesson, we’ll look at SQL aggregate functions. Aggregate functions perform a calculation on a set of values and return a single value. Some commonly used SQL aggregate functions are: -AVG: calculates the average of a set of values -COUNT: counts rows in a specified table or view -MIN: gets the minimum value in a set of values -MAX: gets the maximum value in a set of values -SUM: calculates the sum of values

10. How PHP and Python Scripts Use SQL

Let’s take a brief pause from learning SQL commands to look at how these commands are used in a typical web application. In this lesson, we’ll go behind the scenes to look at a fully-formed web application that uses SQL on the backend to access the database.

11. SQL Code Quiz!

Time to test your knowledge of what we’ve covered so far! In this lesson, we’ll import some data into a database and try writing SQL commands that return a subset of data described in the presented quiz challenges. I strongly recommend you do the quiz before moving on to the next lesson. Practicing is the best way to retain what you’ve just learned. It also reveals gaps in your knowledge of a covered topic and shows you what lessons you might need to rewatch. If you get stuck on any of the quiz challenges, you’re free to use Google for help! If you are completely stuck, no worries, we go through the solutions in the next lesson.

12. Answers to the SQL Code Quiz

Let’s go through the solutions to the SQL quiz together. Here are the answers.

13. SQL INSERT INTO

The INSERT INTO statement is used to add new data to a database. An INSERT INTO statement can contain values for some or all of its columns. If you aren’t adding values for all the columns in a table, you have to specify the columns you want values to be added to. Let’s learn about SQL’s INSERT INTO command together.

14. SQL UPDATE Statement

The UPDATE statement is used to modify existing database records. You can use it to modify one or more records in a table. When you use UPDATE, you’ll always use the WHERE clause. If you forget to do this and omit the WHERE clause in an UPDATE statement, all records in the table will be updated! YIKES!!!

15. SQL DELETE Statement

The DELETE statement is used to remove records from a table permanently. It can be used to delete one or more records in a table. The WHERE clause can be used with the statement to target specific data for deletion. Let’s carefully delete “Florida” from our data.

16. CRUD Examples

In this lesson, we dive deeper into CRUD operations (Create, Read, Update, and Delete) and take a look at examples of these operations in use in a web application.

WEEK 3: Intermediate SQL Commands

1. How to Create a New Table with SQL CREATE TABLE

In this lesson, you’ll learn how to create a SQL table. We’ll go through the process of planning the structure of a table and creating the table. We’ll create a “users” table from scratch, add columns to it, set the data types of each column, set the collation of each column, and set the primary key for the table. By the end, you’ll have created the SQL schema for our next project.

2. Building a Facebook Users Table

In this lesson, you'll create a Facebook Users table in MySQL to see how the real Facebook users table may have been created. We’ll go through the same steps of creating a table that we practiced in the last lesson, but you’ll be introduced to new concepts like password security using MD5 hashes, database IDs, and passing variables through the URL of a web page into your Python, PHP, or SQL Code.

3. SQL Structure Command

As you’ve seen, the phpMyAdmin interface allows you to view and explore a table’s structure (a table’s structure defines its fields, the data types for each field, the collation of each field, indices set on the table, etc.). You can also use phpMyAdmin to edit a table’s structure. We used phpMyAdmin to create our table. Alternatively, you can also use the SQL command CREATE TABLE to create a table. In this lesson, we’ll look at the underlying CREATE TABLE command used to create our table.

4. Data Types in SQL

In a SQL database, you must set a data type for each column of data. In MySQL, some of the most common data types are VARCHAR, INT, TEXT, LONGTEXT, TIMESTAMP, and DATE. Let’s take a look at some best practices to consider when choosing a data type.

5. SQL Join

JOIN statements are an essential part of writing SQL queries! Joins allow you to combine data from two or more tables into one result. For example, if you want to combine “users” with their “blog posts,” you’ll need to combine data from two tables: users and posts. There are different types of SQL JOIN statements (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and OUTER JOIN), but before we get too deep into JOIN statements let’s start with a simple example of why we have JOINS, and how they work.

6. SQL Join Part II

In this lesson, you’ll write a JOIN statement that grabs a user record from the “users” table and matches it with that user’s posts from the “posts” table.

7. AS in SQL

An AS alias is used to give a table or column a temporary name. Aliases are also useful when you use aggregate functions to come up with a value. Keep watching this lesson, and I’ll show you some real-world examples of when AS aliases can be helpful for optimizing your SQL queries.

8. Assignment #1: JOIN our Comments Table

Jessica left a comment on the blog post "The History of the Internet." Only there's something wrong with the comments table. Figure out how to fix the structure of the comments table, and write the JOIN statement that you would use to display her comment.

9. Answer #1: JOIN our Comments Table

Let’s go through the solution to the last assignment before moving on to another one!

10. Assignment #2: A New Table for our E-Commerce Store

If you want to build an e-commerce store, the first question you need to ask is: How do we manage online purchases? Reviewing our data, you'll see we already have "users" and "products." But where do we record new data that allows us to see when specific users make purchases? Watch this video for your next SQL assignment and to learn more.

11. Answer #2: A New Table for our E-Commerce Store

Let's look at a possible answer to the question: How do I build an online store? We have our users and products. But what's the best way to structure our database and print out an invoice? In this video, I'll take you through my process of outlining a database. If you completed the last assignment, compare your answers with mine. How did you do? Leave a comment on the discussions page.

12. Assignment #3: Display Zero Comments (Make Chris Cry)

In this assignment, I want you to answer two questions: How do you join together two tables? And how do you show which posts have zero comments? Watch this video for your assignment so that you can see what we're looking for and some ideas on how to solve the problem.

13. Answer #3 Display Zero Comments

Which posts have which comments? Which posts have zero comments? Let's join together the "posts" table with the comments "table." In this lesson, I'll show you my answer. What did you get? Leave a comment and share it with me.

WEEK 4: Advanced SQL Commands

1. FULL OUTER JOIN

In this lesson, we’ll take a look at FULL OUTER JOIN. In SQL, the FULL OUTER JOIN command combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. In JOIN commands, the INNER and OUTER keywords are optional, so keep in mind that FULL JOIN and FULL OUTER JOIN are the same commands. Let me show you a few SQL examples!

2. UNION Statement

SQL Joins and Unions are two different ways to take data from multiple tables and display them in one result. You might be wondering what the difference is between SQL Joins and Unions? In this video, I'll answer this question and show you a real-world SQL example of when and how to use a UNION statement.

3. GROUP BY

Let’s count the number of comments for each blog post. To do this, we’ll use the GROUP BY clause. GROUP BY groups rows that have the same values into summary rows. It’s typically used with an aggregate function (e.g., COUNT, MAX, MIN, SUM, AVG, etc.).

4. Subquery and IN

In this lesson, we’ll write a query to find all the blog posts that are in “draft” mode in our database. To do this, you’ll need to know how to write a SQL subquery and how to use the IN operator. A subquery is a SQL query nested inside a larger query. A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

5. Truncate vs. Drop

What’s the difference between TRUNCATE and DROP? In this video, I’m going to demonstrate when to use TRUNCATE and when to use DROP. Truncate will delete all the rows of any given table (but the table will still exist). DROP deletes all the rows of data, and the table will also be deleted. Let me show you how to use these SQL statements. If you stick around for the whole video, I’ll share one of my favorite XKCD jokes! Yes, a SQL joke! Little Bobby Tables.

6. What to do next with your new Superpowers!

Congrats! You've completed One Month’s Learn SQL course! I'm really proud of you. Now, what’s next? In this final video, I'll walk you through a few project ideas for how you can continue to refine your SQL skills. I also have some ideas for how you can use your new SQL skills to land your first job.

7. Get Your SQL Certification

In order to earn your SQL Certification you must watch all the lectures in this course.

Bonus Tutorials

1. Why Do We Need a Server?

In this video, I'm going to show you why we need a server. MYSQL is the database we use in this class. Before you can use MYSQL, you’re going to have to install a server. A hosting service like Hostgator does this for you in the cloud. An application like MAMP runs a server (and a database) locally on your computer.

2. How to Install MAMP (Mac or Windows)

Would you prefer to use MAMP? In this video, I'll show you how to install and use MAMP on either your Mac or Windows machine. MAMP will give you the ability to run a database on your computer and serve up backend coding files like PHP.

3. MySQL with MAMP

If you installed MAMP correctly and you are at localhost:8.8.8.8, you are ready to set up MYSQL on your computer. Let me take you through the process of setting up a new database on your computer.

4. MAMP as a Server

With MAMP you are literally running a server on your computer (pretty cool, eh?). In this video, I'll show you a few more tips and tricks for getting the most out of your local MAMP server. Whether you're new to coding, or more advanced, it doesn't matter — this should be helpful for everyone.