KAMAL SQL Material

  SET OPERATORS ·         A set operator combines the result of two or more queries into one result. ·         Queries containing set operator are called compound queries. ·         No of columns and datatypes of the columns being selected must be same as all select statements used in the query and names of the columns need not be same. ·         Types of set operators 1.       UNION 2.       UNION ALL 3.       INTERSECT 4.       MINUS 1. UNION: - it will be written all rows from multiple queries by eliminating duplicate rows if any. Ø   Select deptno, job from emp where deptno=10 UNION Select deptno, job from emp where deptno=20; 2. UNION ALL : - it will be written all rows from multiple queries by including duplicate ...

  SUB QUERIES Types of queries 1.Root query 2.Parent query or outer query or main query 3.Child query or inner query or sub query   1. Root query à The query which is not dependent on another query is called root query à Select ename, sal from emp where deptno=10;   2. Parent query à The query which depends on any another query is called Parent query à Select ename, sal from emp where deptno=(select deptno from dept where deptno=10); à In the above ex 1 st select stmt is Parent query. 3. Child query à The query which provides values to the parent query is called Child query. à The sub query in the where clause is called nested sub query, the sub query in the from clause is called inline view. à The sub query can be part of a column in the select stmt. Select e.ename, e.sal, e.deptno, (select dname from dept d where d.deptno=e.deptno) dname from emp e; à There is no limit the no of sub queries in the from clause, we can have ...

  JOINS Ø   It is used to join rows in one table to rows in another table. Ø   Join condition we will use in where clause Ø   If join involves more than one table oracle 1 st joins 1 st two tables based on condition and then compare the result with next table and so on. Ø   Using table alias names in join, ex: - Select * from emp e, dept d where e.deptno=d.deptno; Ø   Types of joins 1.EQUI JOIN 2.INNER JOIN 3.NON EQUI JOIN 4.SELF JOIN 5.CARTESIAN JOIN OR CROSS JOIN OR CARTESIAN PRODUCT 6.OUTER JOIN 7.NATURAL JOIN 1. EQUI JOIN Ø   Based on equality condition tables are joined, only matching records are displayed, to join tables both the tables must have at least one common col with same datatype and same values but col name may be different. Ø   Select * from emp e, dept d where e.deptno=d.deptno; 2. INNER JOIN Ø   Inner join will also work as same as equi join. Ø   We use on clause for inner join Ø...

  Integrity constraints or Data constraints Types 1.NOT NULL 2.UNIQUE 3.PRIMARY KEY 4.DEFAULT 5.CHECK 6.FORIEGN KEY OR REFERENCIAL INTEGRITY 1. NOT NULL : - Data must enter and duplicate values allowed Ø   Create table student (Sno number constraint Sno_NN NOT NULL); 2. UNIQUE : -no duplicate value allowed and null value allowed Column level Ø   Create table student (Sno number constraint Sno_UNQ UNIQUE); Table level Ø   Create table student (Sno number, Name varchar2(100), constraint Sno_NM_UNQ UNIQUE (Sno, Name)); 3. PRIMARY KEY : -It will not accept null, duplicate values. Only one Primary key allowed on one table Column level Ø   Create table student (Sno number constraint Sno_PK PRIMARY KEY); Table level Ø   Create table student (Sno number, Name varchar2(100), constraint Sno_NM_PK PRIMARY KEY (Sno, Name)); 4. DEFAULT : - Ø   Create table student (Sno number, J_Date date constraint JOD_DF DEFAULT sysdate);...

  Conversion function 1.Number functions note: - no of 9’s should be equal to given number Ø   Select to_char (1234,’9999D99’) from dual; it results as 1234.00 Ø   Select to_char (1234,’9.9EEEE’) from dual; it results as 1.2E+03, means scientific notation form of 1234 Ø   Select to_char (1234,’9G999’) from dual; it results 1,234 Ø   Select to_char (1234,’L9999’) from dual; it results $1234, means local currency Ø   Select to_char (-1234,’9999MI’) from dual; it results 1234-, displays ‘– ‘symbol last position Ø   Select to_char (-1234,’9999PR’) from dual; it results <1234>, displays -ve no between ‘<>’ Ø   Select to_char (10,’RN’), to_char (10,’rn’) from dual; it results X, x , means roman indicator of given number Ø   Select to_char (10,’S99’), to_char(-10,’S99’), to_char (10,’99S’), to_char(-10,’99S’) from dual; it results +10, -10, 10+, 10- , means sign indicator Ø   Select to_char (10,’XX’) from dual; i...