Page Comparison

...

And if we populate it with some data

It's clear to see from this table that there are quite a few repeated elements such as the first name, last name, destination country, and reason for visit.  We can easily improve on this model by splitting the table into two tables

...

In our example above, we can easily link the two tables together because there is only one traveller.  But what if we were to increase the number of travellers and destinations to the following sets

Traveller Table

Destination Table

It's now clear that we cannot easily (actually it's impossible without intimate knowledge of each traveller) associate the destination rows with the travellers.  We need some way of identifying which destination row belongs to which traveller. 

...

And populated with the updated IDs

Traveller Table

Destination Table

The IDs in both cases must be unique.  You might be asking yourself: do we need the destination id?  We will come back to this.

...

Destination Table (updated with traveller id)

We now have enough information to be able to link the rows on either table to each other.  

...

Destination Table (updated with traveller id)

Now we specify the destination id as the primary key and index on it.  Also, we would write some algorithms to identify the full country name from the data in the destination id column.

...

The CREATE TABLE command is used to tell the DBMS that a table should be created, the full syntax is as follows

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

So our traveler table could be defined as follows

CREATE TABLE IF NOT EXISTS traveller(
	traveller_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	first_name varchar(40) NOT NULL,
	last_name varchar(40) NOT NULL);

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.

...

The most general syntax is - to insert a value into 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

INSERT INTO <table_name> (field1, field2, ...) VALUES (field1_value, field2_value, ...);

// Using the destination example
INSERT INTO destination (destination_id, traveller_id, destination_city, destination_country, reason_for_visit, number_of_vists) VALUES ('BRABUZ', 1, 'Buzios', 'Brasil', 'Sun', 2 );

The usual syntax when a field has an auto-increment constraint - insert to selected fields

// assuming field1 is an auto increment field
INSERT INTO <table_name> (field2, ...) VALUES (field2_value, ...);
// field1 will be inserted by the RDBMS

Querying the data

Once you have data in your database, you can use the query elements of SQL to find data.  The SELECT command has many forms, we simply use it to query one and two tables.

Its most basic form is the following - this will read all data from all columns

SELECT * FROM <table name>
// The * means all columns

// Using the traveller example
select * from traveller;

You can restrict the columns returned using

SELECT <comma delimited list of column names> FROM <table name>

// Using the traveller example
select first_name, last_name from traveller;

We can restrict the amount of data returned using this SELECT ... WHERE form

SELECT <columns|*> from <table(s)> WHERE <condition>

// Using the destination table
SELECT * from destination WHERE destination_country = 'Brasil'
// This will only return destinations where Brasil has been visited

SELECT * from destination WHERE locate('Sun', reason_for_visit) AND number_of_visits > 2
// This will return destinations where the traveller specifically went for Sun and they have been there more than twice (notice the use of the function locate(), this a MySQL string handling routine.  Other RDBMS have their own routines, you would need to check their documentation.

The WHERE clause is used to constrain the result set that is returned.  You can see this if you run the query above that contains any of the WHERE clauses.

...

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

select destination_id, destination_country, destination_city, reason_for_visit, number_of_visits, first_name, last_name 
	from destination join traveller
	on traveller.traveller_id = destination.traveller_id;