-- Μια βάση δεδομένων είναι ένας τρόπος οργάνωσης δεδομένων έτσι ώστε να μπορείτε να εκτελέσετε τέσσερις λειτουργίες σε αυτήν
-- -δημιουργία
-- -ανάγνωση
-- -ενημέρωση
-- -διαγραφή
-- Ένα σύστημα διαχείρισης βάσεων δεδομένων (DBMS) είναι ένας τρόπος αλληλεπίδρασης με μια βάση δεδομένων χρησιμοποιώντας 
-- μια γραφική διεπαφή ή μια γλώσσα κειμένου.
-- Παραδείγματα DBMS: MySQL, Oracle, PostgreSQL, SQLite, Microsoft Access, MongoDB κ.λπ.
-- Η επιλογή ενός DBMS βασίζεται σε παράγοντες όπως
-- -Κόστος: ιδιόκτητο έναντι δωρεάν λογισμικού,
-- -Μέγεθος υποστήριξης: δωρεάν και ανοιχτού κώδικα λογισμικό όπως MySQL, PostgreSQL και SQLite συνοδεύεται από το μειονέκτημα ότι πρέπει να ρυθμίσετε μόνοι σας τη βάση δεδομένων,
-- -Βάρος: τα πιο πλήρως εξοπλισμένα συστήματα όπως η MySQL ή η PostgreSQL είναι βαρύτερα και απαιτούν περισσότερους υπολογισμούς για να λειτουργήσουν από συστήματα όπως η SQLite.

-- Σε αυτό το μάθημα, θα εργαστούμε με την PostgreSQL.

-- Η SQL σημαίνει Structured Query Language. Είναι μια γλώσσα που χρησιμοποιείται για την αλληλεπίδραση με βάσεις δεδομένων, 
-- μέσω της οποίας μπορείτε να δημιουργήσετε, να διαβάσετε, να ενημερώσετε και να διαγράψετε δεδομένα σε μια βάση δεδομένων. 
-- Μερικές σημαντικές επισημάνσεις σχετικά με την SQL
-- -είναι δομημένη, όπως θα δούμε σε αυτό το μάθημα,
-- -έχει κάποιες λέξεις-κλειδιά που μπορούν να χρησιμοποιηθούν για αλληλεπίδραση με τη βάση δεδομένων και
-- -είναι μια γλώσσα ερωτημάτων — μπορεί να χρησιμοποιηθεί για να θέσει ερωτήσεις σε δεδομένα μέσα σε μια βάση δεδομένων.

-- Γνωριμία με το PGAdmin GUI...

-- Ποια δεδομένα υπάρχουν στην πραγματικότητα στη βάση δεδομένων μας; 
-- Για να απαντήσουμε σε αυτό, θα χρησιμοποιήσουμε την πρώτη λέξη-κλειδί SQL, SELECT, 
-- η οποία μας επιτρέπει να επιλέξουμε ορισμένες (ή όλες) τις γραμμές από έναν πίνακα μέσα στη βάση δεδομένων.

SELECT * 
FROM "books";

-- Το αποτέλεσμα που λαμβάνουμε περιέχει όλες τις στήλες όλων των γραμμών σε αυτόν τον πίνακα, που είναι πολλά δεδομένα. 
-- Μπορούμε να το απλοποιήσουμε επιλέγοντας μια συγκεκριμένη στήλη, ας πούμε τον τίτλο, από τον πίνακα.

SELECT "title" 
FROM "books";

-- Τώρα, βλέπουμε μια λίστα με τους τίτλους σε αυτόν τον πίνακα. 
-- Τι γίνεται όμως αν θέλουμε να δούμε τίτλους και συγγραφείς στα αποτελέσματα αναζήτησης;

SELECT "title", "authors" 
FROM "books";

-- Η PostgreSQL κάνει διάκριση πεζών-κεφαλαίων σε ονόματα πινάκων, πεδίων, αλφαρηθμητικών, κ.λπ. 
-- Στις λέξεις της SQL δεν κάνει διάκριση πεζών-κεφαλαίων. Συνήθως όμως οι SQL λέξεις-κλειδιά γράφονται με κεφαλαία γράμματα. Αυτό είναι ιδιαίτερα χρήσιμο για τη βελτίωση της αναγνωσιμότητας μεγαλύτερων ερωτημάτων.

-- Αν μια βάση δεδομένων είχε εκατομμύρια γραμμές, ίσως να μην είχε νόημα να επιλέξουμε όλες τις γραμμές της. 
-- Αντίθετα, ίσως να θέλαμε απλώς να ρίξουμε μια ματιά στα δεδομένα που περιέχει. 
-- Χρησιμοποιούμε τη λέξη-κλειδί SQL LIMIT για να καθορίσουμε τον αριθμό των γραμμών στην έξοδο του ερωτήματος.
-- Οι τίτλοι ταξινομούνται με τον ίδιο τρόπο στην έξοδο αυτού του ερωτήματος όπως και στη βάση δεδομένων

SELECT "title" 
FROM "books" 
LIMIT 10;

-- Η λέξη-κλειδί WHERE χρησιμοποιείται για την επιλογή γραμμών με βάση μια συνθήκη. 
-- Θα εμφανίσει τις γραμμές για τις οποίες η καθορισμένη συνθήκη είναι αληθής. 
-- Το 2008 δεν είναι σε εισαγωγικά επειδή είναι ακέραιος αριθμός, όχι συμβολοσειρά ή αναγνωριστικό

SELECT "title", "authors" 
FROM "books" 
WHERE "publication_year" = 2008; 

-- Οι τελεστές που μπορούν να χρησιμοποιηθούν για τον καθορισμό συνθηκών στην SQL είναι 
-- = (“ίσο με”), != (“όχι ίσο με”) και <> (επίσης “όχι ίσο με”).

-- Για να επιλέξουμε τα βιβλία που δεν είναι γραμμένα σε en-US, μπορούμε να εκτελέσουμε το ερώτημα
-- Το en-US βρίσκεται σε μονά εισαγωγικά επειδή είναι μια συμβολοσειρά SQL και όχι ένα αναγνωριστικό.

SELECT "title", "language_code" 
FROM "books" 
WHERE "language_code" <> 'en-US';

SELECT "title", "language_code" 
FROM "books" 
WHERE NOT "language_code" = 'en-US';

-- Για να συνδυάσουμε συνθήκες, μπορούμε να χρησιμοποιήσουμε τις λέξεις-κλειδιά SQL AND και OR. 
-- Μπορούμε επίσης να χρησιμοποιήσουμε παρενθέσεις για να υποδείξουμε πώς να συνδυάσουμε τις συνθήκες 
-- σε μια σύνθετη πρόταση υπό όρους.

SELECT "title", "authors" 
FROM "books" 
WHERE "publication_year" = 2008 OR "publication_year" = 2013;

-- Για να επιλέξετε τα βιβλία που εκδόθηκαν το 2008 ή το 2013 και δεν γράφτηκαν σε en-US
-- Εδώ, οι παρενθέσεις υποδεικνύουν ότι το OR θα πρέπει να αξιολογηθεί πριν από το AND.

SELECT "title", "authors" 
FROM "books" 
WHERE ("publication_year" = 2008 OR "publication_year" = 2013) AND "language_code" != 'en-US'; 

-- Είναι πιθανό οι πίνακες να έχουν δεδομένα που λείπουν. 
-- Ο τύπος NULL χρησιμοποιείται για να υποδείξει ότι ορισμένα δεδομένα δεν έχουν τιμή ή δεν υπάρχουν στον πίνακα.

SELECT "title", "language_code" 
FROM "books" 
WHERE "language_code" IS NULL;

SELECT "title", "language_code" 
FROM "books" 
WHERE "language_code" != 'en-US'; 
SELECT "title", "language_code" 
FROM "books" 
WHERE "language_code" IS NOT NULL;

-- Η λέξη-κλειδί LIKE χρησιμοποιείται για την επιλογή δεδομένων που αντιστοιχούν περίπου στην καθορισμένη συμβολοσειρά. 
-- Για παράδειγμα, το LIKE θα μπορούσε να χρησιμοποιηθεί για την επιλογή βιβλίων 
-- που έχουν μια συγκεκριμένη λέξη ή φράση στον τίτλο τους.
-- Το LIKE συνδυάζεται με τους τελεστές % (αντιστοιχεί σε οποιονδήποτε χαρακτήρα γύρω από μια δεδομένη συμβολοσειρά) 
-- και _ (αντιστοιχεί σε έναν μόνο χαρακτήρα).
-- Για να επιλέξουμε τα βιβλία που έχουν τη λέξη «love» στους τίτλους τους, μπορούμε να εκτελέσουμε

SELECT "title" 
FROM "books" 
WHERE "title" LIKE '%love%';

-- Για να επιλέξετε τα βιβλία των οποίων ο τίτλος αρχίζει με “The”

SELECT "title" 
FROM "books" 
WHERE "title" LIKE 'The%';

-- Το παραπάνω ερώτημα μπορεί επίσης να επιστρέψει βιβλία των οποίων οι τίτλοι αρχίζουν με “Their” ή “They”. 
-- Για να επιλέξετε μόνο τα βιβλία των οποίων οι τίτλοι αρχίζουν με τη λέξη “The”, μπορούμε να προσθέσουμε ένα κενό.

SELECT "title" 
FROM "books" 
WHERE "title" LIKE 'The %';

-- Δεδομένου ότι υπάρχει ένα βιβλίο στον πίνακα του οποίου το όνομα περιέχει είτε “Show” ή “Snow”, μπορούμε να εκτελέσουμε

SELECT "title" 
FROM "books" 
WHERE "title" LIKE '%S_ow';

-- Μπορούμε να χρησιμοποιήσουμε πολλαπλά % ή _ σε ένα ερώτημα

SELECT "title" 
FROM "books" 
WHERE "title" LIKE 'The%Sleep%';

SELECT "title"
FROM "books" 
WHERE "title" LIKE 'the%Sleep%'; -- διάκριση πεζών-κεφαλαίων

-- Αν γνωρίζαμε ότι υπήρχε ένα βιβλίο στον πίνακα του οποίου ο τίτλος αρχίζει με «Τ» και έχει τέσσερα γράμματα, 
-- μπορούμε να προσπαθήσουμε να το βρούμε εκτελώντας

SELECT "title" 
FROM "books" 
WHERE "title" LIKE 'T____';

-- Αν θέλουμε να εντοπίσουμε τίτλους που να περιέχουν κάποιον από τους 
-- ειδικούς χαρακτήρες % ή _ χρησιμοποιούμε το escape χαρακτήρα \

SELECT *
FROM "books"
WHERE "title" LIKE '%\%%';

-- Μπορούμε επίσης να χρησιμοποιήσουμε τους τελεστές <, >, <= και >= στις συνθήκες μας για να αντιστοιχίσουμε ένα εύρος τιμών.

SELECT "title", "publication_year" 
FROM "books" 
WHERE "publication_year" >= 2008 AND "publication_year" <= 2013;

-- Ένας άλλος τρόπος για να έχετε τα ίδια αποτελέσματα είναι να χρησιμοποιήσετε τις λέξεις-κλειδιά BETWEEN και AND 
-- για να καθορίσετε εύρη που περιλαμβάνουν όλα τα δεδομένα.

SELECT "title", "publication_year" 
FROM "books" 
WHERE "publication_year" BETWEEN 2008 AND 2013;

-- Για να επιλέξετε τα βιβλία που έχουν βαθμολογία 4.0 ή υψηλότερη

SELECT "title", "average_rating" 
FROM "books" 
WHERE "average_rating" > 4.0;

-- Για να περιοριστούν περαιτέρω τα επιλεγμένα βιβλία με βάση τον αριθμό των αξιολογήσεων και να υπάρχουν μόνο τα βιβλία 
-- με τουλάχιστον 30.000 αξιολογήσεις

SELECT "title", "average_rating", "rating_count" 
FROM "books" 
WHERE "average_rating" > 4.0 AND "rating_count" > 30000;

-- Για να επιλέξετε τα βιβλία που έχουν λιγότερες από 100 αξιολογήσεις

SELECT "title", "rating_count" 
FROM "books" 
WHERE "rating_count" < 100;

-- Η λέξη-κλειδί ORDER BY μας επιτρέπει να οργανώσουμε τις γραμμές που επιστρέφονται με κάποια καθορισμένη σειρά.
-- Το ακόλουθο ερώτημα επιλέγει τα 10 βιβλία που βρίσκονται στη βάση δεδομένων μας με βάση την βαθμολογία τους

SELECT "title", "average_rating" 
FROM "books" 
ORDER BY "average_rating" LIMIT 10; -- επιλέγει αύξουσα σειρά από προεπιλογή

-- για να επιλέξετε τα 10 κορυφαία βιβλία

SELECT "title", "average_rating" 
FROM "books" 
ORDER BY "average_rating" DESC LIMIT 10;

-- για να επιλέξετε τα 10 κορυφαία βιβλία με βάση την βαθμολογία και επίσης 
-- να συμπεριλάβετε τον αριθμό των αξιολογήσεων ως σημείο ισοβαθμίας
-- για κάθε στήλη στον όρο ORDER BY, μπορούμε να καθορίσουμε αύξουσα ή φθίνουσα σειρά

SELECT "title", "average_rating", "rating_count" 
FROM "books" 
ORDER BY "average_rating" DESC, "rating_count" DESC 
LIMIT 10;

-- Για να ταξινομήσουμε (λεξικογραφικά) τα βιβλία με βάση τον τίτλο τους αλφαβητικά, 
-- μπορούμε να χρησιμοποιήσουμε την εντολή ORDER BY

SELECT "title" 
FROM "books" 
ORDER BY "title";

SELECT "title" 
FROM "books" 
ORDER BY "title" DESC;

-- Η PostgreSQL χρησιμοποιεί Τύπους Δεδομένων. Είναι μια βάση δεδομένων με ισχυρούς τύπους, που σημαίνει 
-- ότι επιβάλλει αυστηρούς κανόνες σχετικά με το είδος των δεδομένων που μπορούν να εισαχθούν σε μια στήλη:

-- 1. Αριθμητικοί Τύποι (Αριθμοί)
-- INTEGER (ή INT): Ο τυπικός ακέραιος αριθμός 4 byte. Κυμαίνεται από -2 δισεκατομμύρια έως +2 δισεκατομμύρια.
-- BIGINT: Ακέραιος 8 byte. Χρησιμοποιήστε το για αναγνωριστικά σε τεράστια σύνολα δεδομένων 
-- NUMERIC / DECIMAL: Ακριβής ακρίβεια. 
-- REAL / DOUBLE PRECISION: Αριθμοί "κινητής υποδιαστολής". Ταχύτεροι για μαθηματικά, αλλά μπορεί να έχουν 
-- μικρά σφάλματα στρογγυλοποίησης.
-- 2. Τύποι Χαρακτήρων (Κείμενο)Στην Postgres, δεν υπάρχει διαφορά απόδοσης μεταξύ αυτών των τριών, 
-- επομένως οι περισσότεροι χρησιμοποιούν απλώς TEXT.
-- TEXT: Μια συμβολοσειρά απεριόριστου μήκους (η καλύτερη προεπιλογή).
-- VARCHAR(n): Μεταβλητό μήκος με όριο. Εάν προσπαθήσετε να εισαγάγετε μια συμβολοσειρά μεγαλύτερη από n, αποτυγχάνει.
-- CHAR(n): Σταθερού μήκους, με κενό. Σπάνια χρησιμοποιείται σήμερα, εκτός εάν έχετε μια πολύ συγκεκριμένη 
-- απαίτηση παλαιού τύπου (όπως κωδικό χώρας 2 χαρακτήρων).
-- 3. Τύποι ημερομηνίας/ώρας
-- DATE: Μόνο η ημερομηνία (ΕΕΕΕ-ΜΜ-ΗΗ).
-- TIMESTAMP: Ημερομηνία και ώρα.
-- TIMESTAMPTZ: Επίγνωση ημερομηνίας, ώρας και ζώνης ώρας. Αυτό είναι το επαγγελματικό πρότυπο για τις στήλες "created_at".
-- INTERVAL: Αντιπροσωπεύει ένα χρονικό διάστημα (π.χ., "3 ημέρες" ή "1 μήνας 2 ώρες").
-- 4. Τύπος Boolean
-- BOOLEAN: Αποθηκεύει TRUE, FALSE ή NULL (άγνωστο). ξ=Η Postgres είναι έξυπνη και δέχεται επίσης συμβολοσειρές 
-- όπως  'yes', 'no', '1', '0', 't', ή 'f' κατά την εισαγωγή.

-- Εκτελούμε την ακόλουθη εντολή για να δημιουργήσουμε τον πίνακα my_table. 

CREATE TABLE "my_table" (
"id" INTEGER,
"name" TEXT
);

-- απόρριψη ενός πίνακα (και των δεδομένων του)

DROP TABLE "my_table";

-- Μπορούμε να χρησιμοποιήσουμε περιορισμούς πίνακα για να επιβάλουμε περιορισμούς σε ορισμένες τιμές στους πίνακές μας.
-- Για παράδειγμα, μια στήλη πρωτεύοντος κλειδιού πρέπει να έχει μοναδικές τιμές. Ο περιορισμός πίνακα που χρησιμοποιούμε 
-- για αυτό είναι PRIMARY KEY.
-- Περισσότεροι τύποι περιορισμών θα παρουσιαστούν στα επόμενα εργαστήρια

CREATE TABLE "my_table" (
"id" INTEGER PRIMARY KEY,
"name" TEXT
);

DROP TABLE "my_table";

-- εναλλακτική γραφή του περιορισμού

CREATE TABLE "my_table" (
"id" INTEGER,
"name" TEXT,
PRIMARY KEY("id")
);

-- Μετονομασία πίνακα

ALTER TABLE "my_table"
RENAME TO "my_table_1";

-- μπορεί να δημιουργηθεί πρωτεύον κλειδί που να περιλαμβάνει δύο ή και περισσότερες στήλες.

CREATE TABLE "my_table_2" (
"id" INTEGER,
"fname" TEXT,
"lname" TEXT,
"phone" TEXT,
PRIMARY KEY("fname", "lname", "phone")
);

-- Προσθήκη στήλης πίνακα

ALTER TABLE "my_table_1"
ADD COLUMN "description" TEXT;

-- Μετονομασία στήλης πίνακα

ALTER TABLE "my_table_1"
RENAME COLUMN "name" TO "area_name";

-- Απόρριψη στήλης πίνακα

ALTER TABLE "my_table_1"
DROP COLUMN "description";

SELECT * FROM "my_table_2";

INSERT INTO "my_table_2" ("id", "fname", "lname", "phone")
VALUES(1, 'Fotis', 'Kazasis', '988345678');

SELECT * FROM "my_table_2";

-- Μπορούμε να προσθέσουμε περισσότερες γραμμές στη βάση δεδομένων εισάγοντας πολλές φορές. 
-- Ωστόσο, η χειροκίνητη πληκτρολόγηση της τιμής του πρωτεύοντος κλειδιού (ως 1, 2, 3 κ.λπ.) μπορεί να οδηγήσει σε σφάλματα.
-- Για το λόγο αυτό χρησιμοποιούμε την αυτόματη αύξηση

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name TEXT
);

-- Τι εκτελείται από την PostgreSQL όταν δίνουμε τον τύπο SERIAL

-- 1
CREATE SEQUENCE authors_author_id_seq;
-- 2
CREATE TABLE authors (
    author_id INTEGER NOT NULL DEFAULT nextval('authors_author_id_seq') PRIMARY KEY,
    name TEXT
);
-- 3
ALTER SEQUENCE authors_author_id_seq OWNED BY authors.author_id;

-- 

SELECT * FROM "authors";

INSERT INTO "authors"("name")
VALUES('Fotis Kazasis');

SELECT * FROM "authors";

DROP TABLE "authors";

-- Ο ενδεδειγμένος τρόπος αυτόματης αύξησης 

CREATE TABLE authors (
    author_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT
);

-- Το σετ δεδομένων books.csv που χρησιμοποιείται Kaggle - Goodbooks-10k (https://www.kaggle.com/datasets/zygmunt/goodbooks-10k)

CREATE DATABASE "db2026_lab1_test";

CREATE TABLE "goodreads_raw" (
    "book_id" TEXT, "id" TEXT, "best_book_id" TEXT, "work_id" TEXT, "books_count" TEXT,
    "isbn" TEXT, "isbn13" TEXT, "authors" TEXT, "original_publication_year" TEXT,
    "original_title" TEXT, "title" TEXT, "language_code" TEXT, "average_rating" TEXT,
    "ratings_count" TEXT, "work_ratings_count" TEXT, "work_text_reviews_count" TEXT,
    "ratings_1" TEXT, "ratings_2" TEXT, "ratings_3" TEXT, "ratings_4" TEXT, "ratings_5" TEXT,
    "image_url" TEXT, "small_image_url" TEXT
);

-- Εισαγωγή των δεδομένων από το αρχείο books.csv είτε με γραφικό τρόπο είτε με την εντολή COPY (θα την δούμε σε άλλο εργαστήριο)
-- format = csv, encoding = UTF8, Header on, Delimiter = ',', Quote = '"', Escape='''

CREATE TABLE "books" (
    "book_id" SERIAL PRIMARY KEY,
    "title" TEXT,
    "authors" TEXT,
"language_code" VARCHAR(5),
    "isbn" VARCHAR(20),
    "publication_year" INT,
    "average_rating" NUMERIC,
    "rating_count" INT
);

-- κατά την εισαγωγή για όσες στήλες δεν έχουν τύπο TEXT γίνεται κατάλληλη μετατροπή τύπων (type casting)

INSERT INTO "books" ("title", "authors", "language_code", "isbn", "publication_year", "average_rating", "rating_count")
SELECT 
    "title", 
    "authors",
"language_code",
"isbn",
    "original_publication_year"::NUMERIC::INT,
"average_rating"::NUMERIC,
    "work_text_reviews_count"::INT
FROM "goodreads_raw";