Structured Query Language (SQL) is a powerful tool for working with relational databases. Whether you're a data analyst, business intelligence professional, or involved in reporting, having a solid understanding of SQL commands is essential. Data analysis and reporting are critical components of leveraging data for informed decision-making. When working with relational databases, having a strong grasp of SQL commands is essential for data analysts and professionals. In this article, we will explore 10 essential SQL commands that are indispensable for data analysis and reporting: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, JOIN, UNION, DISTINCT, and LIMIT (or TOP). By mastering these 10 essential SQL commands, data analysts and professionals can harness the power of SQL to extract, manipulate, and analyze data efficiently, ultimately facilitating data-driven decision-making and generating insightful reports.

Table of Contents

• DEFINE THE TABLES
  • SELECT AND FROM
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY
  • JOIN
  • UNION
  • DISTINCT
  • LIMIT (or TOP)
  • LIKE
• Conclusion

DEFINE THE TABLES

First, lets define the example tables that we will use to run these queries.

Table 1: Employees

EmployeeID	Name
1	John
2	Sarah
3	Michael
4	John
5	Don
6	Victoria

Table 2: Customers

CustomerID	Name
1	Jennifer
2	David
3	Emma
4	Victoria

=== Now, let's dive into the SQL commands.

SELECT AND FROM

The SELECT statement is used to retrieve data from a table. In this case, we are selecting all columns from the "Employees" table using the asterisk (*) wildcard character. The FROM clause specifies the table name "Employees" from which we want to retrieve the data.

SELECT * 
FROM Employees;

Result:

| EmployeeID | Name     |
|------------|----------|
| 1          | John     |
| 2          | Sarah    |
| 3          | Michael  |
| 4          | John     |
| 5          | Don      |
| 6          | Victoria |

The query retrieves all columns and rows from the "Employees" table. The result shows the contents of the table, with each row representing an employee and the columns displaying the respective "EmployeeID" and "Name" values. In this case, the result includes six records, each with a unique "EmployeeID" and corresponding "Name." The table contains duplicate names (John and Victoria) that appear in multiple rows. The SELECT query in conjunction with the FROM clause allows us to access and retrieve the data stored within the specified table. It provides a comprehensive view of the records stored in the "Employees" table.

WHERE

The WHERE clause is used to filter data based on specific conditions. It allows you to retrieve only the rows that meet certain criteria. You can use logical operators like AND, OR, and NOT, as well as comparison operators such as =, <>, >, <, >=, and <= to build complex conditions.

SELECT * FROM Employees 
WHERE Name = 'John';

In this case, we are selecting all columns from the "Employees" table where the "Name" column is equal to 'John'.

| EmployeeID | Name |
|------------|------|
| 1          | John |
| 4          | John |

The query retrieves all columns and rows from the "Employees" table where the "Name" column is equal to 'John'. The result shows the records that match the specified condition, with each row representing an employee and the columns displaying the respective "EmployeeID" and "Name" values. In this example, there are two employees with the name 'John' in the "Employees" table, and the query only returns those specific rows that satisfy the condition specified in the WHERE clause.

GROUP BY

The GROUP BY clause is used in combination with aggregate functions to group data based on one or more columns. It enables you to perform calculations and aggregations, such as sum, count, average, and maximum/minimum, on grouped data. This is particularly useful when analyzing data at different levels of granularity.

SELECT Name, COUNT(*) as Count 
FROM Employees 
GROUP BY Name;

The SELECT statement is used to retrieve data from a table, and the GROUP BY clause is used to group rows based on one or more columns. In this case, we are selecting the "Name" column and using the COUNT(*) function to count the occurrences of each name in the "Employees" table.

| Name     | Count |
|----------|-------|
| John     | 2     |
| Sarah    | 1     |
| Michael  | 1     |
| Don      | 1     |
| Victoria | 1     |

The query groups the rows based on the "Name" column and returns the distinct names along with the count of occurrences for each name. The result displays the names and the corresponding count of occurrences. In this example, the "Employees" table contains duplicate names, such as 'John'. The query groups these duplicate names and provides the count of occurrences. It gives us a summary of the distinct names and their frequencies in the table.

HAVING

The HAVING clause is similar to the WHERE clause but is specifically used with GROUP BY. It allows you to filter the groups based on specific conditions.

SELECT Name, COUNT(*) as Count
FROM Employees 
GROUP BY Name 
HAVING Count > 1;

The SELECT statement is used to retrieve data from a table, and the GROUP BY clause is used to group rows based on one or more columns. In this case, we are selecting the "Name" column and using the COUNT(*) function to count the occurrences of each name in the "Employees" table. The HAVING clause is then used to filter the groups based on a condition. In this example, we are filtering the groups to only include those with a count greater than 1.

| Name | Count |
|------|-------|
| John | 2     |

The query groups the rows based on the "Name" column and returns the distinct names along with the count of occurrences, but only for those groups with a count greater than 1. In this example, the "Employees" table contains duplicate names, and the query filters the result to show only the name 'John', as it is the only name with a count greater than 1. The HAVING clause allows us to apply conditions to the groups generated by the GROUP BY clause and filter the results based on those conditions.

ORDER BY

The ORDER BY clause is used to sort the result set based on one or more columns. You can sort the data in ascending (ASC) or descending (DESC) order. Sorting the data can help in presenting results in a meaningful way or identifying patterns in the data.

SELECT * 
FROM Employees 
ORDER BY Name ASC;

The SELECT statement is used to retrieve data from a table, and the ORDER BY clause is used to sort the result set based on one or more columns. In this case, we are selecting all columns from the "Employees" table and ordering the result set in ascending order based on the "Name" column.

| EmployeeID | Name     |
|------------|----------|
| 5          | Don      |
| 1          | John     |
| 4          | John     |
| 3          | Michael  |
| 2          | Sarah    |
| 6          | Victoria |

The query retrieves all columns and rows from the "Employees" table and sorts the result set in ascending order based on the "Name" column. The result displays the records in alphabetical order of the names. In this example, the names in the "Employees" table are ordered in ascending order. The rows are rearranged such that the names starting with 'D' come first, followed by the names starting with 'J', 'M', 'S', and 'V'. The ORDER BY clause allows us to sort the result set based on specified column(s) in either ascending (ASC) or descending (DESC) order.

JOIN

The JOIN command is used to combine rows from two or more tables based on related columns. It allows you to retrieve data from multiple tables by establishing relationships between them. Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, each serving different purposes in data analysis.

SELECT Employees.EmployeeID, Employees.Name, Customers.CustomerID, Customers.Name
FROM Employees
JOIN Customers ON Employees.Name = Customers.Name;

The SELECT statement is used to retrieve data from one or more tables. In this case, we are selecting specific columns from both the "Employees" and "Customers" tables using the JOIN clause. The JOIN clause is used to combine rows from different tables based on a related column, which is specified using the ON keyword. In this example, we are joining the tables based on the "Name" column, which is common to both tables.

| EmployeeID | Name     | CustomerID | Name     |
|------------|----------|------------|----------|
| 1          | John     | 4          | John     |
| 6          | Victoria | 4          | Victoria |

The query combines the rows from the "Employees" and "Customers" tables where the "Name" column matches. The result includes columns from both tables, displaying the "EmployeeID" and "Name" from the "Employees" table, as well as the corresponding "CustomerID" and "Name" from the "Customers" table. In this example, the result shows two rows where there is a match between the "Name" column in both tables. The JOIN operation combines the relevant rows based on the matching names, resulting in a combined result set that includes information from both tables.

UNION

The UNION command is used to combine the result sets of two or more SELECT statements into a single result set. It allows you to merge data from different tables or queries with compatible columns. UNION eliminates duplicates by default, while UNION ALL includes all rows, even if they are duplicates.

SELECT Name FROM Employees
UNION ALL
SELECT Name FROM Customers;

The SELECT statement with the UNION ALL operator is used to combine the result sets of two or more SELECT queries into a single result set, including all rows, even if they are duplicates. In this example, we are retrieving the "Name" column from the "Employees" table and combining it with the "Name" column from the "Customers" table.

Result:

| Name     |
|----------|
| John     |
| Sarah    |
| Michael  |
| John     |
| Don      |
| Victoria |
| Jennifer |
| David    |
| Emma     |

The query combines the names from both the "Employees" and "Customers" tables into a single result set, including all rows from both tables, even if they are duplicates. In this example, the result set contains all names from both tables, including duplicates. The UNION ALL operator allows us to merge the result sets without removing any duplicate rows, providing a unified list of names from both tables.

DISTINCT

The DISTINCT keyword is used to retrieve unique values from a column. It eliminates duplicate values and returns only distinct values. This is particularly useful when analyzing categorical or nominal data and counting unique occurrences.

SELECT DISTINCT Name 
FROM Employees;

The SELECT statement with the DISTINCT keyword is used to retrieve unique/distinct values from a specific column. In this example, we are selecting the distinct values from the "Name" column of the "Employees" table.

Result:

| Name     |
|----------|
| John     |
| Sarah    |
| Michael  |
| Don      |
| Victoria |

The query retrieves the distinct values from the "Name" column of the "Employees" table. The result set includes only the unique names found in the column. In this example, the result set contains distinct names from the "Employees" table, eliminating any duplicate occurrences. The DISTINCT keyword allows us to retrieve a list of unique values from a specific column in the table.

LIMIT (or TOP)

The LIMIT or TOP clause allows you to restrict the number of rows returned in the result set. It is especially helpful when you only need to analyze a subset of data or when generating paginated reports. LIMIT is commonly used in databases like MySQL and PostgreSQL, while TOP is used in SQL Server.

SELECT * 
FROM Employees 
LIMIT 3;

or

SELECT TOP 3 * 
FROM Employees;

The SELECT statement with the LIMIT clause (for MySQL) or the TOP clause (for SQL Server) is used to limit the number of rows returned in the result set. In this example, we are retrieving the top 3 rows from the "Employees" table.

Result:

| EmployeeID | Name     |
|------------|----------|
| 1          | John     |
| 2          | Sarah    |
| 3          | Michael  |

The query limits the result set to the specified number of rows (3 in this example), starting from the beginning of the table. In this example, the result set includes the top 3 rows from the "Employees" table based on the specified limit. The LIMIT (for MySQL) and TOP (for SQL Server) clauses allow us to restrict the number of rows returned, which can be useful for tasks such as pagination or retrieving a subset of data from a large table.

LIKE

The SQL LIKE command is used in conjunction with the WHERE clause to perform pattern matching within a column of a table. It allows you to search for rows that match a specified pattern or substring.

SELECT * 
FROM Employees 
WHERE Name LIKE '%ohn%';

The SELECT statement with the WHERE clause and the LIKE operator is used to filter rows based on a pattern match. In this example, we are retrieving all columns from the "Employees" table where the "Name" column contains the pattern 'ohn'. The '%' symbol is a wildcard that matches any sequence of characters.

Result:

| EmployeeID | Name     |
|------------|----------|
| 1          | John     |
| 4          | John     |

The query retrieves all columns and rows from the "Employees" table where the "Name" column matches the specified pattern 'ohn'. The result includes rows where the name contains 'John' or any other sequence of characters preceding or following 'ohn'. In this example, the result set contains rows where the "Name" column matches the pattern 'ohn'. The LIKE operator allows for pattern matching and enables us to filter rows based on a specific pattern or substring within the column values.

Conclusion

In conclusion, understanding and mastering the 10 essential SQL commands for data analysis and reporting can significantly enhance your ability to work with relational databases and extract meaningful insights from your data. The SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, JOIN, UNION, DISTINCT, LIKE and LIMIT (or TOP) commands form the core toolkit for data professionals working with SQL. With these commands, you can retrieve specific data, apply conditions to filter records, aggregate data using grouping and filtering, sort query results, join tables based on related columns, combine result sets, eliminate duplicate values, and limit the number of rows returned in the result set. By leveraging the power of SQL, you can efficiently manipulate and analyze vast amounts of data, generate insightful reports, and uncover valuable patterns and trends. Whether you are a data analyst, business intelligence professional, or involved in reporting, having a strong command of these SQL commands will enable you to make data-driven decisions and provide valuable insights to your organization. Continuously honing your SQL skills and staying up-to-date with advanced techniques will empower you to tackle complex data analysis tasks and contribute to the success of your data-driven projects. Embrace these essential SQL commands and unlock the true potential of your data.