Gopi Golla's Tutorial Point
Place to Learn New Things
Saturday, September 7, 2024
Wednesday, May 4, 2022
Sunday, March 27, 2022
Thursday, August 20, 2015
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.
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;
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;
Tuesday, August 6, 2013
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; /
Tuesday, July 9, 2013
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.
man commandname --- shows you the manual page for the command
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.
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%
% 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 negroNow 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 >dict2We could check the number of lines in the new file like this:
% wc -l dict2 8The 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.001To make a file, e.g., a shell script mycommand executable, we do this
% chmod +x mycommandNow 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/jeremyJeremy 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.2This 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 b129lab1Things 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: ncftpExample 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> quitExample 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> quitThe ftp program sends files in ascii (text) format unless you specify binary mode:
ftp> binary ftp> put foo ftp> ascii ftp> get barThe 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 arered rojo green verde blue azul white blanco black negroThen 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-0987The 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.001displays the first 10 lines of the file essay.001 To see a specific number of lines, do this:
% head -n 20 essay.001This 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 lprfor information on how it works. See print for information on our local intelligent print command.
mkdir
Use this command to create a directory.% mkdir essaysTo get "into" this directory, do
% cd essaysTo see what files are in essays, do this:
% lsThere 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 poemsThe 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
mv
Use this command to change the name of file and directories.% mv foo foobarThe 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.txtThe file jokes.txt is downloaded from the machine ftp.fubar.net.
% print foo % print notes.ps % print manuscript.dviIn 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 foo jwb321 % print notes.ps jwb321 % print manuscript.dvi jwb321To 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 essaysA 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 solitudeconnects 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 contentsred rojo green verde blue azul white blanco black negroThen we can do this:
% sort dict black negro blue azul green verde red rojo white blancoHere the output of sort went to the screen. To store the output in file we do this:
% sort dict >dict.sortedYou 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.001displays the last 10 lines of the file essay.001 To see a specific number of lines, do this:
% tail -n 20 essay.001This 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.gzdisplays the file names in the compressed archive foo.tar.gz while
% tar -xvzf foo.tar.gzextracts 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 solitudeSee 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 contentsred rojo green verde blue azul white blanco black negroThen we can do this
% wc dict 5 10 56 tmpThis 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
Friday, May 3, 2013
USB Drive Folders Not Showing
To view the data..
execute the command in cmd........
attrib -h -r -s /s /d g:\*.* ;
execute the command in cmd........
attrib -h -r -s /s /d g:\*.* ;
Tuesday, January 29, 2013
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.
Thursday, January 3, 2013
Monday, December 31, 2012
Saturday, December 29, 2012
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
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/
Friday, December 28, 2012
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.
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.
Wednesday, December 26, 2012
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/.* | Extract title from HTML web page | |
sort -t. -k1,1n -k2,2n -k3,3n -k4,4n | Sort IPV4 ip addresses | |
* | echo ‘Test’ | tr ‘[:lower:]‘ ‘[:upper:]‘ | Case conversion |
* | tr -dc ‘[:print:]‘ < /dev/urandom | Filter non printable characters |
* | history | wc -l | Count lines |
sort file1 file2 | uniq | Union of unsorted files | |
sort file1 file2 | uniq -d | Intersection of unsorted files | |
sort file1 file1 file2 | uniq -u | Difference of unsorted files | |
sort file1 file2 | uniq -u | Symmetric Differenceof unsorted files | |
join -a1 -a2 file1 file2 | Union of sorted files | |
join file1 file2 | Intersection of sorted files | |
join -v2 file1 file2 | Difference of sorted files | |
join -v1 -v2 file1 file2 | Symmetric Difference of sorted files | |
* | echo ‘(1 + sqrt(5))/2′ | bc -l | Quick math (Calculate Ï?). See also bc |
* | echo ‘pad=20; min=64; (100*10^6)/((pad+min)*8)’ | bc | More complex (int) e.g. This shows max FastE packet rate |
* | echo ‘pad=20; min=64; print (100E6)/((pad+min)*8)’ | python | Python handles scientific notation |
* | echo ‘pad=20; plot [64:1518] (100*10**6)/((pad+x)*8)’ | gnuplot -persist | Plot FastE packet rate vs packet size |
* | echo ‘obase=16; ibase=10; 64206′ | bc | Base conversion (decimal to hexadecimal) |
* | echo $((0x2dec)) | Base conversion (hex to dec) ((shell arithmetic expansion)) |
* | units -t ’100m/9.74s’ ‘miles/hour’ | Unit conversion (metric to imperial) |
* | units -t ’500GB’ ‘GiB’ | Unit conversion (SI toIEC prefixes) |
* | units -t ’1 googol’ | Definition lookup |
* | seq 100 | (tr ‘\n’ +; echo 0) | bc | Add a column of numbers. See alsoadd and funcpy |
* | cal -3 | Display a calendar |
* | cal 9 1752 | Display a calendar for a particular month year |
* | date -d fri | What date is it this friday. See also day |
* | date –date=’25 Dec’ +%A | What day does xmas fall on, this year |
* | date –date ’1970-01-01 UTC 2147483647 seconds’ | Convert number of seconds since the epoch to a date |
* | TZ=’:America/Los_Angeles’ date | What time is it on West coast of US (use tzselect to find TZ) |
echo “mail -s ‘get the train’P@draigBrady.com < /dev/null” | at 17:45 | Email reminder | |
* | echo “DISPLAY=$DISPLAY xmessage cooker” | at “NOW + 30 minutes” | Popup reminder |
* | printf “%’d\n” 1234 | Print number with thousands grouping appropriate to locale |
* | BLOCK_SIZE=\’1 ls -l | get ls to do thousands grouping appropriate to locale |
* | echo “I live in `locale territory`” | Extract info from locale database |
* | LANG=en_IE.utf8 locale int_prefix | Lookup locale info for specific country. See also ccodes |
* | locale | cut -d= -f1 | xargs locale -kc | less | List fields available in locale database |
* | recode -l | less | Show available conversions (aliases on each line) |
recode windows-1252.. file_to_change.txt | Windows “ansi” to local charset (auto does CRLF conversion) | |
recode utf-8/CRLF.. file_to_change.txt | Windows utf8 to local charset | |
recode iso-8859-15..utf8 file_to_change.txt | Latin9 (western europe) to utf8 | |
recode ../b64 < file.txt > file.b64 | Base64 encode | |
recode /qp.. < file.txt > file.qp | Quoted printable decode | |
recode ..HTML < file.txt > file.html | Text to HTML | |
* | recode -lf windows-1252 | grep euro | Lookup table of characters |
* | echo -n 0×80 | recode latin-9/x1..dump | Show what a code represents in latin-9 charmap |
* | echo -n 0x20AC | recode ucs-2/x2..latin-9/x | Show latin-9 encoding |
* | echo -n 0x20AC | recode ucs-2/x2..utf-8/x | Show utf-8 encoding |
gzip < /dev/cdrom > cdrom.iso.gz | Save copy of data cdrom | |
mkisofs -V LABEL -r dir | gzip > cdrom.iso.gz | Create cdrom image from contents of dir | |
mount -o loop cdrom.iso /mnt/dir | Mount the cdrom image at /mnt/dir (read only) | |
cdrecord -v dev=/dev/cdrom blank=fast | Clear a CDRW | |
gzip -dc cdrom.iso.gz | cdrecord -v dev=/dev/cdrom - | Burn cdrom image (use dev=ATAPI -scanbus to confirm dev) | |
cdparanoia -B | Rip audio tracks from CD to wav files in current dir | |
cdrecord -v dev=/dev/cdrom -audio *.wav | Make audio CD from all wavs in current dir (see also cdrdao) | |
oggenc –tracknum=’track’ track.cdda.wav -o ‘track.ogg’ | Make ogg file from wav file | |
* | ls -lSr | Show files by size, biggest last |
* | du -s * | sort -k1,1rn | head | Show top disk users in current dir. See also dutop |
* | df -h | Show free space on mounted filesystems |
* | df -i | Show free inodes on mounted filesystems |
* | fdisk -l | Show disks partitions sizes and types (run as root) |
* | rpm -q -a –qf ‘%10{SIZE}\t%{NAME}\n’ | sort -k1,1n | List all packages by installed size (Bytes) on rpm distros |
* | dpkg-query -W -f=’${Installed-Size;10}\t${Package}\n’ | sort -k1,1n | List all packages by installed size (KBytes) on deb distros |
* | dd bs=1 seek=2TB if=/dev/null of=ext3.test | Create a large test file (taking no space). See also truncate |
* | tail -f /var/log/messages | Monitor messages in a log file |
* | strace -c ls >/dev/null | Summarise/profile system calls made by command |
* | strace -f -e open ls >/dev/null | List system calls made by command |
* | ltrace -f -e getenv ls >/dev/null | List library calls made by command |
* | lsof -p $$ | List paths that process id has open |
* | lsof ~ | List processes that have specified path open |
* | tcpdump not port 22 | Show network traffic except ssh. See alsotcpdump_not_me |
* | ps -e -o pid,args –forest | List processes in a hierarchy |
* | ps -e -o pcpu,cpu,nice,state,cputime,args –sort pcpu | sed ‘/^ 0.0 /d’ | List processes by % cpu usage |
* | ps -e -orss=,args= | sort -b -k1,1n | pr -TW$COLUMNS | List processes by mem usage. See alsops_mem.py |
* | ps -C firefox-bin -L -o pid,tid,pcpu,state | List all threads for a particular process |
* | ps -p 1,2 | List info for particular process IDs |
* | last reboot | Show system reboot history |
* | free -m | Show amount of (remaining) RAM (-m displays in MB) |
* | watch -n1 ‘cat /proc/interrupts’ | Watch changeable data continuously |
* | uname -a | Show kernel version and system architecture |
* | head -n1 /etc/issue | Show name and version of distribution |
* | cat /proc/partitions | Show all partitions registered on the system |
* | grep MemTotal /proc/meminfo | Show RAM total seen by the system |
* | grep “model name” /proc/cpuinfo | Show CPU(s) info |
* | lspci -tv | Show PCI info |
* | lsusb -tv | Show USB info |
* | mount | column -t | List mounted filesystems on the system (and align output) |
# | dmidecode -q | less | Display SMBIOS/DMI information |
# | smartctl -A /dev/sda | grep Power_On_Hours | How long has this disk (system) been powered on in total |
# | hdparm -i /dev/sda | Show info about disk sda |
# | hdparm -tT /dev/sda | Do a read speed test on disk sda |
# | badblocks -s /dev/sda | Test for unreadable blocks on disk sda |
* | readline | Line editor used by bash, python, bc, gnuplot, … |
* | screen | Virtual terminals with detach capability, … |
* | mc | Powerful file manager that can browse rpm, tar, ftp, ssh, … |
* | gnuplot | Interactive/scriptable graphing |
* | links | Web browser |
* | alias hd=’od -Ax -tx1z -v’ | Handy hexdump. (usage e.g.: * hd /proc/self/cmdline | less) |
* | alias realpath=’readlink -f’ | Canonicalize path. (usage e.g.: * realpath ~/../$USER) |
* | set | grep $USER | Search currentenvironment |
touch -c -t 0304050607 file | Set file timestamp (YYMMDDhhmm) |
Subscribe to:
Posts (Atom)