SQL Quqries:

1) Searching Procedures in Database.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%foobar%'
AND ROUTINE_TYPE='PROCEDURE'

2) Searching DB objects contains the field 'TEI':
SELECT DISTINCT OBJECT_NAME(B.[OBJECT_ID]), A.[NAME] FROM SYS.COLUMNS A
INNER JOIN SYS.OBJECTS B
ON A.[OBJECT_ID] = B.[OBJECT_ID]
WHERE A.[NAME] LIKE '%TEI%'

3) Finding Duplicate in Table1 and Table2:
SELECT col1,col2,col3,COUNT(*)
FROM table1 A
INNER JOIN table2 B
ON A.col1 = B.col1
AND A.col2 = B.col2
GROUP BY col1,col2,col3
HAVING COUNT(*) > 1

4) Finding nth Max sal in EMP table.
EMP Table and find the 2nd height Salary?

Method1
select min(sal) from EMP p1 where 2=(select count(*) from EMP p2 where p1.EMPID>=p2.EMPID)
Method2
select max(sal) from (select top 2 sal from EMP order by sal asc) t

Interview 1 Questions
Unix:
1) How to Run Application in Back Ground Process?
./prog_name&
2) How to see the Running Back Ground Process?
?
3) How to find the File which have size > 100 KB?

Thanks,
Arun Mannepula

Crontab -Info

1. Crontab Restrictions
You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

2. Crontab Commands

export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e Edit your crontab file, or create one if it doesn’t already exist.
crontab -l Display your crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time you edited your crontab file. (This option is only available on a few systems.)

3. Crontab file
Crontab syntax :
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

* * * * * command to be executed - - - - - | | | | | | | | | +----- day of week (0 - 6) (Sunday=0) | | | +------- month (1 - 12) | | +--------- day of month (1 - 31) | +----------- hour (0 - 23) +------------- min (0 - 59)

* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).
Notes
A. ) Repeat pattern like /2 for every 2 minutes or /10 for every 10 minutes is not supported by all operating systems. If you try to use it and crontab complains it is probably not supported.

B.) The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

4. Crontab Example
A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.

30 18 * * * rm /home/someuser/tmp/*

Changing the parameter values as below will cause this command to run at different time schedule below :

min	hour	day/month	month	day/week	Execution time
30	0	1	1,6,12	*	– 00:30 Hrs on 1st of Jan, June & Dec.
0	20	*	10	1-5	–8.00 PM every weekday (Mon-Fri) only in Oct.
0	0	1,10,15	*	*	– midnight on 1st ,10th & 15th of month
5,10	0	10	*	1	– At 12.05,12.10 every Monday & on 10th of every month
:

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

5. Crontab Environment
cron invokes the command from the user’s HOME directory with the shell, (/usr/bin/sh).
cron supplies a default environment for every shell, defining:
HOME=user’s-home-directory
LOGNAME=user’s-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

6. Disable Email
By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1

7. Generate log file
To collect the cron execution execution log in a file :

30 18 * * * rm /home/someuser/tmp/* > /home/someuser/cronlogs/clean_tmp_dir.log