Exercitii Oracle/SQL+

Trupele care n-au avut concerte in aceeasi statiune mai mult de o data la 2 ani si care n-au avut niciun concert in ultimul an. (au avut concerte in aceeasi statiune dupa o perioada mai mare de 2 ani de la concertul precedent si n-au avut niciun concert in ultimul an )

Rezultat dorit: Narcotic

create table trupe (id number(2) primary key, nume varchar2(30) check (nume = initcap(nume)));
create table statiuni (id number(2) primary key, nume varchar2(30) check (nume = initcap(nume)));
create table concerte (idc number(2) primary key, idt number(2) references trupe(id), ids number(2) references statiuni(id), data date);

insert into trupe values (1, 'Vama');
insert into trupe values (2, 'Voltaj');
insert into trupe values (3, 'Narcotic');
insert into trupe values (4, 'Habibwahid');
insert into statiuni values (1, 'Saidia');
insert into statiuni values (2, 'Antalya');
insert into statiuni values (3, 'Chittagong');
insert into statiuni values (4, 'Albena');
insert into concerte values (1, 1, 2, '5-JUN-03');
insert into concerte values (2, 1, 2, '5-JUN-05');
insert into concerte values (3, 2, 4, '5-JUN-07');
insert into concerte values (4, 3, 4, '6-JUN-00');
insert into concerte values (5, 4, 1, '5-JUN-07');
insert into concerte values (6, 4, 3, '5-JUN-09');
insert into concerte values (7, 4, 2, '5-AUG-10');
insert into concerte values (8, 2, 4, '5-JUN-10');
insert into concerte values (9, 3, 4, '5-JUN-04');

select nume from trupe
where id NOT IN
(select DISTINCT c1.idt
from concerte c1, concerte c2
where (c1.idc != c2.idc AND abs(c1.data-c2.data)/365.5<=2 AND c1.ids = c2.ids)
OR (sysdate-c1.data)/365<1 );

-------------------------- mai multe aici ---------------------------------------



Click pe full screen