Hypertechblog

What is Sequence A Sequence is Database Object and its completely independent object. Use - Sequence are used to generate sequential integers values. and we can also create primary key values by using sequences. Example-   Suppose you are submitting bank application form with fields name, surname, dob, gender and so on.. when you click on submit records is stored in a table. but there is one more column in a table that is serial number of customer that is automatically maintained. Syntax- create sequence sequence_name; // its will start with 1 and incremented by 1 by default. 1,2,3 Now suppose we have to generate value from 100 and incremented by 10.\ create sequence emp_no Start With 100 Incremented By 10; if You want to know current value of the sequence- CURRVAL if You want to know next value of the sequence-  NEXTVAL Practice-  Step-1 - create a table create table employee( emp_id number, name varchar2(100), dob date, salary number); Step-2- creat

What is Synonyms Synonyms is a permanent alias name for table Types of synonyms- public synonyms - is used by all authenticate users. private synonyms - is only used by owner of the object. By Default a synonyms to be created as private synonyms. Syntax-  create synonyms synonyms_name For table_name; create synonyms eit For employee_info_table; // now you can use synonyms instead of table name like... select * from eit; Practice-  Step-1 - create a table create table employee( emp_id number, name varchar2(100), dob date, salary number); Step-2- create a synonyms create synonyms emp for employee // by default you can not create synonym you have to connect to DBA. Step-3- conn system // enter your password step-4- grant create synonyms to rahul (use your oracle name here) step-5 conn rahul/7533 Step-6- create synonyms again create synonyms emp for employee  Now apply some operation  select * from emp;

What is co related sub query Co-related sub query  - if a sub query depends on output generated by outer query. Use-     Get Department details which is having no employee right now.    Get Product details which sold to any single Customer. Co-related sub query Uses two operator EXISTS- get object details   if having something then use EXISTS operator between Inner and outer query. NOT EXISTS- get object details   if not having something then use NOT   EXISTS  operator between Inner and outer query. Syntax - select ..... from table_name where EXISTS/NOT EXISTS ( select..... from table_name2 where table1.pkcolum = table2fkcolum  ); Example get department details where having at least 1 employee select d.* from dept d where EXISTS (select e.empno from emp e where e.deptno =  d.deptno); get department details where not having at least 1 employee select d.* from dept d where NOT EXISTS (select e.empno from emp e where e.deptno =  d.deptno);