SQL Classes Online

SQL stands for Structured Query Language. It is the language people use to manage data in a relational database, and most of the time that means querying it, or asking the database a question and getting back the matching information.

SQL can also insert new records, update ones that change, delete old ones, back data up and export it for tools like Excel. This course focuses on querying, because that is what analysts, marketers and engineers do with SQL every day.

A database is a collection of tables, and each table stores one kind of thing in rows and columns. The store in this course has a users table, an orders table, a line_items table for every product in an order, and a products table.

If you know Excel, you already know the shape of a database. A whole workbook is like a database, each worksheet tab is like a table, and the columns and rows work the same way. Every price goes in the price column, and every product gets its own row.

SQL comes in a few flavors, one for each database management system (DBMS). PostgreSQL, MySQL, Microsoft SQL Server and Oracle are the most popular. The core language is the same everywhere, so a query you write for one usually runs on the others.

Differences are small and well documented. For example, PostgreSQL and MySQL use LIMIT 10 to return ten rows, while SQL Server uses TOP 10. If a query errors on a new system, the error message and a quick search usually point to the fix.

Companies store far more data than fits in a spreadsheet. Try working in an Excel file with a million rows and it slows to a crawl. Databases run on servers with the storage and power to handle terabytes, and SQL is how you talk to them.

SQL does one job, working with data, and nearly everyone uses it for that job. That standardization is a big reason it spread.

The language has changed little since the 1970s. Databases create tables, insert, update, delete and query rows, and once that model was settled SQL did not need to keep reinventing itself. That makes it smaller and faster to learn than languages like JavaScript, whose libraries change every few years.

SQL is one of the most requested skills in tech job postings, and it sits at the top of the list for data roles. Ask working data analysts which tool to learn first and the answer is almost always SQL.

SQL and Python are the two core languages for data analysts and data scientists. Even outside data teams, people in marketing, operations and product benefit from running their own queries instead of waiting on a report.

Because SQL has stayed stable for decades, it is a skill that lasts. You are not betting on a tool that will be gone next year.

Websites run on databases. WordPress, which powers more than 40% of websites, stores every post, page and comment in a MySQL database, and SQL reads and writes all of it behind the scenes.

Businesses keep customers, sales and operations data in databases. Uber, Netflix and Airbnb run on SQL, and even Google, Amazon and Facebook, which build their own database systems, have data teams that query them with SQL.

The payoff is answering business questions with data instead of guessing. Find every client who has spent more than $5,000 in the past decade so you can reach out again. Count purchases by month and department to spot trends. Total up returns to find a problem product. Most dashboards and reports you use are SQL queries under the hood.

To start writing queries you need a database client. DBeaver is a popular free one, and Mode offers a free public dataset in the browser. The editor on this page works too, with nothing to install.

A database client like DBeaver connects to a database server. You enter the host, port, database name, username and password, and the client shows every table in a navigator panel. The client does not supply data. It is the tool you query it with.

Before writing queries, browse the tables. Learn the table and column names and look at what each one stores, because you will need those exact names in your queries.

Most tables have an id column, a unique number for each row that never changes. Other tables point to it. Each order stores the user_id of the person who placed it, and each line item stores the order_id and product_id it belongs to. These references are how tables relate.

Try it here. The Tables panel next to the editor lists every table and column in the practice database.

Open a new SQL script, make sure it is connected to the right database, and write your first statement. SELECT says what you want, the * asterisk means every column, FROM names the table, and a semicolon ends the statement.

The rows a query returns are called the result set. Querying never changes the table. It only chooses what to show you.

A script can hold many queries. The editor runs the statement your cursor is in, so click into a query and press Ctrl+Enter (Ctrl+Return on a Mac). If you highlight part of a statement, only the highlighted part runs.

Most of the time you don't need every column. List the columns you want after SELECT, separated by commas.

Spell each column exactly as it appears in the database. Asking for emails when the column is email returns an error, which is why exploring the tables first helps.

WHERE keeps only the rows that match a condition. The database checks every row and includes the ones where the condition is true.

Data types matter. Text values, called strings, go inside single quotes, as in tags = 'cyan'. Numbers don't need quotes, and you can compare them with >, <, >= and <=.

A long statement can span several lines. The semicolon marks where it ends. Be careful with selections, though. Highlight only SELECT * FROM products and the WHERE clause is skipped, so you get every product again.

ORDER BY sorts the result set by a column. Sorting is ascending (smallest first) by default. Add DESC to sort descending.

LIMIT caps how many rows come back. Sort by price descending and limit to one row and you have the most expensive product.

Clause order matters, which is the structure in Structured Query Language. LIMIT always goes last. Put it right after SELECT and you get an error.

An aggregate function combines many rows into one value. SUM(quantity) adds up the quantity column across every row, much like SUM in Excel.

Think about what the numbers mean. Each line item's price is the price of one unit. To get revenue you multiply by the quantity first, SUM(price * quantity), or you undercount every order that bought more than one.

If you don't have a database, Mode offers a free public dataset in the browser. Its city_populations table lists cities with their state and population, and this course's practice database has a copy of it.

GROUP BY collapses rows that share a value into one row per group, and an aggregate like SUM runs once per group. Group city populations by state and SUM(population_estimate) gives each state's total.

The clause order is SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. GROUP BY must come before ORDER BY. The instructor puts them the other way around in the video and gets an error, which is a common mistake.