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);

What is sub query Sub query is a query with in other query is known is Sub query Use if you want to get data from table1 by using value form table2 we will go for subquery not for joins. syntax. select * from table_name where....(select * from table_name where...(select * from table_name)...)... // Inner most query execute first Type of Sub Query Single row sub query - the sub query which is returning only one output value. example- get department name of employee 708. select dname from department where deptno = (select deptno from employee where empno=708) example-  get department detailsof employee 708. select * from department where deptno = (select deptno from employee where empno=708) Multi row sub query - the sub query which is returning more than one output value. example-  get department detailsof employee 708.709 select dname from department where dept_no IN (select deptno from employee where empno IN( 708, 709)); If we want

What is Data Integrity Constraints Data Integrity means apply some business rules on data fields . for Example-  Employee id must be unique and not null. Employee salary should not be zero etc. Employee Table e_id             e_name             e_sal        e_designation                        100             Rahul                25000       software developer          // (valid record) 0                 duke                30000         manager            // (invalid because E_id must be greater than 0) 101             Shweta            0                Designer           // (invalid because E_sal must be greater than 0)        So this are basic standard that we must have to Follow. so we have constraints for avoiding invalid insertion of invalid record. Types of Data Integrity Constraints- Keys Constraints Unique not null primary key Domain constraints Check Referential Integrity Constraints Foreign Key Unique Key constraints - its

What are Joins Joins are used when we have to fetch data from multiple table with multiple conditions on column. we can also do this by Set Operators but there are a limitation set operator always work on single column and single column data. Example- we want to display name of employee and department of employee. so we have to fetch data from employee table and department table . Types of JOINS- Cross joins/cartesian product. Equi joins/Inner joins. Self joins outer joins. What is cross join or cartesian product cross join concept is similar to cartesian product in mathematics. suppose we have to tables with this data. table_a = {x, y, z}; table_b = {10, 20}; if we apply cross join here then we will get data like this. table_a x table_b = {(x, 10), (x, 20), (y, 10), (y, 20), (z, 10), (z, 20)} What is Equi join if we want to get only valid combination of records then we use Equi joins because in cross join we are getting all combination of data. sup