Prasanna Says

Saturday, March 15, 2008

Sql Part II

Sql Part II

Lets see some of the frequently asked queries in the interview.


1. To find The Nth Maximum Salary.


SELECT DISTINCT SAL
FROM EMP A
WHERE &N=(SELECT COUNT (DISTINCT B.SAL) FROM EMP B WHERE A.SAL<=B.SAL);


2. To use Exists Clause. Correlated subquery

SELECT DNAME, DEPTNO
FROM DEPT
WHERE EXISTS (SELECT * FROM EMP WHERE
DEPT.DEPTNO = EMP.DEPTNO)


3. To Find The Not Null Column Alone In A Table.

SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE NULLABLE = 'N'
AND TABLE_NAME = 'COUNTRY'


4.To delete The Duplicate Rows Alone in A Table. (frequently asked question)

DELETE DEPT WHERE ROWID NOT IN (SELECT MAX (ROWID)
FROM DEPT GROUP BY DEPTNO HAVING COUNT (*) >=1)


5.To find The Max Salary without MAX Function.

1. SELECT DISTINCT SAL
FROM EMP1 WHERE SAL NOT IN
(SELECT SAL FROM EMP1 WHERE SAL < ANY (SELECT SAL FROM EMP1))


2. SELECT SAL FROM EMP WHERE SAL >= ALL (SELECT SAL FROM EMP)



6. To find the database name

select name from v$database;


7. To convert the given number to word

SELECT TO_CHAR (TO_DATE (&NUM,'J'),'JSP') FROM DUAL;

8. What is the difference between unique and distinct keyword in Oracle.

To summarise:

True:
* Only one primary key is allowed per table (any number of unique keys are allowed).
* Primary key columns automatically become NOT NULL (unique keys can contain nulls).
* UNIQUE and DISTINCT are synonymous in a SELECT list.

Not true:
* A unique key cannot be referenced in a foreign key definition (it can)
* A primary key will have a unique index (it might not)
* DISTINCT and UNIQUE handle LOBs differently (they do not)
* UNIQUE is Oracle syntax while DISTINCT is ANSI (both appear to be ANSI-compliant according to the SQL*Plus flagger option, although I couldn't find this standard documented. Oracle did not support SELECT UNIQUE until 8.1.5.)



9. What is the difference between union and union all

UNION
1. returns all distinct rows selected by either query
2. does the multi level sorting on data starting from first column
3. due to sorting query performance goes down dramatically.

UNION ALL
1. returns all the rows selected by either query including all duplicates.
2. No sorting is applied on the data.
3. No performance issue in this set operation. And it is advisable to use union all instead of union where ever applicable.


Also visit : Sql Part 1

Saturday, March 8, 2008

Battery Life

Seven Ways to Improve Cell phone Battery Life


  1. Turn the phone off. This is probably the most effective and
    simplest way of conserving your battery’s power. If you don't plan on
    answering the phone while you're sleeping or after business hours, just
    turn it off. Do the same if you are in an area with no reception (such
    as a subway or remote area) or in a roaming area, since constantly
    searching for service depletes the battery fairly quickly. Some phones
    have an automatic power save feature, but it takes about 30 minutes
    with no service to kick in. By then, much battery power has been used.

  2. Stop searching for a signal. When you are in an area with poor or
    no signal, your phone will constantly look for a better connection, and
    will use up all your power doing so. This is easily understood if you
    have ever forgotten to turn off your phone on a flight or train. The
    best way to ensure longer battery life is to make sure you have a great
    signal where you use your phone.


  3. Get the most out of your battery. Switch off the vibrate function
    on your phone, and use just the ring tone instead. The vibrate function
    uses up a lot of battery power. Keep the ring tone volume as low as
    possible.

  4. Turn off your phone's back light. The back light is what makes
    the phone easier to read in bright light or outside. However, the light
    also uses battery power. If you can get by without it, your battery
    will last longer. If you have to use the back light, many phones will
    let you set the amount of time to leave the back light on. Shorten that
    amount of time. Usually, one or two seconds will be sufficient. Some
    phones have an ambient light sensor, which can turn off the back light
    in bright conditions and enable it in darker ones.

  5. Avoid using unnecessary features. If you know it will be a while
    before your phone’s next charge, don’t use the camera or connect to the
    Internet. Flash photography can drain your battery especially quickly.
    If your phone has bluetooth capability, disable it when not in use.

  6. Keep calls short. This is obvious, but how many times have you
    heard someone on their cell phone say, "I think my battery’s dying,"
    and then continue their conversation for several minutes? Sometimes,
    the dying battery is just an excuse to get off the phone (and a good
    one, at that), but if you really need to conserve the battery, limit
    your talk time.

  7. Don't keep the animated background picture. That will reduce your
    battery life by 25 percent.


Obesity

Obesity Can Cause Cancer


Obesity is on its way to being deadlier than smoking as a cause of cancer, a leading researcher said.

Being obese is currently associated with about 14 percent of cancer deaths in men and 20 percent in women, compared with about 30 percent each for smoking, Dr. Walter C. Willett of the Harvard School of Public Health, told the annual meeting of the American Association for the Advancement of Science.

"As smoking goes down and obesity goes up it won't be long before obesity is the No. 1 cancer killer," Willett said at a symposium on cancer prevention.

Added Dr. Douglas R. Lowy of the National Cancer Institute: "Cancer prevention begins at home. ... Not all of us always act in our own best interest."

Willett said research is producing increasing evidence associating obesity with a variety of cancers, including breast, colorectal, liver, pancreas and gallbladder. Alcohol is also associated with certain cancers, he said.

In the 1980s, researchers focused on the amount of fat people ate as a probable cause of cancer, but studies did not strongly support that. Later they turned to diets high in fruits and vegetables as a way to reduce cancer, but again, Willett said, they struggled to find convincing evidence in studies.

Now attention has turned to obesity, and more and more research is providing evidence that indict that as a cancer cause.

That does not mean people should stop eating fruit and vegetables and go to a high-fat diet, he quickly added. "We do see evidence of a benefit for heart disease, I think that's pretty real," he said.

And, he noted, studies have indicated some benefit from a high fruit and vegetable diet in some cancers, he added, including mouth, esophagus, lung and stomach.

Overall, Willett estimated 30 percent to 35 percent of cancers are due to nutritional factors, much of it to obesity.

For More Information visit the following sites

http://www.obesityfocused.com/


http://www.obesityfocused.com/articles/obesity-and-cancer/index.php