PB_vaje_2

# Druge vaje ------------------------------------------------------------------
# 1. Poišči vse evklidske IN manhattan razdalje med naselji igralca “hinko”.
SELECT n1.village, n2.village,
SQRT(pow((n1.x - n2.x), 2) + pow((n1.y - n2.y), 2)) AS evklidska_r,
abs(n1.x - n2.x) + abs(n1.y - n2.y) AS manhattn_r
 FROM igralec i, naselje n1, naselje n2
WHERE i.pid = n1.pid AND i.pid = n2.pid
AND i.player = "hinko"
AND n1.id != n2.id
AND n1.id > n2.id;

# 2. Izpiši imena mesecev v katerih so bile opravljene rezervacije.
SELECT dan, DATE_FORMAT(dan, '%M') AS mesec
FROM rezervacija;

SELECT DISTINCT DATE_FORMAT(dan, '%M') AS mesec
FROM rezervacija;

# 3. Poišči imena IN njihova pripadajoča plemena vseh igralev,
# ki pripadajo aliansam, ki vsebujejo znak ‘™’.

SELECT i.player, a.alliance, p.tribe
FROM aliansa a, pleme p, igralec i
WHERE i.tid = p.tid AND i.aid = a.aid # Stik
AND a.alliance LIKE "%™%";

# 5 .Ustvari navidezni atribut “Rangiran”,
# ki vsebuje vrednost ‘Da’, če ima jadralec podan rang, sicer ‘Ne’.

SELECT *, IF(rang IS NULL, "Ne", "Da") AS Rangariran
FROM jadralec;

# 4. Poišči vsa imena igralcev, ki ne vsebujejo alfa-numeričnih znakov.

SELECT player
FROM igralec
WHERE player RLIKE "^[a-z]*$";

#  1. Poišči alianse, ki imajo vsaj eno naselje na območju (-50, -50) do (50, 50).

SELECT DISTINCT a.alliance
FROM aliansa a, naselje n, igralec i
WHERE a.aid = i.aid AND n.pid = i.pid # Stik
AND n.x BETWEEN -50 AND 50
AND n.y BETWEEN -50 AND 50;