DataBase Interview Questions

Java Quick Notes

Refresh Your Java

We are Agile, believe in less Documentation - Only Quick notes of Java/J2ee Read more....

Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet
Not Included Yet

"Click on other Tabs for more Questions"  

"DataBase "

DataBase related quick notes:Normalization , Outer Join Vs Inner Join, Sample Stored Procedures, Tiggers etc.,

1 )   DataBase Normalization


  • DataBase Normalization
    Sample Img 1

DataBase Normalization Forms


Back to top

2 )   Database Outer Join VS Inner Join


  • Database Outer Join VS Inner Join
    Sample Img 2

An inner join of TableA and TableB gives the result of TableA intersect TableB, i.e. the
inner part of a venn diagram intersection.

An outer join of TableA and TableB gives the results of TableA union TableB, i.e. the
outer parts of a venn diagram union.

Suppose you have two Tables, with a single column each, and data as follows:
TableA    TableB
------   ------
1          3
2          4
3          5
4          6 
Note that (1,2) are unique to TableA, (3,4) are common, and (5,6) are unique to TableB.

Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables,
i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;

TableA | TableB
------   ---+--
3      | 3
4      | 4

Left outer join

TableA left outer join will give all rows in TableA, plus any common rows in TableB.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b(+);

TableA |  TableB
--     +-----
1      | null
2      | null
3      |    3
4      |    4

Full outer join
TableA full outer join will give you the union of TableA and TableB, i.e. All the rows
in TableA and all the rows in TableB. If something in TableA doesn't have a corresponding
data in TableB, then the TableB portion is null, and vice versa.

select * from TableA FULL OUTER JOIN TableB on a.a = b.b;

 TableA   |  TableB 
-----     +-----
   1      | null
   2      | null
   3      |    3
   4      |    4
null      |    6
null      |    5

Back to top

3 )   Query execution plan


  • Query execution plan
    Sample Img 3

A query plan (or query execution plan) is an ordered set of steps used to access or
modify information in a SQL relational database management system.

There are typically a large number of alternative ways to execute a given query, with
widely varying performance. When a query is submitted to the database, the query optimizer
evaluates some of the different, correct possible plans for executing the query
and returns what it considers the best alternative.

Please take a look at the sample Query Execution Plan above for the following Query.

SELECT  header.[SalesOrderID] ,header.[OrderDate] ,header.[ShipDate] ,details.[ProductID]
,details.[OrderQty] ,details.[UnitPrice] ,header.[CustomerID] FROM  
[Sales].[SalesOrderHeader] AS header JOIN [Sales].[SalesOrderDetail] AS details
ON header.[SalesOrderID] = details.[SalesOrderID] WHERE header.[CustomerID] = 29560;

If your application performamce is poor, this is one of the aspect you have to look at,
identify the time consuming Queries and review the execution plans of them and see
you could find an optimal one.

Back to top

4 )   Select statement Sample


Select distinct pub_id from titles order by type

Back to top

5 )   Sample Delete


delete authors where syb_identity = 4

Back to top

6 )   Sample Update


update salesdetail set discount = 40
        from salesdetail readpast
    where title_id like "BU1032"
        and qty > 100

Back to top

7 )   Sample Cursor ?


create procedure proc2 @flag int
if @flag > 0
    declare names_crsr cursor
    for select au_fname from authors

    declare names_crsr cursor
    for select au_lname from authors

Back to top

8 )   DataBase Tigger ?


A trigger executes automatically when a user attempts a specified data modification
statement on a specified table.

If the pub_id column of the publishers table is changed, make the corresponding
change in the titles table:

create trigger t2
on publishers
for update as
if update (pub_id) and @@rowcount = 1
    update titles
    set titles.pub_id = inserted.pub_id
    from titles, deleted, inserted
    where deleted.pub_id = titles.pub_id

Back to top

9 )   Drop And Alter



Drop :
drop table otherdb..newtable

Alter :

alter table titles
    modify notes varchar(150) not null
    with exp_row_size = 40

alter table titles
    add author_type varchar(30) null
    modify city varchar(30)
    drop notes
    add sec_advance money default 1000 not null
    lock datarows
    with exp_row_size = 40

Back to top

10 )   Sample query with Group By and Having ?


group by

select type, avg(price) from titles group by type order by avg(price)


sets conditions for the group by clause, similar to the way in which where sets
conditions for the select clause. having search conditions can include aggregate
expressions; otherwise, having search conditions are identical to where search

select pub_id, total = sum(total_sales)
from titles
where total_sales is not null
group by pub_id
having count(*)>5

Back to top

11 )   java.lang.IncompatibleClassChangeError


This Error occurs  when you compile a class with Different Version of JRE other than the one
 which this is being run(the Server on which you deploy this class files).

 For example, you compile a Java file by using Eclips which is using JRE 1.6, create a Jar file these class,
 and you deploy this Jar in a Env which is using JRE 1.4, this exception will be thrown.
Solution :

Please recompile this file with the correct version of JRE, in above case you have re-compile with JRE 1.4

Back to top

Not Included Yet
Not Included Yet

This Portal is intended to put all Java/J2ee related topics at one single place for quick referance, not only Technical , but also the Project Management Related thing such as Development Process methodoogies build process, unit testing etc.,

This Portal has More than 500 Java Interview Questions (also could be Considered as Quick Notes) very neatly separated topic by topic with simple diagrams which makes you easily understandable. Importantly these are from our Realtime expericance.

Face Book

Get a PDF

Face Book
Same look (Read) on any device, this is Ads free

Go To Site Map