Using PostgreSQL in command line

Some simple examples SQL commands ane PSQL commands.

[valgamesnow]

CREATE DATABASE skola;

CREATE TABLE ludia(
id serial NOT NULL,
meno character varying NOT NULL,
priezvisko character varying NOT NULL,
tel_c character varying,
ida integer NOT NULL,
CONSTRAINT "pk-ludia" PRIMARY KEY (id));

select * from ludia;

CREATE TABLE adresa (
id serial NOT NULL,
id_psc integer NOT NULL,
id_ulica integer NOT NULL,
cislo integer NOT NULL,
CONSTRAINT "pk-adresa" PRIMARY KEY (id));

CREATE TABLE ulica (
id serial NOT NULL,
nazov character varying NOT NULL,
id_psc integer NOT NULL,
CONSTRAINT "pk-ulica" PRIMARY KEY (id));

CREATE TABLE psc (
id serial NOT NULL,
cislo integer NOT NULL,
idm integer NOT NULL,
CONSTRAINT "pk-psc" PRIMARY KEY (id));

CREATE TABLE mesto (
id serial NOT NULL,
nazov varchar NOT NULL,
CONSTRAINT "pk-mesto" PRIMARY KEY (id));


ALTER TABLE ludia ADD FOREIGN KEY (ida) REFERENCES adresa(id);

ALTER TABLE adresa ADD FOREIGN KEY (id_psc) REFERENCES psc(id);

ALTER TABLE adresa ADD FOREIGN KEY (id_ulica) REFERENCES psc(id);

ALTER TABLE ulica ADD FOREIGN KEY (id_psc) REFERENCES psc(id);

ALTER TABLE psc ADD FOREIGN KEY (idm) REFERENCES psc(id);

INSERT INTO mesto (nazov) VALUES ('Banska Bystrica');

select * from mesto;

INSERT INTO psc (cislo, idm) VALUES (97401, 1);

INSERT INTO psc (cislo, idm) VALUES (97409, 1);

INSERT INTO ulica (nazov, id_psc) values ('Limbova', 2);

select * from ulica;

INSERT INTO adresa (id_psc, id_ulica, cislo) values (2, 1, 23);

select * from adresa;

INSERT INTO ludia (meno, priezvisko, ida) values ('Janko', 'Hrasko', 1);

select * from ludia;

select meno, priezvisko from ludia;

INSERT INTO ulica (nazov, id_psc) values ('Povstalecka', 2);

INSERT INTO adresa (id_psc, id_ulica, cislo) values (2, 2, 6);

INSERT INTO ludia (meno, priezvisko, tel_c, ida) values ('Ruzenka', 'Sipkova', '0915123456', 2);

select * from ludia;

select priezvisko, meno from ludia order by priezvisko desc;

select ludia.meno, ludia.priezvisko, ulica.nazov as ulica, adresa.cislo, mesto.nazov as mesto, psc.cislo as psc from ludia, adresa, ulica, psc, mesto;

select ludia.meno, ludia.priezvisko, ulica.nazov as ulica, adresa.cislo, mesto.nazov as mesto, psc.cislo as psc from ludia, adresa, ulica, psc, mesto where ludia.ida = adresa.id and adresa.id = psc.id and adresa.id_ulica = ulica.id and ulica.id_psc = psc.id and psc.idm = mesto.id;