User talk:Brijbs

Basic DataBase Questions

1.	What are the different types of joins? 2.	Explain normalization with examples.

3.	What cursor type do you use to retrieve multiple recordsets?

4.	Diffrence between a “where” clause and a “having” clause 5.	What is the difference between “procedure” and “function”?

6.	How will you copy the structure of a table without copying the data?

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? 10.	What is the diference between “NULL in C” and “NULL in Oracle?” 11.	What is Pro*C? What is OCI?

12.	Give some examples of Analytical functions.

13.	What is the difference between “translate” and “replace”?

14.	What is DYNAMIC SQL method 4?

15.	How to remove duplicate records from a table?

16.	What is the use of ANALYZing the tables?

17.	How to run SQL script from a Unix Shell?

18.	What is a “transaction”? Why are they necessary?

19.	Explain Normalizationa dn Denormalization with examples.

20.	When do you get contraint violtaion? What are the types of constraints?

21.	How to convert RAW datatype into TEXT?

22.	Difference - Primary Key and Aggregate Key

23.	How functional dependency is related to database table design?

24.	What is a “trigger”?

25.	Why can a “group by” or “order by” clause be expensive to process?

26.	What are “HINTS”? What is “index covering” of a query?

27.	What is a VIEW? How to get script for a view?

28.	What are the Large object types suported by Oracle?

29.	What is SQL*Loader?

30.	Difference between “VARCHAR” and “VARCHAR2″ datatypes.

31.	What is the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table.

32.	Difference between “ORACLE” and “MICROSOFT ACCESS” databases.

33.	How to create a database link?

Finding duplicates in a table

Let say you want to find all email addresses in a table that exist more than once:

SELECT email, COUNT(email) AS NumOccurrences FROM users GROUP BY email HAVING ( COUNT(email) > 1 )

Find rows that occur exactly once:

SELECT email FROM users GROUP BY email HAVING ( COUNT(email) = 1 )

Delete duplicate record

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateValueColumn1, DuplicateValueColumn2 and DuplicateValueColumn3.

DELETE FROM 	MyTable WHERE 	ID NOT IN (SELECT        MAX(ID) FROM            MyTable GROUP BY        DuplicateValueColumn1, DuplicateValueColunm2, DuplicateValueColumn3

Java Web development interview questions

1.	Can we use the constructor, instead of init, to initialize servlet? -

Yes, of course you can use the constructor instead of init. There’s nothing to stop you. But you shouldn’t. The original

reason for init was that ancient versions of Java couldn’t dynamically invoke constructors with arguments, so there was no

way to give the constructur a ServletConfig. That no longer applies, but servlet containers still will only call your no-arg

constructor. So you won’t have access to a ServletConfig or ServletContext.

2.	How can a servlet refresh automatically if some new data has entered the database? -

You can use a client-side Refresh or Server Push.

3.	The code in a finally clause will never fail to execute, right? -

Using System.exit(1); in try block will not allow finally code to execute.

4.	How many messaging models do JMS provide for and what are they? -

JMS provide for two messaging models, publish-and-subscribe and point-to-point queuing.

5.	What information is needed to create a TCP Socket? -

The Local System?s IP Address and Port Number. And the Remote System’s IPAddress and Port Number.

6.	What Class.forName will do while loading drivers?

- It is used to create an instance of a driver and register it with the DriverManager. When you have loaded a driver, it is

available for making a connection with a DBMS.

7.	How to Retrieve Warnings?

- SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the

execution of an application, as exceptions do; they simply alert the user that something did not happen as planned. A warning

can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a

ResultSet object. Each of these classes has a getWarnings method, which you must invoke in order to see the first warning

reported on the calling object

8.		SQLWarning warning = stmt.getWarnings;

9.		if (warning != null)

10.		{

11.			while (warning != null)

12.			{

13.				System.out.println(\"Message: \" + warning.getMessage);

14.				System.out.println(\"SQLState: \" + warning.getSQLState);

15.				System.out.print(\"Vendor error code: \");

16.				System.out.println(warning.getErrorCode);

17.				warning = warning.getNextWarning;

18.			}

19.		}

20.	How many JSP scripting elements are there and what are they?

- There are three scripting language elements: declarations, scriptlets, expressions.

21.	In the Servlet 2.4 specification SingleThreadModel has been deprecated, why?

- Because it is not practical to have such model. Whether you set isThreadSafe to true or false, you should take care of

concurrent client requests to the JSP page by synchronizing access to any shared objects defined at the page level.

22.	What are stored procedures? How is it useful? -

A stored procedure is a set of statements/commands which reside in the database. The stored procedure is pre-compiled and

saves the database the effort of parsing and compiling sql statements everytime a query is run. Each database has its own

stored procedure language, usually a variant of C with a SQL preproceesor. Newer versions of db’s

Sysbase Compute Count

Select col1,col2 from  table1 order by col2 compute count (col2) by col2;

Put If and Else statement in SQL

select (CASE WHEN SEC_TYPE_CDE = 'Certificate' THEN 'COM' when SEC_TYPE_CDE = 'Common' THEN 'COM' when SEC_TYPE_CDE = 'Mutual/Trust Fd' THEN 'MFO' when SEC_TYPE_CDE = 'Preferred' THEN 'PFD' when SEC_TYPE_CDE = 'Special Stock' THEN 'COM' when SEC_TYPE_CDE = 'Unit' THEN 'MFO' ELSE SEC_TYPE_CDE end) SEC_TYPE_CDE from GPW

select (CASE WHEN SEC_TYPE_CDE = 'Certificate' THEN 'COM' WHEN SEC_TYPE_CDE = 'Common' THEN 'ADR' ELSE SEC_TYPE_CDE END) SEC_TYPE_CDE, EXT_SEC_ID from GPW

Select the nth Number of maximum value from the table

select sal from emp t where &n = (select count(sal) from (select distinct sal from emp) where t.sal>=sal);

Finding duplicate value in a Table

select sal from emp t where &n = (select count(sal) from (select distinct sal from emp) where t.sal>=sal);

select sal from emp t where &n = (select count(sal) from (select distinct sal from emp) where t.sal>=sal);

You could also use this technique to find rows that occur exactly once:

SELECT email FROM users GROUP BY email HAVING ( COUNT(email) = 1 )

Limit Clause

There are many solutions to this problem. I agree that if the result set is a too large, storing 1000 of record in session will not be a good idea. Better solution will be to get record from database for each page. If you are working with mysql there is a limit key work that will help you the featch for the particular page. On oracle there is no such keyword but there is a query throught which you can select record form middle of the total selected record for your search condition. say you are fetching emp name from emp table and you want names from 20th to 30th record for a search criteria say in emp in city newyork. The query will be select x.emp_name from (select rownum y, emp_name from emp where city='newyork')x where x.y between 20 and 30;

"select x.emp_name from (select rownum y, emp_name from emp where city='newyork')x where x.y between 20 and 30;"

select emp_name from emp where city = 'newyork' and rownum < 31 MINUS select emp_name from emp where city = 'newyork' and rownum < 20

this clause will perform better, i think so but no test.

Link for this:  http://jguru.com/faq/view.jsp?EID=60818

SELF-JOIN

A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table. One practical use for self-joins: obtaining running counts and running totals in an SQL query.

To write the query, select from the same table listed twice with different aliases, set up the comparison, and eliminate cases where a particular value would be equal to itself.

Example Which customers are located in the same state (column name is Region)? Type this statement in the SQL window:

SELECT DISTINCT c1.ContactName, c1.Address, c1.City, c1.Region FROM Customers AS c1, Customers AS c2           WHERE c1.Region = c2.Region AND c1.ContactName <> c2.ContactName ORDER BY c1.Region, c1.ContactName;

Delete duplicate record

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateValueColumn1, DuplicateValueColumn2 and DuplicateValueColumn3.

DELETE FROM 	MyTable WHERE 	ID NOT IN	(SELECT 	MAX(ID)       FROM   		MyTable        GROUP BY 	DuplicatevalueColumn1, DuplicateValueColumn2, DuplicateValueColumn2

Delete Duplicate Records using SQL

By duplicate record I mean that every field in one record is identical to every field in a different record, i.e. a duplicate

is where there is no way of telling two or more records apart. If you just need to remove records which are similar (i.e. one

or more fields are identical but there are one or more fields which are different) then instead refer to how to delete similar

records.

To check that you have duplicate records in your table do the following:

select count(*) from MyTable

and

select distinct * from MyTable

unfortunately SQL does not permit a select count(distinct). You have duplicate records if the number of records returned by

the second query is less than the number of records returned by the first.

Unfortunately there is no way in SQL to delete one of these duplicates without deleting all of them. They are identical after

all, so there is no sql query that you could put together which could distinguish between them.

What you can do is to copy all the distinct records into a new table:

select distinct *

into NewTable

from MyTable

This query will create a new table (NewTable in my example) containing all the records in the original table but without any

records being duplicated. It will therefore preserve a single copy of those records which were duplicated.

Because this query creates (and populates) a new table, it will fail if the table already exists.

Delete Similar Records

To delete similar records, i.e. where the records are not the same but one field is the same and only one copy needs to be

preserved, try the following SQL:

delete from T1

from MyTable T1, MyTable T2

where T1.dupField = T2.dupField

and T1.uniqueField > T2.uniqueField

This will delete all records from the table MyTable which have the same value for the field dupField, leaving that record

which has the lowest value in uniqueField.

See the duplicate Record

Select sal, count(sal) as numoccurance;

or

Select sal,count(sal) as numoccurance having count(sal)>1