SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE tbl_pokemongames ( poke_game_id CHAR(10) PRIMARY KEY NOT NULL, poke_game_name VARCHAR(150), poke_game_region VARCHAR(50), poke_game_platform VARCHAR(50) ); -- Table CREATE TABLE tbl_nationaldex ( pokemon_generation INT NOT NULL, pokemon_id INT PRIMARY KEY NOT NULL, pokemon_name VARCHAR(150) NOT NULL, pokemon_type VARCHAR(200) NOT NULL, pokemon_base_stats INT NOT NULL ); CREATE TABLE tbl_fireredleafgreen ( poke_game_id CHAR(10) DEFAULT 'frld' NOT NULL, pokemon_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, pokemon_national_id INT UNIQUE NOT NULL, pokemon_name VARCHAR(150) NOT NULL, pokemon_type VARCHAR(200) NOT NULL, pokemon_base_stats INT NOT NULL, pokemon_location VARCHAR(150) NOT NULL, CONSTRAINT pk_poke_game_id_frld FOREIGN KEY (poke_game_id) REFERENCES tbl_pokemongames(poke_game_id), CONSTRAINT pk_poke_naional_id_frld FOREIGN KEY (pokemon_national_id) REFERENCES tbl_nationaldex(pokemon_id) ); CREATE TABLE tbl_rubysapphireemerald ( pokemon_game_id CHAR(10) DEFAULT 'rse' NOT NULL, pokemon_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, pokemon_national_id INT UNIQUE NOT NULL, pokemon_name VARCHAR(150) NOT NULL, pokemon_type VARCHAR(200) NOT NULL, pokemon_base_stats INT NOT NULL, pokemon_location VARCHAR(150) NOT NULL, CONSTRAINT pk_poke_game_id_rse FOREIGN KEY (pokemon_game_id) REFERENCES tbl_pokemongames(poke_game_id), CONSTRAINT pk_poke_naional_id_rse FOREIGN KEY (pokemon_national_id) REFERENCES tbl_nationaldex(pokemon_id) ); INSERT INTO tbl_pokemongames (poke_game_id, poke_game_name, poke_game_region, poke_game_platform) VALUES ('rby', 'Red, Blue, and Yellow', 'Kanto', 'Game Boy'), ('frld', 'FireRed and LeafGreen', 'Kanto', 'Game Boy Advance'), ('rse', 'Ruby Sapphire and Emerald', 'Hoenn', 'Game Boy Advance'), ('hgss', 'HeartGold and SoulSilver', 'Johto', 'Nintendo DS'), ('p', 'Platinum', 'Sinnoh', 'Nintendo DS'), ('oras', 'Omega Ruby and Alpha Sapphire', 'Hoenn', '3DS'), ('sm', 'Sun and Moon', 'Alola', '3DS'); INSERT INTO tbl_nationaldex (pokemon_generation, pokemon_id, pokemon_name, pokemon_type, pokemon_base_stats) VALUES (1, 1, 'Bulbasaur', 'Grass, Poison', 318), (1, 2, 'Ivysaur', 'Grass, Poison', 405), (1, 3, 'Venusaur', 'Grass, Poison', 525), (3, 252, 'Treecko', 'Grass', 318), (3, 253, 'Grovyle', 'Grass', 405), (3, 254, 'Sceptile', 'Grass', 530); INSERT INTO tbl_fireredleafgreen (pokemon_national_id, pokemon_name, pokemon_type, pokemon_base_stats, pokemon_location) VALUES (1, 'Bulbasaur', 'Grass, Poison', 318, 'Pallet Town'), (2, 'Ivysaur', 'Grass, Poison', 405, 'Evolve Bulbasaur'), (3, 'Venusaur', 'Grass, Poison', 525, 'Evolve Bulbasaur/Ivysaur'); INSERT INTO tbl_rubysapphireemerald (pokemon_national_id, pokemon_name, pokemon_type, pokemon_base_stats, pokemon_location) VALUES (252, 'Treecko', 'Grass', 318, 'Route 101'), (253, 'Grovyle', 'Grass', 405, 'Evolve Treecko'), (254, 'Sceptile', 'Grass', 530, 'Evolve Treecko/Grovyle'); DELIMITER // CREATE FUNCTION fn_search_pokemon_game_or_region (_game_or_region VARCHAR(150)) RETURNS VARCHAR(500) BEGIN DECLARE result VARCHAR(500); SET result = 'No matching records found'; SELECT CONCAT('Game Name: ', poke_game_name, ', Region: ', poke_game_region, ', Platform: ', poke_game_platform) INTO result FROM tbl_pokemongames WHERE LOWER(poke_game_name) LIKE CONCAT('%', LOWER(_game_or_region), '%') OR LOWER(poke_game_region) LIKE CONCAT('%', LOWER(_game_or_region), '%') LIMIT 1; RETURN result; END// DELIMITER ; select fn_search_pokemon_game_or_region ('Kanto')