<p>Let’s say you work for a small airline.
You’re given three tables: <code>flights</code>, <code>planes</code>, and <code>flight_purchases</code>.</p>

<p>Write a query to find the number of unpurchased seats for each flight.</p>

<p>For the purposes of this question, assume that there will be no entries in the <code>flight_purchases</code> table for flights or seats that do not exist.</p>

<p><strong>Example:</strong></p>

<p><strong>Input:</strong></p>

<p><code>flights</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>plane_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>planes</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>number_of_seats</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><code>flight_purchases</code> table</p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>flight_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>seat_id</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>
<p><strong>Output:</strong></p>

<table>
<thead>
<tr>
<th>Column</th>
<th>Type</th>
</tr>
</thead>

<tbody>
<tr>
<td><code>flight_id</code></td>
<td>INTEGER</td>
</tr>

<tr>
<td><code>free_seats</code></td>
<td>INTEGER</td>
</tr>
</tbody>
</table>


Free Seats

Comments