Photo by editor
# Introduction
Data analysts need to work with large amounts of information stored in databases. Before they can create reports or find insights, they must first pull the right data and prepare it for use. This is where SQL (Structured Query Language) comes in. SQL is a tool that helps analysts retrieve data, clean it and organize it in the desired format.
In this article, we’ll look at the most important SQL queries that every data analyst should know.
# 1. Selecting data with select
Select Statement is the basis of SQL. You can select or use specific columns * To return all available fields.
SELECT name, age, salary FROM employees;This query only draws namefor , for , for , . ageand salary column from employees Table
# 2. Filtering data with where
where It annoys people who match your circumstances. It supports comparison and logical operators to create precise filters.
SELECT * FROM employees WHERE department="Finance";Where the clause returns only the employees belonging to the Finance Department.
# 3. Sorting results with order
Order by The clause sorts the query results in ascending or descending order. It is used to rank records by numeric, text, or date values.
SELECT name, salary FROM employees ORDER BY salary DESC;This query sorts the employees by salary in descending order, so the highest paid employees appear first.
# 4. Remove duplicates with separate
separately The keyword returns only the unique values ​​from the column. This is useful when creating neat lists of categories or attributes.
SELECT DISTINCT department FROM employees;Removes separate duplicate entries, returning each department name only once.
# 5. Limiting results with limits
limit The clause limits the number of rows returned by a query. It is often paired Order by To display high results or sample data from large tables.
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;It retrieves the top 5 employees with the highest salaries by combining them Order by with the limit.
# 6. Collection with group by
By group Rows of clauses that share the same values ​​in specified columns. It is used in aggregate functions such as SUM()for , for , for , . AVG()or COUNT() To prepare the summary.
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;Group organizes queues by department, and AVG(salary) Calculates the average salary for each group.
# 7. Filtering groups with being
to be Clause filters group the results after aggregation. It is used when conditions depend on aggregate values, such as sums or averages.
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;The query counts the number of employees in each department and then filters to keep only departments with more than 10 employees.
# 8. Join the tables with joins
join clause joins rows from two or more tables based on the corresponding column. It helps in retrieving associated data, such as employees with their departments.
SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON e.dept_id = d.id;Here, the join associates the employees with their matching department names.
# 9. Combination of results with union
Union Combines the results of two or more queries into a single dataset. It automatically removes duplicates unless you use them UNION ALLwhich holds them.
SELECT name FROM employees UNION SELECT name FROM customers;This query joins the two names employees And customers Tables in the same list.
# 10. String functions
String functions in SQL are used to manipulate and transform text data. They help with things like concatenating names, changing case, trimming spaces, or extracting parts of a string.
SELECT CONCAT(first_name, ' ', last_name) AS full_name, LENGTH(first_name) AS name_length FROM employees;This query creates a full name by concatenating the first and last names and calculates the length of the first name.
# 11. Date and time functions
The date and time functions in SQL let you work with temporary data for analysis and reporting. They can calculate differences, extract components such as years or months, and adjust dates by adding or subtracting intervals. For example, DATEDIFF() with the CURRENT_DATE Can measure duration.
SELECT name, hire_date, DATEDIFF(CURRENT_DATE, hire_date) AS days_at_company FROM employees;It calculates how many days each employee has been with the company from today.
# 12. Creating new columns with case
Case Expressions create new columns with conditional logic, similar to IF-else statements. It lets you dynamically categorize or change data within your queries.
SELECT name,
CASE
WHEN age < 30 THEN 'Junior'
WHEN age BETWEEN 30 AND 50 THEN 'Mid-level'
ELSE 'Senior'
END AS experience_level
FROM employees;The case statement creates a new column called experience_level Based on age limits
# 13. Handling Missing Values ​​with Coalesce
coalesce Handles missing values ​​by returning the first non-null value from the list. It is usually used to replace NULL Fields with default values, such as “N/A”.
SELECT name, COALESCE(phone, 'N/A') AS contact_number FROM customers;Here, Coalesce replaces missing phone numbers with “N/A”.
# 14. Subqueries
Subsets are nested queries within another query to provide intermediate results. They are used WHEREfor , for , for , . FROMor SELECT Clauses to dynamically filter, compare, or create datasets.
SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);This query compares each employee’s salary to the company’s average salary using a nested subset.
# 15. Window functions
Window functions perform calculations on a set of rows while still returning individual row details. They are commonly used for sorting, running totals and comparing values ​​between rows.
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees; RANK() The function assigns a salary-based classification to each employee without grouping the rows.
# The result
Mastering SQL is an extremely valuable skill for any data analyst, as it provides the foundation for extracting, transforming and interpreting data. From filtering and aggregation to joining and maintaining datasets, SQL empowers analysts to turn raw data into meaningful insights that drive decision-making. By mastering the essential questions, analysts not only streamline their workflow but also ensure accuracy and scalability in their analyses.
Jayta Gulti A machine learning enthusiast and technical writer driven by his passion for building machine learning models. He holds a Master’s degree in Computer Science from the University of Liverpool.