Intermediate SQL Concepts for Data Analysis

Now that you’ve got a handle on the basics of SQL, it’s time to dive deeper into some intermediate concepts. These will help you perform more complex data analysis and manipulation tasks. In this article, we’ll cover:

• Aggregate Functions
• Grouping Data
• Subqueries
• Using SQL Functions
• Views
• Indexes

1. Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include:

• COUNT(): Counts the number of rows.
• SUM(): Calculates the sum of a numeric column.
• AVG(): Calculates the average value of a numeric column.
• MAX(): Returns the maximum value in a column.
• MIN(): Returns the minimum value in a column.

Examples:

To count the number of books:

SELECT COUNT(*) FROM books;

To find the average published year:

SELECT AVG(published_year) FROM books;

2. Grouping Data