.psql_history

\conninf
\conninfo
CREATE TABLE UNIDAD (uid INT, direccion VARCHAR(250), PRIMARY KEY(uid);
SELECT * FROM UNIDAD
;
\q
SELECT * FROM UNIDAD
CREATE TABLE Capitanes(cid INT, cnombre VARCHAR(100), crating FLOAT, cedad INT);
\q
CREATE TABLE Capitanes(cid INT, cnombre VARCHAR(100), crating FLOAT, cedad INT);
DROP TABLE IF EXISTS Capitanes;
CREATE TABLE Unidad(uid INT, udirección VARCHAR(100), PRIMARY KEY(uid));
SELECT * FROM Unidad
;
DROP TABLE IF EXISTS Unidad;
CREATE TABLE Unidad(uid INT, udirección VARCHAR(100), PRIMARY KEY(uid));
DROP TABLE IF EXISTS Unidad;
CREATE TABLE Unidades(uid INT, udirección VARCHAR(100), PRIMARY KEY(uid));
SELECT * FROM Unidades;
\q
SELECT * FROM Unidades
;
\q
SELECT * FROM Unidades;
\q
\dt
SELECT * FROM Unidades;
INSERT INTO Unidades VALUES (1, 'avenida siempre viva 742');
SELECT * FROM Unidades;
\q
\conninfo
DROP TABLE Unidades;
\dt
CREATRE TABLE Unidades (uid INT PRIMARY KEY, direccion VARCHAR(250));
CREATE TABLE Unidades (uid INT PRIMARY KEY, direccion VARCHAR(250));
\dt
SELECT * FROM unidades;
\q
SELECT * FROM UNIDADES;
COPY Unidades from 'Entrega1/Unidades.csv' DELIMITER ',' CSV HEADER;
\COPY Unidades from 'Entrega1/Unidades.csv' DELIMITER ',' CSV HEADER;
SELECT * FROM UNIDADES
;
\COPY Unidades from 'Entrega1/Unidades.csv' DELIMITER ',' CSV HEADER;
\q
\COPY Unidades from 'Entrega1/Unidades.csv' DELIMITER ',' CSV HEADER;
SELECT * FROM UNIDADES
;
\q
\dt
SELECT * FROM UNIDADES
;
\dt
select * from comunas
;:
\dt
\q
\COPY comunas from 'Entrega1/CSV/comunas.csv' DELIMITER ',' CSV HEADER;
select * from comunas;
\COPY vehiculos from 'Entrega1/CSV/vehiculos.csv' DELIMITER ',' CSV HEADER;
select * from vehiculos;
\dt
\COPY personal from 'Entrega1/CSV/personal.csv' DELIMITER ',' CSV HEADER;
select * from despacho;
select * from personal;
\COPY personal from 'Entrega1/CSV/personal.csv' DELIMITER ',' CSV HEADER;
select * from personal
;
select * from personal
where sexo = 'hombre';
select nombre from personal;
select rut from personal;
drop table personal;
create table personal(rut varchar(15), nombre varchar(150), sexo varchar(20), edad int, tipo varchar(20), primary key(rut));
\q
\COPY personal from 'Entrega1/CSV/personal.csv' DELIMITER ',' CSV HEADER;
select * from personal;
select * from personal
where sexo = 'hombre';
update personal
set sexo = 'hombre'
where rut = '12170531-1';
select * from personal
where sexo = 'hombre';
select * from personal;
select nombre from personal
where 20 < edad < 25;
select nombre from personal
where 20 < edad;
select * from personal where tipo = 'repartidor';
select * from personal where tipo = 'administrativo';
select * from vehiculos where categoria = 'frescos';
exit
\q
\dt
select * from personal;
select * from personal where tipo = 'repartidor';
select * from c_frescos
;
select * from personal where rut like '%-k'; 
select direcciones from unidades;
select direccion from unidades;
select * from vehiculos, unidades, comunas
where vehiculos.uid = unidades.uid and comunas.uid = unidades.uid;
select * from vehiculos;
\COPY personal1 from 'Entrega1/CSV/personal.csv' DELIMITER ',' CSV HEADER;
select * from personal1;
\dt
select * from despacho
;
select * from vehiculos;
select * from c_fria;
select * from vehiculos where categoria = 'cadena fria';
select * from vehiculos where categoria = 'frescos';+
select * from vehiculois where categoria = 'carga';
select * from vehiculois where categoria = 'carga';
select * from vehiculos where categoria = 'carga';
select * from c_carga;
alter table c_carga drop column carga_maxima;
alter table c_carga add column carga_maxima float;
select * from c_carga;
select * from comunas;
select * from c_fria;
\q
\COPY c_carga from 'Entrega1/CSV/carga.csv' DELIMITER ',' CSV HEADER;
\dt
\COPY c_fria from 'Entrega1/CSV/c_fria.csv' DELIMITER ',' CSV HEADER;
\COPY c_frescos from 'Entrega1/CSV/frescos.csv' DELIMITER ',' CSV HEADER;
select * from c_frescos;
select * from c_carga;
select * from comunas;
drop table comunas
;
create table comunas (cid int primary key, nombre varchar(250));
select * from comunas;
create table secursales (uid int, cid int, primary key(uid,cid));
select * from sucursales;
drop table secursales;
create table sucursales (uid int, cid int, primary key(uid,cid));
select * from sucursales;
drop sucursales;
drop table sucursales;
create table zonas_con_delivery (uid int, cid int, primary key(uid,cid));
drop table zonas_con_delivery;
create table zonas_delivery (uid int, cid int, primary key(uid,cid));
create table maps (cid int, direccion varchar(250), primary key(cid,direccion));
select * from maps;
select * from unidades;
insert into comunas values (1, 'san joaquin');
select * from comunas;
insert into comunas values (2, 'valparaiso');
insert into comunas values (3, 'rancagua');
insert into comunas values (4, 'arica');
insert into comunas values (5, 'punta arenas');
select * from comunas;
insert into comunas values (6, 'macul');
insert into comunas values (7, 'san alfonso');
select * from unidades;
select * from maps;
insert into maps values (2,'av siempre viva 742');
insert into maps values (1,'Santiago Chile');
insert into maps values (3,'Villa Diego Portales');
\dt
insert into maps values (5,'Calle Ejército De Chile');
select * from maps;
update maps set cid = 4 where direccion = 'Calle Ejército De Chile';
select * from maps;
insert into maps values (5,'Presidente Carlos Ibáñez Del Campo 05730');
select * from maps;
select * from unidades;
select * from despacho;
\q
\dt
select * from zonas_delivey;
select * from zonas_delivery;
select * from unidades;
select * from comunas;
select * from direcciones;
select * from maps;
insert into zonas_delivery values (1,2);
insert into zonas_delivery values (1,7);
insert into zonas_delivery values (2,1);
insert into zonas_delivery values (2,6);
insert into zonas_delivery values (3,3);
insert into zonas_delivery values (4,4);
insert into zonas_delivery values (5,5);
select * from zonas_delivery;
select * from zonas_delivery,comunas where zonas_delivery.cid = comunas.cid;
select * from zonas_delivery,comunas where zonas_delivery.cid = comunas.cid as a
select * from unidades,a where unidades.uid = a.uid;
select *
from zonas_delivery as z, comunas as c, unidades as u
where z.uid = u.uid and z.cid = c.cid;
select * from despacho;
select * from unidades;
select * from despacho;
select * from maps;
insert into maps values (6, La Purga)
insert into maps values (3,San Carlos Segundo);
insert into maps values (6, La Purga);
insert into maps values (6, 'La Purga');
insert into maps values (3,'San Carlos Segundo');
insert into maps values (4,'Plataniense');
insert into maps values (5,'Avenida Peru');
insert into maps values (2,'Avenida Peru');
update maps set direccion = 'Hermanas de Sangre' where cid = 2 and direccion = 'Avenida Peru';
insert into maps values (4,'Pesadilla');
select * from maps;
select * from comunas;
\dt
select * from zonas_delivery;
select * from comunas;
select * from vehiculos;
select * from personal;
select * from a_administrativo;
exit
\q
select * from despacho;
select * from despacho;
select * from vehiculos;
select * from vehiculos;
select * from despacho;
select * from despachos where fecha > '2020-12-31';
select * from despacho where fecha > '2020-12-31';
select * from despacho where fecha > '2020-12-31' and fecha < '2022-01-01';
select * from despacho where extract(year from fecha) = '2021';
select nombre
from (select * from comunas);
from (select * form comunas) as a
select a.nombre:
;
\dt
select * from unidades;
select * from maps;
select * from despacho;
select * from despacho;
select * from vehiculos;
\dt
select * from zonas_delivery
;
select * from UNIdades;
SHOW KEYS FROM vehiculos WHERE Key_name = 'PRIMARY';
SELECT * FROM sys.objects
WHERE type = 'PK' 
AND  parent_object_id = OBJECT_ID ('vehiculos');
\ql
\q
select maps.direccion from maps where maps.cid = (select comunas.cid from comunas where nombre = 'san joaquin')
;
select * from vehiculos where unidades = 2,
;
select * from vehiculos where unidades = 2;
select * from vehiculos where unidad = 2;
select * from unidades;
select * from comunas;
select * from maps;
select patente from unidades, vehiculos, (select maps.direccion from maps where maps.cid = (select comunas.cid from comunas where nombre = 'san joaquin') as tabla1 where unidades.direccion = tabla1.direccion and unidades.uid = vehiculos.unidad;
\q
select patente from unidades, vehiculos, (select maps.direccion from maps where maps.cid = (select comunas.cid from comunas where nombre = 'san joaquin') as tabla1 where unidades.direccion = tabla1.direccion and unidades.uid = vehiculos.unidad;
;
\q
select patente from unidades, vehiculos, (select maps.direccion from maps where maps.cid = (select comunas.cid from comunas where nombre = 'san joaquin')) as tabla1 where unidades.direccion = tabla1.direccion and unidades.uid = vehiculos.unidad;
;
SELECT vehiculos, destino
     FROM Despacho
     WHERE EXTRACT (year from feca) = '2021
SELECT vehiculo, destino
     FROM Despacho
     WHERE EXTRACT (year from fecha) = '2021';
SELECT cid, vehiculo 
     from Maps, ( SELECT vehiculo, destino
                 FROM Despacho
                 WHERE EXTRACT (year from fecha) = '2021') as dp_21
     WHERE dp_21.destino = Maps.direccion;
SELECT patente, estado, tipo, categoria, unidad
        FROM Comunas, Vehiculos, (SELECT cid, vehiculo 
                               FROM Maps, ( SELECT vehiculo, destino
                                            FROM Despacho
                                            WHERE EXTRACT (year from fecha) = '2021') as dp_21
                                WHERE dp\_21.destino = Maps.direccion) as v_21
     WHERE Comunas.nombre = 'Valparaiso' AND v_21.cid = Comunas.cid AND Vehiculos.patente = v_21.vehiculo;
\q
SELECT patente, estado, tipo, categoria, unidad
        FROM Comunas, Vehiculos, (SELECT cid, vehiculo 
                               FROM Maps, ( SELECT vehiculo, destino
                                            FROM Despacho
                                            WHERE EXTRACT (year from fecha) = '2021') as dp_21
                                WHERE dp_21.destino = Maps.direccion) as v_21
     WHERE Comunas.nombre = 'Valparaiso' AND v_21.cid = Comunas.cid AND Vehiculos.patente = v_21.vehiculo;
select vehiculo, destino from despacho where extract (year from fecha) = '2021';
select cid, vehiculo from maps, (select vehiculo, destino from despacho where extract (year from fecha) = '2021') as dp_21 where dp_21.destino = maps.direccion;
select patente from comunas, vehiculos, (select cid, vehiculo from maps, (select vehiculo, destino from despacho where extract (year from fecha) = '2021') as dp_21 where dp_21.destino = maps.direccion) as v_21 where comunas.nombre = 'valparaiso' and v_21.cid = comunas.cid and vehiculos.patente = v_21.vehiculo;
select patente from comunas, vehiculos, (select cid, vehiculo from maps, (select vehiculo, destino from despacho where extract (year from fecha) = '2021') as dp_21 where dp_21.destino = maps.direccion) as v_21 where comunas.nombre = 'Valparaiso' and v_21.cid = comunas.cid and vehiculos.patente = v_21.vehiculo;
select patente from comunas, vehiculos, (select cid, vehiculo from maps, (select vehiculo, destino from despacho where extract (year from fecha) = '2021') as dp_21 where dp_21.destino = maps.direccion) as v_21 where comunas.nombre = 'valparaiso' and v_21.cid = comunas.cid and vehiculos.patente = v_21.vehiculo;
select * from despacho;
select * from maps;
select * from comunas;
select vehiculos from comunas, vehiculos, (select cid, vehiculo from maps, (select vehiculo, destino from despacho where extract (year from fecha) = '2021') as dp_21 where dp_21.destino = maps.direccion) as v_21 where comunas.nombre = 'valparaiso' and v_21.cid = comunas.cid and vehiculos.patente = v_21.vehiculo;
\q
\dt
select * from comunas
;
\c grupo62e2
select * from unidades;
q
;
select direccion_id from unidades;
\q
\c grupo62e2
\dt info
\q
\l
\e

\h
\q
pg_ctl reload
--password
grupo62
:
;
select pg_reload_conf();
\q
\c grupo62e2
\dt
select * from direcciones;
select direcciones.nombre from unidades,direcciones where unidades.direccion_id==direcciones.id
;
select direcciones.nombre from unidades,direcciones where unidades.direccion_id=direcciones.id
;
select * from direcciones;
select * from direcciones, unidades where unidades.direccion_id = direcciones.id and ;
select * from direcciones, unidades where unidades.direccion_id = direcciones.id and direcciones.comuna = santiago ;
select * from direcciones, unidades where unidades.direccion_id = direcciones.id and direcciones.comuna = 'santiago' ;
select uid from direcciones, unidades where unidades.direccion_id = direcciones.id and direcciones.comuna = 'santiago' ;
select * from vehiculos,(select uid from direcciones, unidades where unidades.direccion_id = direcciones.id and direcciones.comuna = 'santiago') as unidades
where vehiculos.unidad = unidades.uid;
select vehiculos from vehiculos,(select uid from direcciones, unidades where unidades.direccion_id = direcciones.id and direcciones.comuna = 'santiago') as unidades
where vehiculos.unidad = unidades.uid;
\q
\c grupo62e2
select * from direciones where comuna like "%santiago%";
\dt
select * from direcciones where comuna like "%santiago%";
select * from direcciones where comuna like %santiago%;
select * from direcciones where comuna like 'santiago%';
select * from despacho where fecha like '2019%';
select * from despacho where date(fecha) like '2019%';
select * from despacho where fecha like '2019-%';
select * from despacho where despacho.fecha LIKE '2019-%';
select * from depacho
;
select fecha from despacho;
select fecha from despacho where fecha like '2019-%';
select fecha from despacho;
select fecha from despacho where fecha < 2019-31-04;
select fecha from despacho where string(fecha) like '2019-%';
select fecha from despacho where string(timestamp[fecha]) like '2019-%';
select fecha from despacho where string(timestamp fecha) like '2019-%';
select fecha from despacho where date(fecha) like '2019-%';
select fecha from despacho cast(fecha as date) like '2019-%';
select fecha from despacho where cast(fecha as date) like '2019-%';
select fecha from despacho where cast(fecha as text) like '2019-%';
select fecha from despacho where cast(fecha as text) like '2020-%';
select fecha from despacho where cast(fecha as text) like '2021-%';
\c grupo62e2
\dt
select jefe from unidades, (select uid from zonas where cobertura = 'arica' intersect select uid from zonas where cobertura = 'valparaiso') as u where unidades.uid = u.uid;
select * from personal, (select jefe from unidades, (select uid from zonas where cobertura = 'arica' intersect select uid from zonas where cobertura = 'valparaiso') as u where unidades.uid = u.uid) as jefes where personal.id = jefes.jefe;
\q
\c grupo62e2
select * from vehiculos;
select count(unidad)  from vehiculos where tipo = 'auto' group by unidad;
select * from vehiculos;
select unidad, count(unidad)  from vehiculos where tipo = 'auto' group by unidad;
select unidad, count(unidad), tipo  from vehiculos where tipo = 'auto' group by unidad;
select unidad, max(count(unidad))  from vehiculos where tipo = 'auto' group by unidad;
select * from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.uid;
select * from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad;
select uid, max(cantidad) from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad;
select unidades.uid, max(cantidad) from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad;
select jefe, max(cantidad) from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad;
select max(cantidad) from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad;
select unidades, cantidad from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad;
select unidades.uid, cantidad from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad;
select uid, max(cantidad) from (select unidades.uid, cantidad from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad);
select uid, max(cantidad) from (select unidades.uid, cantidad from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad) as alias;
select uid as unidad, max(cantidad) from (select unidades.uid, cantidad from unidades,(select unidad, count(unidad) as cantidad  from vehiculos where tipo = 'auto' group by unidad) as cantidades where unidades.uid = cantidades.unidad) as alias;
select count(cantidad), unidad from vehiculos where tipo = 'auto' group by unidad;
select count(tipo), unidad from vehiculos where tipo = 'auto' group by unidad;
select * fom unidades, (select count(tipo), unidad from vehiculos where tipo = 'auto' group by unidad) as cantidad where cantidad.unidad = unidades.uid;
select * from unidades, (select count(tipo), unidad from vehiculos where tipo = 'auto' group by unidad) as cantidad where cantidad.unidad = unidades.uid;
select unidades.uid, max(cantidad.count) from unidades, (select count(tipo), unidad from vehiculos where tipo = 'auto' group by unidad) as cantidad where cantidad.unidad = unidades.uid;
select max(cantidad.count) from unidades, (select count(tipo), unidad from vehiculos where tipo = 'auto' group by unidad) as cantidad where cantidad.unidad = unidades.uid;
select uid from unidades, (select count(tipo), unidad from vehiculos where tipo = 'auto' group by unidad) as cantidad where cantidad.unidad = unidades.uid AND cantidad.count = ;
select * from (select * from unidades, (select count(tipo), unidad from vehiculos where tipo = 'auto' group by unidad) as cantidad where cantidad.unidad = unidades.uid) as und, (select max(cantidad.count) as max_c from unidades, (select count(tipo), unidad from vehiculos where tipo = 'auto' group by unidad) as cantidad where cantidad.unidad = unidades.uid) as max where und.count = max.max_c; 
\q
\c grupo62e2
select distinc(comunas) from direcciones;
select distinc(comuna) from direcciones;
select distinct(comuna) from direcciones;
\q
\c grupo62e2
\dt
select * vehiculos, despacho where despacho.vehiculo = vehiculo.id and vehiculo.tipo = 'auto;
';
select * vehiculos, despacho where despacho.vehiculo = vehiculo.id and vehiculo.tipo = 'auto';
select * from vehiculos, despacho where despacho.vehiculo = vehiculo.id and vehiculo.tipo = 'auto';
select * from vehiculos, despacho where despacho.vehiculo = vehiculos.id and vehiculos.tipo = 'auto';
select * from vehiculos, despacho, personal where despacho.vehiculo = vehiculos.id and vehiculos.tipo = 'auto' and despacho.repartidor = personal.id and personal.edad between 20 and 30;
\q
\c grupo62e2
\dt
select * from unidades;
\q
\c grupo62e2
select * from despacho;
select fecha from despacho;
select extract(year form fecha) from despacho;
select extract(year from fecha) from despacho;
select distinct(extract(year from fecha)) from despacho;
select distinct(edad) from personal;
select distinct(edad) from personal order by edad;
\q
\c grupo62e2
exit
psql
\q
\c grupo62e2
\q
\c grupo62e3
select * from personal;
exit
\q
ALTER TABLE personal ADD COLUMN carrito varchar(255);
SELECT * FROM personal;
;
SELECT * FROM personal;