Base SQL Empresa

create dataBase Holding

use Holding

create table Empresa (

id_emp smallint,

nom_emp varchar(200),

fecha_ent varchar(25),

fact_anual varchar(25),

num_vend int,

constraint pk_ie primary key (id_emp))

create table Sede (

id_sede smallint,

ciudad varchar(25),

constraint pk_sed primary key (id_sede))

create table Delegacion (

id_dele smallint,

nom_dele varchar(25),

constraint pk_id primary key (id_dele))

create table Calle (

id_calle smallint,

nom_calle varchar(25),

constraint pk_ic primary key (id_calle))

create table Colonia (

id_colo smallint,

nom_colo varchar(25),

constraint pk_icc primary key (id_colo))

create table Titulacion (

num_tit smallint,

tipo_tit varchar(25),

constraint pk_tit primary key (num_tit))

create table Area (

id_area smallint,

nom_area varchar(25),

descrip varchar(300),

constraint pk_ia primary key (id_area))

create table Pais (

id_pais smallint,

nom_pais varchar(100),

num_hab int,

pib float,

capital varchar(200),

constraint pk_ip primary key (id_pais))

create table vendedor(

cod_vend smallint,

nom_vend varchar(20),

num_vend int,

id_emp2 smallint,

id_calle1 smallint,

cod_vend1 smallint,

id_colo1 smallint,

id_dele1 smallint,

constraint pk_cove primary key(cod_vend),

constraint fk_idem foreign key(id_emp2) references empresa (id_emp),

constraint fk_icall foreign key(id_calle1) references calle (id_calle),

constraint fk_idel foreign key(id_dele1) references delegacion (id_dele),

constraint fk_icol foreign key(id_colo1)references colonia (id_colo),

constraint fk_iven foreign key(cod_vend1) references vendedor (cod_vend))

create table Asesor(

id_asesor smallint,

nom_asesor varchar(200),

id_calle2 smallint,

id_colo2 smallint,

id_dele2 smallint,

num_tit1 smallint,

constraint pk_ase primary key(id_asesor),

constraint fk_dell foreign key(id_dele2) references delegacion (id_dele),

constraint fk_call foreign key(id_calle2) references calle (id_calle),

constraint fk_colni foreign key(id_colo2) references colonia (id_colo),

constraint fk_tit foreign key (num_tit1) references Titulacion(num_tit))

create table Paem (

id_pais1 smallint,

id_emp1 smallint,

id_sede1 smallint,

constraint fk_empe foreign key(id_emp1) references empresa (id_emp),

constraint fk_sedi foreign key(id_sede1) references sede (id_sede),

constraint fk_pais foreign key(id_pais1)references pais (id_pais))

create table asempre(

Id_asesor1 smallint,

num_area1 smallint,

id_emp3 smallint,

Fecha_in varchar(100),

constraint fk_ase foreign key(Id_asesor1) references asesor (Id_asesor),

constraint fk_ari foreign key(num_area1) references area (id_area),

constraint fk_asa foreign key(id_emp3)references empresa (id_emp))

/*REGISTROS*/

insert into empresa values(1,'Underground City',10/05/05,400000,9121)

insert into empresa values(2,'Better living industries',31/12/04,540000,9122)

insert into empresa values(3,'Life in colors',10/11/09,980000,9123)

insert into calle values(10,'Leonor')

insert into calle values(20,'Wilfrido M.')

insert into calle values(30,'Sta. Rosalia')

insert into colonia values(10,'Los reyes')

insert into colonia values(20,'Amp.Daniel Garza')

insert into colonia values(30,'Jose Ceballos')

insert into delegacion values(10,'Miguel Hidalgo')

insert into delegacion values(20,'Venustiano Carranza')

insert into delegacion values(30,'Gustavo. A. Madero')

insert into Area values(1,'Zona Norte','excelente empresa')

insert into Area values(2,'Area Metropolitana','Se rigen por la calidad')

insert into Area values(3,'Zona Poniente','Una gran responsabilidad a la hora de trabajar')

insert into Titulacion values(32,'proyecto')

insert into Titulacion values(12,'promedio')

insert into Titulacion values(45,'tesis')

insert into Asesor values(112,'JOSE HERNANDEZ',10,10,10,32)

insert into Asesor values(113,'LINO SAUCEDO',20,20,20,12)

insert into Asesor values(114,'KEVIN ESCALONA',30,30,30,45)

insert into pais values(100,'Argentina',42192494 ,14714018,'Buenos Aires')

insert into pais values(200,'Austria', 8576261, 1081190 ,'Viena')

insert into pais values(300,'Canada',35492000,51958038, 'Ottawa')

insert into Sede values(221,'La paz')

insert into Sede values(222,'NewStoe')

insert into Sede values(223,'toronto')

insert into Paem values(100,1,221)

insert into Paem values(200,2,222)

insert into Paem values(300,3,223)

insert into asempre values(112,1,1,10/05/05)

insert into asempre values(113,2,2,31/12/04)

insert into asempre values(114,3,3,10/11/09)

insert into vendedor values(12,'Luis',15,1,10,12,10,20)

insert into vendedor values(13,'Ernesto',16,2,20,13,30,30)

insert into vendedor values(14,'Fernando',17,3,30,14,20,10)

/*CONSULTAS*/

SELECT *

FROM Area

SELECT *

FROM Asesor

SELECT *

FROM Empresa

SELECT *

FROM vendedor

SELECT *

FROM delegacion

SELECT *

FROM colonia

SELECT *

FROM calle

SELECT *

FROM titulacion

SELECT *

FROM sede

SELECT *

FROM pais

SELECT *

FROM paem

SELECT *

FROM asempre

/*EMPRESA,SEDE,PAIS*/

...