--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
--Above query using Joins
select tprod.ProductName
from tabSales1 ts1
join tabSales2 ts2 on ts1.BillNo = ts2.BillNo
join tabProduct tprod on ts2.ProductID = tprod.ProductID
where ts1.BillNo = 5001
--Find the jewels purchased by Kavya
select tprod.ProductName
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
--Above Query using Joins
select tprod.ProductName
from tabCustomer tc
join tabSales1 ts1 on tc.ID = ts1.CustomerID
join tabSales2 ts2 on ts1.BillNo = ts2.BillNo
join tabProduct tprod on ts2.ProductID = tprod.ProductID
where tc.Name='Kavya'
--Find the jewels purchased by
--customer
phone number +919842522238
select tprod.ProductName
from tabCustomer tc,
tabSales1 ts1,
tabSales2 ts2,
tabProduct tprod
where tc.PhoneNumber='+919842522238' and
tc.ID = ts1.CustomerID and
ts1.BillNo = ts2.BillNo and
ts2.ProductID
= tprod.ProductID
--Above Query using Joins
select tprod.ProductName
from tabCustomer tc
join tabSales1 ts1 on tc.ID = ts1.CustomerID
join
tabSales2 ts2 on ts1.BillNo = ts2.BillNo
join tabProduct tprod on ts2.ProductID = tprod.ProductID
where tc.PhoneNumber='+919842522238'
--Find the jewels purchased
--by 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
--Above Query using Joins
select tprod.ProductName
from tabSales1 ts1
join tabCustomer tc on tc.ID = ts1.CustomerID
join tabSales2 ts2 on
ts1.BillNo = ts2.BillNo
join tabProduct tprod on ts2.ProductID = tprod.ProductID
where tc.Email='navya@gmail.com'
--Select ProdName, Qty, Total-Value 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
--Above Query using joins
select tprod.ProductName,ts2.Qty,(ts2.Qty * tprod.Price) as TotalValue
from tabSales1 ts1
join tabSales2 ts2 on ts1.BillNo = ts2.BillNo
join tabProduct tprod on ts2.ProductID = tprod.ProductID
where ts1.BillNo=5001
--Select ProdName, Qty, Total-Value 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
--Above Query using joins
select tprod.ProductName,ts2.Qty,(ts2.Qty * tprod.Price) as TotalValue
from tabCustomer tc
join tabSales1 ts1 on tc.ID = ts1.CustomerID
join tabSales2 ts2 on ts1.BillNo = ts2.BillNo
join tabProduct tprod on ts2.ProductID = tprod.ProductID
where tc.Name='Kavya'
--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
--Above Query using join
select tabSales1.BillNo,tabCustomer.Name,sum(Qty * Price) as TotalValue
from tabCustomer
join tabSales1 on tabCustomer.ID = tabSales1.CustomerID
join tabSales2 on tabSales1.BillNo = tabSales2.BillNo
join tabProduct on tabSales2.ProductID = tabProduct.ProductID
group by tabSales1.BillNo,tabCustomer.Name
No comments:
Post a Comment