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.