
-- START A POSTGRES SERVER


-- OPEN PGADMIN4


-- CREATE A TABLE IN THE DATABASE, ADD A RECORD AND QUERY THE DATABASE

CREATE TABLE main.xkota (
	xkotaId VARCHAR UNIQUE PRIMARY KEY NOT NULL,
	xkotaName VARCHAR NOT NULL
);

INSERT INTO main.xkota (xkotaId,xkotaName) 
VALUES ('xk1000','fictional name'
);

SELECT * FROM main.xkota;


-- CREATE ANOTHER TABLE

CREATE TABLE main.build (
  buildId VARCHAR UNIQUE PRIMARY KEY NOT NULL,
  buildTitle VARCHAR NOT NULL,
  buildDate DATE,
  buildNote VARCHAR
);

INSERT INTO main.build (buildId,buildTitle,buildDate,buildNote) 
VALUES ('build1000','fictional content','1900-01-01','fictional note'
);

SELECT * FROM main.build;


-- A TABLE TO JOIN xkota rows to build rows

CREATE TABLE main.quad (
  xkotaId VARCHAR,
  buildId VARCHAR,
  FOREIGN KEY (xkotaId) REFERENCES main.xkota (xkotaId),
  FOREIGN KEY (buildId) REFERENCES main.build (buildId)
);

INSERT INTO main.quad (xkotaId,buildId) 
VALUES ('xk1000','build1000');

SELECT * FROM main.quad;

-- END

-- QUERY THE DATABASE JOINING ALL 3 TABLES

SELECT * FROM main.xkota
FULL JOIN main.quad ON xkota.xkotaId = quad.xkotaId
FULL JOIN main.build ON quad.buildId = build.buildId;



