Nurhak Kaya's Software Development Blog

create table students (   student_id number not null,   student_name varchar2(50),   student_surname varchar2(50) not null,   section_id number,   final_grade number,   letter_grade char(1),   constraint students_pk primary key(student_id) ); insert into students values (   1,   'Nurhak',   'Kaya',   89,   85,   'D' ); declare   v_student_id number:=1;   v_section_id number:=89;   v_final_grade number;   v_letter_grade char(1); begin   select final_grade   into v_final_grade   from students   where student_id=v_student_id   and section_id=v_section_id;     case       when v_final_grade>=90 then v_letter_grade:='A';       when v_final_grade>=80 then v_letter_grade:='B';       when v_final_grade>=70 then v_letter_grade:='C';       when v_final_grade>=60 then v_letter_grade:='D';       else v_letter_grade:='F';     end case;     --check result     dbms_output.put_line(

declare   v_counter number; begin   v_counter := 0;   for i in 1..20 loop       v_counter:=v_counter+1;       dbms_output.put_line(i || '. value is: '||v_counter);       if v_counter=10 then             commit;             v_counter:=0;           end if;     end loop; end;

insert into employees (   employee_id,   first_name,   last_name,   email,   hire_date,   job_id ) values (   seq_employee_id.nextval,   'Nurhak',   'Kaya',   '[email protected]',   sysdate,   'IT_PROG' ); savepoint point1; insert into employees (   employee_id,   first_name,   last_name,   email,   hire_date,   job_id ) values (   seq_employee_id.nextval,   'Nurhak',   'Kaya',   '[email protected]',   sysdate,   'IT_PROG' ); savepoint point2; insert into employees (   employee_id,   first_name,   last_name,   email,   hire_date,   job_id ) values (   seq_employee_id.nextval,   'Nurhak3',   'Kaya',   '[email protected]',   sysdate,   'IT_PROG' ); savepoint point3; --ROLLBACK!! select * from employees where email='[email protected]'; --Data has been found! rollback to savepoint point2; --'Rollback to savepoint point2' co

It's been a year since I have started to post my first blog and today it's my blog's birthday! That has been an amazing experience to be a blog writer! I've learnt a lot by blogging and that feels great to have followers from all over the world! Thank you to everyone who's been with me for this past year! You're always welcome to follow my blog in the coming years!

select concat(concat(first_name, ' '), last_name) as "Name_Surname" from employees where employee_id = 152;   create or replace force view vew_emp_full_name as select concat(concat(first_name, ' '), last_name) as "Name_Surname", manager_id+department_id as "Total" from employees;

create or replace force view "xyz"."vew_test_table_name" as select * from test_table_name; alter view "xyz"."vew_test_table_name" add constraint pk_vew_test_table_name_id primary key (id) disable;

select * from personel; create table personel_yeni (  PERSONEL_ID   NUMBER(10,0) NOT NULL ENABLE,  PERSONEL_NAME VARCHAR2(20 BYTE),  OFFICE_ID     NUMBER(10,0) ); select * from personel_yeni; insert into personel_yeni values (   19,   'Some data',   9 ); update personel_yeni y set   personel_name=   (     select personel_name     from personel e     where e.personel_id=y.personel_id   ),   office_id=   (     select office_id     from personel e     where e.personel_id=y.personel_id   );

select * from user_triggers where table_name='my_table_name'; select * from all_triggers where table_name='my_other_table_name';

create table Office (   office_id numeric(10) not null,   building_name varchar2(20),   constraint u_office unique(building_name),   constraint office_pk primary key(office_id) ); create table Personel (   personel_id number(10) not null,   personel_name varchar2(20),   office_id number(10),   constraint u_personel unique(office_id), --with this constraint each personel will have just one office id.   constraint personel_pk primary key(personel_id) ); --Here in the blow code block, as you can see in personel_detail table, personel_id is a PK and also it is a FK that references personel table. --In this condition we can't insert any personel data in to personel detail table which does not have any reference in personel table. For example if there is a personel with personel_id=1 then we can insert one data with this same personel_id-which is 1- into personel_detail table but we can't do the same thing with the personel_id=2, because there is no personel with