What Are the Basic SQL Queries?

You can access the data in a database using some basic SQL queries. In this article, we review the fundamentals SQL queries every beginner should know.

Databases are everywhere, and they contain different kinds of data. Almost all of them support SQL, a language that lets you read and manipulate the data inside a database. In this article, I’ll show you the basic SQL queries that you can use to perform many common everyday data tasks.

The best way to learn SQL is our interactive SQL Basics course. It has 120+ exercises, starting with the very simple examples and then moving on to concepts like JOINs and WHERE conditions. You can read more about SQL in our article What Is SQL?. You can also discover why SQL is not difficult to learn.

What Is a SQL Query?

A database is a computer program that stores large amounts of data. Databases are typically organized in tables. Each table stores data for a thing, person, place, or some other object (i.e. describing customers, products, appointments, or employees, for example). Once you have the data stored in a database, you can create reports using this data – for example, you can create a list of the customers who made transactions in the last month.

You create database reports with the help of SQL, or Structured Query Language. SQL is the programming language used to talk to databases. A SQL query is an expression, similar to an English sentence, that defines the set of data to be retrieved from the database. You can think of a SQL query as a question you sent to the database; after that, you expect the database will respond to the question by sending back the data.

In this article, we’ll show you the most basic SQL queries. These can be used to perform common data tasks. You can easily modify these SQL queries and use them on your own database.

Before we start the queries, let’s look at the data we will be using.

Our Example Table

The queries will be based on a table called products. This table stores all the product information for an e-commerce company. You can see the columns of the table in the image below:

The names of the columns are self-explanatory. Perhaps the most important column is prod_code. It stores a numeric code identifying each product (i.e. the ID of each product). Also, it’s good to note that the status column can have only four valid values: ‘available’, ‘sold out’, ‘arriving soon’ or ‘discontinued’.

Now that we know what data to expect, let’s start writing some SQL queries!

Basic SQL Queries

Query 1: Selecting All the Data from a Table

The first query is a simple way to retrieve all the information from a database table. The following query shows all records in all the product table columns:

SELECT * 
FROM products

This SELECT statement uses the * symbol to represent “all the columns”. After that, the FROM clause indicates the table to be read. As we can see below, the result is the complete table: all records and all columns. You can use this query to peek into a table to get to know what kind of data there is in the table.

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:

SELECT 
  name, 
  provider, 
  prod_family, 
  price 
FROM products

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:

SELECT 
  name,
  price - cost AS rentability
FROM products

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:

SELECT 
  name, 
  provider, 
  prod_family, 
  price 
FROM products
WHERE prod_family = ‘Audio’

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:

SELECT 	
  name, 
  provider, 
  prod_family, 
  price 
FROM products
WHERE prod_family = ‘Audio’
ORDER BY name

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:

SELECT 	
  name, 
  provider, 
  prod_family, 
  price 
FROM products
WHERE prod_family = ’Audio’  
  AND status = ’available’
ORDER BY name

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:

SELECT 
  name, 
  price - cost AS rentability,
  stock, 
  (price - cost) * stock AS potential_rentability
FROM products
WHERE price - cost > 200  
AND stock > 25
ORDER BY (price - cost) * stock DESC;

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.

If you already know the basics of SQL, our SQL Basic Cheat Sheet is a handy reference resource. It will give you a concise reminder of SQL syntax with example queries.