Clase 2 SQL

Modelo Relacional

--Tabla  departamento

CREATE TABLE dbo.departamento
(
  coddep character(10) NOT NULL,
  nomdep character(50) NOT NULL,
  CONSTRAINT pk_departamento PRIMARY KEY (coddep)
);

-- Tabla Cargo

CREATE TABLE dbo.cargo
(
  codcargo character(10) NOT NULL,
  nomcargo character(100) NOT NULL,
  salario integer,
  coddep character(10),
  CONSTRAINT pk_cargo PRIMARY KEY (codcargo),
  CONSTRAINT fk_cargo FOREIGN KEY (coddep)
      REFERENCES dbo.departamento (coddep) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
);

--Tabla Empleado

CREATE TABLE dbo.empleado
(
  codemp character(10) NOT NULL,
  primnom character(20),
  segnom character(20),
  primapel character(20),
  segapel character(20),
  codcargo character(10),
  edad integer,
  CONSTRAINT pk_empleado PRIMARY KEY (codemp),
  CONSTRAINT fk_empleado FOREIGN KEY (codcargo)
      REFERENCES dbo.cargo (codcargo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);


Practica Script Clase

practica clase

Seleccion de la tabla departamento y cargo

select * from dbo.departamento as d,dbo.cargo as c
where d.coddep=c.coddep;

Cuenta el total de cargos

select count(d.coddep) as total
 from dbo.departamento as d,dbo.cargo as c
where d.coddep=c.coddep;

Having y Group By
select d.coddep,nomdep,count(d.coddep) as total
 from dbo.departamento as d,dbo.cargo as c
where d.coddep=c.coddep
group by d.coddep,nomdep
having count(d.coddep)>2;

Having y Group By

select d.coddep,nomdep,min(salario) as promedio
 from dbo.departamento as d,dbo.cargo as c
where d.coddep=c.coddep
group by d.coddep,nomdep

Hav‌ing y Group BY

select c.codcargo,nomcargo,count(codemp) as total
 from dbo.cargo as c,dbo.empleado as e
where c.codcargo=e.codcargo
group by c.codcargo,nomcargo;

vista

create view dbo.vistaest as
select c.codcargo,nomcargo,count(codemp) as total
 from dbo.cargo as c,dbo.empleado as e
where c.codcargo=e.codcargo
group by c.codcargo,nomcargo;

select nomcargo,total from dbo.vistaest;

Subconsulta

SELECT * from dbo.empleado
WHERE edad >(SELECT avg(edad)
        FROM dbo.empleado);

Any

select * from dbo.cargo
where codcargo=any (select codcargo
 from dbo.empleado where edad>30);

All

select * from dbo.cargo
where codcargo=all (select codcargo
 from dbo.empleado where edad>30);


Tabla Copia Sexo

CREATE TABLE dbo.sexocop
(
  idsexo serial NOT NULL,
  nomsexo character(20),
  CONSTRAINT pk_sexoc PRIMARY KEY (idsexo)
)

insertar filas a traves de select
insert into dbo.sexo2(idsexo,nomsexo)
select idsexo,nomsexo from dbo.sexo;