Jovian
Sign In
Learn practical skills, build real-world projects, and advance your career

Aggregation and Joins with SQL

alt

The following topics are covered in this tutorial:

  • Aggregation, grouping and pagination in SQL queries
  • Mapping functions, arithmetic and working with dates
  • Combining data from different tables using SQL joins
  • Improving query performance with indexes
  • Executing SQL queries using Python and SQLAlchemy

This tutorial is a continuation of the tutorial Relational Databases and SQL.

Setting up MySQL Server Locally

We'll use the MySQL server for this tutorial. Make sure to install the following on your computer:

You'll be asked to set a root password while installing MySQL server.

To interact with the MySQL server via the terminal use:

$ /usr/local/mysql/bin/mysql -u root -p

Depending on your operating system the path /usr/local/mysql/bin/mysql may be different. If you're unable to connect, make sure that the server is running and you're using the correct password.

Alternatively, the MySQL Workbench can be used to interact with a MySQL server (local or remote) via a GUI.

alt

Database Setup

In this tutorial, we'll use the Classic Models database from the previous tutorial. To set up the database locally with sample data:

  1. Download this SQL file
  2. In MySQL Workbench, click "File" > "Open SQL Script" to open the script;
  3. Execute the script to create and populate the database.

Once executed, you should be able to view and browse tables in the "Schema" section of the sidebar. If you face an error, make sure you have MySQL server running.

Classic Models Inc. is a distributor of small scale models of cars, motorcycles, planes, ships trains etc. Products manufactured by Classic Models are sold in toy & gift stores around the world. Here's a small sample of their products (source):

alt

Classic Models has offices around the world with dozens of employees. The customers of Classic Models are typically toy/gift stores. Each customer has a designated sales representative (an employee of Classic Models) they interact with. Customers typically place orders requesting several products in different quantities and pay for multiple orders at once via cheques.

Here's the Entity Relationship Diagram (ERD) for the database:

alt

Aggregation, Grouping and Aliases

SQL provides several functions like COUNT, AVERAGE, SUM, MIN and MAX for aggregating the results of a query.