Base De Datos Ejemplo

TRABAJO PRACTICO N°3

Tabla profesor


A: CREATE TABLE prof(leg_prof N(5),nom_prof C(30),fecha_nac date,domicilio C(30),año_inicio N(4))
B: INSERT INTO prof VALUES(100,"lopez,carlos",ctod("01/01/71"),"AAAA",1991)
INSERT INTO prof VALUES (200,"maizares,esteban",ctod("01/01/72"),"BBBB",1992)
INSERT INTO prof VALUES (300,"mendez,sANDra",ctod("01/01/73"),"CCCC",1993)
C: ALTERTABLE prof ADD telefono C(10)
ALTER TABLE prof ADD sexo C(10)
ALTER TABLE prof ALTER domicilio C(25)
D: DELETE FROM prof WHERE leg_prof=200
E: UPDATE prof set sexo=”fem” WHEREleg_prof=300
UPDATE prof set sexo=”masc” WHERE leg_prof=100
F: INSERT INTO prof VALUES (400,”mamani,sergio”,ctod(“01/01/75”),”DDDD”,1998,”4444”,”masc”)
G: INSERT INTO prof VALUES(500,"albarado,daniel",ctod("01/01/74"),"EEEE",1997,"1234","masc")
INSERT INTO prof VALUES (600,"cabero,alberto",ctod("01/01/76"),"FFFF",1999,"4321","masc")
H: SELECT* FROM prof WHERE leg_prof>200 AND leg_prof="HHHH"
F: SELECT nom_mat FROM mat WHERE aula=4 ORDER BY cod_mat DESC


Tabla profesor materias


A: CREATE TABLE prof_mat (leg_prof N(5),cod_mat C(4),plan_est N(5))
B:INSERT INTO prof_mat VALUES(100,"BOGE",1998)
INSERT INTO prof_mat VALUES (100,"QUEL",1997)
INSERT INTO prof_mat VALUES (100,"BIAP",1994)
INSERT INTO prof_mat VALUES (100,"BIAP",1994)
INSERT INTO prof_mat VALUES(300,"BOGE",1996)
INSERT INTO prof_mat VALUES (300,"BIAP",1997)
INSERT INTO prof_mat VALUES (400,"HIAR",1998)
INSERT INTO prof_mat VALUES (400,"HIUN",1999)
INSERT INTO prof_mat VALUES(400,"HIJU",1995)
C: SELECT p.nom_prof,m.nom_mat FROM prof p ,mat m ,prof_mat pm WHERE pm.plan_est>1996 AND pm.leg_prof=p.leg_prof AND pm.cod_mat= m.cod_mat
D: SELECT nom_prof FROM prof WHERE leg_prof IN(SELECTleg_prof FROM prof_mat WHERE cod_mat IN(SELECT cod_mat FROM mat WHERE aula=4 ))ORDER BY nom_prof DESC
E: SELECT m.nom_mat,m.cod_mat FROM mat m,prof_mat pm, prof p WHERE m.cod_mat = pm.cod_mat AND...