Dbt Interview Questions
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Dbt Interview Questions as PDF for free.
More details
- Words: 7,988
- Pages: 58
Re: diff b/w function and procedure? Answer #1 function must return value whereas procedure may or maynot return value Call to Function can be encounter by using select statement where as procedure can not be call in select statement Re: diff b/w function and procedure? Answer #2 A procedure a subprogram used to perform a specific action whereas a function is a subprogram which is used to compute a value,in procedure we can use parameters(IN,OUT,IN OUT) but in function we cant Re: What is TABLE SPACE? Answer #1 Database is logically divided into three tablespaces. once creates the database then automatically create tablespace is also called SYSTEM tablepace.tablespace contain dta dictionary of all data. Re: State the difference between implict and explict cursor's? Answer #1 Implicit Cursor are declared and used by the oracle internally. whereas the explicit cursors are declared and used by the user. more over implicitly cursors are no need to declare oracle creates and process and closes autometically. the
explicit cursor should be declared and closed by the
user.
More over Explicit Cursors were used to retrieve values from two or more rows. Re: State the difference between implict and explict cursor's? Answer #2 The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. During the processing of an implicit cursor,Oracle automatically performs the OPEN, FETCH, and CLOSE operations. Where as in explicit cursors,the process of its working is done in 4 steps namely DECLARE a cursor,OPEN a cursor, FETCH from cursor and CLOSE a cursor. Re: State the difference between implict and explict cursor's? Answer #3 IMPLICT CURSOR:- Automatically porvide by oracle which perform DML statements. queries returns only one row. EXPLICT CURSOR:- Defined by user. queries returns more than rows. Re: State the difference between implict and explict cursor's? Answer #4 Explicit Cursor:-We are not able to Handle NO_DATA_FOUND
Exception. Implicit Cursor:-We are able to Handle NO_DATA_FOUND Exception. Re: Types of locks in database ? Answer #1 Exclusive locks, Share locks Re: Types of locks in database ? Answer #2 2 types 1. Implicit locks: oracle automatically locks the rows whenever user performs DML operations. 2. Explicit locks: provided by user. 2 tpyes a) Row level locks: used to lock selected rows of table.It is imposed by "for update" clause in select. b) Table level locks: used to lock compleate table. 3 models 1) share mode 2) share update 3) exlcusive mode
Question What is Distributed database ?
Rank
Answer Posted By
Question Submitted By :: Guest I also faced this Question!! database is a network
Answer A distributed of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database
© ALL Interview .com
can be simultaneously accessed and modified. Question What is cursor and where do you use them? Rank
Answer Posted By
Question Submitted By :: Mihir This Interview Question Asked @ eBay © ALL I also faced this Question!! Interview . com Answer cursor is temperory block of memory which holds the 0 Guest data as temperory in buffer
Answer Cursor is an named private area called active set which is used to process the multiple rows returned by the query.... cursors are mainly used in procedure/package/function when the select qurey in procedure/package/function returns more than one value....
0 Rajesh
if their is any issue mail me @ [email protected],[email protected]
Ques Distributed database is a database located at different places and include data from common operational and user database and also from data generated and used only at the user's site.
A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
Re: WHAT IS INDEXING? Answer A database index is a data 0 # 1 structure that improves the speed of operations in a table. Indices can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
Savithri
Re: WHAT IS INDEXING? Answer It Is One Of The Database # 2 Object,Which Automatically Created While Creating The Primary Keys Those Gives More Performance
What are the the different Data Question Manipulation Language statements are? Question Submitted By :: Guest I also faced this Question!! Rank
Answer Posted By
Re: What are the the different Data Manipulation Language statements are? Answer INSERT 0 Orawhiz UPDATE #1 SELECT
Re: What are the the different Data Manipulation Language statements are? Answer insert, update,delete,merge # 2 (on 9i) .
Re: What are the different Sequence of events takes place while starting a Database ? Answer Instance started, Database 0 Orawhiz # 1 mounted & Database opened
Re: What is a trigger? Answer A piece of logic written in # 1 PL/SQL Executed at the arrival of a SQL*FORMS event Re: what are the differences you find when working with oracle 91 and sql server 2005? Answer There are so many Difference 0 Daid in oracle 9i and Sql #1 Kumar Server2005 some example are as follows: in Oracle In sqlserver2005 -----------------------------------------------1.Schema
Database 2.Block Pages 3.Segment N/A 4.Clusters N/A 5.Index(Type) Only One Type is Present 6.Materialized Views N/A 7.Operators N/A 8.Packages N/A 9.Hierarchical Operators N/A 10. In case of partition table in oracle we have Three option and easely implemented but in Sqlserver It is complx work Etc......................... Re: What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ? Answer Complete database recovery 0 Guest from disk failure is #1 possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode. Re: What is relational database ? Give me an example ! Answer A relational database is a
#1
database that conforms to the relational model, and refers to a database's data and schema
1) In computer programming, a schema (pronounced SKEE-mah) is the
organization or structure for a database. The activity of data modeling leads to a schema. (The plural form is schemata. The term is from a Greek word for "form" or "figure." Another word from the same source is "schematic.") The term is used in discussing both relational databases and object-oriented databases. The term sometimes seems to refer to a visualization of a structure and sometimes to a formal text-oriented description. Re: how to display last 5 letters from a name (take emp table ename ) Answer select right(ename,5) # 1 as name from emp Re: It's Urgent? How to IMPORT .xls & .txt file into ORACLE? Answer open the TOAD and run # 1 the QUERY by using the SELECT statement. after that click the right button and select the SAVE AS button, in that select .xls Radio button and enter the file name automatically data store in that file Re: query to find the maximum no persons with same age(age colomn) from emp table Answer select max_age,age # 1 from (select count(age) max_age,age from emp group by age order
by max_age desc) where rownum < 2 Re: What is a Data Segment ? Answer Each Non-clustered table # 1 has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment. Re: What is an Extent ? Answer An Extent is a specific # 1 number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information Re: What is Data Block ? Answer ORACLE database's data is # 1 stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. Re: which clause we are not used in where clause? Answer Group by clause #1 Re: What is the use of Redo Log Information ? Answer The Information in a redo # 1 log file is used only to
recover the database from a system or media failure prevents database data from being written to a database's data files Re: When does a Transaction end ? Answer when ever commit or # 1 rollback statement occurred in the end of trasaction Re: What is a View ? Answer View r the virtual table,it # 3 show the selected fields only... it is used for the security purpose Re: which sql command we can use to get a print out from oracle? Answer set autoprint off/on 3 Jamalvalli # 1 it is Sql*plus Command
Re: which sql command we can use to get a print out from oracle? Answer set autoprint on; # 2 will provide the command for printing. Re: What is a Sequence ? Answer Sequence is used to provide # 1 unique values to database object. Re: What is dictionary cache ? Answer Dictionary cache is
0
Sawant
#1
information about the databse objects stored in a data dictionary table.
Re: What is dictionary cache ? Answer Once the database server # 2 has accessed the table from the datafile , it places that information in the data-dictionary cache in shared memory. Re: What are the Characteristics of Data Files ? Answer A data file can be # 1 associated with only one database.Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace. Re: Explain the relationship among Database, Tablespace and Data file? Answer Each databases logically # 1 divided into one or more tablespaces One or more data files are explicitly created for each tablespace. Re: what is exact difference between drop and truncate table. Answer Drop Statement deletes the # 1 Table structure from the database. Truncate statement will
delete the entire data from Table and keeps the Table structure as it is in the DB. Re: How can we Update a table with out using UPDATE command? Answer Use merge command. If the # 1 rows already exist then it will update it with new data if not it would insert the new data. Re: Anybody can tell me, how do we find second largest emp salary from emp table.. Thanks in advance ... Answer select max(sal) from emp # 1 where sal < (select max(sal) from emp) select e.sal from emp e where &2>(select max(count{a.sal}) from emp a where a.sal > e.sal order by desc ) I would say that Raj has given the correct answer in general. In most cases the interviewer restricts the parameter to be written in the query as it might effect the performance. Select TOP 1 salary from (select Distinct TOP 2 salary from employee orderby salary desc) Re: What are main difference between Stored Procedure and Functions.
Answer The Main difference between # 1 stored procedure and Function is SP's can return the value or not, but Functions should return the value. And We can use the function within the Queries, but sp's we won't able to use it in the queries. We can implicitely execute the sp's. But function's we can't. Re: What are main difference between Stored Procedure and Functions. Answer stored procedure : It is Pre # 2 Compiled Code(PCode) it is stored in Database. we can reuse without compile. Function: It need compile whenever it will run. Re: find out first highest salary? Answer select max(sal) from emp; #1 Re: find out first highest salary? Answer select * from emp where &n# 2 1=(select count(distinct sal) from emp e where emp.sal<e.sal); For example:If you enter n=1,it will display first highest salary
if you enter n=2,it will display second highest salary ...... if you enter n=nth,it will display nth salary select * from emp x where 1=(select count(distinct sal) from emp y where x.sal<=y.sal); query to find nth sal select * from emp x where &n=(select count(distinct sal) from emp y where x.sal<=y.sal); Re: What are joins..how many types of joins are there? Answer Joins are used to fetch a query on more # 1 than one table. You can broadly catagorize joins in to four types. 1. Equi Join 2.Non Equijoin 3.outer joins 4.self joins Re: What are joins..how many types of joins are there? Answer In Oracle we can catogrise joins in # 7 following ways (1) Equi Join (Query having joining condition) - Natural Join - Join Using - Join ON (2) Non Equi Join or Cartesian Join ( without using joining condition) (3)Outer Join - left outer join - right outer join
- full outer join (4) self Join or Theta Join (5) Cross Join (6) Inner Join Re: What are Clusters ? Answer Cluster is amechanism which is used to # 1 bind data together such that it will improve performance while retreiving information from a table. Re: what is the use of ondelete cascade? Answer on delete cascade is used to delete the # 1 rows/constraints which are in integrity/foreign key constraints. Re: Can we create more than one index on particular column? Answer No # 1 But we can create composite index including that particular column with other columns. Re: what is the bitmap index? Answer bitmap Index is Used mainly in decision # 1 support systems because it returns value in bit like 0 and 1. Example CREATE BITMAP JOIN INDEX ON () FROM WHERE <join_condition> Re: Maximum how many triggers can be updated in table ? Answer 12 0 #1
Kavitha
Re: Maximum how many triggers can be updated in table ? Answer System Privileges Related To Table # 2 Triggers create trigger create any trigger administer database trigger alter any trigger drop any trigger Table Trigger Firing Options -- before constraints are applied BEFORE INSERT BEFORE UPDATE BEFORE DELETE -- after constraints are applied AFTER INSERT AFTER UPDATE AFTER DELETE Re: what is the difference between UNION AND UNIONALL Answer UNION 0 Vinod The UNION command is used to select #1 Singh related information Kushwah from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. Note: With UNION, only distinct values are selected. SQL Statement 1 UNION SQL Statement 2 Employees_Norway: E_ID E_Name
01 02 03 04
Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari
Employees_USA: E_ID E_Name 01 Turner, Sally 02 Kent, Clark 03 Svendson, Stephen 04 Scott, Stephen -----------------------------------------------------------------------------Using the UNION Command Example List all different employee names in Norway and USA: SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA Result E_Name Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari Turner, Sally Kent, Clark Scott, Stephen Note: This command cannot be used to list all employees in
Norway and USA. In the example above we have two employees with equal names, and only one of them is listed. The UNION command only selects distinct values. UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. SQL Statement 1 UNION ALL SQL Statement 2 -----------------------------------------------------------------------------Using the UNION ALL Command Example List all employees in Norway and USA: SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA Result E_Name Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari Turner, Sally Kent, Clark Svendson, Stephen Scott, Stephen
Re: what is the difference between UNION AND UNIONALL Answer UNION provides only distinct values # 2 as output whereas UNION ALL provides all values. So UNION ALL seems to be faster than UNION. Re: ex. one table is having 1 column with 10 records , then how to display all the values in row wise ? Answer it will need a cursor. 0 Ruchi #1 Varma begin for cur1 in (select a from Name) loop dbms_output.put(cur1.a); end loop; end; dbms_output.put -> displays a chr without putting a new line
Re: ex. one table is having 1 column with 10 records , then how to display all the values in row wise ? Answer create table x (col1 number(4)); # 2 insert into x value(1); .... ... insert into x value(10); /* upto 10 rows then select substr(max(decode(rownum,1,col1,0)),1,2), substr(max(decode(rownum,2,col1,0)),1,2), substr(max(decode(rownum,3,col1,0)),1,2), substr(max(decode(rownum,4,col1,0)),1,2), substr(max(decode(rownum,5,col1,0)),1,2),
substr(max(decode(rownum,6,col1,0)),1,2), substr(max(decode(rownum,7,col1,0)),1,2), substr(max(decode(rownum,8,col1,0)),1,2), substr(max(decode(rownum,9,col1,0)),1,2), substr(max(decode(rownum,10,col1,0)),1,2) from x (where substr only to reduce display size ) Re: Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Otherwise insert new record in the C1 And C2 (Using Procedure) Answer select * from x; 0 #1 C1 C2 ----- ---------1 2 3 4 5 6 7 8 9 10 1 create or replace procedure updt_x is 2 cnt number(4); 3 begin 4 select count(1) into cnt from x where c1 is not null; 5 if cnt > 0 then 6 update x 7 set c2=10; 8 else 9 insert into x
Dinesh A.
10 values(1,2); 11 end if; 12 commit; 13* end; SQL> execute updt_x; PL/SQL procedure successfully completed. SQL> select * from x; C1 C2 ---------- ---------1 10 2 10 3 10 4 10 5 10 6 10 7 10 8 10 9 10 10 10 10 rows selected. SQL> delete from x; 10 rows deleted. SQL> commit; PL/SQL procedure successfully completed. SQL> select * from x; C1
C2
---------- ---------1 2
Re: Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Otherwise insert new record in the C1 And C2 (Using Procedure) Answer Hi Answer to Your question IS #2 create or replace procedure samp(cname IN sample.ename%type, cno IN sample.eno%type) AS ccount number; begin select count(*) INTO ccount from sample; IF ccount = 0 then update sample set ename = cname; else insert into sample values(cname, cno); END IF; END; Sample Table is ENAME ENO 100 use oracle's new feature called upsert : merge t1 using (select * t1) t2 on (t1.col1=t2.col1) when matched then update set col2='value' when not matched then insert into (col1, col2) values ('val1', 'val2');
1) In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships. There are three main normal forms, each with increasing levels of normalization: First Normal Form (1NF): Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field. Second Normal Form (2NF): Each field in a table that is not a determiner of the contents of another field must itself be a function of the other fields in the table. Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflect in all tables that link to the birthdate table. There are additional normalization levels, such as Boyce Codd Normal Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into so many different tables. (2) In data Re: Who i will insert 1 lacks record in a Database table Answer The qs is not quite # 1 clear.... Still I suppose u wana insert 1 lach record in database table.
0
Milan
It is quite simple.... Just write a stored procedure for insert of 1 lakh records,compile it and run... U will b get 1 lakh records inserted in a single stroke... Re: how many columns can a plsql table have Answer total 250 , 249 non clustered and # 2 1 clustered Re: can a view be updated? Answer yes it can be updated ....but # 1 there are certain restrictions in it. For eg: 1.a view created from nonupdatable view cant be updated 2.when it is having having , groupby,orderby it cant be updated.etc Re: what r routers? Answer An electronic device connected with # 1 various nodes in various network. Re: what are the database links ? Answer Database Link is a named path # 1 through which a remote database can be accessed. Re: how do u know the total no of rows in a table? Answer Select count(*) from table
#1 Re: what is RAID technology?which is better RAID 0+1 OR RAID 5? Answer RAID — which stands for Redundant Array # 1 of Inexpensive Disks. A RAID distributes data across several physical disks which look to the operating system and the user like a single disk. Several different arrangements are possible. We assume here that all the disks are of the same capacity, as is usual. We have only 3 basic RAID levels : RAID 0: Provides improved performance and additional storage but no fault tolerance. Any disk failure destroys the array, which becomes more likely with more disks in the array. A single disk failure destroys the entire array because when data is written to a RAID 0 drive, the data is broken into fragments. The number of fragments is dictated by the number of disks in the drive. The fragments are written to their respective disks simultaneously on the same sector. This allows smaller sections of the entire chunk of data to be read off the drive in parallel, giving this type of arrangement huge bandwidth. RAID 0 does not implement error checking so any error is unrecoverable. More disks in the
array means higher bandwidth, but greater risk of data loss. RAID 1: Mirrored set without parity. Provides fault tolerance from disk errors and failure of all but one of the drives. Increased read performance occurs when using a multi-threaded operating system that supports split seeks, very small performance reduction when writing. Array continues to operate so long as at least one drive is functioning. Using RAID 1 with a separate controller for each disk is sometimes called duplexing. RAID 5: Striped set with distributed parity. Distributed parity requires all drives but one to be present to operate; drive failure requires replacement, but the array is not destroyed by a single drive failure. Upon drive failure, any subsequent reads can be calculated from the distributed parity such that the drive failure is masked from the end user. The array will have data loss in the event of a second drive failure and is vulnerable until the data that was on the failed drive is rebuilt onto a replacement drive. Rest of RAID levels are the combination
of these basic raid levels. which is better RAID 0+1 OR RAID 5? ........... good question....... RAID 0+1: striped sets in a mirrored set (minimum four disks; even number of disks) provides fault tolerance and improved performance but increases complexity. Hence, Raid 5 is definitely better because if there is a failure of even 1 disk in RAID 0+1.... the situation is same as it is in raid 5 but, the min. no. of disks utilized in raid 0+1 is 4 and on the other hand in raid 5, the similar fault tolerance is given with a min of 3 HDDs. Seniors kindly correct me if I am wrong. Re: which statement is running fastly ie insert or delete? Answer Its definitely Delete. # 4 Becuase When Delete operation is being performed then Oracle doesnot actualy permanently remove the data from data block but rather marks that particular data block as unusable. Whereas when concerned to Insert Oracle needs to insert the
new values into Datablocks. Re: What are Schema Objects ? Answer Schema objects are the logical structures # 1 that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links. Re: What is Parallel Server ? instances accessing Answer Multiple # 1 same database (Only In Multi-CPU environments)
the
Re: Can we create index on views? Answer An index cannot be defined on a view. # 1 Because view is virtual table, which consists of a subset of columns from one more tables. The Restrictions imposed on views are as follows: 1. A view can be created only in the current database. 2. A view can be created only if there is a SELECT permission on its base table 3. A Trigger or an Index cannot be defined on a view. 4. A view cannot be derive its data from temporary tables. 5. The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.
Re: What is Public Database Link ? Answer Public database link is created # 1 for the special user group PUBLIC.
0
Orawhiz
A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition. Re: Tell me New Feature of Oracle 10g? Answer 10g stands for Grid computing: # 2 read oracle link below for all its features : http://www.oracle.com/technology/pub/articles/10gdba/index.html http://www.oracle.qassociates.co.uk/oracle-10g-features.htm Re: what is the output of select * from emp where null=null & select * from emp where 1=1 Answer select * from emp where null=null: 2 Anitha # 1 will not return any rows in the table since two null values are not always same. select * from emp where 1=1 : will return all the rows in the table because 1 is always equal to 1. Re: how to truncate date and get only time part 9:20:00 Answer select,to_char 0 # 1 (,'fmdd month yyyy fmhh:MI:ss am') from where condition;
Saisivakumar [Kesdee]
Note: i have given for both date and time, pls use as u want. Re: what is primary key and foreign key when and where have to use Answer primary key has a unique & it is 0 # 1 only one in a databasewhile fkey is not unique. it is based on primary key
Prashant
HI Primary key = (Not Null + Unique). Foreign key = (For Referential table access). primary key has unique value, it dostnt contain null values and duplicate values. foregin key is primary key of another table
Re: What is difference between UNIQUE constraint and PRIMARY KEY constraint ? Answer The Primarykey Constraint= # 1 unique + Not Null constraints. For the Unique the meaning it self it is telling unique value. won't accept duplicate values. When ever you create a primary key constraint on perticular column in a table it creates clustered index by default, it cannot allow null values and a table having only
one primary key constraint. But in the case of Unique Key constraint, it allow only one null value, it creates nonclustered index by default and a table having multiple unique key constraints.
Re: what are Triggers? Triggers are basically used to implement business rules. Triggers are also similar to stored procedures.The difference is that it can be activated when data is added or edited or deleted from a table in a database.
Re: what are Triggers? Answer Triggers are the database # 2 objects (Like Procedures) which will be executed by the Database itself and cannot be excuted by the user explicitly. The trigger can be written to execute while inserting/ updating/ deleting a row. Two types of triiger ROW LEVEL TRIGGER
=============== Row Level triggers are FOR EACH ROW, that is, the trigger is activated for each row that is inserted/updated/deleted. STATEMENT LEVEL TRIGGER ===================== Statement level triggers will execute the action specified only once. (Will not consider the no. of rows being inserted/updated/deleted) Re: What are the Limitations of a CHECK Constraint ? Answer The check consttraint should # 1 be with in the two boundaries (min value to max value). Re: What are the Limitations of a CHECK Constraint ? Answer It is used to avoid invalid # 2 and inconsistence data into the data base table. For example if you specify like this sal int Check(sal>=1000) and trying to insert value below 1000 in the sal column,they won't accept you.That means it restrict the data in the database table.
Re: Explain 1st, 2nd, 3rd normalization form of data base Answer HI #1
First Normalisation is "Primary Key" Second Normalisation is " Composite Key" bye Re: Explain 1st, 2nd, 3rd normalization form of data base Answer 1st normal form: first # 2 identify the repeating group of fields n group them into separate table 2nd normal: it should be in 1st normal form and check all fields are dependent on primary key 3nd normal form: it should be in 2nd normal form and check all the fields r independent of any other non-key attribute ie removes the fields that dependent on non-key 1st normal: first identify repeating fields and group them another table 2nd normal: it should be in 1st normal form and identify that all fields all dependent on primary key. 3nd normal: it should be in 2nd normal form and check all
fields all indpendent of other non-key attribute Re: difference between oracle8i and oracle9i Answer merge is a command which is # 1 used to both insert and delete. timestamp datatype is introduced. max no of columns in 8i is 256 where as in 9i we can have 999. on delete set null supports only in 9i. by using this, when we delete the parent record the child records are replaced with null values Re: what is difference between where clause and having clause? Answer in 'where' clause every # 1 records filtered based on where in 'having' is with aggregate records (group by functions ) Re: what is difference between where clause and having clause? Answer RISTRICT GROUP BY FUNCTION BY # 2 HAVING. RESTRICT NORMAL QUERY BY WHERE. YOU CAN PUT WHERE AND HAVING IN A QUERY Re: What are triggers?Where are they used? Answer trigger is autorun query. for # 1 example if you perform some
operation that time some operation(insert,delete) shuld be execute automatically that time you have to use trigger Re: What are triggers?Where are they used? Answer triggers are the named pl/sql # 2 blocks which are executed automatically at the specified events.the event at which triggers are executed is called as the triggering event.there are 3 types of triggers (i) dml triggers: these triggers are executed either before or after any dml events(i.e insert or update or delete) (ii) ddl triggers: these triggers are executed either before or after any ddl events(i.e create or alter or rename or truncate or drop) (iii) database triggers these trigger are executed either before or after any database events(i.e logon or logoff or startup or shutdown) database triggers can be created only by user having dba privileges. using triggers we can perform the following operations (i) auto managing of data (ii) auditing of data (iii) auditing of events (iv)defining of validations
Re: What is Index and different types of Indexes? Answer Index is used to solve some # 1 query fast. we have 2 types of cursors. 1 Bitmap Index ( used only on Low cardinality columns) 2. Btree Index ( used only for High Cardinality of columns) Re: What is Index and different types of Indexes? Answer Index is used to fetch the # 2 data in a reliable and fast manner and the types of index avalilable are clustered index and non clustered index.
Re: What is Index and different types of Indexes? Answer indexes is a process of # 3 creating a structure from a database table from a single or multiple compostion (atleast) of attributes for efficient data retreival. some database even allow to create indexes out of expressions as well. there are different types of indexes based on the implementation methodology: b Trees, b+ trees, bitmap indexes, hash index etc . infact
in some database the index file is different from the database file and hence one can write one's plug in for a special kind of index . clustered and non-clustered is the kind of architecture that is used to define the index methodology as noted earlier. indexes are boon when used in retreivals and they are doomed when used in situations where there's more write than read. this is so because every new write in the table also implies corresponding modofication in the indexes as created for the table. Re: What is Index and different types of Indexes? Answer PHYSICAL OR LOGICAL LISTING # 4 OF COLUMN TO SPEEDUP THE SEARCH IN A TABLE OR VIEW IS CALLED INDEX. FUNCTION BASED INDEX HASH INDEX BITMAP INDEX COMPOSIT INDEX B TREE INDEX Re: Can Multiple instances be run on Single Machine??? Answer Yes, you can run RAC (Real # 2 Application Clusters) that consist of multiple instances
accessing the same database. You can also run the same and different versions of Oracle running on a single machine each of course accessing different databases. Re: what is the difference between primary key & foreign key? Answer Primary keys enforce 1 Ramprakash entity integrity by #1 uniquely identifying entity instances. Foreign keys enforce referential integrity by completing an association between two entities.
Re: what is the difference between primary key & foreign key? Answer #2
Re: what is the difference between primary key & foreign key? Answer #3 Primary key is unique primary key is not NULL and foreign key is NULL foreign key reference as Primary key in another table
Answer #2
Guest
Re: what is the difference between primary key & foreign key? Answer #4 Primary Key is unique Key entire the table which is also NOT NULL where as foriegn key is the null & which references the primary key .
and
0 Muneer
Re: what is the difference between primary key & foreign key? Answer #5 primary key is the parent and foreign is the child. 0 Guest
Answer #2 Re: what is the difference between primary key & foreign key? Answer #6 primary key is used to identify a row and it doesnot allow null values. It avoids duplication of rows. whereas foreign key refers to a column or combination of columns included in the defenition of referential integrity. 0 Guest
Re: what is the difference between primary key & foreign key? Answer #7 In the context of relational databases, a foreign key is a referential constraint between two tables.[1] The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must form a primary key or unique key.
Answer #2 0 Raghu
Re: what is the difference between primary key & foreign key? Answer #8 Basic difference betweeen Primary key and foreign key is : Primary key will not allow "Null values" and "Duplicate values" Foreign key will allow "Null values" and "Duplicte values" and it refers to a primary key in anoter table. 0 Shaik Gouse Peer
Re: what is the difference between primary key & foreign key? Answer #9 primary key is used to identify a row and it doesnot allow null values. It avoids duplication of rows.
Answer #2 whereas foreign key refers to a column or combination of columns included in the defenition of referential integrity. 0 Hardik Patel
Re: what is the difference between primary key & foreign key? Answer # 10 Primary key is used to identify a row in a table and it doesnot allow null values. It avoids duplication of rows. whereas foreign key refers to a column or combination of columns included in the defenition of referential integrity 0 Gaurav , Ashish ,preeti , Main
Re: what is the difference between primary key & foreign key? Answer # 11
Answer #2 foreign key is NULL foreign key reference as Primary key in another table Primary key is unique primary key is not NULL and 0 Shree
Re: what is the difference between primary key & foreign key? Answer # 12 primary key is a keyword .that refers to the unique value in the particular record.it cant have null foreign key is always keyword that refers primary key.it has null value.its refers to another table 0 Simbu
Re: what is the difference between primary key & foreign key? Answer
Answer #2 # 13 foregin key reference a primary key in another table and primary key is used to identify a row. 0 Raji
Re: what is the difference between primary key & foreign key? Answer # 14 primary key does not allow duplicate and null values but foreign key allows null values it refers primary key 0 Harish
Answer #2
Re: what is the syntax of ALTER command? Answer alter table tablename # 5 add(colname datatype,colname1 datatype...); alter table tablename modify(colname datatype,colname1 datatype..); Re: what is the syntax of DROP command? Answer drop table tablebname #3 Re: what is the syntax of CREATE command? Answer create table(column definition1, column definition2,...); Re: what is the syntax of DELETE command? Answer 1) Delete [From] # 1
explicit cursor should be declared and closed by the
user.
More over Explicit Cursors were used to retrieve values from two or more rows. Re: State the difference between implict and explict cursor's? Answer #2 The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. During the processing of an implicit cursor,Oracle automatically performs the OPEN, FETCH, and CLOSE operations. Where as in explicit cursors,the process of its working is done in 4 steps namely DECLARE a cursor,OPEN a cursor, FETCH from cursor and CLOSE a cursor. Re: State the difference between implict and explict cursor's? Answer #3 IMPLICT CURSOR:- Automatically porvide by oracle which perform DML statements. queries returns only one row. EXPLICT CURSOR:- Defined by user. queries returns more than rows. Re: State the difference between implict and explict cursor's? Answer #4 Explicit Cursor:-We are not able to Handle NO_DATA_FOUND
Exception. Implicit Cursor:-We are able to Handle NO_DATA_FOUND Exception. Re: Types of locks in database ? Answer #1 Exclusive locks, Share locks Re: Types of locks in database ? Answer #2 2 types 1. Implicit locks: oracle automatically locks the rows whenever user performs DML operations. 2. Explicit locks: provided by user. 2 tpyes a) Row level locks: used to lock selected rows of table.It is imposed by "for update" clause in select. b) Table level locks: used to lock compleate table. 3 models 1) share mode 2) share update 3) exlcusive mode
Question What is Distributed database ?
Rank
Answer Posted By
Question Submitted By :: Guest I also faced this Question!! database is a network
Answer A distributed of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database
© ALL Interview .com
can be simultaneously accessed and modified. Question What is cursor and where do you use them? Rank
Answer Posted By
Question Submitted By :: Mihir This Interview Question Asked @ eBay © ALL I also faced this Question!! Interview . com Answer cursor is temperory block of memory which holds the 0 Guest data as temperory in buffer
Answer Cursor is an named private area called active set which is used to process the multiple rows returned by the query.... cursors are mainly used in procedure/package/function when the select qurey in procedure/package/function returns more than one value....
0 Rajesh
if their is any issue mail me @ [email protected],[email protected]
Ques Distributed database is a database located at different places and include data from common operational and user database and also from data generated and used only at the user's site.
A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.
Re: WHAT IS INDEXING? Answer A database index is a data 0 # 1 structure that improves the speed of operations in a table. Indices can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.
Savithri
Re: WHAT IS INDEXING? Answer It Is One Of The Database # 2 Object,Which Automatically Created While Creating The Primary Keys Those Gives More Performance
What are the the different Data Question Manipulation Language statements are? Question Submitted By :: Guest I also faced this Question!! Rank
Answer Posted By
Re: What are the the different Data Manipulation Language statements are? Answer INSERT 0 Orawhiz UPDATE #1 SELECT
Re: What are the the different Data Manipulation Language statements are? Answer insert, update,delete,merge # 2 (on 9i) .
Re: What are the different Sequence of events takes place while starting a Database ? Answer Instance started, Database 0 Orawhiz # 1 mounted & Database opened
Re: What is a trigger? Answer A piece of logic written in # 1 PL/SQL Executed at the arrival of a SQL*FORMS event Re: what are the differences you find when working with oracle 91 and sql server 2005? Answer There are so many Difference 0 Daid in oracle 9i and Sql #1 Kumar Server2005 some example are as follows: in Oracle In sqlserver2005 -----------------------------------------------1.Schema
Database 2.Block Pages 3.Segment N/A 4.Clusters N/A 5.Index(Type) Only One Type is Present 6.Materialized Views N/A 7.Operators N/A 8.Packages N/A 9.Hierarchical Operators N/A 10. In case of partition table in oracle we have Three option and easely implemented but in Sqlserver It is complx work Etc......................... Re: What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode ? Answer Complete database recovery 0 Guest from disk failure is #1 possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode. Re: What is relational database ? Give me an example ! Answer A relational database is a
#1
database that conforms to the relational model, and refers to a database's data and schema
1) In computer programming, a schema (pronounced SKEE-mah) is the
organization or structure for a database. The activity of data modeling leads to a schema. (The plural form is schemata. The term is from a Greek word for "form" or "figure." Another word from the same source is "schematic.") The term is used in discussing both relational databases and object-oriented databases. The term sometimes seems to refer to a visualization of a structure and sometimes to a formal text-oriented description. Re: how to display last 5 letters from a name (take emp table ename ) Answer select right(ename,5) # 1 as name from emp Re: It's Urgent? How to IMPORT .xls & .txt file into ORACLE? Answer open the TOAD and run # 1 the QUERY by using the SELECT statement. after that click the right button and select the SAVE AS button, in that select .xls Radio button and enter the file name automatically data store in that file Re: query to find the maximum no persons with same age(age colomn) from emp table Answer select max_age,age # 1 from (select count(age) max_age,age from emp group by age order
by max_age desc) where rownum < 2 Re: What is a Data Segment ? Answer Each Non-clustered table # 1 has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment. Re: What is an Extent ? Answer An Extent is a specific # 1 number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information Re: What is Data Block ? Answer ORACLE database's data is # 1 stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. Re: which clause we are not used in where clause? Answer Group by clause #1 Re: What is the use of Redo Log Information ? Answer The Information in a redo # 1 log file is used only to
recover the database from a system or media failure prevents database data from being written to a database's data files Re: When does a Transaction end ? Answer when ever commit or # 1 rollback statement occurred in the end of trasaction Re: What is a View ? Answer View r the virtual table,it # 3 show the selected fields only... it is used for the security purpose Re: which sql command we can use to get a print out from oracle? Answer set autoprint off/on 3 Jamalvalli # 1 it is Sql*plus Command
Re: which sql command we can use to get a print out from oracle? Answer set autoprint on; # 2 will provide the command for printing. Re: What is a Sequence ? Answer Sequence is used to provide # 1 unique values to database object. Re: What is dictionary cache ? Answer Dictionary cache is
0
Sawant
#1
information about the databse objects stored in a data dictionary table.
Re: What is dictionary cache ? Answer Once the database server # 2 has accessed the table from the datafile , it places that information in the data-dictionary cache in shared memory. Re: What are the Characteristics of Data Files ? Answer A data file can be # 1 associated with only one database.Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace. Re: Explain the relationship among Database, Tablespace and Data file? Answer Each databases logically # 1 divided into one or more tablespaces One or more data files are explicitly created for each tablespace. Re: what is exact difference between drop and truncate table. Answer Drop Statement deletes the # 1 Table structure from the database. Truncate statement will
delete the entire data from Table and keeps the Table structure as it is in the DB. Re: How can we Update a table with out using UPDATE command? Answer Use merge command. If the # 1 rows already exist then it will update it with new data if not it would insert the new data. Re: Anybody can tell me, how do we find second largest emp salary from emp table.. Thanks in advance ... Answer select max(sal) from emp # 1 where sal < (select max(sal) from emp) select e.sal from emp e where &2>(select max(count{a.sal}) from emp a where a.sal > e.sal order by desc ) I would say that Raj has given the correct answer in general. In most cases the interviewer restricts the parameter to be written in the query as it might effect the performance. Select TOP 1 salary from (select Distinct TOP 2 salary from employee orderby salary desc) Re: What are main difference between Stored Procedure and Functions.
Answer The Main difference between # 1 stored procedure and Function is SP's can return the value or not, but Functions should return the value. And We can use the function within the Queries, but sp's we won't able to use it in the queries. We can implicitely execute the sp's. But function's we can't. Re: What are main difference between Stored Procedure and Functions. Answer stored procedure : It is Pre # 2 Compiled Code(PCode) it is stored in Database. we can reuse without compile. Function: It need compile whenever it will run. Re: find out first highest salary? Answer select max(sal) from emp; #1 Re: find out first highest salary? Answer select * from emp where &n# 2 1=(select count(distinct sal) from emp e where emp.sal<e.sal); For example:If you enter n=1,it will display first highest salary
if you enter n=2,it will display second highest salary ...... if you enter n=nth,it will display nth salary select * from emp x where 1=(select count(distinct sal) from emp y where x.sal<=y.sal); query to find nth sal select * from emp x where &n=(select count(distinct sal) from emp y where x.sal<=y.sal); Re: What are joins..how many types of joins are there? Answer Joins are used to fetch a query on more # 1 than one table. You can broadly catagorize joins in to four types. 1. Equi Join 2.Non Equijoin 3.outer joins 4.self joins Re: What are joins..how many types of joins are there? Answer In Oracle we can catogrise joins in # 7 following ways (1) Equi Join (Query having joining condition) - Natural Join - Join Using - Join ON (2) Non Equi Join or Cartesian Join ( without using joining condition) (3)Outer Join - left outer join - right outer join
- full outer join (4) self Join or Theta Join (5) Cross Join (6) Inner Join Re: What are Clusters ? Answer Cluster is amechanism which is used to # 1 bind data together such that it will improve performance while retreiving information from a table. Re: what is the use of ondelete cascade? Answer on delete cascade is used to delete the # 1 rows/constraints which are in integrity/foreign key constraints. Re: Can we create more than one index on particular column? Answer No # 1 But we can create composite index including that particular column with other columns. Re: what is the bitmap index? Answer bitmap Index is Used mainly in decision # 1 support systems because it returns value in bit like 0 and 1. Example CREATE BITMAP JOIN INDEX
Kavitha
Re: Maximum how many triggers can be updated in table ? Answer System Privileges Related To Table # 2 Triggers create trigger create any trigger administer database trigger alter any trigger drop any trigger Table Trigger Firing Options -- before constraints are applied BEFORE INSERT BEFORE UPDATE BEFORE DELETE -- after constraints are applied AFTER INSERT AFTER UPDATE AFTER DELETE Re: what is the difference between UNION AND UNIONALL Answer UNION 0 Vinod The UNION command is used to select #1 Singh related information Kushwah from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. Note: With UNION, only distinct values are selected. SQL Statement 1 UNION SQL Statement 2 Employees_Norway: E_ID E_Name
01 02 03 04
Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari
Employees_USA: E_ID E_Name 01 Turner, Sally 02 Kent, Clark 03 Svendson, Stephen 04 Scott, Stephen -----------------------------------------------------------------------------Using the UNION Command Example List all different employee names in Norway and USA: SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA Result E_Name Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari Turner, Sally Kent, Clark Scott, Stephen Note: This command cannot be used to list all employees in
Norway and USA. In the example above we have two employees with equal names, and only one of them is listed. The UNION command only selects distinct values. UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. SQL Statement 1 UNION ALL SQL Statement 2 -----------------------------------------------------------------------------Using the UNION ALL Command Example List all employees in Norway and USA: SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA Result E_Name Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari Turner, Sally Kent, Clark Svendson, Stephen Scott, Stephen
Re: what is the difference between UNION AND UNIONALL Answer UNION provides only distinct values # 2 as output whereas UNION ALL provides all values. So UNION ALL seems to be faster than UNION. Re: ex. one table is having 1 column with 10 records , then how to display all the values in row wise ? Answer it will need a cursor. 0 Ruchi #1 Varma begin for cur1 in (select a from Name) loop dbms_output.put(cur1.a); end loop; end; dbms_output.put -> displays a chr without putting a new line
Re: ex. one table is having 1 column with 10 records , then how to display all the values in row wise ? Answer create table x (col1 number(4)); # 2 insert into x value(1); .... ... insert into x value(10); /* upto 10 rows then select substr(max(decode(rownum,1,col1,0)),1,2), substr(max(decode(rownum,2,col1,0)),1,2), substr(max(decode(rownum,3,col1,0)),1,2), substr(max(decode(rownum,4,col1,0)),1,2), substr(max(decode(rownum,5,col1,0)),1,2),
substr(max(decode(rownum,6,col1,0)),1,2), substr(max(decode(rownum,7,col1,0)),1,2), substr(max(decode(rownum,8,col1,0)),1,2), substr(max(decode(rownum,9,col1,0)),1,2), substr(max(decode(rownum,10,col1,0)),1,2) from x (where substr only to reduce display size ) Re: Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Otherwise insert new record in the C1 And C2 (Using Procedure) Answer select * from x; 0 #1 C1 C2 ----- ---------1 2 3 4 5 6 7 8 9 10 1 create or replace procedure updt_x is 2 cnt number(4); 3 begin 4 select count(1) into cnt from x where c1 is not null; 5 if cnt > 0 then 6 update x 7 set c2=10; 8 else 9 insert into x
Dinesh A.
10 values(1,2); 11 end if; 12 commit; 13* end; SQL> execute updt_x; PL/SQL procedure successfully completed. SQL> select * from x; C1 C2 ---------- ---------1 10 2 10 3 10 4 10 5 10 6 10 7 10 8 10 9 10 10 10 10 rows selected. SQL> delete from x; 10 rows deleted. SQL> commit; PL/SQL procedure successfully completed. SQL> select * from x; C1
C2
---------- ---------1 2
Re: Table Has C1 And C2 Column If Exits any record in c1 then Update c2 record Otherwise insert new record in the C1 And C2 (Using Procedure) Answer Hi Answer to Your question IS #2 create or replace procedure samp(cname IN sample.ename%type, cno IN sample.eno%type) AS ccount number; begin select count(*) INTO ccount from sample; IF ccount = 0 then update sample set ename = cname; else insert into sample values(cname, cno); END IF; END; Sample Table is ENAME ENO 100 use oracle's new feature called upsert : merge t1 using (select * t1) t2 on (t1.col1=t2.col1) when matched then update set col2='value' when not matched then insert into (col1, col2) values ('val1', 'val2');
1) In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships. There are three main normal forms, each with increasing levels of normalization: First Normal Form (1NF): Each field in a table contains different information. For example, in an employee list, each table would contain only one birthdate field. Second Normal Form (2NF): Each field in a table that is not a determiner of the contents of another field must itself be a function of the other fields in the table. Third Normal Form (3NF): No duplicate information is permitted. So, for example, if two tables both require a birthdate field, the birthdate information would be separated into a separate table, and the two other tables would then access the birthdate information via an index field in the birthdate table. Any change to a birthdate would automatically be reflect in all tables that link to the birthdate table. There are additional normalization levels, such as Boyce Codd Normal Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF). While normalization makes databases more efficient to maintain, they can also make them more complex because data is separated into so many different tables. (2) In data Re: Who i will insert 1 lacks record in a Database table Answer The qs is not quite # 1 clear.... Still I suppose u wana insert 1 lach record in database table.
0
Milan
It is quite simple.... Just write a stored procedure for insert of 1 lakh records,compile it and run... U will b get 1 lakh records inserted in a single stroke... Re: how many columns can a plsql table have Answer total 250 , 249 non clustered and # 2 1 clustered Re: can a view be updated? Answer yes it can be updated ....but # 1 there are certain restrictions in it. For eg: 1.a view created from nonupdatable view cant be updated 2.when it is having having , groupby,orderby it cant be updated.etc Re: what r routers? Answer An electronic device connected with # 1 various nodes in various network. Re: what are the database links ? Answer Database Link is a named path # 1 through which a remote database can be accessed. Re: how do u know the total no of rows in a table? Answer Select count(*) from table
#1 Re: what is RAID technology?which is better RAID 0+1 OR RAID 5? Answer RAID — which stands for Redundant Array # 1 of Inexpensive Disks. A RAID distributes data across several physical disks which look to the operating system and the user like a single disk. Several different arrangements are possible. We assume here that all the disks are of the same capacity, as is usual. We have only 3 basic RAID levels : RAID 0: Provides improved performance and additional storage but no fault tolerance. Any disk failure destroys the array, which becomes more likely with more disks in the array. A single disk failure destroys the entire array because when data is written to a RAID 0 drive, the data is broken into fragments. The number of fragments is dictated by the number of disks in the drive. The fragments are written to their respective disks simultaneously on the same sector. This allows smaller sections of the entire chunk of data to be read off the drive in parallel, giving this type of arrangement huge bandwidth. RAID 0 does not implement error checking so any error is unrecoverable. More disks in the
array means higher bandwidth, but greater risk of data loss. RAID 1: Mirrored set without parity. Provides fault tolerance from disk errors and failure of all but one of the drives. Increased read performance occurs when using a multi-threaded operating system that supports split seeks, very small performance reduction when writing. Array continues to operate so long as at least one drive is functioning. Using RAID 1 with a separate controller for each disk is sometimes called duplexing. RAID 5: Striped set with distributed parity. Distributed parity requires all drives but one to be present to operate; drive failure requires replacement, but the array is not destroyed by a single drive failure. Upon drive failure, any subsequent reads can be calculated from the distributed parity such that the drive failure is masked from the end user. The array will have data loss in the event of a second drive failure and is vulnerable until the data that was on the failed drive is rebuilt onto a replacement drive. Rest of RAID levels are the combination
of these basic raid levels. which is better RAID 0+1 OR RAID 5? ........... good question....... RAID 0+1: striped sets in a mirrored set (minimum four disks; even number of disks) provides fault tolerance and improved performance but increases complexity. Hence, Raid 5 is definitely better because if there is a failure of even 1 disk in RAID 0+1.... the situation is same as it is in raid 5 but, the min. no. of disks utilized in raid 0+1 is 4 and on the other hand in raid 5, the similar fault tolerance is given with a min of 3 HDDs. Seniors kindly correct me if I am wrong. Re: which statement is running fastly ie insert or delete? Answer Its definitely Delete. # 4 Becuase When Delete operation is being performed then Oracle doesnot actualy permanently remove the data from data block but rather marks that particular data block as unusable. Whereas when concerned to Insert Oracle needs to insert the
new values into Datablocks. Re: What are Schema Objects ? Answer Schema objects are the logical structures # 1 that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links. Re: What is Parallel Server ? instances accessing Answer Multiple # 1 same database (Only In Multi-CPU environments)
the
Re: Can we create index on views? Answer An index cannot be defined on a view. # 1 Because view is virtual table, which consists of a subset of columns from one more tables. The Restrictions imposed on views are as follows: 1. A view can be created only in the current database. 2. A view can be created only if there is a SELECT permission on its base table 3. A Trigger or an Index cannot be defined on a view. 4. A view cannot be derive its data from temporary tables. 5. The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.
Re: What is Public Database Link ? Answer Public database link is created # 1 for the special user group PUBLIC.
0
Orawhiz
A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition. Re: Tell me New Feature of Oracle 10g? Answer 10g stands for Grid computing: # 2 read oracle link below for all its features : http://www.oracle.com/technology/pub/articles/10gdba/index.html http://www.oracle.qassociates.co.uk/oracle-10g-features.htm Re: what is the output of select * from emp where null=null & select * from emp where 1=1 Answer select * from emp where null=null: 2 Anitha # 1 will not return any rows in the table since two null values are not always same. select * from emp where 1=1 : will return all the rows in the table because 1 is always equal to 1. Re: how to truncate date and get only time part 9:20:00 Answer select
Saisivakumar [Kesdee]
Note: i have given for both date and time, pls use as u want. Re: what is primary key and foreign key when and where have to use Answer primary key has a unique & it is 0 # 1 only one in a databasewhile fkey is not unique. it is based on primary key
Prashant
HI Primary key = (Not Null + Unique). Foreign key = (For Referential table access). primary key has unique value, it dostnt contain null values and duplicate values. foregin key is primary key of another table
Re: What is difference between UNIQUE constraint and PRIMARY KEY constraint ? Answer The Primarykey Constraint= # 1 unique + Not Null constraints. For the Unique the meaning it self it is telling unique value. won't accept duplicate values. When ever you create a primary key constraint on perticular column in a table it creates clustered index by default, it cannot allow null values and a table having only
one primary key constraint. But in the case of Unique Key constraint, it allow only one null value, it creates nonclustered index by default and a table having multiple unique key constraints.
Re: what are Triggers? Triggers are basically used to implement business rules. Triggers are also similar to stored procedures.The difference is that it can be activated when data is added or edited or deleted from a table in a database.
Re: what are Triggers? Answer Triggers are the database # 2 objects (Like Procedures) which will be executed by the Database itself and cannot be excuted by the user explicitly. The trigger can be written to execute while inserting/ updating/ deleting a row. Two types of triiger ROW LEVEL TRIGGER
=============== Row Level triggers are FOR EACH ROW, that is, the trigger is activated for each row that is inserted/updated/deleted. STATEMENT LEVEL TRIGGER ===================== Statement level triggers will execute the action specified only once. (Will not consider the no. of rows being inserted/updated/deleted) Re: What are the Limitations of a CHECK Constraint ? Answer The check consttraint should # 1 be with in the two boundaries (min value to max value). Re: What are the Limitations of a CHECK Constraint ? Answer It is used to avoid invalid # 2 and inconsistence data into the data base table. For example if you specify like this sal int Check(sal>=1000) and trying to insert value below 1000 in the sal column,they won't accept you.That means it restrict the data in the database table.
Re: Explain 1st, 2nd, 3rd normalization form of data base Answer HI #1
First Normalisation is "Primary Key" Second Normalisation is " Composite Key" bye Re: Explain 1st, 2nd, 3rd normalization form of data base Answer 1st normal form: first # 2 identify the repeating group of fields n group them into separate table 2nd normal: it should be in 1st normal form and check all fields are dependent on primary key 3nd normal form: it should be in 2nd normal form and check all the fields r independent of any other non-key attribute ie removes the fields that dependent on non-key 1st normal: first identify repeating fields and group them another table 2nd normal: it should be in 1st normal form and identify that all fields all dependent on primary key. 3nd normal: it should be in 2nd normal form and check all
fields all indpendent of other non-key attribute Re: difference between oracle8i and oracle9i Answer merge is a command which is # 1 used to both insert and delete. timestamp datatype is introduced. max no of columns in 8i is 256 where as in 9i we can have 999. on delete set null supports only in 9i. by using this, when we delete the parent record the child records are replaced with null values Re: what is difference between where clause and having clause? Answer in 'where' clause every # 1 records filtered based on where in 'having' is with aggregate records (group by functions ) Re: what is difference between where clause and having clause? Answer RISTRICT GROUP BY FUNCTION BY # 2 HAVING. RESTRICT NORMAL QUERY BY WHERE. YOU CAN PUT WHERE AND HAVING IN A QUERY Re: What are triggers?Where are they used? Answer trigger is autorun query. for # 1 example if you perform some
operation that time some operation(insert,delete) shuld be execute automatically that time you have to use trigger Re: What are triggers?Where are they used? Answer triggers are the named pl/sql # 2 blocks which are executed automatically at the specified events.the event at which triggers are executed is called as the triggering event.there are 3 types of triggers (i) dml triggers: these triggers are executed either before or after any dml events(i.e insert or update or delete) (ii) ddl triggers: these triggers are executed either before or after any ddl events(i.e create or alter or rename or truncate or drop) (iii) database triggers these trigger are executed either before or after any database events(i.e logon or logoff or startup or shutdown) database triggers can be created only by user having dba privileges. using triggers we can perform the following operations (i) auto managing of data (ii) auditing of data (iii) auditing of events (iv)defining of validations
Re: What is Index and different types of Indexes? Answer Index is used to solve some # 1 query fast. we have 2 types of cursors. 1 Bitmap Index ( used only on Low cardinality columns) 2. Btree Index ( used only for High Cardinality of columns) Re: What is Index and different types of Indexes? Answer Index is used to fetch the # 2 data in a reliable and fast manner and the types of index avalilable are clustered index and non clustered index.
Re: What is Index and different types of Indexes? Answer indexes is a process of # 3 creating a structure from a database table from a single or multiple compostion (atleast) of attributes for efficient data retreival. some database even allow to create indexes out of expressions as well. there are different types of indexes based on the implementation methodology: b Trees, b+ trees, bitmap indexes, hash index etc . infact
in some database the index file is different from the database file and hence one can write one's plug in for a special kind of index . clustered and non-clustered is the kind of architecture that is used to define the index methodology as noted earlier. indexes are boon when used in retreivals and they are doomed when used in situations where there's more write than read. this is so because every new write in the table also implies corresponding modofication in the indexes as created for the table. Re: What is Index and different types of Indexes? Answer PHYSICAL OR LOGICAL LISTING # 4 OF COLUMN TO SPEEDUP THE SEARCH IN A TABLE OR VIEW IS CALLED INDEX. FUNCTION BASED INDEX HASH INDEX BITMAP INDEX COMPOSIT INDEX B TREE INDEX Re: Can Multiple instances be run on Single Machine??? Answer Yes, you can run RAC (Real # 2 Application Clusters) that consist of multiple instances
accessing the same database. You can also run the same and different versions of Oracle running on a single machine each of course accessing different databases. Re: what is the difference between primary key & foreign key? Answer Primary keys enforce 1 Ramprakash entity integrity by #1 uniquely identifying entity instances. Foreign keys enforce referential integrity by completing an association between two entities.
Re: what is the difference between primary key & foreign key? Answer #2
Re: what is the difference between primary key & foreign key? Answer #3 Primary key is unique primary key is not NULL and foreign key is NULL foreign key reference as Primary key in another table
Answer #2
Guest
Re: what is the difference between primary key & foreign key? Answer #4 Primary Key is unique Key entire the table which is also NOT NULL where as foriegn key is the null & which references the primary key .
and
0 Muneer
Re: what is the difference between primary key & foreign key? Answer #5 primary key is the parent and foreign is the child. 0 Guest
Answer #2 Re: what is the difference between primary key & foreign key? Answer #6 primary key is used to identify a row and it doesnot allow null values. It avoids duplication of rows. whereas foreign key refers to a column or combination of columns included in the defenition of referential integrity. 0 Guest
Re: what is the difference between primary key & foreign key? Answer #7 In the context of relational databases, a foreign key is a referential constraint between two tables.[1] The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must form a primary key or unique key.
Answer #2 0 Raghu
Re: what is the difference between primary key & foreign key? Answer #8 Basic difference betweeen Primary key and foreign key is : Primary key will not allow "Null values" and "Duplicate values" Foreign key will allow "Null values" and "Duplicte values" and it refers to a primary key in anoter table. 0 Shaik Gouse Peer
Re: what is the difference between primary key & foreign key? Answer #9 primary key is used to identify a row and it doesnot allow null values. It avoids duplication of rows.
Answer #2 whereas foreign key refers to a column or combination of columns included in the defenition of referential integrity. 0 Hardik Patel
Re: what is the difference between primary key & foreign key? Answer # 10 Primary key is used to identify a row in a table and it doesnot allow null values. It avoids duplication of rows. whereas foreign key refers to a column or combination of columns included in the defenition of referential integrity 0 Gaurav , Ashish ,preeti , Main
Re: what is the difference between primary key & foreign key? Answer # 11
Answer #2 foreign key is NULL foreign key reference as Primary key in another table Primary key is unique primary key is not NULL and 0 Shree
Re: what is the difference between primary key & foreign key? Answer # 12 primary key is a keyword .that refers to the unique value in the particular record.it cant have null foreign key is always keyword that refers primary key.it has null value.its refers to another table 0 Simbu
Re: what is the difference between primary key & foreign key? Answer
Answer #2 # 13 foregin key reference a primary key in another table and primary key is used to identify a row. 0 Raji
Re: what is the difference between primary key & foreign key? Answer # 14 primary key does not allow duplicate and null values but foreign key allows null values it refers primary key 0 Harish
Answer #2
Re: what is the syntax of ALTER command? Answer alter table tablename # 5 add(colname datatype,colname1 datatype...); alter table tablename modify(colname datatype,colname1 datatype..); Re: what is the syntax of DROP command? Answer drop table tablebname #3 Re: what is the syntax of CREATE command? Answer create table
