Page Comparison

...

...

CREATE TABLE [IF NOT EXISTS] traveller(
	<column name> <type> [NOT NULL] [AUTO_INCREMENT] [PRIMARY KEY]
	[,... other columns] [DEFAULT <value>] [CONSTRAINT <constraint expression>] );

So our traveller traveler table could be defined as follows

...

And the destination table is as follows

CREATE TABLE IF NOT EXISTS destination (
	destination_id char(8) NOT NULL,
	traveller_id int,
	destination_city varchar(100),
	destination_country varchar(50) NOT NULL,
    reason_for_visit varchar(100) NOT NULL,
    number_of_visits int,
	CONSTRAINT CH_visits CHECK (number_of_visits > 0),
	PRIMARY KEY(destination_id),
	FOREIGN KEY(traveller_id) REFERENCES traveller(traveller_id));

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

INSERT INTO <table_name> VALUES ( <comma delimited list of values for all fields> );

// Using the traveller example
INSERT INTO traveller VALUES ('Sel', 'RTR’);

...

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

select * from destination join traveller
	on traveller.traveller_id = destination.traveller_id;

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

...