SQL queries you must know (with examples) — PART 2
I published an article about SQL queries Part 1 on Medium a couple weeks ago. This tutorial will provide three more solutions to basic queries across a construction sector database.
We will use the open-source code ‘Construction Database Schema’ from Github. In the database, you can see three tables — suppliers, projects, and employees. Each project has a name, id, location, start date, end date, and target date — when a building should be ready. Some projects are in the examination stage; some were submitted and released.
We will answer the following questions:
- How many projects were rejected, accepted, or released from 9th September to 9th October 2022
- Find all contracts with agreed_on_price status for the weeks 26–30 in 2022
- Find all subcontractors in Munich
Obviously, the main information is stored in a table ‘Projects’. So, let’s start with the first issue. Assuming your stakeholders would like to understand how many projects have been accepted, rejected, or released for a specific time frame.
1. How many projects were rejected, accepted, and released from 9th September to 9th October 2022
You can select project_id from your table to represent the list of all released projects with a WHERE clause.
select project_id as projects_released from projects where status_work = ‘Released’;
The number of projects might be calculated using the aggregation function COUNT.
select count(project_id) as projects_released from projects where status_work = ‘Released’;
You can use the WHERE OR clause if you need to find all projects with status released and submitted. So, we have the result of 11 released and submitted projects.
select count(project_id) as projects_released_submitted from projects where status_work = ‘Released’ or status_work = ‘Submitted’;
After we have found all projects answering the question about status, we can specify the dates. Since we need to retrieve data for two criteria, such as ‘status_work’ and ‘date’ we can use AND clause.
select count(project_id) as projects_released_submitted from projects where status_work = ‘Released’ or status_work = ‘Submitted’ and end_date between ‘9–09–2022’ and ‘10–9–2022’;
2. Find all contracts with agreed_on_price status for the weeks 26–30 in 2022
Many companies in Germany operate using the calendar weeks approach (whenever the exact date of a milestone cannot be defined). This means your stakeholder can give you a task to find, for instance, all contracts with agreed-on price status for the weeks 26–30. According to the calendar, it is related to July.
select project_id from projects where status_contract = ‘Agreed_on_price’ and start_date between ‘7–04–2022’ and ‘7–25–2022’;
Remember that clause FROM table goes before WHERE BETWEEN clause. Let’s specify project names as well to see a more clear picture.
select project_id, project_name from projects where status_contract = ‘Agreed_on_price’ and start_date between ‘7–04–2022’ and ‘7–25–2022’;
As you can see, there was only one project, ‘Baubau’, with id number 103, within these dates.
3. Find all subcontractors in Munich
Imagine you need to find all subcontractors for Munich city. It can be tricky, given that the information is stored in two tables — projects and suppliers. You need to make JOIN to implement this function.
Let’s first retrieve the data about subcontractors with LEFT JOIN, WHERE GROUP BY clause.
select suppliers.supplier_id, suppliers.supplier_name from suppliers left join projects on suppliers.project_id = projects.project_id where suppliers.supplier_category = ‘Sub_contractor’ group by suppliers.supplier_id;
Then, we need to specify the location with WHERE AND clause.
select suppliers.supplier_id, suppliers.supplier_name from suppliers left join projects on suppliers.project_id = projects.project_id where suppliers.supplier_category = ‘Sub_contractor’ and projects.location = ‘Munich’ group by suppliers.supplier_id;
As a result, we can see three subcontractors — Apple, OSRAM, and Construction Works.
Thank you for reading.
You can read more of my articles on the SQL topic:
