Writing SQL · Chapter 5

First steps in SQL

6 min read · 3 questions

SQL (Structured Query Language) is the language used to query and manipulate relational databases. It was created at IBM in the early 1970s and, fifty years later, it is still the common language of every data role.

The query

A query is an instruction sent to the database. It describes what you want to get, not how to get it: the DBMS takes care of the rest.

SELECT ProductID, Category, SubCategory, Brand
FROM product

This query displays the ProductID, Category, SubCategory and Brand columns of the product table. Click ▶ Run to see the result.

• SELECT lists the columns to display;
• FROM names the table they come from.

Queries answer concrete questions: which were the 5 best-selling products last year? Did they keep their rank compared with the year before? How much revenue did they bring in? By the end of this book, you will be able to answer each of these questions.

Clauses

A query is made of clauses: keywords that each have a specific role. In the code blocks they appear in colour. We already know two of them, essential to any read query: SELECT and FROM.

The more complex the question, the more clauses the query will use: WHERE to filter, GROUP BY to group, ORDER BY to sort… We will see them one by one.

The data for this chapter

The examples in the next chapters use the controlGest database, a coffee wholesaler's. It buys products from roasters and manufacturers, then sells them on to retailers (supermarkets, convenience stores, e-commerce, food service).

Three families of tables are enough to get started:

• customer: the customers;
• product: the products sold;
• perform21 to perform26: the sales, one table per year from 2021 to 2026. Each row gives, for one customer, one product and one month, the volume sold, the revenue, the cost and the margin.

The blocks below show the first 3 rows of each table.

The customer table

SELECT *
FROM customer
LIMIT 3

The perform21 table

SELECT *
FROM perform21
LIMIT 3

The product table

SELECT *
FROM product
LIMIT 3

The database has other tables (sales reps, budgets, price lists, promotions, calendar) that we will use later. You can explore them all with the console's Tables button.

Reading a query step by step

Let's go back to the first query:

SELECT ProductID, Category, SubCategory, Brand
FROM product

It asks the database to display the identifier, category, sub-category and brand of every product in the product table. It has two clauses.

SELECT

SELECT lists the columns to include in the result, separated by commas. Here: ProductID, Category, SubCategory and Brand. To display every column without naming them, write SELECT *.

FROM

FROM names the table the data is read from, here product.

In the console, you can write the table name on its own (product) or prefix it with its database name (controlGest.product). Both refer to the same table.

Comments

A comment is text that the DBMS ignores. It explains the intent of a query to whoever reads it next, including you in three months. A commented-out line is no longer executed, which is also a handy way to switch off part of a query while testing.

There are two ways to write a comment:

On one line, with two dashes --: everything after them on the same line is ignored.

-- List of products with their category and brand
SELECT ProductID, Category, SubCategory, Brand
FROM product

Over several lines, between /* and */:

/* List of products with their category and brand.
   Used to check the product master data. */
SELECT ProductID, Category, SubCategory, Brand
FROM product

Laying out a query

A query is read more often than it is written. A few conventions make it readable by the whole team:

• keywords (SELECT, FROM…) in capitals;
• one clause per line, each at the start of its line;
• table and column names written exactly as in the database.

-- Question 5.1: write your query here
SELECT 

SELECT CustomerID, CustomerName, StrategicCustomer
FROM customer

-- Question 5.2: write your query here
SELECT 

SELECT KeyAccount, CustomerName
FROM customer

-- Question 5.3: write your query here
SELECT 

SELECT *
FROM key_account