SELECT & FROM - The Foundational Words of SQL
Two essential words to every SQL query: SELECT and FROM. So a typical query looks like this:
[name] is an optional (indicated by ) temporary name for the .
Let's break it down:
You can also specify more than one table in your FROM clause. But in order to do that, we will need to learn about the WHERE clause.
WHERE (AND, IN, OR, NOT, IS NULL)
Be aware that null values interacts with you comparison and word operators in a special way. So you would want to use aggregates whenever you work with NULL values (which we will cover later :D ). You can more information about how to handle null values here.
You can also use to join two tables using Boolean expressions!
Our practice questions will not require you to do this but it's an important tool to keep in mind.
LIMIT, ORDER BY
allows you to limit the number of rows displayed in your query.
allows you to order the query by specific column(s)
The query will first sort the orders by their sold_date. If the sold_date of two orders are the same, the query will look to the prod_id to try and break the tie!
Aggregates (COUNT, DISTINCT, SUM, AVG, MIN, MAX)
An aggregate in SQL is a function that performs a calculation on multiple values and returns a single output. There are many different aggregates that perform a variety of calculations. Here are some of the basic, but very important, aggregates:
is a special aggregate because you can use it WITHIN another aggregate.
Subqueries - Creating Tables from Queries
Subqueries are essentially queries inside queries, temporary tables that we create using SQL queries. These can be very filtering tool.
Let's break down this query: