GitHub - brian-emarquez/PostgreSQL-Training: Learn PostgreSQL and how to get started quickly through practical examples.

🐘 PostgreSQL Training

Numeration	Check	Topic	Quantization	Edit Gitpot	Downloads	link
001	✔️	Downloads	✔️	💻	💾	⬅️ back
002	✔️	Usando la DB	✔️	💻	💾	⬅️ back
003	✔️	Visualizar Informacion Tablas	✔️	💻	💾	⬅️ back
004	✔️	SELECT	✔️	💻	💾	⬅️ back
005	✔️	SELECT DISTINCT	✔️	💻	💾	⬅️ back
006	✔️	SELECT COUNT	✔️	💻	💾	⬅️ back
007	✔️	WHERE	✔️	💻	💾	⬅️ back
008	✔️	ORDER BY	✔️	💻	💾	⬅️ back
009	✔️	LIMIT	✔️	💻	💾	⬅️ back
010	✔️	BETWEEN	✔️	💻	💾	⬅️ back
011	✔️	IN	✔️	💻	💾	⬅️ back
012	✔️	LIKE	✔️	💻	💾	⬅️ back
013	✔️	HAVING	✔️	💻	💾	⬅️ back
014	✔️	GROUP BY	✔️	💻	💾	⬅️ back
015	✔️	AS	✔️	💻	💾	⬅️ back

Union de Tablas

Numeration	Check	Topic	Quantization	Edit Gitpot	Downloads	link
016	✔️	AS	✔️	💻	💾	⬅️ back
017	✔️	INNER JOIN	✔️	💻	💾	⬅️ back
018	✔️	FULL JOIN	✔️	💻	💾	⬅️ back
019	✔️	LEFT JOIN	✔️	💻	💾	⬅️ back
020	✔️	RIGHT JOIN	✔️	💻	💾	⬅️ back
021	✔️	UNION	✔️	💻	💾	⬅️ back

Funciones

Numeration	Check	Topic	Quantization	Edit Gitpot	Downloads	link
022	✔️	Formato Hora Fecha	✔️	💻	💾	⬅️ back
023	✔️	Funciones Matematicas	✔️	💻	💾	⬅️ back
024	✔️	Funciones Caracteres	✔️	💻	💾	⬅️ back
025	✔️	Subconsulta Valor numerico	✔️	💻	💾	⬅️ back
026	✔️	Subconsulta por lista de Valores	✔️	💻	💾	⬅️ back
027	✔️	Subconsulta con EXITS	✔️	💻	💾	⬅️ back

Base de Datos

Numeration	Check	Topic	Quantization	Edit Gitpot	Downloads	link
028	✔️	Bases de Datos	✔️	💻	💾	⬅️ back
029	✔️	Crear Tabla SQL	✔️	💻	💾	⬅️ back
030	✔️	Insert SQL	✔️	💻	💾	⬅️ back
031	✔️	UPDATE	✔️	💻	💾	⬅️ back
032	✔️	DELETE	✔️	💻	💾	⬅️ back
033	✔️	ALTER	✔️	💻	💾	⬅️ back
034	✔️	CASE	✔️	💻	💾	⬅️ back
035	✔️	COALESCE	✔️	💻	💾	⬅️ back
036	✔️	CAST	✔️	💻	💾	⬅️ back
037	✔️	Importar Ficheros	✔️	💻	💾	⬅️ back
038	✔️	Exportar Ficheros	✔️	💻	💾	⬅️ back

Downloads

⚙️ Install PostgreSQL Linux How to install and use PostgreSQL on Ubuntu
⚙️ Install PostgreSQL Windows Download the installer for windows

Correccion del Error PATH

Cambiar la Direccion del PATH de PostgreSQL

Linux installation - Ubuntu (Intallation/Comandos)

Comandos consola Postgres

Install Postgres

Delete Postgres

Restore Database

pg_restore -h 127.0.0.1 -p 5432 -U user -d database < backup.sql
psql -h 127.0.0.1 -p 5432 -U user -W -d dabatase < backup.sql

Backup Base de Datos

pg_dump -U postgres -W -h 127.0.0.1 database > backup.sql

Create Postgres Password

sudo -u postgres psql
\password
Enter password: ...

Inicializar Postgres - Ubuntu terminal

 service postgresql start

service postgresql status

service postgresql stop

Usar Postgres

Cambie a la cuenta de postgres

sudo -i -u postgres

Acceder de inmediato a una línea de comandos de PostgresSQL

psql

ver base de datos Acceder de inmediato a una línea de comandos de PostgresSQL

\l

entrar a la base de datos Acceder de inmediato a una línea de comandos de PostgresSQL

\dt <nasededatos>

Create Postgres User

CREATE USER newuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE database to newuser;

Ver Usuarios

SELECT usename FROM pg_user;

Rename a database

ALTER DATABASE olddbname RENAME TO newdbname;

Delete user postgres

Como crear usuarios en PostgreSQL

Configuration Information

La utilidad postgresql pg_lsclusters muestra información sobre la configuración y el estado de todos los clusters, incluyendo el número de puerto.

$ pg_lsclusters

Migrating from SQLite to Postgresql - Django

Migrating data from SQlite to PostgreSQL. Shift database from SQlite to PostgreSQL in Django Migrating data from SQlite to PostgreSQL | Django

Data.json

python manage.py dumpdata > data.json

Example Setting Django

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'p2phelp_db',
        'USER': 'briandb',
        'PASSWORD': 'briandb',
        'HOST': 'localhost',
        'PORT': '5432'
    }
}

Python Django Shell

python manage.py migrate --run-syncdb

python manage.py shell

from django.contrib.contenttypes.models import ContentType
ContentType.objects.all().delete()
exit()

python manage.py loaddata data.json

Usando la DB

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
002	Usando la DB	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Visualizar Informacion Tablas

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
003	Visualizar Informacion Tablas	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

SELECT

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
004	SELECT	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

SELECT DISTINCT

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
005	SELECT DISTINCT	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

SELECT COUNT

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
006	SELECT COUNT	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

WHERE

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
007	WHERE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

ORDER BY

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
008	ORDER BY	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

LIMIT

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
009	LIMIT	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

BETWEEN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
010	BETWEEN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

IN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
011	IN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

LIKE

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
012	LIKE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

GROUP-BY

SELECT "PRODUCTO", SUM("IMPORTE")
FROM esquema."PEDIDOS"
GROUP BY "PRODUCTO"

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
013	GROUP-BY	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

HAVING

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
014	HAVING	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Union de Tablas

AS

SELECT "ID" AS "CLAVE", "PRODUCTO" AS "VALOR 0", "IMPORTE"  AS "VALOR 1" 
from esquema."PEDIDOS"

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
015	AS	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

INNER JOIN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
016	INNER JOIN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

FULL JOIN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
017	FULL JOIN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

LEFT JOIN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
018	LEFT JOIN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

RIGHT JOIN

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
019	RIGHT JOIN	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

UNION

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
020	UNION	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Funciones

Formato Hora Fecha

SELECT EXTRACT (DAY FROM "FECHA") AS "DIAA"
FROM esquema."PEDIDOS" -- EXTRAE EL DIA

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
021	Formato Hora Fecha	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Funciones Matematicas

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
022	Funciones Matematicas	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Funciones Caracteres

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
023	Funciones Caracteres	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Subconsulta Valor numerico

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
024	Subconsulta Valor numerico	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Subconsulta por lista de Valores

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
025	Subconsulta por lista de Valores	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Subconsulta con EXITS

-- Subconsulta con EXITS

SELECT "NOMBRE", "APELLIDO1", "DEP"
from esquema."PERSONAS" as p
where EXISTS
(SELECT * FROM esquema."DEPARTAMENTOS" as D
WHERE D."DEP" = P."DEP")

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
026	Subconsulta con EXITS	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Bases de Datos

PostgreSQL CHECK constraint for new tables

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
	id SERIAL PRIMARY KEY,
	first_name VARCHAR (50),
	last_name VARCHAR (50),
	birth_date DATE CHECK (birth_date > '1900-01-01'),
	joined_date DATE CHECK (joined_date > birth_date),
	salary numeric CHECK(salary > 0)
);

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
027	Base de Datos	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Crear Tabla SQL

Tabla Usuarios

Create table usuarios(
	id_usuario SERIAL PRIMARY KEY,
	nombre VARCHAR(45) not NULL,
	apellido1 VARCHAR(45) not NULL,
	apellido2 VARCHAR(45) not NULL,
	contraseña VARCHAR(45) not NULL,
	Email VARCHAR(45) unique NOT NULL,
	Fecha_cracion TIMESTAMP  unique NOT NULL
)

Tabla Ocupaciones

CREATE TABLE ocupaciones(
	id_ocupaciones serial PRIMARY KEY,
	tipo_ocupacion VARCHAR(45) not NULL, 
	Descrpcion VARCHAR(100) not NULL
)

Tabla Usuario -ocupaciones (FOREING KEY)

CREATE Table usuario_ocupaciones(
	id_usuario INTEGER REFERENCES usuarios(id_usuario), -- hace referencia
	id_ocupacion INTEGER REFERENCES ocupaciones(id_ocupacion)
)

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
028	Crear Tabla SQL	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Insert SQL

Insertar a la Tabla Usuarios

INSERT INTO usuarios(nombre, apellido1, apellido2, contraseña, email, fecha_creacion)
values('Brian', 'marquez', 'inca roca', '123', 'brian@mail.com', CURRENT_TIMESTAMP)

_Insertar a la Tabla Usuarios_

INSERT INTO usuarios(nombre, apellido1, apellido2, contraseña, email, fecha_creacion)
values('Maria', 'Isabel', 'Isabel', '456', 'maria@mail.com', CURRENT_TIMESTAMP)

Insertar a la Tabla Ocupaciones

Insert Into ocupaciones(tipo_ocupacion, descripcion)
values ('DBA', 'Reliaza Mantenimiento a la base de datos')

Insert into usuario_ocupaciones(id_usuario, id_ocupacion)
values (1,1)

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
029	Insert SQL	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

UPDATE

select * from usuarios
update usuarios set email='brianenrique@mail.com'
where id_usuario = 1

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
30	Insert SQL	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

DELETE

Insert Into ocupaciones(tipo_ocupacion, descripcion)
values ('programador', 'Reliaza Mantenimiento de lo sistemas')

Insert Into ocupaciones(tipo_ocupacion, descripcion)
values ('programador 2', 'Reliaza Mantenimiento a la base de datos 2')

/* delete */
delete from ocupaciones
where id_ocupacion = 3

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
31	DELETE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

ALTER

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
32	ALTER	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

CASE

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
33	CASE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

COALESCE

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
34	COALESCE	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

CAST

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
35	CAST	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Importar Ficheros

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
36	Importar Ficheros	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Exportar Ficheros

Numeration	File	Link	Folk	Code	Version	State	Download	Go back
37	Exportar Ficheros	✔️	✔️	yes	yes	✔️	💾	⬅️Atras

Ubuntu

Install Terminal

Install Terminal 1 - Digitalocean

Install Terminal 2 - PostgreSQL

Comandos

Crear Usuario

Para crear un usuario de PostgreSQL, utilice la siguiente instrucción SQL:

CREATE USER myuser WITH PASSWORD 'secret_passwd';

También puede crear un usuario con la siguiente instrucción SQL:

CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';

Books

Server	Name	Authors	Editorial	ISBN	Link
OneDrive	Mastering PostgreSQL 13	Paul Deitel, Harvey Deitel	Packt	N 978-1-80056-749-8	Mastering PostgreSQL 13

Paypal Donation

🩸 Hacer una donación PAYPAL 🍵

A			B
C			E
F			G

Name		Name	Last commit message	Last commit date
Latest commit History 160 Commits
002 Usando la BD		002 Usando la BD
003 Visualizar Informacion Tablas		003 Visualizar Informacion Tablas
004 SELECT		004 SELECT
005 SELECT DISTINCT		005 SELECT DISTINCT
006 SELECT COUNT		006 SELECT COUNT
007 WHERE		007 WHERE
008 ORDER BY		008 ORDER BY
009 LIMIT		009 LIMIT
010 BETWEEN		010 BETWEEN
011 IN		011 IN
012 LIKE		012 LIKE
013 GROUP BY		013 GROUP BY
014 HAVING		014 HAVING
015 AS		015 AS
016 INNER JOIN		016 INNER JOIN
017 FULL JOIN		017 FULL JOIN
018 LEFT JOIN		018 LEFT JOIN
019 RIGHT LEFT		019 RIGHT LEFT
02 Crear Tabla SQL		02 Crear Tabla SQL
020 UNION		020 UNION
021 Formato Hora Fecha		021 Formato Hora Fecha
022 Funcion matematicas		022 Funcion matematicas
023 Funciones Caracteres		023 Funciones Caracteres
024 Subconsulta Valor numerico		024 Subconsulta Valor numerico
025 Sunconsulta por lista de Valores		025 Sunconsulta por lista de Valores
026 Subconsulta con EXITS		026 Subconsulta con EXITS
028 Base de datos		028 Base de datos
029 Insertar SQL		029 Insertar SQL
030 Actualizar UPDATE		030 Actualizar UPDATE
031 Borrar DELETE		031 Borrar DELETE
032 ALTER		032 ALTER
033 CASE		033 CASE
034 COALESCE		034 COALESCE
035 CAST		035 CAST
036 Importar Ficheros		036 Importar Ficheros
037 Export Ficheros		037 Export Ficheros
images		images
.GITIGNORE		.GITIGNORE
LICENCE		LICENCE
README.md		README.md

License

brian-emarquez/PostgreSQL-Training

Folders and files

Latest commit

History

Repository files navigation

🐘 PostgreSQL Training

Table of Contents

Union de Tablas

Funciones

Base de Datos

Downloads

Linux installation - Ubuntu (Intallation/Comandos)

Usando la DB

Visualizar Informacion Tablas

SELECT

SELECT DISTINCT

SELECT COUNT

WHERE

ORDER BY

LIMIT

BETWEEN

IN

LIKE

GROUP-BY

HAVING

Union de Tablas

AS

INNER JOIN

FULL JOIN

LEFT JOIN

RIGHT JOIN

UNION

Funciones

Formato Hora Fecha

Funciones Matematicas

Funciones Caracteres

Subconsulta Valor numerico

Subconsulta por lista de Valores

Subconsulta con EXITS

Bases de Datos

Crear Tabla SQL

Insert SQL

UPDATE

DELETE

ALTER

CASE

COALESCE

CAST

Importar Ficheros

Exportar Ficheros

Ubuntu

Books

Paypal Donation

About

Topics

Resources

License

Stars

Watchers

Forks