PostgresSQL

The below is a quick illustration of postgresql.

Start the service

On Linux, you do it as follows

sudo service postgresql start

See if you can log in with psql

psql -U postgres

Create a database

Call the following file create.sql

/*
Then execute,
psql -U postgres postgres -f create.sql
*/

CREATE SCHEMA IF NOT EXISTS relationships;

CREATE TABLE IF NOT EXISTS relationships.fruits(
    kind character(10) NOT NULL,
    evaluation integer NOT NULL,
    color character(10),
    CONSTRAINT fruits_pkey PRIMARY KEY (kind)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE relationships.fruits
    OWNER to postgres;

CREATE TABLE IF NOT EXISTS relationships."number"(
    fruit character(10) NOT NULL,
    id serial NOT NULL,
    "number" integer,
    CONSTRAINT number_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE relationships."number"
    OWNER to postgres;

For relationships.fruits

postgres=# SELECT * FROM relationships.fruits;
    kind    | evaluation |   color
------------+------------+------------
 oranges    |          2 | orange
 bananas    |          2 | yellow
 apples     |          1 | red
 bberries   |          1 | blue
 mangoes    |          5 | green
(5 rows)

Inserting values

Call the following file insert.sql

/*
To populate the database

Execute:
psql -U postgres postgres -f insert.sql
*/

INSERT INTO relationships.fruits (kind,evaluation,color) VALUES ('apples',1,'red');
INSERT INTO relationships.fruits (kind,evaluation,color) VALUES ('oranges',2,'orange');
INSERT INTO relationships.fruits (kind,evaluation,color) VALUES ('bananas',2,'yellow');
INSERT INTO relationships.fruits (kind,evaluation,color) VALUES ('bberries',1,'blue');
INSERT INTO relationships.fruits (kind,evaluation,color) VALUES ('mangoes',5,'green');

INSERT INTO relationships."number"(fruit,"number") VALUES ('apples',2);
INSERT INTO relationships."number"(fruit,"number") VALUES ('bananas',1);
INSERT INTO relationships."number"(fruit,"number") VALUES ('mangoes',9);

Joining data

SELECT * FROM relationships.number 
INNER JOIN relationships.fruits 
ON relationships.fruits.kind=relationships.number.fruit;

The results after joining the data are shown

postgres=# SELECT * FROM relationships.number 
INNER JOIN relationships.fruits 
ON relationships.fruits.kind=relationships.number.fruit;
   fruit    | id | number |    kind    | evaluation |   color
------------+----+--------+------------+------------+------------
 bananas    |  1 |      1 | bananas    |          2 | yellow
 mangoes    |  2 |      9 | mangoes    |          5 | green
 apples     |  3 |      2 | apples     |          1 | red
 bananas    |  4 |      1 | bananas    |          2 | yellow
 mangoes    |  5 |      9 | mangoes    |          5 | green
(5 rows)