alex1

    LAB 2
P1  /*Afisati salarul mediu, minim, maxim precum si suma salariilor grupate pe job si gradul de
salarizare. Sa secalculeze subtotaluri la nivel de job, grad si total general.*/

SELECT e.job, s.grade, AVG(e.sal), MIN(e.sal), SUM(e.sal)
FROM emp e, salgrade s
WHERE sal BETWEEN losal AND hisal
GROUP BY rollup(job,grade)

P2 /*Adaugati la interogarea anterioara subtotalurile pentru toate combinatiile intre cele doua
dimensiuni: job si gradul de salarizare.*/

SELECT e.job, s.grade, AVG(e.sal), MIN(e.sal), SUM(e.sal)
FROM emp e, salgrade s
WHERE sal BETWEEN losal AND hisal
GROUP BY cube(job,grade)

P3) Scrieti o interogare care sa afiseze urmatoarele informatii:
- numele managerului, job-ul angajatului, suma salariilor, media salariilor, salarul minim si
maxim, grupate pe manager si job;
-   subtotaluri la nivelul managerului si a jobului, a managerului si totalul general*/

SELECT m.ename,e.job, SUM(e.sal), AVG(e.sal), MIN(e.sal), MAX(e.sal)
FROM emp m, emp e
WHERE e.mgr= m.empno
GROUP BY m.ename, rollup(e.job);

    P4) /*La interogarea anterioara adaugati functii de grupare care sa identifice care din valorile
NULL afisate sunt generate de extensia clauzei GROUP BY si care sunt din tabele. Pe
coloanele ename si job, pentru valorile NULL generate de extensia clauzei GROUP BY afisati
urmatoarele valori: Total ename, Total job.*/

SELECT NVL(m.ename,'total ename'),NVL(e.job,'Total job'), SUM(e.sal), AVG(e.sal), MIN(e.sal), MAX(e.sal),
GROUPING(m.ename) AS gMename, GROUPING(e.job) AS gjob
FROM emp m, emp e
WHERE e.mgr= m.empno
GROUP BY rollup(m.ename,e.job);

P5 /*Folosind optiunea GROUPING SETS afisati urmatoarele grupuri:
- nume departament si job
- nume manager si grad salar
Interogarea trebuie sa calculeze suma salariilor. Afisati si nivelul subtotalurilor calculate, iar
valorile NULL generate le afisati cu „Total”. Excludeti eventualele linii duplicate. Ordonati
dupa nivelul subtotalurilor.*/

SELECT NVL(d.dname,'total'), NVL(e.job,'total'), NVL(m.ename,'total'),s.grade, SUM(e.sal), GROUPING_ID(d.dname,e.job) AS nivel1,
GROUPING_ID(m.ename,s.grade) AS nivel2, GROUP_ID()
FROM dept d, emp e, emp m, salgrade s
WHERE d.deptno= e.deptno AND
      e.mgr= m.empno AND
      e.sal BETWEEN losal AND hisal
      GROUP BY GROUPING sets ((d.dname,e.job),(m.ename,s.grade))
      HAVING GROUP_ID()=0
      ORDER BY nivel1, nivel2

P6 /*Afisati subtotalurile si suma vanzarilor pentru urmatoarele dimensiuni:
– denumirea canalului de distributie (channel_desc), luna de vanzare (calendar_month_desc) si
prescurtarea tarii (country_id);
– filtrati liniile dupa urmatoarele conditii: canalul de distributie sa fie 'Direct Sales' si 'Internet'; luna
de vanzare sa fie '2000-09' si '2000-10', tara de desfacere sa fie 'UK' si 'US'.*/

SELECT ch.channel_desc, t.calendar_month_desc,k.country_id, SUM (s.amount_sold)
FROM channels ch, times t, countries k, sales s
WHERE ch.channel_id= s.channel_id AND
      s.time_id=t.time_id AND
      (ch.channel_desc='Direct Sales' OR ch.channel_desc='Internet') AND
      (t.calendar_month_desc='2000-09' OR t.calendar_month_desc='2000-10') AND
      (k.country_id='UK' OR k.country_id='US')
      GROUP BY rollup(ch.channel_desc,t.calendar_month_desc,k.country_id);


    P7 /*Pentru interogarea anterioara afisati gruparea partiala dupa canalul de distributie.*/

SELECT ch.channel_desc, t.calendar_month_desc,k.country_id, SUM (s.amount_sold)
FROM channels ch, times t, countries k, sales s
WHERE ch.channel_id= s.channel_id AND
      s.time_id=t.time_id AND
      (ch.channel_desc='Direct Sales' OR ch.channel_desc='Internet') AND
      (t.calendar_month_desc='2000-09' OR t.calendar_month_desc='2000-10') AND
      (k.country_id='UK' OR k.country_id='US')
      GROUP BY ch.channel_desc, rollup(t.calendar_month_desc,k.country_id);

    P8 /*Afisati subtotalurile pentru fiecare combinatie de dimensiuni de la pb 6. Refaceti interogarea si
afisati doar dimensiunile pentru canalul de distributie si tara. Pentru valorile NULL generate de
extensia GROUP BY afisati valorile: 'All countries', respectiv „All channels”.*/


SELECT NVL(ch.channel_desc,'All channels'),NVL(k.country_id,'all countries'), SUM (s.amount_sold)
FROM channels ch, times t, countries k, sales s
WHERE ch.channel_id= s.channel_id AND
      s.time_id=t.time_id AND
      (ch.channel_desc='Direct Sales' OR ch.channel_desc='Internet') AND
      (t.calendar_month_desc='2000-09' OR t.calendar_month_desc='2000-10') AND
      (k.country_id='UK' OR k.country_id='US')
      GROUP BY cube(ch.channel_desc,t.calendar_month_desc,k.country_id);

    P9 /*La problema 8 afisati doar totalurile pe tara, respectiv pe canal, precum si totalul general.*/

SELECT NVL(ch.channel_desc,'All channels'),NVL(k.country_id,'all countries'), SUM (s.amount_sold),
GROUPING (ch.channel_desc) AS gchannel, GROUPING(k.country_id) AS gcountry
FROM channels ch, times t, countries k, sales s
WHERE ch.channel_id= s.channel_id AND
      s.time_id=t.time_id AND
      (ch.channel_desc='Direct Sales' OR ch.channel_desc='Internet') AND
      (t.calendar_month_desc='2000-09' OR t.calendar_month_desc='2000-10') AND
      (k.country_id='UK' OR k.country_id='US')
      GROUP BY cube(ch.channel_desc,k.country_id)
      HAVING GROUPING (ch.channel_desc)=1 OR GROUPING(k.country_id)=1;

    P10 /*Pentru problema 6 afisati totalul vanzarilor pentru urmatoarele seturi de grupuri de date
('GROUPING SETS'):
- channel_desc, calendar_month_desc, country_id
- channel_desc, country_id
- calendar_month_desc, country_id)
Conditiile de filtrare si informatiile de afisat raman aceleasi.*/



SELECT ch.channel_desc, t.calendar_month_desc,k.country_id, SUM (s.amount_sold)
FROM channels ch, times t, countries k, sales s
WHERE ch.channel_id= s.channel_id AND
      s.time_id=t.time_id AND
      (ch.channel_desc='Direct Sales' OR ch.channel_desc='Internet') AND
      (t.calendar_month_desc='2000-09' OR t.calendar_month_desc='2000-10') AND
      (k.country_id='UK' OR k.country_id='US')
      GROUP BY GROUPING sets((ch.channel_desc,t.calendar_month_desc,k.country_id),(ch.channel_desc, k.country_id),(calendar_month_desc,k.country_id));



    LAB 3

    P 1 /*Afisati clasamentul angajatilor pe departamente dupa data angajarii crescator. Afisati
informatiile: numele departamentului, numele angajatului, data angajarii si pozitia angajatului .*/

SELECT d.dname, e.ename,e.hiredate, RANK() over (PARTITION BY d.deptno ORDER BY e.hiredate ASC) Pozitie
FROM emp e, dept d
WHERE e.deptno=d.deptno;

    P 2 /*Afisati clasamentul salariilor medii (rotunjit la 2 zecimale) in ordine descrescatoare, pe
departamente. Adaugati si liniile totalizatoare pentru departament si job. Afisati urmatoarele
informatii: numarul departamentului sau „DeptAvgSal”, denumirea job-ului sau „JobAvgSal”,
salarul mediu si pozitia in cadrul departamentului.*/


SELECT NVL(TO_CHAR(deptno),'DeptAvgSal') AS deptno, NVL(job,'JobAvgSal') AS Jobb,
ROUND(AVG(sal),2) AS mediu,
RANK() over (PARTITION BY deptno ORDER BY AVG(sal) DESC) RANK
FROM emp
GROUP BY rollup(deptno,job);


P3 /*La interogarea anterioara adaugati si pozitia in cadrul job-ului.*/




SELECT NVL(TO_CHAR(deptno),'DeptAvgSal') AS deptno, NVL(job,'JobAvgSal') AS Jobb,
ROUND(AVG(sal),2) AS mediu,
RANK() over (PARTITION BY deptno ORDER BY AVG(sal) DESC) RANK,
RANK() over (PARTITION BY job ORDER BY AVG(sal) DESC) RankJob
FROM emp
GROUP BY rollup(deptno,job);

    P4 /*Afisati angajatii cu primele 10 niveluri de venituri (pot sa existe mai mult de 10 angajati).
Venit = salar + comision. Afisati urmatoarele informatii: pozitia angajatului (fara
discontinuitati) in clasamentul veniturilor, numele, salarul, comisionul si venitul.*/

SELECT * FROM (SELECT ename,sal,comm, sal+NVL(comm,0) AS venit ,
DENSE_RANK() over (ORDER BY sal+NVL(comm,0) DESC) AS rankk
FROM emp)
WHERE rankk<=10;

    P5 /*La exercitiul anterior afisati si distributia veniturilor (rotunjita la 2 zecimale).*/

SELECT * FROM (SELECT ename,sal,comm, sal+NVL(comm,0) AS venit ,
DENSE_RANK() over (ORDER BY sal+NVL(comm,0) DESC) AS rankk,
ROUND(CUME_DIST() over (ORDER BY sal+NVL(comm,0) DESC) ,2) DistribVenit
FROM emp)
WHERE rankk<=10;

    P6 /*Afisati informatiile despre angajati impartiti in 4 buchete. Afisati urmatoarele informatii:
numarul current, pozitia nivelului de venit, numarul buchetului, numele angajatului, salarul,
comisionul si venitul (salar + commision).*/

SELECT ROW_NUMBER() over (ORDER BY sal+NVL(comm,0) DESC) AS CRT,
RANK() over (ORDER BY sal+NVL(comm,0) DESC) AS PozitieVenit,
NTILE(4) over (ORDER BY sal+NVL(comm,0) DESC) AS NTILE,
ename, sal, comm, sal+NVL(comm,0) AS Venit
FROM emp;

    P7 /*Afisati clasamentul vanzarilor canalelor de distributie pentru lunile '2000-09' si '2000-10' si
tara US. Informatii: numele canalului, valoarea vanzarilor si pozitia in clasament.*/





SELECT COUNT(*) FROM (SELECT ch.channel_desc,s.amount_sold,
DENSE_RANK() over (ORDER BY s.amount_sold DESC)
FROM channels ch, sales s,times t, countries k, customers cust
WHERE ch.channel_id=s.channel_id AND
      s.time_id=t.time_id AND
      s.cust_id= cust.cust_id AND
      cust.country_id= k.country_id AND
      (t.calendar_month_desc='2000-09' OR t.calendar_month_desc='2000-10') AND
      k.country_id='US');

    P8 /*Afisati clasamentul vanzarilor pe canale de distributie, pentru lunile '2000-08', '2000-09',
'2000-10', '2000-11’. Informatii: channel_desc, calendar_month_desc, total vanzari, pozitia in
cadrul canalului de distributie.*/

SELECT ch.channel_desc, t.calendar_month_desc, s.amount_sold,
DENSE_RANK() over (PARTITION BY ch.channel_desc ORDER BY s.amount_sold DESC) Pozitie
FROM channels ch, sales s, times t
WHERE ch.channel_id=s.channel_id AND
      s.time_id=t.time_id AND
      t.calendar_month_desc IN ('2000-08','2000-09','2000-10','2000-11');

    P9 /*La interogarea anterioara adaugati si pozitia in cadrul lunii.*/

SELECT ch.channel_desc, t.calendar_month_desc, s.amount_sold,
DENSE_RANK() over (PARTITION BY ch.channel_desc ORDER BY s.amount_sold DESC) Pozitie,
RANK() over (ORDER BY t.calendar_month_desc DESC) PozitieLuna
FROM channels ch, sales s, times t
WHERE ch.channel_id=s.channel_id AND
      s.time_id=t.time_id AND
      t.calendar_month_desc IN ('2000-08','2000-09','2000-10','2000-11');

    P10 /*Afisati totalul vanzarilor pe canalul de distributie si pe tara impreuna cu pozitionarea in
cadrul acestui grup (channel_desc, country_id), precum si liniile de subtotaluri generate pentru
combinarea celor doua dimensiuni. Se filtreaza liniile pentru canalele: 'Direct Sales', 'Internet',
luna: '2000-09' si tarile: 'UK', 'US', 'JP'. Informatii: channel_desc, country_id, total vanzari,
pozitie in grup.*/


SELECT ch.channel_desc, SUM(s.amount_sold), k.country_id,
GROUPING (ch.channel_desc) AS gchannel,
GROUPING (k.country_id) AS gcountry,
RANK() over (ORDER BY SUM(s.amount_sold)) rankk
FROM channels ch, sales s, countries k, customers cust, times t
WHERE ch.channel_id=s.channel_id AND
          s.cust_id= cust.cust_id AND
          s.time_id=t.time_id AND
          cust.country_id= k.country_id AND
          ch.channel_desc IN ('Direct Sales', 'Internet')  AND
          t.calendar_month_desc = '2000-09' AND
          k.country_id IN ('UK', 'US', 'JP')
GROUP BY cube(ch.channel_desc,k.country_id);

P11 /*Afisati topul primelor 5 tari la vanzari pe luna ‘2000-09’ (tara, total vanzari, pozitie).*/

SELECT * FROM(
SELECT k.country_id, SUM(s.amount_sold),
  DENSE_RANK() over (ORDER BY SUM(s.amount_sold) DESC) AS ranktara
  FROM countries k, sales s, customers cust, times t
    WHERE
      s.cust_id=cust.cust_id AND
      cust.country_id=k.country_id AND
      s.time_id=t.time_id AND
      t.calendar_month_desc='2000-09'
      GROUP BY k.country_id
) WHERE ranktara<=5;

    P 12 /*Afisati suma vanzarilor pe anul 1999, pentru categoria de produse ‘Men’, impartite in 4
buchete dupa suma vanzarilor (luna, vanzarile, numar buchet).*/

SELECT SUM(s.amount_sold), t.calendar_month_desc,
NTILE(4) over (ORDER BY SUM(s.amount_sold)) AS buchet
FROM sales s, times t, products p
WHERE s.time_id=t.time_id AND
      s.prod_id=p.prod_id AND
      p.prod_category='Men' AND
      t.calendar_year='1999'
    GROUP BY (t.calendar_month_desc);





            LAB 4
1) Afisati angajatii si suma salariilor pentru o fereastra cu offset logic, centrata pe data angajarii
cu limitele de plus MINUS o luna. Afisati salarul minim si maxim pentru aceasi fereastra dar cu
limita de plus MINUS 6 luni.

SELECT ename,
     SUM(sal) over (ORDER BY hiredate RANGE BETWEEN INTERVAL '1' MONTH  preceding
                                            AND  INTERVAL '1' MONTH following) AS sumsal_center,
     MIN(sal) over (ORDER BY hiredate RANGE BETWEEN INTERVAL '6' MONTH  preceding
                                            AND  INTERVAL '6' MONTH following) AS minsal_center,
     MAX(sal) over (ORDER BY hiredate RANGE BETWEEN INTERVAL '6' MONTH  preceding
                                            AND  INTERVAL '6' MONTH following) AS maxsal_center

   FROM emp

2) Afisati numarul departamentului, numele angajatului, salarul si suma salariilor cumulative
pana la angajatul curent pe departamente. Ordonarea se face dupa salar. Se considera fereastra
cu offset fizic si apoi cu offset logic. Comparati rezultatele.

--select deptno,ename,sal,
  --      sum(sal) over (partition by deptno order by ename range unbounded preceding) as salCumul
    --    from emp
SELECT deptno,ename,sal,
        SUM(sal) over (PARTITION BY deptno ORDER BY ename rows unbounded preceding) AS salCumul
        FROM emp


3) La interogarea anterioara adaugati comisionul si venitul total (salar + comision) si afisati
suma cumulativa a veniturilor. Inlocuiti partitionarea pe departament cu cea pe job. Rulati
interogarea pentru ferestre cu offset fizic si logic.

/*select deptno,ename,sal,comm, sal + nvl(comm,0) as Venit,
          sum(sal + nvl(comm,0)) over (partition by job order by ename range unbounded preceding) as salCumul
        from emp */
 SELECT deptno,ename,sal,comm, sal + NVL(comm,0) AS Venit,
          SUM(sal + NVL(comm,0)) over (PARTITION BY job ORDER BY ename rows unbounded preceding) AS salCumul  FROM emp
/*4) Afisati numarul departamentului, numele, salarul, valoarea functiei fn si suma salariilor
pentru fereastra mobila intre linia curenta si urmatoarele x linii intoarse de fn. Ordonarea se
face dupa departament si salar. Schimbati functia fn cu functia fnjob care intoarce numarul de
angajati pe job minus 1. Schimbati si interogarea astfel incat situatia sa fie pe job in loc de
departament.

create or replace function fn(dno number) return number is
Result number;
res number;
begin
select count(*)-1 into res from emp where deptno = dno;
Result := res;
return(Result);
exception
when others then
Result := 0;
end fn;
/
select deptno,ename,sal,fn(deptno),
        sum(sal) over (order by deptno,sal rows BETWEEN CURRENT ROW and fn(deptno) following) sumsal_dept
        from emp */

 CREATE OR REPLACE FUNCTION fnjob(djob VARCHAR2) RETURN NUMBER IS
Result NUMBER;
res NUMBER;
BEGIN
SELECT COUNT(*)-1 INTO res FROM emp WHERE job = djob;
Result := res;
RETURN(Result);
EXCEPTION
WHEN OTHERS THEN
Result := 0;
END fnjob;
/
SELECT deptno,ename,sal,fnjob(job),
        SUM(sal) over (ORDER BY job,sal rows BETWEEN CURRENT ROW AND fnjob(job) following) sumsal_dept
        FROM emp



5) Afisati jobul, numele, salarul curent, salarul anterior si salarul urmator pentru angajati pe
job-uri.

SELECT job,ename,sal,
        LAG(sal,1) over (PARTITION BY job ORDER BY sal) lagsal,
        LEAD(sal,1) over (PARTITION BY job ORDER BY sal) leadsal
        FROM emp


6) Afisati numarul total de angajati, precum si numarul lor defalcat pe transe de salarii: 0-1000,
1000-2000, 2000-3000, peste 3000.

      SELECT COUNT(e.empno) AS ang_total,
    SUM((CASE WHEN e.sal BETWEEN 0 AND 1000 THEN 1 ELSE 0 END)) AS sal1,
    SUM((CASE WHEN e.sal BETWEEN 1000 AND 2000 THEN 1 ELSE 0 END)) AS sal2,
    SUM((CASE WHEN sal BETWEEN 2000 AND 3000 THEN 1 ELSE 0 END)) AS sal3,
    SUM((CASE WHEN sal>3000 THEN 1 ELSE 0 END)) AS sal4
FROM emp e
ORDER BY sal;


/*7) Afisati suma vanzarilor (amount_sold) pentru clientii 6380 si 6510 grupat pe trimestrele
anului 1999. De asemenea sa se afiseze si vanzarile cumulate pe trimestre de la inceputul
anului 1999. Informatii de afisat: clientul, trimestrul, suma vanzarilor pe trimestrul curent,
suma cumulata pe trimestrele anterioare. Ordonarea si gruparea se face dupa client si trimestru.*/

SELECT cust.cust_last_name,t.calendar_year,t.calendar_quarter_number,SUM(s.amount_sold) Curenta,
   SUM(SUM(s.amount_sold)) over (PARTITION BY cust.cust_last_name ORDER BY t.calendar_quarter_number rows unbounded preceding) suma
     FROM sales s, times t, customers cust
      WHERE s.time_id = t.time_id AND
              s.cust_id = cust.cust_id AND
              cust.cust_id IN (6380,6510) AND
               t.calendar_year = '1999'
                 GROUP BY cust.cust_last_name,t.calendar_year,t.calendar_quarter_number

              /*8) Afisati suma vanzarilor pentru clientul 6380 pe anul 1999 pe luni. Informatii de afisat:
clientul, luna si anul, suma vanzarilor pe luna. Sa se adauge media sumei vanzarilor pe luna
curenta si pe anterioarele 2 luni. Ordonarea si gruparea se face dupa client si luna.*/

SELECT cust.cust_last_name, t.calendar_month_number, t.calendar_year,SUM(s.amount_sold),
       AVG(SUM(s.amount_sold)) over (ORDER BY t.calendar_month_number rows BETWEEN  2 preceding AND CURRENT ROW ) bla
        FROM times t,customers cust,sales s
        WHERE s.time_id = t.time_id AND
              s.cust_id = cust.cust_id AND
              cust.cust_id = 6380 AND
              t.calendar_year = '1999'
              GROUP BY  cust.cust_last_name, t.calendar_month_number, t.calendar_year


        9) Afisati suma vanzarilor (amount_sold) pe zile pentru clientii 6380 si 6510, pentru anul 1999
si saptamana 51. Adaugati media sumei vanzarilor pe o fereastra de 3 zile (una inainte si una
dupa ziua curenta). Ordonarea si gruparea se face dupa client si zi (time_id).

SELECT cust.cust_last_name,t.day_number_in_week, t.calendar_year,SUM(s.amount_sold) AS Suma,
  AVG(SUM(s.amount_sold)) over (PARTITION BY cust.cust_last_name
          ORDER BY t.day_number_in_week rows BETWEEN 1 preceding  AND 1 FOLLOWING) AvgSuma
     FROM sales s, times t, customers cust
      WHERE s.time_id = t.time_id AND
              s.cust_id = cust.cust_id AND
              cust.cust_id IN (6380,6510) AND
               t.calendar_year = '1999' AND
               t.fiscal_week_number = 51
                 GROUP BY cust.cust_last_name,t.day_number_in_week, t.calendar_year


10) Afisati suma vanzarilor pe zile intre 10 si 15 octombrie 2000. Adaugati doua coloane, una
care sa afiseze suma vanzarilor pentru linia anterioara si una cu suma vanzarilor pentru linia
urmatoare



SELECT t.day_number_in_month,SUM(s.amount_sold) AS Suma,
 -- sum(sum(s.amount_sold)) over (partition by cust.cust_last_name order by t.day_number_in_week rows BETWEEN 1 preceding  and 1 FOLLOWING) AvgSuma
   LAG(SUM(s.amount_sold),1) over (ORDER BY t.day_number_in_month) SumLag,
   LEAD(SUM(s.amount_sold),1) over (ORDER BY t.day_number_in_month) SumLead
     FROM sales s, times t, customers cust
      WHERE s.time_id = t.time_id AND
              s.cust_id = cust.cust_id AND
               t.calendar_year = '2000' AND
               t.calendar_month_number = 10 AND
               t.day_number_in_month BETWEEN 10 AND 15
                 GROUP BY t.day_number_in_month

            LAB 5


--1) Se dau tabelele PRODUCT, CUSTOMER, ORD, ITEM. Creati o tabela fapta (ôfactö),
--numita ORDITEM care sa contina datele din tabelele ORD si ITEM si care sa aiba coloanele:
--custid, prodid, orderdate, commplan, shipdate, qty, actualprice si itemtot.

--create materialized view log on ord with rowid;
--/
--create materialized view log on item with rowid;
--/
DROP MATERIALIZED VIEW orditem_view
/
DROP MATERIALIZED VIEW orditem_view1
/
CREATE materialized VIEW orditem_view
build IMMEDIATE
AS
SELECT o.custid,o.orderdate,o.commplan,o.shipdate,
         i.prodid,i.qty,i.actualprice,i.itemtot
          FROM ord o, item i
            WHERE o.ordid = i.ordid;
/


--2) Pentru tabelele PRODUCT, CUSTOMER si ORDITEM creati view-urile materializate de tip
--log pentru a folosi tipul de sincronizare rapida (äFAST REFRESHö) . Includeti si coloanele
--care trebuie logate.

DROP MATERIALIZED VIEW customer

CREATE materialized VIEW LOG ON customer
            WITH  ROWID (custid,name,city,repid,creditlimit,state,address) including NEW VALUES;

CREATE materialized VIEW LOG ON product WITH  ROWID
                      (prodid,descrip)  including NEW VALUES;
CREATE materialized VIEW LOG ON orditem_view WITH ROWID including NEW VALUES;


/*3) Creati un view materializat de tip join, fara valori agregat, care sa stocheze informatiile din
comenzi precum si denumirile de produse. View-ul se populeaza imediat cu date, poate fi
sincronizat rapid (FAST REFRESH) atunci cand se comite tranzactia. Afisati informatiile din
view-ul materializat. Inserati o comanda noua si verificati informatiile din view-ul materializat.*/

CREATE materialized VIEW fara_valori_agregat
build IMMEDIATE
refresh force ON COMMIT
AS
 SELECT o.custid,o.orderdate,o.commplan,o.shipdate,o.prodid,o.qty,o.actualprice,o.itemtot,
        p.descrip
          FROM product p,orditem_view o
           WHERE p.prodid = o.prodid;
 /
CREATE materialized VIEW LOG ON  fara_valori_agregat
         WITH ROWID including NEW VALUES



--4) In mod analog create un view materializat de tip join pentru afisarea datelor din comenzi
--precum si informatiile din clienti: nume, oras, stat, limita de credit.

CREATE materialized VIEW ord_customer
build IMMEDIATE
refresh force ON COMMIT
AS
SELECT o.ordid,o.orderdate,o.commplan,o.custid,o.shipdate,o.total,
      c.name,c.city,c.state,c.creditlimit
        FROM ord o,customer c
          WHERE c.custid = o.custid;


/*5) Creati un view materializat care sa stocheze totalul vanzarilor si cantitatile totale pe produse,
view bazat pe cel creat anterior, de tip join. View-ul se populeaza imediat cu date, poate fi
sincronizat rapid (FAST REFRESH) atunci cand se comite tranzactia. Afisati informatiile din
view-ul materializat. Inserati o comanda noua si verificati informatiile din view-ul materializat.
*/

CREATE materialized VIEW total_vinzari
build IMMEDIATE
refresh force ON COMMIT
AS
SELECT s.prod_id,
       SUM(s.amount_sold) AS Total_Vanzari,
       SUM(o.qty) AS Total_Produse
         FROM orditem_view o,sales s
           WHERE s.cust_id = o.custid
             GROUP BY prod_id




6) Creati acelasi tip de VIEW materializat pentru totalul vanzarilor pe clienti. View-ul sa poate
aplica sincronizarea rapida daca este posibil sau sincronizarea completa IN caz contrar.
Executati aceleasi operatii.

CREATE materialized VIEW total_vinzari_pe_clienti
build IMMEDIATE
refresh force ON COMMIT
AS
SELECT custid,
       SUM(itemtot) AS Total_Vanzari,
       SUM(qty) AS Total_Produse
         FROM orditem_view
             GROUP BY custid

/*7). Creati un view materializat pre-inregistrat care sa stocheze suma vanzarilor si a cantitatilor
pe luni calendaristice.
// pag 12 din lab 5
*/
CREATE TABLE pre_inregistrat
AS
SELECT t.calendar_month_number,
     SUM(s.amount_sold) AS Total_Vanzari,
       SUM(s.quantity_sold) AS Total_Cantitati
         FROM sales s,times t
           WHERE s.time_id = t.time_id
             GROUP BY calendar_month_number
 /
 CREATE materialized VIEW pre_inregistrat
  ON prebuilt TABLE without reduced precision
  AS
SELECT t.calendar_month_number,
     SUM(s.amount_sold) AS Total_Vanzari,
       SUM(s.quantity_sold) AS Total_Cantitati
         FROM sales s,times t
           WHERE s.time_id = t.time_id
             GROUP BY calendar_month_number


                8) Modificati view-urile CREATE anterior cu diversi parametri. Stergeti view-uirile CREATE.

ALTER MATERIALIZED VIEW sum_sales_tab
REFRESH FAST;

ALTER MATERIALIZED VIEW sum_sales_tab
REFRESH NEXT SYSDATE+7;

ALTER MATERIALIZED VIEW sum_sales_tab COMPILE;

DROP MATERIALIZED VIEW orditem_view;

LAB 6

    P1 /*Testati exemplele din cadrul laboratorului.*/
--CREATE INDEX idxemp_ename ON emp(ename);
--CREATE UNIQUE INDEX idxemp_ename_unique ON emp(ename);
--SELECT * FROM emp WHERE ename = 'MARTIN';
--CREATE INDEX idxemp_deptno ON emp(deptno);
--SELECT * from emp e, dept d WHERE e.deptno = d.deptno;
--CREATE INDEX idxemp_hiredate ON emp(hiredate);
/*SELECT * from emp WHERE hiredate
BETWEEN to_date('20/02/1981','DD/MM/YYYY') AND
to_date('20/02/1982','DD/MM/YYYY');*/
--CREATE INDEX idxemp_salcomm ON emp(sal,comm);
--SELECT * from emp WHERE sal < 1500 and comm IS NOT NULL;
--CREATE INDEX idxemp_upperemp ON emp(UPPER(ename));
--SELECT * from emp WHERE UPPER(ename) like '%LL%';
/*CREATE BITMAP INDEX custidx_gender ON customers (cust_gender);
CREATE BITMAP INDEX custidx_marital ON customers
(cust_marital_status);
CREATE BITMAP INDEX custidx_income ON customers (cust_income_level);*/
/*SELECT COUNT(*) FROM customers
WHERE cust_marital_status = 'married'
AND cust_income_level IN ('H: 150,000 - 169,999',
'G: 130,000 - 149,999');*/
--DROP INDEX idxemp_deptno;
/*SELECT * FROM USER_INDEXES idx, USER_IND_COLUMNS idxc
WHERE idx.index_name = idxc.index_name;*/
/*EXPLAIN PLAN FOR
SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno and e.sal < 2200;
select plan_table_output from
table(dbms_xplan.display('plan_table',null,'serial'));*/

P2 /*Se dau tabelele PRODUCT, CUSTOMER, ORD, ITEM. Creati urmatorii indexi:
- pentru tabela CUSTOMER:
- creati indexii unici pentru coloana NAME;
- creati indexi simpli, normali pentru CREDITLIMIT si REPID;
- creati un index pe baza de functie care sa optimizeze accesul dupa numele clientului
atunci cand valorile se introduc cu litere mici;
- pentru tabela ORD creati indexii necesari pentru interogarile care implica un join cu tabela de
clienti si care filtreaza liniile dupa data comenzii;*/

--create unique index customer_name_index on customer(name);
--create index customer_creditmilit_index on customer(creditlimit);
--create index customer_repid_index on customer(repid);
--CREATE INDEX idx_customer_name ON customer(lower(name));
--p5
--create index ord_join on ord(custid);

    P3 /*Afisati produsele comandate in lunile de vara ale anului 1986.
Se vor afisa informatiile din comanda, numele clientului,
orasul, statul si limita de credit, numele agentului si numele
departamentului din care face parte,
precum si liniile de detaliu (numele produsului, cantitate,
pretul si valoarea). Creati indexii necesari si afisati-i din dictionarul de date.
Executati interogarea si afisati planul de executie al interogarii.
Analizati acest plan.*/

--create index idx_ord_cust on ord(custid);
--create index idx_cust_emp on customer(repid);
--create index idx_emp_dept on emp(deptno);
--create index idx_ord_item on item(ordid);
--create bitmap index idx_bitmap_ord on ord(orderdate);
explain plan FOR
SELECT o.ordid, o.orderdate,c.name,c.city,c.state,c.creditlimit,
e.ename,d.dname,i.prodid,i.qty, i.actualprice,i.itemtot
FROM ord o, customer c, emp e, dept d, item i WHERE
      o.custid= c.custid AND
      c.repid= e.empno AND
      e.deptno=d.deptno AND
      o.ordid= i.ordid AND
      o.orderdate BETWEEN '31-MAY-86' AND '31-AUG-87';
SELECT plan_table_output FROM
TABLE(DBMS_XPLAN.display('plan_table',NULL,'serial'));

    P4 /*La interogarea anterioara filtrati liniile de comanda
care au valoare mai mare decat 400, unde valoarea se calculeaza ca
produs intre cantitate si pret. Creati un index corespunzator pentru
optimizarea accesului. Afisati si analizati planul de executie al interogarii.*/

--create bitmap index idx_bitmap_itemtot on item(itemtot);
explain plan FOR
SELECT o.ordid, o.orderdate,c.name,c.city,c.state,c.creditlimit,
e.ename,d.dname,i.prodid,i.qty, i.actualprice,i.itemtot
FROM ord o, customer c, emp e, dept d, item i WHERE
      o.custid= c.custid AND
      c.repid= e.empno AND
      e.deptno=d.deptno AND
      o.ordid= i.ordid AND
      o.orderdate BETWEEN '31-MAY-86' AND '31-AUG-87' AND
      i.itemtot>400;
SELECT plan_table_output FROM
TABLE(DBMS_XPLAN.display('plan_table',NULL,'serial'));

    P5 /*Pe baza tabelelor PRODUCT, CUSTOMER, ORD, ITEM, creati o tabela fapta (“fact”),
numita ORDITEM care sa contina datele din tabelele ORD si ITEM si care sa aiba coloanele:
custid, prodid, orderdate, commplan, shipdate, qty, actualprice si itemtot.
Creati indexi
necesari:
- unic compus pentru client, produs;
- creati un index de tip bitmap pentru coloanele candidate;
- lansati o serie de interogari care sa utilizeze indexii creati;
- afisati si analizati planurile de executie a interogarilor.*/

/*create table orditem2 as
select o.custid, i.prodid, o.orderdate, o.commplan, o.shipdate,
i.qty,i.actualprice, i.itemtot
from ord o, item i
where o.ordid=i.ordid;*/
--create index idx_comp_client_prod on orditem2(custid, prodid);
--create bitmap index idx_bitm_orditem2 on orditem2(commplan);
/*Exemplu:
EXPLAIN PLAN FOR
SELECT e.empno, e.ename, e.job, e.sal, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno and e.sal < 2200;
select plan_table_output from
table(dbms_xplan.display('plan_table',null,'serial'));*/

        LAB 7
CREATE TABLE sales_22nov_ext
( PROD_ID CHAR(10),
CUST_ID CHAR(10),
QTY CHAR(10),
AMOUNT CHAR(10)
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY dir_temp
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE dir_temp:'sales_22nov.bad_xt'
LOGFILE dir_temp:'sales_22nov.log_xt'
FIELDS TERMINATED BY "|"
MISSING FIELD VALUES ARE NULL
( PROD_ID CHAR(10),
CUST_ID CHAR(10),
QTY CHAR(10),
AMOUNT CHAR(10)
)
)
location
(
'sales_22nov.log'
)
)REJECT LIMIT UNLIMITED;

SELECT * FROM sales_22nov_ext;

SET echo off
SET pagesize 0
SPOOL customer_usa.LOG
SELECT DISTINCT t2.cust_id ||'|'|| t2.cust_first_name ||' ' ||
t2.cust_last_name ||'|'|| t2.cust_gender ||'|'||
t2.cust_marital_status ||'|'|| t2.cust_income_level ||'|'||
t2.cust_credit_limit ||'|'||t2.cust_city||'|'||t2.cust_state_province
FROM countries t1, customers t2
WHERE t1.country_id = t2.country_id
AND t1.country_name= 'United States of America';
SPOOL off


SELECT * FROM SALES WHERE TO_CHAR(TIME_ID,'DD-MON-YY')='22-NOV-98';

CREATE TABLE sales_test (
PROD_ID NUMBER(6) NOT NULL,
CUST_ID NUMBER(6) NOT NULL,
TIME VARCHAR2(10) NOT NULL,
QTY NUMBER(3),
AMOUNT NUMBER(10,2)
);

SET echo off
SET pagesize 0
SPOOL D:\Temp\sales_22nov.LOG
SELECT DISTINCT t1.prod_id ||'|'|| t1.cust_id ||'|'||
t1.quantity_sold ||'|'||
t1.amount_sold ||'|'
FROM sales t1
WHERE TO_CHAR(t1.time_id,'DD-MON-YY') = '22-NOV-98';
SPOOL off

LAB 8

    P1 /*Conectati-va la schema DMxx. Extrageti clientii din CUSTOMERS intr-un tabel CUST_EXT dupa urmatoarele criterii:
- clientii din UK;
- se vor extrage urmatoarele coloane:*/

DROP TABLE cust_ext;
CREATE TABLE cust_ext AS
(SELECT CUST_ID,
        CUST_GENDER,
        CUST_YEAR_OF_BIRTH,
        CUST_MARITAL_STATUS,
        CUST_CITY,
        CUST_STATE_PROVINCE,
        COUNTRY_ID,
        CUST_INCOME_LEVEL,
        CUST_CREDIT_LIMIT
        FROM customers WHERE
        country_id IN (SELECT country_id FROM countries WHERE country_name='United Kingdom'));

    P2 /*Creati tabelul CUST_DM cu urmatoarea structura: CUST_ID GENDER AGE MARITAL_STATUS CITY STATE_PROVINCE COUNTRY_ID INCOME_ID CREDIT_LIMIT
Se populeaza cu datele din CUST_EXT si se respecta urmatoarele reguli:
- toate valorile null se inlocuiesc cu ‘?’ sau cu -1;
- valorile pentru AGE se calculeaza;
INCOME_ID este un ID cu valori intre A si L care refera spre tabelul nou creat INCOME_LEVEL cu structura: INCOME_ID, LIM_INF,LIM_SUP.*/

/*create table cust_dm (
CUST_ID number,
GENDER  char(1),
AGE number,
MARITAL_STATUS varchar2(20),
CITY varchar2(30),
STATE_PROVINCE varchar2(40),
COUNTRY_ID number not null,
INCOME_ID varchar2(20),
CREDIT_LIMIT number
);*/
/*create table income_level as(
select income_id,
CASE
  WHEN lim_inf is NULL AND INSTRC(lim_sup, 'Below', 1, 1) > 0 THEN '0,000'
  WHEN lim_inf is NULL AND INSTRC(lim_sup, 'and above', 1, 1) > 0 THEN substr(lim_sup,2,7)
  ELSE TRIM(lim_inf)
END "lim_inf",
CASE
  WHEN lim_inf is NULL AND INSTRC(lim_sup, 'Below', 1, 1) > 0 THEN substr(lim_sup,8,6)
  WHEN lim_inf is NULL AND INSTRC(lim_sup, 'and above', 1, 1) > 0 THEN '999,999'
  ELSE TRIM(lim_inf)
END "lim_sup"
from
(
select income_level as income_id, lim_inf, lim_sup from
(select income_level, substr(limits,1,instr(limits,'-')-1) as lim_inf,
substr(limits,1+instr(limits,'-')) as lim_sup from
(select distinct substr(cust_income_level,1,instr(cust_income_level,':')-1) as income_level,
substr(cust_income_level,1+instr(cust_income_level,':')) as limits from customers order by income_level))
)
);
select * from income_level;*/
/*create table cust_dm(
CUST_ID number,
GENDER  char(1),
AGE number,
MARITAL_STATUS varchar2(20),
CITY varchar2(30),
STATE_PROVINCE varchar2(40),
COUNTRY_ID char(2),
INCOME_ID varchar2(20),
CREDIT_LIMIT number
);*/
--delete cust_dm;
INSERT INTO cust_dm
SELECT c.cust_id,
      c.cust_gender,
      TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))-c.cust_year_of_birth AS AGE,
      NVL(c.cust_marital_status,'?'),
      c.cust_city,
      c.cust_state_province,
      c.COUNTRY_ID,
      l.income_id,
      c.CUST_CREDIT_LIMIT FROM cust_ext c, income_level l
     WHERE SUBSTR(c.cust_income_level,1,1)= l.income_id ;