Some Useful Queries for Beginners

SELECT * FROM EMP;
SELECT EMPNO, ENAME, SAL FROM EMP;
SELECT * FROM EMP WHERE DEPTNO=20;
SELECT * FROM EMP WHERE HIREDATE = ’02-APR-81’;
SELECT * FROM EMP WHERE SAL = 5000;
SELECT * FROM EMP WHERE HIREDATE <= ’23-MAY-86’;
SELECT * FROM EMP WHERE SAL >= 1500;
SELECT * FROM EMP WHERE SAL ! = 3000;
SELECT * FROM EMP WHERE DEPTNO <> 20;
SELECT * FROM EMP WHERE HIREDATE BETWEEN ’01-JAN-81’ AND ’31-
DEC-81’;
SELECT * FROM EMP WHERE DEPTNO IN (20, 30);
SELECT * FROM EMP WHERE JOB IN (‘ANALYST’,’SALESMAN’);
SELECT * FROM EMP WHERE HIREDATE IN (’20-FEB-81’,’03-DEC-81’);
SELECT * FROM EMP WHERE ENAME LIKE ‘%M%’;
SELECT * FROM EMP WHERE JOB LIKE ‘M%ER’;
SELECT * FROM EMP WHERE ENAME LIKE ‘__R%’;
SELECT * FROM EMP WHERE ENAME LIKE ‘%\_%’ ESCAPE ‘\’;
SELECT * FROM EMP WHERE COMM IS NULL;
SELECT * FROM EMP WHERE JOB = ‘MANAGER’ AND HIREDATE < ’10-JUN-
81’;
SELECT * FROM EMP WHERE SAL < 5000 AND COMM IS NULL;
SELECT * FROM EMP WHERE DEPTNO = 30 OR JOB = ‘PRESIDENT’;
SELECT * FROM EMP WHERE COMM IS NULL OR ENAME LIKE ‘A%’;
SELECT * FROM EMP WHERE JOB NOT LIKE ‘M%’;
SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 3000;
SELECT * FROM EMP ORDER BY ENAME;
SELECT * FROM EMP ORDER BY JOB, ENAME;
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO FROM EMP ORDER BY 3;
SELECT ENAME EMPLOYEE FROM EMP ORDER BY EMPLOYEE;
---------------------------------------------------------------------------------------------------
SELECT UPPER (‘asif khan’), LOWER (‘SQL PLUS’), INITCAP (‘ORACLE’) FROM 
DUAL;
SELECT EMPNO, RPAD (ENAME, 20,’.’), LPAD (SAL, 6,’*’) FROM EMP;
SELECT INSTR (‘PAKISTAN’,’A’, 1) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, 3) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, -1) FROM DUAL;
SELECT INSTR (‘PAKISTAN’,’A’, -3) FROM DUAL;
SELECT ROUND (125.978, 1) FROM DUAL;
SELECT ROUND (123.978,-1) FROM DUAL;
SELECT ROUND (123.978, 0) FROM DUAL;
SELECT ROUND (123.978) FROM DUAL;
SELECT ROUND (126.978,-2) FROM DUAL;
SELECT ROUND (156.978,-2) FROM DUAL;
SELECT ROUND (149.978,-2) FROM DUAL;
SELECT TO_CHAR (SYSDATE,’DD-MON-YYYY’) FROM DUAL;
SELECT TO_CHAR (TO_DATE (’27-OCT-17’, ’DD-MON-RR’),’YYYY’) FROM DUAL;
SELECT TO_CHAR (TO_DATE (’27-OCT-17’, ’DD-MON-YY’),’YYYY’) FROM DUAL;
SELECT TO_CHAR (15000, ‘$99,999.9’) FROM DUAL;
SELECT TO_CHAR (50, ‘$99, 9999.9’) FROM DUAL; 
SELECT 3000 + TO_NUMBER (‘5000’) FROM DUAL;
SELECT TO_CHAR (SYSDATE, ‘YYYY YEAR’) FROM DUAL;
SELECT TO_CHAR (SYSDATE, ‘DY DAY’) FROM DUAL;
SELECT EMPNO, DEPTNO, DNAME FROM EMP, DEPT 
WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, DEPT.DEPTNO, JOB, DNAME FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO AND JOB = ‘MANAGER’;
SELECT E.EMPNO, E.ENAME, S.GRADE, D.DNAME FROM EMP E, DEPT D, 
SALGRADE S WHERE E.DEPTNO=D.DEPTNO AND E.SAL BETWEEN S.LOSAL 
AND S.HISAL;
SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E, DEPT D 
WHERE E.DEPTNO (+) =D.DEPTNO;
SELECT WORKER.ENAME||’WORK FOR’||MANAGER.ENAME FROM EMP 
WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO;
SELECT WORKER.ENAME||’WORK FOR’||MANAGER.ENAME FROM EMP 
WORKER, EMP MANAGER WHERE WORKER.MGR = MANAGER.EMPNO (+);
SELECT COUNT (COMM) FROM EMP;
SELECT JOB, MAX (SAL) FROM EMP GROUP BY JOB;
Q.1. Find the All Employees Who Are Earning More than Mr. JONES
SELECT * FROM EMP 
WHERE SAL> (SELECT SAL FROM EMP
WHERE ENAME = ‘JONES’);
Q2. Display All Employees Which Are Working In a Same Department Of Mr. 
SMITH
SELECT * FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP
WHERE ENAME = ‘SMITH’);
Q3. DISPLAY ALL EMPLOYEES WHO WERE HIRED AFTER MR.FORD WAS 
HIRED.
SELECT * FROM EMP
WHERE HIREDATE > (SELECT HIREDATE FROM EMP
WHERE ENAME = ‘FORD’)
Q4. DISPLAY ALL EMPLOYEES WHO ARE EARNING MORE THAN THE SALARY 
OF THAT EMPLOYEE WHICH HAS HIGHEST SALARY IN DEPARTMENT 30.
SELECT * FROM EMP
WHERE SAL > (SELECT MAX (SAL) FROM EMP
WHERE DEPTNO = 30);
=====================================================================
Questions
1. Which is the subset of SQL commands used to manipulate Oracle Database 
structures, including tables?
2. What operator performs pattern matching?
3. What operator tests column for the absence of data?
4. Which command executes the contents of a specified file?
5. What is the parameter substitution symbol used with INSERT INTO 
command?
6. Which command displays the SQL command in the SQL buffer, and then 
executes it?
7. What are the wildcards used for pattern matching?
8. State true or false. EXISTS, SOME, ANY are operators in SQL.
True
9. State true or false. !=, <>, ^= all denote the same operation.
10. What are the privileges that can be granted on a table by a user to others?
11. What command is used to get back the privileges offered by the GRANT 
command?
12. Which system tables contain information on privileges granted and privileges 
obtained?
13. Which system table contains information on constraints on all the tables 
created?
15. What is the difference between TRUNCATE and DELETE commands?
16. What command is used to create a table by copying the structure of another 
table?
17. What will be the output of the following query?
SELECT REPLACE(TRANSLATE(LTRIM(RTRIM('!! ATHEN !!','!'), '!'), 'AN', 
'**'),'*','TROUBLE') FROM DUAL;
18. What will be the output of the following query?
SELECT DECODE(TRANSLATE('A','1234567890','1111111111'), '1','YES', 'NO' );
19. What does the following query do?
SELECT SAL + NVL(COMM,0) FROM EMP;
20. Which date function is used to find the difference between two dates?

Procedure and Function Examples

Procedures:-
A Procedure is a named PL/SQL block which is compiled and stored in
the database for repeated execution.

Basic Syntax :
------------

Create or replace procedure <procedure_name>
is
begin
..............
..............
.............
end;
/
Ex 1:
-----------
Create or replace procedure p1
is
begin
dbms_output.put_line('Hello World');
end;
/

Procedure created.

To execute the procedure:
----------------------------
Exec command is used to execute the procedure.

SQL> Exec p1
Hello World

A procedure can have three types of parameters.
1) IN Parameter
2) OUT Parameter
3) IN OUT Parameter
In Parameters are used to accept values from the user.
Ex 2:
---------
Create a procedure which accepts two numbers and display its sum.

create or replace procedure add_num ( a IN number,
b IN number)
is
c number(3);
begin
c := a+b;
dbms_output.put_line(' The sum is '||c);
end;
/

Procedure created.

To execute the procedure:
--------------------------
SQL> exec add_num (10,20)


Ex 3:
--------

Create a Procedure which accepts an empno and increments his salary by 1000.
create or replace procedure inc_sal ( a in number)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/

Procedure created.

TO execute the procedure:
---------------------------

SQL> exec inc_sal(7900)

We can improve the above procedure code by using %type attribute in
 procedure parameters.

The above procedure can be re-written as below :

create or replace procedure inc_sal ( a in emp.empno%type)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/


Create a procedure which accepts deptno and display ename and salary
of employees working in that department.
create or replace procedure display_emp1 (l_deptno emp.deptno%type)
is
cursor c1
is select ename,sal from emp
where deptno = l_deptno;

begin

for emp_rec in c1 loop
dbms_output.put_line(emp_rec.ename||'...'||emp_rec.sal);
end loop;

end;
=====================================================================

Functions:-
Function is a PL/SQL block which must and should return single value.
Syntax:
-----------

Create or replace function <Function_name>
( <Par_name> <mode> <datatype>,
,, ,, ,, )
return datatype
is
Begin
..........
.........
end;
/

ex1:
-----

Create a function which accepts two numbers and display its sum.

create or replace function add_num_f1 ( a number, b number)
return number
is
c number(5);
begin

c :=a+b;
return c;
end;
/
To invoke a function from a pl/Sql block:
---------------------------------------------

declare
n number(5);
begin

n := add_num_f1(20,40);

dbms_output.put_line('The sum is '||n);
end;
/
We can invoke functions from select stmt:
----------------------------------------------
select add_num_f1(30,50) from dual;
Functions can be invoked as part of an expression:
----------------------------------------------------

select 100 + add_num_f1(50,10) from dual;
Ex2:
------

create a function which accepts sal and returns tax value ( 10% of sal is tax).

create or replace function cal_tax ( a number)
is
begin

return a*10/100;
end;
/

Note: A function can return a value using return statement.
Ex 3:
----------

Have a look at the following function:



create or replace function add_num_f2 ( a number, b number)
return number
is
c number(5);
begin

insert into dept values (50,'HR','FAISALABAD')

c :=a+b;
return c;
end;
/
The above function gets created.

The above function can be invoked from the pl/SQL block

declare
n number(5);
begin

n := add_num_f2(20,40);

dbms_output.put_line('The sum is '||n);
end;
/


But, we cannot invoke the above function using select stmt.

ex:

select add_num_f2(30,50) from dual; -- will give us error.

Note: So, functions with dml commands cannot be invoked from select stmt.

------------------------

TO see the list of all the functions
select object_name from user_objects
where object_type = 'FUNCTION';
----------------------

To drop a function

drop function <function_name>;

ex:

drop function add_num_f2;

-----------------------

Functions are mainly used for calculation purposes.
Rest of the activities, prefer procedures.Pro

Oracle DBA Salary Position

Oracle DBA salary and job duties
Back in 1981, the average DBA salary was about age+10, and a DBA right out of college could expect to earn $32k/year, a nice salary back when gasoline was 80 cents a gallon.  Today, inflation and increased demand have increased the DBA pay scale:
Low Tier DBA -
An Oracle DBA without college and less than 10 years experience earns about age*2, a 25 year-old earning about $50k/year.
Middle Tier DBA
A DBA with a Bachelors degree and ten years full-time work experience can earn up to age*3, with a 35 year-old DBA earning about $105/year.
Senior DBA
A senior DBA with a Masters degree, 20 years experience managing mission critical database can earn up to age*4, with a 45 year-old DBA manager earning up to $180k/year.
Superstar DBA
For those DBA Superstars with advanced degrees and specialized skills (RAC, Oracle Apps), the pay is often as high as age*6 and a 35 year-old superstar can earn up to $210k per year.

Some Useful Forms Builder Questions

      How to execute the user defined triggers in forms?


Explain What is a predefined exception available in forms?


Explain What is a Check Box?


Can we change the default value of the radio button group at run time?


Explain What is the difference between NAME_IN and COPY?


Explain What is a Layout Editor?


Explain EXIT_FORM is a restricted package procedure?


Explain some built-in routines used to manipulate images in image_item?


Explain What are the types of TRIGGERS?


How to Give built-in routine related to a record groups?


Explain What is a Navigable item?


Can we change the default value of the radio button group at run time?


Explain What is a Navigable item?


Can we change the color of the push button in design time?


Explain When does an on-lock trigger fire?


Explain What does an on-clear-block Trigger fire?


Explain How do you pass the parameters from one form to another form?


Explain What is the difference between a POST-FIELD trigger and a POST-CHANGE trigger?


Explain What is an User Exits?


Explain What package procedure is used for calling another form?


Explain Committed block sometimes refer to a BASE TABLE?


Explain the built-in routine used to count the no of rows in a group?


Explain What is the built-in function used for finding the alert?


Explain What are the types of TRIGGERS?


Explain the Abbreviation for the following File Extension: 1. FMB 2. MMB 3. PLL


Explain what is a display item?


Explain What are the menu items that oracle forms 4.0 supports?


Explain System.effective_date system variable is read only True/False?


Explain What are the types of Pop-up window?


Explain What are the triggers associated with a check box?


Explain What is the content view and stacked view?


Explain What Enter package procedure does?


Explain What package procedure used for invoke sql *plus from sql *forms?


Explain What are the main types of TRIGGERS?


Explain What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS?


Explain What package procedure used for invoke sql *plus from sql *forms?


Explain What is an SQL *FORMS?


Explain What are the types of canvas-views?


Explain some built-in routines used to manipulate images in image_item?


Explain What SYNCHRONIZE procedure does?


Explain What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS?


Explain When is PRE-QUERY trigger executed?


Explain What are the menu items that oracle forms 4.0 supports?


Explain What is a predefined exception available in forms 4.0?


Can we attach an alert to a field?


Explain What are the main types of TRIGGERS?


Explain EXIT_FORM is a restricted package procedure? a. True b. False?


Explain When is PRE-QUERY trigger executed?


Explain the built-in routine used to count the no of rows in a group?


Explain How do we trap the error in forms 3.0 ?


Explain What are the types of Pop-up window?


Explain What Enter package procedure does?


Explain What does an on-clear-block Trigger fire?


Explain How image_items can be populate to field in forms 4.0?


Explain What is a window?


Explain What are the types of canvas-views?


Explain What is the difference between a POST-FIELD trigger and a POST-CHANGE trigger?


Explain What is an User Exits?


Explain What is the maximum size of a form?

Some Useful PL/SQL Questions

State the difference between implicit and explicit cursor's.


How to avoid using cursors? What to use instead of cursor and in what cases to do so?


What is trigger,cursor,functions in pl-sql and we need sample programs about it?


What is difference between stored procedures and application procedures,stored function and application function?


What is pl/sql?what are the advantages of pl/sql?


What is Raise_application_error ?


Explain the usage of WHERE CURRENT OF clause in cursors ?


How we can create a table in PL/SQL block. insert records into it??? is it possible by some procedure or function?? please give example...


What is the starting "oracle error number"? 2)What is meant by forward declaration in functions?


Name the tables where characteristics of Package, procedure and functions are stored ?


How to disable multiple triggers of a table at at a time?


In a Distributed Database System Can we execute two queries simultaneously ? Justify ?


How many types of database triggers can be specified on a table ? What are they ?


Can Commit,Rollback ,Savepoint be used in Database Triggers?If yes than HOW? If no Why?With Reasons


In pl/sql functions what is use of out parameter even though we have return statement.


Explain how procedures and functions are called in a PL/SQL block ?


How to debug the procedure ?


What will the Output for this Coding? Declare Cursor c1 is select * from emp FORUPDATE; Z c1%rowtype; Begin Open C1; Fetch c1 into Z; Commit; Fetch c1 in to Z; end;


Explian rowid,rownum?What are the pseduocolumns we have?


State the advantage and disadvantage of Cursor?
What is Pragma EXECPTION_INIT ? Explain the usage ?


Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?


What is TTITLE and BTITLE?


What is ref cursor?


What are two parts of package ?


What are the Restrictions on Cursor Variables?


How we can create a table through procedure ?


What will happen after commit statement ?


Can we declare a column having number data type and its scale is larger than pricesion ex: column_name NUMBER(10,100), column_name NUMBAER(10,-84)


What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?


What is a database trigger ? Name some usages of database trigger ?


Give the structure of the function ?


What is difference between a PROCEDURE & FUNCTION ?


What is SQL Integrity?


What are the return values of functions SQLCODE and SQLERRM ?


The most important DDL statements in SQL are?


What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?


What is SQL Deadlock?


The IN operator may be used if you know the exact value you want to return for at least one of the columns.

Some Useful SQL Questions

Find out nth highest salary from emp table

Difference between procedure and function.

Whet are the difference between primary key and foreign key?

What is the maximum buffer size that can be specified using the DBMS_OUTPUT.ENABLE function?

Difference between database triggers and form triggers?

Display Odd/ Even number of records

Which datatype is used for storing graphics and images?

What is difference between SQL and SQL*PLUS?

Can you use a commit statement within a database trigger?

What is difference between SUBSTR and INSTR?

Which is more faster - IN or EXISTS?

What is an UTL_FILE.What are different procedures and functions associated with it?

What WHERE CURRENT OF clause does in a cursor?

What is a cursor?

What is the purpose of a cluster?

Difference between an implicit & an explicit cursor.

Differentiate between TRUNCATE and DELETE

There is a % sign in one field of a column. What will be the query to find it?

What should be the return type for a cursor variable.Can we use a scalar data type as return type?

What a SELECT FOR UPDATE cursor represent.
Can a primary key contain more than one columns?

minvalue.sql Select the Nth lowest value from a table

Can a function take OUT parameters. If not why?

Display the number value in Words?

How you will avoid your query from using indexes?

What is difference between Rename and Alias?

What are various joins used while writing SUBQUERIES?

Difference between NO DATA FOUND and %NOTFOUND

What is syntax for dropping a procedure and a function .Are these operations possible?

What are various privileges that a user can grant to another user?

What is OCI. What are its uses?

What is difference between a formal and an actual parameter?

Can the default values be assigned to actual parameters?

Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

How do you find the number of rows in a Table ?

What are different modes of parameters used in functions and procedures?

How you were passing cursor variables in PL/SQL 2.2?

What is a pseudo column. Give some examples?

Display the records between two range?

What are ORACLE PRECOMPILERS?
How will you delete duplicating rows from a base table?

When do you use WHERE clause and when do you use HAVING clause?

maxvalue.sql Select the Nth Highest value from a table

There is a string 120000 12 0 .125 , how you will find the position of the decimal place?

What is difference between UNIQUE and PRIMARY KEY constraints?

What are various constraints used in SQL?

What are different Oracle database objects?

Explain Difference between database triggers and form triggers?

what is a index?with example? what is subquery with example? what is deadlock in SQL with example? what is cursor advantatage?

How you will avoid duplicating records in a query?

The maximum number of columns a table can have in oracle SQL ?

Can cursor variables be stored in PL/SQL tables.If yes how. If not why?

How you open and close a cursor variable.Why it is required?

Explain What is difference between SUBSTR and INSTR?

When I have 6 values for any column, in select statement I get display of 6 rows selected while if I have 5 values for one column i don't get display of 5 rows selected, how to get that display?

How to pass cursor variables in PL/SQL 2.2?

What is a view?

Explain What is difference between SQL and SQL*PLUS?

What are cursor attributes?

Explain Which datatype is used for storing graphics and images?