create database ESCUELA
use ESCUELA
--Tabla 01 Carrera
create table carrera(
Clave_c int,
Nom_c varchar(50),
Durac_c float,
constraint pk_cc primary key(Clave_c)
)
select *
from carrera
------------------------------------------------
--Tabla 02 Materia
create table materia(
Clave_m int,
Nom_m varchar(50),
Cred_m float,
constraint pk_cm primary key(Clave_m)
)
select *
from materia
------------------------------------------
--Tabla 03 Profesor
create table Profesor(
Clave_p int,
Nom_p varchar(150),
Dir_p varchar(200),
Tel_p bigint,
Hor_p datetime,
constraint pk_cp primary key(Clave_p)
)
select *
from Profesor
-----------------------------------------------
/*Tabla 04 Alumno
(Con una Clave Foranea)*/
create table Alumno(
Mat_alu int,
Nom_alu varchar(150),
Edad_alu int,
Sem_alu int,
Gen_alu varchar(10),
Clave_c1 int,
constraint pk_calu primary key(Mat_alu),
constraint fk_fc1 foreign key(Clave_c1)references carrera(Clave_c)
)
select *
from Alumno
--------------------------------------------------------------------
--Tabla 05 Alumno-Profesor
create table Alu_Pro(
Mat_alu1 int,
Clave_p1 int,
constraint fk_falu1 foreign key(Mat_alu1)references Alumno(Mat_alu),
constraint fk_fp1 foreign key(Clave_p1)references Profesor(Clave_p)
)
select *
from Alu_Pro
----------------------------------------------------------------------
--Tabla 06 Materia-Alumno
create table Mat_alu(
Clave_m1 int,
Mat_alu2 int,
constraint fk_fm1 foreign key(Clave_m1)references materia(Clave_m),
constraint fk_falu2 foreign key(Mat_alu2)references Alumno(Mat_alu)
)
select *
from Mat_alu
--------------------------------------------------------------------
--Tabla 07 Materia-Profesor
create table Mat_Pro(
Clave_m2 int,
Clave_p2 int,
constraint fk_fm2 foreign key(Clave_m2)references materia(Clave_m),
constraint fk_fp2 foreign key(Clave_p2)references Profesor(Clave_p)
)
select *
from Mat_Pro
----------------------------------------------------------------------
/*Insert*/
--Tabla carrera
insert into carrera values(1,'Derecho',3)
insert into carrera values(2,'Ingenieria de Sistemas',4)
insert into carrera values(3,'Dise�o',3)
-------------------------------------------------------------------
--Tabla materia
insert into materia values(1,'Matematicas',25)
insert into materia values(2,'Espa�ol',15)
insert into materia values(3,'Dibujo',15)
insert into materia values(4,'Programaci�n',20)
------------------------------------------------------------------
--Tabla Profesor
insert into Profesor values(1,'Laura Rosas','calle azul',567865,'7:00')
insert into Profesor values(2,'Roberto Diaz','Calle Rosa',78592613,'9:00')
------------------------------------------------------------------------------
--Tabla alumno
insert into Alumno values(1,'Ana',18,6,'mujer',1)
insert into Alumno values(2,'Sergio',19,7,'Hombre',2)
insert into Alumno values(3,'Julieta',20,6,'Mujer',3)
------------------------------------------------------------------
--Alumno-Profesor
insert into Alu_Pro values(1,2)
insert into Alu_Pro values(2,1)
insert into Alu_Pro values(3,1)
----------------------------------------------------------------
--Materia-ALumno
insert into Mat_alu values(1,1)
insert into Mat_alu values(4,2)
insert into Mat_alu values(3,3)
insert into Mat_alu values(4,1)
---------------------------------------------------------------------
--Tabla Materia-Profesor
insert into Mat_Pro values(2,1)
----------------------------------------------------------------------
select Nom_alu,Edad_alu,Sem_alu,Nom_c,Nom_p,Nom_m
from Alumno inner join carrera on Alumno.Clave_c1=carrera.Clave_c
inner join Alu_Pro on Alu_Pro.Mat_alu1=Alumno.Mat_alu
inner join Profesor on Profesor.Clave_p=Alu_Pro.Clave_p1
inner join Mat_alu on Mat_alu.Mat_alu2=Alumno.Mat_alu
inner join materia on materia.Clave_m=Mat_alu.Clave_m1
where Edad_alu!=18
