Oracle Support HOT topics

Jaffar's Oracle blog: Subscribe Oracle Support HOT topics- stay in touch...: Subscribing the ' Oracle Support HOT Topics '  to receive an email notification about Oracle latest Bugs, Knowledge articles, Product New...

Basic SQL interview questions

  Basic SQL interview questions

How to display row number with records?

Select rownum, ename from emp;

How to view version information in Oracle?

Select banner from v$version;

How to find the second highest salary in emp table?

select min(sal) from emp a
where 1 = (select count(*) from emp b where a.sal < b.sal) ;

How to delete the duplicate rows from a table?

create table t1 ( col1 int, col2 int, col3 char(1) );
insert into t1 values(1,50, ‘a’);
insert into t1 values(1,50, ‘b’);
insert into t1 values(1,89, ‘x’);
insert into t1 values(1,89, ‘y’);
insert into t1 values(1,89, ‘z’);
select * from t1;

Col1	Col2	Col2
1	50	a
1	50	b
2	89	x
2	89	y
2	89	z

delete from T1
where rowid <> ( select max(rowid)
from t1 b
where b.col1 = t1.col1
and b.col2 = t1.col2 ) 3 rows deleted.

select * from t1;

Col1	Col2	Col2
1	50	a
2	89	z

will do it.

How to select a row using indexes?

You have to specify the indexed columns in the WHERE clause of query.

How to select the first 5 characters of FIRSTNAME column of EMP table?

select substr(firstname,1,5) from emp

How to concatenate the firstname and lastname from emp table?

select firstname ‘ ‘ lastname from emp

What's the difference between a primary key and a unique key?

Primary key does not allow nulls, Unique key allow nulls.

What is a self join?

A self join joins a table to itself.

Example

SELECT a.last_name Employee, b.last_name Manager
FROM employees a, employees b
WHERE b.employee_id = a.manager_id;

What is a transaction and ACID?

Transaction - A transaction is a logical unit of work. It must be commited or rolled back.
ACID - Atomicity, Consistency, Isolation and Duralbility, these are properties of a transaction.

How to add a column to a table?

alter table t1 add sal number;
alter table t1 add middle_name varchar(20);
Is it possible for a table to have more than one foreign key ?
A table can have any number of foreign keys. It can have only one primary key .

How to display number value in words?

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;

What is candidate key, alternate key, composite key.

Candidate Key A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table.
Alternate KeyIf the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
Composite Key: - A key formed by combining at least two or more columns is called composite key.

What's the difference between DELETE TABLE and TRUNCATE TABLE commands? Explain drop command.

Both Delete and Truncate will leave the structure of the table. Drop will remove the structure also.

1. 
   

   Example

   If tablename is T1.
   To remove all the rows from a table t1.
   Delete t1
   Truncate table t1
   Drop table t1.
2. Truncate is fast as compared to Delete. DELETE will generate undo information, in case of rollback, but TRUNCATE will not.
3. Full Table scan and index fast scan read data blocks up to high water mark and truncate resets high water mark but delete does not.So full table scan after Delete will not improve but after truncate it will be fast.
4. Delete is DML. Because truncate is a DDL, it performs implicit commit. You cannot rollback a truncate. Any uncommitted DML changes will also be committed with the TRUNCATE.
5. You cannot specify a WHERE clause in the TRUNCATE statement, but you can specify that in Delete.
6. When you truncate a table the storage for the table and all the indexes can be reset back to its initial size,but a Delete will never shrink the size of the a table or its indexes.

About Dropping
Dropping a table removes the data and definition of the table. The indexes, constraints, triggers, and privileges on the table are also dropped. The action of dropping a table cannot be undone. The views, materialized views or other stored programs that reference the table are not dropped but they are marked as invalid.

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

Procedures and functions are stored in compiled form in database.
Functions take zero or more parameters and return a value. Procedures take zero or more parameters and return no values.
Both functions and procedures can take or return zero or more values through their parameter lists.
Another difference between procedures and functions, other than the return value, is how they are called. Procedures are called as stand-alone executable statements:
my_procedure(parameter1,parameter2...);
Functions can be called anywhere in an valid expression :
e.g
1) IF (tell_salary(empno) < 500 ) THEN … 2) var1 := tell_salary(empno); 3) DECLARE var1 NUMBER DEFAULT tell_salary(empno); BEGIN …
Packages contain function , procedures and other data structures.
There are a number of differences between packaged and non-packaged PL/SQL programs. Package The data in package is persistent for the duration of the user’s session.The data in package thus exists across commits in the session.
If you grant execute privilege on a package, it is for all functions and procedures and data structures in the package specification. You cannot grant privileges on only one procedure or function within a package. You can overload procedures and functions within a package, declaring multiple programs with the same name. The correct program to be called is decided at runtime, based on the number or datatypes of the parameters.

Describe the use of %ROWTYPE and %TYPE in PL/SQL

%ROWTYPE associates a variable to an entire table row.
The %TYPE associates a variable with a single column type.

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the current database error number. These error numbers are all negative, except NO_DATA_FOUND, which returns +100.
SQLERRM returns the textual error message.. These are used in exception handling. 

Q)How can you find within a PL/SQL block, if a cursor is open?
By the Use of %ISOPEN cursor variable. 
Q)How do you debug output from PL/SQL?
By the use the DBMS_OUTPUT package.
By the use of SHOW ERROR command, but this only shows errors.
The package UTL_FILE can also be used. 
Q)What are the types of triggers?

Use Row and Statement Triggers

Use INSTEAD OF Triggers.

Q)Explain the usage of WHERE CURRENT OF clause in cursors ? It refers to the latest row fetched from a cursor in an update and delete statement. Name the tables where characteristics of Package, procedure and functions are stored ? User_objects, User_Source and User_error.

What are two parts of package ?

They consist of package specification, which contains the function headers, procedure headers, and externally visible data structures. The package also contains a package body, which contains the declaration, executable, and exception handling sections of all the bundled procedures and functions.

What are two virtual tables available during database trigger execution ?

The table columns are referred as OLD.column_name and NEW.column_name.
For INSERT only TRIGGERS NEW.column_name values ARE only available.
For UPDATE only TRIGERS OLD.column_name NEW.column_name values ARE only available.
For DELETE only TRIGGERS OLD.column_name values ARE only available.v

What is Overloading of procedures ?

REPEATING OF SAME PROCEDURE NAME WITH DIFERENT PARAMETER LIST.

What are the return values of functions SQLCODE and SQLERRM ?

SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.

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

It is not possible.,because of the side effect to transactions. You can use them indirectly by calling procedures or functions .

What are the modes of parameters that can be passed to a procedure ?

IN, OUT, IN-OUT parameters.

What's SQL

SQL is an English like language consisting of commands to store, retrieve, maintain & regulate access to your database.
What's SQL*Plus
SQL*Plus is an application that recognizes & executes SQL commands & specialized SQL*Plus commands that can customize reports, provide help & edit facility & maintain system variables.
What's NVL
NVL : Null value function converts a null value to a non-null value for the purpose of evaluating an expression. Numeric Functions accept numeric I/P & return numeric values. They are MOD, SQRT, ROUND, TRUNC & POWER.
What's Date Functions
Date Functions are ADD_MONTHS, LAST_DAY, NEXT_DAY, MONTHS_BETWEEN & SYSDATE.
What's Character Functions
Character Functions are INITCAP, UPPER, LOWER, SUBSTR & LENGTH. Additional functions are GREATEST & LEAST. Group Functions returns results based upon groups of rows rather than one result per row, use group functions. They are AVG, COUNT, MAX, MIN & SUM.
What's TTITLE & BTITLE
TTITLE & BTITLE are commands to control report headings & footers.
What's COLUMN
COLUMN command define column headings & format data values.
What's BREAK
BREAK command clarify reports by suppressing repeated values, skipping lines & allowing for controlled break points.
What's COMPUTE
command control computations on subsets created by the BREAK command.
What's SET
SET command changes the system variables affecting the report environment.
What's SPOOL
SPOOL command creates a print file of the report.
What's JOIN
JOIN is the form of SELECT command that combines info from two or more tables.
Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.
Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.
Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause. Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table. Self Join joins a table to itself as though it were two separate tables.
What's Union
Union is the product of two or more tables.
What's Intersect
Intersect is the product of two tables listing only the matching rows.
What's Minus
Minus is the product of two tables listing only the non-matching rows.
What is a Correlated Subquery ?
Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.
What are Multiple columns ?
Multiple columns can be returned from a Nested Subquery.
What are Sequences ?
Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.
What are Synonyms ?
Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.  Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.

1. what is the difference between database trigger and application trigger?
2. what are the file utilit comands used in PL/SQL procedures?
3. what is a cluster and what is the real time use and business reasons to use Clustering
4. In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?
5. what is dense_rank function and it's usage ?
6. What is HIGH WATERMARK?I got to know that it is reset when the TRUNCATE command is executed on a table.
7. explian rowid,rownum?what are the psoducolumns we have?
8. 1)what is the starting "oracle error number"?2)what is meant by forward declaration in functions?
9. what is the difference between database server and data dictionary
10. Hi,Can anyone tell me the difference between instead of trigger, database trigger, and schema trigger?Thanks.
11. what is difference b/w stored procedures and application procedures,stored function and application function..
12. State the difference between implict and explict cursor's
13. what is pragma? can any one give me the example of autonomous Transaction ?can we change the order of procedure parameter while calling procedure?
14. What is the data type of Null?
15. What is autonomous Transaction? Where are they used?
16. Details about FORCE VIEW why and we can use
17. How can one view all the procedures,functions,triggers and packages created by the user
18. What is the difference between User-level, Statement-level and System-level Rollback? Can you please give me example of each?
19. What happens when DML Statement fails?A.User level rollbackB.Statement Level RollbackC.Sustem Level Rollback
20. Why Functions are used in oracle ?Can Functions Return more than 1 values?Why Procedures are used in oracle ?What are the Disadvantages of packages?What are the Global Variables in Packages?
21. Is there any limitation on no. of triggers that can be created on a table?
22. what is p-code and sourcecode ?
23. What are ref cursors ?
24. Which type of binding does PL/SQL use?
25. Talk about "Exception Handling" in PL/SQL?
26. What are the return values of functions SQLCODE and SQLERRM ?
27. What are advantages fo Stored Procedures / Extensibility,Modularity, Reusability,&
What are advantages fo Stored Procedures / Extensibility,Modularity, Reusability, Maintainability and one time compilation.
28. What are two parts of package ?
29. What is Overloading of procedures ?
30. What are the modes of parameters that can be passed to a procedure ?
31. What is difference between a PROCEDURE & FUNCTION ?
32. What is a stored procedure ?
33. Where the Pre_defined_exceptions are stored ?
34. What is Raise_application_error ?
35. What is Pragma EXECPTION_INIT ? Explain the usage ?
36. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
37. Explain the usage of WHERE CURRENT OF clause in cursors ?
38. What is a cursor for loop ?
39. What is nested table in Oracle and and difference between table and nested table
40. What are the components of a PL/SQL block ?
41. What do you mean by OCI, Data guard and Advance queue responsibilities for a Oracle developers?
42. How do you encrypt the function to prevent accessing from users without specific permission. ?
43. HI,What is Flashback query in Oracle9i...?
44. Oracle refcursor and procedure
45. Convert SQL to Oracle Procedure using cursor
46. I want to insert the following information in userAction table:Which user execute which query on which date?the userAction table contains the foolowing attributes:USER DATE QUERYplease write to me how to resolve this problem?
47. what are purity rules for functions? why they use ? what effects if not follow these rules?
48. In function and procedure the parameter p***is "call by value" or "call by referenc
In function and procedure the parameter p***is "call by value" or "call by reference"?
49. What can be the Maximum size of a plsql block?
50. Compare EXISTS and IN Usage with advantages and disadvantages.
51. Which two statements are true?A. A function must return a value.B. A procedure must return a value.C. A function executes a PL/SQL statement.D. A function is invoked as part of an expression.E. A procedure must have a return Data
52. Oracle extract records from temporary table
53. Pragma Init Exception
54. What are the disadvantages of Packages and triggers??
55. Hi, How do we display the column values of a table using cursors without knowing the column names inside the loop?
56. What will happen after commit statement ?
57. What is PL/SQL ?
58. 1.How to display current Date & Time in Pl/Sql2.How to use DML in Procedure?
59. How do you call procedure have a DDL or commit/rollback statement from a trigger?
60. Oracle Cursor types
61. Suppose I have 2 triggers on table T, tr1- a before insert trigger & tr2- a before update trigger.tr1 has update (T) statement inside body of tr1andtr2 has insert (T) statement inside body of tr2Now, I'm tring to insert a row into T.What will hppn??
62. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
63. what is diffrence between IS and AS in procedure?
64. Hi Friends!! Can anybody answer what are the constraints on Mutating tables? How to remove the mutating errors in triggers?
65. What are mutating tables?
66. Write sample code that can create a hierachical set of data without using a start with and connect by clause in PL/SQL
67. how can i import .dmp file in lower version of oracle from higher version ?
68. how can i get set identity for last coloumn of the table.
69. What is the basic structure of PL/SQL ?
70. you have compiled some PL/SQL packages in your schema, and found aome errors in one procedure.how do you find which procedure produced the error?how do you find which section of the code produced the error and look at?
71. char(20) = 'name' varchar2(20)='name' When comparing these two values, are
char(20) = 'name' varchar2(20)='name' When comparing these two values, are the spaces padded in char are considered or not? Are both values equal?
72. What is the difference between right join and right outer join..
73. What is the disadvantage of out paramter in functions
74. What is the need for using function purity in pl/sql
75. What is the difference between using IS and AS while creating a procedure, function package and package body?
76. What are the restrictions on Functions ?
77. What is PL/SQL table? SNO MARK ------- ------------------1 592 403 ‘A’4 60 Write a single query to I) Sorted Marks II)First mark III) replace the mark ‘A’ with 0(zero)?
78. Without closing the cursor, If you want to open it what will happen. If error, get what is the error?
79. What are the components of a PL/SQL Block ?
80. Is it possible to use commit or rollback in exception section.
81. Why DUAL table is not visible?
82. What are the PL/SQL Statements used in cursor processing ?
83. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
84. how can we avoid duplicate rows. without using distinct command
85. can procedures have parameters
86. How to return more than one value from a function?What are the types of triggers?What are the features?
How to return more than one value from a function?What are the types of triggers?What are the features of oracle 9i
87. How can I speed up the execution of query when number of rows in the tables increased
88. 1.What is bulk collect?2.What is instead trigger3.What is the difference between Oracle table & PL/SQL table?4.What R built in Packages in Oracle?5.what is the difference between row migration & row changing?
89. what is diff between strong and weak ref cursors
90. 1)any one can tell me,suppose we have 1000 of records,ok.then we want to update only 500 records,how can we solve this problem?2)how many types of "explicit cursors" we have?
91. why do we need to create a force view?what is its purpose?give some examples?
92. What is a cursor ? Why Cursor is required ?
93. What is materialized view?
94. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
95. What will happen to an anonymus block,if there is no statement inside the block?eg:-declarebeginend;
96. Can we have same trigger with different names for a table?eg: create trigger trig1after insert on tab1;andeg: create trigger trig2after insert on tab1;If yes,which trigger executes first.
97. difference between truncate and delete
98. What are the two parts of a procedure ?
99. How to reduce the the burden/main memory on database if i am using refcursor to hold large data to increase performance.
100. Name the tables where characteristics of Package, procedure and functions are stored ?
101. How packaged procedures and functions are called from the following?
102. Explain how procedures and functions are called in a PL/SQL block ?
103. How many types of database triggers can be specified on a table ? What are they ?
104. What will be the impact of replacing an API call with a stored PL/SQL call?
105. How PL SQL is different from T-SQL
106 > Select Count(*) from T1 where a=10 3> Select count(*) from T1 where b=20 11Now, What will b the O/P of the following..select count(*) from T1 where a=10 or b=20..............................
107. What is the purpose of FORCE while creating a VIEW
108. What is an Exception ? What are types of Exception ?
109. What is the output of the following pl/sql block ?declare v_empno emp.empno%type;begin select empno into v_empno from emp where empno = 10;exception when others then dbms_output.put_line ( 'no data found'); when no_data_found then dbms_output.put_line ( 'ther is no data found ');end;
110. Explain the two type of Cursors ?
111. What is the difference between private packages and public package . what is the difference in declaration of these 2 packages.
112. how to avoid the mutating error with sample program
113. Give the structure of the procedure ?
114. Explain about CURSOR and REF CURSUR with real time scenario where this can be used.
115. When using a count(distinct) is it better to use a self-join or temp table to find redundant data, and provide an example?
116. How do you set table for read only access ?
117. what is the use of nocopy parameter in oracle procedure
118. What is CODEX function?
119. can we use commit in trigger and cursors?
120. Why we use instead of trigger. what is the basic structure of the instead of trigger. Explain specific business reason of it's use
121. how to create a constraint for a tablecolumn which is already created
122. How to disable a trigger for a particular table ?
123. Force View
124. How do you debug the PL/SQL ?
125. What is mutatinig trigger? How do you avoid mutating trigger?
126. What is a purity level? How it is should be taken into consideration when your writing any database objects i.e., trigger,function, procedure etc.,
127. how to trace the errors in pl/sql block code..
128. How to get the 25th row of a table.
129. if there is an index including three columns A, B and C. And if we issue a query in which where clause uses only column B....will the index be useful??and what if the where clause only has coulmn A..will the index b useful??
130. What is difference between PL/SQL tables and arrays?
131. What is the use of NOCOPY Compiler Hint while writing PL/SQL procedures/subprograms???
132. Select from A table through cursor and update B table. If it updates successfully then insert into another table. Handled every type of exception in the code?
133. Is it possible create table in procedure or function? If Not Why?
134. what are the advantages & disadvantages of packages ?
135. What is PRAGMA RESTRICT_REFERENCES:
136. Suppose thr are 10 DMLs(insert,update,delete ) in the main section of the PL/SQL block .The exception in them is handled as a whole in the exception handling section .....The error may occur in any of this DMLs ,so how can we understand that which DML has failed ??
137. What are the advantages and disadvantages of DBMS-SQL
138. how to insert a music file into the database
139. What is Atomic transaction?
140. what is the order of execution if there is a statement level and row level trigger on a same table?
141. Explain, Is it possible to have same name for package and the procedure in that package.
142. How to trace PL/SQL Package?How to trace PL/SQL procedures?How to trace SQL statement?what is DBMS_TRACE? How to use?SET AUTOTRACE ON; ?If anyone tell me how we can use trace and create log that would be great?
143i want to tune the below query for performance issue can u please help me the query is SELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_ITEM_ACTIVITY A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%' AND A.PO_LINE = 0AND A.ENTRY_REASON 'CLEAR'AND A.ASOF_DT > '01-JAN-1900'AND A.USER1 = ' 'UNIONSELECT DISTINCTA.BUSINESS_UNIT,A.CUST_ID,A.ASOF_DTFROM PS_PENDING_ITEM A WHERE A.BUSINESS_UNIT = '1100G'AND A.ITEM LIKE 'OA%&
144. HiWhile creating a table, what is the difference between VARCHAR2(80) and VARCHAR2(80 BYTE)?
145. How can i see the time of execution of a sql statement?
146. what happens when commit is given in executable section and an error occurs ?please tell me what happens if exception block is committed at the last?
147. What are the Limitations of Packages,views,procedures?What is the maximum number of subprograms inside a package?
148. what is difference between varray and nested table.can u explain in brief and clear my these concepts.also give a small and sweet example of both these.
149. Wheather a Cursor is a Pointer or Reference?
150. How to find the nth hightest record holder from a table
151. What is the difference between In, Out, InOut Parameters. Can we p***value or reference or both to the In Out Parameter.
152. What is a NOCOPY parameter? Where it is used?
153. What is PL/SQL table ?
155. Can we create a table using with Procedure or Function?wat is the Mutating trigger error?
156. Can e truncate some of the rows from the table instead of truncating the full table.
157. What are the Restrictions on Cursor Variables?Thanks Ramki, Hyd, TCS
158. How to change owner of a table?
159. Mention the differences between aggregate functions and analytical functions clearly with examples?
160. how can u create session variable in pakages?
161. How can I create a new table by using other two table's values.
162. what is the diff between %Rowtype and %type?
163. what is the difference between database trigger and schema trigger?
164. How to avoid using cursors? What to use instead of cursor and in what cases to do so?
165. How to disable multiple triggers of a table at at a time?
166 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;
167. Can we use commit or rollback command in the exception part of PL/SQL block?
168. Suppose, I've created a new database DB1 n i've created a table DB1.T1.Now, DESC T1 --> d
Suppose, I've created a new database DB1 n i've created a table DB1.T1.Now, DESC T1 --> desplaying the table structure butselect * from DB1.T1 ---->giving--> table or view does not exist.. Can any one explain possible reason behind this.
169. What are the datatypes a available in PL/SQL ?
170. can i change the elements of listitems at runtimes?
171. Give the structure of the function ?
172. pls send the interview qustions from pl/sql, sql, datawarehousing questions.
173. What is the difference between a reference cursor and normal cursor ?
174. How to view the contents of tables created by the following procedure after the Loop?CREATE OR REPLACE PROCEDURE A0_BULK_COLLECT_TEST IS TYPE EMPLOYEE_MRNO IS TABLE OF A_REGISTRATION_HEADER.ARH_MR_NUM%TYPE; TYPE EMPLOYEE_NAME IS TABLE OF VARCHAR2(255); MRNUMBERS EMPLOYEE_MRNO; NAMES EMPLOYEE_NAME; CURSOR crBulkCollect IS SELECT ARH_MR_NUM, ARH_FIRST_NAME||' '||ARH_MIDDLE_NAME||' '||ARH_LAST_NAME FROM A_REGISTRATION_HEADER WHERE ARH_CTGRY_CD='EMP';BEGIN
175. what is difference between Cursor and Ref Cursor. Please give example.
176. State the advatage and disadvantage of Cursor's
177. can we declare a column having number data type and its scale is larger than pricesionex: column_name NUMBER(10,100), column_name NUMBAER(10,-84)
178. what is datatype of x when we say define x in oracle
179. 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...
180. Can any one explain Perforance Tuning in PL/SQL
181. How to display the contents of a current record fetched in a ref cursor
182. How to handle exception in Bulk collector?
183. What is the DATATYPE of PRIMARY KEY?is it Binary integer..i'm not sure..1.Varchar22.Char3.Binary integer4.Number
184. In a Distributed Database System Can we execute two queries simultaneously ? Justify ?
185. #1 What are the advantages and disadvantages of using PL/SQL or JAVA as the primary programming tool for database automation.#2 Will JAVA replace PL/SQL?
186. Write the order of precedence for validation of a column in a table ?
187. 1) Why it is recommonded to use INOUT instead of OUT parameter type in a procedure?2) What happen if we will not assign anything in OUT parameter type in a procedure?
188. What is Mutation of a trigger? why and when does it oocur?
189. can anybody tell me a sample OCI function which will be able to call from Tourbo cthanx!!
190. we have a trigger on data base.in the trigger body we have created a body using dbms_output.put_line(********) ;this should be firedwhen ever trigger executed;
191. What is PL/Sql tables?Is cursor variable store in PL/SQL table?
192. What type of binding is PL/SQL?
193. What steps should a programmer should follow for better tunning of the PL/SQL blocks?Difference between procedure and function?What is the use of ref cursor return type?
194. Based on what conditions can we decide whether to use a table or a view or a materialized view ?
195. What is Data Concarency and Consistency?
196. What is bulk binding please explain me in brief ?
197. What is the difference between all_ and user_ tables ?
198. what is crosstab
199. can i write plsql block inside expection
200. What is a database trigger ? Name some usages of database trigger ? Subscribe
Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications,&n
201. Describe in brief some of the featurs of oracle9i.What is LogMiner?
202. What happens when a package is initialized ?
203. What are the cursor attributes used in PL/SQL ?
204. What is difference between % ROWTYPE and TYPE RECORD ?
205. What are two virtual tables available during database trigger execution ? 

Relational Database Model - Codd's Rules - Part 1

Cool Database Fundas: Relational Database Model - Codd's Rules - Part 1: The Background - Dr. Edgar Frank Codd (1923-2003) introduced the "Relational Model" for database management systems in 1970 in a research...

Relational Database Model - Codd's Rules - Part 2

Cool Database Fundas: Relational Database Model - Codd's Rules - Part 2: This is in continuation to the last post " Relational Database Model - Codd's Rules - Part 1 " The following is explanation of the remain...

IT Certifications - How much they're worth

Cool Database Fundas: IT Certifications - How much they're worth: The Background - This time I am touching a seemingly non-technical topic, but important one; at times controversial and almost always lead...

Distributed Database System - Introduction

Cool Database Fundas: Distributed Database System - Introduction: Introduction - Today's business environment has an increasing need for distributed databases as the desire for reliable, scalable and acc...

Distributed Database System - Principles

Cool Database Fundas: Distributed Database System - Principles: Please spend a moment to read the previous post " Distributed Database System - Introduction " as the text below may contain some referenc...

Transaction Concurrency

Cool Database Fundas: Transaction Concurrency: Introduction - Consistent read / write operation while maximising the concurrent access is the key challenge in developing multi-user d...

Transaction Locks

Cool Database Fundas: Transaction Locks: In a previous post we discussed about why we need our databases to allow for concurrent transactions and the related problems and differe...

Database Basics

Cool Database Fundas: Database Basics: DataBase Management Systems (DBMS) - Let us first understand the meaning of this overall term in installments of meaning of each of simp...

What is RDBMS? - Part I

Cool Database Fundas: What is RDBMS? - Part I: Need for non-procedural databases was felt around 1960 for providing an alternative to  programming language interfaces to be replaced wit...

Distributed Database System - Principles

Cool Database Fundas: Distributed Database System - Principles: Please spend a moment to read the previous post " Distributed Database System - Introduction " as the text below may contain some referenc...

What is RDBMS? - Part II

Cool Database Fundas: What is RDBMS? - Part II: You are advised to visit Part I of this same heading as the following text may contain some references to it! There were a few questions...

Distributed Database System - Introduction

Cool Database Fundas: Distributed Database System - Introduction: Introduction - Today's business environment has an increasing need for distributed databases as the desire for reliable, scalable and acc...

What are Tablespaces?

Cool Database Fundas: What are Tablespaces?: "Tablespace" is part of storage architecture of some of the commercial database systems like Oracle or DB2. Some of the database systems (...

Delete vs. Truncate

Cool Database Fundas: Delete vs. Truncate: Delete and Truncate SQL verbs (commands) are frequently used to empty tables. The actual difference between the two is not much clear...

Database Design - Normalization - Normal Form - Pa...

Cool Database Fundas: Database Design - Normalization - Normal Form - Pa...: You are requested to visit " Database Design .... Part II " for continuity with this post. In the last two posts with the same heading we...

Data Integrity Constraints

Cool Database Fundas: Data Integrity Constraints: A Relational Database Management System must implement Data Integrity in the database in declarative manner ( Codd's Rule #10 ) to ensure...

Integrity Constraints - Creation

Cool Database Fundas: Integrity Constraints - Creation: Please Click Here for the explanation and background on the Data Integrity Constraints. Note : The set of commands / statements used he...

Data Integrity - Triggers

Cool Database Fundas: Data Integrity - Triggers: Please Click Here to continue from the background. Integrity Constraints are the most convenient form of implementation of data valida...

Data Integrity Using Views

Cool Database Fundas: Data Integrity Using Views: Data Integrity is implemented in the databases using various Constraints and Triggers . Whereas Integrity Constraints are a non-procedu...

Materialized Views

Cool Database Fundas: Materialized Views: What are Materialized Views? - Let us start with an example - A company has a database with a table "sales" maintaining millions of ...

Materialized Views

Cool Database Fundas: Materialized Views: What are Materialized Views? - Let us start with an example - A company has a database with a table "sales" maintaining millions of ...

Delete vs. Truncate

Cool Database Fundas: Delete vs. Truncate: Delete and Truncate SQL verbs (commands) are frequently used to empty tables. The actual difference between the two is not much clear...

Procedure vs. Function

Cool Database Fundas: Procedure vs. Function: A database (back end) programming language like PL/SQL in Oracle, has been provided to push the processing of the data to the server end w...

What are Index Organized Tables (IOT)?

Cool Database Fundas: What are Index Organized Tables (IOT)?: Introduction - Tables are what, RDBMS is all about. Tables provide a higher level abstraction layer for the logical storage of data, so ...

What are Clustered Tables ?

Cool Database Fundas: What are Clustered Tables ?: For an introduction to the table types and list visit What are Index Organized Tables ?   Clustered Tables - A "Cluster" is a way to...

What are temporary tables ?

Cool Database Fundas: What are temporary tables ?: For an introduction to the table types and list visit : What are Index Organized Tables ? Temporary Tables - "Temporary Tables" are a ...

What are External Tables ?

Cool Database Fundas: What are External Tables ?: For an introduction to the table types and list visit : What are Index Organized Tables ? "External Tables" are probably the types of ta...

What are Object Tables ?

Cool Database Fundas: What are Object Tables ?: For an introduction to the table types and list visit : What are Index Organized Tables ? The era of Relational Database Management Sy...

What are Nested Tables ?

Cool Database Fundas: What are Nested Tables ?: "Nested Table", as the name suggests is table within a table. A normal Heap Organized Table , if has a column whose datatype is table lik...

Choose a suitable table type - Challenge 1

Cool Database Fundas: Choose a suitable table type - Challenge 1: Examination must be an integral part of learning process or other way round, learning is incomplete without an examination. But not such e...

Database Indexes - Usage and Types

Cool Database Fundas: Database Indexes - Usage and Types: In a previous post entitled " Database Indexes - An Overview " we discussed about two major categories of the indexes - B*Tree and Bitmap...

Integrity Constraints - Creation

Cool Database Fundas: Integrity Constraints - Creation: Please Click Here for the explanation and background on the Data Integrity Constraints. Note : The set of commands / statements used he...

Cool Database Fundas: Data Integrity Constraints

Cool Database Fundas: Data Integrity Constraints: A Relational Database Management System must implement Data Integrity in the database in declarative manner ( Codd's Rule #10 ) to ensure...

Cool Database Fundas: Database Indexes - An Overview

Cool Database Fundas: Database Indexes - An Overview: Why create index? - DBMS is all about storing the data, allowing for the easy access and perform transaction on the data. RDBMS insis...

Oracle Apps: Sample PL/SQL Programs

Oracle Apps: Sample PL/SQL Programs: 1) WRITE A PROGRAM TO PRINT HELLO WORLD  BEGIN  DBMS_OUTPUT.PUT_ LINE ('HELLO WORLD');  END;  /

SQL Queries

SQL Queries

Create the following Tables:

LOCATION
Location_ID	Regional_Group
122	NEW YORK
123	DALLAS
124	CHICAGO
167	BOSTON

DEPARTMENT
Department_ID	Name	Location_ID
10	ACCOUNTING	122
20	RESEARCH	124
30	SALES	123
40	OPERATIONS	167

JOB
Job_ID	Function
667	CLERK
668	STAFF
669	ANALYST
670	SALESPERSON
671	MANAGER
672	PRESIDENT

EMPLOYEE
EMPLOYEE_ID	LAST_NAME	FIRST_NAME	MIDDLE_NAME	JOB_ID	MANAGER_ID	HIREDATE	SALARY	COMM	DEPARTMENT_ID
7369	SMITH	JOHN	Q	667	7902	17-DEC-84	800	NULL	20
7499	ALLEN	KEVIN	J	670	7698	20-FEB-85	1600	300	30
7505	DOYLE	JEAN	K	671	7839	04-APR-85	2850	NULL	30
7506	DENNIS	LYNN	S	671	7839	15-MAY-85	2750	NULL	30
7507	BAKER	LESLIE	D	671	7839	10-JUN-85	2200	NULL	40
7521	WARK	CYNTHIA	D	670	7698	22-FEB-85	1250	500	30

Queries based on the above tables:

Simple Queries:

1. List all the employee details
2. List all the department details
3. List all job details
4. List all the locations
5. List out first name,last name,salary, commission for all employees
6. List out employee_id,last name,department id for all  employees and rename employee id as "ID  of the employee", last name as "Name of the employee", department id as  "department  ID"
7. List out the employees anuual salary with their names only.

Where Conditions:

8. List the details about "SMITH"
9. List out the employees who are working in department 20
10. List out the employees who are earning salary between 3000 and 4500
11. List out the employees who are working in department 10 or 20
12. Find out the employees who are not working in department 10 or 30
13. List out the employees whose name starts with "S"
14. List out the employees whose name start with "S" and end with "H"
15. List out the employees whose name length is 4 and start with "S"
16. List out the employees who are working in department 10 and draw the salaries more than 3500
17. list out the employees who are not receiving commission.

Order By Clause:

18. List out the employee id, last name in ascending order based on the employee id.
19. List out the employee id, name in descending order based on salary column
20. list out the employee details according to their last_name in ascending order and salaries in descending order
21. list out the employee details according to their last_name in ascending order and then on department_id in descending order.

Group By & Having Clause:

22. How many employees who are working in different departments wise in the organization
23. List out the department wise maximum salary, minimum salary, average salary of the employees
24. List out the job wise maximum salary, minimum salary, average salaries of the employees.
25. List out the no.of employees joined in every month in ascending order.
26. List out the no.of employees for each month and year, in the ascending order based on the year, month.
27. List out the department id having atleast four employees.
28. How many employees in January month.
29. How many employees who are joined in January or September month.
30. How many employees who are joined in 1985.
31. How many employees joined each month in 1985.
32. How many employees who are joined in March 1985.
33. Which is the department id, having greater than or equal to 3 employees joined in April 1985.

Sub-Queries

34. Display the employee who got the maximum salary.
35. Display the employees who are working in Sales department
36. Display the employees who are working as "Clerk".
37. Display the employees who are working in "New York"
38. Find out no.of employees working in "Sales" department.
39. Update the employees salaries, who are working as Clerk on the basis of 10%.
40. Delete the employees who are working in accounting department.
41. Display the second highest salary drawing employee details.
42. Display the Nth highest salary drawing employee details

Sub-Query operators: (ALL,ANY,SOME,EXISTS)

43. List out the employees who earn more than every employee in department 30.
44. List out the employees who earn more than the lowest salary in department 30.
45. Find out whose department has not employees.
46. Find out which department does not have any employees.

Co-Related Sub Queries:

47.Find out the employees who earn greater than the average salary for their department.

Joins

Simple join

48.List our employees with their department names

49.Display employees with their designations (jobs)

50.Display the employees with their department name and regional groups.

51.How many employees who are working in different departments and display with department name.

52.How many employees who are working in sales department.

53.Which is the department having greater than or equal to 5 employees and display the department names in ascending order.

54.How many jobs in the organization with designations.

55.How many employees working in "New York".

Non – Equi Join:

56.Display employee details with salary grades.

57.List out the no. of employees on grade wise.

58.Display the employ salary grades and no. of employees between 2000 to 5000 range of salary.

Self Join:

59.Display the employee details with their manager names.

60.Display the employee details who earn more than their managers salaries.

61.Show the no. of employees working under every manager.

Outer Join:

61.Display employee details with all departments.

62.Display all employees in sales or operation departments.

Set Operators:

63.List out the distinct jobs in Sales and Accounting Departments.

64.List out the ALL jobs in Sales and Accounting Departments.

65.List out the common jobs in Research and Accounting Departments in ascending order.

Answers

1. SQL > Select * from employee;
2. SQL > Select * from department;
3. SQL > Select * from job;
4. SQL > Select * from loc;
5. SQL > Select first_name, last_name, salary, commission from employee;
6. SQL > Select employee_id "id of the employee", last_name "name", department id as "department id" from employee;
7. SQL > Select last_name, salary*12 "annual salary" from employee
8. SQL > Select * from employee where last_name='SMITH';
9. SQL > Select * from employee where department_id=20
10. SQL > Select * from employee where salary between 3000 and 4500
11. SQL > Select * from employee where department_id in (20,30)
12. SQL > Select last_name, salary, commission, department_id from employee where department_id not in (10,30)
13. SQL > Select * from employee where last_name like 'S%'
14. SQL > Select * from employee where last_name like 'S%H'
15. SQL > Select * from employee where last_name like 'S___'
16. SQL > Select * from employee where department_id=10 and salary>3500
17. SQL > Select * from employee where commission is Null
18. SQL > Select employee_id, last_name from employee order by employee_id
19. SQL > Select employee_id, last_name, salary from employee order by salary desc
20. SQL > Select employee_id, last_name, salary from employee order by last_name, salary desc
21. SQL > Select employee_id, last_name, salary from employee order by last_name, department_id desc
22. SQL > Select department_id, count(*), from employee group by department_id
23. SQL > Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id
24. SQL > Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id
25. SQL > Select to_char(hire_date,'month')month, count(*) from employee group by to_char(hire_date,'month') order by month
26. SQL > Select to_char(hire_date,'yyyy') Year, to_char(hire_date,'mon') Month, count(*) "No. of employees" from employee group by to_char(hire_date,'yyyy'), to_char(hire_date,'mon')
27. SQL > Select department_id, count(*) from employee group by department_id having count(*)>=4
28. SQL > Select to_char(hire_date,'mon') month, count(*) from employee group by to_char(hire_date,'mon') having to_char(hire_date,'mon')='jan'
29. SQL > Select to_char(hire_date,'mon') month, count(*) from employee group by to_char(hire_date,'mon') having to_char(hire_date,'mon') in ('jan','sep')
30. SQL > Select to_char(hire_date,'yyyy') Year, count(*) from employee group by to_char(hire_date,'yyyy') having to_char(hire_date,'yyyy')=1985
31. SQL > Select to_char(hire_date,'yyyy')Year, to_char(hire_date,'mon') Month, count(*) "No. of employees" from employee where to_char(hire_date,'yyyy')=1985 group by to_char(hire_date,'yyyy'),to_char(hire_date,'mon')
32. SQL > Select to_char(hire_date,'yyyy')Year, to_char(hire_date,'mon') Month, count(*) "No. of employees" from employee where to_char(hire_date,'yyyy')=1985 and to_char(hire_date,'mon')='mar' group by to_char(hire_date,'yyyy'),to_char(hire_date,'mon')
33. SQL > Select department_id, count(*) "No. of employees" from employee where to_char(hire_date,'yyyy')=1985 and to_char(hire_date,'mon')='apr' group by to_char(hire_date,'yyyy'), to_char(hire_date,'mon'), department_id having count(*)>=3
34. SQL > Select * from employee where salary=(select max(salary) from employee)
35. SQL > Select * from employee where department_id IN (select department_id from department where name='SALES')
36. SQL > Select * from employee where job_id in (select job_id from job where function='CLERK'
37. SQL > Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group='New York'))
38. SQL > Select * from employee where department_id=(select department_id from department where name='SALES' group by department_id)
39. SQL > Update employee set salary=salary*10/100 wehre job_id=(select job_id from job where function='CLERK')
40. SQL > delete from employee where department_id=(select department_id from department where name='ACCOUNTING')
41. SQL > Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))
42. SQL > Select distinct e.salary from employee where & no-1=(select count(distinct salary) from employee where sal>e.salary)
43. SQL > Select * from employee where salary > all (Select salary from employee where department_id=30)
44. SQL > Select * from employee where salary > any (Select salary from employee where department_id=30)
45. SQL > Select employee_id, last_name, department_id from employee e where not exists (select department_id from department d where d.department_id=e.department_id)
46. SQL > Select name from department d where not exists (select last_name from employee e where d.department_id=e.department_id)
47. SQL > Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)
48. SQL > Select employee_id, last_name, name from employee e, department d where e.department_id=d.department_id
49. SQL > Select employee_id, last_name, function from employee e, job j where e.job_id=j.job_id
50. SQL > Select employee_id, last_name, name, regional_group from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id
51. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name
52. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having name='SALES'
53. SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having count (*)>=5 order by name
54. SQL > Select function, count(*) from employee e, job j where j.job_id=e.job_id group by function
55. SQL > Select regional_group, count(*) from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id and regional_group='NEW YORK' group by regional_group
56. SQL > Select employee_id, last_name, grade_id from employee e, salary_grade s where salary between lower_bound and upper_bound order by last_name
57. SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound group by grade_id order by grade_id desc
58. SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound and lower_bound>=2000 and lower_bound<=5000 group by grade_id order by grade_id desc
59. SQL > Select e.last_name emp_name, m.last_name, mgr_name from employee e, employee m where e.manager_id=m.employee_id
60. SQL > Select e.last_name emp_name, e.salary emp_salary, m.last_name, mgr_name, m.salary mgr_salary from employee e, employee m where e.manager_id=m.employee_id and m.salary
61. SQL > Select m.manager_id, count(*) from employee e, employee m where e.employee_id=m.manager_id group by m.manager_id
62. SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id
63. SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id and d.department_idin (select department_id from department where name IN ('SALES','OPERATIONS'))
64. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='SALES')) union Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='ACCOUNTING'))
65. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='SALES')) union all Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='ACCOUNTING'))
66. SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='RESEARCH')) intersect Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='ACCOUNTING')) order by function