5 SQL queries you must know (with examples) — PART 1

4 min readOct 13, 2022

In this article, we will discuss some of the problems of database searching. We will take a hypothetical construction company as an example and use open-source code from Github.

The database description.

There are three interconnected tables — suppliers, projects, and employees. Some projects are in the examination stage; some were submitted and released. Each project has a name, id, location, start date, end date, and target date — when a building should be ready. You can also find the main and additional costs and types of agreements.

So, the first question that might interest your stakeholders is, what are the project costs.

(1) total costs from Jan 1, 2018 till Jan 30, 2026 for a supplier ‘Apple’

The first picture shows a table with all suppliers where the company Apple has supplier_id 502 and project_id 119.

Table ‘Suppliers’ with a supplier Apple GmbH

Two projects in the second picture are highlighted, particularly NewApartments and Sportbuilding, whose construction implements Apple. If we look at the main costs for these two projects, we can see that the amount is equal to 50300000 USD.

Table ‘Projects’ with the projects for supplier_id 502

Now, lets discuss how to solve this problem in SQL in three steps

Step 1 . Calculate total costs with the aggregation formula — SUM

The information about costs is represented in the ‘Projects’ table. We should use the statistical function SUM to find the total amount.

select SUM(main_costs) as total_costs from PROJECTS;

This enables us to get our total main costs for all projects, suppliers, and dates.

Step 2. Specify date range — Where Between clause

To find a specific date, we need to use ‘Where’ when talking about the range ‘Where between’ clause. Since this information is stored in the ‘Projects’ table, we retrieve the data with this clause.

select SUM(main_costs) as total_costs from PROJECTS where target_date between ‘01–01–2018’ and ‘01–30–2026’;

Total Costs (main costs) for a specific period of time

Also acceptable, but still, it represents data for all suppliers. How can we improve our query for the ‘Apple GmbH’ company?

Step 3. Combine two tables to show costs for Apple — JOIN

The company ‘Apple’ name might be found in the supplier_name column.
If you need to retrieve the information from two tables, like in our example, you could use the JOIN function. I will use LEFT JOIN to return all records from the left table and the matching records from the right table.

Our matching records will be the foreign key ‘supplier_id’. When implementing a JOIN clause, you need to specify a table name with our dimensions — suppliers.supplier_name instead of supplier_name. Group by function is required to finish the aggregation function.

SELECT suppliers.supplier_name,SUM(projects.main_costs) as average_costs
FROM suppliers AS suppliers
LEFT JOIN projects AS projects
ON projects.supplier_id=suppliers.supplier_id
where suppliers.supplier_name = ‘Apple_GmbH’ and target_date between ‘01–01–2018’ and ‘01–30–2026’
group by suppliers.supplier_name;

The problem is solved.

(2) the number of additional agreements for a Project ‘LabCampus’ started in the last two years

Let’s first add two more values to our table to get additional agreements for one project.

insert into PROJECTS values
(110,’LabCampus’,’Munich’,’Additional_agreement’,’Agreed_on_price’,’Submitted’,’01–01–2015',’05–05–2024',255000,NULL,’08–09–2027');
insert into PROJECTS values
(111,’LabCampus’,’Stuggart’,’Additional_agreement’,’Agreed_on_price’,’Released’,’01–01–2021',’05–05–2024',255000,NULL,’08–09–2029');

We now have three rows with one project Labcampus that contains two additional agreements. Two projects were started in 2021–2022, and one more project in 2015.

Step 1. Find contract types by projects

Let’s first extract the information about all projects with contracts and start dates.

select project_name,contract_type,start_date from projects;

Step 2. Specify the date and the project

select project_name,contract_type,start_date from projects where project_name = ‘LabCampus’ and contract_type = ‘Additional_agreement’ and start_date >=’01.01.2021';

Step 3. Count number of contracts — COUNT

We can calculate the number of rows using the aggregation function COUNT.

select count(project_id) as Projects_LabCampus_Ad_agreement from projects where project_name = ‘LabCampus’ and contract_type = ‘Additional_agreement’ and start_date >=’01.01.2021';

In the second part, we will solve the following problems:

How many projects were rejected, accepted, and released from 9 September to 9 October 2022
Find all contracts with agreed_on_price status for the weeks 26–30 in 2022
Find all subcontractors in Munich

Thank you for reading.

Have a nice day.