oracle

Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



1.



What is the difference between Relational and a Hierarchical database?



Answers:



Hierarchical: The hierarchical data model organizes data in a tree structure. There is a hierarchy of parent and child data segments. This structure implies that a record can have repeating information, generally in the child data segments. To create links between these record types, the hierarchical model uses Parent Child Relationships. These are a 1:N mapping between record types. Parent must be created before child (Course --> Student --> Grades). 1:N relation is handled well but N:N is a big problem. One needs to start from root level to reach a child - that too the structure of the tree is known. Relational: Concept of tables, rows and columns is created. Tables are identified by names - without knowing where the data is stored in hard disk. Acces of data is done by comparing value stored to the search critera. A relational database allows the definition of data structures, storage and retrieval operations and integrity constraints. In such a database the data and relations between them are organised in tables. A table is a collection of records and each record in a table contains the same fields. In this model, Values Are Atomic, Each Row is Unique, Column Values Are of the Same Kind, The Sequence of Columns / Rows is Insignificant and Each Column Has a Unique Name.



Experience: 0-2 Category:



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



2.



What is the difference between a Unique Key and a Primary Key?



Answers:



A primary key cannot contain Null, while Unique key can. Primary key creates a Clustered index behind the scenes, while Unique key creates a Non-Clustered Index.



Experience: 0-2 Category:



3.



How many clustered Indexes can be created on one table?



1. 2. 3. 4. 5.



1 8 16 256 Only limitation is set by the given database version



Answers:-



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing







1



Experience:0-2 Category:



4.



What will happen when one creates multiple clustered index on a single table?



Answers:



Multiple clustered indexes cannot be created on a single table. Clustered index is a special type of index that reorders the way records are physically stored in the table - therefore it is impossible to have more than one clustered index on a single table.



Experience:2-5 Category:



5.



What are cursors? Explain different types of cursors. What are the disadvantages of cursors?



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



Answers:



Cursors allow row-by-row processing of the result sets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one roundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors.



Experience: 2-5 Category:



6.



What are different types of constraints? Give examples.



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



Answers:



Referential, Explicit, Primary, Secondary. Examples: Not Null, Primary Key, Foreign Key and Unique key constraints.



Experience:0-2 Category:



7.



What are joins? Explain inner and out joins. What is the difference between a join and union?



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



Answers:



A join combines columns and data from two or more tables (and possibly with itself). The tables are listed in the FROM section of a SELECT statement and the relationship between the two tables may be specified in the WHERE clause. Inner Join: Inner joins return rows based on the key values two tables have in common. They support both ON and USING clauses. Outer Join: Outer join helps in retrieving rows from one (or both) tables that do not match the comparison criteria. Using the pre-Oracle9i syntax, a (+) sign is placed on the side from where more rows are expected. For Oracle 9i syntax, use LEFT OUTER JOIN or RIGHT OUTER JOIN or FULL OUTER JOIN key words. Union: Union combines two queries. It returns all distinct rows for both SELECT statements, or, when ALL is specified, all rows regardless of duplication.



Experience:0-2 Category:



8.



What is a VIEW? What is it used for?



Answers:



VIEW can be seen as a stored query that can be accessed as if it was a table. It can be used to give restricted access to only some users / columns of a table or also to give permission only for SELECT rather than UPDATE and / or DELETE



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



on a table.



Experience:0-2 Category:



9.



Is there a limit on number of columns, rows, indexes that can be created on a table?



Answers:



Number of columns: 1000 Rows: None as imposed by Oracle Indexes: None in terms of number but can be up to 40% of database block size minus overhead.



Experience:2-5 Category:



10.



Why do we CREATE OR REPLACE a procedure and not DROP and CREATE?



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



Answers:



The benefit of using CREATE OR REPLACE v/s DROP the procedure and then once again CREATE it is that the EXECUTE privilege grants priviously made on the procedure will remain in place.



Experience:2-5 Category:



11.



What do you understand by DDL and DML statements? Do they require explicit commit? Check ALL the correct statements.



1. 2. 3. 4. 5. 6. 7. 8.



DDL is Database Distribution Language DDL is used to create database objects DDL is Data Definition Language DML is Distributed Meta Language DML is Data Manipulation Language DDL requires explicit commit DML requires explicit commit Neither DDL nor DML requires explicit commit



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



Answers:



DDL is used to create database objects







DDL is Data Definition Language







DML is Data Manipulation Language







DML requires explicit commit



Experience:0-2 Category:



12.



What is the benefit of using a stored procedure over normal SQL queries?



Answers:



1. The Stored procedure is pre-compiled, thus, is faster than normal SQL queries. 2. It is stored in the database and can be used / called repeatedly to execute the same code - with / without changing parameters. 3. Same code can be used by multiple applications. 4. You can control access privileges to certain tables - by



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



creating procedures logging in as one user and giving only the execute privileges to the second user. We are assuming "definer rights" and not "invoker rights" as default.



Experience:2-5 Category:



13.



What is a trigger? Give situations where it can be used.



Answers:



A trigger is a special type of stored procedure that is fired on an event-driven basis rather than by a direct call. It can be used :- 1)To maintain data integrity rules that extend beyond simple referential integrity 2)To implement a referential action, such as cascading deletes 3)To maintain an audit record of changes 4)To invoke an external action, such as beginning a reorder process if inventory falls below a certain level or sending e-mail or a pager notification to someone who needs to perform an action because of data changes



Experience:0-2 Category:



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



14.



How will you decide if an index should be created on a column or not? What are the possible reasons when an index can be an overhead?



Answers:



Index should be created on columns on which most of the queries are based. These columns should have as many distinct values as possible – columns having Yes/No flags are bad candidates. As a thumb rule, Index should be created where a query is expected to return less than 25% of the total records or else, full table scan might be better. If there are more inserts / updates / deletes, index will cause more overhead. More index will also need more space to store – however disk space these days is a non-issue.



Experience:2-5 Category:



15.



How will you get the nth highest salary from EMP table which has NAME and SAL as two columns?



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



Answers:



SELECT DISTINCT (A.SAL) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (B.SAL)) FROM EMP B WHERE A.SAL 10000; WHERE clause eliminates rows before a GROUP BY is done and HAVING eliminates rows after the GROUP BY has been done.



Experience:0-2 Category:



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



18.



Which of the following statements contain valid code to use a SQL GROUP function?



1. 2. 3. 4. 5.



SELECT COL1,AVG(COL2) FROM TAB1 GROUP BY COL2 SELECT COL1,MIN(COL2) FROM TAB1 GROUP BY COL2 SELECT COL1,MAX(COL2) FROM TAB1 GROUP BY COL2 SELECT COL1,COUNT(COL2) FROM TAB1 GROUP BY COL2 NONE OF THE ABOVE



Answers:



NONE OF THE ABOVE



Experience:2-5 Category:



19.



For an EMPLOYEE table that has a columns EMP_NO NUMBER(10) and SAL NUMBER(10,2), what will the following statement return? SELECT EMP_NO, NVL(SAL, "NO SALARY") FROM EMPLOYEE



1. 2. 3. 4. 5.



It will return rows for all employees who have NO SALARY It will return rows for all employees who have non-zero salary It will return only EMP_NO where salary is zero or null It will retun the employee number and salary - where salary is null, it will return "NO SALARY" The query has an error - it will fail



Answers:



The query has an error - it will fail



Experience:2-5



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



Category:



20.



What are EXCEPTIONS in PL/SQL? How are they different from ERRORS?



Answers:



Exceptions provide an excellent mechanism to handle run-time errors. Taking example of Oracle, some exceptions are predefined - like NO_DATA_FOUND, INVALID_NUMBER etc. When a SQL statement executes, one can check if any of these exceptions have occured by using appropriate statements in the EXCEPTION block. Users can define their own exceptions also. Based on certain conditions, some specific exceptions can be raised - provided they have been defined in the Declare section. Exceptions basically differ from errors in two ways - (i) Errors are typically known situations and are handled using program logic where as Exceptions are run-time situations that need to be handled and (ii) one cannot define run-time errors the way one can do in case of exceptions.



Experience:0-2 Category:



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



21.



What is the use of DECODE? Give an example.



Answers:



DECODE is like a „case‟ / „switch‟ statement in other languages. It allows us to look at the value of a variable and return same or different value depending on the value contained. For example, DECODE(A, “1”, “YES”, “2”, “NO”, “NOT DEFINED”) statement will check the value of A and if it is 1, it would return YES, if it is 2, it would return NO or else it would return NOT DEFINED.



Experience:0-2 Category:



1. Can one use dynamic SQL statements from PL/SQL? Yes Exp: 2-5 2. What is the difference between %TYPE and %ROWTYPE?

%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. %TYPE is used to declare a field with the same type as that of a specified table's column. Exp: 2-5



3.How does one get the value of a sequence into a PL/SQL variable?

one can use embedded SQL statements to obtain sequence values: select sq_sequence.NEXTVAL into :i from dual;



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



Can we know the size of an existing package or procedure:?

Yes, You can run the following select statement to query the size of an existing package or procedure: SQL> select * from dba_object_size where name = 'procedure_name';



Exp:2-5 4.Can one drop a column from a table? Yes Exp: 0-2 5.Can one rename a column in a table? Yes Exp: 0-2 6.Can a column be added in the middle of a table? No Exp:0-2 7.How does one drop/ rename a column in a table? SQL> create table t2 as select >specific columns> from t1; SQL> drop table t1;

SQL> rename t2 to t1;



Exp: 2-5 8.Can I print inside a PL/SQL program? Yes, there is a standard package DBMS_OUTPUT that lets you do the trick. Exp: 2-5 9.Is it possible to write a PL/SQL procedure that takes a table name as input and does something with that table? No Exp: 2-5 10.How do I stop the "X rows selected" and "PL/SQL procedure successfully completed." messages appearing?

Use SET FEEDBACK OFF.



Exp: 2-5 11.How do I use a semicolon in a text string?

turn off recognition of semicolons as the statement terminator:



set sqlterminator off insert into mytable values ('begin myprocedure();



Sub Categories: end') /



Oracle Sybase DB2



SQL Server



Data Warehousing



When SQLTERMINATOR is OFF you must use a slash to execute or the BLOCKTERMINATOR (e.g. a period) to stop entering statements.



Exp: 2-5

12.How can system global area (SGA) memory structure sized? Using SGA_MAX_SIZE.



Exp: 0-2 CAT:DBA

13.What is data dictionary cache? It is a collection of the most recently used definitions in the database.



Exp: 2-5 CAT:DBA

14.What is Large Pool? It is an optional area of memory in the SGA configured only in shared server environment.



Exp: 2-5 CAT:DBA

15.Which are the Database buffer caches in the following? 1. 2. 3. 4. 5. Ans: 4 DB_CACHE_SIZE DB_DEEP_CACHE_SIZE DB_RECYCLE_CACHE_SIZE All Above None Above



Exp: 2-5 Cat: DBA

16.The oracle database configuration assistant allows you to: 1. 2. 3. 4. 5. Ans: 4 create a database configure database points delete a databasemanage template All above None above



Exp: 2-5 CAT: DBA

17.When a database is created the user SYS and SYSTEM are created By DBA separately Automatically and given the DBA role Depends upon option when creating the database. Answer: 2



Exp: 2-5



Sub Categories:



Oracle Sybase DB2



SQL Server



Data Warehousing



18.SQL*plus is an Oracle that 1. 2. 3. 4. 5. Ans: 4 Provides the capability to interact with and manipulate the database. Provide the ability to startup and shutdown the database. Subset of the standard SQL language with specific add-ons All above None above



Exp: 0-2

19.Can a database be opened as read-only? 1. 2. Yes No



Ans: Yes



Exp: 2-5

20.A read-only database 1. 2. 3. 4. 5. can be used to Execute queries. Can take tablespaces Cannot perform recovery of offline data files and tablespaces. All above None above



Answer: 1



Exp: 2-5

21.The DBA role 1. Includes sysdba and sysoper privileges 2. Does not include sysdba and sysoper privileges 3. Includes only sysdba privileges 4. Includes only sysoper privileges Ans: 2



Exp: 2-5

22.Which constraints are allowed on views? 1. Unique key 2. Primary key 3. Foreign key 4. All above 5. None above Ans: None Above



Exp: 2-5

23. External tables 1. Are read only tables 2. Data can be inserted, deleted, updated or merged. 3. Indexes can be created Ans: 1



Exp: 0-2

24.In inner join 1. 2. Only the matched records are returned. All the records from both tables are returned.



Sub Categories:

Ans: 1



Oracle Sybase DB2



SQL Server



Data Warehousing



Exp: 0-2

25.In LEFT outer join 1. 2. Ans: 1 A join between two tables that returns the results of inner join as well as unmatched rows from the left table is considered A join between two tables that returns the results of inner join as well as unmatched rows from the right table is considered



Exp: 0-2

26.In FULL outer join 1. Return the results of LEFT and RIGHT outer joins 2. Returns the results of LEFT and RIGHT outer join as well as INNER join 3. Returns the result of INNER join. 4. None of above Ans: 2



Exp: 0-2 27.What versions of the database will SQL*Plus 9.2 work with? Oracle 8.0 or later Exp: 2-5