Adatbáziskezelés (TTBML1440)

Adatdefiníciós nyelv (DDL)

CREATE VIEW faj_varos AS SELECT species,township,collector,date 
FROM danubefish WHERE township ILIKE '%ű%' ORDER BY township DESC;

SELECT date, collector, township, species FROM faj_varos LIMIT 2 OFFSET 10;

DROP VIEW faj_varos;

CREATE TABLE allat (
    labak NUMERIC(2),
    nev VARCHAR(60),
    kor DATE,
    allapot VARCHAR(1),
    PRIMARY KEY (nev)
);

ALTER TABLE allatt ADD COLUMN szemek NUMERIC(2);

INSERT INTO allat VALUES (20,'Soklábú','2020-01-01','a','3');

INSERT INTO allat VALUES (200,'nagyon soklábú','2020-01-01','a','3');

INSERT INTO allat VALUES (-99,'nagyon soklábú','2020-01-01','a','3');

INSERT INTO allat VALUES (-9.3,'2 nagyon soklábú','2020-01-01','a','3');

SELECT * FROM allat;

ALTER TABLE allat ADD CONSTRAINT labak_szama CHECK(labak<5);

DELETE FROM allat WHERE labak>4;

ALTER TABLE allat ADD CONSTRAINT labak_szama CHECK(labak<5);

alter table allat drop constraint allat_pkey;

ALTER TABLE allat ADD COLUMN id serial;

ALTER TABLE allat ALTER CONSTRAINT labak_szama CHECK(labak > 0 AND labak<5);

BEGIN;
ALTER TABLE allat DROP CONSTRAINT labak_szama;
ALTER TABLE allat ADD CONSTRAINT labak_szama CHECK(labak > 0 AND labak<5);
COMMIT;

DELETE FROM allat WHERE labak < 0;

BEGIN;
ALTER TABLE allat DROP CONSTRAINT labak_szama;
ALTER TABLE allat ADD CONSTRAINT labak_szama CHECK(labak > 0 AND labak<5);
COMMIT;

\d allat

DROP TABLE allatt;

Ajánlott irodalom

https://www.postgresql.org/docs/8.0/tutorial-table.html