-- create CONTINENTS table
CREATE TABLE continents (
  continent_code VARCHAR(2) NOT NULL,
  continent_name VARCHAR(30) NOT NULL,
  PRIMARY KEY (continent_code)
);

-- create COUNTRIES table
CREATE TABLE countries (
  country_code VARCHAR(2) PRIMARY KEY,
  country_name VARCHAR(30) NOT NULL,
  continent_code VARCHAR(2)
);

-- describe continents;
-- describe countries;

-- Insert data into continents table
INSERT INTO continents (continent_code, continent_name)
VALUES
  ('NA', 'North America'),
  ('SA', 'South America'),
  ('EU', 'Europe'),
  ('AF', 'Africa'),
  ('AS', 'Asia'),
  ('AU', 'Australia');

-- Insert data into countries table
INSERT INTO countries (country_code, country_name, continent_code)
VALUES
  ('US', 'United States', 'NA'),
  ('BR', 'Brazil', 'SA'),
  ('ZA', 'South Africa', 'AF'),
  ('IN', 'India', 'AS'),
  ('AU', 'Australia', 'AU'),
  ('AQ', 'Antarctica', 'AN');

-- select continent_code as C_C, continent_name as C_N  from continents;
-- select country_code as CTR_C, country_name as C_N, continent_code as C_C from countries;

-- SELECT
--   RPAD(continent_code, 15, ' ') AS continent_code,
--   RPAD(continent_name, 30, ' ') AS continent_name
-- FROM continents;

-- SELECT '-------------------------' AS '';

-- SELECT
--   RPAD(country_code, 15, ' ') AS country_code,
--   RPAD(country_name, 10, ' ') AS country_name,
--   continent_code AS continent_code
-- FROM countries;

SELECT 'INNER JOIN:' AS '';
SELECT 'Returns only the rows where there is a match in both tables.' AS '';
SELECT '-------------------------' AS '';

SELECT RPAD(country_name, 15, ' ') as 'country name',
       continent_name as 'continent name'
FROM continents
    INNER JOIN countries
        ON continents.continent_code = countries.continent_code;

SELECT '**********************' AS '';       

SELECT 'LEFT JOIN (or LEFT OUTER JOIN):' AS '';
SELECT 'Returns all rows from the left table and the matching rows from the right table.' AS '';
SELECT '-------------------------' AS '';

SELECT RPAD(country_name, 15, ' ') as 'country name',
       continent_name as 'continent name'
FROM continents
    LEFT JOIN countries
        ON continents.continent_code = countries.continent_code;
        
SELECT '**********************' AS ''; 
        
SELECT 'RIGHT JOIN (or RIGHT OUTER JOIN):' AS '';
SELECT 'Returns all rows from the right table and the matching rows from the left table.' AS '';
SELECT '-------------------------' AS '';

SELECT RPAD(country_name, 15, ' ') as 'country name',
       continent_name as 'continent name'
FROM continents
    RIGHT JOIN countries
        ON continents.continent_code = countries.continent_code;
        
SELECT '**********************' AS ''; 
        
SELECT 'FULL OUTER JOIN (or FULL JOIN) (requires MySQL 5.7+):' AS '';
SELECT 'Returns all rows when there is a match in either the left or right table.' AS '';
SELECT 'Syntax (using LEFT JOIN and UNION with RIGHT JOIN):' AS '';
SELECT '-------------------------' AS '';

SELECT RPAD(country_name, 15, ' ') as 'country name',
       continent_name as 'continent name'
FROM continents
    LEFT JOIN countries
        ON continents.continent_code = countries.continent_code
        
UNION

SELECT RPAD(country_name, 15, ' ') as 'country name',
       continent_name as 'continent name'
FROM continents
    RIGHT JOIN countries
        ON continents.continent_code = countries.continent_code;
        
        
SELECT '**********************' AS ''; 
        
SELECT 'CROSS JOIN (or CARTESIAN JOIN):' AS '';
SELECT 'Returns the Cartesian product of the two tables, ' AS '';
SELECT 'i.e., all possible combinations of rows from both tables.' AS '';
SELECT '-------------------------' AS '';

SELECT 
       ROW_NUMBER() OVER () as 'row number',
       RPAD(country_name, 15, ' ') as 'country name',
       continent_name as 'continent name'
FROM continents
    CROSS JOIN countries;