A - 16
Q 4- How to replace specific values from query result? i.e. how query return 'M' and 'F' can be converted into 'Male' and Female'.
A - This can be done using Oracle Decode function the syntax will be like
SELECT employee_name, DECODE(type,'M', 'Male', 'F', 'Female')type FROM employee;
Q 5 - If the column of the table is having case sensitive data like 'Abc' and 'ABC' and we want to do case sensitive search on table. How we can improve performance of case sensitive search?
A- In this case we can define case sensitive index on table so that each search is restricted to particular value compare to full table scan. You can force all your data to be the same case by using UPPER() or LOWER():
select * from my_table where upper(column_1) = upper('my_string');
select * from my_table where lower(column_1) = lower('my_string');
If column_1 is not indexed on upper(column_1) or lower(column_1), as appropriate, this may force a full table scan. In order to avoid this you can create a function-based index.
create index my_index on my_table ( lower(column_1) );
If you're using LIKE then you have to concatenate a % around the string you're searching for.
select * from my_table where lower(column_1) = lower('my_string') || '%';
- Failover (what happens when your main server fails?)
- Clustering (You may simply need to have more than one database server to handle your load)
- Processors (2? 4? 8? 16? Multi-core? Does the db provide multi-core support that is well optimized?)
- Memory (The faster the better, but your chipset must be able to handle the bandwidth as well)
- Disk (Faster I/O the better. eSATA/SATA or Fiber, etc.)
- Network (You'll need lots of bandwidth to handle this kind of traffic)
- Virtualization (Does it make sense to build this as real hardware? Why not as virtual servers in the cloud? Amazon / Rackspace / etc.?)
Q 9 - Remove duplicates from table?
Q 10 : Finding N'th Maximum salary SQL query?
//Another way to get 2'nd maximum salary Select max(Salary) From Employee e where e.sal < ( select max(sal) from employee );
Q 11 - Database Query Optimisation tips with JDBC:
Use the ResultSet method getString() sparingly
Avoid data type conversions
Q 12: What should be strategy to minimise the impact on external system which is using our table's data for processing?
Answer : Best strategy should be using database views to minimize the impact on external system, If we are changing our table structure or columns and if we are able to populate same data using View then there should not be any impact on external system and they will not require any change.
Spring MVC is mostly used with Spring for any web application development. It is very powerful and nice layered architecture for flow and c...
We are covering here -'Java garbage collection interview questions' or 'Java memory interview questions' in d...
Java Concurrency interview question - In year 2004 when technology gurus said innovation in Java is gone down and Sun Microsystems [Now Or...
JMS is used for asynchronous communication in java. This is reliable medium to communicate between applications. If the application is us...