Sharing Knowledge by Mithun Ashok

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 restart OracleA…

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

9. select…

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> SELECT empno, ename…

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> SELECT * FROM emp WHERE ename LIKE (…

Questions on Basic SELECT statement:

Syntax:
SELECT*|{[DISTINCT] column|expression [alias],...} FROMtable [WHEREcondition(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 'S' in dept 20
16. Li…

(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?
12) What is PL/SQL?
13) Mention some features of Oracle 9i database.
14) Mention some features of Oracle 10g database.
15) What is the meaning …

BASIC SELECT Statement:
 Syntax:
SELECT*|{[DISTINCT] column|expression [alias],...} FROMtable;

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. Write a query to get 2nd…

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 > 5000
16. Dis…