kaplan_db.sql

-- DDL operations

drop function check_the_length;
-- execute functions step by step and observe the structure of the functions
--Do not forget that many operations to be done here will also be done by creating a procedure. I focused on functions

-- length
create or replace function check_the_length(varchar(100), length integer) returns boolean as
$$
begin
    return length($1) = length;
end
$$ language plpgsql;



-- substring
create or replace function check_the_digit(column_name char(10), begins_from integer) returns boolean as
$$
begin
    return substr(column_name, begins_from) ~ '^[0-9]*$';
end
$$ language plpgsql;



--left
create or replace function check_the_left_letter(column_name char(10), begins_from integer) returns boolean as
$$
begin
    return "left"(column_name, begins_from) !~ '^[0-9]*$';
end
$$ language plpgsql;



--regexp_match
create or replace function null_or_empty(text varchar(100)) returns boolean as
$$
begin
    return regexp_match(text, '\s+');
end
$$ language plpgsql;



--upper / left /repeat
create or replace function hide_and_change(name varchar(100), surname varchar(100), ch char(1), len integer)
    returns varchar(100) as
$$
begin
    return upper(left(name, len)) || repeat(ch, length(name) - len) || ' ' || upper(left(surname, len)) ||
           repeat(ch, length(surname) - len);
end
$$ language plpgsql;



--exists
create or replace function hide_and_change_via_citizen_number(char(11), ch char(1), len integer)
    returns varchar(100) as
$$
begin
    if exists((select first_name from customers where citizen_number = $1))
    then
        return hide_and_change((select first_name from customers where citizen_number = $1),
                               (select family_name from customers where citizen_number = $1), ch, len);
    end if;
end
$$ language plpgsql;



--create a semi complex function by using if/else statement
create or replace function get_marital_status_text_tr(marital_status boolean)
    returns varchar(10) as
$$
declare
    status_str varchar(10);
begin
    if marital_status = true then
        status_str = 'married';
    elseif marital_status = false then
        status_str = 'single';
    end if;
    return status_str;
end
$$ language plpgsql;



create or replace function get_detail_information()
    returns table
            (
                id             char(11),
                name           varchar(100),
                marital_status varchar
            )
as
$$
begin
    return query select c.citizen_number,
                        hide_and_change(c.first_name, c.family_name, '#', 2),
                        get_marital_status_text_tr(c.married)
                 from customers c;
end
$$ language plpgsql;



drop table customers;


create table customers
(
    customer_id            serial primary key,
    --check if the first char is string and the rest of it is numbers-using check keyword and functions like
    --length substring etc.
    citizen_number         char(11) unique check (check_the_length(citizen_number, 11) and
                                                  check_the_digit(citizen_number, 2) and
                                                  check_the_left_letter(citizen_number, 1) ) not null,

    number                 varchar(5) unique check (check_the_digit(number, 1))              not null,

    first_name             varchar(100) check ( null_or_empty(first_name) )                  not null,
    middle_name            varchar(100),
    family_name            varchar(100) check ( null_or_empty(family_name) )                 not null,
    birth_date             date                                                              not null,
    enrollment_date_utc    timestamp default (current_timestamp)                             not null,
    enrollment_date_turkey timestamp default (convert_time('Turkey'))                        not null,
    last_update_turkey     timestamp default (current_timestamp)                             not null,
    married                boolean   default (false)                                         not null,
    adult                  boolean   default true,
    phone_number           char(14)                                                          not null

);

--insert by using a function
create or replace function insert_customer(char(11), char(11), varchar(100), varchar(100), varchar(100), date, boolean,
                                           char(14))
    returns void as
$$
declare
begin
    insert into customers (citizen_number, number, first_name, middle_name, family_name, birth_date, married,
                           phone_number)
    values ($1, $2, $3, $4, $5, $6, $7, $8);
end;
$$ language plpgsql;

create or replace function update_customer_middle_name(m_name char(20), c_number char(11))
    returns void as
$$
begin
    update customers set middle_name=m_name, last_update_turkey=convert_time('Turkey') where citizen_number = c_number;
end;
$$ language plpgsql;

--check the error
select insert_customer('aa234567890', '01234', 'baran', 'baran', 'kaplan', '05-10-1989', false, '123');
--check the error
select insert_customer('a1234567890', 'a1234', 'baran', 'baran', 'kaplan', '05-10-1989', false, '123');
--check the error
select insert_customer('a1234567890', '01234', 'baran and', 'baran', 'kaplan', '05-10-1989', false, '123');
--this one works!
select insert_customer('a1234567890', '01234', 'baran', '', 'kaplan', '05-10-2021', false, '123');
select insert_customer('b1234567890', '11234', 'baran', '', 'kaplan', '05-10-2021', false, '123');


--take the id of a row
do
$$
    declare
        device_id int;
    begin
        --         perform insert_customer('a1234567890', '01234', 'baran', '', 'kaplan', '05-10-2021', false, '123');
        perform insert_customer('g1234567890', '98765', 'baran', '', 'kaplan', '05-10-2021', false, '123');
        device_id = currval('customers_customer_id_seq');
        raise notice '%', device_id;
    end
$$;

--using our functions
select update_customer_middle_name('a middle name', 'a1234567890');


select (customer_id, citizen_number)
from customers;

select hide_and_change('baran', 'kaplan', '*', 3);

select hide_and_change_via_citizen_number('a1234567890', '*', 2);

select *
from get_detail_information();

select *, get_marital_status_text_tr(true)
from customers;

select get_marital_status_text_tr(true)
from customers;


------------------- procedures

create or replace function find_birth_date_of_the_customer(char(11))
    returns date as
$$
begin
    return (select birth_date from customers where citizen_number = $1);
end;
$$ language plpgsql;


create or replace procedure get_customer_age_and_insert_customer_label(char(11))
    language plpgsql
as
$$
declare
    birth_date date;
begin
    birth_date = find_birth_date_of_the_customer($1);
    if date_part('year', age(current_date, birth_date)) < 18 then
        update customers set adult= false where citizen_number = $1;
    end if;
end;
$$;



create or replace procedure get_customer_age_and_insert_customer_label_v2(char(11))
    language plpgsql
as
$$
declare
    the_birth_date date;
begin
    select c.birth_date from customers c where c.citizen_number = $1 into the_birth_date;
    if date_part('year', age(current_date, the_birth_date)) < 18 then
        update customers set adult= false where citizen_number = $1;
    end if;
end;
$$;

--a1234567890 recorded himself as adult-true bu he is not
--so we can check and change- after this call true will return to false
call get_customer_age_and_insert_customer_label('a1234567890');
call get_customer_age_and_insert_customer_label_v2('a1234567890');

drop function get_birthday_weekend;

create or replace function get_birthday_weekend(month int, year int)
    returns table
            (
                birt_date date
            )
as
$$
begin
    return query
        select c.birth_date
        from customers c
        where (extract(dow from birth_date) = 0 or extract(dow from birth_date) = 6)
          and (extract(month from birth_date) = month and extract(year from birth_date) = year);
end
$$ language plpgsql;

--function overloading
create or replace function get_birthday_weekend()
    returns table
            (
                birt_date date
            )
as
$$
begin
    return query
        select c.birth_date
        from customers c
        where (extract(dow from birth_date) = 0 or extract(dow from birth_date) = 6);
end
$$ language plpgsql;

drop function get_birthday_weekday;
create or replace function get_birthday_weekday()
    returns table
            (
                birt_date   date,
                family_name varchar(100)
            )
as
$$
begin
    return query
        select c.birth_date, c.family_name
        from customers c
        where (extract(dow from birth_date) <> 0 and extract(dow from birth_date) <> 6);
end
$$ language plpgsql;

create or replace function get_birthday_weekday(month int)
    returns table
            (
                birt_date   date,
                family_name varchar(100)
            )
as
$$
begin
    return query
        select c.birth_date, c.family_name
        from customers c
        where (extract(dow from birth_date) <> 0 and extract(dow from birth_date) <> 6)
          and (extract(month from birth_date) = month);

end
$$ language plpgsql;

-- function overloading -was borned weekend or weekday
select get_birthday_weekend(5, 2021);
select get_birthday_weekday(6);

select get_birthday_weekend();
select get_birthday_weekday();



drop table cards;
--limit ekle
create table cards
(
    card_number   char(16) primary key,
    customer_id   int references customers (customer_id)               not null,
    expiry_month  int check (1 <= expiry_month and expiry_month <= 12) not null,
    expiry_year   integer check (expiry_year >= 0)                     not null,
    security_code char(3)                                              not null,
    card_limit    int8,
    segment       char(15)
);

create or replace procedure insert_card_by_taking_current_customer(char(16), int, integer, char(3), int, char(15))
    language plpgsql
as
$$
declare
    device_id int;
begin
    --@@identity
    device_id = currval('customers_customer_id_seq');

    insert into cards(card_number, customer_id, expiry_month, expiry_year, security_code, card_limit, segment)
    values ($1, device_id, $2, $3, $4, $5, $6);

end;
$$;

call insert_card_by_taking_current_customer('123456789012345', 5, 2030, '123');



create or replace function eomonth(date)
    returns date as
$$
begin
    return date_trunc('month', $1) + interval '1 month - 1 day';
end;
$$ language plpgsql;


create or replace function last_day_of_the_month(date)
    returns int as
$$
declare
begin
    return date_part('day', eomonth($1));
end;
$$ language plpgsql;

-- how to get last day of the month
select last_day_of_the_month(current_date);

create or replace procedure card_information()
    language plpgsql
as
$$
declare
    paying_date   int;
    current_month varchar(15);
    time_to_pay   int;
begin
    paying_date = last_day_of_the_month(current_date) - 7;
    current_month = to_char(current_date, 'month');
    time_to_pay = paying_date - date_part('day', current_date);
    raise notice 'last day of payment in %is on %',current_month,paying_date;
    if time_to_pay < 0 then
        raise notice 'you missed the payment date!';
    else
        raise notice 'you still have % days to pay',time_to_pay;

    end if;
end;
$$;


call card_information();

do
$$
    declare
        date date;
    begin
        date = to_date('02-02-2020', 'DD-MM-YYYY');
        raise notice '%',date_part('day', eomonth(date));
    end
$$;

--date difference
select DATE_PART('year', '2012-01-01'::date) - DATE_PART('year', '2011-10-02'::date);

SELECT DATE_PART('day', '2011-12-31 01:00:00'::timestamp - '2011-12-29 23:00:00'::timestamp);


do
$$
    begin
        select DATE_PART('year', '2012-01-01'::date) - DATE_PART('year', '2011-10-02'::date);
    end
$$;

create or replace function get_full_text(varchar, varchar, varchar)
    returns varchar as
$$
declare
    full_text varchar = '';
begin
    if $1 is not null then
        full_text = full_text || trim($1);
    end if;

    if $2 is not null then
        full_text = full_text || ' ' || trim($2);
    end if;

    if $3 is not null then
        full_text = full_text || ' ' || trim($3);
    end if;

    return full_text;
end;
$$ language plpgsql;

select get_full_text((select first_name
                      from customers
                      where citizen_number = 'a1234567890'),
                     (select middle_name
                      from customers
                      where citizen_number = 'a1234567890'),
                     (select family_name
                      from customers
                      where citizen_number = 'a1234567890')
           );


SHOW TIMEZONE;
SET timezone = 'UTC';
drop table timestamp_demo;
CREATE TABLE timestamp_demo
(
    ts   TIMESTAMP,
    tstz TIMESTAMPTZ
);
INSERT INTO timestamp_demo (ts, tstz)
VALUES (current_timestamp, current_timestamp);
SELECT ts,
       tstz
FROM timestamp_demo;

SET timezone = 'Turkey';


SELECT ts,
       tstz
FROM timestamp_demo;



do
$$
    begin
        raise notice '%', current_date;
        raise notice '%', current_time;
        raise notice '%', current_timestamp;
    end;
$$;

do
$$
    declare
        today date;
    begin
        today = current_date;
        raise notice '%', date_part('day', today);
        raise notice '%', date_part('mon', today);
        raise notice '%', date_part('year', today);
        raise notice '%', date_part('doy', today);
        raise notice '%', date_part('dow', today);
        raise notice '%', date_part('century', today);
        raise notice '%', date_part('decade', today);
    end;
$$;

SELECT date_part('hour', TIMESTAMP '2017-03-18 10:20:30')   h,
       date_part('minute', TIMESTAMP '2017-03-18 10:20:30') m,
       date_part('second', TIMESTAMP '2017-03-18 10:20:30') s;



select date_part('mon', current_date);
select date_part('week', current_date);
select date_part('year', current_date);


select date_part('month', current_date) m, date_part('year', current_date) y;


SHOW TIMEZONE;
Select now();


select current_time;
select current_date;
select current_timestamp;

create or replace function convert_time(area char(40)) returns timestamp as
$$
begin
    return current_timestamp at time zone (area);
end
$$ language plpgsql;


select convert_time('Turkey');

create function time_function_month(area char(40)) returns double precision as
$$
begin
    return date_part('mon', convert_time(area));
end
$$ language plpgsql;

create function time_function_day(area char(40)) returns double precision as
$$
begin
    return date_part('day', convert_time(area));
end
$$ language plpgsql;

select time_function_month('Turkey');


select time_function_day('Turkey');

select date_part('day', current_date);



select time_function_day('Turkey') d, time_function_month('Turkey') m;

select current_time at time zone 'Turkey';


select current_timestamp at time zone 'Turkey';

select current_timestamp at time zone 'Turkey';


--hatali mi?
select current_date at time zone 'Turkey';

--timestamp ve timestampz farki


SELECT LOCALTIMESTAMP;


SELECT date_part('hour', current_time)   h,
       date_part('minute', current_time) m,
       date_part('second', current_time) s;



create or replace function get_add_and_multiply(a int, b int, out sum int, out multiply int)
as
$$
begin
    sum = a + b;
    multiply = a * b;
end;
$$ language plpgsql;

do
$$
    declare
        a               int = 10;
        b               int = 20;
        sum_result      int;
        multiply_result int;
    begin
        select sum, multiply from get_add_and_multiply(a, b) into sum_result, multiply_result;
        raise notice '% + % = %', a, b, sum_result;
        raise notice '% * % = %', a, b, multiply_result;
    end;
$$;

create or replace function eomonth(date)
    returns date as
$$
begin
    return date_trunc('month', $1) + interval '1 month - 1 day';
end;
$$ language plpgsql;

do
$$
    declare
        date date;
    begin
        date = to_date('02-02-2020', 'DD-MM-YYYY');
        raise notice '%', eomonth(date);
    end
$$;


do
$$
    declare
        birth_date     date;
        birth_date_str varchar(10);
        date_format_tr varchar(10);
    begin
        birth_date_str = '25/02/2000';
        date_format_tr = 'DD/MM/YYYY';
        birth_date = to_date(birth_date_str, date_format_tr);

        raise notice '%', date_part('year', age(current_date, birth_date));
    end;
$$;

do
$$
    declare
        birth_date     date;
        birth_date_str varchar(10);
        date_format_tr varchar(10);
    begin
        birth_date_str = '25/02/2000';
        date_format_tr = 'DD/MM/YYYY';
        birth_date = to_date(birth_date_str, date_format_tr);

        raise notice '%', date_part('year', age(current_date, birth_date));
    end;
$$;

truncate table cards;
truncate table customers cascade;

--takes the customer id and fetch automatically !
select insert_customer('g1348234969', '03782', 'baran', '', 'kaplan', '05-10-2010', false, '123');
call insert_card_by_taking_current_customer('123456789012345', 12, 2030, '123', 1000, 'basic');
select insert_customer('f7384025634', '93681', 'paul', '', 'mccartney', '05-10-1993', false, '111');
call insert_card_by_taking_current_customer('123456712312345', 11, 2026, '321', 500, 'premium');
select insert_customer('e1841730394', '74671', 'julia', '', 'field', '05-10-1995', false, '222');
call insert_card_by_taking_current_customer('908456789012345', 1, 2021, '222', 750, 'basic');
select insert_customer('d0418370471', '04792', 'ali', '', 'mark', '05-10-1989', false, '444');
call insert_card_by_taking_current_customer('123445289012345', 2, 2020, '555', 200, 'basic');
select insert_customer('c9478130000', '18490', 'moses', '', 'cheese', '05-10-2001', false, '123');
call insert_card_by_taking_current_customer('123456789567345', 7, 2024, '789', 3000, 'premium');
select insert_customer('k9278130000', '18090', 'moses', '', 'cheese', '05-10-2001', false, '123');
call insert_card_by_taking_current_customer('123456389567345', 4, 2000, '789', 250, 'premium');


--fundamentals

select first_name || ' bla bla ' || family_name as "new column"
from customers;
--got 2 moses
select first_name
from customers;

select distinct first_name
from customers
where (first_name like '%a%' and customers.first_name not like 'b%')
order by first_name;

select distinct first_name
from customers
where (first_name like '%a%' and customers.first_name not like 'b%')
order by first_name
limit 2 offset 1;

select distinct first_name
from customers
where (first_name like '%a%' and customers.first_name not like 'b%')
order by first_name
    fetch first 1 row only;

select distinct first_name
from customers
where citizen_number in ('f7384025634')
   or first_name = 'baran';

select distinct *
from customers
where citizen_number in ('f7384025634')
   or first_name = 'baran';

select distinct *
from customers
where middle_name is not null;

--from where group by having select distinct order by limit


select family_name, first_name, count(first_name)
from customers
where birth_date between '1989-01-01' and '2009-01-10'
group by first_name, family_name
having first_name != 'ali'
order by family_name desc
    fetch first 2 row only;


select family_name, first_name, count(first_name)
from customers
where birth_date between '1989-01-01' and '2009-01-10'
group by first_name, family_name
having first_name != 'ali'
order by family_name desc
limit 1;



select cus.first_name,
       cus.family_name,
       ca.card_number
from customers cus
         inner join cards ca
                    on cus.customer_id = ca.customer_id
order by expiry_year desc;

select insert_customer('z1348234969', '93782', 'none', '', 'kaplan', '05-10-2010', false, '123');


select cus.first_name,
       cus.family_name,
       ca.card_number
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
order by expiry_year desc;

select cus.first_name,
       cus.family_name,
       ca.card_number
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where ca.card_number is null
order by expiry_year desc;

select cus.first_name,
       cus.family_name,
       ca.card_number,
       card_limit
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where ca.card_number is not null
order by expiry_year desc;



select cus.first_name,
       cus.family_name
from customers cus
where length(first_name) > 4;



select cus.first_name,
       cus.family_name
from customers cus
where "left"(family_name, 3) = 'che';


select first_name,
       family_name
from customers
where family_name < any (select max(family_name)
                         from customers)
  and "left"(family_name, 3) != 'che';



select first_name,
       family_name
from customers
where family_name in (select max(family_name)
                      from customers)
  and "left"(family_name, 3) != 'che';



SELECT first_name,
       family_name
FROM customers c
WHERE EXISTS
          (SELECT 1
           FROM cards ca
           WHERE ca.customer_id = c.customer_id
             AND (ca.expiry_year > 2022 or security_code = '555'))
ORDER BY first_name,
         family_name;

SELECT first_name,
       family_name
from customers
where birth_date = null;


select first_name,
       family_name,
       segment,
       card_limit
from customers
         join cards c on customers.customer_id = c.customer_id;



select cus.first_name,
       cus.family_name,
       ca.segment,
       avg(card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where ca.card_number is not null
group by
    grouping sets ((first_name, family_name, segment));

select cus.first_name,
       cus.family_name,
       ca.card_number,
       segment,
       sum(card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where ca.card_number is not null
group by
    rollup (segment, cus.family_name, ca.card_number, cus.first_name)
order by card_number;


SELECT *
FROM cards
         NATURAL JOIN customers;

select *
from customers
         join cards c on customers.customer_id = c.customer_id;


select *
from cards
         join customers c on c.customer_id = cards.customer_id;

select cus.first_name,
       ca.segment,
       sum(card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where ca.card_number is not null
group by segment,
    cube ( first_name)
order by first_name;
--name segment 5
--name 5
--null 1 =11
select cus.first_name,
       ca.segment,
       sum(card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where ca.card_number is not null
group by
    rollup (first_name, segment)
order by first_name;



--name segment 5
--name 5
--segment 2
--null 1 =13
select cus.first_name,
       ca.segment,
       sum(card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where ca.card_number is not null
group by
    cube (first_name, segment)
order by first_name;

--name 5
--segment 2 =7
select cus.first_name,
       ca.segment,
       sum(card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where ca.card_number is not null
group by
    grouping sets (first_name, segment)
order by first_name;

--aggregate functions //moses with same id has 2 cards 3000 and 250
select cus.first_name, sum(ca.card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
group by cus.first_name;

select cus.first_name, sum(ca.card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
group by cus.first_name;


select cus.first_name, sum(ca.card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
group by cus.first_name
having sum(ca.card_limit) < 800;

select cus.first_name, sum(ca.card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where (first_name not like 'a%')
group by cus.first_name
having sum(ca.card_limit) < 800
order by sum(ca.card_limit) desc;


select cus.first_name, sum(ca.card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where (first_name not like 'a%')
group by cus.first_name
having sum(ca.card_limit) < 800
order by sum(ca.card_limit) desc
limit 1;

--string functions
select upper(cus.first_name), sum(ca.card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where (first_name not like 'a%')
group by cus.first_name
having sum(ca.card_limit) < 800
order by sum(ca.card_limit) desc
limit 1;


select upper(cus.first_name), sum(ca.card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where (first_name not like 'a%')
group by cus.first_name
having sum(ca.card_limit) < 800
order by sum(ca.card_limit) desc
limit 1;


create or replace function upper_step_1()
    returns table
            (
                "(cus.first_name)" varchar(100)
            )
as
$$
begin
    return query select (cus.first_name)
                 from customers cus
                          left join cards ca
                                    on cus.customer_id = ca.customer_id
                 where (first_name not like 'a%')
                 group by cus.first_name
                 having sum(ca.card_limit) < 800
                 order by sum(ca.card_limit) desc
                 limit 1;
end
$$ language plpgsql;

select upper_step_1();


select "(cus.first_name)"
from upper_step_1();


create or replace function upper_step_2()
    returns varchar(100)
as
$$
declare
    cus_name varchar(100);
begin
    select (cus.first_name)
    from customers cus
             left join cards ca
                       on cus.customer_id = ca.customer_id
    where (first_name not like 'a%')
    group by cus.first_name
    having sum(ca.card_limit) < 800
    order by sum(ca.card_limit) desc
    limit 1
    into cus_name;
    return cus_name;
end
$$ language plpgsql;

select upper_step_2();

update
    customers
set first_name= upper(first_name)
where first_name = upper_step_2();

--julia is uppercase!
select first_name
from customers;


--julia is lowercase!
update
    customers
set first_name= lower(first_name)
where first_name = upper_step_2();

select first_name
from customers;


--math functions

select cus.first_name,
       ca.segment,
       sqrt(card_limit)
from customers cus
         left join cards ca
                   on cus.customer_id = ca.customer_id
where ca.card_number is not null
group by
    grouping sets (first_name, segment), card_limit
order by first_name;

--date functions

select first_name,
       age(current_date, birth_date) as customer_years
from customers;


select first_name,
       concat(cast(round(date_part('day', current_date::timestamp - birth_date::timestamp) / 365) as text), ' years')
           as customer_years
from customers;


select first_name,
       date_part('day', (now() - birth_date)) as customer_years
from customers;


select first_name,
       to_date(age(current_date, birth_date), 'yyyymmdd') customer_years
from customers;

select date_trunc('day', now());

select first_name,
       age(now(), birth_date) as customer_years
from customers;

select first_name,
       extract(year from age(now(), birth_date)) as customer_years
from customers;
--window functions
--the limit of half of the cards is 500 or less
SELECT card_limit,
       card_number,
       CUME_DIST() OVER (
           ORDER BY card_limit
           )
FROM cards
group by card_number;

--numbering the same code with 5
SELECT security_code,
       DENSE_RANK() OVER (
           ORDER BY security_code
           ) dense_rank_number
FROM cards;
;

SELECT first_name,
       family_name,
       FIRST_VALUE(birth_date)
       OVER (
           ORDER BY birth_date
           ) youngest
FROM customers;



--views
create view kaplans_view as
select cus.first_name,
       cus.family_name,
       ca.card_number,
       case
           when
               ca.card_limit > 600 then 'focus on'
           else ' do nothing'
           end as notes
from customers cus
         inner join cards ca
                    on cus.customer_id = ca.customer_id
order by expiry_year desc;

select *
from kaplans_view;


--triggers

create table customers_logs (
   id int generated always as identity ,
   customer_id int not null ,
   first_name varchar(40) not null ,
   changed_on timestamp(6) not null
);

create or replace function log_first_name_changes()
    returns trigger
    language plpgsql
as
    $$
    begin
    if new.first_name <> old.first_name then
        insert into customers_logs(customer_id,first_name,changed_on)
        values (old.customer_id,old.first_name,now());
    end if;
    return new;
end;
    $$;

drop trigger first_name_changes on customers;

create trigger first_name_changes
    before update
    on customers
    for each row
    execute procedure log_first_name_changes();


update
    customers
set first_name= upper(first_name)
where first_name =upper_step_2();

select * from customers;


--index
--citizen number is something unique
 create unique index idx_citizen_number
on customers(citizen_number);