###################################################################################
ORA03 - DB storing Clients
ORA02 - DB storing Products and Sales
ORA01 - DB storing Shops

############################################
Simple query executed from ORA01 or ORA02
############################################

select CName
from Clients@ORA03
where lower(City)='london';

############################################
Grouping
Query executed from ORA01 or ORA02
############################################

select count(*), City
from Clients@ORA03
group by City
order by City;

select count(*), City
from Clients@ORA03
where Points between 500 and 1100
group by City
order by City;

select count(*), City
from Clients@ORA03
where City in ('Glasgow', 'Luton')
group by City
order by City;

############################################
Joins executed from ORA01
############################################

select SName, sa.Quantity
from Shops sh, Sales@ORA02 sa
where sh.ShopID=sa.ShopID;

---------------------------
View created in ORA02
---------------------------
create view v_Sales_Shops
as 
select sa.SalesID, sa.SDate, sa.Quantity, sa.ProductID,
       sa.ClientID, sa.ShopID,
       sh.SName, sh.City
from Sales sa, Shops@ORA01 sk
where sa.ShopID=sh.ShopID;

----------------------------
Execute from ORA01 and ORA03
----------------------------
select SName, Quantity
from v_Sales_Shops@ORA02 sa
where City='Luton';

select SName, sa.Quantity
from Shops sh, Sales@ORA02 sa
where sh.ShopID=sa.ShopID
and sh.City='Luton';

----------------------------
Execute from ORA01 and ORA03
----------------------------
select sa.SDate, sa.Quantity, p.PName, p.Category
from Sales@ORA02 sa, Products@ORA02 p
where sa.ProductID=p.ProductID
and Category='books';

---------------------------
View created in ORA02
---------------------------
create view v_Sales_Products
as 
select sa.SDate, sa.Quantity, p.PName, p.Category
from Sales sa, Products p
where sa.ProductID=p.ProductID;

----------------------------
Execute from ORA01 and ORA03
----------------------------
select SDate, Quantity, PName, Category
from v_Sales_Products@ORA02
where Category='books';

----------------------------
Execute from ORA03
----------------------------
select kl.CName, kl.City, sa.Quantity, pr.PName
from Clients kl, Sales@ORA02 sa, Products@ORA02 pr
where kl.ClientID=sa.ClientID
and sa.ProductID=pr.ProductID;

select kl.CName, kl.City, v.Quantity, v.PName
from Clients kl, 
     (select sa.ClientID, sa.Quantity, pr.PName
      from Sales@ORA02 sa, Products@ORA02 pr
      where sa.ProductID=pr.ProductID) v
where kl.ClientID=v.ClientID;

---------------------------
View created in ORA02
---------------------------
create view v_Sales_prod1
as
select sa.ClientID, sa.Quantity, sa.SDate, pr.PName, pr.UnitPrice
from Sales sa, Products pr
where sa.ProductID=pr.ProductID;

----------------------------
Execute from ORA03
----------------------------
select kl.CName, kl.City, v.Quantity, v.PName
from Clients cl, v_Sales_prod1@ORA02 v
where cl.ClientID=v.ClientID;

############################################
Subqueries: execute from ORA03
############################################
select sa.Quantity
from Sales@ORA02 sa
where sa.ClientID= (select ClientID
                     from Clients
		     where CName = 'Client100');

############################################
Set operators: execute from ORA03
############################################
select ShopID from Shops@ORA01
union
select ProductID from Products@ORA02
union 
select ClientID from Clients
order by 1;

#######################################################
Hints
#######################################################
select /*+ ordered */ SName, sa.Quantity
from Shops sh, Sales@ORA02 sa
where sh.ShopID=sa.ShopID
and sh.ShopID=20;

select /*+ ordered */ SName, sa.Quantity
from Sales@ORA02 sa, Shops sk
where sh.ShopID=sa.ShopID
and sh.ShopID=20;

select /*+ FULL(sp) */ SName, sa.Quantity
from Sales@ORA02 sa, Shops sk
where sh.ShopID=sa.ShopID;

select /*+ FULL(sh) */ SName, sa.Quantity
from Sales@ORA02 sa, Shops sh
where sh.ShopID=sa.ShopID;

select /*+ DRIVING_SITE(sp) */ kl.CName, sa.SDate, sa.Quantity, sa.ProductID
from Clients cl, Sales@ORA02 sa
where cl.ClientID=sa.ClientID
and cl.City='Luton';

select /*+ NO_MERGE(v) */ kl.CName, kl.City, v.Quantity, v.PName
from Clients kl, 
     (select sa.ClientID, sa.Quantity, pr.PName, pr.ProductID
      from Sales@ORA02 sa, Products@ORA02 pr
      where sa.ProductID=pr.ProductID) v
where kl.ClientID=v.ClientID
and v.ProductID=30;

select kl.CName, kl.City, v.Quantity, v.PName
from Clients kl, 
     (select sa.ClientID, sa.Quantity, pr.PName, pr.ProductID
      from Sales@ORA02 sa, Products@ORA02 pr
      where sa.ProductID=pr.ProductID) v
where kl.ClientID=v.ClientID
and v.ProductID=30;