Tablas JOIN DBeaver

--Crear tablas--

create table orden 

(

id_orden varchar(4),

id_empleado varchar(4),

id_cliente varchar(4),

id_vendedor varchar(4),

descripcion varchar(200)

);

create table vendedor 

(

id_vendedor varchar(4),

nombre varchar(50),

direccion varchar(200)

);

create table empleado 

(

id_empleado varchar(4),

nombre varchar(50),

telefono varchar(50)

);

insert into orden values ('1001','2001','3001','4001','Venta de smartphone LG');

insert into orden values ('1002','2001','3002','4001','Venta de smartphone SAMSUNG');

insert into orden values ('1003','2001','3003','4001','Venta de smartphone NOKIA');

insert into orden values ('1004','2001','3004','4001','Venta de smartphone SONY');

insert into orden values ('1005','2001','3005','4001','Venta de smartphone HUAWEI');

insert into orden values ('1006','2001','3006','4001','Venta de smartphone APPLE');

insert into vendedor values ('4001','Juan Vargas','Av. El Sol 2435');

insert into vendedor values ('4002','Carlos Perez','Av. Javier Prado 1456');

insert into vendedor values ('4003','Lucia Alvarez','Av. La Molina 1236');

insert into empleado values ('2001','Luis Fernandez','987657345');

insert into empleado values ('2002','Jose Villanueva','908765234');

insert into cliente values (3001,'Martin Garcia','Pasaje Las Crucinelas 123','908765432','San Miguel');

insert into cliente values (3002,'Maria Gonzales','Jiron Fatima 456','946754432','Los Olivos');

insert into cliente values (3003,'Manuel Buitron','Av. Las Flores 123','998065432','San Juan de Lurigancho');

--INNER JOIN (2 TABLAS)--

select o.id_orden, c.nombre

from 

orden as o

inner join 

cliente as c 

on o.id_cliente = cast(c.id_cliente as varchar(4))

--INNER JOIN (3 TABLAS)--

select o.id_orden, c.nombre as nombre_cliente, v.nombre as nombre_vendedor 

from 

orden as o 

inner join cliente as c on o.id_cliente = cast(c.id_cliente as varchar(4))

inner join vendedor as v on o.id_vendedor = v.id_vendedor

--LEFT JOIN--

select c.nombre, o.id_orden

from 

cliente as c 

left join 

orden as o 

on o.id_cliente = cast(c.id_cliente as varchar(4))

--RIGHT JOIN--

select c.nombre, o.id_orden

from

cliente as c

right join 

orden as o 

on o.id_cliente = cast(c.id_cliente as varchar(4))

select o.id_orden, e.nombre, e.telefono

from

orden as o

right join 

empleado as e 

on o.id_empleado = e.id_empleado

--FULL OUTER JOIN--

select c.nombre, o.id_orden 

from

cliente as c

full outer join

orden as o 

on o.id_cliente = cast(c.id_cliente as varchar(4))

--SELF JOIN--

select a.nombre, b.nombre, a.ciudad 

from

cliente as a, cliente as b 

where 

a.id_cliente <> b.id_cliente 

and a.ciudad = b.ciudad 

--CROSS JOIN--

select Orden.id_orden, Empleado.Nombre

from Orden 

cross join Empleado 

order by Orden.id_orden;

select orden.id_orden 

from orden -- 6 ordenes 

select empleado.nombre 

from empleado -- 3 empleados