pasta

SELECT O.IMIE, O.NAZWISKO, M1.MIASTO AS "MIASTO ZAMIESZKANIA", M2.MIASTO AS "MIASTO URODZENIA"
FROM OSOBY O
LEFT OUTER JOIN MIASTA M1
ON O.ID_MIASTA=M1.ID_MIASTA
LEFT OUTER JOIN MIASTA M2
ON O.ID_MIASTA_URODZENIA = M2.ID_MIASTA
ORDER BY 2

SELECT O.IMIE, O.NAZWISKO, M1.MIASTO AS "MIASTO ZAMIESZKANIA", M2.MIASTO AS "MIASTO URODZENIA"
FROM OSOBY O
LEFT OUTER JOIN MIASTA M1
ON O.ID_MIASTA=M1.ID_MIASTA
LEFT OUTER JOIN MIASTA M2
ON O.ID_MIASTA_URODZENIA = M2.ID_MIASTA
ORDER BY 2 COLLATE PXW_PLK;

as
/* code here */
BEGIN
  NEW.ID_OSOBY = gen_id(G_OSOBY, 1);
END

as
/* code here */
BEGIN
IF (NEW.IMIE IS NULL) THEN NEW.IMIE = 'Brak imienia';
END

SELECT COALESCE(IMIE, 'Brak imienia') || ' ' || COALESCE(NAZWISKO, 'Brak nazwisko')
as "Imie i nazwisko"
FROM OSOBY;

SELECT COALESCE(NAZWISKO, 'Brak nazwiska') || ' ' || COALESCE(SUBSTRING(IMIE FROM 1 for 1) || '.', '')
as "Imie i nazwisko"
FROM OSOBY;

SELECT NUMER, CAST(NUMER AS VARCHAR(30))
FROM OSOBY;


select CAST('TODAY' AS date)
from osoby

select CAST('TODAY' AS date)
as "data"
from osoby group by 'TODAY'

select CAST('TODAY' AS date)
as "data"
from RDB$DATABASE -tabela systemowa

select CAST('NOW' AS timestamp)
as "Data"
from RDB$DATABASE

select EXTRACT(YEAR FROM CAST('TODAY' AS date))
as "Data"
from RDB$DATABASE


select Imie, Nazwisko, EXTRACT(YEAR FROM DATA_URODZENIA) as "Rok urodzenia"
from OSOBY


select Imie, Nazwisko, EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA)
as "Wiek"
from OSOBY

select Imie, Nazwisko, EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA),
CASE
WHEN EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA) < 18 THEN 'Mlody'
WHEN EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA) > 18 THEN 'Stary'
Else
'Ok'
END
from OSOBY


select Imie, Nazwisko, EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA),
CASE
WHEN EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA) < 18 THEN 'Mlody'
WHEN EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA) > 18 THEN 'Stary'
WHEN EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA) = 18 THEN 'Ok'
Else
'Nie wiadomo'
END
from OSOBY


select Imie, Nazwisko, EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA) as "Wiek",
CASE
WHEN EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA) < :lata THEN 'Mlody'
WHEN EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA) > :lata THEN 'Stary'
WHEN EXTRACT(YEAR FROM CAST('TODAY' AS date)) - EXTRACT(YEAR FROM DATA_URODZENIA) = :lata THEN 'Ok'
Else
'Nie wiadomo'
END as "Info"
from OSOBY



SELECT SUMA
FROM DZIALANIA(21,37);

SELECT *
FROM DZIALANIA(15,5);


begin
SUMA=:SKLADNIK1+:SKLADNIK2;
ROZNICA=:SKLADNIK1 - :SKLADNIK2;
ILOCZYN=:SKLADNIK1 * :SKLADNIK2;
ILORAZ=:SKLADNIK1 / :SKLADNIK2;
SUSPEND;
end



SELECT O.NAZWISKO, O.NUMER, S.SUMA
FROM OSOBY AS O
LEFT JOIN DZIALANIA(0.NUMER,10) AS S ON O.NUMER IS NOT NULL;


create view new_view
(
IMIE, NAZWISKO, MIASTO
)
as
SELECT o.IMIE, o.NAZWISKO, m.MIASTO
FROM OSOBY o LEFT JOIN MIASTA m
ON o.ID_MIASTA = m.ID_MIASTA


SELECT o.IMIE, o.NAZWISKO, SUM(h.KWOTA)
FROM OSOBY o LEFT JOIN HARACZE h ON o.ID_OSOBY = h.ID_OSOBY
GROUP BY o.IMIE, o.NAZWISKO



/* View: WIDOK, Owner: SYSDBA */

CREATE VIEW "WIDOK" (
  "ALA",
  "MA",
  "KOTA"
) AS

SELECT o.IMIE, o.NAZWISKO, m.MIASTO
FROM OSOBY o LEFT JOIN MIASTA m
ON o.ID_MIASTA = m.ID_MIASTA
;