-- 1. Τελεστής IN
-- Βρες το email, το όνομα και το επώνυμο των μελών (member) που προέρχονται από συγκεκριμένες χώρες (π.χ. Ελλάδα και Κύπρο).

SELECT email, "firstName", "secondName", country
FROM public.member
WHERE country IN ('Greece', 'Cyprus');

-- 2. Τελεστής NOT IN
-- Βρες τα μέλη που δεν έχουν δημοσιεύσει κανένα άρθρο.

SELECT email, "firstName", "secondName"
FROM public.member
WHERE email NOT IN (
SELECT email
FROM public.article
WHERE email IS NOT NULL
);

-- 3. Τελεστής EXISTS
-- Βρες τα μέλη που έχουν καταχωρήσει τουλάχιστον μία τρέχουσα επαγγελματική εμπειρία (experience).

SELECT m.email, m."firstName", m."secondName"
FROM public.member m
WHERE EXISTS (
SELECT 1
FROM public.experience e
WHERE e.email = m.email
AND e."workStatus" = 'CURRENT'
);

-- 4. Τελεστής NOT EXISTS
-- Βρες όλες τις ερωτήσεις (question) οι οποίες δεν έχουν λάβει ακόμη καμία απάντηση (answer).

SELECT q."questionID", q.question, q."datePosted"
FROM public.question q
WHERE NOT EXISTS (
SELECT 1
FROM public.answer a
WHERE a."questionID" = q."questionID"
);

-- 5. Τελεστής ANY
-- Βρες αγγελίες (advertisement) οι οποίες προσφέρουν μισθό (salary) μεγαλύτερο από τον μισθό οποιασδήποτε (τουλάχιστον μίας) αγγελίας που έχει αναρτηθεί για τον τομέα της Πληροφορικής (π.χ., industry = 'IT').

SELECT "advertisementID", title, industry, salary
FROM public.advertisement
WHERE salary > ANY (
SELECT salary
FROM public.advertisement
WHERE industry = 'IT' AND salary IS NOT NULL
);

-- 6. Τελεστής ALL
-- Βρες την αγγελία (ή τις αγγελίες) που προσφέρουν τον απολύτως υψηλότερο μισθό σε ολόκληρο το σύστημα. Συγκρίνουμε δηλαδή τον μισθό της αγγελίας με τον μισθό όλων των υπολοίπων.

SELECT "advertisementID", title, industry, salary
FROM public.advertisement
WHERE salary >= ALL (
SELECT salary
FROM public.advertisement
WHERE salary IS NOT NULL
);
-- Σημείωση: Στα ερωτήματα με τους τελεστές NOT IN, ANY και ALL συχνά χρειάζεται να αποκλείουμε
-- τις τιμές NULL (IS NOT NULL) στο υποερώτημα, καθώς η σύγκριση με NULL μπορεί να προκαλέσει
-- απρόβλεπτα αποτελέσματα (επιστρέφοντας "Unknown" λογική τιμή η οποία συχνά απορρίπτεται)

-- 7.
-- Βρες το συνολικό πλήθος άρθρων ανά μέλος

SELECT
m.email,
m."firstName",
m."secondName",
(SELECT COUNT(*)
FROM public.article a
WHERE a.email = m.email) AS "totalArticles"
FROM public.member m;

-- 8.
-- Για λόγους σύγκρισης εκτύπωσε μισθό αγγελίας και μέσο μισθό αγγελιών

SELECT
"advertisementID",
title,
salary,
(SELECT AVG(salary) FROM public.advertisement WHERE salary IS NOT NULL) AS "avgSystemSalary"
FROM public.advertisement
WHERE salary IS NOT NULL;

-- 9.
-- Βρες τον μέσο όρο των αριθμών σχολίων ανά άρθρο

SELECT
AVG(ac_counts."totalComments") AS "avgCommentsPerArticle"
FROM (
SELECT "articleID", COUNT("commentID") AS "totalComments"
FROM public."articleComment"
GROUP BY "articleID"
) AS ac_counts;

-- 10.
-- Βρες το όνομα και το επώνυμο των μελών που έχουν καταχωρήσει περισσότερες από 2 παρελθοντικές επαγγελματικές εμπειρίες (πεδίο "workStatus" = 'PAST' στον πίνακα experience).

SELECT
m."firstName",
m."secondName",
exp_data."pastExperienceCount"
FROM public.member m
JOIN (
SELECT email, COUNT(*) AS "pastExperienceCount"
FROM public.experience
WHERE "workStatus" = 'PAST'
GROUP BY email
) AS exp_data ON m.email = exp_data.email
WHERE exp_data."pastExperienceCount" > 2;