SQL Server: Select query on multiple tables | Combining more than one table to get the result

--Step1

create table tabProductCategroy

(

CategoryCode int primary key identity(101,1),

CategoryName char(30) not null

)

insert into tabProductCategroy values('Gold');

insert into tabProductCategroy values('Silver');

--Step2

create table tabProduct

(

CategoryCode int foreign key references tabProductCategroy(CategoryCode),

ProductID int primary key identity(1,1),

ProductName char(30) not null,

Price int

)

--Step3

insert into tabProduct values(101,'Ring R001',15500)

insert into tabProduct values(101,'Ring R002',12500)

insert into tabProduct values(101,'Bangles B001',15500)

insert into tabProduct values(101,'Bangles B002',12500)

insert into tabProduct values(102,'Anklet A001',20500)

insert into tabProduct values(102,'Anklet A002',10500)

insert into tabProduct values(102,'S.Ring SR01',1500)

insert into tabProduct values(102,'S.Ring SR02',1200)

--Check

select * from tabProductCategroy

101    Gold                          

102    Silver                       

--Check

select * from tabProduct

101    1      Ring R001                         15500

101    2      Ring R002                         12500

101    3      Bangles B001                      15500

101    4      Bangles B002                      12500

102    7      Anklet A001                       20500

102    8      Anklet A002                       10500

102    9      S.Ring SR01                       1500

102    10     S.Ring SR02                       1200

--If you have Product Category Name then,

select p.ProductName,p.Price

from tabProduct p,tabProductCategroy pc

where pc.CategoryName='Gold' and pc.CategoryCode = p.CategoryCode

Ring R001                         15500

Ring R002                         12500

Bangles B001                      15500

Bangles B002                      12500

--If you have Product Category Name then,

select p.ProductName,p.Price

from tabProduct p,tabProductCategroy pc

where pc.CategoryName='Silver' and pc.CategoryCode = p.CategoryCode

Anklet A001                       20500

Anklet A002                       10500

S.Ring SR01                       1500

S.Ring SR02                       1200

--If you have Product Category Code (101 for Gold)then

select p.ProductName,p.Price

from tabProduct p,tabProductCategroy pc

where pc.CategoryCode='101' and pc.CategoryCode = p.CategoryCode

Ring R001                         15500

Ring R002                         12500

Bangles B001                      15500

Bangles B002                      12500

--If you have Product Category Code (102 for Silver) then 

select p.ProductName,p.Price

from tabProduct p,tabProductCategroy pc

where pc.CategoryCode='102' and pc.CategoryCode = p.CategoryCode

Anklet A001                       20500

Anklet A002                       10500

S.Ring SR01                       1500

S.Ring SR02                       1200

--Additional conditions

--Find the Silver Items whose Price is greater than 20,000

select p.ProductName,p.Price

from tabProduct p,tabProductCategroy pc

where pc.CategoryCode='102' and

      pc.CategoryCode = p.CategoryCode and

      p.Price >20000

--Additional condtions

--Find the Ring Item details whose Price is greater than 15,000

select p.ProductID,p.ProductName,p.Price

from tabProduct p,tabProductCategroy pc

where pc.CategoryCode = p.CategoryCode and

      p.Price >15000 and

      p.ProductName Like 'Rin%'