Card 6: Advanced SQL

Now that you know the basics of SQL, let's learn some advanced commands to get more interesting insights from your data. We'll learn how to combine data from multiple tables and perform calculations.

1. JOIN: Combining Data

Story: The `JOIN` command is used to combine rows from two or more tables, based on a related column between them.

Analogy: You have two books. One lists farmers and their ID. The other lists crops and the ID of the farmer who grows them. You use the Farmer ID to JOIN the information and see which farmer grows which crop.

Code: `SELECT Farmers.Name, Crops.CropName FROM Farmers JOIN Crops ON Farmers.ID = Crops.FarmerID;`

2. GROUP BY: Summarizing Data

Story: The `GROUP BY` statement groups rows that have the same values in specified columns into summary rows.

Analogy: You want to know how many farmers are in each village. You can GROUP BY village and count the farmers.

Code: `SELECT Village, COUNT(*) FROM Farmers GROUP BY Village;`

3. Aggregate Functions: Making Calculations

Story: Aggregate functions perform a calculation on a set of values and return a single value.

Analogy: You can find the total number of farmers (`COUNT`), the total land they have (`SUM`), or the average crop yield (`AVG`).

Code: `SELECT AVG(Yield) FROM Crops WHERE CropName = 'Rice';`

Key Technical Terms

JOIN: A clause to combine rows from two or more tables.
Foreign Key: A key used to link two tables together.
GROUP BY: A clause to group rows that have the same values.
COUNT: An aggregate function that returns the number of rows.
SUM: An aggregate function that returns the total sum of a numeric column.
AVG: An aggregate function that returns the average value of a numeric column.

Knowledge Check

How would you find the total number of farmers in a village from a 'Farmers' table?

Previous Card Back to Portal Next Card