dbd_export_lab5(not mine)

--------------------------------------------------------
--  File created - luni-octombrie-31-2016
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table CATEGORIES
--------------------------------------------------------

  CREATE TABLE "CATEGORIES"
   (	"ID_CATEGORY" NUMBER(20,0),
	"NAME" VARCHAR2(20 BYTE),
	"ID_PRODUCT" NUMBER(20,0),
	"ID_PARENTCATEGORY" NUMBER(10,0)
   ) ;
/
--------------------------------------------------------
--  DDL for Table CLIENTS
--------------------------------------------------------

  CREATE TABLE "CLIENTS"
   (	"ID_CLIENT" NUMBER(20,0),
	"NAME" VARCHAR2(20 BYTE)
   ) ;
/
--------------------------------------------------------
--  DDL for Table ORDERS
--------------------------------------------------------

  CREATE TABLE "ORDERS"
   (	"ID_ORDER" NUMBER(20,0),
	"CONFIRMED " VARCHAR2(20 BYTE),
	"ID_CLIENT" NUMBER(20,0),
	"ID_PRODUCT" NUMBER(20,0)
   ) ;
/
--------------------------------------------------------
--  DDL for Table PRODUCTS
--------------------------------------------------------

  CREATE TABLE "PRODUCTS"
   (	"ID" NUMBER,
	"NAME" VARCHAR2(20 BYTE)
   ) ;
/
--------------------------------------------------------
--  DDL for Table PROPERTIES
--------------------------------------------------------

  CREATE TABLE "PROPERTIES"
   (	"ID_PROPERTIES" NUMBER,
	"COLOR" VARCHAR2(20 BYTE),
	"WEIGHT" NUMBER,
	"PRICE" NUMBER(7,0),
	"ID_PRODUCT" NUMBER(20,0)
   ) ;
/
--------------------------------------------------------
--  DDL for View A
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "A" ("PRODUCT_NAME", "CLIENT_NAME") AS
  SELECT p1.NAME AS PRODUCT_NAME, c1.NAME AS CLIENT_NAME
from PRODUCTS p1, CATEGORIES c1,PROPERTIES pr
where PRICE > 4000 AND p1.ID=c1.ID_PRODUCT AND p1.ID=pr.ID_PRODUCT;
/
--------------------------------------------------------
--  DDL for View B
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "B" ("NAME", "ORDEREDPRODUCTS") AS
  SELECT c1.NAME, (select count(*) FROM orders o where o.ID_CLIENT=c1.ID_CLIENT) as ORDEREDPRODUCTS From clients c1;
/
--------------------------------------------------------
--  DDL for View C
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "C" ("NAME", "NUMBEROFCATEGORIES") AS
  SELECT DISTINCT c1.NAME, ct1.ID_CATEGORY AS NUMBEROFCATEGORIES from clients c1, products p1, categories ct1, orders o1
where c1.ID_CLIENT=o1.ID_CLIENT and ct1.ID_CATEGORY = (select count(ct2.ID_PRODUCT) from categories ct2
where ct2.ID_PRODUCT=o1.ID_PRODUCT) ORDER BY ct1.ID_CATEGORY DESC;
/
--------------------------------------------------------
--  DDL for View D
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "D" ("NAME", "TOTALPRICEOFPRODUCTS") AS
  select DISTINCT c1.name, avg(pr1.PRICE) as totalPriceOfProducts from clients c1, orders o1, properties pr1
where pr1.ID_PRODUCT=o1.ID_PRODUCT and c1.ID_CLIENT=o1.ID_CLIENT GROUP BY c1.name order by avg(pr1.PRICE) desc;
/
REM INSERTING into CATEGORIES
SET DEFINE OFF;
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('1','Mancare','1',null);
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('2','Piese','6',null);
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('3','Lactate','1','1');
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('4','Carne','2','1');
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('5','Lactate','3','1');
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('6','Lactate','4','1');
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('7','Carne','5','1');
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('8','Mancare','2',null);
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('9','Mancare','3',null);
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('10','Mancare','4',null);
Insert into CATEGORIES (ID_CATEGORY,NAME,ID_PRODUCT,ID_PARENTCATEGORY) values ('11','Mancare','5',null);
REM INSERTING into CLIENTS
SET DEFINE OFF;
Insert into CLIENTS (ID_CLIENT,NAME) values ('1','David');
Insert into CLIENTS (ID_CLIENT,NAME) values ('2','Nick');
Insert into CLIENTS (ID_CLIENT,NAME) values ('3','Cioti');
Insert into CLIENTS (ID_CLIENT,NAME) values ('4','Alex');
REM INSERTING into ORDERS
SET DEFINE OFF;
Insert into ORDERS (ID_ORDER,CONFIRMED ,ID_CLIENT,ID_PRODUCT) values ('1','YES','1','1');
Insert into ORDERS (ID_ORDER,CONFIRMED ,ID_CLIENT,ID_PRODUCT) values ('2','YES','2','6');
Insert into ORDERS (ID_ORDER,CONFIRMED ,ID_CLIENT,ID_PRODUCT) values ('3','YES','3','3');
Insert into ORDERS (ID_ORDER,CONFIRMED ,ID_CLIENT,ID_PRODUCT) values ('4','NO','1','4');
Insert into ORDERS (ID_ORDER,CONFIRMED ,ID_CLIENT,ID_PRODUCT) values ('5','YES','1','2');
REM INSERTING into PRODUCTS
SET DEFINE OFF;
Insert into PRODUCTS (ID,NAME) values ('1','Lapte
	');
Insert into PRODUCTS (ID,NAME) values ('2','Salam');
Insert into PRODUCTS (ID,NAME) values ('3','Branza');
Insert into PRODUCTS (ID,NAME) values ('4','Cascaval');
Insert into PRODUCTS (ID,NAME) values ('5','Sunca');
Insert into PRODUCTS (ID,NAME) values ('6','Jante');
REM INSERTING into PROPERTIES
SET DEFINE OFF;
Insert into PROPERTIES (ID_PROPERTIES,COLOR,WEIGHT,PRICE,ID_PRODUCT) values ('1','red','1400','2900','1');
Insert into PROPERTIES (ID_PROPERTIES,COLOR,WEIGHT,PRICE,ID_PRODUCT) values ('2','black','1500','3450','2');
Insert into PROPERTIES (ID_PROPERTIES,COLOR,WEIGHT,PRICE,ID_PRODUCT) values ('3','white','1200','8000','3');
Insert into PROPERTIES (ID_PROPERTIES,COLOR,WEIGHT,PRICE,ID_PRODUCT) values ('4','purple','1300','2190','4');
Insert into PROPERTIES (ID_PROPERTIES,COLOR,WEIGHT,PRICE,ID_PRODUCT) values ('5','white','1800','7500','5');
Insert into PROPERTIES (ID_PROPERTIES,COLOR,WEIGHT,PRICE,ID_PRODUCT) values ('6','black','2500','1950','6');
--------------------------------------------------------
--  DDL for Index ORDERS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "ORDERS_PK" ON "ORDERS" ("ID_ORDER")
  ;
/
--------------------------------------------------------
--  DDL for Index CATEGORIES_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CATEGORIES_PK" ON "CATEGORIES" ("ID_CATEGORY")
  ;
/
--------------------------------------------------------
--  DDL for Index PRODUCTS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "PRODUCTS_PK" ON "PRODUCTS" ("ID")
  ;
/
--------------------------------------------------------
--  DDL for Index CLIENTS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "CLIENTS_PK" ON "CLIENTS" ("ID_CLIENT")
  ;
/
--------------------------------------------------------
--  Constraints for Table PRODUCTS
--------------------------------------------------------

  ALTER TABLE "PRODUCTS" ADD CONSTRAINT "PRODUCTS_PK" PRIMARY KEY ("ID") ENABLE;

  ALTER TABLE "PRODUCTS" MODIFY ("ID" NOT NULL ENABLE);

  ALTER TABLE "PRODUCTS" MODIFY ("NAME" NOT NULL ENABLE);
/
--------------------------------------------------------
--  Constraints for Table PROPERTIES
--------------------------------------------------------

  ALTER TABLE "PROPERTIES" MODIFY ("ID_PROPERTIES" NOT NULL ENABLE);

  ALTER TABLE "PROPERTIES" MODIFY ("PRICE" NOT NULL ENABLE);
/
--------------------------------------------------------
--  Constraints for Table CATEGORIES
--------------------------------------------------------

  ALTER TABLE "CATEGORIES" ADD CONSTRAINT "CATEGORIES_PK" PRIMARY KEY ("ID_CATEGORY") ENABLE;

  ALTER TABLE "CATEGORIES" MODIFY ("ID_CATEGORY" NOT NULL ENABLE);
/
--------------------------------------------------------
--  Constraints for Table ORDERS
--------------------------------------------------------

  ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDERS_PK" PRIMARY KEY ("ID_ORDER") ENABLE;

  ALTER TABLE "ORDERS" MODIFY ("ID_ORDER" NOT NULL ENABLE);
/
--------------------------------------------------------
--  Constraints for Table CLIENTS
--------------------------------------------------------

  ALTER TABLE "CLIENTS" ADD CONSTRAINT "CLIENTS_PK" PRIMARY KEY ("ID_CLIENT") ENABLE;

  ALTER TABLE "CLIENTS" MODIFY ("ID_CLIENT" NOT NULL ENABLE);
/
--------------------------------------------------------
--  Ref Constraints for Table CATEGORIES
--------------------------------------------------------

  ALTER TABLE "CATEGORIES" ADD CONSTRAINT "CATEGORIES_PRODUCTS_FK1" FOREIGN KEY ("ID_PRODUCT")
	  REFERENCES "PRODUCTS" ("ID") ENABLE;
/
--------------------------------------------------------
--  Ref Constraints for Table ORDERS
--------------------------------------------------------

  ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDERS_CLIENTS_FK1" FOREIGN KEY ("ID_CLIENT")
	  REFERENCES "CLIENTS" ("ID_CLIENT") ENABLE;

  ALTER TABLE "ORDERS" ADD CONSTRAINT "ORDERS_PRODUCTS_FK1" FOREIGN KEY ("ID_PRODUCT")
	  REFERENCES "PRODUCTS" ("ID") ENABLE;
/
--------------------------------------------------------
--  Ref Constraints for Table PROPERTIES
--------------------------------------------------------

  ALTER TABLE "PROPERTIES" ADD CONSTRAINT "PROPERTIES_PRODUCTS_FK1" FOREIGN KEY ("ID_PRODUCT")
	  REFERENCES "PRODUCTS" ("ID") ENABLE;
/