Clase 1 SQL

MODELO RELACIONAL

--Tabla Sexo

CREATE TABLE dbo.sexo
(
  idsexo serial NOT NULL,
  nomsexo character(20),
  CONSTRAINT pk_sexo PRIMARY KEY (idsexo)
);

--Tabla Persona

CREATE TABLE dbo.persona
(
  codigo character(10) NOT NULL ,
  primnom character(20)NOT NULL,
  segnom character(20),
  primapel character(20) NOT NULL,
  segapel character(20) NOT NULL,
  sexo bigint NOT NULL,
  edad integer NOT NULL,

  CONSTRAINT pk_persona PRIMARY KEY (codigo),
  CONSTRAINT fk_persona FOREIGN KEY (sexo)
      REFERENCES dbo.sexo (idsexo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);


--Datos Tabla Sexo

insert into dbo.sexo(nomsexo)
values('Masculino');
insert into dbo.sexo(nomsexo)
values('Femenino');



--Datos Tabla Persona

INSERT INTO dbo.persona(codigo, primnom, segnom, primapel, segapel, sexo, edad)
VALUES ('1001','carlos','jose','caceres','ochoa',1,40);

INSERT INTO dbo.persona(codigo, primnom, segnom, primapel, segapel, sexo, edad)
VALUES ('1002','manuel','','perez','gomez',1,32);

INSERT INTO dbo.persona(codigo, primnom, segnom, primapel, segapel, sexo, edad)
VALUES ('1003','maria','jose','velez','lopez',2,20);

INSERT INTO dbo.persona(codigo, primnom, segnom, primapel, segapel, sexo, edad)
VALUES ('1004','juan','jose','rodriguez','ramos',1,50);

INSERT INTO dbo.persona(codigo, primnom, segnom, primapel, segapel, sexo, edad)
VALUES ('1005','antonio','','moreno','diaz',1,18);

INSERT INTO dbo.persona(codigo, primnom, segnom, primapel, segapel, sexo, edad)
VALUES ('1006','victoria','','saenz','morelos',2,24);

INSERT INTO dbo.persona(codigo, primnom, segnom, primapel, segapel, sexo, edad)
VALUES ('1007','carlos','julio','romero','paternina',2,24);


Script Sql

distinct

select distinct primnom from dbo.persona;

where

select * from dbo.persona where primnom='carlos';
select * from dbo.persona where primnom='carlos' and edad>30;
select * from dbo.persona where primnom='carlos' or edad>30;
select * from dbo.persona where primnom='carlos' and not edad>30;
select * from dbo.persona order by primapel desc;

contengan i
select * from dbo.persona where primnom like '%i%';

contengan ria
select * from dbo.persona where primnom like '%ria%';

comienze con a
select * from dbo.persona where primnom like 'a%';
comienzen con m
select * from dbo.persona where primnom like 'm%';

comienzen ma

select * from dbo.persona where primnom like 'ma_%';
comienzan ar
select * from dbo.persona where primnom like '_ar%';
select * from dbo.persona where segapel like '__m_%';

concatenar

select primnom||' '||primapel as nomapel from dbo.persona;

select upper(primnom||' '||primapel ) as nomapel from dbo.persona;

select lower(primnom||' '||primapel ) as nomapel from dbo.persona;


select count(codigo) from dbo.persona;

select max(edad) from dbo.persona;
select min(edad) from dbo.persona;
select avg(edad) from dbo.persona;

select max(edad) as edades from dbo.persona
union
select min(edad) from dbo.persona;

between

select * from
dbo.persona
where codigo
between '1001' and '1003';


select * from
dbo.persona
where codigo
between '1001' and '1003';

IN

SELECT * FROM dbo.persona
WHERE primnom IN ('carlos', 'manuel');

NOT IN

SELECT * FROM dbo.persona
WHERE primnom NOT IN ('carlos', 'manuel');


alias estructura


select p.codigo,p.primnom,p.primapel,p.edad,p.sexo from dbo.persona as p,dbo.sexo as s
where p.sexo=s.idsexo and p.sexo=1;