There are situations where not all fields of a row have a value. For example, in the bank_transaction table, the date might be missing because the intelligence service did not obtain this information from the bank. To find the flights without a date we can use the command
select * from bank_transaction where date is null;
37.) What is the percentage of flights where the date information is missing?
In practice, there are usually columns which are created to be not nullable. In this case, the database refuses to add rows which contain null values in the respective fields. This is always true for primary keys.
Assume we are looking for all information on passengers who have send or received a bank transfer on the date of their flights.
38.) What is the SQL query?
This kind of query is called an inner join on the two tables. An alternative syntax making this a little bit more explicit is
select * from flight inner join bank_transaction on flight.date = bank_transaction.date;
Now, we might want to see all flight data even if no bank transaction is performed on the same date while still showing the corresponding transaction data if there is one. This is called an left outer join or simply left join:
select * from flight left join bank_transaction on flight.date = bank_transaction.date;
39.) What happens when no bank transactions occur at the flight date?
In a right (outer) join the roles of the two tables are just interchanged while in a full outer join all rows of both tables are shown. However, they are both not supported by SQLite.
The commands you have learned so far should be enough to get information from SQL data bases for most application. If you are also interested in entering data into databases and also changing existing data you might want to look at the last chapter.