Excel vs SQL: A Head-to-Head Comparison — Part 2

Part 1 can be found here…

Here are some additional code examples to complement part 1 of the series. These examples illustrate the capabilities and differences between Excel and SQL:

✦ Excel provides a rich set of functions and formulas for working with data. For example, the following formula calculates the sum of the values in a range of cells:

=SUM(A1:A10)

✦ SQL, on the other hand, provides a rich set of operators and functions for querying and manipulating data stored in a database. For example, the following query calculates the total salary of employees in a table:

SELECT SUM(salary) FROM employees;

✦ Excel provides a number of functions for working with text data, such as concatenating strings, extracting substrings, and searching for specific patterns. For example, the following formula concatenates the values in cells A1 and A2, and separates them with a comma:

=A1 & ", " & A2

✦ SQL provides similar functions for working with text data in a database. For example, the following query concatenates the first and last name of employees, and separates them with a space:

SELECT first_name || ' ' || last_name FROM employees;

✦ Excel provides a variety of chart and graph types that you can use to visualize data. For example, the following code creates a simple line chart that shows the values in cells A1:A10:

Sub CreateLineChart()
    ActiveSheet.Shapes.AddChart2(240, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range("A1:A10")
End Sub

✦ SQL does not have built-in support for creating charts and graphs, but you can use other tools and libraries to generate graphs and visualizations based on SQL query results. For example, the following code uses the matplotlib library in Python to create a bar chart that shows the average salary of employees by department:

import matplotlib.pyplot as plt

# Execute SQL query to retrieve data
query = "SELECT AVG(salary), department FROM employees GROUP BY department"
data = execute_query(query)

# Extract values from query results
x = [row[0] for row in data]
y = [row[1] for row in data]

# Create bar chart using matplotlib
plt.bar(x, y)
plt.show()