Sharing Knowledge by Mithun Ashok

Many of you may have some concern on Basics of DBMS and about the definitions of R-DBMS, H-DBMS and N-DBMS. Some of you might have studied the concepts little different than the one I have defined on my site, but nevertheless definitions and concepts(DBMS) on my site are simple and easy to understand which are explained at a very basic level. Am collating some links which will provide similar and more detailed definitions and insight on DBMS concepts. DBMS and its history -- http://en.wikipedia.org/wiki/Database_management_system Types of DBMS -- http://en.wikipedia.org/wiki/Types_of_DBMS                               http://sqlserver-guide.blogspot.com/2009/05/data-base-system-models-or-types-of.html Notes from Penn State University, USA -- http://www.personal.psu.edu/glh10/ist110/topic/topic07/topic07_06.html Flat File Database -- http://en.wikipedia.org/wiki/Flat_file_database Hierarchical DBMS -- http://en.wikipedia.org/wiki/Hierarchical_database_model Network DBMS -- ht

This video will take you through the steps to install Oracle 10g Release 2 on Windows 7 (Same steps work for Windows Vista). Usually if you install Oracle database on window 7/Vista you would get an error, Abnormal Program Termination. An internal error has occurred. Please provide the following files to Oracle Support. "Unknown" "Unknown" "Unknown" Follow the steps shown in this video to successfully install Oracle Database on Windows 7/Vista.

Following are the different scripts under COMMON_TOP/admin/scripts/$CONTEXT_NAME and their usages, 1. adapcctl.sh --> Apache Startup/Shutdown script 2. adfrmctl.sh --> Forms server Startup/Shutdown script 3. adrmsctl.sh --> Forms metric server Startup/Shutdown script 4. adfmcctl.sh --> Forms metric client Startup/Shutdown script 5. adcmctl.sh --> Concurrent manager Startup/Shutdown script 6. adautocfg.sh --> Autoconfig Startup/Shutdown script 7. adtcfctl.sh --> TCF Socket Startup/Shutdown script 8. adaprstctl.sh -->  Start / Stop Apache Web Server Listener in restricted mode 9. addisctl.sh --> Discoverer Startup/Shutdown script 10. adalnctl.sh --> Applications RPC Listener process Startup/Shutdown script 11. jtffmctl.sh --> JTF Fulfillment server Startup/Shutdown script 12. adstrtal.sh --> Start all of the scripts above using this script 13. adstpall.sh --> Stop all the services from the above script using this script

Oracle Forms Web CGI: Error detected Your request cannot be serviced at this time due to the following error: ERROR: Error getting least loaded host from Load Balancer Server. This error occurs if Oracle forms metric server does not get updated information on existing clients. In Forms metric server load balancing, always clients ping back the information about themselves and server just listens to it. In case for some reason none of the clients are alive or have not passed the information or if forms metric server is down then this error is observed. Sometimes if forms metric server is restarted without restarting client metric server even then this error is observed. You need to have the following information handy along with the sequence of services to be restarted. Always start the script in the order in which it is shown below. 1. adfrmctl.sh --> Forms server startup script 2. adfmsctl.sh --> Forms metric server startup script 3. adfmcctl.sh --> Forms met

RECYCLEBIN is one of the new and core feature of version 10G. This is similar to recyclebin on Mircosoft Windows. In MS Windows temporarily deleted files are kept in recyclebin and you can selectively restore the files you want. Similar to this option Oracle came up with RECYCLEBIN where in dropped tables are kept in RECYCLEBIN and you can selectively restore the tables. SQL> desc recyclebin  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  OBJECT_NAME                               NOT NULL VARCHAR2(30)  ORIGINAL_NAME                                      VARCHAR2(32)  OPERATION                                          VARCHAR2(9)  TYPE                                               VARCHAR2(25)  TS_NAME                                            VARCHAR2(30)  CREATETIME                                         VARCHAR2(19)  DROPTIME                                           VARCHAR2

Most of them have been asking me how do I display single quote in a select statement, here we go. Single quotes are used to represent literal strings so one should always know how to handle them, Oracle provides different ways of handling single quotes. 1. If you want to begin and end with single quote, SELECT '''hello how are you''' FROM DUAL; 'hello how are you' Notice that you have to use 3 single quotes in the beginning  and at the end. 2. If you want to display single quote in a using concatenation operator, SELECT 'hi, how' || '''' || 's mithun' FROM DUAL; hi, how's mithun Notice that you have to use 4 single quotes here. 3. If you want to display single quote in between, SELECT 'hey how''z dhruv doing' FROM DUAL; hey how'z dhruv doing Notice that you have to use 2 single quotes here.

Links to all the Presentations on SQL BASICS. Intorduction to RDBMS Basic Select Statement Restricting and Sorting Data Functions Subqueries Group Functions JOINS  Data Definition, Modification and Transaction Control Language   Codd Rules and Normalization PL/SQL Basics

Major goofup on one of my portal servers, suddenly started seeing the following while restarting webcache and webcacheadmin. Protocol of Origin Servers were changed. 13001 Virtual Host Map has a mixture of HTTP and HTTPS Protocol origin servers. All origin servers for a virtual host map must use the same protocol. This is caused due to virtual host map consists of origin servers with HTTP and HTTPS protocols. A virtual host mapping must consist of origin servers that use either the HTTP protocol exclusively or the HTTPS protocol exclusively. To resolve this go to Origin Servers definitions page of OracleAS Web Cache Manager, correct the mapping. 1. If you do not have access to Webcache Administrator or if its down, open webcache.xml under ORACLE_HOME/webcache/. 2. Search for Origin Servers definition by searching for the hostname of one of your origin servers. Make sure SSLENABLED="NONE" if your protocol is HTTP for all your Origin Servers. 3. Once this is done re

Errors in webcache event_log, [10/Aug/2011:16:29:55 +0530] [warning 11250] [ecid: 378117729099,0] ESI include fragment protocol does not match origin server protocol: Origin Server Protocol=http URL=https://xxxxxxx.in/portal/pls/portal/!PORTAL.wwpob_smd.has_privilege/selfcare/viewlans [10/Aug/2011:16:29:55 +0530] [warning 11250] [ecid: 1168391710083,0] ESI include fragment protocol does not match origin server protocol: Origin Server Protocol=http URL=https://xxxxxxxxx.in/portal/pls/portal/PORTAL.wwv_setting.render_css?p_lang_type=NOBIDI&p_subscriberid=1&p_styleid=1&p_siteid=0&p_rctx=P These messages are just warning and not errors. These messages are expected if you are using an external SSL server, meaning if SSL is implemented on the load balancer and not on your portal then these messages are expected in webcache event_log.

1. SQL> select empno, ename from emp where deptno=(select deptno from dept where dname='RESEARCH'); 2. SQL> select empno, ename from emp where deptno in (select deptno from dept where loc in ('NEW YORK','CHICAGO')); 3. SQL> select dname from dept where deptno in ( select deptno from emp where job ='ANALYST'); 4. SQL> select empno, ename, mgr from emp where mgr = (select empno from emp where ename='JONES'); 5. SQL> select empno, ename, mgr from emp where mgr = (select mgr from emp where ename='JONES') 6. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','ACCOUNTING')) 7. SQL> select empno, ename, job from emp where deptno in ( select deptno from dept where dname in ('SALES','RESEARCH')) and empno in (select mgr from emp) 8. SQL> select empno, ename from emp where empno not in ( select mgr from emp where mgr is not

1. SQL> SELECT empno, ename FROM emp WHERE Length(ename) = 4; 2. SQL> SELECT empno, ename, job FROM emp where Length(job)=7; 3. SQL> SELECT Length('qspiders') - Length(replace('qspiders','s','')) FROM dual; 4. SQL>  SELECT empno, ename, job FROM emp WHERE Instr(job,'MAN') >0; 5. SQL> SELECT empno, ename, job FROM emp WHERE Instr(job, 'MAN') =1; 6. SQL> SELECT empno, ename, job FROM emp WHERE (Length(ename) - Length(Replace(ename, 'L',''))) = 1; 7. SQL> SELECT * FROM dept WHERE Instr(dname,'O') > 0; 8. SQL> SELECT Concat(ename,' working as a ') || Concat(job, ' earns ') || Concat(sal, '  in ') || Conc at('dept ',deptno) AS text from emp; OR SQL> SELECT Concat(Concat(Concat(Concat(Concat(Concat(Concat(ename,' working as a '), job),' earns '), sal),'  in '),'dept '), deptno) AS text FROM emp; 9. SQL

Answers on Basic SELECT statement with Conditions: 1.  SQL> SELECT * FROM emp WHERE ename LIKE 'S%'; 2.  SQL> SELECT * FROM emp WHERE ename LIKE '_L%'; 3.  SQL> SELECT * FROM emp WHERE ename LIKE '%E_'; 4.  SQL> SELECT * FROM emp WHERE ename LIKE '____'; 5.  SQL> SELECT * FROM emp WHERE ename LIKE '%L%'; 6.  SQL> SELECT * FROM emp WHERE ename LIKE '_____%'; 7.  SQL> SELECT * FROM emp WHERE sal BETWEEN 2000 and 3000; 9.  SQL> SELECT * FROM emp WHERE mgr IS NULL OR COMM IS NULL; 10.  SQL> SELECT * FROM emp WHERE mgr IS NULL AND COMM IS NULL; 11.  SQL> SELECT * from emp WHERE job = 'MANAGER'; 12.  SQL> SELECT * from emp WHERE job = 'MANAGER' and deptno in (10,20); 13.  SQL> SELECT * FROM emp WHERE job IN ('CLERK','ANALYST') AND sal >= 1000 AND deptno IN (20,30); 14.  SQL> SELECT * FROM emp where deptno in (20,30) and comm IS NULL; 15.  SQL>

Questions on Basic SELECT statement: Syntax: SELECT *|{[DISTINCT] column|expression [ alias ],...} FROM table [WHERE condition(s) ]; 1. Display all the employees whose name starts with 'S' 2. List the employees name having letter 'L' as the second character 3. List the employees name having 'E' as last but one character 4. List the employees name having exactly 4 letters 5. List the employee whose name is having letter 'L' 6. List the employees name having atleast 5 characters 7. List employees earning between 2000 and 3000 9. List emp who do not have any reporting manager or commision is Null 10. List emp who do not have any reporting manager AND commision is Null 11. List only managers 12. List managers working in dept 10 and 20 13. List all the clerks and analysts with salary atleast 1000 in dept 20 and 30 14. List the employees in dept 20 and 30 who get no commision 15. List employees whose name starts with either 'A' or

(1) How do I list all of my tables? (2) Create one table from another table without copying the data from the first table. (3) How do I select from different user’s tables? (4) What is SQL? (5) What is a function in oracle? (6) What is the default format of date in Oracle? How can I change my default date format? (7) Difference between a)      ROLLBACK & SAVEPOINT b)      ROLLBACK & COMMIT c)       Truncate & Delete d)      Truncate & Drop e)      Delete & Drop f)       PK and (UNIQUE + Not Null) g)      ROUND & TRUNC h)      ROWID & ROWNUM i)         Alternate Key & Candidate Key j)        Simple Key & Composite Key k)      Stored Procedure and Function l)         Stored Procedure and Trigger m)    Equi-Join and Outer Join n)      CHAR & VARCHAR2 o)      CASE Statements & DECODE p)      UNION and UNION ALL 8) What is a view? What are its advantages? 9) What is a synonym? 10) What is PL/SQL? 11) What is an Index? Why it is useful?

BASIC SELECT Statement:  Syntax: SELECT *|{[DISTINCT] column | expression [ alias ],...} FROM table; 1. Display all rows and all columns of emp table 2. Display any 2 columns of emp table 3. Calculate annual salary with Quarterly commission of 500 4. Display distinct salaries of all the employees 5. Display output as following,       "Hello SMITH your salary is 5000" 6. Is the following statement correct,        SeleCT ENAME,deptno FROM emp;        select *,ename from emp;        select ename deptno from emp; CLICK HERE FOR ANSWERS       1. CLICK HERE FOR QUESTIONS ON BASIC SELECT 2. CLICK HERE FOR QUESTIONS ON BASIC SELECT WITH CONDITION 3. CLICK HERE FOR QUESTIONS FROM QSPIDERS 4. CLICK HERE FOR QUESTIONS ON FUNCTIONS 5. CLICK HERE FOR QUESTIONS ON SUBQUERIES 6. CLICK HERE FOR MORE QUESTIONS ON SQL

SUB Queries: 1. List the employees working in research department 2. List employees who are located in New York and Chicago 3. Display the department name in which ANALYSTS are working 4. Display employees who are reporting to JONES 5. Display all the employees who are reporting to Jones Manager 6. Display all the managers in SALES and ACCOUNTING department 7. Display all the employee names in Research and Sales Department who are having at least 1 person reporting to them 8. Display all employees who do not have any reportees 9. List employees who are having at least 2 reporting 10. List the department names which are having more than 5 employees 11. List department name having at-least 3 salesman 12. List employees from research and accounting having at-least 2 reporting 13. Display second max salary 14. Display 4th max salary 15. Display 5th max salary  -- Answer for nth Max Salary Co-Related Subqueries: 16. Write a query to get 4th max salary from EMP table 17. Wri

SQL Functions: Use only functions for data manupulation. 1. List employees whose name having 4 characters 2. List employees whose job is having 7 characters 3. Find out howmany times letter 'S' occurs in 'qspiders' 4. List the employees whose job is having last 3 characters as 'man' 5. List employees whose job is having first 3 characters as 'man' 6. Display all the names whose name is having exactly 1 'L' 7. Display dept names which are having letter 'O' 8. Display the output as shown below,    Scott working as a clerk earns 3000 in dept 20 9. Display employees who earn odd numbered salaries 10. Display number of employees getting NULL comission. 11. Display total sal and comm drawn by dept 30 12. Count number of clerks in dept 10 and 20 13. List Department wise total salary 14. List department wise total sal only if the total sal is > 3000 15. Display job wise total salary excluding dept 30 only if the total salary is