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;
