Question:
What are the wildcards used for pattern matching.
Answer: _ for single character substitution and % for multi-character substitution.
Question: How can I hide a particular table name of our schema?
Answer: you can hide the table name by creating synonyms.
e.g) you can create a synonym y for table x
create synonym y for x;
Question: When we give SELECT * FROM EMP; How does oracle respond:
Answer: When u give SELECT * FROM EMP;
the server check all the data in the EMP file and it displays the data of the EMP file
Question: What is the use of CASCADE CONSTRAINTS?
Answer: When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
Question: There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?
Answer: What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table and in case there are no matching records in the right table, it shows null for the selected columns of the right table. E.g. in this query which uses the key-word LEFT OUTER JOIN. Syntax though varies across databases. In DB2/UDB it uses the key word LEFT OUTER JOIN, in case of Oracle the connector is Employee_table.Dept_id *= Dept_table.Dept_id
SQL Server/Sybase :
Employee_table.Dept_id(+) = Dept_table.Dept_id
Question: on index
why u need indexing? Where that is stored
and what u mean by schema object?
For what purpose we are using view
Answer: We can?t create an Index on Index. Index is stored in user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table...So that is a view.
Question: How to store directory structure in a database?
Answer: We can do it by the following command: create or replace directory as 'c: \tmp'
Question: Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME;
Answer: Variable names should start with an alphabet. Here the table name starts with an '&' symbol.
Question: Difference between VARCHAR and VARCHAR2?
Answer: Varchar means fixed length character data (size) i.e., min size-1 and max-2000
Varchar2 means variable length character data i.e., min-1 to max-4000
Question: Which command displays the SQL command in the SQL buffer, and then executes it
Answer: You set the LIST or L command to get the recent one from SQL Buffer
Question: Which system table contains information on constraints on all the tables created?
Answer: USER_CONSTRAINTS.
Question: How do I write a program which will run a SQL query and mail the results to a group?
Answer: Use DBMS_JOB for scheduling a program job and DBMS_MAIL to send the results through email.
Question: There is an Eno. & gender in a table. Eno. has primary key and gender has a check constraints for the values 'M' and 'F'.
While inserting the data into the table M was misspelled as F and F as M.
What is the update?
Answer: update <TableName> set gender=
case where gender='F' Then 'M'
where gender='M' Then 'F'
Question: What the difference between UNION and UNIONALL?
Answer: union will return the distinct rows in two select s, while union all return all rows.
Question: How can we backup the sql files & what is SAP?
Answer: You can backup the sql files through backup utilities or some backup command in sql. SAP is ERP software for the organization to integrate the software.
Question: What is the difference between TRUNCATE and DELETE commands?
Answer: TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.
WHERE clause can be used with DELETE and not with TRUNCATE.
Question: State true or false. !=, <>, ^= all denote the same operation.
Answer: True.
Question: State true or false. EXISTS, SOME, ANY are operators in SQL.
Answer: True.
Question: What will be the output of the following query?
SELECT REPLACE (TRANSLATE (LTRIM (RTRIM ('!! ATHEN!!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;
Answer: TROUBLETHETROUBLE.
Question: What is the advantage to use trigger in your PL?
Answer: Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:
- Suppose we need to validate a DML statement (insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.
- Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
- Triggers can be used to enforce constraints. For eg: Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
- Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User login in or user logoff.
Question: How write a SQL statement to query the result set and display row as columns and columns as row?
Answer: TRANSFORM Count (Roll_no) AS Count of Roll_no
SELECT Academic_Status
FROM tbl_enr_status
GROUP BY Academic_Status
PIVOT Curnt_status;
Question: Cursor Syntax brief history
Answer: To retrieve data with SQL one row at a time you need to use cursor processing. Not all relational databases support this, but many do. Here I show this in Oracle with PL/SQL, which is Procedural Language SQL .Cursor processing is done in several steps:1. Define the rows you want to retrieve. This is called declaring the cursor.2. Open the cursor. This activates the cursor and loads the data. Note that declaring the cursor doesn't load data, opening the cursor does.3. Fetch the data into variables.4. Close the cursor.
Question: What is the data type of the surrogate key?
Answer: Data type of the surrogate key is either integer or numeric or number
Question: How to write a sql statement to find the first occurrence of a non zero value?
Answer: There is a slight chance the column "a" has a value of 0 which is not null. In that case, you?ll loose the information. There is another way of searching the first not null value of a column:
select column_name from table_name where column_name is not null and rownum<2;
Question: What is normalazation, types with e.g.\'s. _ with queries of all types
Answer: There are 5 normal forms. It is necessary for any database to be in the third normal form to maintain referential integrity and non-redundancy.
First Normal Form: Every field of a table (row, col) must contain an atomic value
Second Normal Form: All columns of a table must depend entirely on the primary key column.
Third Normal Form: All columns of a table must depend on all columns of a composite primary key.
Fourth Normal Form: A table must not contain two or more independent multi-valued facts. This normal form is often avoided for maintenance reasons.
Fifth Normal Form: is about symmetric dependencies.
Each normal form assumes that the table is already in the earlier normal form.
Question: Given an unnormalized table with columns:
Answer: The query will be: delete from tabname where rowid not in (select max (rowid) from tabname group by name) Here tabname is the table name.
Question: How to find second maximum value from a table?
Answer: select max (field1) from tname1 where field1= (select max (field1) from tname1 where field1<(select max(field1) from tname1);
Field1- Salary field
Tname= Table name.
Question: What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
Answer: The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
Question: What is the main difference between the IN and EXISTS clause in sub queries??
Answer: The main difference between the IN and EXISTS predicate in sub query is the way in which the query gets executed.
IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query. The inner query is executed for only once.
EXISTS -- The first row from the outer query is selected, then the inner query is executed and, the outer query output uses this result for checking. This process of inner query execution repeats as many no .of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no. of times.
Question: TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ
Answer: The difference is that the TRUNCATE call cannot be rolled back and all memory space for that table is released back to the server. TRUNCATE is much faster than DELETE and in both cases only the table data is removed, not the table structure.
Question: What is table space?
Answer: Table-space is a physical concept. It has pages where the records of the database are stored with a logical perception of tables. So table space contains tables.
Question: How to find out the 10th highest salary in SQL query?
Answer: Table - Tbl_Test_Salary
Column - int_salary
select max (int_salary)
from Tbl_Test_Salary
where int_salary in
(select top 10 int_Salary from Tbl_Test_Salary order by int_salary)
Question: Which command executes the contents of a specified file?
Answer: START <filename> or @<filename>.
Question: What is the difference between SQL and SQL SERVER?
Answer: SQL Server is an RDBMS just like oracle, DB2 from Microsoft
whereas
Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS.
Question: What is the difference between Single row sub-Query and Scalar Sub-Query?
Answer: SINGLE ROW SUBQUERY RETURNS A VALUE WHICH IS USED BY WHERE CLAUSE, WHEREAS SCALAR SUBQUERY IS A SELECT STATEMENT USED IN COLUMN LIST CAN BE THOUGHT OF AS AN INLINE FUNCTION IN SELECT COLUMN LIST.
Question: What does the following query do?
Answer: SELECT SAL + NVL (COMM, 0) FROM EMP;
It gives the added value of sal and comm for each employee in the emp table.
NVL (null value) replaces null with 0.
Question: How to find second maximum value from a table?
Answer: select max (field1) from tname1 where field1= (select max (field1) from tname1 where field1< (select max (field1) from tname1);
Field1- Salary field
Tname= Table name.
Question: I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated.
Answer: SELECT COL1 FROM TAB1
WHERE COL1 IN
(SELECT MAX (COL1) FROM TAB1
GROUP BY COL1
HAVING COUNT (COL1) > 1)
Question: How to find out the database name from SQL*PLUS command prompt?
Answer: Select * from global_name;
This will give the data base name which u r currently connected to.....
Question: How to display duplicate rows in a table?
Answer: select * from emp
group by (empid)
having count (empid)>1
Question: What is the value of comm and sal after executing the following query if the initial value of ?sal? is 10000
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;
Answer: sal = 11000, comm = 1000.
Question: 1) What is difference between Oracle and MS Access?
2) What are disadvantages in Oracle and MS Access?
2) What are features & advantages in Oracle and MS Access?
Answer: Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable Oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle.
Answer: _ for single character substitution and % for multi-character substitution.
Question: How can I hide a particular table name of our schema?
Answer: you can hide the table name by creating synonyms.
e.g) you can create a synonym y for table x
create synonym y for x;
Question: When we give SELECT * FROM EMP; How does oracle respond:
Answer: When u give SELECT * FROM EMP;
the server check all the data in the EMP file and it displays the data of the EMP file
Question: What is the use of CASCADE CONSTRAINTS?
Answer: When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
Question: There are 2 tables, Employee and Department. There are few records in employee table, for which, the department is not assigned. The output of the query should contain all th employees names and their corresponding departments, if the department is assigned otherwise employee names and null value in the place department name. What is the query?
Answer: What you want to use here is called a left outer join with Employee table on the left side. A left outer join as the name says picks up all the records from the left table and based on the joint column picks the matching records from the right table and in case there are no matching records in the right table, it shows null for the selected columns of the right table. E.g. in this query which uses the key-word LEFT OUTER JOIN. Syntax though varies across databases. In DB2/UDB it uses the key word LEFT OUTER JOIN, in case of Oracle the connector is Employee_table.Dept_id *= Dept_table.Dept_id
SQL Server/Sybase :
Employee_table.Dept_id(+) = Dept_table.Dept_id
Question: on index
why u need indexing? Where that is stored
and what u mean by schema object?
For what purpose we are using view
Answer: We can?t create an Index on Index. Index is stored in user_index table. Every object that has been created on Schema is Schema Object like Table, View etc. If we want to share the particular data to various users we have to use the virtual table for the Base table...So that is a view.
Question: How to store directory structure in a database?
Answer: We can do it by the following command: create or replace directory as 'c: \tmp'
Question: Why does the following command give a compilation error?
DROP TABLE &TABLE_NAME;
Answer: Variable names should start with an alphabet. Here the table name starts with an '&' symbol.
Question: Difference between VARCHAR and VARCHAR2?
Answer: Varchar means fixed length character data (size) i.e., min size-1 and max-2000
Varchar2 means variable length character data i.e., min-1 to max-4000
Question: Which command displays the SQL command in the SQL buffer, and then executes it
Answer: You set the LIST or L command to get the recent one from SQL Buffer
Question: Which system table contains information on constraints on all the tables created?
Answer: USER_CONSTRAINTS.
Question: How do I write a program which will run a SQL query and mail the results to a group?
Answer: Use DBMS_JOB for scheduling a program job and DBMS_MAIL to send the results through email.
Question: There is an Eno. & gender in a table. Eno. has primary key and gender has a check constraints for the values 'M' and 'F'.
While inserting the data into the table M was misspelled as F and F as M.
What is the update?
Answer: update <TableName> set gender=
case where gender='F' Then 'M'
where gender='M' Then 'F'
Question: What the difference between UNION and UNIONALL?
Answer: union will return the distinct rows in two select s, while union all return all rows.
Question: How can we backup the sql files & what is SAP?
Answer: You can backup the sql files through backup utilities or some backup command in sql. SAP is ERP software for the organization to integrate the software.
Question: What is the difference between TRUNCATE and DELETE commands?
Answer: TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back.
WHERE clause can be used with DELETE and not with TRUNCATE.
Question: State true or false. !=, <>, ^= all denote the same operation.
Answer: True.
Question: State true or false. EXISTS, SOME, ANY are operators in SQL.
Answer: True.
Question: What will be the output of the following query?
SELECT REPLACE (TRANSLATE (LTRIM (RTRIM ('!! ATHEN!!','!'), '!'), 'AN', '**'),'*','TROUBLE') FROM DUAL;
Answer: TROUBLETHETROUBLE.
Question: What is the advantage to use trigger in your PL?
Answer: Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:
- Suppose we need to validate a DML statement (insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.
- Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
- Triggers can be used to enforce constraints. For eg: Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
- Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User login in or user logoff.
Question: How write a SQL statement to query the result set and display row as columns and columns as row?
Answer: TRANSFORM Count (Roll_no) AS Count of Roll_no
SELECT Academic_Status
FROM tbl_enr_status
GROUP BY Academic_Status
PIVOT Curnt_status;
Question: Cursor Syntax brief history
Answer: To retrieve data with SQL one row at a time you need to use cursor processing. Not all relational databases support this, but many do. Here I show this in Oracle with PL/SQL, which is Procedural Language SQL .Cursor processing is done in several steps:1. Define the rows you want to retrieve. This is called declaring the cursor.2. Open the cursor. This activates the cursor and loads the data. Note that declaring the cursor doesn't load data, opening the cursor does.3. Fetch the data into variables.4. Close the cursor.
Question: What is the data type of the surrogate key?
Answer: Data type of the surrogate key is either integer or numeric or number
Question: How to write a sql statement to find the first occurrence of a non zero value?
Answer: There is a slight chance the column "a" has a value of 0 which is not null. In that case, you?ll loose the information. There is another way of searching the first not null value of a column:
select column_name from table_name where column_name is not null and rownum<2;
Question: What is normalazation, types with e.g.\'s. _ with queries of all types
Answer: There are 5 normal forms. It is necessary for any database to be in the third normal form to maintain referential integrity and non-redundancy.
First Normal Form: Every field of a table (row, col) must contain an atomic value
Second Normal Form: All columns of a table must depend entirely on the primary key column.
Third Normal Form: All columns of a table must depend on all columns of a composite primary key.
Fourth Normal Form: A table must not contain two or more independent multi-valued facts. This normal form is often avoided for maintenance reasons.
Fifth Normal Form: is about symmetric dependencies.
Each normal form assumes that the table is already in the earlier normal form.
Question: Given an unnormalized table with columns:
Answer: The query will be: delete from tabname where rowid not in (select max (rowid) from tabname group by name) Here tabname is the table name.
Question: How to find second maximum value from a table?
Answer: select max (field1) from tname1 where field1= (select max (field1) from tname1 where field1<(select max(field1) from tname1);
Field1- Salary field
Tname= Table name.
Question: What is the advantage of specifying WITH GRANT OPTION in the GRANT command?
Answer: The privilege receiver can further grant the privileges he/she has obtained from the owner to any other user.
Question: What is the main difference between the IN and EXISTS clause in sub queries??
Answer: The main difference between the IN and EXISTS predicate in sub query is the way in which the query gets executed.
IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query. The inner query is executed for only once.
EXISTS -- The first row from the outer query is selected, then the inner query is executed and, the outer query output uses this result for checking. This process of inner query execution repeats as many no .of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no. of times.
Question: TRUNCATE TABLE EMP;
DELETE FROM EMP;
Will the outputs of the above two commands differ
Answer: The difference is that the TRUNCATE call cannot be rolled back and all memory space for that table is released back to the server. TRUNCATE is much faster than DELETE and in both cases only the table data is removed, not the table structure.
Question: What is table space?
Answer: Table-space is a physical concept. It has pages where the records of the database are stored with a logical perception of tables. So table space contains tables.
Question: How to find out the 10th highest salary in SQL query?
Answer: Table - Tbl_Test_Salary
Column - int_salary
select max (int_salary)
from Tbl_Test_Salary
where int_salary in
(select top 10 int_Salary from Tbl_Test_Salary order by int_salary)
Question: Which command executes the contents of a specified file?
Answer: START <filename> or @<filename>.
Question: What is the difference between SQL and SQL SERVER?
Answer: SQL Server is an RDBMS just like oracle, DB2 from Microsoft
whereas
Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS.
Question: What is the difference between Single row sub-Query and Scalar Sub-Query?
Answer: SINGLE ROW SUBQUERY RETURNS A VALUE WHICH IS USED BY WHERE CLAUSE, WHEREAS SCALAR SUBQUERY IS A SELECT STATEMENT USED IN COLUMN LIST CAN BE THOUGHT OF AS AN INLINE FUNCTION IN SELECT COLUMN LIST.
Question: What does the following query do?
Answer: SELECT SAL + NVL (COMM, 0) FROM EMP;
It gives the added value of sal and comm for each employee in the emp table.
NVL (null value) replaces null with 0.
Question: How to find second maximum value from a table?
Answer: select max (field1) from tname1 where field1= (select max (field1) from tname1 where field1< (select max (field1) from tname1);
Field1- Salary field
Tname= Table name.
Question: I have a table with duplicate names in it. Write me a query which returns only duplicate rows with number of times they are repeated.
Answer: SELECT COL1 FROM TAB1
WHERE COL1 IN
(SELECT MAX (COL1) FROM TAB1
GROUP BY COL1
HAVING COUNT (COL1) > 1)
Question: How to find out the database name from SQL*PLUS command prompt?
Answer: Select * from global_name;
This will give the data base name which u r currently connected to.....
Question: How to display duplicate rows in a table?
Answer: select * from emp
group by (empid)
having count (empid)>1
Question: What is the value of comm and sal after executing the following query if the initial value of ?sal? is 10000
UPDATE EMP SET SAL = SAL + 1000, COMM = SAL*0.1;
Answer: sal = 11000, comm = 1000.
Question: 1) What is difference between Oracle and MS Access?
2) What are disadvantages in Oracle and MS Access?
2) What are features & advantages in Oracle and MS Access?
Answer: Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable Oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle.
---------------------------------------------------------------------------------------------------------------------
oracle plsql interview
questions and answers part-1
1. Query for retrieving N highest paid employees
FROM each Department.
2. Query that will display the total no. of
employees, and of that total the number who were
hired in 1980, 1981, 1982, and
1983.
3. Query for listing Deptno, ename, sal, SUM(sal
in that dept).
4. Matrix query to display the job, the salary
for that job based on department number, and
the total salary for that job
for all departments.
5. Nth Top Salary of all the employees.
6. Retrieving the Nth row FROM a table.
7. Tree Query.
8. Eliminate duplicates rows in a table.
9. Displaying EVERY Nth row in a table.
10. Top N rows FROM a table.
11. COUNT/SUM RANGES of data values in a column.
12. For equal size ranges it might be easier to
calculate it with DECODE(TRUNC(value/range),
0, rate_0, 1, rate_1, ...).
13. Count different data values in a column.
14. Query to get the product of all the values of
a column.
15. Query to display only the duplicate records in
a table.
16. Query for getting the following output as many
number of rows in the table.
17. Function for getting the Balance Value.
18. Function for getting the Element Value.
19. SELECT Query for counting No of words.
20. Function to check for a leap year.
21. Query for removing all non-numeric.
22. Query for translating a column values to
INITCAP.
23. Function for displaying Rupees in Words.
24. Function for displaying
Numbers in Words
25. Query for deleting alternate even rows FROM a
table.
26. Query for deleting alternate odd rows FROM a
table.
27. Procedure for sending Email.
28. Alternate Query for DECODE function.
29. Create table adding Constraint to a date field
to SYSDATE or 3 months later.
30. Query to list all the suppliers who r supplying
all the parts supplied by supplier 'S2'.
31. Query to get the last Sunday of any month.
32. Query to get all those who have no children
themselves.
33. Query to SELECT last N rows FROM a table.
34. SELECT with variables.
35. Query to get the DB Name.
36. Getting the current default schema.
37. Query to get all the column names of a
particular table.
38. Spool only the query result to a file in
SQLPLUS.
39. Query for getting the current SessionID.
40. Query to display rows FROM m to n.
41. Query to count no. Of columns in a table.
42. Procedure to increase the buffer length.
43. Inserting an & symbol in a Varchar2
column.
44. Removing Trailing blanks in a spooled file.
Sekhar
45. Samples for executing Dynamic SQL Statements.
46. Differences between SQL and MS-Access.
47. Query to display all the children, sub
children of a parent.
48. Procedure to read/write data from/to a text
file.
49. Query to display random number between any two
given numbers.
50. Time difference between two date columns.
51. Using INSTR and SUBSTR
52. View procedure code
53. To convert signed number to number in oracle
54. Columns of a table
55. Delete rows conditionally
56. CLOB to Char
57. Change Settings
58. Double quoting a Single quoted String
59. Time Conversion
60. Table comparison
61. Running Jobs
62. Switching Columns
63. Replace and Round
64. First date of the year
65. Create Sequence
66. Cursors
67. Current Week
68. Create Query to restrict the user to a single
row.
69. Query to get the first inserted record FROM a
table.
70. Concatenate a column value with multiple rows.
71. Query to delete all the tables at once.
72. SQL Query for getting Orphan Records.
ANSWERS
-----------------------------------------------------------------------------------------------
1. The following query
retrieves "2" highest paid employees FROM each Department :
SELECT deptno, empno, sal
FROM emp e
WHERE
2 > ( SELECT COUNT(e1.sal)
FROM emp e1
WHERE e.deptno = e1.deptno
AND e.sal < e1.sal )
ORDER BY 1,3 DESC;
Sekhar
----------------------------------------------------------------------------------------------
2. Query that will display
the total no. of employees, and of that total the number who
were hired in 1980, 1981,
1982, and 1983. Give appropriate column headings.
I am looking at the following
output. We need to stick to this format.
Total 1980 1981 1982 1983
----------- ------------
------------ ------------- -----------
14 1 10 2 1
SELECT COUNT (*),
COUNT(DECODE(TO_CHAR (hiredate, 'YYYY'),'1980', empno)) "1980",
COUNT (DECODE (TO_CHAR
(hiredate, 'YYYY'), '1981', empno)) "1981",
COUNT (DECODE (TO_CHAR
(hiredate, 'YYYY'), '1982', empno)) "1982",
COUNT (DECODE (TO_CHAR
(hiredate, 'YYYY'), '1983', empno)) "1983"
FROM emp;
----------------------------------------------------------------------------------------------
3. Query for listing Deptno,
ename, sal, SUM(sal in that dept) :
SELECT a.deptno, ename, sal,
(SELECT SUM(sal) FROM emp b WHERE a.deptno = b.deptno)
FROM emp a
ORDER BY a.deptno;
OUTPUT :
=======
DEPTNO ENAME SAL SUM (SAL)
========= ======= ====
=========
10 KING 5000 11725
30 BLAKE 2850 10900
10 CLARK 2450 11725
10 JONES 2975 11725
30 MARTIN 1250 10900
30 ALLEN 1600 10900
30 TURNER 1500 10900
30 JAMES 950 10900
30 WARD 2750 10900
20 SMITH 8000 33000
20 SCOTT 3000 33000
20 MILLER 20000 33000
----------------------------------------------------------------------------------------------
4. Create a matrix query to
display the job, the salary for that job based on department
number, and the total salary
for that job for all departments, giving each column an
appropriate heading.
Sekhar
The output is as follows - we
need to stick to this format :
Job Dept 10 Dept 20 Dept 30
Total
---------- ---------------
------------- ------------- ---------
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600
SELECT job "Job",
SUM (DECODE (deptno, 10, sal)) "Dept 10",
SUM (DECODE (deptno, 20,
sal)) "Dept 20",
SUM (DECODE (deptno, 30,
sal)) "Dept 30",
SUM (sal) "Total"
FROM emp
GROUP BY job ;
----------------------------------------------------------------------------------------------
5. 4th Top Salary of all the
employees :
SELECT DEPTNO, ENAME, SAL
FROM EMP A
WHERE
3 = (SELECT COUNT(B.SAL) FROM
EMP B
WHERE A.SAL < B.SAL) ORDER
BY SAL DESC;
----------------------------------------------------------------------------------------------
6. Retrieving the 5th row
FROM a table :
SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE ROWID = (SELECT ROWID
FROM EMP WHERE ROWNUM <= 5
MINUS
SELECT ROWID FROM EMP WHERE
ROWNUM < 5)
----------------------------------------------------------------------------------------------
7. Tree Query :
Name Null? Type
-------------------------------------------------------------------
SUB NOT NULL VARCHAR2(4)
SUPER VARCHAR2(4)
PRICE NUMBER(6,2)
Sekhar
SELECT sub, super
FROM parts
CONNECT BY PRIOR sub = super
START WITH sub = 'p1';
----------------------------------------------------------------------------------------------
8. Eliminate duplicates rows
in a table :
DELETE FROM table_name A
WHERE ROWID > ( SELECT
min(ROWID) FROM table_name B WHERE A.col = B.col);
----------------------------------------------------------------------------------------------
9. Displaying EVERY 4th row
in a table : (If a table has 14 rows, 4,8,12 rows will be
selected)
SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT
ROWID, MOD(ROWNUM,4)
FROM emp);
----------------------------------------------------------------------------------------------
10. Top N rows FROM a table :
(Displays top 9 salaried people)
SELECT ename, deptno, sal
FROM (SELECT * FROM emp ORDER
BY sal DESC)
WHERE ROWNUM < 10;
----------------------------------------------------------------------------------------------
11. How does one count/sum
RANGES of data values in a column? A value x will be
between values y and z if
GREATEST(x, y) = LEAST(x, z).
SELECT
f2,
COUNT(DECODE(greatest(f1,59),
least(f1,100), 1, 0)) "Range 60-100",
COUNT(DECODE(greatest(f1,30),
least(f1, 59), 1, 0)) "Range 30-59",
COUNT(DECODE(greatest(f1,29),
least(f1, 0), 1, 0)) "Range 00-29"
FROM my_table
GROUP BY f2;
----------------------------------------------------------------------------------------------
12. For equal size ranges it
migth be easier to calculate it with
DECODE(TRUNC(value/range), 0,
rate_0, 1, rate_1, ...).
Sekhar
SELECT ename
"Name", sal "Salary",
DECODE( TRUNC(sal/1000, 0),
0, 0.0,
1, 0.1,
2, 0.2,
3, 0.3) "Tax rate"
FROM emp;
13. How does one count
different data values in a column?
COL NAME DATATYPE
----------------------------------------
DNO NUMBER
SEX CHAR
SELECT dno, SUM(DECODE(sex,'M',1,0))
MALE,
SUM(DECODE(sex,'F',1,0))
FEMALE,
COUNT(DECODE(sex,'M',1,'F',1))
TOTAL
FROM t1
GROUP BY dno;
----------------------------------------------------------------------------------------------
14. Query to get the product
of all the values of a column :
SELECT EXP(SUM(LN(col1)))
FROM srinu;
----------------------------------------------------------------------------------------------
15. Query to display only the
duplicate records in a table:
SELECT num
FROM satyam
GROUP BY num
HAVING COUNT(*) > 1;
----------------------------------------------------------------------------------------------
16. Query for getting the
following output as many number of rows in the table :
*
**
***
****
*****
SELECT
RPAD(DECODE(temp,temp,'*'),ROWNUM,'*')
FROM srinu1;
Sekhar
----------------------------------------------------------------------------------------------
17. Function for getting the
Balance Value :
FUNCTION F_BALANCE_VALUE
(p_business_group_id number,
p_payroll_action_id number,
p_balance_name varchar2,
p_dimension_name varchar2) RETURN NUMBER
IS
l_bal number;
l_defined_bal_id number;
l_assignment_action_id
number;
BEGIN
SELECT assignment_action_id
INTO l_assignment_action_id
FROM
pay_assignment_actions
WHERE
assignment_id =
:p_assignment_id
AND payroll_action_id =
p_payroll_action_id;
SELECT
defined_balance_id
INTO
l_defined_bal_id
FROM
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE
pbt.business_group_id =
p_business_group_id
AND UPPER(pbt.balance_name) =
UPPER(p_balance_name)
AND pbt.business_group_id =
pdb.business_group_id
AND pbt.balance_type_id =
pdb.balance_type_id
AND UPPER(pbd.dimension_name)
= UPPER(p_dimension_name)
AND pdb.balance_dimension_id
= pbd.balance_dimension_id;
l_bal :=
pay_balance_pkg.get_value(l_defined_bal_id,l_assignment_action_id);
RETURN (l_bal);
exception
WHEN no_data_found THEN
RETURN 0;
END;
----------------------------------------------------------------------------------------------
18. Function for getting the
Element Value :
Sekhar
FUNCTION f_element_value(
p_classification_name in
varchar2,
p_element_name in varchar2,
p_business_group_id in
number,
p_input_value_name in
varchar2,
p_payroll_action_id in
number,
p_assignment_id in number
)
RETURN number
IS
l_element_value number(14,2)
default 0;
l_input_value_id
pay_input_values_f.input_value_id%type;
l_element_type_id
pay_element_types_f.element_type_id%type;
BEGIN
SELECT DISTINCT
element_type_id
INTO l_element_type_id
FROM pay_element_types_f pet,
pay_element_classifications
pec
WHERE pet.classification_id =
pec.classification_id
AND
upper(classification_name) = upper(p_classification_name)
AND upper(element_name) =
upper(p_element_name)
AND pet.business_group_id =
p_business_group_id;
SELECT input_value_id
INTO l_input_value_id
FROM pay_input_values_f
WHERE upper(name) =
upper(p_input_value_name)
AND element_type_id =
l_element_type_id;
SELECT
NVL(prrv.result_value,0)
INTO l_element_value
FROM pay_run_result_values
prrv,
pay_run_results prr,
pay_assignment_actions paa
WHERE prrv.run_result_id =
prr.run_result_id
AND prr.assignment_ACTION_ID
= paa.assignment_action_id
AND paa.assignment_id =
p_assignment_id
AND input_value_id =
l_input_value_id
AND paa.payroll_action_id =
p_payroll_action_id;
RETURN (l_element_value);
exception
WHEN no_data_found THEN
RETURN 0;
END;
----------------------------------------------------------------------------------------------
Sekhar
19. SELECT Query for counting
No of words :
SELECT ename,
NVL(LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVW
XYZ'' ',' @'),' ',''))+1,1)
word_length
FROM emp;
Explanation :
TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ''
','
@') -- This will translate
all the characters FROM A-Z including a single quote to a space. It will
also translate a space to a
@.
REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ''
','
@'),' ','') -- This will
replace every space with nothing in the above result.
LENGTH(REPLACE(TRANSLATE(UPPER(RTRIM(ename)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ''
',' @'),' ',''))+1 -- This
will give u the count of @ characters in the above result.
----------------------------------------------------------------------------------------------
20. Function to check for a
leap year :
CREATE OR REPLACE FUNCTION
is_leap_year (p_date IN DATE) RETURN VARCHAR2
AS
v_test DATE;
BEGIN
v_test := TO_DATE ('29-Feb-'
|| TO_CHAR (p_date,'YYYY'),'DD-Mon-YYYY');
RETURN 'Y';
EXCEPTION
WHEN OTHERS THEN
RETURN 'N';
END is_leap_year;
SQL> SELECT hiredate,
TO_CHAR (hiredate, 'Day') weekday
FROM emp
WHERE is_leap_year (hiredate)
= 'Y';
----------------------------------------------------------------------------------------------
21. Query for removing all
non-numeric :
SELECT
TRANSLATE(LOWER(ssn),'abcdefghijklmnopqrstuvwxyz-
','')
FROM DUAL;
----------------------------------------------------------------------------------------------
Sekhar
22. Query for translating a
column values to INITCAP :
SELECT
TRANSLATE(INITCAP(temp),
SUBSTR(temp,
INSTR(temp,'''')+1,1), LOWER(SUBSTR(temp, INSTR(temp,'''')+1)))
FROM srinu1;
----------------------------------------------------------------------------------------------
23. Function for displaying
Rupees in Words :
CREATE OR REPLACE FUNCTION
RUPEES_IN_WORDS(amt IN NUMBER) RETURN CHAR
IS
amount NUMBER(10,2);
v_length INTEGER := 0;
v_num2 VARCHAR2 (50) := NULL;
v_amount VARCHAR2 (50);
v_word VARCHAR2 (4000) := NULL;
v_word1 VARCHAR2 (4000) := NULL;
TYPE myarray IS TABLE OF VARCHAR2
(255);
v_str myarray := myarray ('
thousand ',
' lakh ',
' crore ',
' arab ',
' kharab ',
' shankh ');
BEGIN
amount := amt;
IF ((amount = 0) OR (amount IS
NULL)) THEN
v_word := 'zero';
ELSIF (TO_CHAR (amount) LIKE
'%.%') THEN
IF (SUBSTR (amount, INSTR (amount,
'.') + 1) > 0) THEN
v_num2 := SUBSTR (amount, INSTR
(amount, '.') + 1);
IF (LENGTH (v_num2) < 2) THEN
v_num2 := v_num2 * 10;
END IF;
v_word1 := ' AND ' || (TO_CHAR
(TO_DATE (SUBSTR (v_num2,
LENGTH (v_num2) - 1,2), 'J'),
'JSP' ))|| ' paise ';
v_amount := SUBSTR(amount,1,INSTR
(amount, '.')-1);
v_word := TO_CHAR (TO_DATE (SUBSTR
(v_amount, LENGTH (v_amount) -
2,3), 'J'), 'Jsp' ) || v_word;
v_amount := SUBSTR (v_amount, 1,
LENGTH (v_amount) - 3);
FOR i in 1 .. v_str.COUNT
LOOP
EXIT WHEN (v_amount IS NULL);
v_word := TO_CHAR (TO_DATE (SUBSTR
(v_amount, LENGTH
(v_amount) - 1,2), 'J'), 'Jsp' )
|| v_str (i) || v_word;
Sekhar
v_amount := SUBSTR (v_amount, 1,
LENGTH (v_amount) -
2);
END LOOP;
END IF;
ELSE
v_word := TO_CHAR (TO_DATE (SUBSTR
(amount, LENGTH (amount) -
2,3), 'J'), 'Jsp' );
amount := SUBSTR (amount, 1,
LENGTH (amount) - 3);
FOR i in 1 .. v_str.COUNT
LOOP
EXIT WHEN (amount IS NULL);
v_word := TO_CHAR (TO_DATE (SUBSTR
(amount, LENGTH (amount)
- 1,2), 'J'), 'Jsp' ) || v_str (i)
|| v_word;
amount := SUBSTR (amount, 1,
LENGTH (amount) - 2);
END LOOP;
END IF;
v_word := v_word || ' ' || v_word1
|| ' only ';
v_word := REPLACE (RTRIM (v_word),
' ', ' ');
v_word := REPLACE (RTRIM (v_word),
'-', ' ');
RETURN INITCAP (v_word);
END;
----------------------------------------------------------------------------------------------
24. Function for displaying
Numbers in Words:
SELECT TO_CHAR( TO_DATE(
SUBSTR( TO_CHAR(5373484),1),'j'),'Jsp') FROM DUAL;
Only up to integers from 1 to
5373484
----------------------------------------------------------------------------------------------
25. Query for deleting
alternate even rows FROM a table :
DELETE
FROM srinu
WHERE (ROWID,0) IN (SELECT
ROWID, MOD(ROWNUM,2)
FROM srinu);
----------------------------------------------------------------------------------------------
26. Query for deleting
alternate odd rows FROM a table :
DELETE
FROM srinu
WHERE (ROWID,1) IN (SELECT
ROWID, MOD(ROWNUM,2)
FROM srinu);
----------------------------------------------------------------------------------------------
Sekhar
27. Procedure for sending
Email :
CREATE OR REPLACE PROCEDURE
Send_Mail
IS
sender VARCHAR2(50) :=
'sender@something.com';
recipient VARCHAR2(50) :=
'recipient@something.com';
subject VARCHAR2(100) :=
'Test Message';
message VARCHAR2(1000) :=
'This is a sample mail ....';
lv_mailhost VARCHAR2(30) :=
'HOTNT002';
l_mail_conn
utl_smtp.connection;
lv_crlf VARCHAR2(2):= CHR( 13
) || CHR( 10 );
BEGIN
l_mail_conn :=
utl_smtp.open_connection (lv_mailhost, 80);
utl_smtp.helo ( l_mail_conn,
lv_mailhost);
utl_smtp.mail ( l_mail_conn,
sender);
utl_smtp.rcpt ( l_mail_conn,
recipient);
utl_smtp.open_data
(l_mail_conn);
utl_smtp.write_data (
l_mail_conn, 'FROM: ' || sender || lv_crlf);
utl_smtp.write_data (
l_mail_conn, 'To: ' || recipient || lv_crlf);
utl_smtp.write_data (
l_mail_conn, 'Subject:' || subject || lv_crlf);
utl_smtp.write_data (
l_mail_conn, lv_crlf || message);
utl_smtp.close_data(l_mail_conn);
utl_smtp.quit(l_mail_conn);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error');
END;
/
----------------------------------------------------------------------------------------------
28. Alternate Query for
DECODE function :
SELECT case
WHEN sex = 'm' THEN 'male'
WHEN sex = 'f' THEN 'female'
ELSE 'unknown'
END
FROM mytable;
----------------------------------------------------------------------------------------------
29. Create table adding
Constraint to a date field to SYSDATE or 3 months later:
CREATE TABLE srinu(dt1 date
DEFAULT SYSDATE, dt2 date,
CONSTRAINT check_dt2 CHECK
((dt2 >= dt1) AND (dt2 <= ADD_MONTHS(SYSDATE,3)));
Sekhar
----------------------------------------------------------------------------------------------
30. Query to list all the suppliers
who supply all the parts supplied by supplier 'S2' :
SELECT DISTINCT a.SUPP
FROM ORDERS a
WHERE a.supp != 'S2'
AND a.parts IN
(SELECT DISTINCT PARTS FROM
ORDERS WHERE supp = 'S2')
GROUP BY a.SUPP
HAVING
COUNT(DISTINCT a.PARTS) >=
(SELECT COUNT(DISTINCT PARTS)
FROM ORDERS WHERE supp = 'S2');
Table : orders
SUPP PARTS
-------------------- -------
S1 P1
S1 P2
S1 P3
S1 P4
S1 P5
S1 P6
S2 P1
S2 P2
S3 P2
S4 P2
S4 P4
S4 P5
----------------------------------------------------------------------------------------------
31. Query to get the last
Sunday of any month :
SELECT
NEXT_DAY(LAST_DAY(TO_DATE('26-10-2001','DD-MM-YYYY')) - 7,'sunday')
FROM DUAL;
----------------------------------------------------------------------------------------------
32. Query to get all those
who have no children themselves :
table data :
id name parent_id
-------------------------------
1 a NULL - the top level
entry
2 b 1 - a child of 1
3 c 1
4 d 2 - a child of 2
Sekhar
5 e 2
6 f 3
7 g 3
8 h 4
9 i 8
10 j 9
SELECT ID
FROM MY_TABlE
WHERE PARENT_ID IS NOT NULL
MINUS
SELECT PARENT_ID
FROM MY_TABlE;
----------------------------------------------------------------------------------------------
33. Query to SELECT last N
rows FROM a table :
SELECT empno FROM emp WHERE
ROWID in
(SELECT ROWID FROM emp
MINUS
SELECT ROWID FROM emp WHERE
ROWNUM <= (SELECT COUNT(*)-5 FROM emp));
----------------------------------------------------------------------------------------------
34. SELECT with variables:
CREATE OR REPLACE PROCEDURE
disp
AS
xTableName
varchar2(25):='emp';
xFieldName
varchar2(25):='ename';
xValue NUMBER;
xQuery varchar2(100);
name varchar2(10) := 'CLARK';
BEGIN
xQuery := 'SELECT SAL FROM '
|| xTableName || ' WHERE ' || xFieldName ||
' = ''' || name || '''';
DBMS_OUTPUT.PUT_LINE(xQuery);
EXECUTE IMMEDIATE xQuery INTO
xValue;
DBMS_OUTPUT.PUT_LINE(xValue);
END;
----------------------------------------------------------------------------------------------
35. Query to get the DB Name:
SELECT name FROM v$database;
Sekhar
----------------------------------------------------------------------------------------------
36. Getting the current
default schema :
SELECT
SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;
----------------------------------------------------------------------------------------------
37. Query to get all the
column names of a particular table :
SELECT column_name
FROM all_tab_columns
WHERE TABLE_NAME = 'ORDERS';
----------------------------------------------------------------------------------------------
38. How do I spool only the
query result to a file in SQLPLUS :
Place the following lines of
code in a file and execute the file in SQLPLUS :
set heading off
set feedback off
set colsep ' '
set termout off
set verify off
spool c:\srini.txt
SELECT empno,ename FROM emp;
/* Write your Query here */
spool off
/
----------------------------------------------------------------------------------------------
39. Query for getting the
current SessionID :
SELECT
SYS_CONTEXT('USERENV','SESSIONID') Session_ID FROM DUAL;
----------------------------------------------------------------------------------------------
40. Query to display rows
FROM m to n :
To display rows 5 to 7 :
SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE ROWID IN
(SELECT ROWID FROM EMP
WHERE ROWNUM <= 7
MINUS
Sekhar
SELECT ROWID FROM EMP
WHERE ROWNUM < 5);
OR
SELECT ename
FROM emp
GROUP BY ROWNUM, ename
HAVING ROWNUM > 1 and
ROWNUM < 3;
----------------------------------------------------------------------------------------------
41. Query to count no. Of
columns in a table:
SELECT COUNT(column_name)
FROM user_tab_columns
WHERE table_name = 'MYTABLE';
----------------------------------------------------------------------------------------------
42. Procedure to increase the
buffer length :
dbms_output.enable(4000);
/*allows the output buffer to be increased to the specified number
of bytes */
DECLARE
BEGIN
dbms_output.enable(4000);
FOR i IN 1..400
LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/
----------------------------------------------------------------------------------------------
43. Inserting an & symbol
in a Varchar2 column :
Set the following to some
other character. By default it is &.
set define '~'
----------------------------------------------------------------------------------------------
44. How do you remove Trailing blanks in a spooled
file :
Sekhar
Change the Environment
Options Like this :
set trimspool on
set trimout on
----------------------------------------------------------------------------------------------
45. Samples for executing
Dynamic SQL Statements :
Sample :1
CREATE OR REPLACE PROCEDURE
CNT(P_TABLE_NAME IN VARCHAR2)
AS
SqlString VARCHAR2(200);
tot number;
BEGIN
SqlString:='SELECT COUNT(*)
FROM '|| P_TABLE_NAME;
EXECUTE IMMEDIATE SqlString
INTO tot;
DBMS_OUTPUT.PUT_LINE('Total
No.Of Records In ' || P_TABLE_NAME || ' ARE=' || tot);
END;
Sample :2
DECLARE
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) :=
’PERSONNEL’;
location VARCHAR2(13) :=
’DALLAS’;
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE 'CREATE
TABLE bonus (id NUMBER, amt NUMBER)';
sql_stmt := 'INSERT INTO dept
VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt
USING dept_id, dept_name, location;
sql_stmt := 'SELECT * FROM
emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt
INTO emp_rec USING emp_id;
plsql_block := 'BEGIN
emp_pkg.raise_salary(:id, :amt); END;';
EXECUTE IMMEDIATE plsql_block
USING 7788, 500;
sql_stmt := 'UPDATE emp SET
sal = 2000 WHERE empno = :1
RETURNING sal INTO :2';
EXECUTE IMMEDIATE sql_stmt
USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE 'DELETE
FROM dept WHERE deptno = :num'
USING dept_id;
Sekhar
EXECUTE IMMEDIATE ’ALTER
SESSION SET SQL_TRACE TRUE’;
END;
Sample 3
CREATE OR REPLACE PROCEDURE
DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor :=
DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor,
'select dname from dept where deptno > :x',
DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor,
':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor,
1, v_dname, 20);
v_rows :=
DBMS_SQL.EXECUTE(v_cursor);
LOOP
IF
DBMS_SQL.FETCH_ROWS(v_cursor) = 0 THEN
EXIT;
END IF;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor,
1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment
name: '||v_dname);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000,
'Unknown Exception Raised: '||sqlcode||'
'||sqlerrm);
END;
----------------------------------------------------------------------------------------------
46.Differences between SQL
and MS-Access :
Difference 1:
Oracle : select name from
table1 where name like 'k%';
Access: select name from
table1 where name like 'k*';
Difference 2:
Access: SELECT TOP 2 name
FROM Table1;
Oracle : will not work there
is no such TOP key word.
----------------------------------------------------------------------------------------------
47. Query to display all the
children, sub children of a parent :
SELECT organization_id,name
FROM
hr_all_organization_units
WHERE organization_id in
Sekhar
(
SELECT ORGANIZATION_ID_CHILD
FROM PER_ORG_STRUCTURE_ELEMENTS
CONNECT BY PRIOR
ORGANIZATION_ID_CHILD =
ORGANIZATION_ID_PARENT
START WITH
ORGANIZATION_ID_CHILD =
(SELECT organization_id
FROM
hr_all_organization_units
WHERE name = 'EBG Corporate
Group'));
----------------------------------------------------------------------------------------------
48. Procedure to read/write
data from a text file :
CREATE OR REPLACE PROCEDURE
read_data
AS
c_path varchar2(100) :=
'/usr/tmp';
c_file_name varchar2(20) :=
'EKGSEP01.CSV';
v_file_id utl_file.file_type;
v_buffer varchar2(1022) :=
This is a sample text’;
BEGIN
v_file_id :=
UTL_FILE.FOPEN(c_path,c_file_name,'w');
UTL_FILE.PUT_LINE(v_file_id,
v_buffer);
UTL_FILE.FCLOSE(v_file_id);
v_file_id :=
UTL_FILE.FOPEN(c_path,c_file_name,'r');
UTL_FILE.GET_LINE(v_file_id,
v_buffer);
DBMS_OUTPUT.PUT_LINE(v_buffer);
UTL_FILE.FCLOSE(v_file_id);
END;
/
----------------------------------------------------------------------------------------------
49. Query to display random
number between any two given numbers :
SELECT DBMS_RANDOM.VALUE
(1,2) FROM DUAL;
----------------------------------------------------------------------------------------------
50. How can I get the time
difference between two date columns :
SELECT
FLOOR((date1-date2)*24*60*60)/3600)
|| ' HOURS ' ||
FLOOR((((date1-date2)*24*60*60)
-
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
ROUND((((date1-date2)*24*60*60)
-
FLOOR(((date1-date2)*24*60*60)/3600)*3600
-
Sekhar
(FLOOR((((date1-date2)*24*60*60)
-
FLOOR(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))
|| ' SECS ' time_difference
FROM my_table;
----------------------------------------------------------------------------------------------
51. Using INSTR and SUBSTR
I have this string in a
column named location
LOT 8 CONC3 RR
Using instr and substr, I
want to take whatever value follows LOT and put
it into a different column
and whatever value follows CONC and put it into
a different column
select substr('LOT 8 CONC3
RR',4,instr('LOT 8 CONC3 RR','CONC')-4) from
dual;
select substr('LOT 8 CONC3
RR',-(length('LOT 8 CONC3 RR')-(instr('LOT 8
CONC3 RR','CONC')+3)))
from dual
----------------------------------------------------------------------------------------------
52. View procedure code
select text from all_source
where name = 'X'
order by line;
select text from user_source
where name = 'X'
select text from user_source
where type = 'procedure' and
name='procedure_name';
Sekhar
select name,text from
dba_source where name='ur_procedure'
and owner='scott';
----------------------------------------------------------------------------------------------
53. To convert signed number
to number in oracle
select
to_number('-999,999.99', 's999,999.99') from dual; -999,999.99
select to_number('+0,123.45',
's999,999,999.99') from dual; 123.45
select
to_number('+999,999.99', 's999,999.99') from dual; 999,999.99
----------------------------------------------------------------------------------------------
54. Columns of a table
select column_name from
user_tab_columns where TABLE_NAME = 'EMP'
select column_name from
all_tab_columns where TABLE_NAME = 'EMP'
select column_name from
dba_tab_columns where TABLE_NAME = 'EMP'
select column_name from cols
where TABLE_NAME = 'EMP'
----------------------------------------------------------------------------------------------
55. Delete rows conditionally
I have a table have
a,b,c field,
a,b should be unique, and
leave max(c) row in.
How can I delete other rows?
delete from 'table'
where (a,b,c) not in (select
a,b,max(c) from 'table' group by a,b);
----------------------------------------------------------------------------------------------
56. CLOB to Char
1) This function helps if
your clob column value not exceed 4000 bytes
(varchar2 limit).if clob
column's data exceeds 4000 limit, you have to
follow different approach.
create or replace function
lob_to_char(clob_col clob) return varchar2 IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
Sekhar
pos INTEGER := 1;
l clob;
bfils bfile;
l_var varchar2(4000):='';
begin
LOOP
if
dbms_lob.getlength(clob_col)<=4000 THEN
dbms_lob.read (clob_col, amt,
pos, buffer);
l_var := l_var||buffer;
pos:=pos+amt;
ELSE
l_var:= 'Cannot convert to
varchar2..Exceeding varchar2 field
limit';
exit;
END IF;
END LOOP;
return l_var;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return l_var;
END;
2) CREATE GLOBAL TEMPORARY
TABLE temp_tab(id number,varchar_col
varchar2(4000));
SQL> var r refcursor
SQL> exec
lobpkg.lob_to_char(:r);
SQL> print r
create or replace package
lobpkg is
type ref1 is ref cursor;
n number:=0;
PROCEDURE lob_to_char(rvar IN
OUT lobpkg.ref1) ;
end;
/
create or replace package
body lobpkg is
PROCEDURE lob_to_char(rvar IN
OUT lobpkg.ref1) IS
buffer varchar2(4000);
amt BINARY_INTEGER := 4000;
pos INTEGER := 1;
l clob;
r lobpkg.ref1;
bfils bfile;
l_var varchar2(4000):='';
CURSOR C1 IS SELECT * FROM
clob_tab;
-- change clob_tab to
your_table_name
Sekhar
begin
n:=n+1;
FOR crec IN c1 LOOP
amt:=4000;
pos:=1;
BEGIN
LOOP
--change crec.clob_col to
crec.your_column_name
dbms_lob.read (crec.clob_col,
amt, pos, buffer);
--change next line if you
create temporary table with different name
insert into temp_tab values
(n,buffer);
pos:=pos+amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
--change next line if you
create temporary table with different name
open rvar for select vchar
from temp_tab where id=n;
END;
END;
----------------------------------------------------------------------------------------------
57. Change Settings
Open file
oracle_home\plus32\glogin.sql and
add this
set linesize 100
set pagewidth 20
and save the file
and exit from sql and reload
then it will set it.
Sekhar
----------------------------------------------------------------------------------------------
58. Double quoting a Single
quoted String
declare
-- we need one here to get a
single quote into the variable
v_str varchar2 (20) :=
'O''reilly''s';
begin
DBMS_OUTPUT.PUT_LINE (
'original single quoted v_str= ' || v_str );
v_str := replace(v_str, '''',
'''''');
DBMS_OUTPUT.PUT_LINE ( 'after
double quoted v_str= ' || v_str );
end;
SQL> /
original single quoted v_str=
O'reilly's
after double quoted v_str=
O''reilly''s
----------------------------------------------------------------------------------------------
59. Time Conversion
CREATE OR REPLACE FUNCTION
to_hms (i_days IN number)
RETURN varchar2
IS
BEGIN
RETURN TO_CHAR (TRUNC
(i_days)) || ' days ' ||
TO_CHAR (TRUNC (SYSDATE) +
MOD (i_days, 1), 'HH24:MI:SS');
END to_hms;
select
to_hms(to_date('17-Jan-2002 13:20:20', 'dd-Mon-yyyy hh24:mi:ss') -
to_date('11-Jan-2002
11:05:05', 'dd-Mon-yyyy hh24:mi:ss')) from
dual;
----------------------------------------------------------------------------------------------
60. Table comparison
The table in both the schemas
should have exactly the same structure. The data in
it could be same or different
a-b and b-a
select * from a.a minus
select * from b.a and select * from b.a minus select * from a.a
----------------------------------------------------------------------------------------------
61. Running Jobs
Sekhar
select * from user_jobs;
exec dbms_job.remove(job_no);
----------------------------------------------------------------------------------------------
62. Switching Columns
Update tblname
Set column1 = column2,
Column2 = column1;
----------------------------------------------------------------------------------------------
63. Replace and Round
I have the number e.g.
63,9823874012983 and I want to round it to 63,98 and at the
same time change the , to a .
select
round(replace('63,9823874012983',',','.'),2) from dual;
----------------------------------------------------------------------------------------------
64. First date of the year
select trunc(sysdate, 'y')
from dual;
01-jan-2002
last year this month through
a select statement
select add_months(sysdate,
-12) from dual;
05-APR-01
----------------------------------------------------------------------------------------------
65. Create Sequence
create sequence sh increment
by 1 start with 0;
----------------------------------------------------------------------------------------------
66. Cursors
cursor is someting like
pointers in C language.
u fetch the data using
cursor.( wiz...store it somewhere temporarily). u
can do any manipulation to
the data that is fetched by the cursor. like
trim, padd, concat or
validate. all this are done in temporary areas called
as context area or the cursor
area. u can insert this data again in some
other table or do anything u
want!!...like setting up some flags etc.
U can display the contents of
cursor using the dbms_output only. U can
create an anonymous plsql
block or a stored procedure. the major advantage
Sekhar
of cursors is that you can
fetch more thatn one row and u can loop through
the resultset and do the
manupulations in a secure manner.
set serveroutput on;
declare
cursor c1 is select * from
emp;
begin
for var in c1 loop
exit when c1%notfound;
dbms_output.put_line('the
employee' || var.ename ||'draws a
salary of '||
var.sal);
end loop;
end;
----------------------------------------------------------------------------------------------
67. Current Week
select
next_day(sysdate-7,'SUNDAY'), next_day(sysdate,'SATURDAY') from dual;
NEXT_DAY( NEXT_DAY(
--------- ---------
07-APR-02 13-APR-02
No comments:
Post a Comment