interview tips

Perl 概念,语法,特性

 

Why do you use Perl?

• Perl is a powerful free interpreter.
• Perl is portable, flexible and easy to learn.

How do I set environment variables in Perl programs?

you can just do something like this:
$path = $ENV{'PATH'};
As you may remember, "%ENV" is a special hash in Perl that contains the value of all your environment variables.
Because %ENV is a hash, you can set environment variables just as you'd set the value of any Perl hash variable. Here's how you can set your PATH variable to make sure the following four directories are in your path::
$ENV{'PATH'} = '/bin:/usr/bin:/usr/local/bin:/home/yourname/bin';

How to open and read data files with Perl

Data files are opened in Perl using the open() function. When you open a data file, all you have to do is specify (a) a file handle and (b) the name of the file you want to read from.
As an example, suppose you need to read some data from a file named "checkbook.txt". Here's a simple open statement that opens the checkbook file for read access: open (CHECKBOOK, "checkbook.txt"); In this example, the name "CHECKBOOK" is the file handle that you'll use later when reading from the checkbook.txt data file. Any time you want to read data from the checkbook file, just use the file handle named "CHECKBOOK".
Now that we've opened the checkbook file, we'd like to be able to read what's in it. Here's how to read one line of data from the checkbook file:
$record = < CHECKBOOK > ;
After this statement is executed, the variable $record contains the contents of the first line of the checkbook file. The "<>" symbol is called the line reading operator.
To print every record of information from the checkbook file

open (CHECKBOOK, "checkbook.txt") || die "couldn't open the file!";
while ($record = < CHECKBOOK >) {
print $record;
}
close(CHECKBOOK);

How do I do fill_in_the_blank for each file in a directory?

Here's code that just prints a listing of every file in the current directory:
#!/usr/bin/perl -w
opendir(DIR, ".");
@files = readdir(DIR);
closedir(DIR);
foreach $file (@files) {
print "$file\n";
}

or

my @list=glob "/home/edrea/*";

print @list;

How do I generate a list of all .html files in a directory?

Here's a snippet of code that just prints a listing of every file in the current directory that ends with the extension .html:
#!/usr/bin/perl -w
opendir(DIR, ".");
@files = grep(/\.html$/,readdir(DIR));
closedir(DIR);
foreach $file (@files) {
print "$file\n";
}

or

my @list=glob "/home/edrea/*.html";

print @list;

What are scalar data and scalar variables?

Perl has a flexible concept of data types. Scalar means a single thing, like a number or string. So the Java concept of int, float, double and string equals to Perl\'s scalar in concept and the numbers and strings are exchangeable. Scalar variable is a Perl variable that is used to store scalar data. It uses a dollar sign $ and followed by one or more aphanumeric characters or underscores. It is case sensitive.

Why aren't Perl's patterns regular expressions?

Because Perl patterns have backreferences.
A regular expression by definition must be able to determine the next state in the finite automaton without requiring any extra memory to keep around previous state. A pattern /([ab]+)c\1/ requires the state machine to remember old states, and thus disqualifies such patterns as being regular expressions in the classic sense of the term.

How to concatenate strings with Perl?

Method #1 - using Perl's dot operator:
$name = 'checkbook';
$filename = "/tmp/" . $name . ".tmp";

Method #2 - using Perl's join function
$name = "checkbook";
$filename = join "", "/tmp/", $name, ".tmp";

Method #3 - usual way of concatenating strings
$filename = "/tmp/${name}.tmp";

How do I read command-line arguments with Perl?

With Perl, command-line arguments are stored in the array named @ARGV.
$ARGV[0] contains the first argument, $ARGV[1] contains the second argument, etc.
$#ARGV is the subscript. of the last element of the @ARGV array, so the number of arguments on the command line is $#ARGV + 1.
Here's a simple program:
#!/usr/bin/perl
$numArgs = $#ARGV + 1;
print "thanks, you gave me $numArgs command-line arguments.\n";
foreach $argnum (0 .. $#ARGV) {
print "$ARGV[$argnum]\n";
}

Perl Question Bank

1.       What is the difference between 'use' and 'require'?

Use :

1. The method is used only for the modules(only to include .pm type file)

2. The included objects are varified at the time of compilation.

3. No Need to give file extension.

Require:

1. The method is used for both libraries and modules.

2. The included objects are varified at the run time.

3. Need to give file Extension.

"use" is a keyword to incorporate a module (.pm file) at compile time whereas "require" can be used to import a package or a module at run time.

While using "use" keyword, we dont need to specify the file extension.only the file name is required.

suppose we have a module file as myModule.pm

use myModule;

or

require "myModule.pm";

will do the same job.

2.       What is the difference between 'my' and 'local'?

my and local Both of tboth are used to declare local variables.
The variables declared with "my" can live only within the block it was defined and cannot get its visibility inherited functions called within that block.
A variable defined with "local" can live within the block and can be accessed in the functions called within that block.

3.       What is the use of __END__ & __DATA__ ?

.6. Storing Files Inside Your Program Text

Problem

You have data that you want to bundle with your program and treat as though it were in a file, but you don't want it to be in a different file.

Solution

Use the __DATA__ or __END__ tokens after your program code to mark the start of a data block, which can be read inside your program or module from the DATA filehandle.

Use __DATA__ within a module:

while () {
    # process the line
}
__DATA__
# your data goes here

Similarly, use __END__ within the main program file:

while (<:data>) {
    # process the line
}
__END__
# your data goes here

Discussion

__DATA__ and __END__ indicate the logical end of a module or script. before the physical end of file is reached. Text after __DATA__ or __END__ can be read through the per-package DATA filehandle. For example, take the hypothetical module Primes. Text after __DATA__ in Primes.pm can be read from the Primes::DATA filehandle.

__END__ behaves as a synonym for __DATA__ in the main package. Text after __END__ tokens in modules is inaccessible.

This lets you write self-contained programs that would ordinarily keep data kept in separate files. Often this is used for documentation. Sometimes it's configuration data or old test data that the program was originally developed with, left lying about in case it ever needs to be recreated.

Another trick is to use DATA to find out the current program's or module's size or last modification date. On most systems, the $0 variable will contain the full pathname to your running script. On systems where $0 is not correct, you could try the DATA filehandle instead. This can be used to pull in the size, modification date, etc. Put a special token __DATA__ at the end of the file (and maybe a warning not to delete it), and the DATA filehandle will be to the script. itself.

use POSIX qw(strftime);

$raw_time = (stat(DATA))[9];
$size     = -s DATA;
$kilosize = int($size / 1024) . 'k';

print "

Script. size is $kilosize\n";

print strftime("

Last script. update: %c (%Z)\n", localtime($raw_time));

__DATA__
DO NOT REMOVE THE PRECEDING LINE.






Everything else in this file will be ignored.

4.       Are Perl arrays homogeneous?

No, perl array can include different types .

5.       What are the ways in which parameters are passed to Perl subroutine?

All parameters to a function are stored in an array called @_. One side effect of this is that you can find out how many parameters were passed by evaluating @ in a scalar context.

8.       How do you delete a hash element?

#!/usr/bin/perl

print "content-type: text/html \n\n";

# DEFINED HASH
%coins = ( "Quarter" , .25,
           
           
           "Dime" ,    .10,
           "Nickel",   .05 );

# PRINT OLD HASH
while (($key, $value) = each(%coins)){
     print $key.", ".$value."
";
}

# DELETE THE ELEMENT PAIRS



# PRINT THE NEW HASH
print "
";
while (($key, $value) = each(%coins)){
     print $key.", ".$value."
";
}

9.       What is difference between delete and undef in context with hash element?

Hashes are pairs of scalars: the first is the key, the second is the value. The key will be coerced to a string, although the value can be any kind of scalar: string, number, or reference. If a key $key is present in the array, exists($key) will return true. The value for a given key can be undef, in which case $array{$key} will be undef while $exists{$key} will return true. This corresponds to ($key, undef) being in the hash.

Pictures help... here's the %ary table:

          keys  values
        +------+------+
        |  a   |  3   |
        |  x   |  7   |
        |  d   |  0   |
        |  e   |  2   |
        +------+------+

And these conditions hold

        $ary{'a'}                       is true
        $ary{'d'}                       is false
        defined $ary{'d'}               is true
        defined $ary{'a'}               is true
        exists $ary{'a'}                is true (perl5 only)
        grep ($_ eq 'a', keys %ary)     is true

If you now say

        undef $ary{'a'}

your table now reads:

          keys  values
        +------+------+
        |  a   | undef|
        |  x   |  7   |
        |  d   |  0   |
        |  e   |  2   |
        +------+------+

and these conditions now hold; changes in caps:

        $ary{'a'}                       is FALSE
        $ary{'d'}                       is false
        defined $ary{'d'}               is true
        defined $ary{'a'}               is FALSE
        exists $ary{'a'}                is true (perl5 only)
        grep ($_ eq 'a', keys %ary)     is true

Notice the last two: you have an undef value, but a defined key!

Now, consider this:

        delete $ary{'a'}

your table now reads:

          keys  values
        +------+------+
        |  x   |  7   |
        |  d   |  0   |
        |  e   |  2   |
        +------+------+

and these conditions now hold; changes in caps:

        $ary{'a'}                       is false
        $ary{'d'}                       is false
        defined $ary{'d'}               is true
        defined $ary{'a'}               is false
        exists $ary{'a'}                is FALSE (perl5 only)
        grep ($_ eq 'a', keys %ary)     is FALSE

See, the whole entry is gone!

10.    When is a perl variable defined?

11.    What is the difference between 'chop' and 'chomp'?

chop removes the last character of each argument.

chomp removes the last character(s) of each argument if they're equal to the value of $/.

chomp is useful in combination with because reads until the value of $/ or eof is found.

12.    Write a regular expression to convert date from mm/dd/yyyy format to mm/yy format.

#!/usr/bin/perl

$date="03/16/2012";

$date=~ s/(\d\d)\/\d\d\/(\d\d\d\d)/$2\/$1/s;

print $date;

13.    What does 'bless' do?

bless associates a reference with a package.

It doesn't matter what the reference is to, it can be to a hash (most common case), to an array (not so common), to a scalar (usually this indicates an inside-out object), to a regular expression, subroutine or TYPEGLOB (see the book Object Oriented Perl: A Comprehensive Guide to Concepts and Programming Techniques by Damian Conway for useful examples) or even a reference to a file or directory handle (least common case).

The effect bless-ing has is that it allows you to apply special syntax to the blessed reference.

For example, if a blessed reference is stored in $obj (associated by bless with package "Class"), then $obj->foo(@args) will call a subroutine foo and pass as first argument the reference $obj followed by the rest of the arguments (@args). The subroutine should be defined in package "Class". If there is no subroutine foo in package "Class", a list of other packages (taken form. the array @ISA in the package "Class") will be searched and the first subroutine foo found will be called.

14.    What are the OO features imported by Perl?

An object is anything that provides a way to locate, access, modify, and secure data;
• A class is a description of what data is accessible through a particular kind of object, and how that data
may be accessed;
• A method is the means by which an object’s data is accessed, modified or processed;
• Inheritance is the way in which existing classes of objects can be upgraded to provide additional data or
methods;
• Polymorphism is the way that distinct objects can respond differently to the same message, depending
upon the class to which they belong.

15.    How would you comment multiple lines in Perl?

36.    Reverse and sort the list @list?

48.    How can I know how many entries are in a hash?

If you mean how many keys, then all you have to do is take the scalar sense of the keys() function:

    $num_keys = scalar keys %hash;

In void context it just resets the iterator, which is faster for tied hashes.

50.    What is the difference between pack and unpack?

Packed are extensions that have been compiled into a downloadable .crx file. These are the files you download and install from such places like the Chrome Web Store.

Unpacked are extensions that are available from your computer. Typically all the source files within a folder when you are developing an extension.

51.    How do I count the number of lines in a file?

perl -e 'print scalar(()=<>),"\n"' filename

60.    What is $_ , $& , $? , $@ , $0 , %ENV , @ARGV, @INC , @_

@ARGV The array ARGV contains the command line arguments intended for the script. Note that $#ARGV is the generally number of arguments minus one, since $ARGV[0] is the first argument, NOT the command name. See $0 for the command name.
 $!
 in the message? That's the human-readable complaint from the system. In general, when the system refuses to do something we've requested (like opening a file), $! will give you a reason (perhaps "permission denied" or "file not found," in this case).
$& has the entire matched section.

if(“Hello there, neigbor”=~ /\S(\w+),/){

print “That actually matched ‘$&’.\n”;

}

匹配的部分是“there,”（空格，单词，和一个逗号）。变量$1 中的值为there，而$&为整个被匹配的部分。

$_ Perl uses its favorite default variable. This is (mostly) like any other scalar variable, except for its unusual name
@_ you have to store this list somewhere, so Perl automatically stores the parameter list (another name for the argument list) in the special array variable named @_ for the duration of the subroutine
what happened to the program's own name (that's found in Perl's special variable $0, not @ARGV)
The exit status is then available in the $? variable (reminiscent of the same variable in the Bourne Shell) and is the same kind of number as the value returned by the system function: zero for success and nonzero for failure.

@INC include all perl module path.

$ perl -e 'print @INC'

/usr/lib/perl5/5.10/i686-cygwin/usr/lib/perl5/5.10/usr/lib/perl5/site_perl/5.10/i686-cygwin/usr/lib/perl5/site_perl/5.10/usr/lib/perl5/vendor_perl/5.10/i686-cygwin/usr/lib/perl5/vendor_perl/5.10/usr/lib/perl5/vendor_perl/5.10/usr/lib/perl5/site_perl/5.8/usr/lib/perl5/vendor_perl/5.8.

61.    How to read a complete file into a String ?

while(<>){
chomp;
$a.=$_
}
print $a;

62.    What is the difference between $@ and $! ?

When a normally fatal error happens during the execution of an eval block, the block is done running, but the program doesn't crash. Right after an eval finishes, you'll be wanting to know if it exited normally or caught a fatal error for you. The answer is in the special $@ variable. If the eval caught a fatal error, $@ will hold what would have been the program's dying words, perhaps something like: Illegal division by zero at my_program line 12. If there was no error, $@ will be empty. That means that $@ is a useful Boolean (true/false) value (true if there was an error), so you'll sometimes see code like this after an eval block:

    print "An error occurred: $@" if $@;

$!
 in the message? That's the human-readable complaint from the system. In general, when the system refuses to do something we've requested (like opening a file), $! will give you a reason (perhaps "permission denied" or "file not found," in this case).

---

Unix & Shell Scripting

1) How you can search multiple pattern using "grep" command?

2) What are different option in "grep: command?

grep-ipattern files：不区分大小写地搜索。默认情况区分大小写，
grep-lpattern files ：只列出匹配的文件名，
grep-Lpattern files ：列出不匹配的文件名，
grep-wpattern files：只匹配整个单词，而不是字符串的一部分（如匹配‘magic’，而不是‘magical’）

Grep NOT using grep -v

3) How you can select a field from a particular row?

 cat grade.txt | awk 'NR==3 {print $2}'

4) How u can replace a blank space from a file?

cat grade.txt | sed 's/  *//g'|sed 's/\t//g' -----remove blank space and tab

there are two spaces in the search block,
*

5) What is the command to list the all process are running in that terminal?

ps -t

6) What is "kill" command? what is the meaning of using "9" with :kill: command?

kill -9 ------force kill process

强制踢人命令格式：pkill -kill -t pts
踢出tty1用户的命令为： pkill -kill -t tty1

7) Any idea about scheduling the task?

Cron table

8) Do you know about the cron job?
9) Why we are using nohup?

All processes, except the at and batch requests, are killed when you log out. If you want a background process to continue running after you log out, you must use the nohup command to submit that background command.

1.       How do I remove a file whose name begins with a "-" ?

A.   rm ./-filename   OR rm -- -filename  OR rm - -filename

 

2.       How do I set the permissions on a symbolic link?

A.      Permissions on a symbolic link don't really mean anything. The only permissions that count are the permissions on the file that the link points to.

  ln -s 2.ksh link.ksh

Property/Action		Symbolic link	Hard Link	Junction
When symbolic link/ hard link/ junction is deleted ...		No change on target	Reference counter is decremented; when it reaches 0, the target is deleted	Target is deleted (except when using special tools)
When target is moved		Symbolic link becomes invalid	Hard link remains valid	Junction becomes invalid
Relative path		allowed	(unused)	not allowed (on save becomes an absolute path)
Drive requirement		Any drive allowed	Only same drive (link stored in descriptor)	Any drive allowed
Read of target		allowed		allowed
Windows	for files	from Vista / Windows 2008; administrator rights needed	yes	no
	for folders		no	yes
Unix	for files	yes	yes	(unused)
	for folders	yes	yes(root rights needed, although not for all filesystems)	(unused)

4.       How do I "include" one shell script. from within another shell script?

A.      All of the shells from the Bourne shell category (including rc) use the "." command. All of the shells from the C shell category use "source".

 eg . $HOME/.profile    ------------------

5.       What does fork do?

A.      The fork() function is used to create a new process from an existing process

 copy-on-write

9.       What is a zombie?

A.      When a program forks and the child finishes before the parent, the kernel still keeps some of its information

about the child in case the parent might need it -- for example, the parent may need to check the child's exit status. To be able to get this information, the parent calls wait(); when this happens, the kernel can discard the information.

In the interval between the child terminating and the parent calling wait(), the child is said to be a `zombie'. (If you do `ps', the child will have a `Z' in its status field to indicate this.) Even though it's not running, it's still taking up an entry in the process table.

 

Alternate definition of Zombie Process

A Unix process that has terminated (either because it has been killed by a signal or

because it has called exit()) and whose parent process has not yet received notification of its termination by executing wait() system call.

 

10.   How do you prevent Zombie from occuring?

A.      You need to ensure that your parent process calls wait() (or waitpid(), wait3(), etc.) for every child

process that terminates; or, on some systems, you can instruct the system that you are uninterested in child exit states.

 

Another approach is to fork() twice, and have the immediate child process exit straight away. This causes the grandchild process to be orphaned, so the init process is responsible for cleaning it up.

 

The other technique is to catch the SIGCHLD signal, and have the signal handler call waitpid() or wait3().

 

11.   How do I remove the marker(^M) at the end of each lines?

A.      On the Unix systems, this command will filter out the end-of-line markers:

 

       tr  -d '\015'   < file_with_eol_marker  >  file_without_eol_marker

      dos2unix

      unix2dos

       If you are on Omega, a simpler command is available:

       rmcr  file_with_eol_marker  >  file_without_eol_marker

 

12.   How do you find CPU limit on your account?

A.      You can determine your currently CPU limit by using the command:

 

       limit cputime

 

13.   How can you change the CPU limit for your account?

A.      The `limit' command may be used to change your CPU limit. For example, if you want to raise your cpu time limit up to 11 hours, use this command:

 

      limit cputime 11h

 

      Note that your limits can never exceed the "hard limits". You can determine your "hard limits" by   

      running the command:

 

      limit -h

 

14.   What is an Orphan process?

A.      A Unix process whose original parent has terminated and which has become a child of "init(1)".

 

15.   What is difference between soft link and hard link?

A.      Soft Link can be created on a directory where as hard link necessarily has to be on a file.

      Soft link can reside on same File System where as hard link can reside on other File system as well.

 

 

17.   How do you find all the processes that belong to you?

ps –ef | grep myname

-e to display all the processes.

-f to display full format listing.

18.   How do you kill a process?    kill -9 PID

Number	Name	Description	Used for
0	SIGNULL	Null	Check access to pid
1	SIGHUP	Hangup	Terminate; can be trapped
2	SIGINT	Interrupt	Terminate; can be trapped
3	SIGQUIT	Quit	Terminate with core dump; can be
9	SIGKILL	Kill	Forced termination; cannot be trapped
15	SIGTERM	Terminate	Terminate; can be trapped
24	SIGSTOP	Stop	Pause the process; cannot be trapped
25	SIGTSTP	Terminal	stop Pause the process; can be
26	SIGCONT	Continue	Run a stopped process

 

19.   How do you find the version of OS that you are using?   uname -a

20.   What is difference in using single and double quotes in shell script?

Use single quote when you want to literally print everything inside the single quote.

Use double quotes when you want to display the real meaning of special variables.

echo $HOME

>/home/Emma

echo '$HOME'

>$HOME

echo "$HOME"

>/home/Emma

 

21.   What is the use of eval in shell scripting?

eval is useful when cmd contains something which needs to be evaluated by the shell.

vnix$ set -- one two three  # sets $1 $2 $3
vnix$ echo $1
one
vnix$ n=1
vnix$ echo ${$n}  # attempt to echo $1
bash: ${$n}: bad substitution
vnix$ eval echo \${$n}
one

22.   What is use of 'tee' command?

Tee commands are quite useful particularly if you want to view output and keep it for other use.

cat 1.txt | tee 2.txt

output 1.txt and save it to 2.txt

23.   What is the use of 'nohup'?

All processes, except the at and batch requests, are killed when you log out. If you want a background process to continue running after you log out, you must use the nohup command to submit that background command.

24.   How do you run a process in background?

& makes the command run in the background

9. If you have submitted a job to run in foreground, how could you move it to background?

Correct Answer:

First suspend the job with -z, and then use the bg command to move it to the background.

---

Databases

1.     How you can transfer the data from database to a file? how u can list the only uniq value of some field?

 

export to itftacttest.dat of del select curr_mo from ITFT.ACTUALS_DETAIL_2011, ITFT.ACTUALS_CUR_DATE where LEDGER_YEAR_NUM = CURR_YEAR and LEDGER_MONTH_NUM = CURR_MO fetch first 1 row only

 

Export to c:\org.txt of del select * from org

例二：改变del格式文件的格式控制符
export to c:\staff.txt of del modified by coldel$ chardel'' decplusblank select * from staff
在 该例中，modified子句用于控制各种符号，coldel表示字段之间的间隔符，默认情况为逗号，现在改为$号；chardel表示字符串字段用什么 符号引用，默认情况下为一对双引号括起来，现在改为用一对单引号括起来；decplusblank表示对于十进制数据类型，用空格代替最前面的加号，因为 默认情况下会在十进制数据前面加上正负号的。

 

2.     What is normalization?

(1NF):Eliminate duplicative columns from the same table and data must be broken up into smallest units

(2NF):Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

     

Create relationships between these new tables and their predecessors through the use of foreign keys

(3NF):Remove columns that are not dependent upon the primary key.such as total=price*Qty

3.     Tell me about the Join? What are the different Joins?

Nested loops join: 确定一个驱动表(outer table)，另一个表为inner table，驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。适用于驱动表的记录集比较小（<10000）而且inner表需要有有效的访问方法（Index）。需要注意的是：JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

Hash join: 将两个表中较小的一个在内存中构造一个HASH表（对JOIN KEY），扫描另一个表，同样对JOIN KEY进行HASH后探测是否可以JOIN。适用于记录集比较大的情况。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个写的代价，会降低效率。

Merge join:将两个表排序，然后将两个表合并。通常情况下，只有在以下情况发生时，才会使用此种JOIN方式：

1.RBO模式

2.不等价关联(>,=,<=,<>

3.HASH_JOIN_ENABLED=false

4.数据源已排序

4.     What is inner Join , outer join, self join?

Inner Join: combine columns value of two tables base opon the join-predicate.

Full outer join:return all matched value from two tables, null in case of no matching.

Left outer join: return all values from left table plus matched value from right table.null in case of no matching

Right outer join:return all values from right table plus matched value from left table.null in case of no matching

Self join: self join is just like other join, except two instance of the same table will be joined in the query

5.     You have table Employee. List all the employee with the manger name? fields are empId, F_name, L_name, ManId.

select a.*,b.* from T1 a, T1 b where a.id=b.managerid

 

6.     What is Index? Why we are using Index? Is it fine if we can define the index with every column?

Index is a data structure that improve the speed of data retrieval operations on a table at the cost of lower writes and increase storage space. 

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same time, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used

7.     What is cursor? Describe all stages?

Cursor allow row-by-row processing of the resultset.

DECLARE vend_cursor CURSOR
    FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

----- 

FETCH NEXT FROM vend_cursor

END 
CLOSE vend_cursor;
DEALLOCATE vend_cursor

8.     What is procedure? why we are using the procedure?

  a set of SQL statements with an assigned name that's stored in the database in compiled form. so that it can be shared by a number of programs. 

First, stored procedures allow a lot more flexibility offering capabilities such as conditional logic.

Second, because stored procedures are stored within the DBMS, a single stored procedure can execute a complex set of SQL statements.Productivity.

Third, SQL Server pre-compiles stored procedures such that they execute optimally.

Fourth, client developers are abstracted from complex designs. They would simply need to know the stored procedure's name and the type of data it returns.

CREATE PROCEDURE HumanResources.uspGetEmployeesTest2 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 

BEGIN

    SET NOCOUNT ON;
    SELECT FirstName, LastName, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName
    AND EndDate IS NULL;

END

9.     What is trigger? How many trigger define on a table?

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

CREATETRIGGER[trigger_delete]

ONdbo.TableA

afterDELETE,insert,update

AS

 

 

BEGIN

    INSERTINTO dbo.TBVALUES(4,'Keeking')

    print'inserted表'

    select*frominserted

    print'deleted表'

    select*fromdeleted

END

10.  Is it possible that trigger not getting fired even some transaction is happing with data?

GRANT/REVOKE isn't allowed in stored procedures or triggers

DISABLE TRIGGER Person.uAddress ON Person.Address;

11.  What is truncate and delete?

12.  What is bcp? option of bcp?

No bcp in DB2. bcp used in MS SQLserver and Sybase.

BCP AdventureWorks.sales.currency out c:\currency1.txt -c -U"sa" -P"password"

注：BCP除了可以在控制台执行外，还可以通过调用SQL Server的一个系统存储过程xp_cmdshell以SQL语句的方式运行BCP。如上述第一条命令可改写为

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency out c:\currency1.txt -c -U"sa" -P"password"'

bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}

   [-a packet_size]
   [-b batch_size]
   [-c]
   [-C { ACP | OEM | RAW | code_page } ]
   [-d database_name]
   [-e err_file]
   [-E]
   [-f format_file]
   [-F first_row]
   [-h"hint [,...n]"] 
   [-i input_file]
   [-k]
   [-K application_intent]
   [-L last_row]
   [-m max_errors]
   [-n]
   [-N]
   [-o output_file]
   [-P password]
   [-q]
   [-r row_term]
   [-R]
   [-S [server_name[\instance_name]]
   [-t field_term]
   [-T]
   [-U login_id]
   [-v]
   [-V (80 | 90 | 100 )]
   [-w]
   [-x]
   /?

13.  Have you done performance tuning?

 No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.

1.Avoid tablespace scan for large tables

2.Avoid sorting if possible.(index for order by and group by,union all vs union,distinct)

3.Consider lock_isolation to avoid lock

4.Do not forget to run status.

runstats on table tablename and indexes all

5.Use buffer pool

6.Right Data type

14.  What are the scripts you have written in your previous project?

SQL on join and aggregate function? 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7899089/viewspace-735106/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7899089/viewspace-735106/