KAMAL SQL Material

  CHAPTER 9-SQL * Loader v   SQL*Loader allows you to load data from an external file into a table in the database. It can parse many delimited file formats such as CSV, tab-delimited, and pipe-delimited.          v   Method 1 by using par file in command prompt v   We will load email data CSV file format into the  emails  table in the database. v   The following is the content of the  email.dat  file: 1, john.doe@example.com 2, jane.doe@example.com 3, peter.doe@example.com v   The contents of the control file ( email.ctl ) is as follows: load data into table emails insert fields terminated by "," ( email_id, email ) In the control file:               The  load data into table emails insert  instruct the SQL*Loader to load data into the emails table using the        ...

  CASE Function Ø   It can be used to perform if then else logic in SQL. Ø   These are two types 1)      Simple case expression 2)      Search case expression Ø   1. Simple case expression Select empno, ename, CASE deptno When 10 then ‘ACCOUNTING’ When 20 then ‘RESEARCH’ When 30 then ‘SALES’ When 40 then ‘OPERATION’ Else ‘others’ End Department_name from emp; Ø   2. Search case expression Select empno, ename, job, sal, Case When job=’MANAGER’ then ‘MGR’ When sal=3000 then ‘hipay’ When job=’SALESMAN’ then ‘sman’ Else ‘not specified’ End Employee_name from emp; Multiple inserts Ø   Insert all When deptno<20 then into d1 values (deptno, dname, loc) When deptno>20 and deptno<=30 then into d2 values (deptno, dname, loc) Else into d3 values (deptno, dname, loc) Select * from dept d;   Commit; DML MERGE statement à It is used to select rows from one o...

    CHAPTER 7-OLAP features in oracle à OLAP (Online analytical processing) à OLAP provides extra features to queries up on database. à The OLAP operations are perform enhancements like top n query, group (), cube, ROLLUP… ROLLUP à It is used with group by clause to display summarized data. à ROLLUP grouping produces subtotals and grand total. à Select deptno, sum (sal) from emp group by ROLLUP (deptno); Select job, sum (sal) from emp group by ROLLUP (job); à Passing multiple columns to ROLLUP Select deptno, job, sum (sal) from emp group by ROLLUP (deptno, job); 1 st it groups into deptno wise and then calculate totals of job wise in that deptno. CUBE à The result of a cube in a summery that stores subtotals of every combination of column or expression. à It is similar to ROLLUP but extension of ROLLUP. à Select deptno, job, sum (sal) from emp group by CUBE (deptno, job); It 1 st display grand total, job wise sub totals and then dept...