Create sample data using Continents, Countries and Cities

stackoverflow-dapper-50091943.sql

use tempdb
go

drop table if exists dbo.City;
drop table if exists dbo.Country;
drop table if exists dbo.Continent;

create table dbo.Continent
(
	continent_id int not null primary key,
	continent nvarchar(100) not null unique
)

create table dbo.Country
(
	country_id int not null primary key,
	continent_id int not null references dbo.Continent(continent_id),
	country nvarchar(100) not null unique
)

create table dbo.City
(
	city_id int not null primary key identity,
	country_id int not null references dbo.Country(country_id),
	city nvarchar(100) not null 
)
go

insert into dbo.Continent (continent_id, continent) values
(1, 'Africa'), 
(2, 'Anctartica'), 
(3, 'Asia'), 
(4, 'Australia'), 
(5, 'North America'), 
(6, 'South America'), 
(7, 'Europe')
go

insert into dbo.Country (country_id, continent_id, country)
values 
(1, 1, 'Nigeria'),
(2, 1, 'South Africa'),
(3, 1, 'Egypt'),
(4, 1, 'Algeria'),
(5, 1, 'Angola'),
(6, 3, 'China'),
(7, 3, 'India'),
(8, 3, 'Japan')

go

insert into dbo.City(country_id, city)
values 
(1, 'Lagos'),
(1, 'Abuja')
go

create or alter procedure dbo.GetContinentsDetail
as
select result = 
(
select 
	continents.continent_id as ContinentId,
	continents.continent as ContinentName,
	countries.country_id as CountryId,
	countries.country as CountryName,
	cities.city_id as CityId,
	cities.city as CityName	
from 
	dbo.Continent continents
left join 
	dbo.Country countries on continents.continent_id = countries.continent_id
left join
	dbo.City cities on countries.country_id = cities.country_id
for json auto
)
go

exec dbo.GetContinentsDetail
go