SQL Server: Query to combine results of many tables | Jewellery Shop Billing Model

Jewellery Shop model

Step-1

create table tabCustomer

(

ID int primary key identity(1001,1),

[Name] char(20) not null,

[Address] char(100) not null,

PhoneNumber char(13) default '+91',

Email char(20)

)

insert into tabCustomer(Name,Address) values('Navya','AndraPradesh');

insert into tabCustomer(Name,Address) values('Kavya','AndraPradesh');

update tabCustomer set PhoneNumber='+919842522238' where id=1001

update tabCustomer set PhoneNumber='+919842522248' where id=1002

update tabCustomer set email='navya@gmail.com' where id=1001

update tabCustomer set Email='kavya@gmail.com' where id=1002

select * from tabCustomer

Step-2

create table tabSales1

(

BillNo int primary key identity(5001,1),

CustomerID int foreign key references tabCustomer(ID)

)

insert into tabSales1 values(1001)

insert into tabSales1 values(1002)

select * from tabSales1

Step-3

create table tabSales2

(

BillNo int foreign key references tabSales1(BillNo),

ProductID int foreign key references tabProduct(ProductID),

Qty int,

)

insert into tabSales2 values(5001,1,2)--BNo:5001,Ring,2-Nos

insert into tabSales2 values(5001,4,2)--BNo:5001,Bangles,2-nos

insert into tabSales2 values(5001,9,1)--No:5001,S.Ring,1-No

insert into tabSales2 values(5002,1,2)

insert into tabSales2 values(5002,3,1)
insert into tabSales2 values(5002,7,1)

select * from tabSales2

Queries

--Find the jewels purchased by bill number 5001

select tprod.ProductName

from tabSales1 ts1,

     tabSales2 ts2,

     tabProduct tprod

where ts1.BillNo = 5001 and

      ts1.BillNo = ts2.BillNo and

      ts2.ProductID = tprod.ProductID

--Find the jewels purchased by Navya

select tprod.ProductName

from tabSales1 ts1,

     tabCustomer tc,

     tabSales2 ts2,

     tabProduct tprod

where tc.Name='Kavya' and

      tc.ID = ts1.CustomerID and

      ts1.BillNo = ts2.BillNo and

      ts2.ProductID = tprod.ProductID  

--Find the jewels purchased by customer phone number +919842522238

select tprod.ProductName

from tabSales1 ts1,

        tabCustomer tc,

     tabSales2 ts2,

        tabProduct tprod

where tc.PhoneNumber='+919842522238' and

      tc.ID = ts1.CustomerID and

      ts1.BillNo = ts2.BillNo and

         ts2.ProductID = tprod.ProductID

--Find the jewels purchased by the customer email navya@gmail.com

select tprod.ProductName

from tabSales1 ts1,

     tabCustomer tc,

     tabSales2 ts2,

     tabProduct tprod

where tc.Email='navya@gmail.com' and

      tc.ID = ts1.CustomerID and

      ts1.BillNo = ts2.BillNo and

      ts2.ProductID = tprod.ProductID

--Select ProdName,Qty,TotalValue of each product 

--purchased on bill number=5001

select tprod.ProductName,ts2.Qty,(ts2.Qty * tprod.Price) as TotalValue

from tabSales1 ts1,

     tabSales2 ts2,

     tabProduct tprod

where ts1.BillNo=5001 and

      ts1.BillNo = ts2.BillNo and

      ts2.ProductID = tprod.ProductID

--Select ProdName,Qty,TotalValue of each product purchased by Kavya

select tprod.ProductName,ts2.Qty,(ts2.Qty * tprod.Price) as TotalValue

from tabCustomer tc,

     tabSales1 ts1,

     tabSales2 ts2,

     tabProduct tprod

where tc.Name='Kavya' and

      tc.ID = ts1.CustomerID and

      ts1.BillNo = ts2.BillNo and

      ts2.ProductID = tprod.ProductID

--Select Bill Number, Name, Net Purchase of each customer

select ts1.BillNo,tc.Name,sum(ts2.Qty * tprod.Price) as TotalValue

from tabCustomer tc,

     tabSales1 ts1,

     tabSales2 ts2,

     tabProduct tprod

where tc.ID = ts1.CustomerID and

     ts1.BillNo = ts2.BillNo and

     ts2.ProductID = tprod.ProductID

group by ts1.BillNo,tc.Name