me new in ASP.me having problem whoile displaying the data from the database on toi page in a tabular form.actually the problem is tht while i displays the data first time every thing goes well and good.at the 2nd time when i goes to tht page the data that has been displayd at first time remains their i think i hav some logical mistake but not reaching up to tht.so any body plz help me plz
I am useing oracle forms 10g and reports 10g and database (AIX) 10g. I want to send email from oracle to others plz anyvody can help me by giving the entair logic and code for sending email from oracle. if any configuration have to made in my server plz suggest me how to configure.
How can i send email from oracle form 10g anybocy can help me with code (if package is required also send me that package) and if any configuration is required
1. What are the implicit objects? - Implicit objects are objects that are created by the web container and contain information related to a particular request, page, or application. They are: request, response, pageContext, session, application, out, config, page, exception.
31. What is the difference among “dropping a tableâ€Â, “truncating a table†and “deleting all records†from a table.
Drop Table - will remove the existance of the table from the database along with its data and structure and all the constraints. The table will be no longer available. This is an DDL Statement.
Truncate Table - will remove all the rows from a table. It will not delete the table. Its a DDL statement that means the deleted rows cannot be reverted back by ROLLBACK statement. It will only be used if users needs to delete the entire rows from a tabls. No conditions will be applied in Truncate.
Delete Table - is a DML statement which will delete rows from a table according to the matching criteria mentions in the ‘where’ clause and these rows can be reverted back by ‘ROLLBACK’ statement if ‘COMMIT’ is not fired. Delete statement will used in both the cases, eitehr selected rows or entire rows from tables.
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
VARCHAR2 is used to store variable length character strings. The string value’s length will be stored on disk with the value itself.
1.What are the different types of joins?
Ans: INNER JOIN(equi join)
OUTER JOIN(left,right,full)
CROSS JOIN
2.Explain normalization with examples.
3.What cursor type do you use to retrieve multiple recordsets?
Ans: REF CURSOR
4.Diffrence between a “where†clause and a “having†clause
Ans: WHERE clause is used to compare single row resultset;
HAVING clause is used to compare multiple row resultset;
5.What is the difference between “procedure†and “function�
Ans: PROCEDURE may or may not return a value;
FUNCTION has to return a value,func can be directly used in sql query;
for eg:select employee_id,salary,tax(salary) from employees:this stat calculate the tax for every employees salary;
6.How will you copy the structure of a table without copying the data?
Ans: create table new_emp select * from employees where 1=0;
7.How to find out the database name from SQL*PLUS command prompt?
8.Tadeoffs with having indexes
9.Talk about “Exception Handling†in PL/SQL?
Ans: 2 types of Exception In User-defined & Pre-defined.Any Error can be Handled by Pre-defined Exception Handler
(Ex. no_data_found,too_many_rows etc.)
In User-defined exception has to declare in Declaration section and any exception can be handled by that
Exception name.Any types of Exception(User-defined & Pre-defined) can be Handled by When Others exception Handler.
10.What is the diference between “NULL in C†and “NULL in Oracle?â€Â
Ans: NULL in oracle is neither an empty string neither a zero,any arithmetic operation on null return NULL.
for eg:NULL*0=NULL
11.What is Pro*C? What is OCI?
12.Give some examples of Analytical functions.
Ans: Analytical function are:AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE,
LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK,
RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.
13.What is the difference between “translate†and “replace�
Ans: REPLACE replaces every instance of a set of characters with another set of characters; that is,
REPLACE works with entire words or patterns. TRANSLATE replaces single characters at a time, translating
the nth character in the match set with the nth character in the replacement set.
14.What is DYNAMIC SQL method 4?
Ans: some applications must accept (or build) and process a variety of SQL statements at run time which is achieved
by DYNAMIC SQL
For eg:’DELETE FROM EMP WHERE MGR = :mgr_number AND JOB = :job_title’ (promt from user for mgr& job during runtime)
15.How to remove duplicate records from a table?
Ans: delete from employees e where rowid(select max(rowid) from employees e1 where e.employee_id=e1.employee_id);
18.What is a “transaction� Why are they necessary?
Ans: All changes of data in an Oracle database can only be done within a transaction.
A transaction must either be committed or rolled back.
19.Explain Normalizationa dn Denormalization with examples.
20When do you get contraint violtaion? What are the types of constraints?
Ans: there r 2 types of contraints:I/O constraint &business-level constraint
I/O constraint->PRIMARY KEY,FOREIGN KEY,UNIQUE KEY
Business-leve->NOT NULL,CHECK
24.What is a “trigger�
Ans: TRIGGER is the user defined constraint which is used for validation or any check condition,it is fired always when
DML (update,delete,insert) stat r done on the table;
25.Why can a “group by†or “order by†clause be expensive to process?
Ans: GROUP BY,ORDER BY is executed by the oracle engine at last this leads to overhead bcoz whole data has
to be rearranged again.
27.What is a VIEW? How to get script for a view?
Ans: to get VIEW script :select text from user_view where user_name=”;
31What is the difference among “dropping a tableâ€Â, “truncating a table†and “deleting all records†from a table.
Ans: DROP command is used to remove any database object from the database. It works by removing the object definition.
Since the object no longer exists, it can no longer be used. The drop command is DDL because it defines objects
in the database. Anytime you execute a DDL command, you implicitly issue a commit and the statement cannot be
rolled back
The DELETE command will not remove the table but will remove all the rows in the table.It wll also return the no of
rows being deleted.It deletes one row at a time.You can delete specific no or all rows from a table.
The TRUNCATE drops the table and recreates its structure.All rows are deleted.It doesnot returns the no of rows
deleted.You cannot delete specific rows.
61 Comments on Basic database interview quesitons
me new in ASP.me having problem whoile displaying the data from the database on toi page in a tabular form.actually the problem is tht while i displays the data first time every thing goes well and good.at the 2nd time when i goes to tht page the data that has been displayd at first time remains their i think i hav some logical mistake but not reaching up to tht.so any body plz help me plz
I am useing oracle forms 10g and reports 10g and database (AIX) 10g. I want to send email from oracle to others plz anyvody can help me by giving the entair logic and code for sending email from oracle. if any configuration have to made in my server plz suggest me how to configure.
rajendra thanx in advance
How can i send email from oracle form 10g anybocy can help me with code (if package is required also send me that package) and if any configuration is required
1. What are the implicit objects? - Implicit objects are objects that are created by the web container and contain information related to a particular request, page, or application. They are: request, response, pageContext, session, application, out, config, page, exception.
31. What is the difference among “dropping a tableâ€Â, “truncating a table†and “deleting all records†from a table.
Drop Table - will remove the existance of the table from the database along with its data and structure and all the constraints. The table will be no longer available. This is an DDL Statement.
Truncate Table - will remove all the rows from a table. It will not delete the table. Its a DDL statement that means the deleted rows cannot be reverted back by ROLLBACK statement. It will only be used if users needs to delete the entire rows from a tabls. No conditions will be applied in Truncate.
Delete Table - is a DML statement which will delete rows from a table according to the matching criteria mentions in the ‘where’ clause and these rows can be reverted back by ‘ROLLBACK’ statement if ‘COMMIT’ is not fired. Delete statement will used in both the cases, eitehr selected rows or entire rows from tables.
5.What is the difference between “procedure” and “function”?
Ans. Function returns values to the calling element but procedure can’t.
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
VARCHAR2 is used to store variable length character strings. The string value’s length will be stored on disk with the value itself.
questions & Answers
1.What are the different types of joins?
Ans: INNER JOIN(equi join)
OUTER JOIN(left,right,full)
CROSS JOIN
2.Explain normalization with examples.
3.What cursor type do you use to retrieve multiple recordsets?
Ans: REF CURSOR
4.Diffrence between a “where†clause and a “having†clause
Ans: WHERE clause is used to compare single row resultset;
HAVING clause is used to compare multiple row resultset;
5.What is the difference between “procedure†and “function�
Ans: PROCEDURE may or may not return a value;
FUNCTION has to return a value,func can be directly used in sql query;
for eg:select employee_id,salary,tax(salary) from employees:this stat calculate the tax for every employees salary;
6.How will you copy the structure of a table without copying the data?
Ans: create table new_emp select * from employees where 1=0;
7.How to find out the database name from SQL*PLUS command prompt?
8.Tadeoffs with having indexes
9.Talk about “Exception Handling†in PL/SQL?
Ans: 2 types of Exception In User-defined & Pre-defined.Any Error can be Handled by Pre-defined Exception Handler
(Ex. no_data_found,too_many_rows etc.)
In User-defined exception has to declare in Declaration section and any exception can be handled by that
Exception name.Any types of Exception(User-defined & Pre-defined) can be Handled by When Others exception Handler.
10.What is the diference between “NULL in C†and “NULL in Oracle?â€Â
Ans: NULL in oracle is neither an empty string neither a zero,any arithmetic operation on null return NULL.
for eg:NULL*0=NULL
11.What is Pro*C? What is OCI?
12.Give some examples of Analytical functions.
Ans: Analytical function are:AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE,
LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK,
RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.
13.What is the difference between “translate†and “replace�
Ans: REPLACE replaces every instance of a set of characters with another set of characters; that is,
REPLACE works with entire words or patterns. TRANSLATE replaces single characters at a time, translating
the nth character in the match set with the nth character in the replacement set.
14.What is DYNAMIC SQL method 4?
Ans: some applications must accept (or build) and process a variety of SQL statements at run time which is achieved
by DYNAMIC SQL
For eg:’DELETE FROM EMP WHERE MGR = :mgr_number AND JOB = :job_title’ (promt from user for mgr& job during runtime)
15.How to remove duplicate records from a table?
Ans: delete from employees e where rowid(select max(rowid) from employees e1 where e.employee_id=e1.employee_id);
18.What is a “transaction� Why are they necessary?
Ans: All changes of data in an Oracle database can only be done within a transaction.
A transaction must either be committed or rolled back.
19.Explain Normalizationa dn Denormalization with examples.
20When do you get contraint violtaion? What are the types of constraints?
Ans: there r 2 types of contraints:I/O constraint &business-level constraint
I/O constraint->PRIMARY KEY,FOREIGN KEY,UNIQUE KEY
Business-leve->NOT NULL,CHECK
24.What is a “trigger�
Ans: TRIGGER is the user defined constraint which is used for validation or any check condition,it is fired always when
DML (update,delete,insert) stat r done on the table;
25.Why can a “group by†or “order by†clause be expensive to process?
Ans: GROUP BY,ORDER BY is executed by the oracle engine at last this leads to overhead bcoz whole data has
to be rearranged again.
27.What is a VIEW? How to get script for a view?
Ans: to get VIEW script :select text from user_view where user_name=”;
31What is the difference among “dropping a tableâ€Â, “truncating a table†and “deleting all records†from a table.
Ans: DROP command is used to remove any database object from the database. It works by removing the object definition.
Since the object no longer exists, it can no longer be used. The drop command is DDL because it defines objects
in the database. Anytime you execute a DDL command, you implicitly issue a commit and the statement cannot be
rolled back
The DELETE command will not remove the table but will remove all the rows in the table.It wll also return the no of
rows being deleted.It deletes one row at a time.You can delete specific no or all rows from a table.
The TRUNCATE drops the table and recreates its structure.All rows are deleted.It doesnot returns the no of rows
deleted.You cannot delete specific rows.
Q1. Different type of joins
SQL Server joins there are three types
A. Inner Join
B. Outer Join
C. Cross Join
B. The outer joins there are three types
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join
Senthil Kumar @ Klsen
US Technology - Kerala
How will you copy the structure of a table with copying the data?
select * into new table name from oldtable
eg,
Select * into dept_backup from dept
How will you copy the structure of a table with copying the data?
select * into new table name from oldtable where 1=2
eg,
Select * into dept_backup from dept
where 1=2