drop table Score;
drop table Show;
drop table Dog;
drop table Owner;
drop table Judge;
drop table Expertise_Level;

create table Expertise_level
(
  idExpertise      INTEGER,
  Elevel           VARCHAR(70),
  PRIMARY KEY (idExpertise));

  insert into Expertise_level values
  (1,'Expert');

  insert into Expertise_level values
  (2,'Intermediate');

  insert into Expertise_level values
  (3,'Amature');



    create table Judge
(
  idJudge       INTEGER,
  F_name      VARCHAR(70),
  L_name      VARCHAR(70),
  ExpertiseID     INTEGER,
  PRIMARY KEY (idJudge) ,
  FOREIGN KEY  (ExpertiseID) references Expertise_level(idExpertise))
;

   insert into Judge values
  (1,'Ahmad','Nino',1)
;

  insert into Judge values
  (2,'Abdo','fargali',3)
;

  insert into Judge values
  (3,'Matuesz','Natuesz',2)
;

  insert into Judge values
  (4,'Smokes','Biggie',2)
;

  insert into Judge values
  (5,'Portal','Ido',1)
;

create table Owner
(
  idOwner       INTEGER,
  F_name      VARCHAR(70),
  L_name     VARCHAR(70),
  Nationality     VARCHAR(70),
  PRIMARY KEY (idOwner))
;

 insert into Owner values
  (1,'Rostik','Slav','Ukrainian')
;

  insert into Owner values
  (2,'Artem','Lobov','Russian')
;

  insert into Owner values
  (3,'Patem','patty','American')
;

  insert into Owner values
  (4,'Ahmad','Alaziz','Syrian')
;

  insert into Owner values
  (5,'maznakh','wejak','Jordan')
;

 create table Dog
(
  idDog       INTEGER,
  Regestered_name      VARCHAR(70),
  Breed      VARCHAR(70),
  Owner_Id     INTEGER,
  PRIMARY KEY (idDog) ,
  FOREIGN KEY  (Owner_Id) references Owner(idOwner))
;

insert into Dog values
  (1,'Max Klitford','Nova Scotia',1)
;

  insert into Dog values
  (2,'Coastalight Maxarrow Quest SHr','Labrador',3)
;

  insert into Dog values
  (3,'Atlas','German Shepherd',2)
;

  insert into Dog values
  (4,'Chef Von Der Burg Konigstein','Labrador',3)
;

 insert into Dog values
  (5,'MAXY','POODLE',4)
;

 insert into Dog values
  (6,'DOODY','POODLE',5)
;

  insert into Dog values
  (7,'Quori Vom Eichenluft BN','Bernese Mountain Doa',1)
;


create table Show
(
  idShow       INTEGER,
  show_name      VARCHAR(70),
  City      VARCHAR(70),
  Show_Date DATE,
  PRIMARY KEY (idShow))
;

 insert into Show values
  (1, 'Janesville Club',	'Loves Park', TO_DATE('17-07-1999', 'DD-MM-YYYY'))
;

    insert into Show values
  (2,'Cactus StateClub','J.Adalene',TO_DATE('16-03-2010', 'DD-MM-YYYY'))
;

  insert into Show values
  (3,'Greater Association','Warsaw',TO_DATE('20-05-1996', 'DD-MM-YYYY'))
;

  insert into Show values
  (4,'St. Charle Club','Lodz',TO_DATE('18-01-2000', 'DD-MM-YYYY'))
;

  insert into Show values
  (5,'Aloha Association','Riyadh', TO_DATE('12-09-2000', 'DD-MM-YYYY'))
;



  create table Score
(
  idScore    INTEGER,
  Show_Id      INTEGER,
  Dog_Id       INTEGER,
  Judge_Id     INTEGER,
  ScoreValue        INTEGER,
  PRIMARY KEY(idScore),
  foreign key (Show_Id) references Show(idShow),
  foreign key (Dog_Id) references Dog(idDog),
  foreign key (Judge_Id) references Judge(idJudge)
);

 insert into Score values
  (1,2,3,4,70)
;

 insert into Score values
  (2,2,5,4,73)
;
 insert into Score values
  (3,3,6,1,70)
;

  insert into Score values
  (4,2,1,1,80)
;

  insert into Score values
  (5,4,4,3,50)
;

  insert into Score values
  (6,4,2,3,88)
;

  insert into Score values
  (7,1,3,4,88)
;

/*QUERY SHOWING DOGS FOR ALL OWNERS*/
SELECT F_NAME,REGESTERED_NAME
FROM OWNER,DOG
WHERE OWNER.IDOWNER = DOG.OWNER_ID
ORDER BY F_NAME;

/*QUERY SHOWING DOGS AND THEIR OWNER WITH THE OWNER BEING FROM A CERTAIN NATIONALITY (UKRAINIAN) */
SELECT REGESTERED_NAME,F_NAME
FROM   DOG,OWNER
WHERE  DOG.OWNER_ID = OWNER.IDOWNER
AND    OWNER.NATIONALITY = 'Ukrainian';


/*QUERY SHOWING DOGS WITH THEIR SCORE VALUES FOR EVERY SHOW */
SELECT REGESTERED_NAME,SCOREVALUE,SHOW_ID
FROM   DOG,SCORE,SHOW
WHERE  DOG.IDDOG = SCORE.DOG_ID
AND    SHOW.IDSHOW = SCORE.SHOW_ID;


/*QUERY SHOWING THE NUMBER OF DOGS THAT APPLIED TO A CERTAIN SHOW*/
SELECT  COUNT(DOG_ID)
FROM SCORE
WHERE SHOW_ID = 2;

/*QUERY SHOWING DOGS THAT SCORED MORE THAN THE AVERAGE FOR A PARTICULAR SHOW (2)*/
SELECT REGESTERED_NAME,SCOREVALUE,SHOW_ID
FROM   DOG,SCORE
WHERE  DOG.IDDOG = SCORE.DOG_ID
AND    SCORE.SCOREVALUE > (SELECT AVG(SCOREVALUE) FROM SCORE WHERE SCORE.SHOW_ID = 2);

/*QUERY SHOWING DOG AND SCORE OF THE BEST DOG IN ALL EXHIBITIONS*/
SELECT REGESTERED_NAME,SCOREVALUE,SHOW_ID
FROM DOG D, SCORE C
WHERE  D.IDDOG = C.DOG_ID
AND C.SCOREVALUE = (SELECT MAX(SCOREVALUE) FROM SCORE);



/*QUERY SHOWING ALL EXHIBITIONS BETWEEEN 1999 AND 2001*/
SELECT *
FROM SHOW
WHERE SHOW_DATE BETWEEN TO_DATE('01-JAN-1999', 'DD-MON-YYYY' ) AND TO_Date('31-DEC-2001', 'DD-MON-YYYY' );


SELECT MAX(SCOREVALUE) FROM SCORE;