One Month SQL

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.

Download these course files

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.

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.

Select *

SELECT *

FROM users

Select specific columns

SELECT user_login

FROM users

Why do we use the asterisk (*) in SQL?

The asterisk (*) is a wildcard operator that returns all of the available columns in the table.

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.

Select*

Select*

FROM users

Order By

users.user_email DESC

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.

Limit

SELECT * FROM users LIMIT 2

Offset

For example, if you want to retrieve nearly all of the contacts from an Address Book database, but not the first five, then you would use OFFSET 5. Or, if you want to get contacts 3 to 7, you would SELECT * FROM contacts LIMIT 5 OFFSET 2. Besides returning more specific results, LIMITing queries can significantly reduce the time it takes for the query to run, which is a good thing for application performance.

LIMIT and OFFSET are commonly used in the pagination of data. They enable data to be fetched in bits instead of all together at once, which improves the performance of an application and makes for better user experience.

SELECT user_login FROM users LIMIT 10 OFFSET 5

Select Multiple Columns

SELECT user_login, user_pass

FROM users 

Order By

The ORDER BY clause is used to sort the returned data in ascending or descending order. By default, ORDER BY sorts the records in ascending order. You can specify the sort order you want by using the ASC keyword for ascending or DESC keyword for descending.

SELECT * FROM users 

ORDER BY users.user_email DESC

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.

WHERE 

SELECT * FROM products WHERE price = 11.99

SELECT * FROM products WHERE price < 9.99

SELECT * FROM products WHERE book_name = "WILD"

OPERATORS 

= equality

<> non-equality

!= non-equality

< less than

<= less than or equal

> greater than

>= greater than or equal

!> not greater than

BETWEEN

IS NULL

AND

OR

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. 

LIKE

SELECT * FROM products WHERE author LIKE "%Miller%"

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

AGGREGATE FUNCTIONS 

AVG()

COUNT()

MAX()

MIN()

SUM()

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.

STATES QUIZ

Using SQL, and the states.sql dataset, answer the following questions. For each, record your SQL statement in a separate file so that you can check your work in the next lesson.

1. What is the state drink of Maine?

2. How many states have "milk" as their state drink? (show only states where milk is the drink)

3. How do you count the number of states where drinks are milk?

4. What is the first state to initiate a state drink?

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.

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

STATES QUIZ

1. What is the state drink of Maine?

SELECT * FROM `states` WHERE state = "Maine"

2. How many states have "milk" as their state drink? (show only states where milk is the drink)

SELECT * FROM `states` WHERE drink = "Milk"

3. How do you count the number of states where drinks are milk?

SELECT COUNT(drink) FROM `states` WHERE drink = "Milk"

4. What is the first state to initiate a state drink?

SELECT * FROM `states` ORDER BY year LIMIT 1

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.

INSERT

INSERT INTO states (state, drink, year, image)

VALUES ("New Jersey", "Vodka", 2018, "vodka.jpg")

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!!!

UPDATE

UPDATE states

SET drink = "Chocolate Oat Milk"

WHERE state = "Delaware"

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.

DELETE

DELETE FROM states

WHERE state = "Florida"

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.

CRUD

C — Create
R — Read
U — Update
D — Delete

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.

How to create a new SQL table — My checklist

1. Choose the number of columns your database will have. Remember that the first column should always be your "ID."

2. Choose a "Name" for each column.

3. Choose a "Type" (aka. a datatype) and set its length for each column. You'll learn more about this in the following videos.

4. Set collation as "utf8_general_ci" or leave as the default "latin1_swedish_ci" for each column.

5. For your ID (the first column) you will need to set Index to "Primary" and check the box for "A_I"

6. Save

SQL CREATE Table Vocabulary

Primary Key
A field in a table that uniquely identifies each row/record in a database table. Each row must contain unique values, and cannot have NULL values. Each table can only have one primary key.

Unsigned
An integer that can only hold positive numbers

Signed
An integer that can hold both positive and negative numbers

Collation
Defines your character data. Is your data in English? Japanese? Russian? Defining your collation helps your database understand the type of data that it will be holding, in order to more efficiently store and process requests. The default collation in MYSQL is "latin1_swedish_ci" — which is fine to use. Although, in most cases (and assuming you are creating a primarily English language website), you could switch it to "utf8_general_ci" for a slight improvement in efficiency.

AI (Auto-increment)
Tells a table in your database to numerically increase by one (+1) every time a new row is created.

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.

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.

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.

SQL Datatypes

Here are some of the more common datatypes that you'll be using in your database whether it's MYSQL, SQL Server, or Oracle.

TINYINT(Size)
A very small integer. Signed range is from -128 to 127. Unsigned range is from 0 to 255. "Size" specifies the maximum display width (which is 255)

INT(Size)
A medium integer. Signed range is from -2147483648 to 2147483647. Unsigned range is from 0 to 4294967295. "Size" specifies the maximum display width (which is 255).

BIGINT(Size)
A large integer. Signed range is from -9223372036854775808 to 9223372036854775807. Unsigned range is from 0 to 1844674407370955

VARCHAR(256)
A VARIABLE length string that can contain letters, numbers, and special characters. The length can be from 0 to 65535

LONGTEXT
A string with up to 4,294,967,295 characters

DECIMAL(size, d)
"Size" is the total number of digitals. "d" is the number of digits after the decimal point. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0. For money, a suitable recommendation is to use DECIMAL(10, 2)

DATETIME
Used to set the date, Google instructions for more info on this!

TIMESTAMP
The current timestamp (in UNIX time) when the row was created.

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.

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.

JOIN

SELECT posts.post_title, users.user_nicename

FROM posts

JOIN users ON users.ID = posts.post_author

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.

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.

AS Example #1

SELECT p.post_title, u.user_nicename

FROM posts AS p

JOIN users

AS u ON u.ID = p.post_author

AS Example #2

SELECT p.post_title as "Post Title", u.user_nicename, count(*) as "Total Posts"

FROM posts AS p

JOIN users

AS u ON u.ID = p.post_author

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

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.

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.

Use the INSERT INTO statement to insert data into your table

Alternatively, you can use the UI and click on "Insert" and do these manually without SQL code. Either approach is correct.

INSERT INTO purchases (user_id, product_id, quantity) VALUES (2, 2, 1);

INSERT INTO purchases (user_id, product_id, quantity) VALUES (2, 1, 1);

INSERT INTO purchases (user_id, product_id, quantity) VALUES (1, 7, 1);

INSERT INTO purchases (user_id, product_id, quantity) VALUES (3, 9, 1);

My SELECT query to join user purchases

SELECT users.user_nicename, products.book_name

FROM purchases

JOIN users ON users.ID = purchases.user_id

JOIN products ON products.ID = purchases.product_id

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.

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.

LEFT JOIN

SELECT posts.post_title, comments.comment_content

FROM posts

LEFT JOIN comments ON posts.ID = comments.post_id

LEFT JOIN WHERE with IS NULL

SELECT posts.post_title, comments.comment_content

FROM posts

LEFT JOIN comments ON comments.post_id = posts.id

WHERE comments.comment_content IS NULL

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!

FULL OUTER JOIN

SELECT posts.post_title, comments.comment_content

FROM posts

LEFT JOIN comments ON posts.ID = comments.post_id

UNION

SELECT posts.post_title, comments.comment_content

FROM posts

RIGHT JOIN comments ON posts.ID = comments.post_id

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.

UNION

How do you find all the emails among two tables?

SELECT users.user_email

FROM users

UNION

SELECT comments.comment_author_email

FROM comments

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.).

GROUP BY

How do you display the number of comments each post has?

SELECT posts.post_title, count(comments.ID) as "# of Comments"

FROM posts

LEFT JOIN comments ON posts.ID = comments.post_id

Group BY posts.ID

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.

IN / SUBQUERIES
How do you find the emails of users which have blog posts in draft mode?

SELECT users.user_email FROM users

WHERE id IN (SELECT post_author FROM posts WHERE post_status = "draft")

IN

SELECT users.user_email

FROM users

WHERE id IN "caraadams@gmail.com, jamie@gmail.com"

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.

TRUNCATE

TRUNCATE TABLE facebook_users

DROP

DROP TABLE facebook_users

Resources

• Little Bobby Tables (XKCD): https://xkcd.com/327/

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.

User free datasets to build a project using SQL (and Python)

New datasets can be used to practice your new SQL skills, and to build something magical that you can share with me and the rest of the world!

• https://www.data.gov/

• https://www.kaggle.com/

Find some work as a SQL consultant!

Hundreds of people right now are looking to find someone with your SQL skills. Create a profile on UpWork, and market yourself as a contractor with SQL skills. If you land a job let me know in the discussion section of this video!

Dive deeper into SQL with these resources:

• Advanced SQL training with Mode

• Find a SQL 1on1 mentor with CodeMentor

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. 

Resources

• Download MAMP: https://www.mamp.info/

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.

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.

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.