Frequently used queries in Oracle, SQL and PL/SQL

Frequently used queries in Oracle, SQL and PL/SQL SUBQUERIES & MULTIPLE SUBQUERIES =>1a. Select FIRST n records from a table     =>1b. Select LAST n records from a table     =>2a. Select * from emp where ROWNUM >= 1; --- what happens=>     =>2b. Select * from emp where ROWNUM > 1; --- what happens=>     =>2c. Select * from emp where ROWNUM > 0; --- what happens=>     =>2d. Select * from emp where ROWNUM = 1; --- what happens=>     =>2e. Select * from emp where ROWNUM > 5; --- what happens=>     =>2f. Select * from emp where ROWID is null; --- what happens=>     =>2g. Select * from emp where ROWID is not null; --- what happens=>     Decode()     =>3. To select ALTERNATE(EVEN NUMBERED) records from a table     =>3a. To select ALTERNATE(ODD NUMBERED) records from a table     =>4. If sal >= 2000, increment salary by 1000 else return sal from emp table     n AND nth MAXIMUM & MINIMUM SALARIES     =>5. Find the 3rd MAX salary in the emp table     =>6. Find the 3rd MIN salary in the emp table     =>7. Find 3 MAX salaries in the emp table     =>8. Find 3 MIN salaries in the emp table     =>9. Find the nth MAX date from emp table     =>10. Delete DUPLICATE records based on deptno     =>11. Select DISTINCT RECORDS from emp table     **** JOINS ****     OUTER JOIN     =>1. List employees' names and their managers' names (USING OUTER JOIN)     =>2. List Dept no., Dept name for all the departments in which there are no employees in the department     =>3. List the count of number of employees in each department (INTERVIEW QUESTION)     =>4a. Creating a table 'parts' with some constraints     =>4b. List all partnames and their alternate partnames     =>5. Details of employees along with their location (EQUI JOIN)     =>6. Details of ALLEN along with his location     =>7. List down the employees working in CHICAGO     =>8. List down details of employees getting sal < that of ALLEN     **** VIEWS ****     SINGLE TABLE VIEWS     .To create a view and insert     CREATE VIEWS WITH THE CHECK OPTIONS     To create a view with 'with check option' and updating     MULTI TABLE VIEWS    19 CREATE VIEW FROM A NON EXISTING TABLE USING 'FORCE' KEYWORD     CREATING VIEWS WITH COLUMNS RENAMED     ALTERING VIEWS     TO DROP A VIEW     **** SET OPERATORS ****     1. MINUS     2. UNION     3. UNION ALL     4. INTERSECT     **** INT QUESTIONS (QUERIES) ****     =>1. Produce the following report: dept no dept name avg_sal of dept     =>2. Produce the above report for sal > 2000     =>3. List dept no., Dept name for all the departments in which there are no employees in the department     =>4. List the count of number of employees in each department (refer under ?JOINS?-OUTER JOIN)     =>5. List ename,sal,new salary with 25% rise in existing sal for those employees who have present sal< 2000 and are from departments 10 & 20     =>6. List empno,ename,sal,manager's name, manager's sal for all employees who earn more than their manager     =>7. List the name and salary of all the employees working in all the departments who earn more salary than everybody in deptno=20     =>8. Copy only the structure(not records) from an existing table while creating a table     **** SOME QUESTIONS ****     =>1. Create referential integrity on emp table with respect to deptno on deptno of dept table     =>2. Create a check to ensure that only ename and job is entered in uppercase     =>3. List all employees who have joined before the 'president'     =>4. List all employees who have joined after 12th march 1982     =>5. Assuming hiredate as birthdate of an employee list hiredate and retirement of all employees     =>6. List empno,ename,'Manager' or 'Staff', sal with an employee appearing only once in the list. Print 'Manager' for a manager and 'Staff' for non-managers     =>7. List the empno,ename,sal of the first 5 highest paid employees. (Do not use the rownum or rowid functions. Use only std SQL features)     =>8. List the command to produce the following effect on the dept table     =>9. If the salary of Clerks does not fall in the range 0-1000 then display 'Not Valid' against the clerks' names     =>10. Find all departments which have more than 3 employees     SHUBA    24 1. Creation of a new table     2. Creation of a table from another table     3. Copy of structure only from another table     4. To insert at macro level into a table *     5. To append data from one table to another     6. Creation of constraints on a new table     7. Creation of contraints on a new table with contraint names     8. Creation of constraint with "REFERENCES" Table called DEPT should be existing where the column Called deptno with primary key constraint    25 9. Creation of constraint on an existing table     10. To drop a constraint on a table    25 11. To drop the main tables and referenced tables     12. Creation of table with DEFAULT values     13 To modify default clause on a column     14. Cascade constraints     15. To disable/enable a constraint on a table     16. Display of data with spaces in the alias names     17. To obtain the retirement date for any person     18. To obtain the manager's name for a person in the emp table (REFER JOINS-?OUTER JOIN)     19. To find the no. of months left in service     PSEUDOCOLUMNS    27 20. To select only the 1st row from any table     21. To display the hierarchial order of emp table     22. To select from table with subquieries     23. To select from table where the SQ returns more than one row     24. Select with more than one column     25. Select with Multiple sqs     26. To select 1st 'm' rows and last 'n' rows from a table     27. To select a range of rows from a table say starting from rownum = 's' and say 'r' rows     28a. Select with group by and having clause - where clause is also applicable     28b. Selecting avg(sal) individual average sal of various departments     28c. Conditional group by     29. Order by clause with ascending/descending     30. EQUI JOIN conditions i.e. '=' operator     31. OUTER JOIN conditions (REFER JOINS-OUTER JOINS)     32. SELF JOIN conditions applicable only for common tables     33. NON_EQUI join conditions     ****INDEXES****    30 Creating indexes on tables     create an UNIQUE INDEXES     To drop an index     How to disable an index     ****SEQUENCES ****     To create a sequence which is also a database object     To select from sequences     To alter the sequence     How to generate a sequence without using the sequences     If the sequence is for the very first instance then     To get the listing of sequences already created     **** CREATION OF CLUSTERS ****              **** SUBQUERIES & MULTIPLE SUBQUERIES **** ROWNUM =>1a. Select FIRST n records from a table     select * from emp where rownum <= &n; =>1b. Select LAST n records from a table     select * from emp minus select * from emp     where rownum <= (select count(*) - &n from emp); =>2a. Select * from emp where ROWNUM >= 1; --- what happens=>     ## returns all rows of table =>2b. Select * from emp where ROWNUM > 1; --- what happens=>     ## gives ?NO ROWS SELECTED? =>2c. Select * from emp where ROWNUM > 0; --- what happens=>     ## returns all rows of table =>2d. Select * from emp where ROWNUM = 1; --- what happens=>     ## Yes, this returns the 1st row =>2e. Select * from emp where ROWNUM > 5; --- what happens=>     ## gives ?NO ROWS SELECTED? =>2f. Select * from emp where ROWID is null; --- what happens=>     ## gives 'NO ROWS SELECTED' =>2g. Select * from emp where ROWID is not null; --- what happens=>     ## Returns all rows from the table. Decode() =>3. To select ALTERNATE(EVEN NUMBERED) records from a table     select * from emp where rowid in (select     decode(mod(rownum,2),0,rowid,                 null) from emp);     ## here in the DECODE function,     1st argument (mod(rownum,2)) is expression to check,     2nd argument (0) is a search value,     3rd argument (rowid) is to return the even rows if expr returns 0,     4th argument (null) is to return no rows if expr does not return 0. =>3a. To select ALTERNATE(ODD NUMBERED) records from a table     select * from emp where rowid in (select     decode(mod(rownum,2),0,null,             rowid) from emp); =>4. If sal >= 2000, increment salary by 1000                             else return sal from emp table     select ename,empno,job,sal,     decode(mod(sal,2000),sal,sal,             sal+1000) salinc from emp; n AND nth MAXIMUM & MINIMUM SALARIES =>5. Find the 3rd MAX salary in the emp table     select distinct sal from emp a     where 3 = (select count(distinct sal) from emp b     where a.sal <= b.sal);    -- sals in descending order     ## The count() function in the subquery is set to return the count of each distinct salaries         arranged in descending order (since <= is used here (i.e for max '<=' is used)). So when         it reaches the 3rd record (i.e count is 3) its corresponding sal will be returned finally.     for eg.,     emp a            emp b     4000            4000     3000            3000     2500            2500     1800            1800 =>6. Find the 3rd MIN salary in the emp table     select distinct sal from emp a     where 3 = (select count(distinct sal) from emp b     where a.sal >= b.sal);     -- sals in ascending order =>7. Find 3 MAX salaries in the emp table     select distinct sal from emp a     where 3 >= (select count(distinct sal) from emp b     where a.sal <= b.sal) order by a.sal desc; =>8. Find 3 MIN salaries in the emp table     select distinct sal from emp a     where 3 >= (select count(distinct sal) from emp b     where a.sal >= b.sal); =>9. Find the nth MAX date from emp table     select distinct hiredate from emp a     where &n = (select count(distinct to_char(hiredate,'ddd'))     from emp b where a.hiredate <= b.hiredate);             or     select distinct hiredate from emp a where     3 = (select count(distinct hiredate) from emp b where     a.hiredate <= b.hiredate);     ## here no need of converting date to char datatype as in 1st query =>10. Delete DUPLICATE records based on deptno     delete from emp a where rowid !=     (select max(rowid) from emp b where     a.deptno=b.deptno);     ## this query will retain all the records which are having unique deptno that are having maximum rowid and delete the rest duplicate ones ie., which are having repeated deptnos. =>11. Select DISTINCT RECORDS from emp table     select * from emp a where     rowid = (select max(rowid) from emp b where     a.empno=b.empno);     ## here we can have 'in' also instead of '='                  **** JOINS **** OUTER JOIN =>1. List employees' names and their managers' names (USING OUTER JOIN)     select lo.ename "EMP NAMES", hi.ename "MGR NAMES" from     emp lo, emp hi where lo.mgr = hi.empno(+);     ## where (we can find the empno against each manager) each lo.mgr is a manager of a hi.empno             or     select a.empno,a.ename,a.mgr,b.ename from emp a, emp b where     b.ename = (select b.ename from emp b where a.mgr = b.empno) ;             or     select a.empno,a.ename,a.mgr,b.ename from emp a, emp b where     where a.mgr = b.empno ; =>2. List Dept no., Dept name for all the departments in which there are no employees in the department (REPEATED QUESTION NO(3) BELOW UNDER ?INTERVIEW QUESTIONS?)     select empno,ename,b.deptno,dname from emp a, dept b     where a.deptno(+) = b.deptno and empno is null;    -- (1)     ## (1) gives empno and ename as null also                 or                      select * from dept where deptno not in (select deptno from emp); -- (2)                 or     select * from dept a where     not exists (select * from emp b where     a.deptno = b.deptno); -- (3)     ## (2) & (3) GIVE THE SAME RESULT as (1) =>3. List the count of number of employees in each department (REPEATED QUESTION NO(4) BELOW UNDER ?INTERVIEW QUESTIONS?)     select count(EMPNO), b.deptno, dname from emp a, dept b     where a.deptno(+)=b.deptno     group by b.deptno,dname;     ## NOTE : Here if we give count(*) it counts as 1 for deptno = 40. So we must give     count(empno) only for our required correct output. =>4a. Creating a table 'parts' with some constraints     create table parts(     partno number(2),     partname char(15),     alt_partno number(2) references parts(partno),     constraint pky primary key(partno));     ## 1st record insertion shd be null for alt_partno     eg. insert into parts values (09,'refil',null); =>4b. List all partnames and their alternate partnames     select a.partname "PARTNAME" ,b.partname "ALT PARTNAME" from     parts a, parts b where     b.partno(+) = a.alt_partno =>5. Details of employees along with their location (EQUI JOIN)     select empno,ename,a.deptno,b.dname,b.loc     from emp a, dept b where a.deptno = b.deptno; =>6. Details of ALLEN along with his location     select empno,ename,a.deptno,b.dname,b.loc     from emp a, dept b where a.deptno=b.deptno     and ename = 'ALLEN'; =>7. List down the employees working in CHICAGO     select empno,ename,loc from     emp a, dept b where a.deptno=b.deptno and     loc = 'CHICAGO'; =>8. List down details of employees getting sal < that of ALLEN     select b.empno, b.ename,b.deptno,b.sal     from emp a, emp b where a.ename = 'ALLEN'     and b.sal <= a.sal;             or     select empno,ename,deptno,sal from emp     where sal < (select sal from     emp where ename = 'ALLEN');                 **** VIEWS ****          ## information on table views can be obtained from the table     called USER_VIEWS ## SINGLE TABLE VIEWS     SQL> create view empv1 as select * from emp; .To create a view and insert - ( To insert a record in a view, the view should consist of the NOT NULL (MANDATORY) column of the base table)     SQL>create view emp_view as select ename,job,sal from         emp where sal <1500;         ## view created     SQL>insert into emp_view values('xxx','clerk',1000);     SQL> insert into emp_view values('xxx','clerk',1000)     2 *     3 ERROR at line 1:     4 ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert CREATE VIEWS WITH THE CHECK OPTIONS     SQL> create or replace view empview as select         sal,empno,ename from emp where sal>1500;     ## in the above case no record can be inserted into the table through the view EMPVIEW if the value of sal<1500, however the record can be inserted into the table directly.     SQL> create or replace view empview sal,empno,ename,dname     as select sal,empno,ename,dname from emp a, dept b where     a.deptno=b.deptno and sal >1500 with check option;     ## information on the check option can be obtained from the table called ALL_VIEWS To create a view with 'with check option' and updating     SQL>create view emp_view as select ename,job,sal         from emp where sal <1500 with check option;         ## View created.     SQL>update emp_view set sal=sal + 1000;         update emp_view set sal=sal + 1000                 *         ERROR at line 1:         ORA-01402: view WITH CHECK OPTION where-clause violation MULTI TABLE VIEWS     SQL>create or replace view empview         as select empno,ename,a.deptno,dname from         emp a, dept b where a.deptno=b.deptno;     ## in complex table views no update,insert, etc.(i.e. DML commands) will be applicable     SQL>create view emp_dept_view as         select empno,ename,a.deptno,dname from         emp a,dept b where a.deptno=b.deptno;     SQL>create view dept_grp_view as         select deptno,max(sal) "max sal" from         emp group by deptno; CREATE VIEW FROM A NON EXISTING TABLE USING 'FORCE' KEYWORD     SQL> create FORCE view v1 as select * from aaa;         Warning: View created with compilation errors. CREATING VIEWS WITH COLUMNS RENAMED     create or replace view empview (emp_number,emp_name,salary,dept_name)     as select empno,ename,sal,dname from emp a, dept b where     a.deptno=b.deptno; ALTERING VIEWS     alter view empview compile;     ## this command is useful if the table was altered using the command "alter table ...." , the command now regenerates the view ### TO DROP A VIEW     drop view empview ;                 **** SET OPERATORS **** 1. MINUS     SQL> select job from emp where deptno=20 MINUS         select job from emp where deptno=30;             or     SQL> select distinct                         job from emp where deptno=20 and         job not in (select job from emp where deptno=30);         ## Here if we don't give distinct in subquery then repeated jobs may be displayed if job             is repeated in the dept=20. 2. UNION     SQL> select job from emp where deptno=10 UNION         select job from emp where deptno=20;             or     SQL> select distinct job from emp where deptno in(10,20);             or     SQL> select distinct job from emp where deptno=10 or deptno=20; 3. UNION ALL     SQL> select job from emp where deptno=10 UNION ALL         select job from emp where deptno=20;             or     SQL> select job from emp where deptno in(10,20); 4. INTERSECT     SQL> select job from emp where deptno=20 INTERSECT         select job from emp where deptno=30;             or     SQL> select distinct job from emp where deptno=20         and job in (select distinct job from emp where deptno=30);             **** INT QUESTIONS (QUERIES) **** =>1. Produce the following report:                                dept no        dept name        avg_sal of dept     select b.deptno "dept no",dname "dept name",avg(sal) "avg sal of dept"     from emp a, dept b where a.deptno = b.deptno     group by b.deptno, dname; =>2. Produce the above report for sal > 2000     select b.deptno "dept no",dname "dept name",avg(sal) "avg sal of dept"     from emp a, dept b where a.deptno = b.deptno     group by b.deptno, dname having avg(sal) > 2000; =>3. List dept no., Dept name for all the departments in which there are no employees in the department (REPEATED QUESTION NO.(2) AT TOP UNDER ?JOINS?)     select empno,ename,b.deptno,dname from emp a, dept b     where a.deptno(+) = b.deptno and empno is null;    -- (1)     ## (1) gives empno and ename as null also             or     select * from dept where deptno not in (select deptno from emp); -- (2)                 or     select * from dept a where     not exists (select * from emp b where     a.deptno = b.deptno); -- (3)     ## (2) & (3) GIVE THE SAME RESULT as (1) =>4. List the count of number of employees in each department (REPEATED QUESTION NO(3) AT TOP UNDER ?JOINS?)     select count(EMPNO), b.deptno, dname from emp a, dept b     where a.deptno(+)=b.deptno     group by b.deptno,dname;     ## NOTE : Here if we give count(*) it counts as 1 for deptno = 40. So we must give count(empno) only for our required correct output. =>5. List ename,sal,new salary with 25% rise in existing sal for those employees who have present sal< 2000 and are from departments 10 & 20     select ename, deptno, sal, sal+(sal*.25) " new sal" from emp     where sal < 2000 and deptno in (10,20); =>6. List empno,ename,sal,manager's name, manager's sal for all employees who earn more than their manager     select a.empno empno, a.ename name, a.sal "emp sal",     b.ename "mgr name", b.sal "mgr sal" from emp a, emp b     where a.mgr = b.empno and a.sal > b.sal; =>7. List the name and salary of all the employees working in all the departments who earn more salary than everybody in deptno=20      select ename, deptno, sal from emp where     sal > (select max(sal) from emp where deptno=30); =>8. Copy only the structure(not records) from an existing table while creating a table     create table empp as select * from emp where 1= 2;     ## Here 1=2 is a false condition hence copies only the structure of emp to empp table(but not the records)             **** SOME QUESTIONS **** =>1. Create referential integrity on emp table with respect to deptno on deptno of dept table      alter table emp add (constraint emp_fk foreign key(deptno)     references dept(deptno)); =>2. Create a check to ensure that only ename and job is entered in uppercase      alter table emp1 add (constraint nm_upp check(ename = upper(ename)),     constraint job_upp check(job = upper(job))); =>3. List all employees who have joined before the 'president'      select empno, ename, hiredate from emp1 where hiredate < (select hiredate     from emp1 where job = 'PRESIDENT'); =>4. List all employees who have joined after 12th march 1982      select * from emp where hiredate > '12-MAR-82'; =>5. Assuming hiredate as birthdate of an employee list hiredate and retirement of all employees      select empno,ename,hiredate,     to_number(to_char(hiredate,'yy')) + 58 "RETIREMENT DATE" from     emp; /* not correct */ =>6. List empno,ename,'Manager' or 'Staff', sal with an employee appearing only once in the list. Print 'Manager' for a manager and 'Staff' for non-managers      select empno,ename,sal, decode(job,'MANAGER','Manager','Staff')     from emp ; =>7. List the empno,ename,sal of the first 5 highest paid employees. (Do not use the rownum or rowid functions. Use only std SQL features)      select max(sal) from emp where sal <     (select max(sal) from emp ) <     (select max(sal) from emp where sal <     (select max(sal) from emp where sal <     (select max(sal) from emp )))); =>8. List the command to produce the following effect on the dept table     before        after     deptno        deptno     10        10     12        20     17        20     22        30     26        30     30        30     32        40     update dept set deptno = round(deptno+5,-1) where mod(deptno,10)!=0; =>9. If the salary of Clerks does not fall in the range 0-1000 then display 'Not Valid' against the clerks' names      select empno,ename,sal,decode(sign(sal), sign(1000-sal),'Within Range','Not Valid')     from emp where job = 'CLERK';     ## select empno,sal,sign(sal),sign(sal-1000) from emp =>10. Find all departments which have more than 3 employees     select deptno,count(empno) from emp     group by deptno having count(empno) >= 3; ******************************************************************************** ******************************************************************************** 1. Creation of a new table      Create table dept (dept number (2), dname char(20)); 2. Creation of a table from another table      create table emp1 as select * from emp; 3. Copy of structure only from another table     create table emp1 as select * from emp where 1=2     ## (1=2 is a false condition hence only the stucture is copied) 4. To insert at macro level into a table *      insert into emply values (&no,'&name',&dept,'&job','&dob','&jn_dt',&pay); 5. To append data from one table to another      insert into emp1 select * from emp; 6. Creation of constraints on a new table     create table emp1     (empno number(5) primary key,     ename varcha2(20) not null,     sal number(10,2) check (sal>1000),     phone number(6) unique);     ## here the constraint name is alloted by oracle 7. Creation of contraints on a new table with contraint names      create table emp1 (empno number(5) constraint pk primary key,     ename varcha2(20) constraint nt not null,     sal number(10,2) constraint ch check (sal>1000),     phone number(6) constraint un unique);     ## if constraint name is not provided by the owner the system by default assigns a contraint name in the format "SYS_C-----" where ----- is a continuous number 8. Creation of constraint with "REFERENCES" Table called DEPT should be existing where the column Called deptno with primary key constraint      create table emp1     (empno number(5) constraint pk primary key,     ename varcha2(20) constraint nt not null,     deptno NUMBER(2) references dept(deptno) [on delete cascade],     sal number(10,2) constraint ch check (sal>1000),     sex char constraint ck (sex in ('M','F')),     phone number(6) constraint un unique);     ## the syntax in square brackets is optional, i.e deleting any row in DEPT TABLE will delete all related rows in the dependent table 9. Creation of constraint on an existing table (egs)     SQL>alter table emp add (constraint FK foreign key (deptno)         references dept(deptno) [on delete cascade]);     SQL>alter table emp add constraint pk primary key(empno);     SQL>alter table emp1 add (constraint pk primary key(empno),         constraint fk1 foreign key (mgr) references emp1(empno)         on delete cascade);     SQL> alter table emp add constraint pk primary key(empno,phone)         ## pk creates a composite primary key on two fields     SQL>delete from emp1 where empno = (select empno from emp1         where ename = 'KING'); 10. To drop a constraint on a table      alter table dept1 drop constraint pk;     ## refer constraint name in table called USER_CONSTRAINTS 11. To drop the main tables, its constraints with its referencing tables     drop table dept cascade constraints ;     ## drops all referential integrity constraints referring to the keys in the dropped table also , does     drop the dependent tables. 12. Creation of table with DEFAULT values      create table emp1     (empno number(5) primary key,     ename varcha2(20) not null,     sal number(10,2) default 3500,     phone number(6) unique);     ## Strictly speaking DEFAULT is not a constraint since this information will not be available in the user_constraints table     ## DEFAULT clause is applicable to tables with more than one column only     ##To find out DEFAULT setup values on a table, check in table called "USER_TABS_COLUMNS?     ## to find the information on only primary key and foreign keys check in table called "USER_CONS_COLUMNS " 13 To modify default clause on a column     alter table emp modify (sal default null); 14. Cascade constraints - to only drop constraints and its references that are related in other dependent tables.      alter table dept drop constraint pk cascade;     ## here any table referencing this table DEPT for pk will drop automatically the constraint on all dependent tables 15. To disable/enable a constraint on a table     SQL>alter table dept disable constraint pk ;         ## here pk is the primary key of the column     SQL> alter table dept enable constraint nt;         ## here nt is the not null of the column 16. Display of data with spaces in the alias names     select deptno "Dept no " from dept1; 17. To obtain the retirement date for any person      select add_months(last_day('&dob'),58*12)     "Retirement Date" from dual;/* not correct*/ select to_char(hiredate,'dd-mon-yyyy'), to_char(add_months(last_day(hiredate),58*12),'dd-mon-yyyy') from emp; ##the above query works correctly dealing with year 2000. 18. To obtain the manager's name for a person in the emp table (REFER JOINS-?OUTER JOIN)      select a.empno,a.ename,a.mgr,b.ename from emp a, emp b where     b.ename = (select b.ename from emp b where a.mgr = b.empno) ;             or     select a.empno,a.ename,a.mgr,b.ename from emp a, emp b where     where a.mgr = b.empno ; 19. To find the no. of months left in service      select months_between(add_months(last_day('16-MAY-64'),58*12),     to_date(sysdate)) "No.Of months" from dual;             or      select months_between(to_date('31-MAY-2022','DD-MON-YYYY'),     to_date(sysdate)) from dual; ## here in to_date(sysdate) no need to give ?to_date? PSEUDOCOLUMNS 20. To select only the 1st row from any table     select * from emp where rownum =1;     ## only rownum operates with the literal 1 for the 1st row for eg. "where rownum = 5" is wrong 21. To display the hierarchial order of emp table      select level, ename EMPNAME, job     from emp start with job = 'PRESIDENT' connect by prior empno=mgr; 22. To select from table with subquieries     select * from emp where ename =     (select ename from emp where empno = '7566');     ## here the subquery returns only one value ( or row) if the SQ returns more than one row then an error message occurs 23. To select from table where the SQ returns more than one row     SQL>select * from emp where sal in     (select sal from emp where deptno = 20);     ## for multiple rows returned the SQ must be preceded with the clause "IN" or "NOT IN" or "ANY" or "ALL"     ## the "=" clause will yield error     SQL> select * from emp where exists (select deptno from deptno)     ## if the SQ following 'exists' returns atleast one row then the main query returns all the rows     SQL> select dept.deptno,dept.dname from dept where     dept.deptno = (select emp.deptno,dept.dname from emp,dept     where emp.deptno!=dept.deptno); 24. Select with more than one column      select * from emp where (job,deptno) in     (select job,deptno from emp where sal>1000); 25. Select with Multiple sqs      select * from emp where empno in     (select empno from emp where deptno =     (select deptno from dept where dname='SALES')); 26. To select 1st 'm' rows and last 'n' rows from a table      select * from emp where rownum <= &m union     (select * from emp minus     (select * from emp where rownum <=     (select count(*) - &n from emp))); 27. To select a range of rows from a table say starting from rownum = 's' and say 'r' rows =>=>=>    select * from emp where rownum <=(s+r) intersect     (select * from emp minus     (select * from emp where rownum <=     (select count(*)-(s+r) from emp))) ;     ## say rownum 5 to 8      select * from emp where rownum <=8 intersect     (select * from emp minus     (select * from emp where rownum <=     (select count(*)-13 from emp))) ; 28a. Select with group by and having clause - where clause is also applicable      select sum(sal),deptno,nvl(sum(comm),0) from emp1 group by deptno ; 28b. Selecting avg(sal) individual average sal of various departments      select a.deptno,dname,avg(sal) from emp a, dept b     where a.deptno=b.deptno     group by a.deptno,dname; 28c. Conditional group by      select a.deptno,dname,avg(sal) from emp a, dept b     where a.deptno=b.deptno     group by a.deptno,dname having avg(sal) >2000;     ## in a group by clause always the "selected columns" should be listed in the columns following the group by clause 29. Order by clause with ascending/descending     select * from emp where sal > 2000 order by sal desc, ename asc; 30. EQUI JOIN conditions i.e. '=' operator      select empno,ename,dname from emp,dept where emp.deptno=dept.deptno; 31. OUTER JOIN conditions (REFER JOINS-OUTER JOINS)     ##to select all departments     select b.deptno,dname,empno,ename from dept b,emp a where     b.deptno=a.deptno(+) and b.deptno in (select b.deptno from dept);     ##to select only departments where no employees are present      select b.deptno,dname,empno,ename from dept b,emp a where     b.deptno=a.deptno(+) and b.deptno in (select b.deptno from dept)     and empno is null;                 or     select deptno,dname from dept where deptno not in (select deptno     from emp);                 or     select deptno,dname from dept a where not exists     (select * from emp b where a.deptno = b.deptno) 32. SELF JOIN conditions applicable only for common tables     SQL> select a.ename,a.job, b.ename from emp a, emp b where a.empno=b.empno;     SQL> select a.empno "Emp No",a.ename "Emp Name", a.mgr "Mgr No",     b.ename "Mgr Name" from emp a, emp b where     b.ename = (select ename from emp where a.mgr=empno); 33. NON_EQUI join conditions     select sal,empno from emp,dept where dept.deptno=emp.deptno     and sal between 1000 and 3000; ****INDEXES****     ## information on indexes is available in the table called USER_INDEXES Creating indexes on tables     create index empndx on emp (empno asc, sal desc, mgr desc);     ## Only a maximum of 16 columns can be included in the index column this is same as primary key where only 16 columns can be included at a time create an UNIQUE INDEXES      create unique index empuniq on emp (empno,mgr,sal); To drop an index     drop index empuniq; How to disable an index     ## actually no index can be disabled, only an index usage can be avoided through an SQL statement     ## for if there is an index table called empndx on the empno then the following SQL fools the index     select * from emp where empno + 1 - 1 = 7902;     ## here actually there is no index by name empno+1-1 and hence can be avoided     ****SEQUENCES **** To create a sequence which is also a database object     ## always the 2 psuedocolumns - nextval & currval are affiliated with sequences     create sequence empno     increment by 2     start with 100     maxvalue 500     minvalue 150 - gives error     cycle     cache 25; -- here 25 is the ready memory of numbers     ## START WITH <value> cannot be less than MINVALUE To select from sequences     ## always once the sequence is created to know the current val first use "select empno.nextval from dual" and then use the command "select empno.currval/empno.nextval from dual"     SQL> select empno.nextval from dual;     SQL> select empno.currval from dual; To alter the sequence     alter sequence empno increment by ..... How to generate a sequence without using the sequences      select max(empno)+1 from emp; If the sequence is for the very first instance then     select nvl(max(empno),0)+1 from dual     update emp set empno = nvl(max(empno),0)+1 where     ## information on the sequences can be obtained from the table called USER_SEQUENCES To get the listing of sequences already created     select * from cat; -- catalogue table     ## synonym table name for cat is user_catalog;     **** CREATION OF CLUSTERS ****     SQL> create cluster clust (deptno number(2));     SQL> alter cluster clust (deptno number(2));     SQL> alter table emp1 (empno number(5), ename char(10), deptno number(2))         cluster clust(deptno);     SQL> Create table emp2         (empno number(4) primary key,         ename varchar2(20) constraint ch1 check(ename = upper(ename)),         mgr number(4) references emp2(empno),         comm number(7,2),         job varchar2(15) constraint ch2 check(job = upper(job)),         deptno number(2) not null); =>=>=> delete from emp where rowid = (select * from emp minus (select * from emp where rownum <= (select max(rownum)-14 from emp))) GENERAL QUESTIONS What is Oracle => What are the advantages of Oracle => What are potential disadvantages of Oracle => What is SQL => What is PL/SQL => What is Procedural Option => What products are available from Oracle => What Public Domain interfaces are there => What third party interfaces are available => How portable are Oracle applications to other RDBMS => What Query Optimisers are there => Is there an anonymous FTP site for Oracle stuff => What mail lists are there => What bulletin boards are there => What news groups are there => How does Oracle compare to ... => SQL QUESTIONS How can I avoid a divide by zero error => Can I update tables from other tables=> Can I remove duplicate rows=> Can I update using a view => Are views automatically updated when I update base tables => Should we use complex views that cruel performance => Can I implement tree structured queries => How can I get information on the row based on group information=> How can I get a name for a temporary table that will not clash => How can I find out about what tables, columns and indices there are => Is there a formatter for SQL statements => What is the DUAL table => What is the difference between CHAR and VARCHAR => What is ROWNUM good for => How do I get a top ten => How can I control the rollback segment I use => How can I order a union => How can I rename a column => Who are SCOTT/TIGER, SYSTEM and SYS => Who do various access methods compare => What are clusters => How can I update a big table without blowing rollback segments => Why don?t I get records for the date I want => SQL*PLUS QUESTIONS How can I control the startup configuration of SQL*Plus => Can I get a column value into a substitution variable => How can I change the (hated default) editor to my favorite => What is the difference between & and && => What is the difference between ?host? and ?!? => Why can?t I use a table name in a substitution variable => How can I see all of a LONG => How can I force a column to begin on the left of the page => Can I alias SQL commands => Can I escape significant punctuation marks => SQL*FORMS 3 QUESTIONS How can I get a list of values from a hard coded list => How can I get find to look at description with list of values => Can I edit SQL*Forms code with my text editor => Can I edit SQL*Forms code by updating the database => Why can?t I see data in a control field => Why is my terminal scrambled in a user exit => What happens to LONGs => What are user-written form level functions => How can I use regular expressions for field validation => What is a user-exit => How can I call a popup window for field validation => SQL*FORMS 4 QUESTIONS What new features can be expected in forms 4 generator from CASE => What new features can be expected in forms 4 => PRO*C QUESTIONS Why are my C variables overwritten => Can I use C preprocessor definitions for VARCHAR size => What can I do about ?line too long? errors with version control=> Why do my compiles crash or weird things happen => How do I use OPS$login => CASE QUESTIONS Can CASE generate forms with owner prepended to table names => Can CASE generate V7 databases => UNIX QUESTIONS Can I create a compressed export on the fly without needing to have the space for both the export file and the compressed file=> How can I prevent trailing spaces in a spooled report => How can I get an environmental variable into SQL*Plus variables=> Can I pipe stuff through SQL*Plus => Why do Pro*C compiles or programs crash on my Sun => How can I find a lost Oracle export file => How can I tell make about SQL*Forms => MISC QUESTIONS How can I alter table storage parameters from an export file=> What makes the best Oracle server for a network => How can I implement version control => What books are available about Oracle =>