Structure Query Language (I): Multiple Table Manipulation
JOIN BY ROW
Both tables must have same structure
Example:
Table1
Table2
Practical
Make use of the Insert statements in last practical.
Create 2 tables SONG1, SONG2 with identical structure
create database if not exists mydb;
use mydb;
drop table if exists song1;
drop table if exists song2;
/* Create song1, song2, */
INSERT INTO Song1 VALUES ('173', 'Tree and Leaves', '0001', '121');
INSERT INTO Song1 VALUES ('174', 'Leaves and Flowers', '0001', '131');
INSERT INTO Song1 VALUES ('175', 'Rose', '0001', '121');
INSERT INTO Song1 VALUES ('176', 'Green land', '0001', '131');
INSERT INTO Song1 VALUES ('177', 'Livid songs', '0002', '121');
INSERT INTO Song1 VALUES ('178', 'Speed', '0002', '131');
INSERT INTO Song1 VALUES ('179', 'Power', '0002', '141');
INSERT INTO Song1 VALUES ('180', 'Non-stop', '0002', '141');
INSERT INTO Song1 VALUES ('181', 'Calm lives', '0003', '121');
INSERT INTO Song1 VALUES ('182', 'Staying still', '0003', '151');
INSERT INTO Song1 VALUES ('183', 'Beautiful sun', '0003', '151');
INSERT INTO Song2 VALUES ('180', 'Non-stop', '0002', '141');
INSERT INTO Song2 VALUES ('181', 'Calm lives', '0003', '121');
INSERT INTO Song2 VALUES ('182', 'Staying still', '0003', '151');
INSERT INTO Song2 VALUES ('183', 'Beautiful sun', '0003', '151');
INSERT INTO Song2 VALUES ('184', 'White snow', '0004', '131');
INSERT INTO Song2 VALUES ('185', 'Strong wind', '0004', '131');
INSERT INTO Song2 VALUES ('186', 'Good sleep', '0004', '121');
Join By Column
- Cross Join (Without Join condition)
TableA
TableB
StudID
001
002
Subject
ICT
PHY
Select * from TableA, TableB
- INNER JOIN (Most Common Questions in EXAM)
7.2 Equi-join
Equi-join (Appear in both Table)
Method 1:
Select * from TableA, TableB
where TableA.FieldX=TableB.FieldX (Join condition)
[Optional for filtering condition, ordering ]
Method 2:
Select *
from TableA INNER JOIN TableB
ON TableA.FieldX=TableB.FieldX (Join condition)
[Optional for filtering condition, ordering ]
Non-Equi join (Seldom use)
The join condition not using =, use >=, > <>, <, <=
Practical
Refer to P.194, Draw the schema of the tables, OWNER, CAR.
Complete the data dictionary
Create the table with SQL
Insert the records with the statements found below
Try out the equi-join statements, natural join statements, Left outer join, Right outer join, Full outer join
show databases;
use mydb;
Drop table if exists car;
Drop table if exists owner;
drop table if exists car;
drop table if exists owner;
insert into car values ('C001', 'Honda','Sport');
insert into car values ('C002', 'Toyota','Family');
insert into car values ('C003', 'BMW','Sport');
insert into car values ('C004', 'Mercedes-Benz','Family');
insert into owner values ('Jack Chik',30, 20000,'C002');
insert into owner values ('Billy Liu',43, 18000,'C001');
insert into owner values ('Robert Yau',25, 35000,'C001');
insert into owner values ('Mary Ho',24, 27000,'C004');
insert into owner values ('Jessica Lai',38, 19000,'C002');
Test answer