How To create Table and How to mearge in multiple table with where clause.

use RMCCinema

///////////////////Country Name /////////////////////

create table CountryName (id int identity not null ,

Country_Name varchar(50) primary key)

insert into CountryName values('India')

insert into CountryName values('Srilanka')

select * from CountryName

//////////////////End Country Name.//////////////////

/////////////////State Name ///////////////////////

create table StateName(id int identity not null,

Country_Name varchar(50) foreign key references CountryName(Country_Name),

State_Name varchar(50) not null primary key)

insert into StateName values('India','AandraPradesh')

insert into StateName values('India','Arunachal Pradesh')

insert into StateName values('India','Assam')

insert into StateName values('India','Bihar')

insert into StateName values('India','Chhattisgarh')

insert into StateName values('India','Goa')

insert into StateName values('Srilanka','Colombo')

select * from StateName

SELECT c.Country_Name,s.State_Name

FROM CountryName c

INNER JOIN StateName s ON c.Country_Name = s.Country_Name

WHERE c.Country_Name='India'

//////////////////////// End State Name ///////////////////

/////////////////////////City Name///////////////////

create table CityName (id int identity not null,

Country_Name varchar(50) foreign key references CountryName(Country_Name),

State_Name varchar(50) foreign key references StateName(State_Name),

City_Name varchar(50) not null primary key)

select * from CityName

insert into CityName values('India','AandraPradesh','Vijaywada')

insert into CityName values('India','Bihar','Patna')

SELECT s.State_Name ,ci.City_Name,ci.Country_Name

FROM StateName s

INNER JOIN CityName ci ON s.State_Name = ci.State_Name

WHERE s.State_Name='AandraPradesh'

//////////////////////End City Name/////////////////////

Rahul kumar jaiwal
Thanks