1) What is an integrity constraint?
A. Interity constraint is a rule that restricts values to a column in a table.
A. Interity constraint is a rule that restricts values to a column in a table.
2) How does one create a repositary?
A. For OEM v2 and aobve, start the Oracle Enterprise Manager Configruation Assistant (emca or Unix) to create and configure management server and repositary. Remember to setup a backup for the respositary database after creating it.
A. For OEM v2 and aobve, start the Oracle Enterprise Manager Configruation Assistant (emca or Unix) to create and configure management server and repositary. Remember to setup a backup for the respositary database after creating it.
3) What is a database link?
A. Database Link is a named path through which a remote database can be accessed.
A. Database Link is a named path through which a remote database can be accessed.
4) what is CYBLE / NO CYCLE in a Sequence?
A. CYBLE specifies that the sequence continues to generate values after reaching either maximum or minimum or minimum values. After an ascending sequence reaches its maximum values, it generated its minimum value. After a descending sequence reaches its minimum, it generate its maximum. NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.
A. CYBLE specifies that the sequence continues to generate values after reaching either maximum or minimum or minimum values. After an ascending sequence reaches its maximum values, it generated its minimum value. After a descending sequence reaches its minimum, it generate its maximum. NO CYCLE specifies that the sequence cannot generate more values after reaching its maximum or minimum value.
5) What is correlated sub-query?
A. Correlated sub-query is a sub query which has reference to the main query.
A. Correlated sub-query is a sub query which has reference to the main query.
6) What are the data types allowed in a table?
A. CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.
A. CHAR, VARCHAR2, NUMBER, DATE, RAW, LONG and LONG RAW.
7) what is the difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
A. CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.
A. CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.
8) Can a view be updaets / inserted / deleted? If yes under what conditions?
A. A view can be updated / inserted / deleted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
A. A view can be updated / inserted / deleted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
9) Use the ADD_GROUP_COLUMN function to add a column to a record group that was created at design time? I) TRUE II) FALSE
A. FALSE
A. FALSE
10) Use the ADD_GROUP_ROW procedure to add a row to a static record group? I) TRUE II) FALSE
A. FALSE
A. FALSE
11) maxvalue.sql select the Nth Highest value from a table?
A. select level, max(‘col_name’) from my_table where level = ‘&n’ connect by prior(‘col_name’) > ‘col_name’) group by level;
Example : Giv en a table called up emp with the following columns :
-- id number
-- name varchar2(20)
-- sal number
A. select level, max(‘col_name’) from my_table where level = ‘&n’ connect by prior(‘col_name’) > ‘col_name’) group by level;
Example : Giv en a table called up emp with the following columns :
-- id number
-- name varchar2(20)
-- sal number
For the second highest salary :
-- select level, max( sal) from emp
-- where level = 2
-- connect by prior sal > sal
-- group by level
-- select level, max( sal) from emp
-- where level = 2
-- connect by prior sal > sal
-- group by level
12) Find out nth highest salary from emp table?
A. SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT ( DISTINCT (b.sal)) FROM EMP B WHERE a.sal <= b.sal);
For e.g. Enter value for n : 2
SAL
----------
3700
A. SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT ( DISTINCT (b.sal)) FROM EMP B WHERE a.sal <= b.sal);
For e.g. Enter value for n : 2
SAL
----------
3700
13) Suppose a customer table is having different columns like customer no, payments. What will be the query to select top three max payments?
A. SELECT customer_no, payements from customer C1 WHERE 3 <= (SELECT COUNT(*) from customer C2 where C1.payment <= c2.payment)
A. SELECT customer_no, payements from customer C1 WHERE 3 <= (SELECT COUNT(*) from customer C2 where C1.payment <= c2.payment)
14) What utility is used to create a physical backup?
A. Either rman or alter tablespace begin backup will do.
A. Either rman or alter tablespace begin backup will do.
15) How many types of Sql Statements are there in Oracle?
A. There are basically 6 types of sql statems in oracle.
a) Data Definition Language (DDL) : The DDL statements define and maintain objecets and drop objects.
b) Data Manipulation Language (DML) : The DML statements manipulate database data.
c) Transaction Control Statements: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing e.g. :: Alter Statements, Set Role
e) System Control Statements : : Used to control the properties of Current session enabling and disabling roles and changing. E.g. Alter statements, set role.
f) Embedded Sql :: Incorporate DDL, DML and T.C.S in Programming Language e.g. :: Using the Sql Statements in languages such as ‘C’, Open, Fetch, execute and close
A. There are basically 6 types of sql statems in oracle.
a) Data Definition Language (DDL) : The DDL statements define and maintain objecets and drop objects.
b) Data Manipulation Language (DML) : The DML statements manipulate database data.
c) Transaction Control Statements: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing e.g. :: Alter Statements, Set Role
e) System Control Statements : : Used to control the properties of Current session enabling and disabling roles and changing. E.g. Alter statements, set role.
f) Embedded Sql :: Incorporate DDL, DML and T.C.S in Programming Language e.g. :: Using the Sql Statements in languages such as ‘C’, Open, Fetch, execute and close
16) What is a Transaction in Oracle?
A. Transaciton is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly commited or rolled back.
A. Transaciton is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statement and ends when it is explicitly commited or rolled back.
17) what are the difference between Functions and Procedures?
A. Function are used for computations where as Procedures can be used for performing business logic.
functions must returns a value, Procedures need not be. We can have DML statements in a function. But, we can not call such a function in a SQL Query. Functions parameters are always IN, no OUT is possible. Function returns 1 value only, procedure can return multiple values (max. 1024). We can select the fields from function in the case of procedure we cannot select the fields. Functions do not return the images, text whereas stored procedures returns all.
A. Function are used for computations where as Procedures can be used for performing business logic.
functions must returns a value, Procedures need not be. We can have DML statements in a function. But, we can not call such a function in a SQL Query. Functions parameters are always IN, no OUT is possible. Function returns 1 value only, procedure can return multiple values (max. 1024). We can select the fields from function in the case of procedure we cannot select the fields. Functions do not return the images, text whereas stored procedures returns all.
18) What is the difference between UNION and UNION ALL ?
A. UNION ALL selects all records between two queries including duplicates, whereas UNION takes the time to sort records in order to remove duplicates. In short UNION only select distinct values, UNION ALL selects all values.
A. UNION ALL selects all records between two queries including duplicates, whereas UNION takes the time to sort records in order to remove duplicates. In short UNION only select distinct values, UNION ALL selects all values.
19) Is BETWEEN inclusive of the range values specified?
A. Yes
A. Yes
20) What is correlated sub-query?
A. Correlated sub-query is a sub query which has reference to the main query.
A. Correlated sub-query is a sub query which has reference to the main query.
21) What is the difference between CHAR and VARCHAR2? What is the maximum SIZE allowed for each type?
A. CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.
A. CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.
22) When do we use a LIKE statement?
A. To do partial search e.g. to search employee by name, you need not specify the complete name; using LIKE, you can search for partial string matches.
A. To do partial search e.g. to search employee by name, you need not specify the complete name; using LIKE, you can search for partial string matches.
Example SQL : SELECT EMPNO FROM EMP WHERE EMPNAME LIKE ‘RAMESH%’
% is used to represent remaining all characters in the name.
This query fetches all records contains RAMESH in six characters.
% is used to represent remaining all characters in the name.
This query fetches all records contains RAMESH in six characters.
23) What is self join and give me an example?
A. Joining two instances of the same table is known as a self join.
SELECT A.EMPNAME, B.EMPNAME FROM EMP A, EMP B WHERE A.MGRID = B.EMPID;
A. Joining two instances of the same table is known as a self join.
SELECT A.EMPNAME, B.EMPNAME FROM EMP A, EMP B WHERE A.MGRID = B.EMPID;
24) How do I eliminate the duplicate rows?A. SQL > delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name );
SQL > delete duplicate_values_field_name dv from table_name ta where rowid < (select min(rowid) from table_name tb where ta.dv = tb.dv );
Example :
empno ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
SQL > delete duplicate_values_field_name dv from table_name ta where rowid < (select min(rowid) from table_name tb where ta.dv = tb.dv );
Example :
empno ename
101 Scott
102 Jiyo
103 Millor
104 Jiyo
105 Smith
Delete ename from emp a where rowid < (select min (rowid) from emp b where a. ename = b.ename);
The output like,
empno ename
101 Scott
102 Millor
103 Jiyo
104 Smith
25) What is the maximum number of triggers, can apply to a single table?empno ename
101 Scott
102 Millor
103 Jiyo
104 Smith
A. 12 Triggers.
26) How to see current user in Oracle?
A. show user;
A. show user;
27) How to change SQL prompt name
A. SQL > set sqlprompt “Test >”
Test >
Test>
A. SQL > set sqlprompt “Test >”
Test >
Test>
28) How to switch to DOS Prompt?
A. host
A. host
29) How do I display row number with records?
A. To achieve this use rownum psedocolumn with query, like SQL > SQL > select rownum, ename from emp;
output :
1 Scott
2 Millor
3 Jiyo
4 Smith
A. To achieve this use rownum psedocolumn with query, like SQL > SQL > select rownum, ename from emp;
output :
1 Scott
2 Millor
3 Jiyo
4 Smith
30) Display the records between two range.
A. select empno, ename from emp where rowid in
( select rowid from emp where rownum <=& upto
minus
select rowid from emp where rownum <&Start);
Enter value for upto : 10
Enter value for Start : 7
ROWNUM EMPNO NAME
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
A. select empno, ename from emp where rowid in
( select rowid from emp where rownum <=& upto
minus
select rowid from emp where rownum <&Start);
Enter value for upto : 10
Enter value for Start : 7
ROWNUM EMPNO NAME
1 7782 CLARK
2 7788 SCOTT
3 7839 KING
4 7844 TURNER
31) I know the nvl function only allows the same data type (i.e. number or char or date Nvl (comm, 0 )), if commission is null then the next “Not Applicable” want to display, instead of blank space. How do I write the query?
A. select nvl ( to_char (comm.),’NA’) from emp;
Output :
NVL (TO_CHAR (COMM),’NA’)
----------------------------------------
NA
300
500
NA
1400
NA
NA
A. select nvl ( to_char (comm.),’NA’) from emp;
Output :
NVL (TO_CHAR (COMM),’NA’)
----------------------------------------
NA
300
500
NA
1400
NA
NA
32) How to view current Oracle version?
A. select banner from v$version;
A. select banner from v$version;
33) Display the number value in Words.
A. SQL > select sal, (to_char ( to_date (sal , ‘j’), ‘jsp’)) from emp;
and the output like,
SAL ( TO_CHAR (TO_DATE (SAL,’J’),’JSP’))
--------------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
A. SQL > select sal, (to_char ( to_date (sal , ‘j’), ‘jsp’)) from emp;
and the output like,
SAL ( TO_CHAR (TO_DATE (SAL,’J’),’JSP’))
--------------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
34) Display Odd / Even number of records.
A. Odd number of records :
select * from emp where (rowid, 1) in (select rowid, mod (rownum, 2) from emp);
1
3
5
Even number or records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);
2
4
6
A. Odd number of records :
select * from emp where (rowid, 1) in (select rowid, mod (rownum, 2) from emp);
1
3
5
Even number or records:
select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp);
2
4
6
35) What are the advantages of Oracle?
A. Portability: Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols.Market 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 that 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 industrial strength support for on-line backup and recovery and good software fault tolerance to disk failure. You can also do point-in-time recovery.
Performance: Speed of ‘tuned’ Oracle Database and application is quite good, even with large databases. Oracle can manage > 100GB databases.
Multiple database support: Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.
Backup and Recovery: Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerance to disk failure. You can also do point-in-time recovery.
Performance: Speed of ‘tuned’ Oracle Database and application is quite good, even with large databases. Oracle can manage > 100GB databases.
Multiple database support: Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.
36) If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted?
A. 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 fails internally ( eg table alreadyexists error) and not syntactically.
A. 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 fails internally ( eg table alreadyexists error) and not syntactically.
37) What are the various types of queries?
A. There are basically five types of queries in oracle
Normal Queries
Sub Queries
Co-related Queries
Nested Queries
Compound Queries
A. There are basically five types of queries in oracle
Normal Queries
Sub Queries
Co-related Queries
Nested Queries
Compound Queries
38) What is the maximum number of columns a table can have?
A. A table can contain maximum 254 columns.
A. A table can contain maximum 254 columns.
39) Is Sysdate a system variable or a system function?
A. System Function.
A. System Function.
40) Consider a sequence whose currval is 1 and gets incremented by 1 using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be?
A. 3
A. 3
41) What is Two-Phase commit?
A. Two-phase commit is mechanisms that guarantee a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phases, a Prepare Phase and a Commit Phase.
A. Two-phase commit is mechanisms that guarantee a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phases, a Prepare Phase and a Commit Phase.
42) What is SGA?
A. The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log bugger and shared pool area.
A. The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log bugger and shared pool area.
43) What is a shared pool?
A. The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL Statements among concurrent users.
A. The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL Statements among concurrent users.
44) Do a view contain data?
A. Views do not contain or store data.
45) What is Integrity constraints?
A. Integrity constraint is a rule that restricts values to a column in a table.
A. Views do not contain or store data.
45) What is Integrity constraints?
A. Integrity constraint is a rule that restricts values to a column in a table.
46) What is a database link?
A. Database link is a named path through which remote databases can be accessed.
A. Database link is a named path through which remote databases can be accessed.
47) What is the advantage of a stored procedure over a database trigger?
A. We have control over the hiring of a stored procedure but we have control over the firing of a trigger.
A. We have control over the hiring of a stored procedure but we have control over the firing of a trigger.
48) Can you increase the size of a table space? How?
A. Yes, by adding datafiles on it.
A. Yes, by adding datafiles on it.
49) Describe oracle database’s physical and logical structure?
A. Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Table spaces, etc.
50) What are the advantages of clusters?
A. Access time reduced for joins.
A. Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Table spaces, etc.
50) What are the advantages of clusters?
A. Access time reduced for joins.
