Archive

Author Archive

Synchronization

January 20, 2016 Leave a comment

When a thread starts working on some critical work he locks the room (say monitor) using synchronized keyword. So no other thread can enter into the room until the current thread finish his work.

You can synchronized either an object or a method in following ways

1. Synchronize an object

public void run() {
	synchronized(target) { // synchronized object
	:
	}
}

2. Synchronize a method

public synchronized void myMethod() {
	:
}
//or
public void myMethod() {
	Synchronized(this){
	:
	}
}

Note :

If you are synchronizing a static method then it’ll take lock over complete class.
If you place synchronized block over the same object on multiple places and one of the thread had already entered into one of them, no other thread can enter into any of the synchronized block until first thread comes out.

public class SynchoTest {
	private Integer a = 10;

	public void acquire(){
		synchronized(a){
			print("acquire()");
			try{
				Thread.sleep(10000);
				print("I have awoken" + a);
			}catch(InterruptedException e){
				e.printStackTrace();
			}
		}
		print("Leaving acquire()");
	}

	public void modify(int n){
		this.a=n;
		print("new value" + a);
	}

	public void display(){
		print("a=" + a);
	}
	:
}

For above program, I am starting 3 threads. Thread A calls method acquire(), Thread B calls modify(97), and Thread C calls display().

Output:

2012-03-06 07:48:10.038 :: A: acquire()
2012-03-06 07:48:10.038 :: B: new value97
2012-03-06 07:48:10.038 :: C: a=97
2012-03-06 07:48:20.054 :: A: I have awoken
2012-03-06 07:48:20.054 :: A: 97
2012-03-06 07:48:20.054 :: A: Leaving acquire()

What have you noticed in above output? When A entered in his critical section, a’s value was 10. But when it went to print it, B already changed a’s value. So A prints new value of a ie 97.

Now do some small changes

public void modify(int n){
	print("Entered in modfy");
	synchronized(a){
		this.a=n;
		print("new value" + a);
	}
}

Output:

2012-03-06 07:51:46.19 :: A: acquire()
2012-03-06 07:51:46.19 :: B: Entered in modfy
2012-03-06 07:51:46.19 :: C: a=10
2012-03-06 07:51:56.206 :: A: I have awoken
2012-03-06 07:51:56.206 :: A: 10
2012-03-06 07:51:56.206 :: A: Leaving acquire()
2012-03-06 07:51:56.206 :: B: new value97

This time everything is fine. why so?

If there are many synchronized blocks on same object and any thread has entered into one of the synchronized block then no other thread can enter into any synchronized block on the same object. synchronized checks happens-before condition then either it acquire lock or waits until another thread releases lock or get interrupted.

synchronized blocks on different-2 objects has no interrelation. Any thread can enters into any synchronized block. Synchronized(a) and synchronized(this) are also non-related.
Lets play with some more code:

public void modify(int n){
	this.a=n;
	print("new value" + a);
}
public void display (int n){
	print("a=" + a);
	synchronized(a){
		try{
			Thread.sleep(1000);
			print("a=" + a);
		}catch(InterruptedException e){
			e.printStackTrace();
		}
	}
}

Output:

a=34
new value45
a=45

Code Postmortem

Thread 1 inserts into synchronized block of display(). It takes lock on a. Thread 2 insert into modify(). But there is no new synchronized block. So it doesn’t check whether some thread has taken lock on a before. So it allows modification in a. Thus display() prints modified value of a.

If I makes modify() synchronized even, it doesn’t change the output.

public synchronized void modify(int n){
	this.a=n;
	print("new value" + a);
}

Because making modify() synchronized ensures that no more than 1 thread can call modify() at a time. It is not related to synchronized(a).

Lets read some more important points about synchronization.

If you are starting threads on 2 separate objects then there is no relation between those threads because both objects use different memory address. Hence their synchronized blocks are also not related.
synchronized(a) should not contain any statement modifying value of a. You’ll notice it when you’ll read about wait(),notify() etc. However it is allowed.

Synchronizing a method or using synchronized(this) everywhere in class is not good practice. Because it blocks all threads to access any synchronized block. Taking lock on necessary field is always a good practice.

Deadlock

I tried to code many programs which can help me to understand race condition, starvation, and deadlock practically. But I failed. Unknowingly I made this program which creates deadlock due improper synchronization.

public synchronized void acquire() throws Exception{
	Thread.sleep(5000);
	synchronized(lock){
		wait(1);
	}
}

public synchronized void modify() throws Exception{
	Thread.sleep(5000);
	synchronized(lock){
		wait(1);
	}
}

I added a tracer who explained me why both threads were getting blocked. You can integrate separate java thread tracer with your other threading programs.

Refer the output below to understand how improper nested monitoring took lock.

Output:

A:8
B:9
2012-03-07 19:54:57.625 :: [A:RUNNABLE,B:RUNNABLE]
2012-03-07 19:54:57.788 :: [A:RUNNABLE,B:BLOCKED]
2012-03-07 19:54:57.804 :: [A:TIMED_WAITING,B:BLOCKED]
2012-03-07 20:54:59.888 :: [A:RUNNABLE,B:BLOCKED]
2012-03-07 19:55:02.787 :: [A:TIMED_WAITING,B:TIMED_WAITING]
2012-03-07 19:55:02.788 :: [A:BLOCKED,B:TIMED_WAITING]
2012-03-07 19:55:07.787 :: [A:BLOCKED,B:RUNNABLE]
2012-03-07 19:55:07.787 :: [A:BLOCKED,B:BLOCKED]
Both threads are blocked
Deadlock detected in
Thread id: 9
Thread id: 8

Proper synchronization:

public void acquire() throws Exception{
	Thread.sleep(5000);
	synchronized(lock){
		lock.wait(1);
	}
}

public void modify() throws Exception{
	Thread.sleep(5000);
	synchronized(lock){
		lock.wait(1);
	}
}

You can solve this deadlock in another way as well. Its up to your need.

Categories: Java, Multithreading

Java Multithreading – Notify & Wait

January 20, 2016 Leave a comment

Read about synchronization first to understand this article better.

notify() or notifyAll(), and wait() must be in a synchronized block for the object you are waiting on. So the following codes are correct;

public synchronized void method(){
	wait(); //or this.wait();
}

public void method(){
	synchronized(this){
		wait(); //or this.wait();
	}
}

public void method(){
	synchronized(a){
		a.wait();
	}
}

public synchronized void method(){
	synchronized(a){
		wait();
	}
}

But following code generates run time exception java.lang.IllegalMonitorStateException

public void method(){
	synchronized(a){
		wait();
	}
}

public void method(){
	synchronized(a){
		synchronized(this){
			wait(n);
		}
	}
}

public synchronized void method(){
	synchronized(a){
		wait(n);
	}
}

If you change synchronized object inside synchronized block notify() or notifyAll(), and wait() will give run time exception java.lang.IllegalMonitorStateException.

synchronized(a){
	a = 55;
	a.wait();
}

In above example, you are waiting on new copy of a while synchronizing on old copy of a. Below code doesn’t generate exception.

synchronized(a){
	a.wait();
	a = 55;
}

Because you are waiting on same copy of a, you are synchronizing on. Then you are changing a.

Thread.sleep(1000) asks current thread to wait till specified period. It doesn’t bother about locks and all. So it doesn’t releases lock. On the other hand, wait() releases lock and acquires lock again once notify() or notifyAll() get called on same object or specified time is over.

public void acquire(){
	synchronized(a){
		print("acquire()");
		try{
			a.wait(5000);
			print("I have awoken");
			print(":" + a);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	print("Leaving acquire()");
}

public void modify(int n){
	print("Entered in modify");
	synchronized(a){
		try{
			a.wait(3000);
			this.a=n;
			print("new value " + a);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}

Output:
2012-03-06 19:51:27.969 :: A: acquire()
2012-03-06 19:51:27.969 :: B: Entered in modify
2012-03-06 19:51:32.992 :: B: new value 97
2012-03-06 19:51:32.992 :: A: I have awoken
2012-03-06 19:51:32.992 :: A: 97
2012-03-06 19:51:32.992 :: A: Leaving acquire()

Explanation:

Thread A: enters into monitor of a.
Thread B: waits to get entered into monitor of a.
Thread A: meets to a.wait(5000). So it releases lock on a.
Thread B: enters into monitor a. waits and acquires lock on a.
Thread A: try to acquire released lock. But it is already acquired by Thread B. So it waits.
Thread B: changes value of a.
Thread A: acquire lock on a. It prints new value of a.
On the other hand, if I use Thread.sleep(n) in place of wait(n)

public void acquire(){
	synchronized(a){
		print("acquire()");
		try{
			//a.wait(5000);
			Thread.sleep(5000);
			print("I have awoken");
			print(":" + a);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	print("Leaving acquire()");
}

Output:
2012-03-06 20:02:39.395 :: A: acquire()
2012-03-06 20:02:39.395 :: B: Entered in modify
2012-03-06 20:02:44.418 :: A: I have awoken
2012-03-06 20:02:44.418 :: A: 10
2012-03-06 20:02:44.418 :: A: Leaving acquire()
2012-03-06 20:02:49.427 :: B: new value 97

Explanation:
When Thread A meets to sleep(n) it doesn’t release lock.

The problem with calling wait() and notify() on the empty string, or any other constant string is, that the JVM/Compiler internally translates constant strings into the same object. That means, that even if you have two different MyWaitNotify instances, they both reference the same empty string instance. This also means that threads calling wait() on the first MyWaitNotify instance risk being awakened by notify() calls on the second MyWaitNotify instance.
If there are multiple threads waiting on same object, notify() can awake any thread randomly. It arises the condition that some threads never get awaken and some always get awaken. This situation is called Starvation. Sometimes some greedy threads don’t release resources (call sleep() instead of wait() etc). It also force other threads to wait. Some threads increase their priority to get served first to CPU, it also force low priority threads to wait for long. These all conditions where any thread need to wait very long is called Starvation.

 

Categories: Multithreading

Java Multithreading

January 19, 2016 Leave a comment

I, originally, wrote this article 4 years ago. And all the examples are based of Java 6. I don’t have any liability If any example fails due to change in java libraries.

There are 3 ways to create threads in java;

    • Extends Thread class
class ConcreteClass extends Thread {
	public void run() {
	:
	}
}

ConcreteClass cObj = new ConcreteClass();
cObj.start();
    • Implements Runnable Interface and pass it’s object to Thread class
    • Implements Callable interface and pass it’s object to Thread class
class ConcreteClass implements Runnable {
	public void run() {
	:
	}
}

ConcreteClass cObj = new ConcreteClass();
Thread T1 = new Thread(cObj,"my Thread");
T1.start();

Important Terms

Monitor
it ensures that only one thread can enter in its critical section. It is associated with every object in java. Synchronized acquires a monitor for a thread. It is also called intrinsic or monitor lock.

method(
	:
	synchronized(this){
		:
	}
}

method2(
	synchronized(this){

	}
	:
}

Starvation
When a thread waits too long.

  • If there are multiple threads waiting on same object, notify() can awake any thread randomly. It arises the condition that some threads never get awaken and some always get awaken.
  • Sometimes some greedy threads don’t release resources (call sleep() instead of wait() etc). It also force other threads to wait.
  • Some threads increase their priority to get served first to CPU, it also force low priority threads to wait for long.

Slipped condition 
from the time a thread has checked a certain condition until it acts upon it, the condition has been changed by another thread.

method(
	:
	while(amount < required){
		synchronized(filler){
			filler.notify();
		}
		wait();
	}
	amount -= required;
	:
}

Race condition
A race condition occurs when two or more threads can access shared data and they try to change it at the same time. Both threads are “racing” to access/change the data. We don’t know who will win.

It can be avoided by creating lock over the shared data.

DeadLock
When all threads waiting outside the door closed by another thread. And this chain is either circular or ending on resource which cant be assigned to any thread due to some reasons. (Might be because it is held by some devil thread).

Nested Monitor Lockout
Lock inside lock. wait() releases one of the lock but not all. It results deadlock.

method(
	synchronized(this){
		while(flag){
			synchronized(a){
				a.wait();
			}
		}
	}
}

method2(
	synchronized(this){
		flag = false;
		synchronized(a){
			a.notify();
		}
	}
}

Guarded/loop/spin lock
Loop & Wait until the condition becomes false.

while(condition){
	wait();
}

Reentrance Lock
When one thread calls a synchronized block inside from another synchronized block on the same object. (when a thread reenters in its monitor)

public synchronized outer(){
	inner();
}

public synchronized inner(){
	//do something
}

It is allowed for same thread. But if 2 separate threads access 2 separate synchronized blocks, then one of them will have to wait.

Daemon Thread
A thread that has no other role in life than to serve others. For example Timer thread, garbage collector. JVM stop executing a program if only daemon.

To make a thread daemon in java just call setDaemon(true) on a thread.

	private static class MyDaemonThread extends Thread {

		public MyDaemonThread() {
			setDaemon(true);
		}

		@Override
		public void run() {
			while (true) {
				try {
					Thread.sleep(500);
				} catch (InterruptedException e) {
				}
			}
		}
	}

	public static void main(String[] args) throws InterruptedException {
		Thread thread = new MyDaemonThread();
		thread.start();
		:
	}

Livelock
A livelock is a situation in which two or more threads block each other by responding to an action that is caused by another thread. In contrast to a deadlock situation, where two or more threads wait in one specific state, the threads that participate in a livelock change their state in a way that prevents progress on their regular work.

If two threads detect a deadlock, and try to “step aside” for each other, without care they will end up being stuck in a loop always “stepping aside” and never managing to move forwards.

By “step aside” I mean that they would release the lock and attempt to let the other one acquire it.

Example:

  • Two persons passing each other in a narrow gallery.
  • A husband and wife are trying to eat soup, but only have one spoon between them. Each spouse is too polite, and will pass the spoon if the other has not yet eaten.

Reference

Categories: Multithreading

AWK

January 19, 2016 Leave a comment

An AWK command can be broken in three parts.

Syntax

BEGIN { print "START" }
      { print         }
END   { print "STOP"  }

BEGIN, END blocks are optional and run only once. While, in the other hand, the middle block is run for every line of given file. This is the bock where actual processing logic is written.

You can use BEGIN block to set initial parameters, while you can use END block to print result as summary.

Eg.

1. To print number of lines in a file [one time only]

awk 'END{print NR}' filename

2. To Count number of entries for a month

dates.txt
10-Jul-10
23-Jul-10
:
31-Jul-10
1-Aug-10
:
4-Aug-10
5-Aug-10

awk 'BEGIN{FS="-";OFS=","}{$1="";print substr($0,2,length($0))}' dates.txt
| sort |
awk 'BEGIN{
	lastline=$0;
	count=1;
}
{
	if(lastline==$0){
		count+=1;
	}
	else{
		print lastline": "count;
		lastline=$0;
		count=1;
	}
}
END{print lastline": "count;}'

Output:
Aug,10: 5
Jul,10: 22

3. To Remove all spaces & tabs from all fields of a file

awk 'BEGIN {FS="^";OFS="^";}{for (i=1; i<=NF; i++) {gsub(/^[ \t]+|[ \t]+$/,"",$i);} print }' filename > file_name_new
Categories: Tutorial Tags: ,

SQL – N rows

January 19, 2016 Leave a comment

Fetch starting N rows

DB2

select *
from (select rownumber() over(order by <colName>) as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num < N+1 with ur

Oracle

select *
from (select rowid as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num < N+1

Some DBs has function like Top and limit

Fetch Nth row

DB2

select *
from (select rownumber() over(order by <colName>) as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num = N with ur

Oracle

select *
from (select rowid as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num = N

Fetch Nth to Mth rows

DB2

select *
from (select rownumber() over(order by <colName>) as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num between 1003 and 1118 with ur

Oracle

select *
from (select rowid as row_num, <colName2>
from
<table name>)
as <alias name>
where row_num between 1003 and 1118
Categories: SQL Tags: , ,

SQL exercise – Learn by practice

January 19, 2016 1 comment

There is an example , some questions, and corresponding queries. These queries are written in Oracle. But it’ll give you clear logic about how to write SQL queries in any sort of SQL like DB2, TSQL, MySql etc. I constructed this exercise during my training. Although Some questions do not having solution. So those will be an exercise for you. If i get time then will update them later.

First of all you need to create tables in your database. Use above “ready to use” code.

--################ DDL #######################
 create table stock
 (item_no varchar2(5) primary key,
 item_name varchar2(15) not null,
 Quoted_price number(6,2),
 Category varchar2(5));
create table category
 (c_code varchar2(5) primary key,
 c_name varchar2(15) not null unique);
alter table stock
 add constraint s_fk Foreign key(category) references Category(c_code) on delete cascade ;
create table purchase
 (p_no varchar2(5) PRIMARY KEY,
 item_no varchar2(5) references stock(item_no),
 p_price number(6,2) not null,
 p_date date,
 p_qty number(3));
create table sale
 (s_no varchar2(5) PRIMARY KEY,
 item_no varchar2(5) references stock(item_no),
 s_price number(6,2) not null,
 s_date date,
 s_qty number(3),
 cust_name varchar2(15)) ;

Now you need to populate your tables with some dummy but meaningful data. So you can test your queries.

--############### DML ##################### BEGIN insert into CATEGORY VALUES('C101','FOOD'); insert into CATEGORY VALUES('C102','SPORT'); insert into CATEGORY VALUES('C103','ELEX'); insert into CATEGORY VALUES('C104','CLOTH'); insert into CATEGORY VALUES('C105','OTHER'); END ;
BEGIN
 insert into stock values('i101','BREAD',17,'C101');
 insert into stock values('i102','BUTTER',67,'C101');
 insert into stock values('i103','BISCUIT',87,'C101');
 insert into stock values('i104','GEM',56,'C101');
 insert into stock values('i105','CHOCOLATE',17,'C101');
 insert into stock values('i106','EGG',5,'C101');
 insert into stock values('i107','SALT',8,'C101');
 insert into stock values('i108','BALL',27,'C102');
 insert into stock values('i109','BAT',217,'C102');
 insert into stock values('i110','FOOTBALL',117,'C102');
 insert into stock values('i111','WICKETS',97,'C102');
 insert into stock values('i112','CAP',111,'C102');
 insert into stock values('i113','TSHIRT',1011,'C104');
 END
BEGIN
 insert into PURCHASE values('P101','i101',15,'01-nov-2008',16);
 insert into PURCHASE values('P102','i102',65,'01-nov-2008',34);
 insert into PURCHASE values('P103','i103',85,'01-nov-2008',12);
 insert into PURCHASE values('P104','i104',56,'01-nov-2008',35);
 insert into PURCHASE values('P105','i105',17,'01-nov-2008',23);
 insert into PURCHASE values('P106','i106',4,'01-nov-2008',34);
 insert into PURCHASE values('P107','i107',7,'01-oct-2008',23);
 insert into PURCHASE values('P108','i108',25,'01-oct-2008',56);
 insert into PURCHASE values('P109','i109',201,'01-sep-2008',67);
 insert into PURCHASE values('P110','i110',100,'01-oct-2008',54);
 insert into PURCHASE values('P111','i111',90,'01-mar-2008',23);
 insert into PURCHASE values('P112','i112',100,'01-feb-2008',12);
 insert into PURCHASE values('P113','i113',800,'01-mar-2008',6);
 END;
BEGIN
 insert into SALE values('S101','i101',19,'01-DEC-2008',6,'AMIT');
 insert into SALE values('S102','i102',65,'02-DEC-2008',14,'ROHIT');
 insert into SALE values('S103','i103',86,'05-DEC-2008',2,'AMIT');
 insert into SALE values('S104','i104',63,'27-nov-2008',12,'SHYAM');
 insert into SALE values('S105','i105',21,'16-nov-2008',3,'KAMAL');
 insert into SALE values('S106','i106',6,'13-DEC-2008',4,'PREETI');
 insert into SALE values('S107','i103',88,'17-nov-2008',2,'RAHUL');
 insert into SALE values('S108','i104',57,'19-nov-2008',5,'RAHUL');
 insert into SALE values('S109','i105',19,'28-nov-2008',2,'SOHAN');
 insert into SALE values('S110','i111',115,'12-DEC-2008',3,'SHAALU');
 insert into SALE values('S111','i112',145,'17-NOV-2008',8,'SHAALU');
 insert into SALE values('S113','i113',1300,'17-FEB-2008',2,'UMESH');
 insert into SALE values('S112','i112',120,'01-MAR-2008',4,'SHYAM');
 insert into SALE values('S114','i112',130,'06-OCT-2008',2,'KAMAL');
 END;

Now this is the time to practice. See given examples and solved queries. First try all queries given below. Then login to this article-stack to find out their solution.

1) LIST OF ITEMS OF ‘SPORT’ CATEGORY

A)

SELECT *
FROM STOCK , (SELECT * FROM CATEGORY
WHERE CATEGORY.C_NAME='SPORT') CAT
WHERE CAT.C_CODE=STOCK.CATEGORY;

B)

SELECT * FROM STOCK JOIN CATEGORY
ON (STOCK.CATEGORY=CATEGORY.C_CODE
AND CATEGORY.C_NAME='SPORT')

2) LIST OF ITEMS OF ‘SPORT’ & ‘FOOD’ CATEGORIES

A)

SELECT * FROM STOCK JOIN CATEGORY
ON (STOCK.CATEGORY=CATEGORY.C_CODE
AND CATEGORY.C_NAME in ('SPORT','FOOD'))

3) LIST OF ITEMS OF ‘SPORT’ CATEGORY, whose PRICE IS greater than average PRICE of all items.

select * from (select * from STOCK
WHERE STOCK.QUOTED_PRICE > ( SELECT AVG(QUOTED_PRICE)
FROM STOCK)) STK
JOIN CATEGORY ON (CATEGORY.C_CODE=STK.CATEGORY
AND CATEGORY.C_NAME='SPORT')

4) LIST OF SOLD ‘SPORT’ ITEMS.

a)

select * from sale NATURAL JOIN
(select * from stock JOIN category ON
(stock.category=category.c_code
and category.c_name='SPORT'));

b)

select *
from sale JOIN STOCK
ON (SALE.ITEM_NO=STOCK.ITEM_NO) JOIN CATEGORY
ON (stock.category=category.c_code and category.c_name='SPORT');


C)

SELECT *
FROM SALE NATURAL JOIN STOCK
JOIN CATEGORY
ON (CATEGORY.C_CODE=STOCK.CATEGORY
AND CATEGORY.C_NAME='FOOD')

5) LIST OF ITEMS SOLD TO ‘AMIT’.

A)

SELECT *
FROM STOCK JOIN SALE
ON ( STOCK.ITEM_NO=SALE.ITEM_NO AND SALE.CUST_NAME='AMIT');

B)

SELECT *
FROM STOCK NATURAL JOIN SALE
WHERE SALE.CUST_NAME='AMIT'

6) LIST OF ITEMS SOLD IN LAST WEEK.

SELECT *
FROM SALE NATURAL JOIN STOCK
WHERE SALE.S_DATE > (CURRENT_DATE-7)

7) LIST OF ‘FOOD’ ITEMS SOLD IN LAST WEEK.

SELECT *
FROM SALE NATURAL JOIN STOCK
JOIN CATEGORY
ON (CATEGORY.C_CODE=STOCK.CATEGORY
AND CATEGORY.C_NAME='FOOD')
WHERE SALE.S_DATE > (CURRENT_DATE-7)

8 ) LIST OF ITEMS THOSE ARE SOLD TO GAIN MAX PROFIT.

with price_diff as
(
Select s.item_no item_no,(s_price - p_price) diff
from
	(select ITEM_NO,avg(S_PRICE) s_price
	from sale
	group by ITEM_NO) s
join
	(select ITEM_NO,avg(P_PRICE) p_price
	from purchase
	group by ITEM_NO) p
on (p.item_no = s.item_no)
)
select item_no,diff
from price_diff
where diff = (select max(diff) from price_diff)
;

9) LIST OF ‘FOOD’ ITEMS PURCHASED 1 MONTH AGO.

 SELECT *
 FROM PURCHASE NATURAL JOIN STOCK
 WHERE PURCHASE.P_DATE < ADD_MONTHS(CURRENT_DATE,-1)

10) LIST OF ‘FOOD’ ITEMS THOSE ARE NOT SOLD IN LAST WEEK.

SELECT *
FROM SALE NATURAL JOIN STOCK
JOIN CATEGORY
ON (CATEGORY.C_CODE=STOCK.CATEGORY
AND CATEGORY.C_NAME='FOOD')
WHERE SALE.S_DATE < (CURRENT_DATE-7)

11) LIST OF CUSTOMERS WHO PURCHASED ‘FOOD’ ITEMS BUT NOT ‘SPORT’ OR ‘CLOTH’ ITEMS.

A)

select distinct cust_name
from sale s1 
join
	(select *
	from stock join category
	on (Category= c_code and c_name in ('FOOD'))
	) s2
on (s1.item_no = s2.item_no )
where cust_name not in (
	select distinct cust_name
	from sale s1 
	join
		(select *
		from stock 
		join category
		on (Category= c_code and c_name in ('SPORT','CLOTH'))
		) s2
	on (s1.item_no = s2.item_no )
	)

B)

with cust_cat as
(select cust_name, c_name
from sale s1 
join
	(select item_no,c_name
	from stock join category
	on (Category= c_code)
	) s2
on (s1.item_no = s2.item_no )
group by cust_name, c_name
)
select cust_name
from cust_cat
where c_name = 'FOOD' and cust_name not in
	(select cust_name
	from cust_cat
	where c_name in ('SPORT','CLOTH')
	);

12) LIST OF CUSTOMERS WHO PURCHASE ITEMS OF SINGLE CATEGORY ONLY.

select cust_name
from sale s1 
join
	(select *
	from stock join category
	on (Category= c_code)
	) s2
on (s1.item_no = s2.item_no )
group by cust_name
having count(c_name) = 1

13) LIST OF SOLD ITEMS WHOESE QUOTED PRICE > 30.

SELECT *
FROM SALE NATURAL JOIN STOCK
WHERE STOCK.QUOTED_PRICE > 30;

14) LIST OF ITEMS OF ‘SPORT’ CATEGORY, whose PRICE IS greater than average PRICE of all items.

select *
from stock join category
on (Category = c_code and c_name in ('SPORT') )
where Quoted_price > (select avg(Quoted_price) from stock);

15) LIST OF SOLD ‘SPORT’ ITEMS.

Select *
from sale s1 join
(select *
from stock join category
on (Category= c_code and c_name in ('SPORT')) ) s2
on (s1.item_no = s2.item_no);
Categories: SQL, Tutorial Tags: ,

Good links to start experimenting with lenses in minimum expense

April 26, 2013 Leave a comment
Categories: tips & tricks Tags: ,

Easiest way to learn Spring. Link of best tutorials

November 22, 2012 Leave a comment

If you are completely blank about Spring framework then I’ll suggest you to visit following tutorials link of video tutorial;

Video tutorials are always best. And the above mentioned tutorials, covering basic step-by-step, fits to novice learning. But after watching 10-12 videos, you’ll realize that there many things being repeated within a tutorial, and so slow. You can actually learn them a bit early. However, it is really good to start.

Now you’ll need something in written which can save your time to make a project. So these tutorials will help you a lot. Read in sequence, please.

 

Quick jump with Spring 3 – Theory apart
Dependency Injection – just in 2 mins
Spring 3 Autowiring – Theory apart
Spring 3 Bean Lifecycle and scope – Theory apart
Customize initialization of Spring bean through BeanPostProcessor – Theory apart

However, I strongly suggest to write your own code instead of referencing code available on net because it limits your thinking.

I’ll update more links further. If you some more good resources which are covering rest spring portion then keep commenting.

Categories: Spring Tags: , ,

Why not timthumb but amtythumb

April 12, 2012 Leave a comment

Timthumb is a great script, written in PHP for image editing. However I strongly suggest amtyThumb for WordPress users due to some very basic reasons.

  1. amtyThumb is specially designed for WordPress user.
  2. It automatically extract 1st image from an article and resize it as per need.
  3. It requires post id instead of image URL. So no other sites can use script to steal your bandwidth.
  4. Like timThumb, you need not to specify allowed websites.
  5. It is light weighted and uses caching. Caching saves time to extract an image from the post and resizing it.
  6. You can cache/re-cache images for selected or all posts from admin page.
  7. It has an option page where you can test the plugin.

Home URL for amtythumb

Latest version of amtyThumb ie 8.0 doesn’t depend on any external plugin. So just install and enjoy.

Java Collection Framework perfect doodle cheat sheet

November 22, 2011 Leave a comment

I googled for “java collection cheat sheet” sometimes ago. And I found single one having a class diagram showing an inheritance tree of many classes of java collection framework. In the header part, there was some little discussion about some collection classes.

But that sheet were neither pointing out small aspects of any class nor comparing any class with other to decide which class may suite to someones project.

Finally there is a Java Collection Doodle Cheat Sheet. This sheet has comparison of 15+ collection classes using flow chart. So anyone can choose the best collection class to their project.

This sheet also has small aspects of most collection classes.

Refer the below screen shot from that sheet. And visit original article to download the sheet.

Java collection doodle cheat sheet

 

Java collection doodle cheat sheet flow chart

Categories: Java, Tutorial Tags: , ,
%d bloggers like this: