Java Quick Notes

Ans)

• 
  Sample Img 1

DataBase Normalization Forms

Back to top

Ans)

• 
  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.

Examples
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

Ans)

• 
  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

Ans)

Select distinct pub_id from titles order by type

Back to top

Ans)

delete authors where syb_identity = 4

Back to top

Ans)

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

Back to top

Ans)

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

Back to top

Ans)

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
begin
    update titles
    set titles.pub_id = inserted.pub_id
    from titles, deleted, inserted
    where deleted.pub_id = titles.pub_id
end

Back to top

Ans)

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

Ans)

group by

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

having

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
conditions.

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

Ans)

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