Crearea bazelor de date, indecsilor, view-urilor si clusterelor

DROP TABLE COMANDA;

CREATE TABLE  COMANDA

(NR_COM NUMBER(8) NOT NULL PRIMARY KEY,

DATA DATE,

COD_CLIENT VARCHAR(10),

DEN_CLIENT VARCHAR(20),

LOC VARCHAR(10),

STR VARCHAR(10),

NR VARCHAR(20)

CODF VARCHAR(10),

CONTB VARCHAR(25),

BANCA CHARACTER(3),

DEN PROD VARCHAR(30),

UM VARCHAR(3),

PRET_UNITAR NUMBER(10),

CANT_COM NUMBER(10),

DATA_LIVR DATE,

CANT_DE_LIVR NUMBER(10));

CREATE INDEX  ICOMANDA

ON COMANDA(NR_COM);

DROP TABLE RANDCOM;

CREATE TABLE RANDCOM

(NR_RANDCOM NUMBER(8) NOT NULL PRIMARY KEY,

DATA_RANDCOM DATE,

COD_CLIENT VARCHAR(10),

DEN_CLIENT VARCHAR(20),

LOC VARCHAR(10),

STR VARCHAR(10),

NR VARCHAR(20),

CODF VARCHAR(10),

CONTB VARCHAR(25),

BANCA CHARACTER(3),

DATA_DE_LIVR DATE,

DEN_PROD VARCHAR(30),

CANT_COM NUMBER(10),

UM CHARACTER(3),

PRET_UNITAR NUMBER(10),

TVA NUMBER(2));

CREATE INDEX  IRANDCOM

ON RANDCOM(NR_RANDCOM);

DROP TABLE LIVRARE;

CREATE TABLE LIVRARE

(NR_LIVR NUMBER(8) NOT NULL PRIMARY KEY,

COD_CLIENT VARCHAR(10),

DEN_CLIENT VARCHAR(20),

STR VARCHAR(10),

LOC VARCHAR(10),

NR VARCHAR(20),

CODF VARCHAR(10),

CONTB VARCHAR(25),

BANCA CHARACTER(3),

NR_AVIZ NUMBER(8),

DATA_AVIZ DATE,

MIJL_TRANSP VARCHAR(20),

NR_MIJL VARCHAR(10),

COD_PROD VARCHAR(10),

DEN_PROD VARCHAR(30),

UM CHARACTER(3),

PRET_UNITAR NUMBER(10),

TVA NUMBER(2),

CANT_LIVR NUMBER(10));

CREATE INDEX  ILIVRARE

ON LIVRARE(NR_LIVR);

DROP TABLE RANDLIVR;

CREATE TABLE RANDLIVR

(NR_AVIZ NUMBER(8) NOT NULL PRIMARY KEY,

DATA_AVIZ DATE,

COD_FIRMA VARCHAR(10),

DEN_CLIENT VARCHAR(20),

LOC VARCHAR(10),

STR VARCHAR(10),

NR VARCHAR(20),

CODF VARCHAR(10),

CONTB VARCHAR(25),

BANCA CHARACTER(3),

MIJL_TRANSP VARCHAR(20),

NR_MIJL VARCHAR(10),

CODPROD VARCHAR(10),

DENPROD VARCHAR(30),

UM CHARACTER(3),

PRET_UNITAR NUMBER(10),

TVA NUMBER(2),

CANT_LIVR NUMBER(10));

CREATE INDEX  IRANDLIVR

ON RANDLIVR(NR_AVIZ);

DROP TABLE FACTURA;

CREATE TABLE FACTURA

(NRFACT NUMBER(8) NOT NULL PRIMARY KEY,

DATA_FACT DATE,

DEN_CLIENT VARCHAR(20),

CODF VARCHAR (10),

CONTB VARCHAR(25),

BANCA CHARACTER(3),

NR_AVIZ NUMBER(8),

DATA_AVIZ DATE,

NR_MIJL VARCHAR(10),

MIJL_TRANSP VARCHAR(20),

DEN_PROD VARCHAR(30),

CANT NUMBER(10),

UM CHARACTER(3),

PRET_UNITAR NUMBER(10),

TVA NUMBER(2));

CREATE INDEX IFACTURA

ON FACTURA(NRFACT);

DROP TABLE RANDFACT;

CREATE TABLE RANDFACT

(NR_RAND_FACT NUMBER(8) NOT NULL PRIMARY KEY,

DEN_CLIENT VARCHAR(20),

LOC VARCHAR(10),

STR VARCHAR(10),

NR VARCHAR(20),

CODF VARCHAR(10),

CONTB VARCHAR(25),

BANCA CHARACTER(3),

NR_AVIZ NUMBER(5),

NR_MIJL VARCHAR(10),

MIJL_TRANSP VARCHAR(20),

DEN_PROD VARCHAR(30),

COD_PROD VARCHAR(10),

UM CHARACTER(3),

CANT NUMBER(10),

PRET_UNITAR NUMBER(10),

TVA NUMBER(2));

CREATE INDEX IRANDFACTURA

ON RANDFACTURA(NR_RAND_FACT);

DROP TABLE PRODUS;

CREATE TABLE PRODUS

(COD_PROD VARCHAR(10) NOT NULL PRIMARY KEY,

DEN_PROD VARCHAR(30),

UM CHARACTER(3),

PRET_UNITAR NUMBER(10),

TVA NUMBER(2),

STOC_PROD NUMBER(10));

CREATE VIEW PRODUS_SOLD AS

SELECT COD_PROD, DEN_PROD, STOC_PROD

FROM PRODUS;

CREATE INDEX  IPRODUS

ON PRODUS (COD_PROD);

DROP TABLE  CLIENT;

CREATE TABLE CLIENT

(COD_CLIENT VARCHAR(10) NOT NULL PRIMARY KEY,

DEN_CLIENT VARCHAR(20),

CODF VARCHAR(10),

LOC VARCHAR(10),

STR VARCHAR(10),

NR VARCHAR(20),

TEL VARCHAR(10),

CONTB VARCHAR(25),

BANCA CHARACTER(3),

SOLDCRT NUMBER(10));

CREATE VIEW CLIENTSOLD AS

SELECT COD_CLIENT, DEN_CLIENT,SOLDCRT

FROM CLIENT;

CREATE INDEX  ICLIENT

ON CLIENT(COD_CLIENT);

SELECT COD_CLIENT, NR_LIVRA, COD_PROD, CANT_LIVR, PRET_UNITAR

FROM CLIENT,LIVRARE;

SELECT NR_AVIZ, COD_CLIENT, COD_PROD, CANT_LIVR, PRET_UNITAR

FROM RANDLIVR, PRODUS;

SELECT NR_COM, COD_CLIENT, NR_RANDCOM, COD_PROD, DATA_DE_LIVR, CANT_DE_LIVR

FROM COMANDA, RANDCOM;

SELECT  NR_FACT, COD_CLIENT, NR_AVIZ, COD_PROD, CANT_LIVR, PRET_UNITAR

FROM FACTURA, RANDFACT;

SELECT NR_CERERE, COD_CLIENT, NR_RANDCERERE, COD_PROD

FROM CERERE, RANDCERERE;

SELECT NR_REP, COD_CLIENT, NR_PVREP, COD_PROD

FROM REPARATIE, RANDREP;