Uni DB1 Syntax Cheatsheet

create table persons (
    person_id number primary key not null ,
    first_name varchar2(50),
    last_name varchar2(50) default 'Duck' not null
);

drop table persons;

alter table persons rename to people;

alter table persons add ( phone varchar2(20), email varchar2(100) )

alter table persons modify ( birthdate date null, email varchar2(255) );

alter table persons drop column birthdate;

alter table purchase_orders add constraint purchase_orders_order_id_pk primary key(order_id);

alter table purchase_orders drop constraint purchase_orders_order_id_pk;

create view employees_years_of_service
as select
    employee_id, first_name || ' ' || last_name as full_name,
    floor(months_between(current_date, hire_date) / 12) as years_of_service
from employees;

drop view employees_years_of_service;

create index members_full_name on members(first_name, last_name);

drop index members_full_name;

create trigger customers_credit_trigger
    before update of credit_limit
    on customers
declare
    current_day number;
begin
    current_day := extract(day from sysdate);

    if current_day between 28 and 31 then
        raise_application_error(-20100, 'Locked at the end of the month');
    end if;
end;

drop trigger customers_credit_trigger;

create trigger users_ensure_trigger
    before update
    on users
    for each row
declare
    user_invalid exception;
    pragma exception_init(user_invalid, -20555);
begin
    raise user_invalid;

    exception
        when user_invalid then
            raise_application_error(-20555, 'User is invalid');
        when others then
            dbms_output.put_line('Unexpected error: ' || sqlerrm);
end;

create or replace function get_my_sum( a integer, b integer ) return integer
is
        multiplier number := 2;
begin
        return a + b * multiplier;
end;

select get_my_sum(1, 2) from dual;

drop function get_my_sum;

create or replace procedure get_sum ( a integer, b integer )
is
        multiplier number := 2;
        result number := 0;
begin
        result := a + b * multiplier;

        insert into results ( result ) values ( result );
end;

exec get_sum(1, 2);

drop procedure get_sum;

insert into discounts(
    discount_name,
    amount,
    start_date,
    expired_date
) values (
    'Summer Promotion',
    9.5,
    date '2017-05-01',
    date '2017-08-31'
)

update products
set list_price = 420
where list_price < 69;

delete from products
where list_price > 69;

select
    first_name,
    last_name,
    email,
    'contact' as role
from contacts
union select
    first_name,
    last_name,
    email,
    'employee' as role
from employees order by role

select
    a.id as id_a,
    a.color as color_a,
    b.id as id_b,
    b.color as color_b
from palette_a a
inner join palette_b b using(color);

select
    a.id as id_a,
    a.color as color_a,
    b.id as id_b,
    b.color as color_b
from palette_a a
left outer join palette_b b using(color);

select
    a.id as id_a,
    a.color as color_a,
    b.id as id_b, b.color as color_b
from palette_a a
right outer join palette_b b using(color);

select
    a.id as id_a,
    a.color as color_a,
    b.id as id_b,
    b.color as color_b
from palette_a a
full outer join palette_b b using(color);

create or replace trigger customers_credit_trigger
    before insert of credit_limit
    on customers
declare
    current_day number;
begin
    current_day := extract(day from sysdate);

    if current_day between 28 and 31 then
        raise_application_error(-20100, 'Locked at the end of the month');
    end if;
end;

create or replace trigger customers_credit_limit_trigger
    before update of credit_limit
    on customers
    for each row
    when (new.credit_limit > 0)
begin
    if :new.credit_limit >= 2*:old.credit_limit then
        raise_application_error(-20101, 'The new credit cannot be more than double the old credit!');
    end if;
end;

create or replace trigger customers_audit_trigger
    after delete
    on customers
    for each row
declare
    transaction_type varchar2(10);
begin
    transaction_type := case
        when updating then 'update'
        when deleting then 'delete'
    end;

    insert into audits(
        table_name,
        transaction_name,
        by_user,
        transaction_date
    ) values (
        'customers',
        transaction_type,
        user,
        sysdate
    );
end;

create or replace trigger create_customer_trigger
    instead of insert on customers_and_contacts
    for each row
declare
    current_customer_id number;
begin
    insert into customers(
        name,
        address,
        website,
        credit_limit
    ) values (
        :new.name,
        :new.address,
        :new.website,
        :new.credit_limit
    ) returning customer_id into current_customer_id;

    insert into contacts(
        first_name,
        last_name,
        email,
        phone,
        customer_id
    ) values (
        :new.first_name,
        :new.last_name,
        :new.email,
        :new.phone,
        current_customer_id
    );
end;

referencing new as neu old as alt