Collection of SQL queries with answers

SQL

Disadvantages of trigger in oracle? Triggers can execute every time some field in database is updated. If a field is likely to be updated often, it is a system overhead. Viewing a trigger is difficult compared to tables, views stored procedures. It is not possible to track or debug triggers. ------------------------------------------------------------------------------------------------------------------------------------Create a query that will display the total no.of employees and, of that total, t he no.of employees hired in 1995,1996,1997, and 1998. create appropriate column headings. SELECT to_char(hiredate,yyyy),count(*) FROM emp GROUP BY to_char(hiredate,yyyy) HAVING to_char(hiredate,yyyy) IN (1995,1996,1997,1998); select Count(*) as NumberOfEmployees, EmployeesHiredIn1995_1996_1997_1998 = (select count(*) from employees where hiredate like '%1995%' or hiredate like '%1996%' or hiredate like '%1997%' or hiredate like '%1998%') from employees select count(*) from employees where hiredate in(1995,1996,1997,1998); select count(*) Count, to_char(hiredate, 'YYYY') YEAR from emp group by rollup( to_char(hiredate, 'YYYY')) Select count(emp_code), count(case when TO_NUMBER(to_CHAR(hire_date,'YYYY'))=1995 then (emp_code) s hire_1995 count(case when TO_NUMBER(to_CHAR(hire_date,'YYYY'))=1996 then (emp_code) s hire_1996 from employees --similary the query will continue. remember that the fields are from one and you are not doing any dimension querying. only the fact info is being d

select count(decode(to_char(hiredate, count(decode(to_char(hiredate, count(decode(to_char(hiredate, count(decode(to_char(hiredate, count(*) total_emp from emp;

'yyyy') 'yyyy') 'yyyy') 'yyyy')

, , , ,

'1995', '1996', '1997', '1998',

hiredate, hiredate, hiredate, hiredate,

select to_char(PADATEOFJOININGD,'YYYY'), count(*)

null)) null)) null)) null))

end) a end) a table querie

hired_in_1995, hired_in_1996, hired_in_1997, hired_in_1998,

from PAEMPLOYEE_M group by (to_char(PADATEOFJOININGD,'YYYY')) union select 'total emplyee', count(*) from PAEMPLOYEE_M SELECT COUNT(*) TOTAL_EMP, SUM(CASE WHEN INSTR(HIREDATE,'81') SUM(CASE WHEN INSTR(HIREDATE,'80') SUM(CASE WHEN INSTR(HIREDATE,'87') SUM(CASE WHEN INSTR(HIREDATE,'82') FROM EMP

> > > >

0 0 0 0

THEN THEN THEN THEN

1 1 1 1

ELSE ELSE ELSE ELSE

0 0 0 0

END)HIRED_81, END)HIRED_80, END)HIRED_87, END)HIRED_82

select count(*) Count, to_char(hiredate, 'YYYY') YEAR from emp group by to_char( hiredate, 'YYYY') having substr(to_char(hiredate,'yyyy'),-1) in(5,6,7,8)

Please check with this query... SELECT COUNT(EMPID) TOTAL_EMPS, DECODE(TO_CHAR(HIRE_DATE,YYYY)),'1995',COUNT(EMPID)) DECODE(TO_CHAR(HIRE_DATE,YYYY)),'1996',COUNT(EMPID)) DECODE(TO_CHAR(HIRE_DATE,YYYY)),'1997',COUNT(EMPID)) DECODE(TO_CHAR(HIRE_DATE,YYYY)),'1998',COUNT(EMPID)) FROM EMP

TOTAL_HIRE_1995, TOTAL_HIRE_1996, TOTAL_HIRE_1997, TOTAL_HIRE_1998

select count(*), count(decode(to_char(hiredate, 'yyyy'), 1995,1)), count(decode(to_char(hiredate, 'yyyy'), 1996,1)), count(decode(to_char(hiredate, 'yyyy'), 1997,1)), count(decode(to_char(hiredate, 'yyyy'), 1998,1)) from emp

SELECT to_char(hiredate,'YYYY'),count(*) FROM employee GROUP BY to_char(hiredate ,'YYYY'); Code SELECT hiredate,count(*) FROM ven_emp WHERE hiredate BETWEEN 01-jan-95 AND 31-dec-98 GROUP BY hiredate Code SELECT deptno,count(to_char(hiredate,yyyy),1980,empno) AS yr1980, count(to_char(hiredate,yyyy),1981,empno) AS yr1981, count(to_char(hiredate,yyyy),1982,empno) AS yr1982 FROM emp GROUP BY deptno; SELECT Hiredate,Count(*) FROM emp WHERE to_char(hiredate,yyyy) in (1995,1996,1997,1998) GROUP BY Hiredate; SELECT to_char(hiredate,YYYY),count(*) FROM employee GROUP BY to_char(hiredate,Y YYY); Code SELECT to_char(hiredate,yyyy),count(*) FROM emp GROUP BY to_char(hiredate,yyyy)

HAVING to_char(hiredate,yyyy) IN (1995,1996,1997,1998); Code SELECT COUNT(*) AS "Total" ,SUM(CASE TO_CHAR(HIRE_DATE,YYYY) WHEN 1995 THEN 1 EL SE 0 END) AS "1995", SUM(CASE TO_CHAR(HIRE_DATE,YYYY) WHEN 1996 THEN 1 ELSE 0 END) AS "1996" , SUM(CASE TO_CHAR(HIRE_DATE,YYYY) WHEN 1997 THEN 1 ELSE 0 END) AS "1997", SUM(CASE TO_CHAR(HIRE_DATE,YYYY) WHEN 1998 THEN 1 ELSE 0 END) AS "1998" FROM HR.EMPLOYEES >> << What is the difference between rownum,rowid Interview Candidate Jul 26th, 2006 11 34446

BasicsAnswerFirstPrevNextLast Editorial / Best Answer rishipahuja Member Since Aug-2006 | Aug 6th, 2006 rowid has a physical significance i.e you can read a row if you know rowid. It i s complete physical address of a row. While rownum is temporary serial number allocated to each returned row during qu ery execution. Showing Answers 1 - 11 of 11 Answers Anurag puranik Jul 27th, 2006 Rownum is just the serial No of your output while Rowid is automatically generat ed unique id of a row an it is generated at the time of insertion of row. Rownum is numeric and rowid is 16 bit hexadecimal no. Was this answer useful? Yes Yes 1 Reply rishipahuja Aug 6th, 2006 rowid has a physical significance i.e you can read a row if you know rowid. It i s complete physical address of a row. While rownum is temporary serial number allocated to each returned row during qu ery execution. Was this answer useful? Yes Yes 5 Reply vidhya Aug 29th, 2006 very good answer Was this answer useful? Reply lanka_satya Sep 12th, 2008

Yes

rownum is generated for the data retrieved and stored in an implicit cursor so depending on the outcum it may varry where as rowid is generated automatically f or creation of every row,hence forth if we want retrive a particular row in a ta ble we can abosolutelt do that if we can give rowid..... Was this answer useful? Yes Reply nalinbit

Nov 24th, 2008 rownum is a pseudo column which is generated for query data set at runtime. whil e rowid is the physical address of the row and hence definition suggest rowid fo r a row will never change but row num will always change. Was this answer useful? Yes Reply venugopalreddy.net Jun 30th, 2009 rownum is the temp num assigned for the return rows per statement query It will change as per the query statement. It can be used with "=1" or "< 10" id number . Where as rowid is the id assigned by the Oracle while creating record in database. It is permanent & we can query with " =" sign & it will not change or depend on query. Was this answer useful? Yes Reply Vikram Simha Reddy Jan 8th, 2012 1. Rowid gives address of rows or records. Rownum gives count of records 2. Rowid is permanently stored in database. Rownum is not stored in database per manently 3. Rowid is automatically assigned with every inserted into a table. Rownum is a n dynamic value automatically retrieved along with select statement output. 4. It is only for display purpose. Was this answer useful? Yes Reply Chittaranjan Kotian May 16th, 2012 ROWNUM is a pseudocolumn returning a sequential number along with the rows retri eved, whereas rowid (also a pseudocolumn) contains the actual physical address o f the data block containing the row Was this answer useful? Yes Reply Sunny Mar 25th, 2013 Row num is a sequential number allocated to each returned row for query executio n. It is nothing but a numeric values. It is a temporary values. But row id is a physical address of the rows. It is permanent. Was this answer useful? Yes Reply Arif Jul 3rd, 2015 I have one doubt like you said that rowid is automatically created when we add a row(record) then what happens to rowid when we delete that record? Was this answer useful? Yes Reply vinay Sep 20th, 2015 It it will be re-assigned to a different row when inserted by Oracle.

>> <<

What is difference between COM & DCOM? Interview Candidate Oct 10th, 2005 2 4523

BasicsAnswerFirstPrevNextLast Showing Answers 1 - 2 of 2 Answers maqk Jan 2nd, 2006 What's the Difference between COM and DCOM? Obviously, the difference is that DCOM is distributed but COM is not. To be more precise, there are three main elements added to COM: The way for creating a remote object. The way for accessing a remote object. The way to ensure secure access to a remote object. Was this answer useful? Reply Balaji Aug 5th, 2015

Yes

The way for accessing a remote object. >> << What is normalization? What is the advantage of normalization? Interview Candidate Sep 8th, 2005 18 39772

BasicsAnswerFirstPrevNextLast Showing Answers 1 - 18 of 18 Answers Nimi Aug 25th, 2005 The process of separating n. This is implemented to s redunbdancy of data and Was this answer useful? Reply N.Ramesh Aug 31st, 2005

data into distinct, unique sets is called normalizatio imorove the performance of the RDBMS, such as reducece data inconsistency. Yes Yes 1

Normalization is the process of removing redundant data from your tables in orde r to improve storage efficiency, data integrity and scalability Was this answer useful? Yes Yes 1 Reply Vijay Jaiswal Sep 8th, 2005 Database normalization is a series of steps followed to obtain a database design that allows for consistent storage and efficient access of data in a relational database .These steps reduce data redundancy and the risk of data becoming inco

nsistent Was this answer useful? Reply Praveen Tangirala Dec 13th, 2005

Yes

Normalization is the process used to reduce the unnecessary repetetion of data i .e, redundant data.It is performed on the data which is redundant and makes the data in a normalized format.It is of step-by-step processIstNotmal FormIIndNorma l formIIIrdNormalformIVth Normalform or BoyceCodd Normal formBy perofmring this we will get the data in the Normalized formati.,e from DBMS to RDBMS. Was this answer useful? Yes Reply ruchi Mar 20th, 2006 what is the advantage of normalization (briefly) Was this answer useful? Yes Yes 1 Reply rajanipriya May 9th, 2006 Normalization solely reduces redundancy and it give rises to a database which c onsists of distinct yet related tables. This results in database consistency, flexible data accesses . Was this answer useful? Yes Reply Sri Jul 6th, 2006 Normalization is the process of organizing data in a database. This includes cre ating tables and establishing relationships between those tables according to ru les designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency. Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exact ly the same way in all locations. A customer address change is much easier to im plement if that data is stored only in the Customers table and nowhere else in t he database. What is an "inconsistent dependency"? While it is intuitive for a user to look i n the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. T he employee's salary is related to, or dependent on, the employee and thus shoul d be moved to the Employees table. Inconsistent dependencies can make data diffi cult to access; the path to find the data may be missing or broken. There are a few rules for database normalization. Each rule is called a "normal form." If the first rule is observed, the database is said to be in "first norma l form." If the first three rules are observed, the database is considered to be in "third normal form." Although other levels of normalization are possible, th ird normal form is considered the highest level necessary for most applications. As with many formal rules and specifications, real world scenarios do not always allow for perfect compliance. In general, normalization requires additional tab les and some customers find this cumbersome. If you decide to violate one of the first three rules of normalization, make sure that your application anticipates any problems that could occur, such as redundant data and inconsistent dependen cies. NOTE: The following descriptions include examples. First Normal Form

Eliminate repeating groups in individual tables. Create a separate table for each set of related data. Identify each set of related data with a primary key. Do not use multiple fields in a single table to store similar data. For example, to track an inventory item that may come from two possible sources, an inventor y record may contain fields for Vendor Code 1 and Vendor Code 2. But what happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate t able called Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key. Second Normal Form Create separate tables for sets of values that apply to multiple records. Relate these tables with a foreign key. Records should not depend on anything other than a table's primary key (a compou nd key, if necessary). For example, consider a customer's address in an accounti ng system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of sto ring the customer's address as a separate entry in each of these tables, store i t in one place, either in the Customers table or in a separate Addresses table. Third Normal Form Eliminate fields that do not depend on the key. Values in a record that are not part of that record's key do not belong in the t able. In general, any time the contents of a group of fields may apply to more t han a single record in the table, consider placing those fields in a separate ta ble. For example, in an Employee Recruitment table, a candidate's university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities with no current candidates. Create a separate Unive rsities table and link it to the Candidates table with a university code key. EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate al l possible interfield dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that ma y be duplicated in multiple records. In theory, normalization is worth pursuing; however, many small tables may degrade performance or exceed open file and memo ry capacities. It may be more feasible to apply third normal form only to data that changes fre quently. If some dependent fields remain, design your application to require the user to verify all related fields when any one is changed. Other Normalization Forms Fourth normal form, also called Boyce Codd Normal Form (BCNF), and fifth normal form do exist, but are rarely considered in practical design. Disregarding these rules may result in less than perfect database design, but should not affect fu nctionality. ********************************** Examples of No rmalized Tables ********************************** Normalization E xamples: Unnormalized table: Student# Advisor Adv-Room Class1 Class2 Class3 ------------------------------------------------------1022 Jones 412 101-07 143-01 159-02 4123 Smith 216 201-01 211-02 214-01 First Normal Form: NO REPEATING GROUPS Tables should have only two dimensions. Since one student has several classes, t hese classes should be listed in a separate table. Fields Class1, Class2, & Clas s3 in the above record are indications of design trouble. Spreadsheets often use the third dimension, but tables should not. Another way t o look at this problem: with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first nor mal form by eliminating the repeating group (Class#), as shown below:

Student# Advisor Adv-Room Class# -------------------------------------1022 Jones 412 101-07 1022 Jones 412 143-01 1022 Jones 412 159-02 4123 Smith 216 201-01 4123 Smith 216 211-02 4 123 Smith 216 214-01 Second Normal Form: ELIMINATE REDUNDANT DATA Note the multiple Class# values for each Student# value in the above table. Clas s# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form. The following two tables demonstrate second normal form: Students: Student# Advisor Adv-Room ----------------------------1022 Jones 412 4123 Smith 216 Registration: Student# Class# ----------------1022 101-07 1022 143-01 1022 159-02 4123 201-01 4123 211-02 4123 21401 Third Normal Form: ELIMINATE DATA NOT DEPENDENT ON KEY In the last example, Adv-Room (the advisor's office number) is functionally depe ndent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below: Students: Student# Advisor ------------------1022 Jones 4123 Smith Faculty: Name Room Dept -------------------Jones 412 42 Smith 216 42 Was this answer useful? Yes Yes 1 Reply Naveed Shahzad Aug 1st, 2006 send me advantages of nomalization. Was this answer useful? Yes Reply Ashish Sep 7th, 2006 The main advantage of normalisation is that it helps to reduce redundancy. However too much of normalisation is also not preferred as it creates too many t ables leading too more nos fo joins making the query even more complex.Normalisa tion upto 3NF is generally preferred. Was this answer useful? Yes Reply deolyn makoni Dec 5th, 2006 what is normalization? what is the advantage of normalisation Was this answer useful? Yes Reply deolyn makoni Dec 5th, 2006 what are te problems that may arise if a system is created without completing no rmalisation first Was this answer useful? Yes Reply Ramya Jan 24th, 2007 The answer for this normalization is good. If u don't mine can u give me one mor e example for normalization.

Was this answer useful? Reply Muhammad Wali Ullah Feb 21st, 2012

Yes

The Process of Simplifying the structure of data. Normalization increase granula rity and Granularity is the scope of a definition for any particular thing.The m ore granular a data model is the easier it becomes to manage ,up to a point ,dep ending ,of course, on the application of the database model. Was this answer useful? Yes Reply Chittaranjan Kotian May 16th, 2012 Normalization is a process of reviewing a data model to remove data redundency, maintain data intergrity, and make the data model as simple as possible. There a re 3 normalization forms that are generally used, and an additional fourth and f ifth normal form The advantages of normalization is to remove data redundency Was this answer useful? Yes Reply Raheela Warraich Oct 12th, 2014 plz anyone tell me : What is the process of normalization in information retrivel. Was this answer useful? Yes Reply Mayank Feb 10th, 2015 Normalization is the Process to remove the redundancy data from the Table. Was this answer useful? Yes Reply rama May 11th, 2015 normalization is the process of dividing the data into separate tables because o f dividing data into tables avoiding the data redundancy(duplications) Was this answer useful? Yes Reply Subrat Jul 28th, 2015 It is a Process to obtain a database design that allows for efficient access and storage of data. This process use reduce data redundancy and the chances of dat a becoming inconsistent. >> << What is the difference between trigger and constraints ? narasingha baral Aug 28th, 2014 6 6014 Questions by narasingha baral BasicsAnswerFirstPrevNextLast Showing Answers 1 - 6 of 6 Answers

Rushil Sep 17th, 2014 Trigger can be use to create user defined constraints. This is one difference wr t constraints. It has many other benefits also. Thanks Was this answer useful? Yes Reply binita Sep 24th, 2014 Trigger is a stored routine.it is a set of SQL statements used for protecting th e integrity of data.But constraint is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce integrity of database.it restricts the insertion of data in a table. Was this answer useful? Yes Reply Vaithy Oct 11th, 2014 1. Trigger can be on update/delete statements also, but constraints are related to provide restrictions while inserting the data. 2. Triggers can be defined at system level(Database shutdown, log in, etc), but constraints cant be used at system level. 3. We can use anonymous transactions on trigger, but we cant use anonymous trans actions on constraints. Was this answer useful? Yes Reply Samudrala Jan 30th, 2015 Triggers are stored programs, which are automatically executed or fired when som e events occur. Triggers are, in fact, written to be executed in response to any of the following events: A database manipulation (DML) statement (DELETE, INSERT, or UPDATE). A database definition (DDL) statement (CREATE, ALTER, or DROP). A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN). Triggers could be defined on the table, view, schema, or database with which the event is associated. Syntax: Code CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n] [FOR EACH ROW]

WHEN (condition) DECLARE

Declaration-statements BEGIN

Executable-statements EXCEPTION

Exception-handling-statements

END; Was this answer useful? Reply Samudrala Jan 30th, 2015

Yes

Constraints are the rules enforced on data columns on table. These are used to l imit the type of data that can go into a table. This ensures the accuracy and re liability of the data in the database. Contraints could be column level or table level. Column level constraints are applied only to one column where as table l evel constraints are applied to the whole table. Following are commonly used constraints available in SQL: ï· NOT NULL Constraint: Ensures that a column cannot have NULL value. ï· DEFAULT Constraint: Provides a default value for a column when none is specified. ï· UNIQUE Constraint: Ensures that all values in a column are different. ï· PRIMARY Key: Uniquely identified each rows/records in a database table. ï· FOREIGN Key: Uniquely identified a row/record in any other database table. ï· CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions. ï· INDEX: Use to create and retrieve data from the database very quickly. Was this answer useful? Yes Reply munny

Jul 24th, 2015 Triggers are the stored procedures that are fired when an DB event occur where a s Constraints are the checks or the rules that are enforced on data columns to m aintain the data integrity. Both can be use for the data integrity. Constraints are used at the data creation level. With Triggers we can define the some more r equisite action like data messaging, data population) on other relation tables >> << Can any one tell me how the get the datafile when my controlfile is lost and i h ave created a new controlfile. and the information about the datafile is present in the previous controlfile which is corrupt and deleted. and there is no backu p of the datafile is present parthokonar1 Oct 2nd, 2006 3 1703 Questions by parthokonar1 answers by parthokonar1 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 3 of 3 Answers taraldesai Jun 23rd, 2007 mount with pfile where your pfile control_file parameter points to old ctl file. ..then issue alter database backup control file to trace;

Was this answer useful? Reply Rushil Sep 17th, 2014

Yes

Hi, Can anyone explain what is data file and control file. Was this answer useful? Yes Reply SATHEES KUMAR Jul 1st, 2015 Control file is a small binary file that record the physical structure of the da tabase like datafile and redolog file location, timestamp,scn and used for recov er purpose all so but have no datafile backup then you cant recover the database . >> << Can you explain what is DUAL table in oracle ? Sundra Mar 29th, 2006 17 6379 Questions by Sundra BasicsAnswerFirstPrevNextLast Showing Answers 1 - 17 of 17 Answers Smrati Saxena Apr 3rd, 2006 Hi

Dual is a virtual table ...it do not eists . It is used to query fro pseudo columns.. eg; select sysdate from dual Was this answer useful? Yes Reply pramod kumar Apr 6th, 2006 Dummy table with one row and one column, we can select from it but cant insert u pdate delete. Was this answer useful? Yes Reply dhk Apr 20th, 2006 Hello Friends Dual is a dummy table which has one row and one column . Was this answer useful? Yes Reply Renuka Rajput Aug 2nd, 2006 Dual is oracle created table.It is called as dummy table n it has one row and on e cloumn. Was this answer useful? Yes Reply lieni Jul 30th, 2007 Sorry dual is not a dummy table. Dual really exists and also that it has exactly one row and one column isn't correct. You can insert: Insert into dual value 'Y' Select sysdate from dual will then return two lines with the actual timestamp. Was this answer useful? Yes Yes 1 Reply shina Sep 18th, 2007 Yes you are right that DUAL table has multiple cols but only one row. But the insert statement that you've mentioned is not working. Was this answer useful? Yes Reply kishore Oct 6th, 2007 Dual table is one which is having only one row & one coulmn and it is maintained by Oracle server. Which is used for mathamatical calculations like sqrt, round, power, max, min etc. Was this answer useful? Yes Reply shravanam Oct 24th, 2007 DUAL IS A DUMMY TABLE. WE CAN INSERT,UPDATE AND DELETE FROM DUAL TABLE IF YOU LO G IN WITH SYSTEM/MANGER PRIVILLAGES.

EXAMPLE: SELECT * FROM DUAL; no rows selected DESC DUAL Name

Null? Type ----------------------------------------- -------- ---------------------------VARCHAR2(1) SQL> INSERT INTO DUAL VALUES('Y'); 1 row created. SQL> SELECT * FROM DUAL; D Y SQL> UPDATE DUAL SET DUMMY='S'; 1 row updated. SQL> SELECT * FROM DUAL; D S

SQL> DELETE FROM DUAL; 1 row deleted. SQL> SELECT * FROM DUAL; no rows selected

EVEN IF U INSERT A ROW INTO DUAL TABLE STILL IT CAN BE USED TO RETRIEVE DATA FOR PSUEDO COLUMNS. YOU CAN INSERT ONLY ONE RECORD INTO THE DUAL TABLE, IF U TRY TO INSERT ANOTHER R ECORD IT WILL NOT GIVE ANY ERROR,IT WILL JUST DISPLAY 1 ROW INSERTED BUT U WILL FIND ONLY 1 ROW. ie THE PREVIOUS RECORD.

REGARDS SHARAT Was this answer useful? Reply lanka_satya Jul 4th, 2008

Yes Yes

1

most of our friends have given different answers for this..........infact most o f them are same and correct .......but I will give one simple definition and sma ll logic beyond its working.........? I can say it is a practise table as it contains only one column and one row...t hat is its column name is dummy and value is x...even though if try to insert an y value also it will take,but will not be updated into the actual table.....

even we can create this type of table for ur own purpose...... second point which i have mentioned is: we normally use this table for functions by using select stmt................ for example: let us type this. select sysdate from dual; here the select statement works for every row in a table and its basic use is it will always try to retrive a value for the expression or column or function for which we have given in select statement and it works for each record.......... so normally it contains one row, so you will be displayed with one value that's if u can see we cannot test group functions with dual table.. if you have any doubts regarding functionality of dual table: let try to write this query... select sysdate from emp this will try to display sysdate for the number of rows that is present in emp t able...... so dual table creation is different and don't try to think of inserting any rows in it,because it won't take any as this is used for simple processing of select statement....

Was this answer useful? Reply nare4u Nov 15th, 2008

Yes Yes

1

SQL> select 2 from dual; 2 ---------2 SQL> select 2,3 from dual; 2 3 ---------- ---------2 3 Was this answer useful? Reply meenatchim Nov 16th, 2008

Yes

DUAL is a table.That is owned by SYS user .It has a column name as dummy and i t containts a row having a vale "X".The dual table is useful when u want to retu n a value once only,the value that is not derived in from a table with userdata Was this answer useful? Yes Reply deepak Feb 24th, 2009

Dual is a system defined table having one rows and one column. A person who has informed that it has virtual existence is not proper. It has physical existence. Ex: SQL> select * from dual; D X Means a table having single row and columns. Regards, Deepak Mahapatra TCS Bangalore Was this answer useful? Reply samarendra161 Jul 2nd, 2010

Yes

Dual is a virtual table which contains only one row & column. Generally this tab le can be used into test predefined function functionality & also used to perfor m mathematical operation. By default dual table column datatype is varchar2. Was this answer useful? Yes Reply sk45453 Aug 23rd, 2010 Dual is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record. The value of that record is X. The owner of dual is SYS but dual can be accessed by every user. As dual contains exactly one row (unless someone fiddled with it), it is guaranteed to return exactly one row in select statements. Therefore, dual is the preferred table to select a pseudo column (such as sysdate). Although it is possible to delete the one record, or insert additional records, one really should not do that! Was this answer useful? Reply Waseem Mehmood Jun 2nd, 2011

Yes

Dual is a dummy table which has one row and one column but can't make the DML op ertions. Was this answer useful? Yes Reply subrahmanyam pattapu Jul 19th, 2011 Dual is default table in oracle .it contains single row and single column.All th e Character functions and number functions and date functions execution done in this Dual table. Was this answer useful? Yes Reply shadiq Jun 25th, 2015 Insert into dual values (Y); will work i guess, only thing is you need a suffici ent privilege... Also Dual can have 1000 columns and only one row.

>> << Finding errors from Pl/SQL package How to check errors from plsql package not by log file not by "show err"from sql * plus provided concurrent program name Interview Candidate Nov 2nd, 2014 5 2004

BasicsAnswerFirstPrevNextLast Showing Answers 1 - 5 of 5 Answers DivyaRam Nov 24th, 2014 put a print with DBMS_OUTPUT.PUT_LINE Was this answer useful? Yes Reply Akshatha Dec 3rd, 2014 Use Exceptions for Error Handling Was this answer useful? Yes Reply kuldeep Dec 13th, 2014 use pragma autonomous transaction Was this answer useful? Yes Reply Karthik Sai Feb 2nd, 2015 using exception / dbms output are the best and easier methods Was this answer useful? Yes Reply prakash Jun 8th, 2015 Using dbms_utility.format_error_mask function will be used to >> << What steps server process has to take to execute an update statement. Interview Candidate Oct 11th, 2007 4 2196 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 4 of 4 Answers kishore.giri Dec 9th, 2007 During the execution of an update statement, the server process prepares its par sing,execution plan in shared pool and then search for that record in the databa se buffer cache if it finds the record in the buffer then it updates the record else it goes to the datafile to read the record to the database buffer cache and then updates it. Was this answer useful? Yes Reply ajaymore

Feb 2nd, 2008 During the execution of an update statement, the server process looks into libra ry cache whether same kind of query is recently executed or not i.e. explain pla n is ready for execution or not in shared pool and then search for that record b lock in the database buffer cache if it finds the block in the buffer then it mo difies the block else it fetches the same from datafile to database buffer and t hen modifies it. Was this answer useful? Yes Yes 2 Reply premk Dec 23rd, 2009 When a user issues a update statement the server process checks for weather the same type of command is executed recently or not if that command is executed ver y recently then it directly proceed to the dictionary cache to fecth the metadat a presented in the system.dbf file and required data is going to updated to the system.dbf file and respective database. if the sever process does't find any pr eviously executed similar type of command then parsing is going to be takes plac e in dictionary cache and ash code is created at the same time execution plan of the command is created.after that the data from database buffer cache is going to be written to the respective data files. Was this answer useful? Yes Reply anjum May 1st, 2015 when update statement fires data will be loaded into buffer from data files and keeps the old image into undo tablespace and modifies the data in buffer once yo u commit modified data will be written to data files if you rollback data from u ndotablespace it will read and writes to the data files. >> << Difference between IS and AS in oracle What is the difference between IS and AS in Oracle PL/SQL..? Interview Candidate Feb 24th, 2015 3 2530 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 3 of 3 Answers Deval Parikh Mar 17th, 2015 No difference in PL/SQL. In SQL, can provide alias using "AS". Was this answer useful? Yes Reply pabitra praharaj Mar 26th, 2015 IS is used in to filter which value is null or not null. AS is used in for ALIAS Was this answer useful? Reply Nupur Apr 24th, 2015

Yes

In plsql there is no difference.

>> << What is the difference between ALL and ANY in ORACLE?can any one explain with ex ample? abhimanu.singh Mar 27th, 2006 7 17045 Questions by abhimanu.singh BasicsAnswerFirstPrevNextLast Showing Answers 1 - 7 of 7 Answers vijay Jaiswal Apr 19th, 2006 ANY The If any value from outcome of subquery is macthed then desired output will b e retrived.It checks with lowest value . ALL Itwill check for greatest value from outcome of subquery

Was this answer useful? Reply Nikhil_4_Oracle Mar 15th, 2007

Yes

DEAR ALL, ANY or SOME : Compares a value to each value in a list or retuned by a query. Mu st be preceded by =,!=,>,<,<=,=> Evalutes FALSE when query returns no rows. select empno,ename,sal from emp where sal = any(1500,3000); EMPNO ENAME SAL ------- ---------- ---------7499 ALLEN 1600 7566 JONES 2975 7839 KING 5000 7902 FORD 3000 so ,optimizer take above query as , --select empno,ename,sal from emp where sal>1500 or sal>3000; ALL : Compares a value to every value in a list or return by a query .Must be pr eceded by =,!=,>,<,=>,<=. Evalutes to TRUE if the query returns no rows. select empno,ename,sal from emp where sal > ALL (1600,3000); EMPNO ENAME SAL ---------- ---------- ---------7839 KING 5000 -- in other words optimizer take this query as , --select empno,ename,sal from emp where sal 1600 AND sal>3000; Regards,

Nikhil

Was this answer useful? Reply rajakumar_na Nov 14th, 2007

Yes Yes

1

ANY and ALL are Multiple row operators. ANY - Greater than the Minimum ALL - Greater than the maximum Was this answer useful? Yes Reply sure_prince Jun 26th, 2009 IN->Equal to Any One in the List. ANY->Compares Value to Each Value Returned by

the Sub Query.

ALL->Compares Value To Every Value Returned by the Sub Query. For Example: IN: (Q):Display the Details of all the Employees Whose Salaries are Matching with L east Investments of Departments? (A): SQL>Select Ename,Sal,Deptno from Emp Where Sal IN(Select Min(Sal) From Emp Group By Deptno); ANY: Meaans Less Than The Maximum Value in the List. (Q):Get The Details of All Employees Who are Earning Less Than The Highest Earni ng Employee Controling Other Emp? (A): SQL>Select Empno,Ename,Job,Sl From Emp Where Sal ANY:->Meaans More Than The Minimum Value in the List. (Q):Get The Details Of All Emps Who are Earning more than the least paid of Depa rtment 10? (A): SQL>Select Empno,Ename,Job,Sal From Emp Where Sal>Any(Select Min(Sal) From Emp Where Deptno=10); =ANY:->It's Equivalent to In Operator. Note: 'Some' is also used insted of ANY.

ALL Means Less Than The Minimum Value in the List. (Q):Get The Details Of All Emps Who are Earning Less than the Avg Investment of Department 10? (A): SQL>Select Empno,Ename,Job,Sal From Emp Where SalALL:->Means More Than The Maximum Value in the List. (Q):Get The Details Of All Emps Who are Earning More than the Avg Investment of Department 10? (A): SQL>Select Empno,Ename,Job,Sal From Emp Where Sal>All(Select Avg(Sal) From Emp Where Deptno=10);

Thank you Any Mistakes please Inform to me... -Suresh

Was this answer useful? Reply saurabh Jan 30th, 2015

Yes

ANY The If any value from outcome of sub query is matched then desired output will b e retrieved. It.It checks with lowest value . ALL ALL It will check for greatest value from outcome of sub-query Was this answer useful? Yes Reply Samudrala Jan 30th, 2015 ALL The ALL comparison condition is used to compare a value to a list or subquery. I t must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery. When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with AND operato rs, as shown below. SELECT empno, sal FROM emp WHERE sal > ALL (2000, 3000, 4000); EMPNO SAL ---------- ---------7839 5000 SQL> -- Transformed to equivalent statement without ALL.

SELECT empno, sal FROM emp WHERE sal > 2000 AND sal > 3000 AND sal > 4000; EMPNO SAL ---------- ---------7839 5000 Was this answer useful? Yes Reply Samudrala Jan 30th, 2015 ANY The ANY comparison condition is used to compare a value to a list or subquery. I t must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery. When the ANY condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with OR operator s, as shown below. SELECT empno, sal FROM emp WHERE sal > ANY (2000, 3000, 4000); EMPNO SAL ---------- ---------7566 2975 7698 2850 7782 2450 7788 3000 7839 5000 7902 3000 SQL> -- Transformed to equivalent statement without ANY. SELECT empno, sal FROM emp WHERE sal > 2000 OR sal > 3000 OR sal > 4000; EMPNO SAL ---------- ---------7566 2975 7698 2850 7782 2450 7788 3000 7839 5000 7902 3000 >> << Truncate command in oracle is DDL command or DML command ? give the answer with descirptive reason which is and why? nareshgupta12 Jan 8th, 2007 16 4949 Questions by nareshgupta12 answers by nareshgupta12 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 16 of 16 Answers ranjeetapegu Jan 11th, 2007 The truncate command is neither DDL statement or DML statement but it is A TCL s tatement . TCL- Transcation control statement. COMMIT, ROLLBACK,SAVEPOINT,TRUNCA TE all are transcation control statement Was this answer useful? Yes Reply santosh_yadein

Jan 23rd, 2007 Truncate commad is DDL statement ,we can`t after rolledback truncate command is executed. Was this answer useful? Yes Reply Praveen reddy.P Jan 31st, 2007 Truncate is a DDL statement Was this answer useful? Yes Reply A.R.Anandhi Feb 17th, 2007 Truncate is a DDL Statement.Whereas Delete is a DML Statement. Was this answer useful? Yes Reply A.R.Anandhi Feb 17th, 2007 Truncate is a DDLcommand.Whereas Delete is a DML Command. Was this answer useful? Yes Reply sekhar Feb 25th, 2007 truncate is DDL command . truncate means delete+commit. DDL commands are directly connecting to database. DML commands are first connecting to memory cache. these are deferences between ddl and dml Was this answer useful? Yes Reply mahi Sep 14th, 2007 Truncate command is a DDL statement. DDL ststements can't be rollback. Truncate statement can't be rollback. In DDL statement we can't use WHERE. In truncate also we can't use WHERE. DELETE is a DML statement we can rollback as well as we can use WHERE. Was this answer useful? Yes Reply dipak2704 Aug 27th, 2009 Following are different types of SQL language DQL - Data query language e.g. select DDL - Data definition language e.g. create, alter, drop DML - Data manipulation language e.g. insert, update, delete TCL - Transaction control language e.g commit, rollback, savepoint DCL - Data control language e.g grant, revoke Was this answer useful? Yes Reply Waseem Mehmood Jun 2nd, 2011 Truncate is a DDL command. Was this answer useful? Yes Reply

asit Jun 9th, 2012 Truncate is a DDL command. Internally what it does is it drops the whole table a nd recreate the structure. Was this answer useful? Yes Reply HARSHINI Jul 13th, 2012 TRUNCATE is a data definition language (DDL) command.Removes all rows from the t able,leaving the table empty and the table structure intact. Was this answer useful? Yes Reply Krishna G Mar 4th, 2013 Truncate is a DDL command.You can not rollback the data once you done truncate c ommand Was this answer useful? Yes Reply vishnu vardhan Nov 16th, 2014 TRUNCATE is a DDL command.If u want to delete all the records permanently from t he table truncate command will be use. Was this answer useful? Yes Reply Asus Dec 8th, 2014 If we compare truncate command with windows it is just link s=Shift+Del which de letes permanently. Was this answer useful? Yes Reply gulshan Dec 9th, 2014 since ddl deals with the structure of objects n here truncate is also used for p ermanently deleting the data with objects so truncate is a ddl command.. Was this answer useful? Yes Reply Akshatha Dec 25th, 2014 Truncate is DDL. Auto commits the transaction. Also, truncating a table can rese t the high water mark if REUSE storage clause is not used. >> << In Oracle varchar2 takes dynamic space for storage then why char is still in ora cle? Beena Sep 16th, 2005 19 5979 Questions by Beena answers by Beena BasicsAnswerFirstPrevNextLast Showing Answers 1 - 19 of 19 Answers rahultripathi Sep 28th, 2005

The mejor defference between varchar2 and char is fixed length and variable leng th . varchar2 have varible length mean if we declare as 20 space and its use onl y 5 space the memory asigne only 5 . but in char takes daclare space while use any number space less than declare any problem / suggetion contact [email protected] Was this answer useful? Yes Yes 2 Reply geetha24 Mar 31st, 2006 but the question here is why char is still used in oracle Was this answer useful? Yes Reply ramiya Apr 20th, 2006 hi plz reply me why we use char still we use varchar2 Was this answer useful? Yes Yes 1 Reply ranjana tripathi May 18th, 2006 char is fixed we know and therefore this is also sure that whenever we will use that it will fix the memory space accordingly still we use it because while work ing sometimes we are sure that one of the column value should not be tampered or u may say shoulod not be allowed to be tampered by giving them more space so we fix that particular column as char. Was this answer useful? Yes Yes 2 Reply Vikram Muddya May 18th, 2006 char is used if we know that the length wont exceed the specified range while va rchar is used for varying range. If we want any string not exceeding 6, we use char(6),because we can put a const raint if someone is trying to enter more or less than 6. Was this answer useful? Yes Yes 1 Reply Amit May 25th, 2006 When we export external data then we used fixed lenght of record this is possibl e by char or second reson is whenever we use varchar it stores its width which o ccupies memory. so here two reson is sufficient for char. Third reason is performance . Was this answer useful? Yes Yes 4 Reply umesh Jun 6th, 2006 char is of fied length search operation becomes faster since oracle engine has t o look for predetermined length of characters. Was this answer useful? Yes Yes 1 Reply anil kumar Aug 2nd, 2007 The char data type is used in oracle for giving small and single values like T ( or) F..

Was this answer useful? Reply sshravanam Oct 8th, 2007

Yes Yes

3

In VARCHAR2 we can make use of numbers along with characteres...... But in CHAR we can have only characters. Was this answer useful? Yes Yes 1 Reply NIRANJAN Oct 16th, 2007 We can use numbers and characters in char function Please try it.ok. with regaurds niranjan Was this answer useful? Reply navin05_06 Jun 2nd, 2008

Yes Yes

2

Still there is no answer for why char is there in oracle9i Was this answer useful? Yes Reply Ajay Lavanuru Aug 18th, 2008 The reason for the question is 1. To provide flexibility in oracle i.e we can restrict a column to a limited sp ace Example: you have come across some fields like 6 charecters are mandotory.....that is to enforce security in some areas .(if 6 are fixed that column is fixed....) 2. To improve performance Example: In varchar2 unnecessary storage can be recovered by some sort of mechanism which wont be there in char datatyp0e. so for a column which is fixed usage of char is appropriate than varchar2 that i s associated with some mechanism for recovery. Regards Ajay, Was this answer useful? Reply manniik Aug 21st, 2008

Yes Yes

2

Also When we migrate from Legacy system to Oracle, we need to use CHAR to make s ure that we capture the data properly. Was this answer useful? Yes Yes 1 Reply ds_devi

Jan 3rd, 2009 char(2) can be used when you want to store state codes and you know that the len gth is fixed--not less, not more. Was this answer useful? Yes Reply bhaumiklitu Jan 4th, 2009 We use Varchar2 in long length characters, so considering space complexity, we u se char for answering like yes/no. Was this answer useful? Yes Reply U.Janki Rao Mar 17th, 2009 Of course Varchar2 is there to maintain variable length of columns. It keeps an internal pointer to maintain Variable length that means it wastes two bytes to k eep first and last index where column starts and Calculates for each DML operati on against that column. So We can Avoid this house keeping work using Char data type if we can estimate the maximum length of the column. So both have it's own significance. Be careful while using varchar2 (It make overhead for each row). U se Char if you are sure with the lump sum length of the column. Thanks Was this answer useful? Yes Yes 1 Reply Prasad Aug 18th, 2011 Since Char is fixed length , the processing speed will be more as there is no ne ed of extra work to measure how much an instance of column is used the memory sp ace. But in Varchar, the processor has to do extra work to measure the amount of memo ry space used by an instance of column and to reallocate the remaining memory sp ace to other instance. hence the speed of operation is less compared to char. Was this answer useful? Reply Paras Feb 23rd, 2013

Yes

To Provide backward compatibility. i.e. program written in older version of Orac le might have CHAR used in it. The newer version should be compatible with it. T hats why CHAR is still there in Oracle .. Was this answer useful? Yes Reply Vijay Shewale Sep 11th, 2014 The reason behind char is still in oracle that is some of the table and objects are stored in oracle in char data types. to support that data types for that it is still in oracle. one more thing that i s char allows the limited character and size of memory so we can use this data t ypes in such limited cases. >> << What is the difference between primary key, unique key, surrogate key? Interview Candidate

Aug 27th, 2005 55 19286

BasicsAnswerFirstPrevNextLast Editorial / Best Answer Kolta Sam Member Since Jul-2011 | Jul 10th, 2011 Primary Key: It is a visible key It generated by user or application. It could be changed by the user or application. It could be queried It used to form a relation between tables It shouldn't contain null value It resemble table row It is a unique identifier for a table object. It contains only one key It could contain numeric and strings characters. It is an unique key which each row contain a distinct different key. Example for it is a customer_Id. It always starts by number one and second is two and so on but can starts with a different number. Could created on one or more columns No duplicate records Secondary Key: It used to form a relation between tables. It is alternate table key. It used to search data with primary key It could contains null value It could contains more than one secondary key for each table Created only on one columns No duplicate records It creates index clustered by default Surrogate Key: It is invisible key for the user or the application. It resembles database entity. It generated by the system so it is invisible for user and application. It shouldn't contain null values Only one surrogate key for each data entity Its value is unique system wide. Its value is never manipulated by the user or the application. It never reused It is frequently sequential number It called synthetic key, an entity identifier, a system-generated key, a databas e sequence number, a factless key, a technical key, or an arbitrary unique ident ifier No duplicate records Showing Answers 1 - 55 of 55 Answers nagaa Aug 16th, 2005 what is the difference b/w rownum and rowid? Was this answer useful? Yes Yes 1 Reply Nimi Aug 25th, 2005 To answer your question its sufficient enough to understand their definitions. Primary Key: A column in a table whose values uniquely identify the rows in the

table. A primary key value cannot be NULL. Unique Key: Unique Keys are used to uniquely identify each row in an Oracle tabl e. There can be one and only one row for each unique key value. Surrogate Key: A system generated key with no business value. Usually implemente d with database generated sequences. Was this answer useful? Yes Yes 4 Reply Archit Dave Aug 26th, 2005 i have one question is that what are the differences between primary key and unique key Was this answer useful? Yes Yes 1 Reply vandita Aug 27th, 2005 we can refer the primary key value in some other table ie child table. Was this answer useful? Yes Reply suresh Aug 27th, 2005 primaruy key should not dontain nulls and unique key can contains null values Was this answer useful? Yes Yes 3 Reply Vijay Raj Jaiswal Sep 20th, 2005 Primay Key Unique key ---------------------------1.There is only one there may be more than 1 Primary key for Unique Key in table 1 table 2.It can contain It Can contain Null Value Null value Was this answer useful? Yes Yes 1 Reply gomathi.e Sep 30th, 2005 unique key - allowes NULL ValuesPrimary Key - not allowed The NULL values Was this answer useful? Yes Yes 2 Reply chandrakant Oct 16th, 2005 nagaa Wrote: what is the difference b/w rownum and rowid? what is the difference between primary key, unique key, sorrougate key? 3how Was this answer useful? Reply PSRaja Oct 18th, 2005

Yes Yes

1

send me the differences bet unique key & primary key Was this answer useful? Yes Yes 1 Reply santhi Nov 23rd, 2005 both primary key and unique key doesn t have duplicate values. but there will be only primary key in a table and u can have any number of unique keys. Was this answer useful? Yes Yes 2 Reply Rajesh Kashyap Dec 27th, 2005 Primery Key:- A column or set of columns that uniquely identify all the rows in a table. Primary keys do not allow null values. No two rows can have the same pr imary key value; therefore, a primary key value always uniquely identifies a sin gle row. Unique Key:- Unique Keys are used to uniquely identify each row in an Oracle tab le. There can be one and only one row for each unique key value. no value repea te in the other rows of the same table. Surrogate key:- An ID column populated via a sequence is known as a surrogate key. if the table has a true primary key Was this answer useful? Reply KOTESWARA RAO CHAVA Apr 15th, 2006

Yes

primary key : duplicate recrods will not allowe, null values also will not consider, index automatically created on a column which u set a primary key column, Was this answer useful? Yes Reply KOTESWARA RAO CHAVA Apr 15th, 2006

primary key : duplicate recrods will not allowe, null values also will not consider, index automatically created on a column which u set primary key column,

unique

key : no repeated twise a value in a

Was this answer useful? Reply rajanipriya May 9th, 2006

a

columns along with zero

Yes

Aactually, the rows of a table in database terminology are considered as candi dates of a table. So, the colunm that uniquely identifies these candidates of a table is called CAndidate Key. Candidate key shortly called as key ---->> The column which is used to Uniquely identify the rows of a table. Primary key ----->> Unique Key +Not Null contraint Unique Key

----->>

The key column which has no repeated values in it.

Was this answer useful? Reply yogesh _ gyanendra Dec 13th, 2006

Yes

Hi dear,primary key can t contain NULL but Unique key contain NULL value........ .. Was this answer useful? Yes Reply shilpa ashwin Feb 8th, 2007 ROWID : Unique index number of every row of a table maintained by the database a utomatically

ROWNUM: Sequential number of rows in the resultset object. Was this answer useful? Yes Reply praveenladdu1 Mar 7th, 2007 hi this is correct u people r saying wt is primary and wt is unique key but the diffrence is unique key contains only unique valvues i.e it does not allow duplicate values and where as a primary key allows only unique values and also it have NOT NULL charactaristic too .. Was this answer useful? Yes Reply Rohan Kumar K Mar 26th, 2007 Primary key: key + not null values in the table. Unique key : key + null /not null values in the table. Was this answer useful? Yes Reply Yogita Apr 18th, 2007 Primary Key: 1) It creates clustered index by default 2) It doesn t allow nulls Unique Key: 1) It creates non-clustered index by default 2) It allows only one null value Was this answer useful? Yes Reply AMIYA KUMAR SAHOO May 2nd, 2007 The Primary key never contains any Null value Was this answer useful? Yes Yes 1 Reply raafay

May 2nd, 2007 primary key cannot contain null value but unique can contain a single null value or primary key have to be not null and unique key can null or not null. Was this answer useful? Yes Yes 1 Reply P.V.Sarveswararao May 25th, 2007 Unique key can contain null values Primary key can t contain null values Primary key is a combnation of Unique and notnull. Was this answer useful? Yes Yes 1 Reply Srinivas N Jun 27th, 2007 Primary Key : Can be only one per table Can be created on one or more than one column Will not allow null values Can be reffered in another table i.e primary key and foreign key relation Unique Key: It allows null values If values exists it should be unique Was this answer useful? Yes Yes 3 Reply bhargav Jul 18th, 2007 Primary key is a combination of unique key and not null Was this answer useful? Reply vikrant Jul 21st, 2007

Yes Yes

1

Primary key is having both the constraints that are unique key and not null. The re can be only one primary key in a table. But unique key is a key which uniquel y identifies each row in a table but its not mandatory that it should be primary key but a primary key has to be unique as well as not null key. There can be mo re than one unique key in a table. Was this answer useful? Yes Reply Amit Aggarwal Aug 11th, 2007 Primary key has a unique value it doesn t contains Null value but in the unique key it takes null value also but only once time. Was this answer useful? Yes Reply Prakash Chandra Das Aug 18th, 2007 Primary Key will never contain Null Value. Primary Key is a key is always NOT NU LL Was this answer useful? Yes Reply kamalalkarim Aug 30th, 2007

Well the basic difference b/w primary key and the unique key is... Primary key not only keeping the unique record in the particular field also it s orts the records as well and by this key we can relate other tables by a creatin g a referential key. unique key only act in a column to keep the non-duplicate records. I hope it would clear the idea about these two keys. Regards, Was this answer useful? Reply swaminathan Sep 14th, 2007

Yes

Unique Key - If a column is defined as UNIQUEY KEY, it only accept Non-Duplicate value. But The column can contain a null value. Primary Key - Primary key combines UNIQUE + NOTNULL If a column is defined as primary key, the column can accept e value only. Was this answer useful? Yes Reply prasads_delight1 Sep 18th, 2007

NOT null and uniqu

The field which is bounded by the Primary key constraint should not accep t null valus and duplicate valus. And the primary key constraint field of a tabl e will give the provision to relate with another table in the database which con tains the field with same datatype. More than one primary key fields are possible in a single table, but stri ctly depends upon the nature of the data and the relations among the fields in t he table. Was this answer useful? Yes Reply harshada Sep 30th, 2007 unique key is a key which is only one key while primary key is a key which is on ly unique Was this answer useful? Yes Reply sridhar Oct 4th, 2007 Both are pseudo columns, one thing is, rownum will create on a fly while display ing resultset whereas rowid is the address of the record for the entire database . Was this answer useful? Yes Reply purnimamca Nov 5th, 2007 difference between primary key an dunique key is by default clustered index is c reated in primary where as a non clustered index is created in unique key and it alsoe enforces uniqueness of the column Was this answer useful?

Yes

Reply Sunil Raina Nov 26th, 2007 Primary key creates a clustered index and unique key creates nonclustered index Was this answer useful? Yes Reply Reddy555 May 24th, 2008 Primary key does not allow nulls and repeated values where as unique key accepts nulls but in unique key data should not repeated Was this answer useful? Yes Reply navin05_06 Jun 2nd, 2008 Primary Key allows not null values and unique values Unique key allows null and unique values Only Primary Key is allowed for a table but Multiple unique key can exist in a table Was this answer useful? Yes Reply pradhanmk1 Jun 10th, 2008 Primary Key: A column in a table whose values uniquely identify the rows in the table.There can be only one primary key in a table. A primary key value cannot b e NULL. Unique Key: Unique Keys are used to uniquely identify each row in an Oracle tabl e. There can be one or more unique key in a table. Surrogate Key: A system generated key with no business value. Usually implemente d with database generated sequences. Was this answer useful? Yes Reply Rajanikanththota Jul 23rd, 2008 A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UN IQUE constraint per table. When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table. * The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index. Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept N ULL and can have some values on them, as long as the combination values is uniqu e. Was this answer useful? Yes Reply arvindgeek Aug 7th, 2008 We can have foreign key refer to Unique Key (UK) and also to Primary Key (which is combination of UK+not null); then it means we can create primary key equivale

nt constratint on a column with the help of UK and Not Null, then why do we have PK? Was this answer useful? Yes Reply santoshkharma Aug 15th, 2008 Primary Key: 1) One Primary Key per Table 2) By Default creates Clustred Index 3) Cannot Have Null values. Unique Key 1) Can have multiple Unique keys. 2) By default creates Non-Clustred Index. 3) Can Have one Null value. Shoot a question on MSSql concepts. Thanks and Cheers!!! Was this answer useful? Yes Yes 1 Reply prashantmirje2000 Feb 25th, 2009 primary key

unique key

can t NULL

can NULL

By default create cluster index Was this answer useful? Reply suman thummala Jun 30th, 2009

By default create non cluster index Yes

rownum is a pseudo column which is generated for query data set at runtime. whil e rowid is the physical address of the row and hence definition suggest rowid fo r a row will never change but row num will always change. Rowid has a physical significance i.e you can read a row if you know rowid. It i s complete physical address of a row. While rownum is temporary serial number allocated to each returned row during qu ery execution. Primary Key is Unique and not null Unique Key is Unique and can contain null values. Was this answer useful? Yes Reply imnrj23 Nov 11th, 2009 Primary Key & Unique Key 1.))) P_key - A column in a table whose values uniquely identify the rows/tuple/entry of the table. A primary key value cannot be NULL. U_key - A column in a table whose values uniquely identify the rows/tuple/entry of the table. A unique key value can be NULL.

2.))) P_key - A table can have only 1 primary key (If its on multiple columns, its a c omposite key). U_key - A table can have 1 or more Unique keys. 3.))) P_key - Clustered index is created on Primary key constraint U_key - non clustered unique indexes iscreated on Unique key constraint. Surrogate Key: A system generated key with no business value. Usually implemente d with database generated sequences. Surrogate Key features: the value is unique system-wide, hence never reused; the value is system generated; the value is not manipulable by the user or application; the value contains no semantic meaning; the value is not visible to the user or application; the value is not composed of several values from different domains. Was this answer useful? Yes Yes 1 Reply Raza Darvaish Apr 1st, 2011 A primary key doesn t allow nulls, but a unique key can allow ONE null. A primary key can be a foreign key in other relation while a unique key cannot b e referenced. Was this answer useful? Yes Reply rahulksinghh Jul 7th, 2011 difference between rowid and rownum you can check rowid and rownum for every table by using syntax select rownum,rowid from tablename; when you create a table than rownum and rowid automatically created by database like here i created a table fee,than by using syntax select rowid,rownum,fid,fee ammount,student_id from fee; we got the table which have rowid and rownum...... ROWNUM ROWID FID FEEAMMOUNT STUDENT_ID ---------- ------------------ ---------- ---------- -----------1 AAAEfwAABAAAKxCAAA f01 56000 1ms08is088 2 AAAEfwAABAAAKxCAAB f02 450000 1ms08is088 3 AAAEfwAABAAAKxCAAC f03 45000 1ms08is090 so as we can see rownum is the number for each record given by database.....and rowid is a id given by database for each record... Was this answer useful? Yes Reply Kolta Sam Jul 10th, 2011 Primary Key: It is a visible key It generated by user or application. It could be changed by the user or application. It could be queried

It used to form a relation between tables It shouldn't contain null value It resemble table row It is a unique identifier for a table object. It contains only one key It could contain numeric and strings characters. It is an unique key which each row contain a distinct different key. Example for it is a customer_Id. It always starts by number one and second is two and so on but can starts with a different number. Could created on one or more columns No duplicate records Secondary Key: It used to form a relation between tables. It is alternate table key. It used to search data with primary key It could contains null value It could contains more than one secondary key for each table Created only on one columns No duplicate records It creates index clustered by default Surrogate Key: It is invisible key for the user or the application. It resembles database entity. It generated by the system so it is invisible for user and application. It shouldn't contain null values Only one surrogate key for each data entity Its value is unique system wide. Its value is never manipulated by the user or the application. It never reused It is frequently sequential number It called synthetic key, an entity identifier, a system-generated key, a databas e sequence number, a factless key, a technical key, or an arbitrary unique ident ifier No duplicate records Was this answer useful? Yes Reply nandha Aug 17th, 2011 Primary key- It is unique identifier,it will not accept null values, Unique- it is unique identifier it is accept null values.. Was this answer useful? Yes Reply Gopinath Aug 17th, 2011 Primary key is the key can have unique value and non null records. where as uniq ue key can have unique value but it allow only one null value. Was this answer useful? Yes Reply arjun Sep 1st, 2011 Primary key: unique+notnull It is implicitly indexed.

Unique key accepts null and non-unique records, No indexing is done automatically. Was this answer useful? Reply rama Sep 5th, 2011

Yes

primary key is not null +unique unique key is null or not null +unique Was this answer useful? Yes Reply Rajakumar B V Sep 11th, 2011 Primary key 1) It creates clustered index by default 2) It doesn t allow nulls Unique Key: 1) It creates non-clustered index by default 2) It allows only one null value Was this answer useful? Yes Reply Lucky Nov 29th, 2011 We can create only one primary key per table where as we can create numbers of u nique key in a table. Primary key does not accept any null value but unique key accepts maximum of one null value. Primary key creates clustered index by default where unique key creates non-clus tered index by default. Was this answer useful? Yes Reply Faisal zaman Gandapure Jan 26th, 2014 there is a minor difference between them.. Primary Key : This is a unique key . it does not allow null values. Unique Key: This key is also unique . And it allow null values and but only one. Was this answer useful? Yes Reply Kote Mar 26th, 2014 Primary key does not accept any null value but unique key accepts null value for each row of Unique columns. Kote Was this answer useful? Yes Reply gagandeepp Aug 21st, 2014

null values can be used more than one times in the unique key column .....becaus e null is not equal to null or null is not equal to zero or null is not equal to space.............. >> << What is the diffrence between and constraints and triggers? Interview Candidate Sep 9th, 2005 12 11298

BasicsAnswerFirstPrevNextLast Showing Answers 1 - 12 of 12 Answers Sunil Sep 9th, 2005 Constraints are used to maintain the integrity and atomicity of database .in oth er words it can be said they are used to prevent invalid data entry . the main 5 constraints are NOT NULL,PRIMARY KEY,FOREIGN KEY,UNIQUE KEY and CHECK Triggers are bascically stored procedures which automaticallly fired when any in sert,update or delete is issued on table Was this answer useful? Yes Yes 1 Reply rahultripathi Sep 28th, 2005 Another most imp. deff. is that trigger effected only those row after which trig ger applied but constraint effected all row of table . Was this answer useful? Yes Reply krishnaveni Sep 29th, 2005 Generally we cant compare between and constraintsbut the difference between cons traints and triggers is already there see. Was this answer useful? Yes Reply Piyushjohn May 4th, 2006 Triggers - They start a chain reaction- for instance each delete, update action etc. can trigger off another function Contstraint is set when the application is customized so that there are restrict ions to the data entered, so that the data is authentic. Was this answer useful? Yes Reply RamaKrishna Yerra,TCS,Hyd,9989018508 Aug 31st, 2006 Both are used for Business Rules... But the major diff is... Triggers can nt fire on the Pre-Loaded data inthe table where as Constraints can be.. Thanks&Regds RamaKrishna Yerra,TCS,Hyd,9989018508 Was this answer useful? Yes Reply Ashish Sep 7th, 2006

Triggers are used to carry out tasks which cant be done using constraints. For eg:-A change in the "sal" column of a table should change the "tax" column i n another table.This cant be done using constraints.It has to be done using trig gers.Thats where the importance of triggers lie. Was this answer useful? Yes Reply anil kumar Aug 2nd, 2007 Constraints are the conditions it can set while creating the table. To eliminate duplicates, not null values and also check the conditions.. Triggers are used when the event occurs. Was this answer useful? Yes Reply sankar babu May 25th, 2011 1.constraints are oracles predefined business rules.but triggers are user define d business rules. 2.constraints validates the data immediately but triggers validates the data whe n event occurs. Was this answer useful? Yes Reply z_ashwini Jan 13th, 2012 Contraints can be enforced on DB table to enforce intergrity or uniqueness on a table. Specfically it it a bussiness rule used to validate data. Type of Constra ints are: Primay key, Foreign Key, Unique key, Check constraint... Triggers are DB object that is validated when an event occurs. Type of triggers are : Row level triggers Statement level Triggers Schema level triggers DB triggers Was this answer useful? Yes Reply Chittaranjan Kotian May 16th, 2012 Constraints and triggers are both used to enforce business rules at the database level. Constraints are primary key, unique key, foreign key, not null contraint s etc, while triggers can be defined at the database and table level to do certa in tasks depending on data entered Was this answer useful? Yes Reply vishnuvardhanarao Nov 14th, 2012 Check constraints it cant work multiple tables Was this answer useful? Yes Reply rc Aug 15th, 2014 CONSTRAINTS:= 1.it will check the existing data and feature data. 2.we cant change the constraint behavior these are predefined. 3.constraint are not support the object tables.

4.constraints can create on table,view and define on variables also(default,not null).

Trigger:= 1.It evaluates feature data. 2.we can change the trigger behavior 3.Trigger supports the object tables. 4.trigger are created only on table,view >> << BASECONV.CONVDATA What is the function BASECONV.CONVDATA(FILED_01,ARG_1,ARG_2) used for ? Satish_Chaudhary_2113 Jan 26th, 2008 1 1588 Questions by Satish_Chaudhary_2113 answers by Satish_Chaudhary_2113 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers nagarjuna May 2nd, 2014 baseconv.convdata is the conversion of some encrypted data to numeric data. the conversion values depends on the arguments u are passing for the function >> << Adding new application at existing oracle 11gr2 database. I have a request about adding new application at existing oracle 11gr2 database. What questions should I raise with the vendor about there application and databa se setup requirements. Please give my answer at your earliest possible time, bec ause my nagement waiting for the answers. Thanks in advance. Ibrahim >> << Tablespace Management What are the ways tablespaces can be managed and how do they differ? arunakumaris Mar 22nd, 2010 3 4649 Questions by arunakumaris BasicsAnswerFirstPrevNextLast Showing Answers 1 - 3 of 3 Answers shekar2130 Jul 17th, 2010 We can manage table space in 2 ways, they are: 1. Locally managed 2. Dictionary managed From 10g LMTS is default, DMTS is manual Upto 9i DMTS is default, LMTS is manual The difference between LMTS,DMTS is in LMTS freespace avilable in datafile heade r etc can be checked by Oracle defaultly, whereas in DMTS it can be checked by u ser so automatically it will degrades the performance. Was this answer useful?

Yes

Reply ashdba Sep 5th, 2010 Tablespace can be managed by specifying Extent management local or Extent manage ment dictionary, segment space management auto or manually by using the key word s pctfree, pctused, pctincrease during tablepsace creation. Was this answer useful? Yes Reply Affrayam Mar 8th, 2013 The difference between LMTS & DMTS is if the extent management is local(LMTS) th e information about free extents and free blocks will be maintained in DATAFILE HEADER it self AND if the extent management is dictionary(DMTS) the information about free extents and free blocks will be maintained in DATA DI CTIONARY so internally oracle issues a select statement on DATA DICTIONARY(SYSTE M) in the case of DMTS.

>> << Performance improvement in Bulk Collect What is the amount of performance improvement you get with doing BULK COLLECT? I tried answering I will measure with the cost.. but the interviewer wants a spec ific answer. Is there any general formula for deriving the cost improvement base d on the no. of records processed in bulk fetch? Jamuna_J Jul 6th, 2012 1 3671 Questions by Jamuna_J BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers dinesh Feb 3rd, 2013 We can limit the bulk binding values. It would improve the overall performance. It would not consume more memory. >> << Referential Integrity Constraint What is the role of Referential Integrity Constraint in Normalization? leorence Nov 4th, 2009 1 2073 Questions by leorence BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers surendra Jan 31st, 2013 Best examples of Referential Integrity constants Primary Key And Foreign key. Pr imary Key constraint allow a particular attribute to be not null and unique. Whe re as Foreign key will be used when ever there is a need to maintain the same at tribute values in the multiple tables.

>>

<< Oracle forms... 1) How to create login page in oracle 10g forms. 2) How to create an exe file of oracle 10g database sayyad Sep 26th, 2012 1 3082 Questions by sayyad BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers Sarat Teja Jan 5th, 2013 The login credentials will be requested at the time when you run a form on runti me of developer suit, that particular fmb/fmx is usually known as the login form or the master form from which a menu module is called and the menu module calls other forms. Menu access is controlled by defining roles in the database and th ese roles are hard coded in the property palette of the menu module. When you export the whole database it is created in a .DMP format usually called the database dump. Please research on database import/export. >> << Performance Tuning My query was running fine till last month. the query will run for each month end . suddenly it is taking more than 20 minutes for running. the data is increased by 1 million. the index are all working fine . There is no cpu utilization time and also no memory blocks. What may be the reason?The increase in data has happe ned previous months also srividhya_85 Jun 24th, 2012 2 36003 Questions by srividhya_85 answers by srividhya_85 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 2 of 2 Answers Avi Sep 25th, 2012 check for the bottle necks starts from the source and continue till target Was this answer useful? Yes Reply shams756 Nov 2nd, 2012 1) Try collecting Statistics 2) Spilt your query based on incoming unique values >> << What is Pro*C? What is OCI? Beena Sep 19th, 2005 5 9600 Questions by Beena answers by Beena BasicsAnswerFirstPrevNextLast Showing Answers 1 - 5 of 5 Answers rahultripathi Sep 22nd, 2005 Pro *C

The Pro* c/C++ precompiler takes the SQL statement that embeded in C/C++ code co nvert into standard C/C++ code . when succefully precompile this code ,the resu lt is a C or C++ programe that we compile and used to build the application that access the Oracle Application OCI :- OCI refere to Oracle Call interface is set of Low Lavel API(Applcation Pr ogram Interface Call) used to intract with Oracle Database. By OCI one can use t he operation such as Logon,Fatch,parse ,excute etc. Generally these are written in C/C++. These can be written in any language. Any problem Don t Hesitate write [email protected] Was this answer useful? Yes Reply sravan May 18th, 2007 Pro*C is an embedded SQL statements. in a high level source program. OCI--Oracle call Interface Was this answer useful? Yes Reply zelani Jan 12th, 2009 Pro*c is a precompiler tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc. The precompiler accepts the source program as input, translates the embedded SQL statements into standard Or acle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way. Examples are the Pro*C Precompiler for C, Pro*Cobol for Cobol, SQLJ for Java etc Was this answer useful? Yes Reply Nisikant Mar 3rd, 2009 Pro *C is a Oracle database pre-compiler. It acts as a database connectivity. We can also embed the SQL statement within C or C++ programming code which will ex ecute successfully. Was this answer useful? Yes Reply Adas Nov 1st, 2012 Can anyone tell the exact diff b/w Pro*C and OCI >> << Primary and unique key In which scenario we can use primary and unique key in same table??????? sachinkshd Oct 1st, 2012 1 2889 Questions by sachinkshd BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers abinay Oct 6th, 2012 the combination of unique key and not null key is called primary key. In a table we use only one primary key.It is unique,it cant allow the null value s. UNIQUE key ,in a table we use more than one unique key.It is also a unique numbe

r,but it allows null values. If a table having the more than one unique columns,in that situation we use one column as primary key, remain once use unique key. >> << Flow in Oracle Database. What happens when a query is submitted in oracle? Please give the complete flow as to which all processes act and how the data is submitted / retrieved to / fro m the database . Bharatsubnis Apr 13th, 2012 2 6789 Questions by Bharatsubnis BasicsAnswerFirstPrevNextLast Showing Answers 1 - 2 of 2 Answers Chittaranjan Kotian May 16th, 2012 A query is first checked for semantics, and is then parsed. Parse can be hard pa rse or soft parse. Oracle will check if the parsed statement exists in the libra ry cache. If it exists the existing statement is used, If not, the query stateme nt will be hard parsed and placed in the library cache. The optimized then decid es on the best execution plan and executes the statement. The data to be retriev ed is fetched from the buffer cache if it already exists, if not the data blocks are read, data fetched and stored in the buffer cache and presented to the sess ion requesting the data Was this answer useful? Yes Reply himanshu Sep 27th, 2012 1. RDBMS checks if a copy of the parsed SQL statement exists in the library cach e. If parsed copy exists, then steps 2 to 6 are skipped. 2. RDBMS validates the syntax of the statement. 3. RDBMS ensures that all the columns and tables referenced in the statement exi st. 4. RDBMS acquires parse locks on objects referenced in the statement so that the ir definitions do not change while statement is parsed. 5. RDBMS ensures that the user has sufficient privileges. 6. Statement is parsed and execution plan is created. 7. Statement is executed. 8. Values are fetched. >> << Creating a matrix query Create a matrix query to display the job, the salary for that job based on depar tment number and the total salary for that job, for departments 20,50,80, and 90 ,giving each column and appropriate heading. zecar Sep 14th, 2006 12 14176 Questions by zecar answers by zecar BasicsAnswerFirstPrevNextLast Showing Answers 1 - 12 of 12 Answers vssandilya Sep 24th, 2006 dear friend,

try this code. select department_id,job_id,sum(salary) from employees where department_id in(20,50,80,9) group by rollup(department_id,job_id) then reply me. sandilya Was this answer useful? Yes Reply adarsh_sp Oct 3rd, 2006 select department,job,sum(salary) from emp where department in(20,50,80,9) group by rollup(department,job) Was this answer useful? Yes Reply Vijayalakshmi Jul 6th, 2007 SELECT DISTINCT job, SUM(CASE deptno WHEN 20 THEN sal END) "Dept 20", SUM(CASE deptno WHEN 50 THEN sal END) "Dept 50", SUM(CASE deptno WHEN 80 THEN sal END) "Dept 80", SUM(sal) "Total" FROM emp GROUP BY job; Was this answer useful? Reply geethika Oct 10th, 2007

Yes Yes

1

select distinct job, sum(decode(deptno,20,sal) ) "20" ,sum(decode(deptno,50,sal) )"50" ,sum(decode(deptno,80,sal) ) "80" ,sum(decode(deptno,90,sal) )"90" ,sum(sal) "total" from emp group by job; Was this answer useful? Yes Reply Pallavi50953 Dec 13th, 2007 select * from (select job,sum(decode(deptno,20,sal)) dept20, sum(decode(deptno,50,sal)) dept50, sum(decode(deptno,80,sal)) dept80, sum(decode(deptno,90,sal)) dept90, sum(sal) total_sal from emp group by job) order by 1 Was this answer useful? Yes Yes 1 Reply madhukar.charla Jan 27th, 2008 the date is given in the exceel sheet how do we import in to winrunner plz answe r my question Was this answer useful? Yes Reply shyamvarmatra Aug 8th, 2008

select job_id,salary,department_id,avg(salary) "Average Salary" from employees where department_id in(20,80,90,50) group by job_id,salary,department_id order by job_id Was this answer useful? Yes Reply pchandna Jul 23rd, 2009 select job,deptno,salary, sum(salary) over( partition by deptno,job) dep_sum_sal from emp Was this answer useful? Yes Reply ARAVINDA Aug 3rd, 2011 Code SELECT FROM

* (SELECT job,

SUM(DECODE(deptno,10,sal)) DEPT10,

SUM(DECODE(deptno,20,sal)) DEPT20, SUM(DECODE(deptno,30,sal)) DEPT30,

SUM(DECODE(deptno,40,sal)) DEPT40 FROM scott.emp

GROUP BY job)

ORDER BY 1;

SELECT

FROM

*

(SELECT job, SUM(DECODE(deptno,10,sal)) DEPT10,

SUM(DECODE(deptno,20,sal)) DEPT20,

SUM(DECODE(deptno,30,sal)) DEPT30, SUM(DECODE(deptno,40,sal)) DEPT40

FROM scott.emp GROUP BY job)

ORDER BY 1;

Was this answer useful? Reply Mahesh Bhiosale Aug 19th, 2011

Yes

Hi bro, I had recentely learn about matrix queries. By using following code into oracle 9i or sql server 2005 you can create matrix query to get desired result as per ur wish.

Code SELECT FROM

* (SELECT job_id,

SUM(DECODE(department_id,10,salary)) DEPT10, SUM(DECODE(department_id,20,salary)) DEPT20,

SUM(DECODE(department_id,30,salary)) DEPT30,

SUM(DECODE(department_id,40,salary)) DEPT40 FROM employees GROUP BY job_id) Was this answer useful? Reply Neha Sep 25th, 2011

Yes

select job in job, sal in salary, sum(salary) in total_sal where job=(select job in job where deptno=20 and 50 and 80 and 90) ; Was this answer useful?

Yes

Reply sachin Apr 18th, 2012 select job, sum(decode(deptno,10,sal)) deptno10, sum(decode(deptno,20,sal)) deptno20, sum(decode(deptno,30,sal)) deptno30, sum(decode(deptno,40,sal)) deptno40 from emp group by job order by 1; >> << Reverse a string in Oracle without using reverse How do I reverse a dtring in Oracle without using PL/SQl and without using the r everse function?? amit88 Apr 5th, 2012 1 7332 Questions by amit88 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers Mark Haynes Apr 13th, 2012 Are you looking for something like this? Code FUNCTION revstring(instring VARCHAR2) RETURN VARCHAR2 IS

rstring VARCHAR2(750) := ; iCnt

PLS_INTEGER;

BEGIN FOR icnt IN REVERSE 1..LENGTH(istring) LOOP

BEGIN

rstring := rstring ||

SUBSTR(istring, icnt, 1);

END; END LOOP;

RETURN rstring; END; >> << What is a mutating trigger error? How can we resolve it?I need this reply asap.

Thanks be17be Dec 1st, 2006 6 8479 Questions by be17be answers by be17be BasicsAnswerFirstPrevNextLast Showing Answers 1 - 6 of 6 Answers joseph0609 Dec 5th, 2006 MUTATING TRIGGER ERROR IS NOTHING BUT MUTATING TABLE ERROR. FIRST WHAT IS MUTATION WHY IT HAPPENS? FOR EXAMPLE A TRIGGER IS WRITTEN (BEFORE UPDATING A RECORD CHECK FOR VALIDATION OF THAT VALUE) WHILE A UPDATE STATEMENT IS WRITTEN EXPLICITLY THEN TRIGGER IS EXECUTED IF THE TRIGGER KEEPING THE SELECT STMT THERE THE PROBLEM OCCURS THIS P ROBLEM IS TRIGGER MUTATION HERE ORACLE PL/SQL ENGINE TRYS TO UPDATE THE RECORD VALUE AND SIMULTANEOUSLY WE ARE READING THE RECORD VALUE INSIDE THE TRIGGER USIN G A SELECT STMT HERE ORACLE PL/SQL ENGINE WILL BE IN A BIT OF CONFUSION WHICH VA LUE TO UPDATE WHETHER WHICH VALUE TO UPDATE . WHILE SELECT STMT THE TABLE WILL BE LOCKED. Was this answer useful? Yes Reply joseph0609 Dec 5th, 2006 WE CAN RESOLVE IT BY NOT KEEPING A SELECT STMT INSIDE A TRIGGER IN MUTATING TRIG GER WE ARE SIMULTANEOUSLY READING THE VALUE AND WANTS TO UPDATE IT. SO WE BYPAS S BY READING THE VALUE WITH A SEPERATE VARIABLE IN A PROCEDURE ,SO BY READIN G THE OLD VALUE THROUGH THIS VARIABLE. AFTER STORING THE VALUE IN THAT VARIABLE WE ARE GOING FRO UPDATION . Was this answer useful? Yes Reply shravanam Oct 24th, 2007 WHEN WE ARE UPDATING A TABLE AND AT THE SAME TIME TRYING TO RETRIEVE DATA FROM THAT TABLE. IT WILL RESULT INTO MUTTAING TABLE AND IT WILL RESULT INTO MUTATING ERROR. WE CAN RESOLVE IT BY DECLARING PRAGMA AUTONOMUS TRANSACTION TION OF THE BLOCK Was this answer useful? Yes Yes 1 Reply xxx123 Feb 24th, 2011

IN DECLARATIVE SEC

While updating any table from that table dont use SELECT statement inside the tr igger. Was this answer useful? Yes Reply Kranthi Jul 13th, 2011 In before update/delete trigger if we use select statement for fetching the reco rds.in this scenario will wet mutating trigger error.using row level trigger we can avoid instead of statement level trigger. we can accurate results. Was this answer useful? Yes Reply Ashish Mar 24th, 2012

See, in oracle to maintain read consistency oracle keeps a copy in undo section of database for the data which is under modification by some transaction. And so readers do not wait for writers and can read the previous data from this sectio n until it is not committed by writers. So my question is why we can not retriev e data in this fashion in case of this also . After all we are only reading the data in triggering body. >> << Table contain 2 columns like name and genderfor that i need output like this..co untof female countofmale totalcountforeg 5 3 8 Interview Candidate Oct 24th, 2006 7 1325

BasicsAnswerFirstPrevNextLast Showing Answers 1 - 7 of 7 Answers Sandeep Sharma Oct 24th, 2006 Select Male,Female,Other,(Male+Female+Other) total from ( select count(*) Male from voters where gender = 1 ) A, ( select count(*) Female from voters where gender = 0 ) B, ( select count(*) Other from voters where gender is null ) C ; Was this answer useful? Yes Reply sanju699369 Oct 24th, 2006 Select Male,Female,Other,(Male+Female+Other) total from ( select count(*) Male from voters where gender = 1 ) A, ( select count(*) Female from voters where gender = 0 ) B, ( select count(*) Other from voters where gender is null ) C Was this answer useful? Yes Reply ora.nachs Feb 7th, 2007 Hi ,you can go for this also.Select count(decode(gender,M,name,null)) MALE,count(d ecode(gender,F,name,null)) FEMALE,count(*) TOTAL from emp_table; Was this answer useful? Yes Yes 1 Reply vkc_keerthi Apr 14th, 2010 select count( case when gender= M then 1 end) "Male_count" , count(case when gender= F then 1 end) Female_count, count(*) Total_count from temp1 Was this answer useful? Yes Reply neelapu Apr 16th, 2010 SELECT COUNT(decode(gender, M ,1,0)) "count of male", COUNT(decode(gender, F ,1,0)) "count of Female", COUNT(*) "Total Count"

FROM GROUP BY gender; Was this answer useful? Reply neelapu Apr 16th, 2010

Yes

SELECT COUNT(decode(sex1, F ,1)) "female count", COUNT(decode(sex1, m ,1)) "male count", COUNT(*) "total count" FROM Was this answer useful? Yes Reply PK Feb 29th, 2012 There is one Oracle function ROLLUP, the same can be used for required output QUERY: SELECT GENDER,COUNT(GENDER) FROM TABLE_NAME GROUP BY ROLLUP(GENDER); >> << Materialized View Memory Space How to see materialized view occupied memory space? nageshk143 questions Profilequestions Answers by nageshk143questions Questions by nageshk14 3 Jun 14th, 2010 1 1973 Questions by nageshk143 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers PK Feb 29th, 2012 There is a data dictionary called as ALL_MVIEWS. You can check the query and the length of the query in this data dictionary as Select * from ALL_MVIEWS where MVIEW_NAME = MVIEWNAME >> << Join two Columns with different datatype How to join two columns which is having different datatype? Say Coulmn1 with Var char2 datatype and Coulmn2 with long datatype. RPush Jul 7th, 2010 1 2305 Questions by RPush answers by RPush BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers PK Feb 28th, 2012 You can join the columns with two different data types by using to_char, to_numb er to_date functions. For e.g. one field is a varchar which stores number and other field is a number

then you can use. to_number(var_column) = number_column >> << Write a query to display the no.of people with the same job zecar Sep 14th, 2006 12 7074 Questions by zecar answers by zecar BasicsAnswerFirstPrevNextLast Showing Answers 1 - 12 of 12 Answers oradev03 Sep 17th, 2006 SELECT job,COUNT(*) As "No of employees" FROM emp GROUP BY job; Was this answer useful? Yes Yes 1 Reply Dakshina Murthy Dec 22nd, 2006 SELECT job,COUNT(*) FROM emp GROUP BY job; Was this answer useful? Reply amikat Jun 3rd, 2008

Yes

select job, count(ename) from emp group by job; Was this answer useful? Yes Yes Reply sravee123 Jun 4th, 2008 SELECT job,COUNT(*) FROM emp GROUP BY job; Was this answer useful? Reply lanka_satya Sep 12th, 2008

1

Yes

select count(name) from emp e where 1>(select count(*) from emp x where x.job=e .job) Was this answer useful? Yes Reply POPAM143 Oct 29th, 2008 SELECT job, count(*) AS no.ofemployee FROM emp GROUP BY job; Was this answer useful? Yes Reply sankar babu May 25th, 2011 select job,count(*) no_of_employees

from emp group by job; Was this answer useful? Reply machomanic May 26th, 2011

Yes

Hello, This will give you the number of employees according to the job. select count(*), job from emp group by job; Was this answer useful? Reply sandeshshinde000 Jun 4th, 2011

Yes

select job, count(job) from emp group by job; Was this answer useful? Yes Reply babu s Jun 6th, 2011 select job count(*) from emp group by job; Was this answer useful? Reply z_ashwini Jan 13th, 2012

Yes

Code SELECT count(*), job

FROM emp GROUP BY job Was this answer useful? Reply Iyappan Feb 24th, 2012

Yes

select job, count(job) from emp group by job; >> << Use of Constraint Name What is the use of mentioning of constraint_name along with NOT NULL Constraint _type while creating a table? svp.kiran Sep 28th, 2008 3 1720 Questions by svp.kiran BasicsAnswerFirstPrevNextLast Showing Answers 1 - 3 of 3 Answers see_the_c

Oct 25th, 2011 The database server implements the constraint as an index. Whenever you use the single- or multiple-column constraint format to place a dat a restriction on a column, but without declaring a constraint name, the database server creates a constraint and adds a row for that constraint in the sysconstr aints system catalog table. The database server also generates an identifier and adds a row to the sysindexe s system catalog table for each new primary-key, unique, or referential constrai nt that does not share an index with an existing constraint. Even if you declare a name for a constraint, the database server generates the n ame that appears in the sysindexes table.

If you want, you can specify a meaningful name for the constraint. The name must be unique among the names of constraints and indexes in the database. Constraint names appear in error messages having to do with constraint violation s. You can use this name when you use the DROP CONSTRAINT clause of the ALTER TA BLE statement. Ex: DROPPING OF CONSTRAINTS: ALTER TABLE EMP DROP CONSTRAINT

Was this answer useful? Reply jimmy.katiyar11 Nov 29th, 2011

Yes

Hi, Mainly use constraints for a businessmen rule that restrict enter the record in a table. IN oracle there are 5 type of constraints --- 1) Primary key, 2) foreign key 3) Unique key 4) Not NUll 5) Check Not Null constraint restrict the user can t null the column record means must be value every records in a column In Oracle we check the constraint by the help of these query see below-Select * from user_constraints, --- If show the which column and which table column constraints by the help --Select * from user_cons_constraints; Thanks Was this answer useful? Reply naizy_dba

Yes

Feb 18th, 2012 Constraint are easy to reference if you give them a meaningful name. It is not n ecessary to assign name to constraint. If you do not assign any name to constrai nt Oracle will assign a name to constraint which will like sys_c(any six digit n umber). To check constraints applied on your table, simply apply this query : Code SELECT constraint_name,constraint_type,column_name,STATUS FROM user_constraints u1 JOIN user_cons_columns USING(constraint_name) WHERE u1.table_name=name of TAB LE IN capital letters; Here, constraint name is name given to any constraint by you or Oracle. Constraint type is type of constraint like not null, check, unique etc. Column name on which constraint is applied status of constraint whether it is enabled or disabled. user_constraints and user_cons_columns are two data dictionary views. u1 is the alias given to user_constraints. >> << What is the use of SCN number? When does SCN increment? muralikasya questions Profilequestions Answers by muralikasyaquestions Questions by muralika sya Dec 22nd, 2011 1 2191 Questions by muralikasya

answers by muralikasya

BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers Neer.ocp10g Jan 20th, 2012 The SCN is an important quantifier that oracle uses to keep track of its state a t any given point in time. The SCN is used to keep track of all changes within t he database, its a logical timestamp that is used by oracle to order events that have occurred within the database. SCNs are increasing sequence numbers and are used in redo logs to confirm that transactions have been committed, all SCNs ar e unique. SCNs are used in crash recovery as the control maintains a SCN for eac h data file, if the data files are out of sync after a crash oracle can reapply the redo log information to bring the database backup to the point of the crash. You can even take the database back in time to a specific SCN number (or point in time). >> << What is the Difference between Replace and Translate Interview Candidate Mar 22nd, 2006 14 21590

BasicsAnswerFirstPrevNextLast Editorial / Best Answer Answered by: Saumendra Mohanty

May 16th, 2006 Both Replace and Translate are single row functions in Oracle 9i. The Replace Function replaces single character with multiple characters. But in Translate Function replaces sinlge character with sinlge character only. Showing Answers 1 - 14 of 14 Answers gomathi.e Mar 24th, 2006 replace chages the record in database. but translate won t change it the database content is not changed Was this answer useful? Yes Reply meetnaveen4u Mar 24th, 2006 Replace used for String and Translate used for Character Was this answer useful? Yes Yes 1 Reply Saumendra Mohanty May 16th, 2006 Both Replace and Translate are single row functions in Oracle 9i. The Replace Function replaces single character with multiple characters. But in Translate Function replaces sinlge character with sinlge character only. Was this answer useful? Yes Yes 5 Reply oradev03 Sep 14th, 2006 TRANSLATE function: Translate function does character by character substitution in a string format- TRANSLATE(STRING,IF,THEN) Translate looks at each character in STRING and then check IF to see if that character is there, if it is there then it notes the position in IF where it found the character and then looks the same position in THEN for example-1. SELECT TRANSLATE(7671234,234567890, BCDEFGHIJ ) FROM dual; Result of the query above is- GFG1BCD 2. SELECT TRANSLATE( NOW VOWELS ARE UNDER ATTACK , TAEIOU , Taeiou ) FROM dual; Result- NoW VoWeLS aRe uNDeR aTTaCK this feature of TRANSLATE, ability to eliminate characters from a string, can pr ove very useful in cleaning up data. one more example SELECT amountchar,TRANSLATE(amountchar, 1,$ , 1 ) FROM comma; result: AMOUNTCHAR TRANSLATE(AMOUNTCHAR....) $0 0 $0.25 0.25 $1.25 1.25 $12.25 12.25 $1,234.25 1234.25 3. SELECT AMOUNTCHAR,TRANSLATE(AMOUNTCHAR, ,s , ) FROM dual; AMOUNTCHAR TRASLATE(AMOUNTCHAR , ) $0 $0.25 $1.25 $12.25 Without at least one real character in the THEN TRANSLATE produces nothing. -----------------------------------------------------------------------REPLACE REPLACE function replaces a character or characters in a string with zero or mor

e charcters REPLACE( ADAH , A , BLAH ) this evaluate the string ADAH . Everywhere an A is found, it will be replaced with a string BLAH so the result will be- BLAHDBLAHH 2.REPLACE( GEORGE , GE ,NULL) result- OR ---- Courtesy: Oracle Complete Reference Book Was this answer useful? Yes Yes 3 Reply dl_mstr Feb 12th, 2008 Translate replaces by position, the first character of the list to match is repl aced by the first character of the replacement list. The second character with t he second, and if there are characters in the list to match that do not have pos itional equivalents in the replacements list they are dropped. Replace replaces the string to match with the replacement string. The replacemen t of a single character is the same as that of TRANSLATE. Was this answer useful? Yes Yes 1 Reply dl_mstr Feb 12th, 2008 Translate replaces by position, the first character of the list to match is repl aced by the first character of the replacement list. The second character with t he second, and if there are characters in the list to match that do not have pos itional equivalents in the replacements list they are dropped. Replace replaces the string to match with the replacement string. The replacemen t of a single character is the same as that of TRANSLATE. For Example: SQL> SELECT translate( So What , 2 FROM dual;

o ,

ay )

TRANSLATE ------Sa What SQL> SELECT REPLACE( So What , 2 FROM dual; REPLACE -------Say What Was this answer useful? Reply ashishdixit May 27th, 2008

o ,

Yes Yes

ay )

1

below is an eg showing the difference SQL> select replace( missisippi , is , 12 ) replace, translate( missisippi , is , 12 ) translate from dual; REPLACE ---------m12s12ippi

TRANSLATE ---------m122121pp1

hope it clarifies the doubt! Was this answer useful? Yes Yes Reply rkhreddy Sep 23rd, 2008

1

if we write query like this using traslate function SYNTAX:transalte(string,char which u want replace with,) Suppose for the EMPLOYEE table.... SQL>SELECT TRASLATE (upper(ENAME), ABCDEFGHIJKLMNOPQRSTUVWXYZ , EFGHIJKLMNOPQRSTUVWXYZABCD ) FROM EMP -THIS CHARCTER REPLACED BY -A REPLACED BY E B F C " G | | | | | | / / Z

D

every character that is present in the employee names in the emplooyee table suppose name is PAULL --------->TEYPP LIKE this every chat is replaced by its corresponding character We use traslate function inthe encryption of the data ... Was this answer useful? Yes Reply ravindra1001 Apr 7th, 2009 replace function replaces a sequence of characters in a string with another set of characters. ex. select replace( 123data ,123, X ) from dual;

REPLA ----Xdata here 123 replaces by X translate function replaces a sequence of characters in a string with another se t of characters. However, it replaces a single character at a time. ex1. select translate( 1data23base ,123, XYZ ) from dual TRANSLATE( ----------XdataYZbase Was this answer useful? Reply

Yes

sankar babu May 25th, 2011 replace function replaces entire sting as required but translate function transl ate character by character Was this answer useful? Yes Reply laxmikanta Oct 21st, 2011 hi friend All the above answer are true but one main difference between replace & translat e is in case of replace 3rd argument is optional but in case of translate 3rd ar gument is most required or compulsory .. we cant live up it.. try it to find sol ution.. select replace( manager , man ) rep from dual;........ok select translate( manager , man ) trns from dual;..........it will show error Was this answer useful? Yes Reply vannalas Nov 10th, 2011 Hi, Here s one example of counting the no.of vowels in a string. >select length( Sanjeeva kumar )-length(translate( Sanjeeva kumar , xaeiou , x ) ) from dual Was this answer useful? Yes Reply #emadri Jan 3rd, 2012 It is used to replace one string with another string, and translate used to translate one character to another char. Was this answer useful? Yes Reply Jan 8th, 2012 The Replace Function can replace the old substring whose length are not same to the length of new substring. (length of newsub DONT NEED eaqual tolength of olds ub ) But in Translate Function only can replace same length of the old substring.(Onl y can replate same number of characters of old sub) example: select replace(So What,o,ay) from dual --Say What select replace (So What,So,Say) from dual --Say What select translate(So What,So,Say) from dual --Sa What select translate(So What,o,ay) from dual--Sa What >> << Alternative to TK Prof in oracle 11g Hi, What is alternative to TK prof (used in oracle 9i) in 10g and 11g? Regards,

Shashi ishashi Aug 22nd, 2011 2 4473 Questions by ishashi BasicsAnswerFirstPrevNextLast Showing Answers 1 - 2 of 2 Answers Lokesh M Nov 29th, 2011 You can make use of PL/SQL based TRCA Other notable alternatives are SQLTXPLAIN Method-R profiler OraSRP TVD$XTAT Was this answer useful? Reply geekano Jan 4th, 2012

Yes Yes

1

Trace Analyzer Traditionally, tkprof has been the best tracing diagnostics tool available. That is, until the introduction of Trace Analyzer which is everything tkprof is and more. However, as of version 10.2, the Trace Analyzer utility is still not s hipped with the Oracle DBMS like tkprof. Adv over TK Prof are; 1 Trace Analyzer separates user recursive and internal recursive calls, unlike t kprof 2 Trace Analyzer provides more detailed wait event information, which can be ver y useful to those DBAs that prefer wait-based tuning methodologies >> << What is difference between varchar and varchar2 Interview Candidate Mar 9th, 2006 36 12035 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 36 of 36 Answers asheeshbarua Mar 10th, 2006 varchar means fixed length char varchar2 means variable length char Was this answer useful? Yes Reply mitto Mar 12th, 2006 can i get an example for the two. Was this answer useful?

Yes

Reply niranjan rampure Mar 12th, 2006 varchar stands for variable character length type both are ideally same . varchar2 is supported by Oracle varchar is supported by MSSQL server 2000 Was this answer useful? Yes Yes 1 Reply krishna Mar 15th, 2006 Varchar and Varchar2 are both same except for varchar2 supports upto 2000 char Was this answer useful? Yes Reply venkat_rolta Mar 19th, 2006 both are same varchar is ANSI standardvarchar2 is Oracle Was this answer useful? Yes Reply ramadass Mar 21st, 2006 hi... in oracle name varchar(10)-in this declaration it allocate 10 spaces in me mory.if suppose ur using 4 charecter the extra space wasted. name varcher2(10)-i n this declaration the extra space used by other operations automatically. Was this answer useful? Yes Yes 1 Reply Ankur Apr 10th, 2006 when varchar means fixed leght character then what is the diff between char and varchar Was this answer useful? Yes Yes 1 Reply shambhukjha Apr 20th, 2006 Above said answer was wrong . Actually both varchar and varchar2 means variable character string. varchar2 h as been introduced in higher verson with higher limit. Means It can store more number of character as compared to Varchar Was this answer useful? Yes Reply tjsivakumar Jun 15th, 2006 Varchar and varchar2 are ideally the same. For backward compatibility both are a ccepted. Was this answer useful? Yes Reply tjsivakumar Jun 15th, 2006 There is no differenc between the both. Its accepted in later versions only for backward compatibility. Was this answer useful? Yes Reply sireesha

Aug 3rd, 2006 Hi As u said the diff between varchar and varchar2 is wrong i think. varchar and varchar2 are same. upto 7i we have used varchar after 8i we are using varchar2. ur answer is the diff between char and varchar. Was this answer useful? Yes Reply vidhya Aug 29th, 2006 your reply is not correct. both are of variable length Was this answer useful? Yes Reply Gowri Aug 30th, 2006 Both varchar and varchar2 are same which is of variable length characters but in varchar maximum chars are 2000 and in later one is 4000 bytes. Was this answer useful? Yes Yes 1 Reply barman Sep 3rd, 2006 varchar,varchar2 both assigns memory,but varchar2 assigns double of what varchar assigns Was this answer useful? Yes Yes 1 Reply Nagarajan Sep 4th, 2006 In oracle both varchar and varchar2 are the same with 4096bytes max Was this answer useful? Yes Reply Kiran Sep 29th, 2006 Varchar and Varchar2 behave same in the latest versions of Oracle. Earlier Varch ar could hold 2000 bytes and Varchar2 4000 bytes. In 10g, both are same. As said , Varchar is still kept for backward compatibility. Oracle has already warned us ers not to use Varchar but to use Varchar2. Oracle may depricate it or may use i t for new datatype in future releases. Was this answer useful? Yes Yes 1 Reply Santosh Dec 13th, 2006 Dear, The difference between Varchar2 and Varchar is :1)Varchar2 Maxlength 4000 1)Varchar Maxlength 2000 Both are Variable length character data. In coming Version of oracle u will not see Varchar Was this answer useful? Yes Reply Guest Jan 4th, 2007 VARCHAR in SQL Server corresponds to VARCHAR2 in Oracle. Again, their width is d ifferent (Oracle VARCHAR2 4000, SQL Server VARCHAR 8000). Was this answer useful?

Yes

Reply poonama Mar 9th, 2007 1. VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle suggests the use VARCHAR2 instead of VARCHAR while declaring datatype. 2. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters. 3. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space. Was this answer useful? Yes Reply namrata.somal Apr 24th, 2009 Varchar & Varchar2 both are variable data type. Difference: First: Varchar is used in Oracle 8i version & varchar2 is used in oracle 9i Second: Varchar s memory size is 2000 & varchar2 s memory size is 4000. Third: Varchar2 is faster than Varchar. Was this answer useful? Reply ruchik999 Apr 25th, 2009

Yes

In case of varchar it store upto 2000 bytes and in case of varchar2 it stores 40 00 bytes that is the basic difference between varchar and varchar2. Was this answer useful? Yes Reply sandeshshinde000 Jun 11th, 2011 name varchar(200) := ABC ; name varchar2(200) := ABC ; Now in memory name defined with varchar(200) will take 200 bytes, but varchar2(2 00) will take only 3 bytes. Was this answer useful? Yes Reply arunkumarb6 Jun 24th, 2011 varchar and varchar2 both are same but the only difference is varchar can store 2000 bytes of characters and varchar2 can store 4000 bytes of characters.but or acle suggest varchar2 to use.varchar is for future use. Was this answer useful? Yes Reply Kolta Sam Jul 10th, 2011 VARCHAR2( ) Stores strings of variable length. The length parameter specifies the maximum length of the strings It stores up to 2000 bytes of characters. It will occupy space for NULL values. VARCHAR2( ) Stores strings of variable length.

The length parameter specifies the maximum length of the strings. It stores up to 4000 bytes of characters. It will not occupy space for NULL values. The total length of strings is defined when strings are given Was this answer useful? Yes Reply Avnish Jul 19th, 2011 Code

CREATE TABLE avnish

(Name varchar2(30), address varchar(30));

Address field takes the 30 bytes space and that is fixed , if you are inserting add1 in address field in address the it takes 4 bytes a nd rest 26 bytes are useless. 26 Bytes can t be used for another work. if you are inserting

name1

Was this answer useful? Reply Nishant Galav Jul 30th, 2011

for Name field then it will occupy only 5 bytes,

Yes

Actually varchar and vachar2 are the same thing but varchar2 is the advanced ver sion of varchar. now a days even if we give the datatype varchar the computer un derstands it as varchar2 by default. Was this answer useful? Yes Reply Nishant Galav Jul 30th, 2011 varchar2 is used in 8i and above versions and varchar in lower versions than 8i. varchar2 has max permitted length of about 4000 char varchar has max permitted length of 2000 char If we declare as following varchar(10) than all the 10 spaces are reserved and e ven if we use 5 spaces the rest 5 spaces will be wasted BUT, in varchar2(10) if we use 5 spaces then the remaining 5 spaces will not be wasted it will be kept vacant and can be used. Was this answer useful? Yes Reply GOVARDHANREDDY Aug 12th, 2011 Char has fixed length where as varchar has variable length. For example if we ta ke some employee name ramu and we can declare ename char(10) the output will be

10 where as using varchar declare ename varchar2(10) , it will give output as 4 only. Was this answer useful? Yes Reply anand Aug 14th, 2011 Please find the query Code SQL> CREATE TABLE temp (name VARCHAR(10));

TABLE created.

SQL> SQL> INSERT INTO temp (name) VALUES( anand );

1 ROW created.

SQL> COMMIT;

COMMIT complete.

SQL> SELECT * FROM temp;

NAME ----------

anand

SQL> SELECT LENGTH(name) FROM temp;

LENGTH(NAME)

-----------5 Was this answer useful? Reply as_anand_kumar Aug 14th, 2011

Yes

Hi All Code SQL> CREATE TABLE temp (name varchar(10));

TABLE created.

SQL> SQL> INSERT INTO temp (name) VALUES( anand );

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT length(name) FROM temp;

LENGTH(NAME) ------------

5

SQL> CREATE TABLE temp2 (name char(10));

TABLE created.

SQL> INSERT INTO temp2 VALUES( anand );

1 row created.

SQL>

SELECT length(name) FROM temp2;

LENGTH(NAME)

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

10

Was this answer useful? Reply lakshmareddy Sep 24th, 2011

Yes

varchar:-fixed length i.e,we cannot modify the word suppose lakshmareddy but in varchar2:-variable length i.e., we can modify the word suppose pammilakshmareddy Was this answer useful? Yes Reply Neha Sep 25th, 2011 Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type shoul d not be used as it is reserved for future usage. VARCHAR2 is used to store variable length character strings. The string value s length will be stored on disk with the value itself. Was this answer useful? Yes Reply Arjun Bansal Oct 1st, 2011 Varchar2 memory size is bigger then varchar memory size. Was this answer useful? Yes Reply Kalpana Mooraka Oct 5th, 2011

SQL> create table kalpu2(ename varchar2(10),address varchar(10)); Table created. SQL> desc kalpu2; Name Null? Type ----------------------------------------- -------- ---------------------------ENAME VARCHAR2(10) ADDRESS VARCHAR2(10) SQL> we give the varchar or varchar2 in oracle ,it automatically changed into varchar 2. the main difference between varchar2 and char is that the char data type is a fi xed-length data type,any unused room is blank padded with spaces. for eg a column defined as char(10) and containing the five character length val ue kalpu in a row will have 5 blank characters padded at the end to make the tot al length 10 spaces. if the column is stored in a varchar2(10) column instead, it stores 5 characters only. Was this answer useful? Yes Reply venkat Dec 4th, 2011

"varchar" allocates memory in dynamic fashion where as "varchar2" also allocates memory in dynamic fashion but it also supports garbage collection. "varchar" is developed along with SQL where as "varchar2" is developed by Oracle . Was this answer useful? Yes Reply shaik shamsheer Dec 28th, 2011 In case of varchar it store upto 2000 bytes and in case of varchar2 it stores 40 00 bytes varchar is occupies space for the null value and varchar2 does not occupy space for null values. >> << How do we insert BLOB DATATYPE into table? How to insert image in table? >> << What is the maximum number of columns in a table in Oracle?What is the maximum n umber of canvases in a form in forms 6i an forms 9i? Interview Candidate Mar 16th, 2006 12 5932

BasicsAnswerFirstPrevNextLast Showing Answers 1 - 12 of 12 Answers venkat Mar 19th, 2006 maximum number of columns in a table is 254 Was this answer useful? Yes Yes 1 Reply meetnaveen4u Mar 24th, 2006 Table or view can have upto 1000 columns. Was this answer useful? Yes Yes 2 Reply Aditya May 16th, 2006 A table can have max of 255 columns Was this answer useful? Yes Yes 1 Reply Adarsh S Pazhayampillil Aug 7th, 2006 250 - 1600 depending on column types Was this answer useful? Yes Yes 1 Reply khaja Aug 7th, 2006 It varies from 250 to 1600 depends on column types Was this answer useful? Yes Reply Lalit Kumar Oct 25th, 2006 The maximum number of columns in a table or view are 1000.

Was this answer useful? Reply Nikhil_4_Oracle Mar 15th, 2007

Yes

Final Answer : 1000 columns/table. Was this answer useful? Yes Yes 1 Reply laxma823 Aug 6th, 2008 A table contains maximum 1000 columns Was this answer useful? Yes Yes 1 Reply emraan Dec 27th, 2009 The maximum number of columns in a table or view is 1000 Was this answer useful? Yes Reply sirih1 May 24th, 2011 ORA-01792: maximum number of columns in a table or view is 1000 Cause: An attempt was made to create a table or view with more than 1000 columns , or to add more columns to a table or view which pushes it over the maximum all owable limit of 1000. Note that unused columns in the table are counted toward t he 1000 column limit. Action: If the error is a result of a CREATE command, then reduce the number of columns in the command and resubmit. If the error is a result of an ALTER TABLE command, then there are two options: 1) If the table contained unused columns, r emove them by executing ALTER TABLE DROP UNUSED COLUMNS before adding new column s; 2) Reduce the number of columns in the command and resubmit. Was this answer useful? Yes Yes 1 Reply ambinu Jun 14th, 2011 MySQL: Maximum number of columns in one table - 3398; size of a table row - 6553 4 (BLOB and TEXT not included). Oracle: Unlimited rows@table. Maximum number of columns in one table - 1000. Up to 32 columns in index key. PostgreSQL: Rows - u nlimited, columns - 1600; size of a table row - 1.6TB. Was this answer useful? Yes Yes 1 Reply venkat Dec 4th, 2011 From Oracle 9i it allows maximum 1000 columns in a table. In Oracle 8i, it only allows 256 columns in a table. >> << Can we create sequence to a View?? rajanipriya May 9th, 2006 4 3246 Questions by rajanipriya answers by rajanipriya BasicsAnswerFirstPrevNextLast Showing Answers 1 - 4 of 4 Answers

sankardotb May 30th, 2006 S. We can create sequence to a view. If the view have writtable permission then the new values are Inserted/Updated/Deleted into TABLE. Was this answer useful? Yes Reply rampratap409 Dec 4th, 2006 sequence is a independent object . It has no relation with table/view . its matt er of use. where is being used because in general sequence is used for generatin g a sequenceal number ( primary number). example: create sequnec my_seq start with 101 increment by 1 -- and other perameter it has no direct relation with other objects Was this answer useful? Yes Reply shamkohli Jun 17th, 2008 Sequence is used to generate numbers which can be stored in tables. View does no t have data of its own and is generally used to query data. So sequence can not be created to a view. Was this answer useful? Yes Yes 2 Reply suganya Nov 30th, 2011 no....because sequence is used for creating the integer sequence values only at the time of table creation... but view is a subset of data for the base table.....so cant create the sequence for the view >> << Oracle Synonym What is a Synonym in Oracle? ccp3170 questions Profilequestions Answers by ccp3170questions Questions by ccp3170 Sep 6th, 2010 2 1530 Questions by ccp3170 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 2 of 2 Answers Payal Shah Sep 5th, 2011 Synonum is a alias of the table.. Was this answer useful? Yes Reply jimmy.katiyar11 Nov 29th, 2011 Hi,

In oracle use SYNONYM as a temporary name of the object ( Table, view, index etc .) Code

CREATE SYNONYM synonym_name FOR table_name

You CREATE PUBLIC SYNONYM WITH the PUBLIC keywrd

CREATE PUBLIC SYNONYM synonym_name FOR table_name >> << How many types of triggers used in d2k report What r they? meghtripathy Oct 30th, 2006 4 3057 Questions by meghtripathy BasicsAnswerFirstPrevNextLast Showing Answers 1 - 4 of 4 Answers subasini sahu Nov 17th, 2006 mainly 5 types with sequence are Before parameter trigger After parameter trigger Before report trigger Between pages trigger After report trigger Expect that there are Action trigger and Format trigger in d2k report thanks Subasini Was this answer useful? Yes Reply Pawan Ahuja Dec 9th, 2006 Hi, Basically we used five types of triggers in oracle reports. before parameter form after parameter form before report between pages after report Regards Pawan Ahuja Was this answer useful? Yes Reply

selvaspak Dec 31st, 2010 Total 5 Types of Trigger 1. Before Parameter Trigger 2. After parameter Trigger 3. Before Report Trigger 4. Between Pages Trigger 5. After Report Trigger the Action are given below 1. Before Parameter Trigger Parameter form will displayed 2. After Parameter Trigger Query will parsed 3. Before Report Trigger will fired 4. Between Pages Trigger It will fire between the pages in your report Note: This Trigger will fire between first & second & so on pages but it wi ll not fired in reverse condition 5. After Report Trigger Closing the Runtime Parameter Form is closed Was this answer useful? Yes Reply sateeshkonthala Nov 21st, 2011 There are eight report triggers. Of these there are five global triggers called the Report Triggers. They are fired in the following order : * Before Parameter Form * After Parameter Form * Before Report * Between Pages * After Report Apart from the above Five Report Triggers, there are three other types of trigge rs : * Validation Triggers * Format Triggers * Action Triggers >> << Identify Instance Name How we identify instance name, free space, used space in database & datafile tha t makes up the system tablespace? mojidra Oct 4th, 2008 3 3585 Questions by mojidra answers by mojidra BasicsAnswerFirstPrevNextLast Showing Answers 1 - 3 of 3 Answers chaitra murthy Jan 9th, 2009 To check for instance name:-

SQL> show parameter instance_name To check for free space in tablespace:SQL> desc dba_free_space To check datafile size:SQL> desc v$datafile Was this answer useful? Reply nattuduraip Dec 29th, 2010

Yes

Instance Name Is DATABASE NAME Default Was this answer useful? Yes Reply kishore Nov 15th, 2011 how to find the instance name? >select instance_name from v$instance; >> <<

Application of cursors in applications specifically Dynamic cursors are used in airline reservation system as changes made must be r eflected in result set as most updated information is required for booking. can anyone tell me the exact use of static cursor where most update changes are not required? in which application system it is useful? phanindra170531 questions Profilequestions Answers by phanindra170531questions Questions by phan indra170531 Oct 20th, 2011 1 3561 Questions by phanindra170531 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers vannalas Nov 10th, 2011 Hi, Can you give the examples of static and dynamic cursor ? I will answer your question. >> <<

Compund Triggers Explain the concept of compond triggers balu.kolla Aug 18th, 2010 1 1361 Questions by balu.kolla BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers

neeraj06.in Nov 10th, 2011 Compound Triggers: -> It combines all DML timing events into single trigger body. -> It also resolves mutating table error and enhance the code maintenance. -> The memory variables are persistent till execution finishes. Example: Code CREATE OR REPLACE TRIGGER [TRIGGER NAME]

FOR [DML] ON [TABLE NAME] COMPOUND TRIGGER

-- Initial section -- Declarations

-- Subprograms

Optional section BEFORE STATEMENT IS â¦; Optional section

AFTER STATEMENT IS

â¦; Optional section BEFORE EACH ROW IS â¦; Optional section

AFTER EACH ROW IS

â¦; END;

For views, the trigger body contains INSTEAD OF EACH ROW section. >> << >>

. What is the difference between char,varchar,varchar2? praveen_garigipati Apr 5th, 2006 17 15460 Questions by praveen_garigipati answers by praveen_garigipati BasicsAnswerFirstPrevNextLast Showing Answers 1 - 17 of 17 Answers pramod kumar Apr 6th, 2006 no diff between varchar and varchar2 char store only chacter type but varchar2 store variable chacters. also varchar2 shirinks the space if not fully filled but char cant. Was this answer useful? Yes Reply hussain Apr 14th, 2006 char is fixed length data type whereas varchar & varchar2 are variable in length Was this answer useful? Yes Reply brahma reddy kurre Jul 7th, 2006 Hi there is no variable type called varchar.. So lest come to the main point. Here I am explaining wiht example 1. Varchar2 name [100]="brahma reddy"; // here it uses only the required bytes, it uses 12 bytes and remaining 88 bytes will be freed 2. char name [100]="brahma reddy"; // here remaining bytes will be filled with n ull values. Thank you Brahma Was this answer useful? Yes Yes 1 Reply Jitendra Kumar Jul 20th, 2006 I read all the answer that has been posted all are different According to me :in a varchar variable if we define the size 10 weather u enter or not but in var

char2 variable will take space in memory only when u enter the value and it will take space in memory length u enter. Was this answer useful? Yes Reply Sphurti Aug 11th, 2006 Hi, No Jitender is wrong. As explained in one of the reply. Char is the datatype that occupies complete space declared whether used or not. ie:- If any field is declared as char(10) then this field for all records will o ccupy complete 10 bytes whether the value stored in it is 1 byte or 10 byte. Whereas Varchar2, as the first 3 characters explains Var-Variable, will occupy o nly the bytes for the value entered in the field. So ideally it is good practice to declare fields like emp_flg or any other field s which we are sure will have fixed length value as Char. Was this answer useful? Yes Reply Amritpal May 26th, 2007 Hello ppl, i think u all r got confused with this char and varchar. here is it char : will allocate values upto full length specified using whitespaces varchar: will allocate limited memory which is occupied by data. no padding is t here. varchar2: same as varchar but has some special internal benefits which helps ora cle perform fast. Now question is diff bw varchar/varchar2? as we all know for RDBMS Codd NewMan provides thumb rules n to just satisfy those rules Oracle created these two types os datatypes char/varchar just to fulfill ANSI standard for RDBMS. whereas oracle realises that varchar datatype can further more be enhansed so va rchar2 datatype was introduced. hope this helps u. bye amrit Was this answer useful? Reply purnachand_venu Nov 3rd, 2007

Yes

char store only chacter type but varchar2 store variable chacters. also varchar2 shirinks the space if not fully filled but char cant. for more clear create table gopal (name char(8),name varchar(8)); in this table if u inserts any values let us take ( india ) .in the case of cha r data type, india contains 5 chrecters but we have intialised for 8 chrecters rest 3 charecters will inserted as blank spaces,if u what use this column for f iltering(where class)u shoud give ( india ) as ( india ) than only rows will come where in varchar datatype u can give as india for rust of charecters padding w ill applied Was this answer useful? Yes Reply kaustavban Jun 5th, 2008

The most important difference is that CHAR is padded with spaces and VARCHAR is not. For example, if you have: CREATE TABLE t1 ( c1 VARCHAR(2), c2 CHAR(2) ); INSERT INTO t1 (c1,c2) VALUES ( a ,

a );

The column c1 will contain value a , while column c2 will contain value a wi th additional space. Trailing spaces are ignored when doing comparisons, so both columns would match the WHERE c = a Was this answer useful? Reply kaustavban Jun 5th, 2008

Yes

The char is a fixed-length character data type, the varchar is a variable-length character data type.Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the act ual length of the data entered, not the maximum size for this column.You can use char when the data entries in a column are expected to be the same size.You can use varchar when the data entries in a column are expected to vary considerably in size. Was this answer useful? Yes Yes 2 Reply getadoll Jan 17th, 2009 The char is a fixed-length character data type, the varchar is a variable-length character data type. Because char is a fixed-length data type, the storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the dat a entered, not the maximum size for this column. You can use char when the data entries in a column are expected to be the same s ize. You can use varchar when the data entries in a column are expected to vary consi derably in size.VARCHAR2 is used to store variable length character strings. The string value s length will be stored on disk with the value itself. Was this answer useful? Yes Reply kreddy.vonteddu Jan 20th, 2009 char specifies fixed length characters only. varchar & varchar2 is specifies a variable length character only Was this answer useful? Yes Reply bismita Jan 24th, 2009 As I believe there is no difference between VARCHAR and VARCHAR2 except that VAR

CHAR2 is the latest released one.CHAR datatype represents fixed length hence not compressed while giving a value of less length. Instead it will padded blanks w hile storing. But VRACHAR s are variable length characters and in case of less length of the characters it will be compressed and stored. Was this answer useful? Yes Reply vijaydeep Mar 1st, 2009 The advanced type is varchar2 in latest versions. The simplest way to define is the char i.e. char(s): Fixed length character valu e of size s and the varchar2(s): Variable-length character value of maximum size s ie. char(s) can enter only char type, where as varchar2()can accept number and char type also. Was this answer useful? Yes Reply srinivas Sep 16th, 2011 Char: char is fixed length data type VarChar:Varchar are variable in length In that varchar take the additional Memor y bytes depending on the Declare the size of the variable. Was this answer useful? Reply ii Sep 21st, 2011

Yes

Code SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[" dBConnection"].ToString()); con.Open();

DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand("GetICon", con);

SqlDataAdapter adap = new SqlDataAdapter(cmd); cmd.CommandType = CommandType.StoredProcedure;

adap.Fill(ds);

return ds.Tables[0]; Was this answer useful? Reply lina Aug 22nd, 2012

Yes

1.CHAR should be used for storing fix length character strings. String values wi ll be space/blank padded before stored on disk. If this type is used to store va ribale length strings, it will waste a lot of disk space. 2.VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle sugges ts the use VARCHAR2 instead of VARCHAR while declaring datatype. 3. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters. 4. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space. Varchar stores alphanumeric values without padding the unused memory locations. Varchar2 is also used to store alphanumeric values with padding the unused memor y locations by using varchar2 we are saving the memory locations. Was this answer useful? Yes Reply lipika mishra Aug 22nd, 2012 1.CHAR should be used for storing fix length character strings. String values wi ll be space/blank padded before stored on disk. If this type is used to store va ribale length strings, it will waste a lot of disk space. 2.VARCHAR is going to be replaced by VARCHAR2 in next version. So, Oracle sugges ts the use VARCHAR2 instead of VARCHAR while declaring datatype. 3. VARCHAR can store up to 2000 bytes of characters while VARCHAR2 can store up to 4000 bytes of characters. 4. If we declare datatype as VARCHAR then it will occupy space for NULL values, In case of VARCHAR2 datatype it will not occupy any space. Varchar stores alphanumeric values without padding the unused memory locations. Varchar2 is also used to store alphanumeric values with padding the unused memor y locations by using varchar2 we are saving the memory locations. >> << Collection of Privileges What is a collection of privileges? ccp3170 Sep 6th, 2010 2 2467 Questions by ccp3170 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 2 of 2 Answers BBBB Apr 19th, 2012 Collecting the access eg: insert,select,update in this group the user can apply only the above operation on the tables. Was this answer useful? Yes Reply hari kumar

Aug 2nd, 2012 its role .... we can assign a role like manager , etc , collection of privs is k nown as role >> << Hi All, Getting the following error in Oracle9i on AIX, unique index ORA-01652: unable to extend temp segment by 128 in tablespace USERS; though the USER tables pace is of 10gb.Could anyone provide a solution.Regards,Ashish Thomas thomashish Dec 13th, 2006 3 1279 Questions by thomashish BasicsAnswerFirstPrevNextLast Showing Answers 1 - 3 of 3 Answers siva Apr 10th, 2007 i think the size of the temporary tablespace is small. try to increase the size of the temporary table. Was this answer useful? Yes Reply Daid Aug 20th, 2007 Please Set the Undo Tablespace management auto and scope is memory and then try to do Thanks Was this answer useful? Yes Reply Aaryan Jul 25th, 2012 Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. >> << Oracle Program Display list of jobs, number of employees of each job from departments 10 and 20 . List only records if number of employees in each jobs are more than 1. mishel Feb 23rd, 2012 1 1913 Questions by mishel BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers DURGAPRASAD Jul 23rd, 2012 SELECT JOB,COUNT(EMPNO) AS NOOFEMP FROM SCOTT.EMP WHERE DEPTNO IN (10,20) GROUP BY JOB HAVING COUNT(EMPNO) > 1 >> << Force View What do you mean by Force View? samarendra161 Jul 2nd, 2010 1 1663 Questions by samarendra161

answers by samarendra161

BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers asit Jun 9th, 2012 Force view is the process to create a view forcefully without a base table. syntax: Code CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW VIEW_NAME AS

SELECT STMT; >> << Privileges Granted & Privileges Obtained Which system tables contain information on privileges granted & privileges obtai ned? samarendra161 Jul 2nd, 2010 1 1298 Questions by samarendra161 answers by samarendra161 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers asit Jun 9th, 2012 for users permissions : user_tab_privs table is used. for viewing all permissions : all_tab_privs >> << Data Dictionary How can we know the all tables and views that are given for the specific user(su ch as USER_VIEWS,USER_OBJECTS etc.)? bhanumurthy03 Aug 31st, 2009 2 1238 Questions by bhanumurthy03 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 2 of 2 Answers harshljica Sep 2nd, 2009 Open dba_objects in that open column name owner, in that mention user name and i t will display the list of tables and views of specific users. Was this answer useful? Yes Reply asit Jun 9th, 2012 Data dictionary is nothing but the place holder which contains all information o f the object like name,type,size,owner,permissions etc.

some data dictionaries : user_views,user_mviews, user_tabs, user_sequences, all_ objects etc >> <<

ROW_NUMBER(), RANK(), DENSE_RANK() What do you mean by ROW_NUMBER(), RANK(), DENSE_RANK() differentiate them? samarendra161 Jul 2nd, 2010 1 4135 Questions by samarendra161 answers by samarendra161 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers asit Jun 9th, 2012 THE BELOW FUNCTIONS ARE OLAP FUNCTIONS... rank() function skips the continuity but dense_rank() function gives the sequenc e.It is always advised to use dense_rank for viewing nth highest/lowest value. r ow_num() gives the serial no in the order how they are inserted in to a table. >> <<

Log Switch What is log switch? megha90 Jun 9th, 2010 1 1575 Questions by megha90 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers asit Jun 9th, 2012 log switch ::suppose we have two redo log files LOG A, LOG B. when transaction(DML) are occurred in Database then they are recorded in LOG A. When LOG A is filled up, a log switch occurs, All new transactions are recorded into LOG B. This process is cyclic. because after LOG B is filled up then again log switch o ccurs and transaction are again recorded into LOG A. The previous transaction in formation in LOG A is then over written. Then archivelog/noarchivelog mode comes into picture >> << Oracle Exception What is an exception in Oracle? ccp3170 Sep 6th, 2010 1 1426 Questions by ccp3170 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers asit Jun 9th, 2012 Exceptions are nothing but the errors occurred at the run time. To handle the ex

ception we use exception block in the PLSQL. >> << Oracle Archiver What is an Archiver in Oracle? ccp3170 Sep 6th, 2010 1 1259 Questions by ccp3170 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers venkatesh Jun 8th, 2012 Its background processes in oracle, it will write offline redolog group informat ion into archive log files >> <<

How to find the Foreign keys of all child tables when pass the Parent Table name . I developed like below.Any experts query ....please send. select table_name,constraint_name from user_cons_columns where column_name=( select a.COLUMN_NAME from user_cons_columns a,user_constraints b where a.TABLE_NAME=b.TABLE_NAME and b.constraint_type in ( P ) and a.TABLE_NAME= DEPT ) and constraint_name like %FK% sheker2007 Nov 9th, 2007 4 9532 Questions by sheker2007 BasicsAnswerFirstPrevNextLast Showing Answers 1 - 4 of 4 Answers kishore.giri Dec 12th, 2007 MY DEAR, PLZ QUERY THE DATA DICTIONARY VIEW CALLED USER_DEPENDENCIES AND PASS YOUR PARE NT TABLENAME IN THE NAME COLUMN AND ALSO GIVE THE TYPE = TABLE IN THE WHERE CLAU SE OF QUERY. PLZ TRY IT AND REPLY.

REGARDS Kishore Kumar Giri Was this answer useful? Reply Imran_Javed May 13th, 2008 SELECT uc.CONSTRAINT_NAME,

Yes

ut.TABLE_NAME, ucc.COLUMN_NAME FROM USER_CONS_COLUMNS ucc, USER_CONSTRAINTS uc , USER_TABLES ut WHERE uc.table_name = ut.TABLE_NAME AND uc.CONSTRAINT_NAME =ucc.CONSTRAINT_NAME AND uc.CONSTRAINT_TYPE = R AND ut.table_name = DEPT ; Was this answer useful? Yes Reply sen_sam86 Aug 18th, 2009 Try this script, Example SELECT child.table_name AS "TABL_NAME_2", Is Parent Of AS "RELATION", parent.table_name AS "TABL_NAME_1", UCC.column_name AS "COL_NAME", parent.constraint_name AS "CONSTRNT_NAME", DECODE (parent.constraint_type, P , Primary Key , R , TRNT_TYPE" FROM user_constraints parent, user_constraints child, user_cons_columns UCC WHERE parent.r_constraint_name = child.constraint_name AND parent.r_owner = child.owner AND child.table_name = v_tab_name AND child.constraint_name = ucc.constraint_name

Foreign Key ) AS "CONS

UNION ALL SELECT child.table_name AS "TABL_NAME_2", Is Child Of AS "RELATION", parent.table_name AS "TABL_NAME_1", ucc.column_name AS "COL_NAME", child.constraint_name AS "CONSTRNT_NAME", DECODE (child.constraint_type, P , Primary Key , R , RNT_TYPE" FROM user_constraints child, user_constraints parent, user_cons_columns ucc WHERE child.r_constraint_name = parent.constraint_name AND child.r_owner = parent.owner AND child.table_name = v_tab_name --and child.constraint_type in ( R ) AND child.constraint_name = ucc.constraint_name Hope it will help you, Was this answer useful? Reply asheesh Jun 2nd, 2012

Yes

Foreign Key ) AS "CONST

select table_name from USER_CONSTRAINTS where r_constraint_name= (select CONSTRA INT_NAME from USER_CONSTRAINTS where table_name=INPUT_PARENT_TABLE and CONSTRAIN T_TYPE=P) and CONSTRAINT_TYPE=R; >> << What is meant by deadlock in database? Transaction is unit of work done. So a database management system will have numb er of transactions. There may be situations when two or more transactions are pu t into wait state simultaneously .In this position each would be waiting for the other transaction to get released. Suppose we have two transactions one and two both executing simultaneously. In transaction numbered one we update student ta ble and then update course table. We have transaction two in which we update cou rse table and then update student table. We know that when a table is updated it is locked and prevented from access from other transactions from updating. So in transaction one student table is updated it is locked and in transaction t wo course table is updated and it is locked. We have given already that both tra nsactions gets executed simultaneously. So both student table and course table g ets locked so each one waits for the other to get released. This is the concept of deadlock in DBMS. GeekAdmin Sep 23rd, 2006 5 18798 Questions by GeekAdmin answers by GeekAdmin BasicsAnswerFirstPrevNextLast Showing Answers 1 - 5 of 5 Answers vmayur Oct 26th, 2006 Deadlock means Locking put on data when one transaction is being under operation on a record. supposs you are inserting a record into the table that tabel is being put under lock.This does not mean that no one can see the record from the table but this m ens no one can apply any DML to that table.Same time any changes made to that ta ble will not be seen untill the record have been commited Once the record is Commited the Deadlock is realaesed from that table. Was this answer useful? Yes Reply gvvskumaran Jan 15th, 2007 two users Accesing and Altering the datas in a table at the same time...(Is it r ight or wrong) Was this answer useful? Yes Reply deepak meena Sep 6th, 2011 dead lock is defined as a situation arise during multiple transactions which put all the transactions on halting situation where the transactional programs seem s dead means they stop their execution until the suitable technique never applie d to solve this. Was this answer useful? Yes Reply rohit Apr 22nd, 2012 It is absolutely wrong.you can read data simultaneously but you cant write data ......you can write data one at a time only... Was this answer useful? Yes Reply

Chittaranjan Kotian May 16th, 2012 Two Oracle Sessions attempting to obtain a lock on a rows that is being locked b y each other. For example Session A has locked a row for update in a transaction . Session B is trying to obtain a lock on the same record in its own transaction . Session A now completes is update but the transaction is not yet committed or ro lled back and hence the lock is still maintained. Session A now tries to obtain a lock on a record that has been locked by Session B. So a situation occurs wher e both Session A & B are waiting on records locked by each other. Such a situati on is called a DEADLOCK >> << What is Clustered Table in Oracle? Difference between Clustered Table and View? A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common. Pradeep_Oracle Dec 12th, 2011 2 8262 Questions by Pradeep_Oracle BasicsAnswerFirstPrevNextLast Showing Answers 1 - 2 of 2 Answers Lokesh M Feb 13th, 2012 A cluster is a group of tables which share the same data blocks. Clusters are an optional method of storing table data. Cluster share common columns and are often used together. It is a powerful method of storing and organizing data in the dataset into group s or clusters of similar data. Oracle physically stores all rows for each table in the same data blocks. A view can be considered as a virtual table, It is like a stored query which tre ats the output of the underlaying query as a table. Read more here http://www.geekinterview.com/talk/20450-what-cluster-table-oracle-diff-between.h tml#post61454 Was this answer useful? Yes Yes 1 Reply ravinder May 4th, 2012 Clustered table store data of multiple table based on common columns. View is virtual table or which has attached select query.Its not actual table. >> << What is the difference between 9i and 10g.(especially i and g stands) i in 9i is internet. g in 10g is grid. "i" stands for Internet Application & "g" for Grid Computing.

i stands for the internet and the g stands for the grid computing... Feature wise difference between oracle10g over oracle9i are:: a:isql plus b:j server c:flashback table d:flashback database in oracle "i" stands for internet & "g" stands for grid.

Oracle 9i and Oracle 10g....there are more implementation differences. In terms of architecture, 9i is based on Internet technology while 10g is grid computing based one. Many DBA features like Automated Storage Management (ASM), Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM) were int roduced. For developers, 10g is more stable than 9i. All the earlier bugs reported were f ixed in 10g release. Performance wise, it was more impressive. It increased the data chunk size durin g I/O between the SQL and PL/SQL engines. Use of collections was recommended. PLS_INTEGER, a new data type was added which enhances performance. ANYDATA data type was introduced to hold a data of variant feature. FLASHBACK option was made more stronger like TABLE FLASHBACK was introduced. Reg ular expression function were introduced REGEXP_SUBSTR, REGEXP_INSTR, REGEXP_REP LACE. Oracle Recyclebin was introduced. The dropped objects can be restored from the r ecyclebin until it is purge The Oracle version starting of I. The starting in 1999 with version 6i, 8i and 9 i, I signify "Internet" means stands for "Internet" and Oracle added the "I" to the version name to reflect support for the Internet with its built-in Java Virt ual Machine (JVM). Oracle 9i added more support for XML in 2001. Meaning of G in Oracle: The starting in 2003 with version 10g and 11g, G signifies "Grid Computing" with the release of Oracle10g in 2003. Oracle 10g was introduced with emphasis on th e "g" for grid computing, which enables clusters of low-cost, industry standard servers to be treated as a single unit. Upgrade Enterprise Manager 10g Grid Cont rol Release 4 (10.2.0.4.0) or higher to Enterprise Manager 11g Grid Control Rele ase 1 (11.1.0.1.0). >> << Compund Triggers Explain the concept of compond triggers balu.kolla Aug 18th, 2010 1 1361 Questions by balu.kolla BasicsAnswerFirstPrevNextLast Showing Answers 1 - 1 of 1 Answers neeraj06.in Nov 10th, 2011 Compound Triggers:

-> It combines all DML timing events into single trigger body. -> It also resolves mutating table error and enhance the code maintenance. -> The memory variables are persistent till execution finishes. Example: Code CREATE OR REPLACE TRIGGER [TRIGGER NAME]

FOR [DML] ON [TABLE NAME] COMPOUND TRIGGER

-- Initial section -- Declarations

-- Subprograms Optional section

BEFORE STATEMENT IS â¦; Optional section

AFTER STATEMENT IS â¦;

Optional section BEFORE EACH ROW IS â¦; Optional section

AFTER EACH ROW IS â¦;

END;

>> << >> 1. Difference between varchar and varchar2 data types? Varchar can store upto 2000 bytes and varchar2 can store upto 4000 bytes. Varcha r will occupy space for NULL values and Varchar2 will not occupy any space. Both are differed with respect to space. 2. In which language Oracle has been developed? Oracle has been developed using C Language. 3. What is RAW datatype? RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes. 4. What is the use of NVL function? The NVL function is used to replace NULL values with another or given value. Exa mple is ± NVL(Value, replace value) 5. Whether any commands are used for Months calculation? If so, What are they? In Oracle, months_between function is used to find number of months between the given dates. Example is ± Months_between(Date 1, Date 2) 6. What are nested tables? Nested table is a data type in Oracle which is used to support columns contai ning multi valued attributes. It also hold entire sub table. 7. What is COALESCE function? COALESCE function is used to return the value which is set to be not null in the list. If all values in the list are null, then the coalesce function will retur n NULL. Coalesce(value1, value2,value3,¼) 8. What is BLOB datatype? A BLOB data type is a varying length binary string which is used to store two gi gabytes memory. Length should be specified in Bytes for BLOB. 9. How do we represent comments in Oracle?

Comments in Oracle can be represented in two ways ± Two dashes(±) before beginning of the line ± Single statement /*ÐÐ */ is used to represent it as comments for block of statement 10. What is DML? Data Manipulation Language (DML) is used to access and manipulate data in the ex isting objects. DML statements are insert, select, update and delete and it won't implicitly commit the current transaction. 11. What is the difference between TRANSLATE and REPLACE? Translate is used for character by character substitution and Replace is used su bstitute a single character with a word. 12. How do we display rows from the table without duplicates? Duplicate rows can be removed by using the keyword DISTINCT in the select statem ent. 13. What is the usage of Merge Statement? Merge statement is used to select rows from one or more data source for updating and insertion into a table or a view. It is used to combine multiple operations . 14. What is NULL value in oracle? NULL value represents missing or unknown data. This is used as a place holder or represented it in as default entry to indicate that there is no actual data pre sent. 15. What is USING Clause and give example? The USING clause is used to specify with the column to test for equality when tw o tables are joined. [sql] Select * from employee join salary using employee ID[/sql] Employee tables join with the Salary tables with the Employee ID. 16. What is key preserved table?f A table is set to be key preserved table if every key of the table can also be t he key of the result of the join. It guarantees to return only one copy of each row from the base table. 17. What is WITH CHECK OPTION? The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query. 18. What is the use of Aggregate functions in Oracle? Aggregate function is a function where values of multiple rows or records are jo ined together to get a single value output. Common aggregate functions are ± Average

Count Sum 19. What do you mean by GROUP BY Clause? A GROUP BY clause can be used in select statement where it will collect data acr oss multiple records and group the results by one or more columns. 20. What is a sub query and what are the different types of subqueries? Sub Query is also called as Nested Query or Inner Query which is used to get dat a from multiple tables. A sub query is added in the where clause of the main que ry. There are two different types of subqueries: Correlated sub query A Correlated sub query cannot be as independent query but can reference column i n a table listed in the from list of the outer query. Non-Correlated subquery This can be evaluated as if it were an independent query. Results of the sub que ry are submitted to the main query or parent query. 21. What is cross join? Cross join is defined as the Cartesian product of records from the tables presen t in the join. Cross join will produce result which combines each row from the f irst table with the each row from the second table. 22. What are temporal data types in Oracle? Oracle provides following temporal data types: Date Data Type ± Different formats of Dates TimeStamp Data Type ± Different formats of Time Stamp Interval Data Type ± Interval between dates and time 23. How do we create privileges in Oracle? A privilege is nothing but right to execute an SQL query or to access another us er object. Privilege can be given as system privilege or user privilege. [sql]GRANT user1 TO user2 WITH MANAGER OPTION;[/sql] 24. What is VArray? VArray is an oracle data type used to have columns containing multivalued attrib utes and it can hold bounded array of values. 25. How do we get field details of a table? Describe is used to get the field details of a specified table. 26. What is the difference between rename and alias? Rename is a permanent name given to a table or a column whereas Alias is a tempo rary name given to a table or column. Rename is nothing but replacement of name and Alias is an alternate name of the table or column.

27. What is a View? View is a logical table which based on one or more tables or views. The tables upon which the view is based are called Base Tables and it doesn't contain data. 28. What is a cursor variable? A cursor variable is associated with different statements which can hold differe nt values at run time. A cursor variable is a kind of reference type. 29. What are cursor attributes? Each cursor in Oracle has set of attributes which enables an application program to test the state of the cursor. The attributes can be used to check whether cu rsor is opened or closed, found or not found and also find row count. 30. What are SET operators? SET operators are used with two or more queries and those operators are Union, U nion All, Intersect and Minus. 31. How can we delete duplicate rows in a table? Duplicate rows in the table can be deleted by using ROWID. 32. What are the attributes of Cursor? Attributes of Cursor are %FOUND Returns NULL if cursor is open and fetch has not been executed Returns TRUE if the fetch of cursor is executed successfully. Returns False if no rows are returned. %NOT FOUND Returns NULL if cursor is open and fetch has not been executed Returns False if fetch has been executed Returns True if no row was returned %ISOPEN Returns true if the cursor is open Returns false if the cursor is closed %ROWCOUNT Returns the number of rows fetched. It has to be iterated through entire cursor to give exact real count. 33. Can we store pictures in the database and if so, how it can be done? Yes, we can store pictures in the database by Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. But the table can have o nly on Long Raw data type. 34. What is an integrity constraint?

An integrity constraint is a declaration defined a business rule for a table col umn. Integrity constraints are used to ensure accuracy and consistency of data i n a database. There are types ± Domain Integrity, Referential Integrity and Domain Integrity. 35. What is an ALERT? An alert is a window which appears in the center of the screen overlaying a port ion of the current display. 36. What is hash cluster? Hash Cluster is a technique used to store the table for faster retrieval. Apply hash value on the table to retrieve the rows from the table. 37. What are the various constraints used in Oracle? Following are constraints used: NULL ± It is to indicate that particular column can contain NULL values NOT NULL ± It is to indicate that particular column cannot contain NULL values CHECK ± Validate that values in the given column to meet the specific criteria DEFAULT ± It is to indicate the value is assigned to default value 38. What is difference between SUBSTR and INSTR? SUBSTR returns specific portion of a string and INSTR provides character positio n in which a pattern is found in a string. SUBSTR returns string whereas INSTR returns numeric. 39. What is the parameter mode that can be passed to a procedure? IN, OUT and INOUT are the modes of parameters that can be passed to a procedure. 40. What are the different Oracle Database objects? There are different data objects in Oracle ± Tables ± set of elements organized in vertical and horizontal Views ± Virtual table derived from one or more tables Indexes ± Performance tuning method for processing the records Synonyms ± Alias name for tables Sequences ± Multiple users generate unique numbers Tablespaces ± Logical storage unit in Oracle ///////////////////////////////////// 41. What are the differences between LOV and List Item? LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column , LOV can have one or more columns. 42. What are privileges and Grants? Privileges are the rights to execute SQL statements ± means Right to connect and c onnect. Grants are given to the object so that objects can be accessed according ly. Grants can be provided by the owner or creator of an object. 43. What is the difference between $ORACLE_BASE and $ORACLE_HOME?

Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is lo cated beneath base folder in which all oracle products reside. 44. What is the fastest query method to fetch data from the table? Row can be fetched from table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table. 45. What is the maximum number of triggers that can be applied to a single table ? 12 is the maximum number of triggers that can be applied to a single table. 46. How to display row numbers with the records? Display row numbers with the records numbers ± Select rownum, from table; 1 Select rownum, from table; This query will display row numbers and the field values from the given table. 47. How can we view last record added to a table? Last record can be added to a table and this can be done by ±

Select * from (select * from employees order by rownum desc) where rownum<2; 1 Select * from (select * from employees order by rownum desc) where rownum<2; 48. What is the data type of DUAL table? The DUAL table is a one-column table present in oracle database. The table has a single VARCHAR2(1) column called DUMMY which has a value of `X'. 49. What is difference between Cartesian Join and Cross Join? There are no differences between the join. Cartesian and Cross joins are same. C ross join gives cartesian product of two tables ± Rows from first table is multipl ied with another table which is called cartesian product. Cross join without where clause gives Cartesian product. 50. How to display employee records who gets more salary than the average salary in the department? This can be done by this query ±

Select * from e dept.deptno 1 Select * from e dept.deptno

employee where salary>(select avg(salary) from dept, employee wher = employee.deptno; employee where salary>(select avg(salary) from dept, employee wher = employee.deptno;

+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_

+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_+_ +_ 1. What is PL/SQL? PL/SQL is a procedural language that has both interactive SQL and proced ural programming language constructs such as iteration, conditional branching. 2.

What is the basic structure of PL/SQL? PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL. 3.

What are the components of a PL/SQL block? A set of related declarations and procedural statements is called block.

4.

What are the components of a PL/SQL Block? Declarative part, Executable part and Execption part.

5.

What are the datatypes a available in PL/SQL? Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE. 6. What are % TYPE and % ROWTYPE? What are the advantages of using these ov er datatypes? % TYPE provides the data type of a variable or a database column to that variable. % ROWTYPE provides the record type that represents a entire row of a table or vi ew or columns selected in the cursor. The advantages are: I. need not know about variable s data type ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly. 7.

What is difference between % ROWTYPE and TYPE RECORD ? % ROWTYPE is to be used whenever query returns a entire row of a table o

r view. TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables. E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type ); e_rec emp% ROWTYPE Cursor c1 is select empno,deptno from emp; e_rec c1 %ROWTYPE. 8.

What is PL/SQL table? Objects of type TABLE are called "PL/SQL tables", which are modelled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key. 9.

What is a cursor? Why Cursor is required? Cursor is a named private SQL area from where information can be accesse

d. Cursors are required to process rows individually for queries returning multiple rows. 10.

Explain the two types of Cursors? There are two types of cursors, Implict Cursor and Explicit Cursor. PL/SQL uses Implict Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used. 11.

What are the PL/SQL Statements used in cursor processing?

DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name. 12.

What are the cursor attributes used in PL/SQL? %ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows featched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are featched . % NOT FOUND - to check whether cursor has featched any row. True if no rows are featched. These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors. 13.

What is a cursor for loop? Cursor for loop implicitly declares %ROWTYPE as loop index,opens a curso r, fetches rows of values from active set into fields in the record and closes w hen all the records have been processed. eg. FOR emp_rec IN C1 LOOP salary_total := salary_total +emp_rec sal; END LOOP; 14.

What will happen after commit statement ? Cursor C1 is Select empno, ename from emp; Begin open C1; loop Fetch C1 into eno.ename; Exit When C1 %notfound;----commit; end loop; end; The cursor having query as SELECT .... FOR UPDATE gets closed after COMMIT/ROLLB ACK. The cursor having query as SELECT.... does not get closed even after COMMIT/ROLL BACK. 15.

Explain the usage of WHERE CURRENT OF clause in cursors ? WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the late st row fetched from a cursor. 16.

What is a database trigger ? Name some usages of database trigger ? Database trigger is stored PL/SQL program unit associated with a specifi c database table. Usages are Audit data modificateions, Log events transparently , Enforce complex business rules Derive column values automatically, Implement c omplex security authorizations. Maintain replicate tables. 17. How many types of database triggers can be specified on a table? What ar e they? Insert Update Delete Before Row After Row

o.k. o.k.

o.k. o.k.

o.k. o.k.

Before Statement After Statement

o.k. o.k.

o.k. o.k.

o.k. o.k.

If FOR EACH ROW clause is specified, then the trigger for each Row affected by t he statement. If WHEN clause is specified, the trigger fires according to the retruned boolean value. 18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why? It is not possible. As triggers are defined for each table, if you use C OMMIT of ROLLBACK in a trigger, it affects logical transaction processing. 19.

What are two virtual tables available during database trigger execution? The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available. For triggers related to UPDATE only OLD.column_name NEW.column_name values only available. For triggers related to DELETE only OLD.column_name values only available. 20. What happens if a procedure that updates a column of table X is called i n a database trigger of the same table? Mutation of table occurs. 21.

Write the order of precedence for validation of a column in a table ? I. done using Database triggers. ii. done using Integarity Constraints. 22.

What is an Exception? What are types of Exception? Exception is the error handling part of PL/SQL block. The types are Pred efined and user_defined. Some of Predefined execptions are. CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR INVALID_NUMBER LOGON_DENIED NOT_LOGGED_ON PROGRAM-ERROR STORAGE_ERROR TIMEOUT_ON_RESOURCE VALUE_ERROR ZERO_DIVIDE OTHERS. 23.

What is Pragma EXECPTION_INIT? Explain the usage? The PRAGMA EXECPTION_INIT tells the complier to associate an exception w ith an oracle error. To get an error message of a specific oracle error. e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number) 24.

What is Raise_application_error? Raise_application_error is a procedure of package DBMS_STANDARD which al lows to issue an user_defined error messages from stored sub-program or database trigger.

25.

What are the return values of functions SQLCODE and SQLERRM? SQLCODE returns the latest code of the error that has occured. SQLERRM returns the relevant error message of the SQLCODE. 26.

Where the Pre_defined_exceptions are stored? In the standard package. Procedures, Functions & Packages; 27.

What is a stored procedure? A stored procedure is a sequence of statements that perform specific fun

ction. 30.

What is difference between a PROCEDURE & FUNCTION? A FUNCTION is alway returns a value using the return statement. A PROCEDURE may return one or more values through parameters or may not return a t all. 31.

What are advantages of Stored Procedures? Extensibility,Modularity, Reusability, Maintainability and one time comp ilation. 32.

What are the modes of parameters that can be passed to a procedure? IN,OUT,IN-OUT parameters.

33.

What are the two parts of a procedure? Procedure Specification and Procedure Body.

34.

Give the structure of the procedure? PROCEDURE name (parameter list.....)

is local variable declarations BEGIN Executable statements. Exception. exception handlers end; 35.

Give the structure of the function? FUNCTION name (argument list .....) Return datatype is local variable declarations Begin executable statements Exception execution handlers End;

36.

Explain how procedures and functions are called in a PL/SQL block ? Function is called as part of an expression. sal := calculate_sal ( a822 ); procedure is called as a PL/SQL statement calculate_bonus ( A822 ); 37.

What is Overloading of procedures? The Same procedure name is repeated with parameters of different datatyp es and parameters in different positions, varying number of parameters is called overloading of procedures.

e.g. DBMS_OUTPUT put_line 38.

What is a package? What are the advantages of packages? Package is a database object that groups logically related procedures. The advantages of packages are Modularity, Easier Applicaton Design, and Informa tion. Hiding,. Reusability and Better Performance. 39.

What are two parts of package? The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY. Package Specification contains declarations that are global to the packages and local to the schema. Package Body contains actual procedures and local declaration of the procedures and cursor declarations. 40. What is difference between a Cursor declared in a procedure and Cursor d eclared in a package specification? A cursor declared in a package specification is global and can be access ed by other procedures or procedures in a package. A cursor declared in a procedure is local to the procedure that can not be acces sed by other procedures. 41. How packaged procedures and functions are called from the following ? a. Stored procedure or anonymous block b. an application program such a PRC *C, PRO* COBOL c. SQL *PLUS a. PACKAGE NAME.PROCEDURE NAME (parameters); variable := PACKAGE NAME.FUNCTION NAME (arguments); EXEC SQL EXECUTE b. BEGIN PACKAGE NAME.PROCEDURE NAME (parameters) variable := PACKAGE NAME.FUNCTION NAME (arguments); END; END EXEC; c. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A function can not be called. 42. Name the tables where characteristics of Package, procedure and function s are stored? User_objects, User_Source and User_error. What are the various types of queries ? Answer: The types of queries are: Normal Queries Sub Queries Co-related queries Nested queries Compound queries ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is a transaction ? Answer: A transaction is a set of SQL statements between any two COMMIT and ROLL BACK statements. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is implicit cursor and how is it used by Oracle ? Answer: An implicit cursor is a cursor which is internally created by Oracle.It

is created by Oracle for each individual SQL. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Which of the following is not a schema object : Indexes, tables, public synonyms , triggers and packages ? Answer: Public synonyms ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is PL/SQL? Answer: PL/SQL is Oracle s Procedural Language extension to SQL.The language inc ludes object oriented programming techniques such as encapsulation, function ove rloading, information hiding (all but inheritance), and so, brings state-of-theart programming to the Oracle database server and a variety of Oracle tools. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Is there a PL/SQL Engine in SQL*Plus? Answer: No.Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine.Thus, all your PL/SQL are send directly to the database engine for execution.This makes i t much more efficient as SQL statements are not stripped off and send to the dat abase individually. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Is there a limit on the size of a PL/SQL block? Answer: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K.You can run the following select statement to que ry the size of an existing package or procedure. SQL> select * from dba_object_s ize where name = procedure_name ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can one read/write files from PL/SQL? Answer: Included in Oracle 7.3 is a UTL_FILE package that can read and write fil es.The directory you intend writing to has to be in your INIT.ORA file (see UTL_ FILE_DIR=...parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with t he SQL*Plus SPOOL command. DECLARE fileHandler UTL_FILE.FILE_TYPE; BEGIN fileHandler := UTL_FILE.FOPEN( /home/oracle/tmp , myoutput , W ); UTL_FILE.PUTF(fileHandler, Value of func1 is %sn , func1(1)); UTL_FILE.FCLOSE(fileHandler); END; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------How can I protect my PL/SQL source code? Answer: PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for P L/SQL programs to protect the source code.This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewha t larger than the srcinal).This way you can distribute software without having to worry about exposing your proprietary algorithms and methods.SQL*Plus and SQL *DBA will still understand and know how to execute such scripts.Just be careful, there is no "decode" command available. The syntax is: wra p name=myscript.sql

oname=xxxx.yyy ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ? Answer: From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements. Eg: CREATE OR REPLACE PROCEDURE DYNSQL AS cur integer; rc integer; BEGIN cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cur, CREATE TABLE X (Y DATE) , DBMS_SQL.NATIVE); rc := DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR(cur); END; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the various types of Exceptions ? Answer: User defined and Predefined Exceptions. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can we define exceptions twice in same block ? Answer: No. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the difference between a procedure and a function ? Answer: Functions return a single variable by value whereas procedures do not re turn any variable by value.Rather they return multiple variables by passing vari ables by reference through their OUT parameter. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you have two functions with the same name in a PL/SQL block ? Answer: Yes. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you have two stored functions with the same name ? Answer: Yes. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you call a stored function in the constraint of a table ? Answer: No. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the various types of parameter modes in a procedure ? Answer: IN, OUT AND INOUT. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is Over Loading and what are its restrictions ? Answer: OverLoading means an object performing different functions depending upo

n the no.of parameters or the data type of the parameters passed to it. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can functions be overloaded ? Answer: Yes. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can 2 functions have same name & input parameters but differ only by return data type Answer: No. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the constructs of a procedure, function or a package ? Answer: The constructs of a procedure, function or a package are : variables and constants cursors exceptions ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Why Create or Replace and not Drop and recreate procedures ? Answer: So that Grants are not dropped. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you pass parameters in packages ? How ? Answer: Yes.You can pass parameters to procedures or functions in a package. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the parts of a database trigger ? Answer: The parts of a trigger are: A triggering event or statement A trigger restriction A trigger action ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the various types of database triggers ? Answer: There are 12 types of triggers, they are combination of : Insert, Delete and Update Triggers. Before and After Triggers. Row and Statement Triggers. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the advantage of a stored procedure over a database trigger ? Answer: We have control over the firing of a stored procedure but we have no con trol over the firing of a trigger. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the maximum no.of statements that can be specified in a trigger statemen t ? Answer: One. ---------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------Can views be specified in a trigger statement ? Answer: No ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the values of :new and :old in Insert/Delete/Update Triggers ? Answer: INSERT : new = new value, old = NULL DELETE : new = NULL, old = old value UPDATE : new = new value, old = old value ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are cascading triggers? What is the maximum no of cascading triggers at a t ime? Answer: When a statement in a trigger body causes another trigger to be fired, t he triggers are said to be cascading.Max = 32. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are mutating triggers ? Answer: A trigger giving a SELECT on the table on which the trigger is written. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are constraining triggers ? Answer: A trigger giving an Insert/Updat e on a table having referential integri ty constraint on the triggering table. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Describe Oracle database s physical and logical structure ? Answer: Physical : Data files, Redo Log files, Control file. Logical : Tables, Views, Tablespaces, etc. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you increase the size of a tablespace ? How ? Answer: Yes, by adding datafiles to it. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you increase the size of datafiles ? How ? Answer: No (for Oracle 7.0) Yes (for Oracle 7.3 by using the Resize clause ) ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the use of Control files ? Answer: Contains pointers to locations of various data files, redo log files, et c. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the use of Data Dictionary ? Answer: It Used by Oracle to store information about various physical and logica l Oracle structures e.g.Tables, Tablespaces, datafiles, etc --------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------What are the advantages of clusters ? Answer: Access time reduced for joins. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the disadvantages of clusters ? Answer: The time for Insert increases. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can Long/Long RAW be clustered ? Answer: No. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can null keys be entered in cluster index, normal index ? Answer: Yes. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can Check constraint be used for self referential integrity ? How ? Answer: Yes.In the CHECK condition for a column of a table, we can reference som e other column of the same table and thus enforce self referential integrity. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the min.extents allocated to a rollback extent ? Answer: Two ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the states of a rollback segment ? What is the difference between partl y available and needs recovery ? Answer: The various states of a rollback segment are : ONLINE OFFLINE PARTLY AVAILABLE NEEDS RECOVERY INVALID. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the difference between unique key and primary key ? Answer: Unique key can be null; Primary key cannot be null. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ? Answer: No. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you define multiple savepoints ? Answer: Yes. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Can you Rollback to any savepoint ? Answer: Yes. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the maximum no.of columns a table can have ? Answer: 254. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the significance of the & and && operators in PL SQL ? Answer: The & operator means that the PL SQL block requires user input for a var iable.The && operator means that the value of this variable should be the same a s inputted by the user previously for this same variable ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you pass a parameter to a cursor ? Answer: Explicit cursors can take parameters, as the example below shows.A curso r parameter can appear in a query wherever a constant can appear. CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHERE sal > median; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the various types of RollBack Segments ? Answer: The types of Rollback sagments are as follows : Public Available to all instances Private Available to specific instance ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you use %RowCount as a parameter to a cursor ? Answer: Yes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Is the query below allowed : Select sal, ename Into x From emp Where ename = KING (Where x is a record of N umber(4) and Char(15)) Answer: Yes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Is the assignment given below allowed : ABC = PQR (Where ABC and PQR are records) Answer: Yes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Is this for loop allowed : For x in &Start..&End Loop Answer: Yes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------How many rows will the following SQL return : Select * from emp Where rownum < 1 0; Answer: 9 rows ---------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------How many rows will the following SQL return : Select * from emp Where rownum = 1 0; Answer: No rows ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Which symbol preceeds the path to the table in the remote database ? Answer: @ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Are views automatically updated when base tables are updated ? Answer: Yes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can a trigger written for a view ? Answer: No ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------If all the values from a cursor have been fetched and another fetch is issued, t he output will be : error, last record or first record ? Answer: Last Record ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------A table has the following data : [[5, Null, 10]].What will the average function return ? Answer: 7.5 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Is Sysdate a system variable or a system function? Answer: System Function ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Consider a sequence whose currval is 1 and gets incremented by 1 by using the ne xtval reference we get the next number 2.Suppose at this point we issue an rollb ack and again issue a nextval.What will the output be ? Answer: 3 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Definition of relational DataBase by Dr.Codd (IBM)? Answer: A Relational Database is a database where all data visible to the user i s organized strictly as tables of data values and where all database operations work on these tables. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is Multi Threaded Server (MTA) ? Answer: In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user.But in MT A the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request fo r work thus reducing the databases memory requirement and resources. --------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------Which are initial RDBMS, Hierarchical & N/w database ? Answer: RDBMS - R system Hierarchical - IMS N/W - DBTG ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Difference between Oracle 6 and Oracle 7 Answer: ORACLE 7 ORACLE 6 Cost based optimizer Rule based optimizer Shared SQL Area SQL area allocated for each user Multi Threaded Server Single Threaded Server Hash Clusters Only B-Tree indexing Roll back Size Adjustment No provision Truncate command No provision Distributed Database Distributed Query Table replication & snapshots No provision Client/Server Tech No provision ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is Functional Dependency? Answer: Given a relation R, attribute Y of R is functionally dependent on attrib ute X of R if and only if each X-value has associated with it precisely one -Y v alue in R ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is Auditing ? Answer: The database has the ability to audit all actions that take place within it. a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest (1,NULL) or Least(1,NULL) NULL ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------While designing in client/server what are the 2 imp.things to be considered ? Answer: Network Overhead (traffic), Speed and Load of client server ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the disadvantages of SQL ? Answer: Disadvantages of SQL are : Cannot drop a field Cannot rename a field Cannot manage memory Procedural Language option not provided Index on view or index on index not provided View updation problem ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------When to create indexes ? Answer: To be created when table is queried for less than 2% or 4% to 25% of the table rows. ---------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------How can you avoid indexes ? Answer: To make index access path unavailable Use FULL hint to optimizer for ful l table scan Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. Use an expression in the Where Clause of the SQL. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the result of the following SQL : Select 1 from dual UNION Select A fr om dual; Answer: Error ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can database trigger written on synonym of a table and if it can be then what wo uld be the effect if srcinal table is accessed. Answer: Yes, database trigger would fire. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you alter synonym of view or view ? Answer: No ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can you create index on view Answer: No. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the difference between a view and a synonym ? Answer: Synonym is just a second name of table used for multiple link of databas e.View can be created with many tables, and with virtual columns and with condit ions.But synonym can be on view. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What s the length of SQL integer ? Answer: 32 bit length ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What is the difference between foreign key and reference key ? Answer: Foreign key is the key i.e.attribute which refers to another table prima ry key. Reference key is the primary key of table referred by another table. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Can dual table be deleted, dropped or altered or updated or inserted ? Answer: Yes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------If content of dual is updated to some value computation takes place or not ? Answer: Yes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------If any other table same as dual is created would it act similar to dual?

Answer: Yes ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------For which relational operators in where clause, index is not used ? Answer: <> , like %... is NOT functions, field +constant, field|| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Assume that there are multiple databases running on one machine.How can you swit ch from one to another ? Answer: Changing the ORACLE_SID ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------What are the advantages of Oracle ? Answer: Portability : Oracle is ported to more platforms than any of its competi tors, running on more than 100 hardware platforms and 20 networking protocols. M arket Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue.This market clout means t hat you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available. Backup and Recovery : Oracle provides indu strial strength support for on-line backup and recovery and good software fault tolerence to disk failure.You can also do point-in-time recovery. Performance : Speed of a tuned Oracle Database and application is quite good, even with larg e databases.Oracle can manage > 100GB databases. Multiple database support : Ora cle has a superior ability to manage multiple databases within the same transact ion using a two-phase commit protocol. ----------+---------------+---------------+---------------+---------------+--------------+---------------+---------------+---------------+---------------+-------------What is a forward declaration ? What is its use ? Answer: PL/SQL requires that you declare an identifier before using it.Therefore , you must declare a subprogram before calling it.This declaration at the start of a subprogram is called forward declaration.A forward declaration consists of a subprogram specification terminated by a semicolon. ----------+---------------+---------------+---------------+---------------+--------------+---------------+---------------+---------------+---------------+-------------What are actual and formal parameters ? Answer: Actual Parameters : Subprograms pass information using parameters.The va riables or expressions referenced in the parameter list of a subprogram call are actual parameters.For example, the following procedure call lists two actual pa rameters named emp_num and amount: Eg.raise_salary(emp_num, amount);Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal param eters.For example, the following procedure declares two formal parameters named emp_id and increase: Eg.PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL ; ----------+---------------+---------------+---------------+---------------+--------------+---------------+---------------+---------------+---------------+-------------What are the types of Notation ? Answer: Position, Named, Mixed and Restrictions. ----------+---------------+---------------+---------------+---------------+--------------+---------------+---------------+---------------+---------------+--------------

What all important parameters of the init.ora are supposed to be increased if yo u want to increase the SGA size ? Answer: In our case, db_block_buffers was changed from 60 to 1000 (std values ar e 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 30 0) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of databas e creation}. The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB. ----------+---------------+---------------+---------------+---------------+--------------+---------------+---------------+---------------+---------------+-------------If I have an execute privilege on a procedure in another users schema, can I exe cute his procedure even though I do not have privileges on the tables within the procedure ? Answer: Yes ----------+---------------+---------------+---------------+---------------+--------------+---------------+---------------+---------------+---------------+-------------What are various types of joins ? Answer: Types of joins are: Equijoins Non-equijoins self join outer join ----------+---------------+---------------+---------------+---------------+--------------+---------------+---------------+---------------+---------------+-------------What is a package cursor ? Answer: A package cursor is a cursor which you declare in the package specificat ion without an SQL statement.The SQL statement for the cursor is attached dynami cally at runtime from calling procedures. ----------+---------------+---------------+---------------+---------------+--------------+---------------+---------------+---------------+---------------+-------------If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted ? Answer: Yes.Because Create table is a DDL which commits automatically as soon as it is executed.The DDL commits the transaction even if the create statement fai ls internally (eg table already exists error) and not syntactically. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++ What is the purpose of the PL/SQL language? PL/SQL is an extension of SQL. SQL is non-procedural. PL/SQL is a procedural lan guage designed by oracle to overcome the limitations that exist in SQL. Say True or False. If False, explain why. Routines written in PL/SQL can be called in Oracle call interface, Java, Pro*C/C ++, COBOL etc. True. Say True or False. If False, explain why. PL/SQL does not have data types or variables. False. PL/SQL has all features of a structured programming language including da ta types, variables, subroutines, modules and procedural constructs. State few notable characteristics of PL/SQL. Block-structured language.

Stored procedures help better sharing of application. Portable to all environments that support Oracle. Integration with the Oracle data dictionary. ---Name few schema objects that can be created using PL/SQL? Stored procedures and functions Packages Triggers Cursors ------------State some features or programming constructs supported by PL/SQL? Variables and constants Embedded SQL support Flow control Cursor management Exception handling Stored procedures and packages Triggers ----------------------------What are the three basic sections of a PL/SQL block? Declaration section Execution section Exception section --------------What is wrong in the following assignment statement? balance = balance + 2000; Use of wrong assignment operator. The correct syntax is: balance := balance + 20 00; ---------------Write a single statement that concatenates the words `Hello' and `World' and assign it i n a variable named greeting.? greeting := `Hello' || `World'; ---------------------------Which operator has the highest precedence among the following AND, NOT, OR? NOT Which of the following operator has the lowest precedence among the following **, OR, NULL ? OR What does the colon sign (: ) implies in the following statement? :deficit := balance ± loan; The colon (: )sign implies that the variable :deficit is an external variable. What is the purpose of %type data type? Explain with example. It assigns a variable the same data type used by the column, for which the varia ble is created. For example,

dcode := dept.detpno%type; The variable dcode is created with the same data type as that of the deptno colu mn of the dept table. What is the purpose of %rowtype data type? Explain with example. It declares a composed variable that is equivalent to the row of a table. After the variable is created, the fields of the table can be accessed, using the name of this variable. For example emptype := emp%rowtype; name := emptype.empname; What is a PL/SQL package? A package is a file that groups functions, cursors, stored procedures, and varia bles in one place.

What is a trigger? A trigger is a PL/SQL program that is stored in the database and executed immedi ately before or after the INSERT, UPDATE, and DELETE commands.

What are the PL/SQL cursors? Oracle uses workspaces to execute the SQL commands. In other words, when Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. A cursor is an identifier for this area. It allows programmers to name this are a and access it's information.

Say True or False. If False, explain why. PL/SQL engine is part of Oracle Server. True. Say True or False. If False, explain why. The BEGIN declaration starts the variable declaration sections of a PL/SQL block . False. The BEGIN declaration starts the execution section. Say True or False. If False, explain why. The PL/SQL engine executes the procedural commands and passes the SQL commands f or the Oracle server to process. True. Say True or False. If False, explain why. PL/SQL supports the CREATE command. False. PL/SQL doesn't support the data definition commands like CREATE.

What is returned by the cursor attribute SQL%ROWCOUNT? It returns the number of rows that are processed by a SQL statement.

What is returned by the cursor attribute SQL%FOUND? It returns the Boolean value TRUE if at least one row was processed. What is returned by the cursor attribute SQL%NOTFOUND? It returns the Boolean value TRUE if no rows were processed. Which command/commands allow iteration a use of loops in a PL/SQL block? LOOP command, FOR.. LOOP command, WHILE command. What is the difference in execution of triggers and stored procedures? A trigger is automatically executed without any action required by the user, whe reas, a stored procedure needs to be explicitly invoked.

What are the uses of triggers? Basically triggers are used to create consistencies, access restriction and impl ement securities to the database. Triggers are also used for Creating validation mechanisms involving searches in multiple tables Creating logs to register the use of a table Update other tables as a result of inclusion or changes in the current table.

Say True or False. If False, explain why. Triggers can be associated to a view. True. Say True or False. If False, explain why. When a trigger is associated to a view, the base table triggers are normally dis abled. False. When a trigger is associated to a view, the base table triggers are norma lly enabled.

Say True or False. If False, explain why. A trigger can perform the role of a constraint, forcing an integrity rule. True. Say True or False. If False, explain why. A trigger can execute the COMMIT, ROLLBACK, or SAVEPOINT commands. A trigger cannot execute the COMMIT, ROLLBACK, or SAVEPOINT commands. What is the use of a WHEN clause in a trigger? A WHEN clause specifies the condition that must be true for the trigger to be tr iggered. Say True or False. If False, explain why. Statement level triggers are triggered only once. True.

What is the purpose of the optional argument [OR REPLACE] in a CREATE TRIGGER co mmand? The optional argument [OR REPLACE] in a CREATE TRIGGER command re creates an exi sting trigger. Using this option allows changing the definition of an existing t rigger without having to delete it first. Say True or False. If False, explain why. INSTEAD OF is a valid option only for triggers in a table. False. INSTEAD OF is a valid option only for views. INSTEAD OF trigger cannot be specified in a table. Write a statement to disable a trigger named update_marks. ALTER TRIGGER update_marks DISABLE;

Which command is used to delete a trigger? DROP TRIGGER command. Which command is used to delete a procedure? DROP PROCEDURE command. .

What is the difference between a function and a stored procedure? A function returns a value and a stored procedure doesn't return a value.

How do you declare a user defined exception? User defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION. Syntax EXCEPTION; What do you understand by explicit cursors? Explicit cursors are defined explicitly using the CURSOR statement, with a gener al syntax CURSOR cursor_name [(parameters)] IS query_expression; It allows processing queries that return multiple rows.

What are the steps that need to be performed to use an explicit cursor? Discuss briefly. The steps that need to be performed on explicit cursor are DECLARE - assigns a name to the cursor and defines the structure of query within i t. OPEN - executes the query, whereby the rows returned by the query are available fo

r fetching. FETCH - assigns values from the current row (cursor position) into specified varia bles. CLOSE - releases the memory space.

PL/SQL packages usually have two parts. What are these two parts? PL/SQL packages have two parts Specification part - where the interface to the application are defined. Body part - where the implementation of the specification are defined.

Which command(s) are used for creating PL/SQL packages? CREATE PACKAGE command is used for creating the specification part. CREATE PACKA GE BODY command is used for creating the body part.

How do you refer to the types, objects and subprograms declared within a packag e? The types, objects, and subprograms declared within a package are referred to u sing the dot notation as package_name.type_name package_name.object_name package_name.subprogram_name Say True or False. If False, explain why. PL/SQL allows subprogram overloading feature within a package. true

Which command is used to delete a package? The DROP PACKAGE command. What is the difference between implicit and explicit cursors? Oracle implicitly declares a cursor to all the DDL and DML commands that return only one row. For queries returning multiple rows, an explicit cursor is created .

Say True or False. If False, explain why.

The %NOTFOUND attribute returns true when the cursor is not created explicitly. False. The %NOTFOUND attribute returns true when the last row of the cursor is p rocessed and no other row is available. Say True or False. If False, explain why. The %ROWCOUNT attribute returns the total number of rows returned by the FETCH c ommand. True.

Oracle interview questions and answers

Part 2 | Part 3 | Part 4 | Part 5 | Part 6 | Part 7 | Part 8 | Part 9 Oracle interview questions

April 17, 2013 at 01:16 PM by Kshipra Singh

1. Explain: a.) Integrity Constraints It can be called as a declarative way in order to define a business rule for a t able s column b.) Index It can be called as an optional structure which is associated with a table for direct access to the rows Index can be created for one or more columns in a table c.) Extent It can be defined as a specific number of contiguous data blocks in single all ocation. It is used to store a specific type of information. 2. What is ANALYZE command used for? ANALYZE command is used to perform various functions on index, table, or cluster , as listed below: It helps in dentifying migrated and chained rows of the table or cluster. It helps in validating the structure of the object. It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary. It helps in deleting statistics used by object from the data dictionary. Oracle 11g dba interview questions and answers

50 questions

Test your Oracle skills: Oracle Oracle Oracle Oracle Oracle Oracle

interview test part 1 (40 questions) DBA interview test (30 questions) PLSQL interview test (30 questions) Replication interview test (20 questions) Architecture interview test (20 questions) Transaction interview test (20 questions)

3. a.) List the types of joins used in writing SUBQUERIES.

Self join Outer Join Equi join b.) List the various Oracle database objects. TABLES VIEWS INDEXES SYNONYMS SEQUENCES TABLESPACES 4. a.) Explain i.)Rename ii.)Alias. Rename It is a permanent name provided to a table or column. Alias It is a temporary name provided to a table or column which gets over a fter the execution of SQL statement. b.) What is a view? It is virtual table which is defined as a stored procedure based on one or mor e tables. 5. a.) What are the varoius components of physical database structure of Oracle database? Oracle database comprises of three kinds of files: Datafiles, Redo log files, Control files. b.) List out the components of logical database structure of Oracle database. Tablespaces Database s schema objects. 6. a.) What do you mean by a tablespace? These are the Logical Storage Units into which a database is divided. It is used to group together the related logical structures. b.) What is Control File used for? Control File is used for: Database recovery. Whenever an instance of an ORACLE database begins, its control file is used to identify the database and redo log files that must be opened for database opera tion to go ahead. 7. a.) What is a synonym? What are its various types? A synonym can be called as an alias for a table, view, sequence or program unit. It is basically of two types: Private Only the owner can access it. Public Can be accessed by any database user. b.) What are the uses of synonyms?

Mask the real name and owner of an object. Provide public access to an object Provide location transparency for tables, views or program units of a remote d atabase. Simplify the SQL statements for database users. 8.) What do you mean by a deadlock? When two processes are waiting to update the rows of a table which are locked by another process, the situation is called a deadlock. The reasons for it to happen are: lack of proper row lock commands. Poor design of front end application It reduces the performance of the server severely. These locks get automatically released automatically when a commit/rollback op eration is performed or any process is killed externally. 9.) a.) What suggestions do you have to reduce the network traffic? Following are some of the actions which can be taken to reduce the network traff ic: Use snapshots to replicate data. Use remote procedure calls. Replicate data in distributed environment. b.) What are the various types of snapshots ? There are two types of snapshots: Simple snapshots Based on a query that does not contain GROUP BY clauses, CO NNECT BY clauses, JOINs, sub query or snashot of operations. Complex snapshots which contains atleast any one of the above. 10.) What is a sub query? What are its various types? Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables. A sub query is added in the where clause of the main query. There can be two types of subqueries: a.) Correlated sub query It can reference column in a table listed in the from list of the outer query but is not as independent as a query. . b.) Non Correlated sub query Results of this sub query are submitted to the main query or parent query. It is independent like a query 11.) Will you be able to store pictures in the database?Explain. Yes, pictures can be stored in the database using Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. However, the table can have only one Long Raw data type. 12.) Explain: a.) Integrity constraint. It is a declaration defined in a business rule for a table column. Integrity constraint ensures the accuracy and consistency of data in a databas

e. It is of three types main Integrity.

namely

Domain Integrity, Referential Integrity and Do

b.) COALESCE function This function is used to return the value which is set not to be null in the l ist. Incase all values in the list are null the coalesce function will return NULL. Its representation: Coalesce(value1, value2, value3,¼) 13.) Explain the following: a.) BLOB datatype. It is a data type with varying length binary string, used to store two gigabyt es memory. For BLOB, the length needs to be specified in bytes. b.) DML. DML it is also termed as Data Manipulation Language (DML). It is used to access and manipulate data in the existing objects. DML statements are insert, select, update and delete. 14.) Differentiate between: a.) TRANSLATE and REPLACE. Translate is used to substitute character by character. Replace is used to substitute a single character with a word. b.) What is Merge Statement used for? Merge statement is used to select rows from one or more data source to updatin g and insert into a table or a view. 15.) What are the various Oracle Database objects? Various database objects are as follows: Tables ± This is a set of elements organized in vertical and horizontal fashion. Tablespaces ± This is a logical storage unit in Oracle. Views ± It is virtual table derived from one or more tables. Indexes ± This is a performance tuning method to process the records. Synonyms ± This is a name for tables. Sequences. 16. What is the purpose of Save Points in Oracle database? Save Points are used to divide a transaction into smaller phases. It enables rolling back part of a transaction. Maximum 5 save points are allowed in Oracle Database. Whenever an error is encountered, it is possible to rollback from the point wh ere the SAVEPOINT has been saved. 17. a.) What is an ALERT? It a window which appears on the screen overlaying a portion of the current di splay. b.) Differentiate between post database commit and post form commit.

The post database commit trigger is fired after oracle forms issue the commit to finalized transactions. The post form commit is fired during the post and commit transactions process, after the database commit occurs. 18. Differentiate between pre select and pre query. Once oracle forms construct the select statement to be issued Pre select is fi red during the execute query and count query processing. All this happens before the statement is actually issued. The pre query trigger is fired just before oracle forms issue the select state ment to the database. 19. What is hot backup and logical backup? Hot backup Backing up the archive log files when database is open is called Hot backup. To do this, the ARCHIVELOG mode is enabled. Following files are backed up All data files, Archive log, redo log files an d control files. Logical backup Logical back ip is reading a set of database records and writing them into a f ile. An Export utility is required to take the backup while an Import utility is re quired to recover from the backup. 20. What do you mean by Redo Log file mirroring ? The process of having a copy of redo log files is called mirroring. It is done by creating group of log files together. This ensures that LGWR aut omatically writes them to all the members of the current on line redo log group. In case a group fails, the database automatically switches over to the next gr oup. It diminishes the performance. Interview Questions Oracle interview questions <> | part 8 | part 9

Part 2

Part 1 | Part 2 | Part 3 | Part 4 | part 5 | part 6 | part 7

What is implicit cursor in Oracle? Latest answer: An implicit cursor is a cursor which is internally created by Ora cle, It is created by Oracle for each individual SQL........... Read answer Can you pass a parameter to a cursor? Explain with an explain Latest answer: Yes, explicit cursors can take parameters........... Read answer What is a package cursor? Latest answer: In a package cursor, the SQL statement for the cursor is attached dynamically at runtime from calling procedures............

Read answer Explain why cursor variables are easier to use than cursors. Latest answer: They are easier to define as there is no need to specify a query statement, The query can also be specified dynamically at the opening time...... ..... Read answer What is locking, advantages of locking and types of locking in oracle? Latest answer: Locking protect table when several users are accessing the same t able. Locking is a concurrency control technique in oracle. It helps in data int egrity while allowing maximum.......... Read answer What are transaction isolation levels supported by Oracle? Latest answer: READ COMMITTED: If row locks are obtained by a certain transactio n, then any other transaction that contains DML needs to wait until the row lock s have been released by that particular transaction............. Read answer Explain how to view existing locks on the database. Latest answer: A number of data locks need to be monitored, in order to maintain a good performance level for all sessions, along with the time for which they l ast............ Read answer Explain how to lock and unlock a user account in Oracle. Latest answer: SQL> ALTER USER user_name ACCOUNT LOCK; SQL> ALTER USER user_name ACCOUNT UNLOCK; Read answer What are background processes in oracle? Latest answer: Oracle uses background process to increase performance : Database writer, DBWn, Log Writer, LGWR, Checkpoint, CKPT, System Monitor, SMON, Process Monitor, PMON, Archiver, ARCn........... Read answer What is SQL*Loader? Latest answer: It is a database tool that allows data to be loaded from external files into database tables, It is available as part of the free Oracle 10g Expr ession Edition.......... Read answer What is a SQL*Loader Control File? Latest answer: A SQL*Loader control file contains the following specification: l ocation of the input data file, format of the input date file, target table wher e the data should be loaded............ Read answer Explain oracle memory structures. Latest answer: Two memory area: System global area(SGA), Program Global Area(PGA

).......... Read answer What is Program Global Area (PGA)? Latest answer: The PGA is a memory area that contains data and control informati on for the Oracle server processes. This area consists of the following componen ts:............. Read answer What is a shared pool? Latest answer: It is the area in SGA that allows sharing of parsed SQL statement s among concurrent users........... Read answer What is snapshot in oracle? Latest answer: A recent copy of a table or a subset of rows or cols of a table i s called as snapshot in oracle. A snapshot is more useful in distributed computi ng environment. We can create.......... Read answer What is a synonym? Latest answer: Synonym simplifies the use of the table, the table for which syno nym is created can be referred by synonym name............ Read answer What is a schema? Latest answer: Schema represents structure of the database. Database has two mai n types of schemas partitioned : Physical schema: Describes the database design at the physical level............. Read answer Explain how to list all indexes in your schema. Latest answer: The list of all indexes in a schema can be obtained through the U SER_INDEXES view with a SELECT statement:........... Read answer What are Schema Objects? Latest answer: Schema objects are the logical database structure that represents database s data. Schema objects include tables, views, sequences, synonyms, ind exes, clusters, database............ Read answer What is an Archiver? Latest answer: Archiving is the process of removing of old data and unused data from the main databases. This process keeps databases smaller, more manageable a nd thus acquires............. Read answer What is a sequence in oracle? Latest answer: A Sequence is a user created database object. A sequence can be s hared by multiple users to generate unique integers. This object is used to crea

te a primary key value............... Read answer Difference between a hot backup and a cold backup Latest answer: Cold Backup : In this type of backup, after the database is shut down, DBA exits the SVRMGR utility and copies the log files, data files and cont rol files onto a backup media........... Read answer How to retrieve 5th highest sal from emp table? SELECT DISTINCT (emp1.sal) FROM EMP emp1 WHERE &N = (SELECT COUNT (DISTINCT (emp 2.sal)).......... Read answer What is $FLEX$ and $PROFILES$? Where are they used? $FLEX$ is used to get a value used in the previous value set. It is usually used to retrieve the Flex value contained in an AOL value.......... Read answer How to call a trigger inside a stored procedure? A trigger cannot be called within a stored procedure. Triggers are executed auto matically as a result of DML or DDl commands............ Read answer What is WATER MARK IN oracle? Explain the significance of High water mark. WATER MARK is a divided segment of used and free blocks. Blocks which are below high WATER MARK i.e. used blocks, have at least once........... Read answer What is an object groups? Object group is a container for a group of objects.......... Read answer Difference between clustering and mirroring Clustering means one than one database server configured for the same user conne ction. When users connect, one of the server's responds.......... Read answer Difference between paging and fragmentation Paging is a concept occurring in memory, whereas, Fragmentation occurs on disk l evel.......... Read answer Can you explain how to insert an image in table in oracle? Insert image into a table: Create the following table: create table pics_table ( bfile_id number,......... Read answer How to find out second largest value in the table? SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP.........

Read answer Disadvantage of user defined function in oracle. Disadvantage of UDF in Oracle: Oracle does not support calling UDFs with Boolean parameters or return types.......... Read answer Explain the significance of cluster table or non cluster table. A Cluster provides an alternate method of storing table data. It is made up of a group of tables that share the same data......... Read answer What are the purposes of Import and Export utilities? Latest answer: Import and Export utilities are helpful in following ways: They c an be used for backup and recovery process, they can also be used while moving d atabase between two different............... Read answer Difference between ARCHIVELOG mode and NOARCHIVELOG mode Latest answer: There are two modes in which the hot backup works: ARCHIEVELOG mo de, NOARCHIVELOG mode........ Read answer What are the srcinal Export and Import Utilities? Latest answer: The import and export utilities of oracle provide a very simple w ay to transfer data objects between Oracle databases. These may reside on hetero geneous software and hardware............ Read answer What are data pump Export and Import Modes? Latest answer: Data pump export and import modes are used to determine the type and portions of database to be exported and imported:............... Read answer What are SQLCODE and SQLERRM and why are they important for PL/SQL developers? Latest answer: When a SQL statement raises an exception, Oracle captures the err or codes by using the SQLCODE and SQLERRM globally defined variables............ ... Read answer Explain user defined exceptions in oracle. Explain the concepts of Exception in Oracle. Explain its type. Latest answer: Exceptions in oracle occur when unwanted situations happen during the execution of a program. They can occur due to system error, user error or a pplication error............. Read answer How exceptions are raised in oracle? Latest answer: Internal exceptions are raised implicitly by the run time system. However, user defined exceptions must be raised explicitly by RAISE statements.

............... Read answer What is tkprof and how is it used? Latest answer: Tkprof is a performance diagnosing utility available to DBAs. It formats a trace file into a more readable format for performance analysis. So th at the DBA can identify and resolve............... Read answer What is Oracle Server Autotrace? Latest answer: The Autotrace feature of Oracle server generates two statement ex ecution reports which are useful for performance tuning. They are: Statement exe cution path.............. Read answer Explain the different types of queries in Oracle. Latest answer: Session Queries are implicitly constructed and executed by a Sess ion based on input parameters.Input parameters are used to perform the most comm on data source actions on objects............ Read answer What is SQL*Plus? Latest answer: SQL*Plus is an interactive and batch query tool, it gets installe d with every Oracle Database Server or Client installation............. Read answer Explain how to change SQL*Plus system settings. Latest answer: The SET command can be used to change the settings in the SQl*PLU S environment : SET AUTOCOMMIT OFF: Turns off the auto commit feature, SET FEEDB ACK OFF............ Read answer What are SQL*Plus Environment variables? Latest answer: The behaviour of SQL PLUS depends on some environmental variables predefined in the OS: ORACLE_HOME: This variable stores the home directory wher e the Oracle client............. Read answer What is Output Spooling in SQL*Plus? Latest answer: The spooling feature facilitates copying of all the contents of t he command line SQL*Plus to a specified file. This feature is called Spooling... ......... Read answer What is Input Buffer in SQL*Plus? Latest answer: Input buffer feature of the command line SQL*Plus tool allows a r evision of multiple line command and rerunning it with a couple of simple comman ds. The last SQL statement is........... Read answer What is a subquery in Oracle?

Latest answer: When a query needs to be run which has a condition that needs to be a result of another query then, the query in the condition part of the main o ne is called a sub query............... Read answer What is Data Block? Latest answer: Data blocks are also called logical blocks, Oracle blocks, or pag es. At the finest level of granularity, Oracle stores data in data blocks....... ..... Read answer Explain the difference between a data block, an extent and a segment. Latest answer: A data block is the smallest unit of logical storage for a databa se object. As objects grow they take chunks of additional storage that are compo sed of contiguous data blocks............. Read answer What are the uses of Rollback Segment? Latest answer: Rollback segments undo changes when a transaction is rolled back, they also ensure that transactions leave the uncommitted changes unnoticed..... ......... Read answer What is Rollback Segment in oracle? Latest answer: Rollback Segment in oracle is used to store "undo" information te mporarily......... Read answer What are the different types of Segments? Latest answer: Data Segment, Index Segment, Rollback Segment, and Temporary Segm ent........... Read answer Explain difference between SQL and PL/SQL. Latest answer: PL/SQL is a transaction processing language that offers the follo wing advantages: support for SQL SQL is flexible, powerful and easy to learn. support for object oriented programming............... Read answer <> Part 1 | Part 2 | P

Interview Questions Oracle interview questions <> | part 8 | part 9

Part 3

Part 1 | Part 2 | Part 3 | Part 4 | part 5 | part 6 | part 7

Write a PL/SQL program for a function returning total tax collected from a parti cular place. Latest answer: PL/SQL program

Create of Replace Function Tax Amt Place varchar2, Return Number is............ Read answer What are the types PL/SQL code blocks? Latest answer: Anonymous Block, Stored Program Unit, Trigger........... Read answer Advantages of PL/SQL Latest answer: Support SQL data manipulation, provide facilities like conditiona l checking, branching and looping............. Read answer What is WebDB? Latest answer: WebDB is a tool written in PL/SQL, used to develop HTML based app lication that can easily interact with Oracle data................... Read answer What is Nested Table? Latest answer: Nested Table is a table inside a table. It can have several rows for each row of its parent table............ Read answer What is clusters? Latest answer: Clusters group together tables that share common columns. These t ables are often used together......... Read answer Explain how to add a new column to an existing Table in Oracle. Latest answer: Use the ALTER TABLE command to do this ALTER TABLE employee ADD ( department VARCHAR2);......... Read answer How many types of Tables supported by Oracle? Explain them Latest answer: Ordinary (heap organized) table, Clustered table, Index organized table, Partitioned table........... Read answer Explain how to view all columns in an Existing Table. Latest answer: Use the command DESC and the table name to view the information a bout the columns.......... Read answer Explain how to recover a dropped Table in Oracle. Latest answer: select * from recyclebin where srcinal_name =

table_name ;

Then u can use flashback table table_name to before drop;..................... Read answer

What is an Oracle Recycle Bin? Latest answer: The tables that have been dropped can be retrieved back using the reycle bin feature of Oracle, they can be recovered back by the Flashback Drop action............ Read answer What is an External Table? Latest answer: A table with its data stored outside the database as an OS file i s an external table........... Read answer Describe how to load data through External Tables. Latest answer: Create an external table with columns matching data fields in the external file........... Read answer What is the role of Archiver [ARCn]? ARCn is an oracle background process responsible for copying the entirely filled online redo log file to the archive log......... Read answer Structural difference between bitmap and btree index in oracle. Btree It is made of branch nodes and leaf nodes. Branch nodes holds prefix key v alue along with the link to the leaf node. The leaf node in turn........ Read answer Can you explain how to convert oracle table data into excel sheet? There are 2 ways to do so: 1. Simply use the migration wizard shipped with Oracl e......... Read answer When should we go for hash partitioning? Scenarios for choosing hash partitioning: Not enough knowledge about how much da ta maps into a give range........... Read answer What is Materialized view? What is a snapshot? What are the similarities and dif ferences between Materialized views and snapshots? A materialized view is a database object that contains the results of a query. A snapshot is similar to materialized view.......... Read answer What are the advantages of running a database in NO archive log mode? 1. Less disk space is consumed 2. Causes database to freeze if disk gets full... ..... Read answer What are the advantages of running a database in archive log mode? It makes it possible to recover database even in case of disk failure...........

. Read answer What is dba_segment in oracle? DBA_SEGMENTS tells about the space allocated for all segments in the database fo r storage......... Read answer ) What is oracle database ? Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical a nd logical structures. Logical structures and physical structures are separated from each other

2) What is schema? A user account and its associated data including tables, views, indexes, cluster s, sequences,procedures, functions, triggers,packages and database links is know n as Oracle schema. System, SCOTT etc are default schema s. We can create a new Schema/User. But we can t drop default database schema s.

3) What is a Tablespace? Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema s and a schema can have multiple tablespace s. Database creates "SYSTEM tablespace" by d efault during database creation. It contains read only data dictionary tables wh ich contains the information about the database. Also Read Basic to Advanced Oracle SQL Query Interview Question and Answers 4) What is a Control file redo files, ile database

Control File ? is a binary file which stores Database name, associated data files, DB creation time and current log sequence number. Without control f cannot be started and can hamper data recovery.

5) Define data blocks ? Data Blocks are the base unit of logical database space. Each data block represe nts a specific number of bytes of database space on a disk 6) What is an Extent ? Extent is a collection of Continuous data blocks, which is used for storing a sp ecific type of information. Are you looking for frequently asked Database Interview Questions and Answers? C lick here 7) What is a Segment ? A segment is a collection of extends which is used for storing a specific data s tructure and resides in the same tablespace. 8) What is Rollback Segment ? Database contain one or more Rollback Segments to roll back transactions and dat a recovery.

9) What are the different type of Segments ? Data Segment(for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment. 10) What is a Redo Log ? Redo Log files is a collection of 2 or more pre allocated files, which is used i n data recovery. When ever a change is made to the database, change info gets st ored in redo files. In case of a database crash, we can used redo files for data recovery. 11) What is a table Cluster ? Table Cluster is a group of related tables that share common columns are store r elated data in the same block.

Write a PL/SQL program for a trigger. PL/SQL program for tracking operation on a emp table. Create or Replace Trigger EmpTracking Before Insert or Delete or Update on Emp For each row Declare Begin If Inserting then /*Can perform operations just before record is inserted into the emp table*/ Else if Updating then /*Can perform operations just before record is about to be updated */ Else if Deleting then /*Can perform operations just before record is about to be delete d*/ End If End EmpTracking

×

Report "Collection of SQL queries with answers"

Your name

Email

Reason -Select Reason- Pornographic Defamatory Illegal/Unlawful Spam Other Terms Of Service Violation File a copyright complaint

Description

Close Send

×

Sign In

Email

Password

Remember me Forgot password?

Sign In

Our partners will collect data and use cookies for ad personalization and measurement. Learn how we and our ad partner Google, collect and use data. Agree & close