SELECT Simplified: Practical examples to boost your SQL skills

The SELECT key word is the first statement learned by data analysts, juniors, or interns when they start working with databases using SQL. This statement is used to query data objects from the database, detect errors or perform analysis combined with sanity checks.

The power of SELECT goes far beyond simple data retrieval. Let’s dive deeper into it.👇

1. String Functions

At an advanced level, SELECT allows for multiple levels of function nesting, enabling complex operations in a single query. This opens the door to higher-level data analysis, allowing you to combine multiple functions into one quick SELECT statement.

💡Use case: Imagine the system has been updated with the wrong version for one day. As a result of a bug -  all employee IDs in the production database got inserted in wrapped XXXemployee_idYY, for example, XXX101YY. 

Your task as a data analyst is to find those IDs and clear those values. Since it is a production environment, you have to be extremely careful about your UPDATE statement. Before running the update, you can run SELECT to make sure that your solution is really cleaning columns.

Your testing SELECT:

Your UPDATE:

The code above demonstrates nested functions by cleaning column ‘employee_id’ with a condition ids come from specific date and have length above 6 characters. Remember that the sequence of functions is important because the evaluation starts from the innermost function and proceeds to the outermost one.

👉Task for you! -> What other string functions could I use to clear this column? Hint, use my cheat sheet posted a while ago ;)

2. Dynamic Column Creation (also called 'flag columns')

SELECT can create dynamically new columns during execution using built-in CASE statements, allowing for data categorization and manipulation.

💡Use case: You get an ad hoc question from your client to classify orders from the last quarter based on the value in revenue and used payment method. Here is an example of how your SELECT statement could be implemented:

What can you learn from this query? Columns ‘order_size’ & ‘payment_method’ originally do not exist in the database, while sql is able to create them on the spot even with complex, conditional logic and grouping. In the next step you can save the data and provide to your client. 

PS. Did you notice an error in my query above? (answer in the end🙃)

3. Date Formatting 

With SELECT, you can format dates, extract day, month, year, quarter, or even build your own classification. This is a popular approach to calculate date, especially if you produce a sample of data in Excel and want to pivot on it later. This is very handy in analytics.

💡Use case: Your company has changed its tax submission date. Your task is to implement a new quarter system for transactions from last year. Now, Q1 starts in April.

❗️Important hint: Check your database engine to adjust your date functions accordingly.

🤔Question: what database engine is using example functions? Advise readers to always consult the specific documentation for their database engine when writing complex queries or using advanced features.

4. Data Retrieval, Filtering & Aggregating

Combining SELECT statements with aggregate functions allow you to create quick summaries of revenue, detect duplicates, check number of customers or perform sanity checks(what are sanity checks). Aggregate functions help you derive insights from datasets quickly. For example, you can verify that revenue is not negative. (if it is, something is wrong!).

Did you learn something new? 🤔

Let me know in the comment!👇🥰

🙃A comma should be removed after the 'payment method' column.