...
destination id | traveller id | destination city | destination country | reason for visit | number of visits |
---|---|---|---|---|---|
BRABUZ | 1 | Buzios | Brasil | Sun | 2 |
ZAFJOH | 1 | Jo/burg | South Africa | Work, Sun | 4 |
JPNTOK | 2 | Tokyo | Japan | Manga comics | 2 |
NPLXXX | 1 | Nepal | Mountains | 2 | |
MUSPOR | 2 | Port Luis | Mauritius | Chilling in the sun | 3 |
BRARIO | 1 | Rio De Janeiro | Brasil | Lifestyle, Sun | 2 |
BRBBRI | 3 | Bridgetown | Barbados | Sun | 3 |
ESPPUE | 3 | Puerto Banús | Spain | Lifestyle | 5 |
ZAFEMP | 1 | EmpumalangaMpumalanga | South Africa | Safari | 2 |
JAMFLO | 1 | Flogaman | Jamaica | Family, Sun, Lifestyle | 10 |
...
|
So our traveller traveler table could be defined as follows
...
And the destination table is as follows
|
In this example, we have used a CONSTRAINT just to show an example of its usage. We could have and should have used the NOT NULL constraint as shown on the destination_country.
Notice the last two entries, the PRIMARY KEY, and FOREIGN KEY. The PRIMARY KEY statement is an alternative syntax for declaring the primary key. The FOREIGN KEY entry is how you link a column in one table into to another table.
Inserting data in the database (SQL)
...
The most general syntax is - to insert a value into each and every column
|
...
The most common syntax is - to insert a value into selected columns
...
The usual syntax when a field has an auto-increment constraint - insert to selected fields
...
On many occasions your query needs to bring together results from across two or more tables, this is known as performing a JOIN. Think of it this way - we began our discussion with a single table and the then split it out to into two tables, we now use SQL to JOIN the two tables back into a single RESULT SET.
A SQL join is an instruction to combine data from two sets of data (i.e. two tables). So in our example, above we can bring the two tables back together as a single result set using the following query
|
Unfortunately, when you run this query it will duplicate the traveller_id because the select * selects all columns from both tables. Let's narrow this by altering the query
...