SQL cheat sheet

Joins

Materialized Views in Oracle

A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table. Using materialized views against remote tables is the simplest way to achieve replication of data between sites. The example code in this article assumes DB1 is the master instance and DB2 is the materialized view site.

Basic Syntax

The full syntax description for the CREATE MATERIALIZED VIEW command is available in the documentation. Here we will only concern ourselves with the basics.

CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
[ON PREBUILT TABLE]
AS
SELECT ...;

The BUILD clause options are shown below.

• IMMEDIATE : The materialized view is populated immediately.
• DEFERRED : The materialized view is populated on the first requested refresh.

The following refresh types are available.

• FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
• COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
• FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

A refresh can be triggered in one of two ways.

• ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
• ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. An example of the query rewrite functionality is shown below.

The ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.

CREATE MATERIALIZED VIEW MV_RFIDS
REFRESH FORCE ON DEMAND
AS
SELECT RFIDS.RFID_NO RFID_NO,RFIDS.RFID_TYPE RFID_TYPE,
RFIDS.STATUS STATUS 
FROM DBA.RFIDS@Remote DB RFIDS;

begin
 dbms_mview.refresh('rfids');

end;

Using Ref Cursors To Return Recordsets

recordsets to be returned from stored procedures and functions is called Ref Cursor

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno    IN  emp.deptno%TYPE,
                      p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/

Basic UNIX commands

Note: not all of these are actually part of UNIX itself, and you may not find them on all UNIX machines. But they can all be used on turing in essentially the same way, by typing the command and hitting return. Note that some of these commands are different on non-Solaris machines - see SunOS differences.
If you've made a typo, the easiest thing to do is hit CTRL-u to cancel the whole line. But you can also edit the command line (see the guide to More UNIX).
UNIX is case-sensitive.

Files

• ls --- lists your files
  ls -l --- lists your files in 'long format', which contains lots of useful information, e.g. the exact size of the file, who owns the file and who has the right to look at it, and when it was last modified.
  ls -a --- lists all files, including the ones whose filenames begin in a dot, which you do not always want to see.
  There are many more options, for example to list files by size, by date, recursively etc.
• more filename --- shows the first part of a file, just as much as will fit on one screen. Just hit the space bar to see more or q to quit. You can use /pattern to search for a pattern.
• emacs filename --- is an editor that lets you create and edit a file. See the emacs page.
• mv filename1 filename2 --- moves a file (i.e. gives it a different name, or moves it into a different directory (see below)
• cp filename1 filename2 --- copies a file
• rm filename --- removes a file. It is wise to use the option rm -i, which will ask you for confirmation before actually deleting anything. You can make this your default by making an alias in your .cshrc file.
• diff filename1 filename2 --- compares files, and shows where they differ
• wc filename --- tells you how many lines, words, and characters there are in a file
• chmod options filename --- lets you change the read, write, and execute permissions on your files. The default is that only you can look at them and change them, but you may sometimes want to change these permissions. For example, chmod o+r filename will make the file readable for everyone, and chmod o-r filename will make it unreadable for others again. Note that for someone to be able to actually look at the file the directories it is in need to be at least executable. See help protection for more details.
• File Compression
  • gzip filename --- compresses files, so that they take up much less space. Usually text files compress to about half their original size, but it depends very much on the size of the file and the nature of the contents. There are other tools for this purpose, too (e.g. compress), but gzip usually gives the highest compression rate. Gzip produces files with the ending '.gz' appended to the original filename.
  • gunzip filename --- uncompresses files compressed by gzip.
  • gzcat filename --- lets you look at a gzipped file without actually having to gunzip it (same as gunzip -c). You can even print it directly, using gzcat filename | lpr
• printing
  • lpr filename --- print. Use the -P option to specify the printer name if you want to use a printer other than your default printer. For example, if you want to print double-sided, use 'lpr -Pvalkyr-d', or if you're at CSLI, you may want to use 'lpr -Pcord115-d'. See 'help printers' for more information about printers and their locations.
  • lpq --- check out the printer queue, e.g. to get the number needed for removal, or to see how many other files will be printed before yours will come out
  • lprm jobnumber --- remove something from the printer queue. You can find the job number by using lpq. Theoretically you also have to specify a printer name, but this isn't necessary as long as you use your default printer in the department.
  • genscript --- converts plain text files into postscript for printing, and gives you some options for formatting. Consider making an alias like alias ecop 'genscript -2 -r \!* | lpr -h -Pvalkyr' to print two pages on one piece of paper.
  • dvips filename --- print .dvi files (i.e. files produced by LaTeX). You can use dviselect to print only selected pages. See the LaTeX page for more information about how to save paper when printing drafts.

Directories

Directories, like folders on a Macintosh, are used to group files together in a hierarchical structure.

• mkdir dirname --- make a new directory
• cd dirname --- change directory. You basically 'go' to another directory, and you will see the files in that directory when you do 'ls'. You always start out in your 'home directory', and you can get back there by typing 'cd' without arguments. 'cd ..' will get you one level up from your current position. You don't have to walk along step by step - you can make big leaps or avoid walking around by specifying pathnames.
• pwd --- tells you where you currently are.

Finding things

• ff --- find files anywhere on the system. This can be extremely useful if you've forgotten in which directory you put a file, but do remember the name. In fact, if you use ff -p you don't even need the full name, just the beginning. This can also be useful for finding other things on the system, e.g. documentation.
• grep string filename(s) --- looks for the string in the files. This can be useful a lot of purposes, e.g. finding the right file among many, figuring out which is the right version of something, and even doing serious corpus work. grep comes in several varieties (grep, egrep, and fgrep) and has a lot of very flexible options. Check out the man pages if this sounds good to you.

About other people

• w --- tells you who's logged in, and what they're doing. Especially useful: the 'idle' part. This allows you to see whether they're actually sitting there typing away at their keyboards right at the moment.
• who --- tells you who's logged on, and where they're coming from. Useful if you're looking for someone who's actually physically in the same building as you, or in some other particular location.
• finger username --- gives you lots of information about that user, e.g. when they last read their mail and whether they're logged in. Often people put other practical information, such as phone numbers and addresses, in a file called .plan. This information is also displayed by 'finger'.
• last -1 username --- tells you when the user last logged on and off and from where. Without any options, last will give you a list of everyone's logins.
• talk username --- lets you have a (typed) conversation with another user
• write username --- lets you exchange one-line messages with another user
• elm --- lets you send e-mail messages to people around the world (and, of course, read them). It's not the only mailer you can use, but the one we recommend. See the elm page, and find out about the departmental mailing lists (which you can also find in /user/linguistics/helpfile).

About your (electronic) self

• whoami --- returns your username. Sounds useless, but isn't. You may need to find out who it is who forgot to log out somewhere, and make sure *you* have logged out.
• finger & .plan files
  of course you can finger yourself, too. That can be useful e.g. as a quick check whether you got new mail. Try to create a useful .plan file soon. Look at other people's .plan files for ideas. The file needs to be readable for everyone in order to be visible through 'finger'. Do 'chmod a+r .plan' if necessary. You should realize that this information is accessible from anywhere in the world, not just to other people on turing.
• passwd --- lets you change your password, which you should do regularly (at least once a year). See the LRB guide and/or look at help password.
• ps -u yourusername --- lists your processes. Contains lots of information about them, including the process ID, which you need if you have to kill a process. Normally, when you have been kicked out of a dialin session or have otherwise managed to get yourself disconnected abruptly, this list will contain the processes you need to kill. Those may include the shell (tcsh or whatever you're using), and anything you were running, for example emacs or elm. Be careful not to kill your current shell - the one with the number closer to the one of the ps command you're currently running. But if it happens, don't panic. Just try again :) If you're using an X-display you may have to kill some X processes before you can start them again. These will show only when you use ps -efl, because they're root processes.
• kill PID --- kills (ends) the processes with the ID you gave. This works only for your own processes, of course. Get the ID by using ps. If the process doesn't 'die' properly, use the option -9. But attempt without that option first, because it doesn't give the process a chance to finish possibly important business before dying. You may need to kill processes for example if your modem connection was interrupted and you didn't get logged out properly, which sometimes happens.
• quota -v --- show what your disk quota is (i.e. how much space you have to store files), how much you're actually using, and in case you've exceeded your quota (which you'll be given an automatic warning about by the system) how much time you have left to sort them out (by deleting or gzipping some, or moving them to your own computer).
• du filename --- shows the disk usage of the files and directories in filename (without argument the current directory is used). du -s gives only a total.
• last yourusername --- lists your last logins. Can be a useful memory aid for when you were where, how long you've been working for, and keeping track of your phonebill if you're making a non-local phonecall for dialling in.

Connecting to the outside world

• nn --- allows you to read news. It will first let you read the news local to turing, and then the remote news. If you want to read only the local or remote news, you can use nnl or nnr, respectively. To learn more about nn type nn, then \tty{:man}, then \tty{=.*}, then \tty{Z}, then hit the space bar to step through the manual. Or look at the man page. Or check out the hypertext nn FAQ - probably the easiest and most fun way to go.
• rlogin hostname --- lets you connect to a remote host
• telnet hostname --- also lets you connect to a remote host. Use rlogin whenever possible.
• ftp hostname --- lets you download files from a remote host which is set up as an ftp-server. This is a common method for exchanging academic papers and drafts. If you need to make a paper of yours available in this way, you can (temporarily) put a copy in /user/ftp/pub/TMP. For more permanent solutions, ask Emma. The most important commands within ftp are get for getting files from the remote machine, and put for putting them there (mget and mput let you specify more than one file at once). Sounds straightforward, but be sure not to confuse the two, especially when your physical location doesn't correspond to the direction of the ftp connection you're making. ftp just overwrites files with the same filename. If you're transferring anything other than ASCII text, use binary mode.
• lynx --- lets you browse the web from an ordinary terminal. Of course you can see only the text, not the pictures. You can type any URL as an argument to the G command. When you're doing this from any Stanford host you can leave out the .stanford.edu part of the URL when connecting to Stanford URLs. Type H at any time to learn more about lynx, and Q to exit.

Miscellaneous tools

• webster word --- looks up the word in an electronic version of Webster's dictionary and returns the definition(s)
• date --- shows the current date and time.
• cal --- shows a calendar of the current month. Use e.g., 'cal 10 1995' to get that for October 95, or 'cal 1995' to get the whole year.

You can find out more about these commands by looking up their manpages:
man commandname --- shows you the manual page for the command

Unix Command Summary

Contents

• cat --- for creating and displaying short files
• chmod --- change permissions
• cd --- change directory
• cp --- for copying files
• date --- display date
• echo --- echo argument
• ftp --- connect to a remote machine to download or upload files
• grep --- search file
• head --- display first part of file
• ls --- see what files you have
• lpr --- standard print command (see also print )
• more --- use to read files
• mkdir --- create directory
• mv --- for moving and renaming files
• ncftp --- especially good for downloading files via anonymous ftp.
• print --- custom print command (see also lpr )
• pwd --- find out what directory you are in
• rm --- remove a file
• rmdir --- remove directory
• rsh --- remote shell
• setenv --- set an environment variable
• sort --- sort file
• tail --- display last part of file
• tar --- create an archive, add or extract files
• telnet --- log in to another machine
• wc --- count characters, words, lines

---

cat

This is one of the most flexible Unix commands. We can use to create, view and concatenate files. For our first example we create a three-item English-Spanish dictionary in a file called "dict."

   % cat >dict
     red rojo
     green verde
     blue azul
 
   %

stands for "hold the control key down, then tap 'd'". The symbol > tells the computer that what is typed is to be put into the file dict. To view a file we use cat in a different way:

   % cat dict
     red rojo
     green verde
     blue azul
   %

If we wish to add text to an existing file we do this:

   % cat >>dict
     white blanco
     black negro
      
   %

Now suppose that we have another file tmp that looks like this:

   % cat tmp
     cat gato
     dog perro
   %

Then we can join dict and tmp like this:

   % cat dict tmp >dict2

We could check the number of lines in the new file like this:

   % wc -l dict2
8

The command wc counts things --- the number of characters, words, and line in a file.

---

chmod

This command is used to change the permissions of a file or directory. For example to make a file essay.001 readable by everyone, we do this:

   % chmod a+r essay.001

To make a file, e.g., a shell script mycommand executable, we do this

   % chmod +x mycommand

Now we can run mycommand as a command.
To check the permissions of a file, use ls -l . For more information on chmod, use man chmod.

---

cd

Use cd to change directory. Use pwd to see what directory you are in.

   % cd english
   % pwd
   % /u/ma/jeremy/english
   % ls
novel poems
   % cd novel
   % pwd
   % /u/ma/jeremy/english/novel
   % ls
ch1 ch2 ch3 journal scrapbook
   % cd ..
   % pwd
   % /u/ma/jeremy/english
   % cd poems
   % cd
   % /u/ma/jeremy

Jeremy began in his home directory, then went to his english subdirectory. He listed this directory using ls , found that it contained two entries, both of which happen to be diretories. He cd'd to the diretorynovel, and found that he had gotten only as far as chapter 3 in his writing. Then he used cd .. to jump back one level. If had wanted to jump back one level, then go to poems he could have said cd ../poems. Finally he used cd with no argument to jump back to his home directory.

---

cp

Use cp to copy files or directories.

   % cp foo foo.2

This makes a copy of the file foo.

   % cp ~/poems/jabber .

This copies the file jabber in the directory poems to the current directory. The symbol "." stands for the current directory. The symbol "~" stands for the home directory.

---

date

Use this command to check the date and time.

   % date
Fri Jan  6 08:52:42 MST 1995

---

echo

The echo command echoes its arguments. Here are some examples:

   % echo this
     this
   % echo $EDITOR
     /usr/local/bin/emacs
   % echo $PRINTER
     b129lab1

Things like PRINTER are so-called environment variables. This one stores the name of the default printer --- the one that print jobs will go to unless you take some action to change things. The dollar sign before an environment variable is needed to get the value in the variable. Try the following to verify this:

   % echo PRINTER
     PRINTER

---

ftp

Use ftp to connect to a remote machine, then upload or download files. See also: ncftp
Example 1: We'll connect to the machine fubar.net, then change director to mystuff, then download the file homework11:

   % ftp solitude
     Connected to fubar.net.
     220 fubar.net FTP server (Version wu-2.4(11) Mon Apr 18 17:26:33 MDT 1994) ready.
   Name (solitude:carlson): jeremy
     331 Password required for jeremy.
   Password: 
     230 User jeremy logged in.
   ftp> cd mystuff
     250 CWD command successful.
   ftp> get homework11
   ftp> quit

Example 2: We'll connect to the machine fubar.net, then change director to mystuff, then upload the file collected-letters:

   % ftp solitude
     Connected to fubar.net.
     220 fubar.net FTP server (Version wu-2.4(11) Mon Apr 18 17:26:33 MDT 1994) ready.
   Name (solitude:carlson): jeremy
     331 Password required for jeremy.
   Password: 
     230 User jeremy logged in.
   ftp> cd mystuff
     250 CWD command successful.
   ftp> put collected-letters
   ftp> quit

The ftp program sends files in ascii (text) format unless you specify binary mode:

   ftp> binary
   ftp> put foo
   ftp> ascii
   ftp> get bar

The file foo was transferred in binary mode, the file bar was transferred in ascii mode.

---

grep

Use this command to search for information in a file or files. For example, suppose that we have a file dict whose contents are

   red rojo
   green verde
   blue azul
   white blanco
   black negro

Then we can look up items in our file like this;

   % grep red dict
     red rojo
   % grep blanco dict
     white blanco
   % grep brown dict
   %

Notice that no output was returned by grep brown. This is because "brown" is not in our dictionary file.
Grep can also be combined with other commands. For example, if one had a file of phone numbers named "ph", one entry per line, then the following command would give an alphabetical list of all persons whose name contains the string "Fred".

   % grep Fred ph | sort
     Alpha, Fred: 333-6565
     Beta, Freddie: 656-0099
     Frederickson, Molly: 444-0981
     Gamma, Fred-George: 111-7676
     Zeta, Frederick: 431-0987

The symbol "|" is called "pipe." It pipes the output of the grep command into the input of the sort command.
For more information on grep, consult

   % man grep

---

head

Use this command to look at the head of a file. For example,

   % head essay.001

displays the first 10 lines of the file essay.001 To see a specific number of lines, do this:

   % head -n 20 essay.001

This displays the first 20 lines of the file.

---

ls

Use ls to see what files you have. Your files are kept in something called a directory.

   % ls
     foo       letter2
     foobar    letter3
     letter1   maple-assignment1
   %

Note that you have six files. There are some useful variants of the ls command:

   % ls l*
     letter1 letter2 letter3
   %

Note what happened: all the files whose name begins with "l" are listed. The asterisk (*) is the " wildcard" character. It matches any string.

---

lpr

This is the standard Unix command for printing a file. It stands for the ancient "line printer." See

   % man lpr

for information on how it works. See print for information on our local intelligent print command.

---

mkdir

Use this command to create a directory.

   % mkdir essays

To get "into" this directory, do

   % cd essays

To see what files are in essays, do this:

   % ls

There shouldn't be any files there yet, since you just made it. To create files, see cat or emacs.

---

more

More is a command used to read text files. For example, we could do this:

   % more poems

The effect of this to let you read the file "poems ". It probably will not fit in one screen, so you need to know how to "turn pages". Here are the basic commands:

• q --- quit more
• spacebar --- read next page
• return key --- read next line
• b --- go back one page

For still more information, use the command man more.

---

mv

Use this command to change the name of file and directories.

   % mv foo foobar

The file that was named foo is now named foobar

---

ncftp

Use ncftp for anonymous ftp --- that means you don't have to have a password.

   % ncftp ftp.fubar.net
     Connected to ftp.fubar.net
   > get jokes.txt

The file jokes.txt is downloaded from the machine ftp.fubar.net.

---

print

This is a moderately intelligent print command.

   % print foo
   % print notes.ps
   % print manuscript.dvi

In each case print does the right thing, regardless of whether the file is a text file (like foo ), a postcript file (like notes.ps, or a dvi file (like manuscript.dvi. In these examples the file is printed on the default printer. To see what this is, do

   % print

and read the message displayed. To print on a specific printer, do this:

   % print foo jwb321
   % print notes.ps jwb321
   % print manuscript.dvi jwb321

To change the default printer, do this:

   % setenv PRINTER jwb321

---

pwd

Use this command to find out what directory you are working in.

   % pwd
/u/ma/jeremy
   % cd homework
   % pwd
/u/ma/jeremy/homework
   % ls
assign-1 assign-2 assign-3
   % cd
   % pwd
/u/ma/jeremy
   %

Jeremy began by working in his "home" directory. Then he cd 'd into his homework subdirectory. Cd means " change directory". He used pwd to check to make sure he was in the right place, then used ls to see if all his homework files were there. (They were). Then he cd'd back to his home directory.

---

rm

Use rm to remove files from your directory.

   % rm foo
     remove foo? y
   % rm letter*
     remove letter1? y
     remove letter2? y
     remove letter3? n
   %

The first command removed a single file. The second command was intended to remove all files beginning with the string "letter." However, our user (Jeremy?) decided not to remove letter3.

---

rmdir

Use this command to remove a directory. For example, to remove a directory called "essays", do this:

   % rmdir essays

A directory must be empty before it can be removed. To empty a directory, use rm.

---

rsh

Use this command if you want to work on a computer different from the one you are currently working on. One reason to do this is that the remote machine might be faster. For example, the command

   % rsh solitude

connects you to the machine solitude. This is one of our public workstations and is fairly fast.
See also: telnet

---

setenv

   % echo $PRINTER
     labprinter
   % setenv PRINTER myprinter
   % echo $PRINTER
     myprinter

---

sort

Use this commmand to sort a file. For example, suppose we have a file dict with contents

red rojo
green verde
blue azul
white blanco
black negro

Then we can do this:

   % sort dict
     black negro
     blue azul
     green verde
     red rojo
     white blanco

Here the output of sort went to the screen. To store the output in file we do this:

   % sort dict >dict.sorted 

You can check the contents of the file dict.sorted using cat , more , or emacs .

---

tail

Use this command to look at the tail of a file. For example,

   % tail essay.001

displays the last 10 lines of the file essay.001 To see a specific number of lines, do this:

   % tail -n 20 essay.001

This displays the last 20 lines of the file.

---

tar

Use create compressed archives of directories and files, and also to extract directories and files from an archive. Example:

   % tar -tvzf foo.tar.gz

displays the file names in the compressed archive foo.tar.gz while

   % tar -xvzf foo.tar.gz

extracts the files.

---

telnet

Use this command to log in to another machine from the machine you are currently working on. For example, to log in to the machine "solitude", do this:

   % telnet solitude

See also: rsh.

---

wc

Use this command to count the number of characters, words, and lines in a file. Suppose, for example, that we have a file dict with contents

red rojo
green verde
blue azul
white blanco
black negro

Then we can do this

   % wc dict
     5      10      56 tmp

This shows that dict has 5 lines, 10 words, and 56 characters.
The word count command has several options, as illustrated below:

   % wc -l dict
     5 tmp
   % wc -w dict
     10 tmp
   % wc -c dict
     56 tmp     

USB Drive Folders Not Showing

To view the data..

execute the command in cmd........

attrib -h -r -s /s /d  g:\*.*  ;

How to change all the file extensions in a folder

Try this (ren stands for rename):

ren *.XXX *.YYY

and of course, switch XXX and YYY for the appropriate extensions. It will change from XXX to YYY. If you want to change all extensions, just use the wildcard again:

ren *.* *.YYY

And, since you have thousands of files, make sure to wait until the cursor starts blinking again indicating that it's done working.

Oracle interview questions and answers

http://careerride.com/Oracle-Interview-Questions.aspx

Converting Doc File to PDF

http://convertonlinefree.com/

General Questions on SQL SERVER

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage. (Read more here)

What are the Properties of the Relational Tables?

Relational tables have the following six properties:

• Values are atomic.
• Column values are of the same kind.
• Each row is unique.
• The sequence of columns is insignificant.
• The sequence of rows is insignificant.
• Each column must have a unique name.

What is Normalization?

Database normalization is a data design and organization process applied to data structures based on rules that help building relational databases. In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while providing physical storage of data that is tuned for high performance. De-normalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access.

How is ACID property related to Database?

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for while evaluating databases and application architectures. For a reliable database, all this four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they are finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. (Read more here)

What are the Different Normalization Forms?

1NF: Eliminate Repeating Groups

Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.

2NF: Eliminate Redundant Data

If an attribute depends on only part of a multi-valued key, then remove it to a separate table.

3NF: Eliminate Columns Not Dependent On Key

If attributes do not contribute to a description of the key, then remove them to a separate table. All attributes must be directly dependent on the primary key. (Read more here)

BCNF: Boyce-Codd Normal Form

If there are non-trivial dependencies between candidate key attributes, then separate them out into distinct tables.

4NF: Isolate Independent Multiple Relationships

No table may contain two or more 1:n or n:m relationships that are not directly related.

5NF: Isolate Semantically Related Multiple Relationships

There may be practical constrains on information that justify separating logically related many-to-many relationships.

ONF: Optimal Normal Form

A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.

DKNF: Domain-Key Normal Form

A model free from all modification anomalies is said to be in DKNF.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

ACID PROPERTIES

Atomicity

All changes to data are performed as if they are a single operation. That is, all the changes are performed, or none of them are.

For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.

Consistency

Data is in a consistent state when a transaction starts and when it ends.

For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation

The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized.

For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

Durability

After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure.

For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

SQL SERVER IV
http://blog.sqlauthority.com/sql-server-interview-questions-and-answers/

Oracle SQL & PL/SQL

http://sql-plsql.blogspot.in/2007/03/sql-introduction.html


http://blog.sqlauthority.com/sql-server-interview-questions-and-answers/

SQL Server Tutorial

Server Database Systems

Server based database systems are designed to run on a central server, so that multiple users can access the same data simultaneously. The users normally access the database through an application.

For example, a website could store all its content in a database. Whenever a visitor views an article, they are retrieving data from the database. As you know, websites aren't normally limited to just one user. So, at any given moment, a website could be serving up hundreds, or even thousands of articles to its website visitors. At the same time, other users could be updating their personal profile in the members' area, or subscribing to a newsletter, or anything else that website users do.

Generally, it's the application that provides the functionality to these visitors. It is the database that stores the data and makes it available. Having said that, SQL Server does include some useful features that can assist the application in providing its functionality.



Here are the different editions available for SQL Server 2008.

• Enterprise Edition
  Data management and business intelligence platform providing enterprise class scalability, high availability, and security for running business-critical applications
• Standard Edition
  Data management and business intelligence platform providing ease of use and manageability for running departmental applications
• Workgroup Edition
  Data management and reporting platform providing secure, remote synchronization, and management capabilities for running branch applications
• Developer Edition
  May be installed and used by one user to design, develop, test, and demonstrate your programs on as many systems as needed
• Web Edition
  A low-TCO, scalable, and manageable database option for web hosters and end customers looking to deploy publicly facing web applications and services
• Express Edition
  A free edition of SQL Server ideal for learning and building desktop and small server applications and for redistribution by ISVs
• Compact Edition
  A free, SQL Server embedded database ideal for building stand-alone and occasionally connected applications for mobile devices, desktops, and web clients
• Evaluation Edition
  This edition may be installed for demonstration and evaluation purposes until an expiration period of 180 days.

Linux Shell Commands Cheat Sheet

Examples marked with * are valid and safe to paste into a linux terminal without modification.

Command		Description
*	apropos whatis	Show commands pertinent to string. See also threadsafe
*	man -t man | ps2pdf – > man.pdf	make a pdf of a manual page
	which command	Show full path name of command
	time command	See how long a command takes
*	time cat	Start stopwatch. Ctrl-d to stop. See also sw
*	nice info	Run a low priority command (The “info” reader in this case)
*	renice 19 -p $$	Make shell (script) low priority. Use for non interactive tasks
*	cd -	Go to previous directory
*	cd	Go to $HOME directory
	(cd dir && command)	Go to dir, execute command and return to current dir
*	pushd .	Put current dir on stack so you canpopd back to it
*	alias l=’ls -l –color=auto’	quick dir listing
*	ls -lrt	List files by date. See also newest andfind_mm_yyyy
*	ls /usr/bin | pr -T9 -W$COLUMNS	Print in 9 columns to width of terminal
	find -name ‘*.[ch]‘ | xargs grep -E ‘expr’	Search ‘expr’ in this dir and below. See also findrepo
	find -type f -print0 | xargs -r0 grep -F ‘example’	Search all regular files for ‘example’ in this dir and below
	find -maxdepth 1 -type f | xargs grep -F ‘example’	Search all regular files for ‘example’ in this dir
	find -maxdepth 1 -type d | while readdir; do echo $dir; echo cmd2; done	Process each item with multiple commands (in while loop)
*	find -type f ! -perm -444	Find files not readable by all (useful for web site)
*	find -type d ! -perm -111	Find dirs not accessible by all (useful for web site)
*	locate -r ‘file[^/]*\.txt’	Search cached index for names. This re is like glob *file*.txt
*	look reference	Quickly search (sorted) dictionary for prefix
*	grep –color reference /usr/share/dict/words	Highlight occurances of regular expression in dictionary
	gpg -c file	Encrypt file
	gpg file.gpg	Decrypt file
	tar -c dir/ | bzip2 > dir.tar.bz2	Make compressed archive of dir/
	bzip2 -dc dir.tar.bz2 | tar -x	Extract archive (use gzip instead of bzip2 for tar.gz files)
	tar -c dir/ | gzip | gpg -c | ssh user@remote ‘dd of=dir.tar.gz.gpg’	Make encrypted archive of dir/ on remote machine
	find dir/ -name ‘*.txt’ | tar -c –files-from=- | bzip2 > dir_txt.tar.bz2	Make archive of subset of dir/ and below
	find dir/ -name ‘*.txt’ | xargs cp -a –target-directory=dir_txt/ –parents	Make copy of subset of dir/ and below
	( tar -c /dir/to/copy ) | ( cd /where/to/ && tar -x -p )	Copy (with permissions) copy/ dir to /where/to/ dir
	( cd /dir/to/copy && tar -c . ) | ( cd /where/to/ && tar -x -p )	Copy (with permissions) contents of copy/ dir to /where/to/
	( tar -c /dir/to/copy ) | ssh -C user@remote ‘cd /where/to/ && tar -x -p’	Copy (with permissions) copy/ dir to remote:/where/to/ dir
	dd bs=1M if=/dev/sda | gzip | ssh user@remote ‘dd of=sda.gz’	Backup harddisk to remote machine
	rsync -P rsync://rsync.server.com/path/to/file file	Only get diffs. Do multiple times for troublesome downloads
	rsync –bwlimit=1000 fromfile tofile	Locally copy with rate limit. It’s like nice for I/O
	rsync -az -e ssh –delete ~/public_html/ remote.com:’~/public_html’	Mirror web site (using compression and encryption)
	rsync -auz -e ssh remote:/dir/ . && rsync -auz -e ssh . remote:/dir/	Synchronize current directory with remote one
	ssh $USER@$HOST command	Run command on $HOST as $USER (default command=shell)
*	ssh -f -Y $USER@$HOSTNAME xeyes	Run GUI command on $HOSTNAME as $USER
	scp -p -r $USER@$HOST: file dir/	Copy with permissions to $USER’s home directory on $HOST
	ssh -g -L 8080:localhost:80 root@$HOST	Forward connections to $HOSTNAME:8080 out to $HOST:80
	ssh -R 1434:imap:143 root@$HOST	Forward connections from $HOST:1434 in to imap:143
*	(cd cmdline && wget -nd -pHEKk http://www.pixelbeat.org/cmdline.html)	Store local browsable version of a page to the current dir
	wget -c http://www.example.com/large.file	Continue downloading a partially downloaded file
	wget -r -nd -np -l1 -A ‘*.jpg’ http://www.example.com/dir/	Download a set of files to the current directory
	wget ftp://remote/file[1-9].iso/	FTP supports globbing directly
*	wget -q -O- http://www.pixelbeat.org/timeline.html | grep ‘a href’ | head	Process output directly
	echo ‘wget url’ | at 01:00	Download url at 1AM to current dir
	wget –limit-rate=20k url	Do a low priority download (limit to 20KB/s in this case)
	wget -nv –spider –force-html -i bookmarks.html	Check links in a file
	wget –mirror http://www.example.com/	Efficiently update a local copy of a site (handy from cron)
	ethtool eth0	Show status of ethernet interface eth0
	ethtool –change eth0 autoneg off speed 100 duplex full	Manually set ethernet interface speed
	iwconfig eth1	Show status of wireless interface eth1
	iwconfig eth1 rate 1Mb/s fixed	Manually set wireless interface speed
*	iwlist scan	List wireless networks in range
*	ip link show	List network interfaces
	ip link set dev eth0 name wan	Rename interface eth0 to wan
	ip link set dev eth0 up	Bring interface eth0 up (or down)
*	ip addr show	List addresses for interfaces
	ip addr add 1.2.3.4/24 brd + dev eth0	Add (or del) ip and mask (255.255.255.0)
*	ip route show	List routing table
	ip route add default via 1.2.3.254	Set default gateway to 1.2.3.254
*	tc qdisc add dev lo root handle 1:0 netem delay 20msec	Add 20ms latency to loopback device (for testing)
*	tc qdisc del dev lo root	Remove latency added above
*	host pixelbeat.org	Lookup DNS ip address for name or vice versa
*	hostname -i	Lookup local ip address (equivalent to host `hostname`)
*	whois pixelbeat.org	Lookup whois info for hostname or ip address
*	netstat -tupl	List internet services on a system
*	netstat -tup	List active connections to/from system
*	smbtree	Find windows machines. See also findsmb
	nmblookup -A 1.2.3.4	Find the windows (netbios) name associated with ip address
	smbclient -L windows_box	List shares on windows machine or samba server
	mount -t smbfs -o fmask=666,guest //windows_box/share /mnt/share	Mount a windows share
	echo ‘message’ | smbclient -M windows_box	Send popup to windows machine (off by default in XP sp2)
	sed ‘s/string1/string2/g’	Replace string1 with string2
	sed ‘s/\(.*\)1/\12/g’	Modify anystring1 to anystring2
	sed ‘/ *#/d; /^ *$/d’	Remove comments and blank lines
	sed ‘:a; /\\$/N; s/\\\n//; ta’	Concatenate lines with trailing \
	sed ‘s/[ \t]*$//’	Remove trailing spaces from lines
	sed ‘s/\([\\`\\"$\\\\]\)/\\\1/g’	Escape shell metacharacters active within double quotes
*	seq 10 | sed “s/^/ /; s/ *\(.\{7,\}\)/\1/”	Right align numbers
	sed -n ’1000p;1000q‘	Print 1000th line
	sed -n ’10,20p;20q‘	Print lines 10 to 20
	sed -n ‘s/.*