We recently had our first exposure to SQL at the Data School where we received a introduction to all things SQL.

Here are some useful queries and thoughts from that intro:

  • Select Statements: The SELECT statement forms the backbone of SQL queries, enabling you to retrieve specific columns or entire datasets from a table. For example, to retrieve all columns from the “Customers” table, you would use the following query:

SELECT * FROM Customers;

  • Select Distinct: The SELECT DISTINCT statement allows you to retrieve unique values from a specific column. Let’s say you want to retrieve unique countries from the “Customers” table:

SELECT DISTINCT Country FROM Customers;

  • Text and Numeric Fields: In SQL, text fields are enclosed in single quotes (”) or double quotes (“”) to denote character strings. Numeric fields, on the other hand, are used without quotes. Square brackets ([]) are optional and used to escape reserved words or when column names contain special characters.
  • Where Clause and Operators: The WHERE clause is used to filter results based on specific conditions. You can employ various operators such as “=”, “<>”, “>”, “<“, “>=”, “<=”, etc. For example, to retrieve customers from a particular city, you can use the WHERE clause with the equality operator:

SELECT * FROM Customers WHERE City = ‘Melbourne’;

  • The LIKE operator, coupled with wildcards (% and _), enables pattern matching within data. It’s particularly handy when searching for specific strings. Some handy use cases are:

%b% = contains anything with b
%town% = contains anything with town
%b = has to end with b
b% = starts with b

  • The IN operator allows you to specify multiple values in a WHERE clause. It simplifies queries where you want to match against several options. For example, to retrieve customers from the USA or Canada, you can use the IN operator:

SELECT * FROM Customers WHERE Country IN (‘Australia’, ‘England’);

  • The AND and OR operators are used to filter recrods based on more than one condition
    – AND operator displays a record if both the first condition AND the second condition is true
    – The OR operator displays a record if either the first condition OR the second condition is true

 

  • Order By and Select Top: The ORDER BY clause arranges the result set in ascending or descending order based on a specified column. To retrieve the top five highest purchase amounts from the “Customers” table, you can use the ORDER BY and LIMIT clauses:

SELECT * FROM Customers ORDER BY PurchaseAmount DESC LIMIT 5;

Alternatively you can use SELECT TOP to return a set number of records. This can be useful for large tables with thousands of records when returning the whole table would be quite large

SELCT TOP 10 FROM Customers

  • Aggregate Functions return a single value calculated from values in a column. Aggregate functions include SUM(), AVG(), COUNT(), COUNT(DISTINCT), MIN(), MAX(). Aggregate values are often used with aliases and a Group By Statement if you want to aggregate at a dimension level

SELECT
SUM(Quantity) as Quantity,
SUM(Sales) as Sales,
SUM(Profit) as Profit,
(SUM(Profit)/SUM(Sales))*100 as Profit_ratio
GROUP BY Ship_Mode
FROM Customer_Orders

Those were the main queries that we learnt, so feel free to have a play around with these.

We also used dBeaver which is a really handy database software tool which had some great features like auto-filling queries similar to Visual Studio Code for coding and web development.

Tim Fawcett
Author: Tim Fawcett