rahul jaiswal

rahul jaiswal

  • NA
  • 177
  • 133.2k

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

Sep 16 2012 5:04 AM

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 

Answers (1)