Where to Start AWS ( Amazon Web Server)

Query 2: Selecting Specific Columns from a Table

You can also only show a few columns from a table. Let’s suppose we want a price list with the columns name, provider, prod_family and price. The query is very simple:

We specified the columns we want to extract from the table: name, provider, prod_family, and price. In the second line of the query, we use the FROM clause to indicate the table to read. The result is:

Query 3: Doing Simple Computations

We can do computations within a SQL query. These let us obtain a calculated value derived from other values stored in the database table.

Let’s calculate products’ rentability amounts using the price and cost columns and then show the amount alongside the product name. Here’s the query we’d use:

The second line of the query has the arithmetic expression price - cost. The AS keyword allows to assign the name rentability to the result of this arithmetic expression. The new information is shown as a column in the query result:

Query 4: Filtering Data

Usually you don’t want to see all the data in a database. You want to see only the rows that match certain criteria: products from one product family or with a price below a certain threshold. In SQL, this is called filtering the data.

The WHERE clause filters the data you want from the rest of the data in the table. It extracts records that match certain conditions. For example, suppose we want a price list for products in the “Audio” family. We can use the following query to get the result:

The WHERE clause indicates that we only want to obtain those records where the condition prod_family = ‘Audio’ is TRUE. Other records (those belonging to the Phones or Tablets families) will not be part of the result. In other words, we are filtering records from the table products.

Query 5: Sorting Data in the Query Result

Often you want to see query results in a certain order, such as products from the cheapest to the most expensive or product names in alphabetical order. The clause ORDER BY is used to define how the records will be ordered in the query result.

Let’s suppose we want to order the products from our previous query by name. Note how we use ORDER BY in this query:

We can see the same records in alphabetical order by product name:

Combining It All to Solve Business Problems

You can combine all the SQL features shown in this article into one query. Combining SQL features in this way creates a more complex query that can solve real business problems.

The result of the last query in the previous section is similar to a price list of Audio family products. However, it shows products that are discontinued or sold out. In the next query, we’ll add a second logical condition in the WHERE clause to include only products that are currently available:

The conditions in the WHERE clause are connected with the AND logical connector; thus, the result will include only records where both logical conditions are TRUE. We can see this in the following results:

In the next example query, we’ll try combining all the SQL clauses covered in this article. Let’s suppose that we are planning a marketing campaign and we need to choose two products to promote. We want products with a rentability greater than $200 and with a stock greater than 25 units. We will also order the report by a metric called “potential rentability” which is calculated with the formula: (price - cost) * stock. Here is the query:

The previous query calculates two metrics. One metric is rentability which is calculated using the formula price - cost. The other metric is potential_rentability, which is calculated using the formula (price - cost) * stock. In the WHERE clause, the query specifies two conditions: the records to return must have a rentability greater than 200 and a stock greater than 25 units. Finally, the result of the query is ordered by the metric potential_rentability in descending (largest to smallest) order. Below is the result:

Continue Learning Basic SQL Queries!

In this article, we covered the basic SQL queries that can be used to perform simple data tasks. However, SQL offers many more clauses and techniques that allow you to do more operations on data.

Our interactive SQL Basics course is the best way to master foundational SQL. It contains over 120 exercises that cover basic SQL queries (like the ones shown in this article) and other SQL constructions that allow you to combine data from multiple tables and compute various statistics.