Proiect Baze de Date

Sală de sport

Ene Raluca, grupa 1026, seria B

Instructoriid instructor

numeprenumecalificare

data_angajare

Membriiid_membru

numeprenume

cod ab

Claseid clasa

tipdificultate

id instructorid salacod ab

Salaid sala

nume sala

Abonamentcod ab

tipdurata

pret

Clase:

Kangoo Jumps, Zumba Fitness, Tae Bo, Pilates, Cycling, Silhouette Contour, Stretching, Strength Cardio, Functional Training, Aerial Yoga, Rebound AAS, Butt&Abs, Abt-Abs, Fitball, TRX, Aerobic, Step Tone,

Passion Dance, Interval Training, XTreme Fitness, aqua gym, inot, fitness

Sali: 01 fitness, 02 piscina, 03 dans, 04 aerobic, 05 cycling, 06 kombat

Instructor:

100Valentin Mavrodineanu: Fitness, Kangoo Jumps, Cycling, Aerial Yoga, Rebound AAS - Personal Trainer

101Sorin Geana: Arte Martiale-Ashihara, Aerobic, Tae-bo, X-treme, Kombat, Cycling, TRX - Personal Trainer

formator, personal trainer

102Denisa Ganea: Zumba, Aerobic, Aerial Yoga, Bokwa Fitness, Pilates, Aerial Yoga, Rebound AAS, Step Tone, Cardio Tone, AquaGym

103 Alexandru Bors: Aerobic, Pilates, Stretching, Cardio Tone, Aerial Yoga, Dance Aerobic, Arms&Legs

104Alexandra Ghezea: Kangoo Jumps, Aerial Yoga

105Mihai Visan: Tae Bo, Strength Cardio, Cardio Tone, Functional Training, Circuit Training, Cycling, Interval Training - Personal Trainer

106Ciprian Polexe: Fitness

107Andreea Preda: Kangoo Jumps, Aerobic, Pilates, Rebound, Cycling

108Gabriel Niculescu: inot

109Diana Dumitrescu: Aerobic, AquaGym Therapy, Kangoo Jumps - Personal Trainer

• Abonament

• cod ab

• tip

• id clasa

• durata

• pret

Aerobic Full:

12, 6 , 3, 1 luni

1600, 900, 550, 250 ron

Fitness Full: Fitness

12, 6, 3, 1 luni

1520, 820, 500, 215 ron

Aero-Fit Full

12, 6, 3, 1 luni

1680, 980, 580, 220 ron

Aero-Fit Daytime

3, 1 luni

285, 130 ron

Piscina Full: 3, 1 luni: 640, 250

Piscina Day: 3, 1 luni: 510, 200

All Inclusive: 12, 6, 3, 1 luni

2600, 1500, 900, 400 ron

Comenzi DLL: Create, Alter, Drop

Create table Instructori(id_instructor number(3) constraint instructor_pk primary key, nume varchar2(30), prenume varchar2(30), calificare varchar2(30), data_angajare date);

Create table Clase(id_clasa number(3), tip varchar2(30), dificultate varchar2(30), id_instructor number(2) constraint instructor_fk references Instructori(id_instructor));

Create table Abonament(cod_abonament number(5), tip varchar2(30), durata number(2) not null, pret number(5));

Create table Membrii(id_membru number(3) constraint membru_pk primary key, nume varchar2(30), prenume varchar2(30), cod_abonament number(5), durata number(2));

Create table Sala(id_sala number(2) constraint sala_pk primary key, nume varchar2(30));

Create table Instructori_BE as Select* from Instructori;

Alter table MembriiAdd constraint ab_fkey Foreign Key(cod_abonament, durata) references Abonament(cod_abonament, durata);

Alter table Clase Add id_sala number(2);

Alter table Clase Add constraint sala_fk Foreign Key(id_sala) references Sala(id_sala);

Alter table ClaseAdd constraint clasa_pk primary key(id_instructor,id_sala);

Alter table Clase Add durata number(2);

Alter table Clase Add constraint codab_fk Foreign Key(cod_abonament, durata) references Abonament(cod_abonament, durata);

Alter table MembriiAdd varsta number(2);

Alter table MembriiModify varsta varchar2(2);

Alter table MembriiDrop column varsta;

Alter table SalaAdd Constraint sala_ck check (nume IS NOT NULL);

Alter table ClaseRename to Ore;

Rename Ore to Clase;

Drop table Instructori_BE;

Comenzi DLL: Insert, Select, Update, Delete

Insert into Instructori values(100, 'Valentin', 'Mavrodineanu', 'Personal Trainer', TO_DATE('15.10.2011','DD.MM.YYYY'));

Insert into Instructori values(101, 'Sorin', 'Geana ', 'Personal Trainer', TO_DATE('12.04.2009','DD.MM.YYYY'));

Insert into Instructori values(102, 'Denisa', 'Ganea ', NULL , TO_DATE('01.02.2012','DD.MM.YYYY'));

Insert into Instructori values(103, 'Alexandru ', 'Bors', NULL, TO_DATE('15.04.2012','DD.MM.YYYY'));

Insert into Instructori values(104, 'Alexandra ', 'Ghezea', NULL, TO_DATE('20.11.2011','DD.MM.YYYY'));

Insert into Instructori values(105, 'Mihai', ' Visan', 'Personal Trainer', TO_DATE('05.06.2009','DD.MM.YYYY'));

Insert into Instructori values(106, 'Ciprian', 'Polexe ', NULL, TO_DATE('15.08.2010','DD.MM.YYYY'));

Insert into Instructori values(107, 'Andreea ', 'Preda', NULL, TO_DATE('11.01.2013','DD.MM.YYYY'));

Insert into Instructori values(108, 'Gabriel ', 'Niculescu', NULL, TO_DATE('23.05.2011','DD.MM.YYYY'));

Insert into Instructori values(109, 'Diana', 'Dumitrescu', 'Personal Trainer', TO_DATE('17.09.2009','DD.MM.YYYY'));

Insert into Sala values('01','fitness');

Insert into Sala values('02','piscina');

Insert into Sala values('03','dans');

Insert into Sala values('04','aerobic');

Insert into Sala values('05','cycling');

Insert into Sala values('06','kombat');

Insert into Abonament values(1000, 'Aerobic Full', 12, 1600);Insert into Abonament values(1000, 'Aerobic Full', 6, 900);Insert into Abonament values(1000, 'Aerobic Full', 3, 550);Insert into Abonament values(1000, 'Aerobic Full', 1, 250);Insert into Abonament values(2000, 'Fitness Full', 12, 1520);Insert into Abonament values(2000, 'Fitness Full', 6, 820);Insert into Abonament values(2000, 'Fitness Full', 3, 500);Insert into Abonament values(2000, 'Fitness Full', 1, 220);Insert into Abonament values(3000, 'Aero-Fit Full', 12, 1680);Insert into Abonament values(3000, 'Aero-Fit Full', 6, 980);Insert into Abonament values(3000, 'Aero-Fit Full', 3, 580);Insert into Abonament values(3000, 'Aero-Fit Full', 1, 220);Insert into Abonament values(4000, 'Aero-Fit Daytime', 3, 285);Insert into Abonament values(4000, 'Aero-Fit Daytime', 1, 130);Insert into Abonament values(5000, 'Piscina', 3, 640);Insert into Abonament values(5000, 'Piscina', 1, 250);Insert into Abonament values(6000, 'All Inclusive', 12, 2600);Insert into Abonament values(6000, 'All Inclusive', 6, 1500);Insert into Abonament values(6000, 'All Inclusive', 3, 900);Insert into Abonament values(6000, 'All Inclusive', 1, 400);

Insert into Clase values('10','Kangoo Jumps','Ridicata',100,03,1000,NULL);Insert into Clase values('10','Kangoo Jumps','Ridicata',109,03,1000,NULL);Insert into Clase values('10','Kangoo Jumps','Ridicata',107,04,1000,NULL);Insert into Clase values('11','Zumba Fitness','Scazuta',102,04,1000,NULL);Insert into Clase values('12','Tae Bo','Medie',105,06,1000,NULL);Insert into Clase values('12','Tae Bo','Medie',101,03,1000,NULL);Insert into Clase values('13','Pilates','Scazuta',103,04,1000,NULL);Insert into Clase values('14','Cycling','Ridicata',100,05,2000,NULL);Insert into Clase values('14','Cycling','Ridicata',105,05,2000,NULL);Insert into Clase values('15','Stretching','Scazuta',103,06,1000,NULL);Insert into Clase values('16','Strength Cardio','Ridicata',106,04,1000,NULL);Insert into Clase values('17','Aerial Yoga','Scazuta',102,03,1000,NULL);Insert into Clase values('18','Rebound AAS','Ridicata',104,03,1000,NULL);Insert into Clase values('19','Fitball','Scazuta',104,04,1000,NULL);Insert into Clase values('20','TRX','Ridicata',101,06,1000,NULL);Insert into Clase values('21','Aerobic','Medie',107,03,1000,NULL);Insert into Clase values('21','Aerobic','Medie',109,04,1000,NULL);Insert into Clase values('22','Step Tone','Medie',105,04,1000,NULL);Insert into Clase values('23','Passion Dance','Scazuta',103,03,1000,NULL);Insert into Clase values('24','Interval Training','Medie',105,03,1000,NULL);Insert into Clase values('24','Interval Training','Medie',100,04,1000,NULL);Insert into Clase values('24','Interval Training','Medie',101,04,1000,NULL);Insert into Clase values('25','XTreme Fitness','Ridicata',101,01,2000,NULL);Insert into Clase values('20','Aqua Gym','Medie',109,01,5000,NULL);Insert into Clase values('20','Inot','Ridicata',108,01,5000,NULL);Insert into Clase values('20','Fitness','Medie',106,01,2000,NULL);

Insert into Membrii values(184,'Dumitru','Robert', 3000, 1);Insert into Membrii values(549,'Lolea','Diana', 6000,6);Insert into Membrii values(485,'Vasile','Ana',2000,3);Insert into Membrii values(309,'Marin','Emil',1000,12);Insert into Membrii values(948,'Bunea','Maria',5000,3);Insert into Membrii values(135,'Neagu','Dima',2000,12);Insert into Membrii values(247,'Stan','Alexandru',5000,1);Insert into Membrii values(682,'Soare','Irina',1000,3);Insert into Membrii values(348,'Radu','Antonia',4000,1);Insert into Membrii values(728,'Grigore','Adrian',6000,3);Insert into Membrii values(953,'Oprea','Alin',5000,1);Insert into Membrii values(638,'Mitran','Iulia',2000,6);Insert into Membrii values(482,'Lungu','Raluca',3000,12);Insert into Membrii values(137,'Ganea','Carmen',1000,6);Insert into Membrii values(238,'Bica','Ioana',3000,6);Insert into Membrii values(653,'Sitca','Ion',1000,3);Insert into Membrii values(353,'Rodoiu','Andreea',4000,3);Insert into Membrii values(729,'Goicu','Anton',6000,1);Insert into Membrii values(926,'Olariu','Marin',5000,3);Insert into Membrii values(663,'Mutu','Iuliana',2000,1);Insert into Membrii values(403,'Lintoiu','Roxana',3000,6);Insert into Membrii values(174,'Gica','Cristian',1000,12);Insert into Membrii values(282,'Butnaru','Camelia',3000,12);

    

Sa se modifice dificultatea clasei, pentru clasa cu id-ul 14.

Update ClaseSet Dificultate='Medie'Where id_clasa=14;

->>

Sa se mareasca pretul abonamentelor de tip Aerobic, Fitness si Aero-Fit cu durata de 6 luni cu 10%.

Update AbonamentSet Pret=1.1*PretWhere cod_abonament in (1000,2000,3000) and durata=6;

->>

Sa se modifice abonamentele Aero-Fit Daytime in Full ale membrilor cu id-ul intre 100 si 400.

Update MembriiSet cod_abonament=3000Where cod_abonament=4000 and id_membru between 100 and 400;

->>

Sa se stearga membrul clubului cu id-ul 137.

Delete From MembriiWhere id_membru=137;

->>

Sa se afiseze membrii clubului cu abonament Aerobic Full.

Select *From MembriiWhere cod_abonament=1000;

Sa se afiseze membrii clubului cu initiala prenumelui ‘A’.

Select *From MembriiWhere prenume like ‘A%’;

Sa se afiseze numele, prenumele si tipul de abonament al fiecarui membru.

select nume , prenume , tip as abonamentfrom Membrii m, Abonament awhere m.cod_abonament=a.cod_abonament;

Sa se afiseze pretul minim, maxim, si media preturilor abonamentelor.

Select max(pret) as "Pret maxim", min(pret) as "Pret minim", round(avg(pret),2) as "Pret mediu"From abonament;

Sa se afiseze toate clasele lui Mihai Visan si ale lui Sorin Geana.

select id_clasa, tip from clase where id_instructor=105UNION select id_clasa, tip from clase where id_instructor=101;