Page Comparison

...

And if we populate it with some data

first name	last name	destination city	destination country	reason for visit	number of visits
Sel	RTR	Buzios	Brasil	Sun	2
Sel	RTR	Jo/burg	South Africa	Work, Sun	4
Sel	RTR		Nepal	Mountains	2
Sel	RTR	Rio De Janeiro	Brasil	Lifestyle, Sun	2
Sel	RTR	empumalanga	South Africa	Safari	2
Sel	RTR	Flogaman	Jamaica	Family, Sun, Lifestyle	10

...

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

first name	last name
Sel	RTR
Sam	Wright
Carol	Anne

Destination Table

destination city	destination country	reason for visit	number of visits
Buzios	Brasil	Sun	2
Jo/burg	South Africa	Work, Sun	4
Tokyo	Japan	Manga comics	2
	Nepal	Mountains	2
Mauritius	India	Chilling in the sun	3
Rio De Janeiro	Brasil	Lifestyle, Sun	2
Bridgetown	Barbados	Sun	3
Puerto Banús	Spain	Lifestyle	5
Empumalanga	South Africa	Safari	2
Flogaman	Jamaica	Family, Sun, Lifestyle	10

...

And populated with the updated IDs

Traveller Table

traveller

id

first name

last name

1

Sel

RTR

2

Sam

Wright

3

Carol

Anne

Destination Table

destination id	destination city	destination country	reason for visit	number of visits
1	Buzios	Brasil	Sun	2
2	Jo/burg	South Africa	Work, Sun	4
3	Tokyo	Japan	Manga comics	2
4		Nepal	Mountains	2
5	Port Luis	Mauritius	Chilling in the sun	3
6	Rio De Janeiro	Brasil	Lifestyle, Sun	2
7	Bridgetown	Barbados	Sun	3
8	Puerto Banús	Spain	Lifestyle	5
9	Empumalanga	South Africa	Safari	2
10	Flogaman	Jamaica	Family, Sun, Lifestyle	10

...

The most general syntax is - insert a value into each and every column

Code Block

language	sql

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 - insert a value into selected columns

Code Block

language	sql

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

Code Block

language	sql

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

...

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

Code Block

language	sql

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

// Using the traveller example
select * from traveller;

...

You can restrict the columns returned using

Code Block

language	sql

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

Code Block

language	sql

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.

...

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

Code Block

language	sql

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

Code Block

language	sql

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;

...

Versions Compared

Old Version 2

New Version 3

Key

Traveller Table

Destination Table

Traveller Table

Destination Table