tag:blogger.com,1999:blog-338858692024-03-13T13:03:12.475-07:00PL SQL Interview QuestionsSQL,PL SQL,Oracle ,SQL Server Interview QuestionsNoddyhttp://www.blogger.com/profile/12432932653472595275noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-33885869.post-67481213156592268482009-12-14T21:28:00.000-08:002009-12-14T21:34:22.817-08:00Oracle Basic Questions<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>Explain oracle architecture?</strong></span><br />
<br />
<br />
<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>What is normalization? what is the advantage of normalization?</strong></span><br />
<span style="background-color: #eeeeee; color: #741b47; font-family: "Courier New", Courier, monospace;"><span style="background-color: #cccccc; color: magenta;">Normalization is the process of removing redundant data from your tables</span><strong> </strong></span><br />
<br />
<strong><span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;">What are the types of SQL Statement ?</span></strong><br />
<span style="color: #274e13;"><span style="background-color: #cccccc; color: magenta; font-family: "Courier New", Courier, monospace;">DDL - Create, Alter..</span></span><br />
<span style="background-color: #cccccc; color: magenta; font-family: "Courier New", Courier, monospace;">DML - Insert, Update ..</span><br />
<br />
<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>What is a join ? Explain the different types of joins ?</strong></span><br />
<br />
<span style="color: #274e13;"><span style="background-color: #cccccc; color: magenta; font-family: "Courier New", Courier, monospace;">Self Join - Joining the table with itself.</span></span><span style="color: #274e13;"></span><br />
<span style="background-color: #cccccc; color: magenta; font-family: "Courier New", Courier, monospace;"></span><br />
<span style="background-color: #cccccc; color: magenta; font-family: "Courier New", Courier, monospace;">Equi Join - Joining two tables by equating two common columns.</span><br />
<span style="background-color: #cccccc; color: magenta; font-family: "Courier New", Courier, monospace;">Non-Equi Join - Joining two tables by equating two common columns.</span><br />
<span style="background-color: #cccccc; color: magenta; font-family: "Courier New", Courier, monospace;">Outer Join - Joining two tables in such a way that query can also retrive rows that do not have corresponding join value in the other table.</span><br />
<br />
<br />
<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>What is difference between TRUNCATE & DELETE ?</strong></span><br />
<br />
<span style="background-color: #cccccc; color: magenta; font-family: "Courier New", Courier, monospace;">Truncate is a DDL Statement, cannot rollback</span><br />
<span style="background-color: #cccccc; color: magenta; font-family: "Courier New", Courier, monospace;">Delete is a DML statement, can issue rollback</span><br />
<br />
<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>What is difference between group functions and single row functions?</strong></span><br />
<br />
<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>What is difference between DECODE and TRANSLATE ?</strong></span><br />
<br />
<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>What is difference between SUBSTR and INSTR </strong></span><br />
<br />
<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>Bitmap and B-tree index?</strong></span><br />
<br />
<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>What is a view ? What are materialized views (snapshots)? </strong></span><br />
<br />
<span style="color: #274e13; font-family: Arial, Helvetica, sans-serif;"><strong>Significance of keys ? primary key , unique key?</strong></span>Noddyhttp://www.blogger.com/profile/12432932653472595275noreply@blogger.com0tag:blogger.com,1999:blog-33885869.post-20487926017149282502007-06-12T03:03:00.000-07:002007-06-12T03:07:02.362-07:00Oracle concepts and questions<span style="font-family:trebuchet ms;">Oracle DBA Questions and Answers<br /><br />1. Explain database instance ?<br /><br />A database instance (server) is a set of memory structures and background processes that access a set of database files.<br />The memory structures are used to store most queried data from database. This helps us to improve database performance by decreasing the amount of I/O performed against data file.<br />The process can be shared by all users.<br /><br />2. What is parallel server?<br />Multiple instances accessing the same database (Only in Multi-CPU environments).<br /><br />3. What is Schema ?<br />The set of objects owned by user account is called the schema<br /><br />4. What is an Index ? How it is implemented in Oracle Database ?<br />An index is a database structure used by the server to have direct access of a row in a table.An index is automatically created when a unique or primary key constraint clause is specified in create table command<br /><br />5. What is a clusters? Explain<br />Group of tables physically stored together because they share common columns and are often used together is called Clusters.<br /><br />6. What is a cluster key ?<br />The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stores only once for multiple tables in the cluster.<br /><br />7. What are the basic element of an oracle Database ?<br />It consists ofone or more data filesone or more control filestwo or more redo log files<br />The database containsMultiple users/schemasone or more rollback segmentsone or more tablespacesData dictionary tables<br />User objects (tables,indexes,views etc)<br />The server that access the database consists of<br />SGA (Database buffer, Dictionary Cache Buffers, redo log buffers,Shared SQL pool)<br />SMON<br />PMON<br />LGWR<br />DBWR<br />ARCH<br />CKPT<br />RECO<br />Dispatcher<br />User process with associated PGA<br /><br />8. What is deadlock ? Explain.<br />Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises.<br />In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.<br />These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.<br /><br />9. What is SGA ?<br />The System Global Area in a Oracle database is the area in memory to facilitates the transfer of information between users. It holds the most recently requested structural information about the database.<br /><br />10. What is Shared SQL pool ?<br />The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.<br /><br />11. What is meant by Program Global Area (PGA) ?<br />It is area in memory that is used by a Single Oracle User process.<br /><br />12. What is a data segment ?<br />Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.<br /><br />13. What are the factors causing the reparsing of SQL statements in SGA ?<br />Due to insufficient Shared SQL pool size<br /><br />Questions on Server concepts<br />1. what are the physical database components<br />2. what are the logical database components<br />3. what is row chaining<br />4. what is the relation between oracle data block ,extents and segments<br />5. how many types of segments are there<br />6. what is temporary segments<br />7. what is redo log<br />8. what is the difference between rollback segments and redo log files<br />9. what is the difference between database buffers and redo log buffer of SGA</span><br /></span>Noddyhttp://www.blogger.com/profile/12432932653472595275noreply@blogger.com0tag:blogger.com,1999:blog-33885869.post-34301393487021200092007-03-07T04:54:00.001-08:002007-06-04T02:36:33.317-07:00SQL / PL SQL QuestionsHow do you convert a date to a string?<br />What is an aggregate function?<br />What is the dual table?<br />What are cursors? Distinguish between implicit and explict cursors?<br />Explain how cursors are used by Oracle?<br />What is PL/SQL? Describe the block structure of PL/SQL?<br />What is a nested subquery?<br />What are the various types of queries ?<br />Which of the following is not a schema object : Index, table, public synonym, trigger and package ?<br />What is dynamic sql in oracle?<br />What is the difference between a package, procedure and function<br />What is the difference between delete, drop and truncating a table<br />How many triggers are supported in Oracle<br />Are you aware of FLASHBACK concept ? What is it?<br />Describe oracle’s logical and physical structure?<br />What is data dictionary<br />What is the use of control files<br />How would store XML data in table ? What data type would be used for the columns?<br />Difference between post and commit?<br />Difference between commit and rollback?<br />What are savepoints?<br />Difference between a View and Synonym<br />How would you fetch system date from oracle<br />What is the difference between primary key, unique key, foreign key?<br />What is the difference between NO DATA FOUND and %NOTFOUND<br />What is cursor for loop<br />What are cursor attributes<br />What will you use in Query : IN or EXISTS? Why<br />Explain the difference between a data block, an extent and a segment.<br />What's the difference between logical and physical I/O?<br />What is an anonymous block?<br />What is a PL/SQL collection?<br />How can you tell if an UPDATE updated no rows<br />How can you tell if a SELECT returned no rows<br /><br /><br />DB concepts:<br /><br />Physical Database Structure<br /><br />1. Datafiles<br />2. Redo log files<br />3. Control files.<br /><br />Logical Structures<br /><br />1. TableSpaces<br />2. DB Schema Objects.<br /><br />Segments,Extents and Data Blocks<br /><br />Oracle background processes<br /><br />1. PMON<br />2.SMON<br />3.DBWR<br />4.LGWR<br />5.ARCH<br />6.RECO<br /><br />A synonym is an alias for a table, view, sequence, create public synonyms that make the base schema object available for general, system-wide use by any database user.<br /><br />Indexes are created to increase the performance of data retrievalNoddyhttp://www.blogger.com/profile/12432932653472595275noreply@blogger.com0tag:blogger.com,1999:blog-33885869.post-1164197091009858102006-11-22T04:03:00.000-08:002009-12-09T02:58:03.210-08:00Database : Oracle, SQL, PL / SQL Technical Interview QuestionsListed here are Interview Questions for Database Interviews.<br />
This mainly lists Oracle, SQL, PL SQL frequently asked questions in technical Interviews<br />
<br />
<blockquote><br />
</blockquote><blockquote><br />
</blockquote><br />
1. What is DDL, DML ? How are they different?<br />
2. What are different types of joins in SQL?<br />
3. How do you select unique rows using SQL?<br />
4. What is the difference between DELETE and TRUNCATE ?<br />
5. What is the difference between a "where" clause and a "having" clause?<br />
6. What is the difference between "procedure" and "function"?<br />
7. What is the difference between "translate" and "replace" ?<br />
8. How to remove duplicate records from a table?<br />
9. What is a "trigger"?<br />
10.What is the difference between "translate" and "replace"?<br />
11.What is a VIEW?<br />
12.What is the difference among "dropping a table", "truncating a table"<br />
and "deleting all records" from a table<br />
13.Explain new feature of 9i Database ? Explain new feature of 10g Database ?<br />
14.How to use DECODE function?<br />
15.What is “Group by” clause?<br />
16.What are cursors and what are the situations you will use them?<br />
17.What default packages are provided by Oracle?<br />
18.How do you debug a oracle procedure /function?<br />
19.How many triggers are available?<br />
20.How are procedures executed?<br />
21.Noddyhttp://www.blogger.com/profile/12432932653472595275noreply@blogger.com0tag:blogger.com,1999:blog-33885869.post-1157451274657369922006-09-05T03:11:00.000-07:002009-12-09T02:58:44.381-08:00Oracle PL/ SQL Interview QuestionsInterview Questions: Oracle, PL SQL<br />
<br />
<blockquote> </blockquote><br />
<br />
• What are the different types of joins?<br />
• What is the difference between procedure and function<br />
• What is group by function<br />
• What is the difference between a where clause and having clause<br />
• What are indexes. Advantages of indexes<br />
• Triggers, how many triggers are available<br />
• What does “i” represent in Oracle8i / 9i<br />
• Difference between primary key and foreign key<br />
• Can we have null in a primary key<br />
• Can we have null in a foreign key, if yes , how many in a table<br />
• What are cursors<br />
• Give example of how SQL tuning can be done<br />
• What is normalization<br />
• Difference between delete and truncate<br />
• Are you aware of water level mark in oracle database? What is it?<br />
• What are views, snapshots and synonyms?<br />
• What value one gets for “Select * from dual”<br />
• Have you used Decode function? Give an example<br />
• Example when inner joins was used?<br />
• Example when outer joins was used?<br />
• Datatypes supported by Oracle<br />
• Can you explain how do index retrieve records from database<br />
• What is commit and rollback<br />
• What is the difference between these 2 queries<br />
o Select count(*) from table<br />
o Select count(1) from table<br />
• Difference between NODATAFOUND and %NOFOUND<br />
• Difference between IN and EXISTS? Which is faster in execution?<br />
• What is referential integrity<br />
• What are constraints<br />
• What are transaction isolation levels?<br />
• What are materialized views?Noddyhttp://www.blogger.com/profile/12432932653472595275noreply@blogger.com0