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 more filename --- shows the first part of a file, just as much as w ill fit on one screen. Just hit the space bar to see more or q to quit. You can use / pattern pattern to search for a pattern. emacs filename emacs filename --- is an editor that lets you create and edit a file. See the emacs page.. page mv filename1 mv filename1 filename2 --- moves a file (i.e. gives it a different name, or moves it into a different directory (see below) cp filename1 cp filename1 filename2 --- copies a file rm filename 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 filename1 filename2 --- compares files, and shows where they differ wc filename 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 o+r filename will make the file readable for everyone, and example, chmod o+r filename chmod o-r filename 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 o gzip filename gzip filename --- compresses files, so that they take up much less space. Usually text files compress to about half their original size, but it depe nds 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. o gunzip filename gunzip filename --- uncompresses files compressed by gzip. o gzcat filename 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 gzcat filename | lpr printing o lpr filename 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
o
o
o
o
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 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 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 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 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 d oing 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
o
o
o
o
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 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 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 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 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 d oing 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 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, page, and find out about the departmental mailing lists (which you can also find in /user/linguistics/helpfile). /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 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. password. ps -u yourusername -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 ha ve 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 nu mber closer to the one of o f 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 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 login: `Logging in' ssh: Connect to another machine logout : `Logging out'
File Management emacs: `Using the emacs text editor' mkdir: `Creating a directory' cd: `Changing your current working directory' ls: `Finding out what files you have' cp: `Making a copy of a file' mv: `Changing the name of a file' rm: `Getting rid of unwanted files' chmod: `Controlling access to your files' cmp: Comparing two files wc: Word, line, and character count compress : Compress a file
Communication e-mail : `Sending and receiving electronic mail' talk : Talk to another user write : Write messages to another user sftp: Secure file transfer protocol
Information man: Manual pages quota -v: Finding out your available disk space quota ical: `Using the Ical personal organizer' finger : Getting information about a user passwd : Changing your password who: Finding out who's logged on
Printing
lpr: `Printing' lprm: Removing a print job lpq: Checking the print queues
Job control ps: `Finding your processes' kill : `Killing a process' nohup: Continuing a job after logout nice: Changing the priority of a job &: `What is a background process?' Cntrl-z : Suspending a process fg: `Resuming a suspended process' Next: Selecting a Unix shell See also: Learning the basic Unix tools Previous: Unix fundamentals Site map Index: Keyword index to help pages Help: New Mexico Tech Computer Center: Help System
Extract lines from a file using a keyword COMMAND DESCRIPTION EXAMPLE grep string file
grep -c string file grep -i string file grep -n string file grep -v string file grep -x string file • • • •
EXPLANATION Searches for the string mike in Searches input file(s) for grep mike the file named letter and prints specified string and prints letter any line with mike in it to the the line with matches. screen. Searches the file bankletter for Searches and prints only grep -c hayes the string hayes and prints the the number of matches to number of matches to the bankletter the screen. screen. Searches file1 for hi, Hi, hI, Searches without regard to grep -i hi file1 and HI and prints all matches to letter case. the screen. Prints to the screen Searches alpha for abc and grep -n abc preceded by the line prints the matches’ lines and alpha number. line numbers to the screen. All lines that do not match grep -v lead Prints all lines in pencils that do pencils are printed. not contain the string lead. Only exact matches are grep -x time Prints only lines in meetings printed. that match time exactly. meetings
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 diretory novel, 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 co ntains 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 foobar letter1
letter2 letter3 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, seecat 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, userm.
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
dummy Under construction
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 1. What are two methods of retrieving SQL? 2. What cursor type do you use to retrieve multiple recordsets?
3. What is the difference between a "where" clause and a "having" clause? "Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command. 4. What is the basic form of a SQL statement to read data out of a table? The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called basic form because of WHERE clause. 5. What structure can you implement for the database to speed up table reads? - Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …) 6. What are the tradeoffs with having indexes ? - 1. Faster selects, slower updates. 2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index. 7. What is a "join"? - ‘join’ used to connect two or more tables logically with or without common field. 8. What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing. 9. What is a "constraint"? - A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE - enforces uniqueness of a particular table column. DEFAULT - specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY - validates that every value in a column exists in a column of another table. CHECK - checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint. NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint. 10. What types of index data structures can you have? - An index helps to faster search values in tables. The three most commonly used index-types are: - B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. - Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) - Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the un derlying data. Takes longer to build and is supported by relatively few databases.
11. What is a "primary key"? - A PRIMARY INDEX or PRIMARY KEY is something which comes mainly from database theory. From its behavior is almost the same as an UNIQUE INDEX, i.e. there may only be one of each value in this column. If you call such an INDEX PRIMARY instead of UNIQUE, you say something about your table design, which I am not able to explain in few words. Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property. 12. What is a "functional dependency"? How does it relate to database table design ? - Functional dependency relates to how one object depends upon the other in the database. for example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2. 13. What is a "trigger"? - Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or de lete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can. 14. Why can a "group by" or "order by" clause be expensive to process ? Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depe nding of the result set can be very expensive. 15. What is "index covering" of a query ? - Index covering means that "Data can be found only using indexes, without touching the tables" 16. What types of join algorithms can you have? 17. What is a SQL view ? - An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often. View c an also be used to restrict users from accessing the tables directly. 18. What are the different types of joins? 19. Explain normalization with examples. 20. What cursor type do you use to retrieve multiple recordsets? 21. Diffrence between a “where” clause and a “having” clause 22. What is the difference between “procedure” and “function”? 23. How will you copy the structure of a table without copying the data? 24. How to find out the database name from SQL*PLUS command prompt? 25. Tadeoffs with having indexes 26. Talk about “Exception Handling” in PL/SQL?
27. What is the diference between “NULL in C” and “NULL in Oracle?” 28. What is Pro*C? What is OCI? 29. Give some examples of Analytical functions. 30. What is the difference between “translate” and “replace”? 31. What is DYNAMIC SQL method 4? 32. How to remove duplicate records from a table? 33. What is the use of ANALYZing the tables? 34. How to run SQL script from a Unix Shell? 35. What is a “transaction”? Why are they necessary? 36. Explain Normalizationa dn Denormalization with examples. 37. When do you get contraint violtaion? What are the types of constraints? 38. How to convert RAW datatype into TEXT? 39. Difference - Primary Key and Aggregate Key 40. How functional dependency is related to database table design? 41. What is a “trigger”? 42. Why can a “group by” or “order by” clause be expensive to process? 43. What are “HINTS”? What is “index covering” of a query? 44. What is a VIEW? How to get script for a view? 45. What are the Large object types suported by Oracle? 46. What is SQL*Loader? 47. Difference between “VARCHAR” and “VARCHAR2″ datatypes. 48. What is the difference among “dropping a table”, “truncating a table” and “deleting all records” from a table. 49. Difference between “ORACLE” and “MICROSOFT ACCESS” databases. 50. How to create a database link?
QL INNER JOIN Keyword The INNER JOIN keyword return rows when there is at least one match in both tables.
SQL INNER JOIN Syntax SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name PS: INNER JOIN is the same as JOIN.
SQL INNER JOIN Example The "Persons" table: P_Id
LastName
FirstName
Address
City
1 2 3
Hansen Svendson Pettersen
Ola Tove Kari
Timoteivn 10 Borgvn 23 Storgt 20
Sandnes Sandnes Stavanger
The "Orders" table: O_Id
OrderNo
P_Id
1 2 3 4 5
77895 44678 22456 24562 34764
3 3 1 1 15
Now we want to list all the persons with any orders. We use the following SELECT statement: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName The result-set will look like this: LastName
FirstName
OrderNo
Hansen Hansen Pettersen Pettersen
Ola Ola Kari Kari
22456 24562 77895 44678
The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT b e listed.
SQL LEFT JOIN Keyword « Previous
Next Chapter »
SQL LEFT JOIN Keyword The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
SQL LEFT JOIN Syntax SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN Example The "Persons" table: P_Id
LastName
FirstName
Address
City
1 2 3
Hansen Svendson Pettersen
Ola Tove Kari
Timoteivn 10 Borgvn 23 Storgt 20
Sandnes Sandnes Stavanger
The "Orders" table: O_Id
OrderNo
P_Id
1 2 3 4 5
77895 44678 22456 24562 34764
3 3 1 1 15
Now we want to list all the persons and their orders - if any, from the tables abo ve. We use the following SELECT statement: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName The result-set will look like this: LastName
FirstName
OrderNo
Hansen Hansen
Ola Ola
22456 24562
Pettersen Pettersen Svendson
Kari Kari Tove
77895 44678
The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).
SQL RIGHT JOIN Keyword « Previous
Next Chapter »
SQL RIGHT JOIN Keyword The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
SQL RIGHT JOIN Syntax SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL RIGHT JOIN Example The "Persons" table: P_Id
LastName
FirstName
Address
City
1 2 3
Hansen Svendson Pettersen
Ola Tove Kari
Timoteivn 10 Borgvn 23 Storgt 20
Sandnes Sandnes Stavanger
The "Orders" table: O_Id
OrderNo
P_Id
1 2 3 4 5
77895 44678 22456 24562 34764
3 3 1 1 15
Now we want to list all the orders with containing persons - if any, from the tables abo ve. We use the following SELECT statement: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName The result-set will look like this: LastName
FirstName
OrderNo
Hansen Hansen Pettersen Pettersen
Ola Ola Kari Kari
22456 24562 77895 44678 34764
The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).
SQL FULL JOIN Keyword The FULL JOIN keyword return rows when there is a match in one of the tables.
SQL FULL JOIN Syntax SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
SQL FULL JOIN Example The "Persons" table: P_Id
LastName
FirstName
Address
City
1 2 3
Hansen Svendson Pettersen
Ola Tove Kari
Timoteivn 10 Borgvn 23 Storgt 20
Sandnes Sandnes Stavanger
The "Orders" table: O_Id
OrderNo
P_Id
1 2 3 4 5
77895 44678 22456 24562 34764
3 3 1 1 15
Now we want to list all the persons and their orders, and all the orders with their persons. We use the following SELECT statement: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName The result-set will look like this: LastName
FirstName
OrderNo
Hansen Hansen Pettersen Pettersen Svendson
Ola Ola Kari Kari Tove
22456 24562 77895 44678 34764
The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.
SQL PRIMARY KEY Constraint « Previous
Next Chapter »
SQL PRIMARY KEY Constraint The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values.
A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only one primary key.
SQL PRIMARY KEY Constraint on CREATE TABLE The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created: MySQL:
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ) SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
SQL PRIMARY KEY Constraint on ALTER TABLE To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD PRIMARY KEY (P_Id) To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).
To DROP a PRIMARY KEY Constraint To drop a PRIMARY KEY constraint, use the following SQL:
SQL FOREIGN KEY Constraint « Previous
Next Chapter »
SQL FOREIGN KEY Constraint A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Let's illustrate the foreign key with an example. Look at the following two tables: The "Persons" table: P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2 3
Svendson Pettersen
Tove Kari
Borgvn 23 Storgt 20
Sandnes Stavanger
The "Orders" table: O_Id
OrderNo
P_Id
1 2 3 4
77895 44678 22456 24562
3 3 2 1
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table. The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table. The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table. The FOREIGN KEY constraint is used to prevent actions that would destroy link between tables. The FOREIGN KEY constraint also prevents that invalid data is inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
SQL FOREIGN KEY Constraint on CREATE TABLE The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created: MySQL:
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ) SQL Server / Oracle / MS Access:
CREATE TABLE Orders
( O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, P_Id int FOREIGN KEY REFERENCES Persons(P_Id) ) To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) )
SQL FOREIGN KEY Constraint on ALTER TABLE To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL: MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax: MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
To DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL: 1.Simple/Inner/Natural Join 2.Equi(contains only = sign) /Non-Equi Joins(other simbles) 3.Outer Joins(Left Outer Join/Right Outer Join) 4.Cross Join 5.Self Join
Types of joins ? Answer #9 1.Cross Join - is a join without a join contition SELECT * FROM table-1 CROSS JOIN table-2 2.Inner Join - return only matched records i.Equi-Join ii.Non-Equi Join * Inner joins or equi joins are the most common type of joins, they use equality "=" of common attributes to join tables. SELECT projects.name AS "Project Name", teachers.name FROM teachers, projects WHERE teachers.id = projects.teacher; * Like an inner join, but with an operator different from "=" in the condition, e.g., not equal "<>". SELECT projects.name AS "Project Name", teachers.name FROM teachers, projects WHERE teachers.id != projects.teacher; 3.Outer Join i.Full Outer Join 11.Left Outer Join iii.Right Outer Join LEFT -- only unmatched rows from the left side table (table1) are retained RIGHT -- only unmatched rows from the right side table (table-2) are retained FULL -- unmatched rows from both tables (table-1 and table2) are retained SELECT assign.project, projects.name, assign.percentage FROM projects LEFT OUTER JOIN assign ON projects.id = assign.project ; 4.Self Join - With table aliases you can join a table to itself. SELECT DISTINCT * FROM assign a, assign b WHERE a.stud = 2 AND b.stud = 4 AND a.project = b.project; 5.Multi Join - The number of tables involved in a join are not restricted. SELECT DISTINCT assign.project, projects.name AS "Project", assign.percentage, stud.name AS "Student" FROM projects, assign, stud WHERE projects.id = 1 AND projects.id = assign.project
AND assign.stud = stud.id ;
Left outer join: Oracle8i select last_name, department_name from employees e, departments d where e.department_id = d.department_id(+);
Left outer join: Oracle9i select last_name, department_name from employees e left outer join departments d on e.department_id = d.department_id;
Right Outer Join A right outer join will return all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order. The query that was used as the left outer join example could be rewritten as a right outer join as follows:
SELECT EMPNO, LASTNAME, PROJNO FROM CORPDATA.PROJECT RIGHT OUTER JOIN CORPDATA.EMPLOYEE ON EMPNO = RESPEMP WHERE LASTNAME > 'S'
Outer join of two tables
Example query (left outer join) select * from state left outer join region on state.city = region.city;
Result State:city
State:state
Region:city
Region:area
Jacksonville
FL
Jacksonville
South
Miami
FL
Miami
South
Nashville
TN
NULL
NULL
Example query (right outer join) select * from state right outer join region on state.city = region.city;
Result State:city
State:state
Region:city
Region:area
Jacksonville
FL
Jacksonville
South
Miami
FL
Miami
South
NULL
NULL
New Orleans
South
Example query (full outer join) select * from state full outer join region on state.city = region.city;
Result State:city
State:state
Region:city
Region:area
Jacksonville
FL
Jacksonville
South
Miami
FL
Miami
South
Nashville
TN
NULL
NULL
NULL
NULL
New Orleans
South
Important: These examples use the tables introduced on page Join of two tables.
Outer joins In most cases, tables are joined according to search conditions that find only the rows with matching values; this type of join is known as an inner equijoin. In some cases, however, decision-support analysis requires outer joins, which retrieve both matching and non-matching rows, or non-equijoins, which express, for example, a greater-than or lessthan relationship.
An outer join operation returns all the rows returned by an inner join plus all the rows from one table that do not match any row from the other table. An outer join can be left, right, or full, depending on whether rows from the left, right, or both tables are retained. The first table listed in the FROM clause is referred to as the left table and the second as the right table. For all three types of outer join, NULLs are used to represent empty columns in rows that do not match.
Syntax As shown in the preceding examples, an outer join between two tables can be specified in the FROM clause with the OUTER JOIN keywords followed by the ON subclause: >>-FROM--table_1--+-LEFT--+--OUTER JOIN--table_2----------------> +-RIGHT-+ '-FULL--' >--ON--table_1.column--=--table_2.column-----------------------><
sQL Commands: SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality: •
•
•
•
Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE. Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE. Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT. Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE
SQL SELECT Statement The most commonly used SQL command is SELECT statement. The SQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To
create a simple SQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement.
Syntax of SQL SELECT Statement: SELECT column_list FROM table-name [WHERE Clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause]; • • •
table-name is the name of the table from which the information is retrieved. column_list includes one or more columns from which data is retrieved. The code within the brackets is optional.
database table student_details; id 100 101 102 103 104
first_name last_name age subject games Rahul Sharma 10 Science Cricket Anjali Bhagwat 12 Maths Football Stephen Fleming 09 Science Cricket Shekar Gowda 18 Maths Badminton Priya Chandra 15 Economics Chess
NOTE: These database tables are used here for better explanation of SQL commands. In reality, the tables can have different columns and different data.
For example, consider the table student_details. To select the first name of all the students the query would be like: SELECT first_name FROM student_details;
NOTE: The commands are not case sensitive. The above SELECT statement can also be written as "select first_name from students_details;"
You can also retrieve data from more than one column. For example, to select first name and last name of all the students. SELECT first_name, last_name FROM student_details;
You can also use clauses like WHERE, GROUP BY, HAVING, ORDER BY with SELECT statement. We will discuss these commands in coming ch apters. NOTE: In a SQL SELECT statement only SELECT and FROM statements are mandatory. Other clauses like WHERE, ORDER BY, GROUP BY, HAVING are optional.
How to use expressions in SQL SELECT Statement? Expressions combine many arithmetic operators, they can be used in SELECT, WHERE and ORDER BY Clauses of the SQL SELECT Statement. Here we will explain how to use expressions in the SQL SELECT Statement. About using expressions in WHERE and ORDER BY clause, they will be explained in their respective sections. The operators are evaluated in a specific order of precedence, when more than one arithmetic operator is used in an expression. The order of evaluation is: parentheses, division, multiplication, addition, and subtraction. The evaluation is performed from the left to the right of the expression. For example: If we want to display the first and last name of an employee combined together, the SQL Select Statement would be like SELECT first_name || ' ' || last_name FROM employee;
Output:
first_name || ' ' || last_name --------------------------------Rahul Sharma Anjali Bhagwat Stephen Fleming Shekar Gowda Priya Chandra You can also provide aliases as below. SELECT first_name || ' ' || last_name AS emp_name FROM employee;
Output:
emp_name ------------Rahul Sharma Anjali Bhagwat Stephen Fleming Shekar Gowda Priya Chandra
SQL Alias SQL Aliases are defined for columns and tables. Basically aliases is created to make the column selected more readable. For Example: To select the first name of all the students, the query would be like:
Aliases for columns: SELECT first_name AS Name FROM student_details; or SELECT first_name Name FROM student_details;
In the above query, the column first_name is given a alias as 'name'. So when the result is displayed the column name appears as 'Name' instead of 'first_name'. Output:
Name ------------Rahul Sharma Anjali Bhagwat Stephen Fleming Shekar Gowda Priya Chandra
Aliases for tables: SELECT s.first_name FROM student_details s;
In the above query, alias 's' is defined for the table student_details and the column first_name is selected from the table. Aliases is more useful when • • • •
There are more than one tables involved in a query, Functions are used in the query, The column names are big or not readable, More than one columns are combined together
SQL WHERE Clause The WHERE Clause is used when you want to retrieve specific information from a table excluding other irrelevant data. For example, when you want to see the information about students in class 10th only then you do need the information about the students in other
class. Retrieving information about all the students would increase the processing time for the query. So SQL offers a feature called WHERE clause, which we can use to restrict the data that is retrieved. The condition you provide in the WHERE clause filters the rows retrieved from the table and gives you only those rows which you expected to see. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.
Syntax of SQL WHERE Clause: WHERE {column or expression} comparison-operator value Syntax for a WHERE clause with Select statement is: SELECT column_list FROM table-name WHERE condition; • • •
column or expression - Is the column of a table or a expression comparison-operator - operators like = < > etc. value - Any user value or a column name for comparison
For Example: To find the name of a student with id 100, the query would be like: SELECT first_name, last_name FROM student_details WHERE id = 100;
Comparison Operators and Logical Operators are used in WHERE Clause. These operators are discussed in the next chapter. NOTE: Aliases defined for the columns in the SELECT statement cann ot be used in the WHERE clause to set conditions. Only aliases created for tables can be used to reference the columns in the table.
How to use expressions in the WHERE Clause? Expressions can also be used in the WHERE clause of the SELECT statement. For example: Lets consider the employee table. If you want to display employee name, current salary, and a 20% increase in the salary for only those products where the percentage increase in salary is greater than 30000, the SELECT statement can be written as shown below SELECT name, salary, salary*1.2 AS new_salary FROM employee WHERE salary*1.2 > 30000;
Output:
name ---------Hrithik Harsha Priya
salary new_salary -----------------------35000 37000 35000 37000 30000 360000
NOTE: Aliases defined in the SELECT Statement can be used in WHERE Clause.
SQL Operators There are two type of Operators, namely Comparison Operators and Logical Operators. These operators are used mainly in the WHERE clause, HAVING clause to filter the data to be selected.
Comparison Operators: Comparison operators are used to compare the column data with specific values in a condition. Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions. The below table describes each comparison operator. Comparison Operators = <>, != < >
>= <=
Description
equal to is not equal to less than greater than greater than or equal to less than or equal to
Logical Operators: There are three Logical Operators namely AND, OR and NOT. Logical operators are discussed in detail in the next section.
SQL Logical Operators There are three Logical Operators namely, AND, OR, and NOT. These op erators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.
SQL Comparison Keywords There are other comparison keywords available in sql which are used to enhance the search capabilities of a sql query. They are "IN", "BETWEEN...AND", "IS NULL", "LIKE". Comparision Operators
Description
column value is similar to specified character(s). column value is equal to any one of IN a specified set of values. column value is between two BETWEEN...AND values, including the end values specified in the range. IS NULL column value does not exist. LIKE
SQL LIKE Operator The LIKE operator is used to list all rows in a table whose column values match a specified pattern. It is useful when you want to search rows to match a specific pattern, or when you do not know the entire value. For this purpose we use a wildcard character '%'. For example: To select all the students whose name begins with 'S' SELECT first_name, last_name FROM student_details WHERE first_name LIKE 'S%';
The output would be similar to: first_name ------------Stephen Shekar
last_name ------------Fleming Gowda
The above select statement searches for all the rows where the first letter of the column first_name is 'S' and rest of the letters in the name can be any character.
There is another wildcard character you can use with LIKE operator. It is the underscore character, ' _ ' . In a search string, the underscore signifies a single character. For example: to display all the names with 'a' second character, SELECT first_name, last_name FROM student_details WHERE first_name LIKE '_a%';
The output would be similar to: first_name ------------Rahul
last_name ------------Sharma
NOTE:Each underscore act as a placeholder for only one character. So you can use more than one underscore. Eg: ' __i% '-this has two underscores towards the left, 'S__j%' - this has two underscores between character 'S' and 'i'.
SQL BETWEEN ... AND Operator The operator BETWEEN and AND, are used to compare data for a range of values. For Example: to find the names of the students between age 10 to 15 years, the query would be like, SELECT first_name, last_name, age FROM student_details WHERE age BETWEEN 10 AND 15;
The output would be similar to: firs irst_n t_name ame --------------------Rahul Anajali Shekar
last_n st_na ame age --------------------- ---------Sharma 10 Bhagwat 12 Gowda 15
SQL IN Operator: The IN operator is used when you want to compare a column with more than one value. It is similar to an OR condition. For example: If you want to find the names of o f students who are studying either Maths or Science, the query would be like,
SELECT first_name, last_name, subject FROM student_details WHERE subject IN ('Maths', 'Science');
The output would be similar to: first_na first_name me last_nam last_namee subject subject ------------------------------- ----------------------Anajali Bhagwat Maths Shekar Gowda Maths Rahul Sharma Science Stephen Fleming Science
You can include more subjects in the list like ('maths','science','history') NOTE:The data used to compare is case sensitive.
SQL IS NULL Operator A column value is NULL if it does not exist. The IS NULL operator is used to display all the rows for columns that do not have a value. For Example: If you want to find the names of students who do not participate in any games, the query would be as given below SELECT first_name, last_name FROM student_details WHERE games IS NULL
There would be no output as we have every student participate in a game in the table student_details, else the names of the students who do not participate in any games would be displayed
SQL ORDER BY The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts query results in ascending order by default.
Syntax for using SQL ORDER BY clause to sort data is: SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1 [, column2, .. columnN] [DESC]];
database table "employee";
id 100 101 101 102 103 104
name dept age Ramesh Electrical 24 Hrithik Electronics 28 Harsha Aeronautics 28 Soumya Electronics 22 Priya InfoTech 25
salary 25000 3500 5000 35000 20000 30000
location Bangalore Banga ngalore Mysore Bangalore Mangalore
For Example: If you want to sort the employee table by b y salary of the employee, the sql query would be. SELECT name, salary FROM employee ORDER BY salary;
The output would be like name ---------Soumya Ramesh Priya Hrithik Harsha
salary ---------20000 25000 30000 35000 35000
The query first sorts the result according to name and then displays it. You can also use more than one column in the ORDER BY clause. If you want to sort the employee table by the name and salary, the query would be like, SELECT name, salary FROM employee ORDER BY name, salary;
The output would be like: name salary ------------------------- ------------------------Soumya 20000 Ramesh 25000 Priya 30000 Harsha 35000 Hrithik 35000 NOTE:The columns specified in ORDER BY clause should be one of the columns selected in the SELECT column list.
You can represent the columns in the ORDER BY clause by specifying the position of a column in the SELECT list, instead of writing the column name. The above query can also be written as given below, SELECT name, salary FROM employee ORDER BY 1, 2;
By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it as shown below. SELECT name, salary FROM employee ORDER BY name, salary DESC;
The above query sorts only the column 'salary' in descending order and the column 'name' by ascending order. If you want to select both name and salary in descending order, the query would be as given below. SELECT name, salary FROM employee ORDER BY name DESC, salary DESC;
How to use expressions in the ORDER BY Clause? Expressions in the ORDER BY clause of a SELECT statement. For example: If you want to display employee name, current salary, and a 20% increase in the salary for only those employees for whom the percentage increase in salary is greater than 30000 and in descending order of the increased price, the SELECT statement can be written as shown below SELECT name, salary, salary*1.2 AS new_salary FROM employee WHERE salary*1.2 > 30000 ORDER BY new_salary DESC;
The output for the above query is as follows. name ---------Hrithik Harsha Priya
salary new_salary ---------- ------------35000 37000 35000 37000 30000 36000
NOTE:Aliases defined in the SELECT Statement can be used in ORDER BY Clause.
SQL GROUP Functions Group functions are built-in SQL functions that operate on groups of rows and return one value for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT SQL COUNT (): This function returns the number of rows in the table that satisfies the condition specified in the WHERE condition. If the WHERE condition is not specified, then the query returns the total number of rows in the table. For Example: If you want the number of employees in a particular department, the query would be: SELECT COUNT (*) FROM employee WHERE dept = 'Electronics';
The output would be '2' rows. If you want the total number of employees in all the department, the query wou ld take the form: SELECT COUNT (*) FROM employee;
The output would be '5' rows.
SQL DISTINCT(): This function is used to select the distinct rows. For Example: If you want to select all distinct department names from employee table, the query would be: SELECT DISTINCT dept FROM employee;
To get the count of employees with unique name, the query would be: SELECT COUNT (DISTINCT name) FROM employee;
SQL MAX(): This function is used to get the maximum value from a column.
To get the maximum salary drawn by an employee, the query would be: SELECT MAX (salary) FROM employee;
SQL MIN(): This function is used to get the minimum value from a column.
To get the minimum salary drawn by an employee, he query would be: SELECT MIN (salary) FROM employee;
SQL AVG(): This function is used to get the average value of a numeric column.
To get the average salary, the query would be SELECT AVG (salary) FROM employee;
SQL SUM(): This function is used to get the sum of a numeric column
To get the total salary given out to the employees, SELECT SUM (salary) FROM employee;
SQL GROUP BY Clause The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns. For Example: If you want to know the total amount of salary spent on each department, the query would be: SELECT dept, SUM (salary) FROM employee GROUP BY dept;
The output would be like: dept
salary ---------------------------Electrical 25000 Electronics 55000 Aeronautics 35000 InfoTech 30000
NOTE: The group by clause should contain all the columns in the select list expect those used along with the group functions. SELECT location, dept, SUM (salary) FROM employee GROUP BY location, dept;
The output would be like: location dept salary ------------- --------------- ----------Bangalore Electrical 25000 Bangalore Electronics 55000 Mysore Aeronautics 35000 Mangalore InfoTech 30000
SQL HAVING Clause Having clause is used to filter data based on the group functions. This is similar to WHERE condition but is used with group functions. Group functions cannot be used in WHERE Clause but can be used in HAVING clause. For Example: If you want to select the department that has total salary paid for its employees more than 25000, the sql query would be like; SELECT dept, SUM (salary) FROM employee GROUP BY dept HAVING SUM (salary) > 25000
The output would be like: dept ------------Electronics Aeronautics InfoTech
salary
------------55000 35000 30000
When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement, the WHERE clause is processed first, then the rows that are returned after the WHERE clause is executed are grouped based on the GROUP BY clause. Finally, any conditions on the group functions in the HAVING clause are applied to the grouped rows before the final output is displayed.
SQL INSERT Statement The INSERT Statement is used to add new rows of data to a table. We can insert data to a table in two ways, 1) Inserting the data directly to a table.
Syntax for SQL INSERT is: INSERT INTO TABLE_NAME [ (col1, col2, col3,...colN)] VALUES (value1, value2, value3,...valueN); •
col1, col2,...colN -- the names of the columns in the table into which you want to insert data.
While inserting a row, if you are adding value for all the columns of the table you need not specify the column(s) name in the sql query. But you need to make sure the order of the values is in the same order as the columns in the table. The sql insert query will be as follows INSERT INTO TABLE_NAME VALUES (value1, value2, value3,...valueN);
For Example: If you want to insert a row to the employee table, the query would be like, INSERT INTO employee (id, name, dept, age, salary location) VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);
NOTE:When adding a row, only the characters or date values should be enclosed with single quotes.
If you are inserting data to all the columns, the column names can be omitted. The above insert statement can also be written as, INSERT INTO employee VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);
Inserting data to a table through a select statement.
Syntax for SQL INSERT is: INSERT INTO table_name [(column1, column2, ... columnN)] SELECT column1, column2, ...columnN FROM table_name [WHERE condition];
For Example: To insert a row into the employee table from a temporary table, the sql insert query would be like, INSERT INTO employee (id, name, dept, age, salary location) SELECT emp_id, emp_name, dept, age, salary, location FROM temp_employee;
If you are inserting data to all the columns, the above insert statement can also be written as, INSERT INTO employee SELECT * FROM temp_employee;
NOTE:We have assumed the temp_employee table has columns emp_id, emp_name, dept, age, salary, location in the above given order and the same datatype. IMPORTANT NOTE:
1) When adding a new row, you should ensure the datatype of the value and the column matches 2) You follow the integrity constraints, if any, defined for the table.
SQL UPDATE Statement The UPDATE Statement is used to modify the existing rows in a table.
The Syntax for SQL UPDATE Command is: UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... [WHERE condition] • • •
table_name - the table name which has to be updated. column_name1, column_name2.. - the columns that gets changed. value1, value2... - are the new values.
NOTE:In the Update statement, WHERE clause identifies the rows that get affected. If you do not include the WHERE clause, column values for all the rows get affected. For Example: To update the location of an employee, the sql update query would be like, UPDATE employee SET location ='Mysore' WHERE id = 101;
To change the salaries of all the employees, the query would be, UPDATE employee SET salary = salary + (salary * 0.2);
SQL Delete Statement The DELETE Statement is used to delete rows from a table. The Syntax of a SQL DELETE statement is: DELETE FROM table_name [WHERE condition]; •
table_name -- the table name which has to be updated.
NOTE:The WHERE clause in the sql delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause. For Example: To delete an employee with id 100 from the employee table, the sql delete query would be like, DELETE FROM employee WHERE id = 100;
To delete all the rows from the employee table, the query would be like, DELETE FROM employee;
SQL TRUNCATE Statement The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
Syntax to TRUNCATE a table: TRUNCATE TABLE table_name;
For Example: To delete all the rows from employee table, the query would be like, TRUNCATE TABLE employee;
Difference between DELETE and TRUNCATE Statements: DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.
TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.
SQL DROP Statement: The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using RENAME command. When a table is dropped all the references to the table will not be valid. Syntax to drop a sql table structure: DROP TABLE table_name;
For Example: To drop the table employee, the query would be like DROP TABLE employee;
Difference between DROP and TRUNCATE Statement:
If a table is dropped, all the relationships with other tables will no longer be v alid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.
SQL CREATE TABLE Statement The CREATE TABLE Statement is used to create tables to store data. Integrity Constraints like primary key, unique key, foreign key can be defined for the columns while creating the table. The integrity constraints can be defined at column level or table level. The implementation and the syntax of the CREATE Statements differs for different RDBMS.
The Syntax for the CREATE TABLE Statement is: CREATE TABLE table_name (column_name1 datatype, column_name2 datatype, ... column_nameN datatype ); • • •
table_name - is the name of the table. column_name1, column_name2.... - is the name of the columns datatype - is the datatype for the column like char, date, number etc.
For Example: If you want to create the employee table, the statement would be like, CREATE TABLE employee ( id number(5), name char(20), dept char(10), age number(2), salary number(10), location char(10) );
In Oracle database, the datatype for an integer column is represented as "number". In Sybase it is represented as "int". Oracle provides another way of creating a table. CREATE TABLE temp_employee SELECT * FROM employee
In the above statement, temp_employee table is created with the same number of columns and datatype as employee table.
SQL ALTER TABLE Statement The SQL ALTER TABLE command is used to modify the definition (structure) of a table by modifying the definition of its columns. The ALTER command is used to perform the following functions. 1) Add, drop, modify table columns 2) Add and drop constraints 3) Enable and Disable constraints
Syntax to add a column ALTER TABLE table_name ADD column_name datatype;
For Example: To add a column "experience" to the employee table, the query would be like ALTER TABLE employee ADD experience number(3);
Syntax to drop a column ALTER TABLE table_name DROP column_name;
For Example: To drop the column "location" from the employee table, the query would be like
ALTER TABLE employee DROP location;
Syntax to modify a column ALTER TABLE table_name MODIFY column_name datatype;
For Example: To modify the column salary in the employee table, the query would be like ALTER TABLE employee MODIFY salary number(15,2);
SQL RENAME Command The SQL RENAME command is used to change the name of the table or a database object. If you change the object's name any reference to the old name will be affected. You have to manually change the old name to the new name in every reference.
Syntax to rename a table RENAME old_table_name To new_table_name;
For Example: To change the name of the table employee to my_employee, the query would be like RENAME employee TO my_emloyee;
SQL Integrity Constraints Integrity Constraints are used to apply business rules for the database tables. The constraints available in SQL are Foreign Key, Not Null, Unique, Check. Constraints can be defined in two ways 1) The constraints can be specified immediately after the column definition. This is called column-level definition. 2) The constraints can be specified after all the columns are defined. This is called tablelevel definition.
1) SQL Primary key: This constraint defines a column or combination of columns which uniquely identifies each row in the table. Syntax to define a Primary key at column level:
column name datatype [CONSTRAINT constraint_name] PRIMARY KEY
Syntax to define a Primary key at table level: [CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..) •
•
column_name1, column_name2 are the names of the columns which define the primary Key. The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.
For Example: To create an employee table with Primary Key constraint, the query would be like. Primary Key at table level: CREATE TABLE employee ( id number(5) PRIMARY KEY, name char(20), dept char(10), age number(2), salary number(10), location char(10) );
or CREATE TABLE employee ( id number(5) CONSTRAINT emp_id_pk PRIMARY KEY, name char(20), dept char(10), age number(2), salary number(10), location char(10) );
Primary Key at table level: CREATE TABLE employee ( id number(5), name char(20), dept char(10), age number(2), salary number(10), location char(10), CONSTRAINT emp_id_pk PRIMARY KEY (id) );
2) SQL Foreign key or Referential Integrity : This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different
tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key. Syntax to define a Foreign key at column level: [CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)
Syntax to define a Foreign key at table level: [CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);
For Example:
1) Lets use the "product" table and "order_items". Foreign Key at column level: CREATE TABLE product ( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY, product_name char(20), supplier_name char(20), unit_price number(10) ); CREATE TABLE order_items ( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY, product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id), product_name char(20), supplier_name char(20), unit_price number(10) );
Foreign Key at table level: CREATE TABLE order_items ( order_id number(5) , product_id number(5), product_name char(20), supplier_name char(20), unit_price number(10) CONSTRAINT od_id_pk PRIMARY KEY(order_id), CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id) );
2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the qu ery would be like,
CREATE TABLE employee ( id number(5) PRIMARY KEY, name char(20), dept char(10), age number(2), mgr_id number(5) REFERENCES employee(id), salary number(10), location char(10) );
3) SQL Not Null Constraint : This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed. Syntax to define a Not Null constraint: [CONSTRAINT constraint name] NOT NULL
For Example: To create a employee table with Null value, the query would be like CREATE TABLE employee ( id number(5), name char(20) CONSTRAINT nm_nn NOT NULL, dept char(10), age number(2), salary number(10), location char(10) );
4) SQL Unique Key: This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated. Syntax to define a Unique key at column level: [CONSTRAINT constraint_name] UNIQUE
Syntax to define a Unique key at table level: [CONSTRAINT constraint_name] UNIQUE(column_name)
For Example: To create an employee table with Unique key, the query would be like, Unique Key at column level: CREATE TABLE employee ( id number(5) PRIMARY KEY,
name char(20), dept char(10), age number(2), salary number(10), location char(10) UNIQUE );
or CREATE TABLE employee ( id number(5) PRIMARY KEY, name char(20), dept char(10), age number(2), salary number(10), location char(10) CONSTRAINT loc_un UNIQUE );
Unique Key at table level: CREATE TABLE employee ( id number(5) PRIMARY KEY, name char(20), dept char(10), age number(2), salary number(10), location char(10), CONSTRAINT loc_un UNIQUE(location) );
5) SQL Check Constraint : This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied for a single column or a group of columns. Syntax to define a Check constraint: [CONSTRAINT constraint_name] CHECK (condition)
For Example: In the employee table to select the gender of a person, the query would be like Check Constraint at column level: CREATE TABLE employee ( id number(5) PRIMARY KEY, name char(20), dept char(10), age number(2), gender char(1) CHECK (gender in ('M','F')), salary number(10),
location char(10) );
Check Constraint at table level: CREATE TABLE employee ( id number(5) PRIMARY KEY, name char(20), dept char(10), age number(2), gender char(1), salary number(10), location char(10), CONSTRAINT gender_ck CHECK (gender in ('M','F')) );
SQL Joins SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements. The Syntax for joining two tables is: SELECT col1, col2, col3... FROM table_name1, table_name2 WHERE table_name1.col2 = table_name2.col1;
If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute. Lets use the below two tables to explain the sql join conditions. database table "product"; product_id product_name supplier_name unit_price 100 Camera Nikon 300 101 Television Onida 100 102 Refrigerator Vediocon 150 103 Ipod Apple 75 104 Mobile Nokia 50 database table "order_items";
order_id 5100 5101 5102 5103
product_id 104 102 103 101
total_units 30 5 25 10
customer Infosys Satyam Wipro TCS
SQL Joins can be classified into Equi join and Non Equi join. 1) SQL Equi joins
It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join. For example: You can get the information about a customer who purchased a product and the quantity of product. 2) SQL Non equi joins
It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=
1) SQL Equi Joins: An equi-join is further classified into two categories: a) SQL Inner Join b) SQL Outer Join
a) SQL Inner Join: All the rows returned by the sql query satisfy the sql join c ondition specified. For example: If you want to display the product information for each order the query will be as given below. Since you are retrieving the data from two tables, you need to identify the common column between these two tables, which is theproduct_id.
The query for this type of sql joins would be like, SELECT order_id, product_name, unit_price, supplier_name, total_units FROM product, order_items WHERE order_items.product_id = product.product_id;
The columns must be referenced by the table name in the join condition, because product_id is a column in both the tables and needs a way to be identified. This avoids ambiguity in using the columns in the SQL SELECT statement.
The number of join conditions is (n-1), if there are more than two tables joined in a query where 'n' is the number of tables involved. The rule must be true to avoid Cartesian product. We can also use aliases to reference the column name, then the above query would be like, SELECT o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units FROM product p, order_items o WHERE o.product_id = p.product_id;
b) SQL Outer Join: This sql join condition returns all rows from both tables which satisfy the join condition along with rows which do not satisfy the join condition from one of the tables. The sql outer join operator in Oracle is ( + ) and is used on one side of the join condition only. The syntax differs for different RDBMS implementation. Few of them represent the join conditions as "sql left outer join", "sql right outer join". If you want to display all the product data along with order items data, with null values displayed for order items if a product has no order item, the sql query for outer join would be as shown below: SELECT p.product_id, p.product_name, o.order_id, o.total_units FROM order_items o, product p WHERE o.product_id (+) = p.product_id;
The output would be like, product_id product_name order_id ----------------------- -------------100 Camera 101 Television 5103 102 Refrigerator 5101 103 Ipod 5102 104 Mobile 5100
total_units
------------10 5 25 30
NOTE:If the (+) operator is used in the left side of the join condition it is equivalent to left outer join. If used on the right side of the join condition it is equivalent to right outer join.
SQL Self Join:
A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary to ensure that the join statement defines an alias for both copies of the table to avoid column ambiguity. The below query is an example of a self join, SELECT a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name FROM sales_person a, sales_person b WHERE a.manager_id = b.sales_person_id;
2) SQL Non Equi Join: A Non Equi Join is a SQL Join whose condition is established using all comparison operators except the equal (=) operator. Like >=, <=, <, > For example: If you want to find the names of students who are not studying either Economics, the sql query would be like, (lets use student_details table defined earlier.) SELECT first_name, last_name, subject FROM student_details WHERE subject != 'Economics'
The output would be something like, first_name last_name subject --------------------- ---------------Anajali Bhagwat Maths Shekar Gowda Maths Rahul Sharma Science Stephen Fleming Science
SQL Views A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based. The Syntax to create a sql view is CREATE VIEW view_name AS
SELECT column_list FROM table_name [WHERE condition]; • •
view_name is the name of the VIEW. The SELECT statement is used to define the columns and rows that you want to display in the view.
For Example: to create a view on the product table the sql query would be like CREATE VIEW view_product AS SELECT product_id, product_name FROM product;
SQL Subquery Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value. Subqueries are an alternate way of returning d ata from multiple tables. Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc. • • • •
SELECT INSERT UPDATE DELETE
For Example:
1) Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like, SELECT first_name, last_name, subject FROM student_details WHERE games NOT IN ('Cricket', 'Football');
The output would be similar to: first_name last_name games ------------- ------------- ----------
Shekar Priya
Gowda Chandra
Badminton Chess
2) Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below, SELECT id, first_name FROM student_details WHERE first_name IN ('Rahul', 'Stephen');
but, if you do not know their names, then to get their id's you need to write the query in this manner, SELECT id, first_name FROM student_details WHERE first_name IN (SELECT first_name FROM student_details WHERE subject= 'Science');
Output: id -------100 102
first_name ------------Rahul Stephen
In the above sql statement, first the inner query is processed first and then the outer query is processed.
3) Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study Maths in a table 'maths_group'. INSERT INTO maths_group(id, name) SELECT id, first_name || ' ' || last_name FROM student_details WHERE subject= 'Maths'
4) A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section. select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101
product_name supplier_name order_id ------------------ ------------------ ---------Television Onida 5103
Correlated Subquery A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed. SELECT p.product_name FROM product p WHERE p.product_id = (SELECT o.product_id FROM order_items o WHERE o.product_id = p.product_id);
NOTE: 1) You can nest as many queries you want but it is recommended not to nest more than 16 subqueries in oracle. 2) If a subquery is not dependent on the outer query it is called a non-correlated subquery.
SQL Index Index in sql is created on existing tables to retrieve the rows quickly. When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row. Syntax to create Index: CREATE INDEX index_name ON table_name (column_name1,column_name2...);
Syntax to create SQL unique Index: CREATE UNIQUE INDEX index_name ON table_name (column_name1,column_name2...); • • •
index_name is the name of the INDEX. table_name is the name of the table to which the indexed column belongs. column_name1, column_name2.. is the list of columns which make up the INDEX.
In Oracle there are two types of SQL index namely, implicit and explicit.
Implicit Indexes: They are created when a column is explicity defined with PRIMARY KEY, UNIQUE KEY Constraint.
Explicit Indexes: They are created using the "create index.. " syntax. NOTE: 1) Even though sql indexes are created to access the rows in the table quickly, they slow down DML operations like INSERT, UPDATE, DELETE on the table, because the indexes and tables both are updated along when a DML operation is performed. So use indexes only on columns which are used to search the table frequently. 2) Is is not required to create indexes on table which have less data. 3) In oracle database you can define up to sixteen (16) columns in an INDEX.
SQL Tuning or SQL Optimization Sql Statements are used to retrieve data from the database. We can get same results by writing different sql queries. But use of the best qu ery is important when performance is considered. So you need to sql query tuning based on the requirement. Here is the list of queries which we use reqularly and how these sql queries can be optimized for better performance.
SQL Tuning/SQL Optimization Techniques: 1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'. For Example: Write the query as SELECT id, first_name, last_name, age, subject FROM student_details;
Instead of: SELECT * FROM student_details;
2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes. For Example: Write the query as
SELECT subject, count(subject) FROM student_details WHERE subject != 'Science' AND subject != 'Maths' GROUP BY subject;
Instead of: SELECT subject, count(subject) FROM student_details GROUP BY subject HAVING subject!= 'Vancouver' AND subject!= 'Toronto';
3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query. For Example: Write the query as SELECT name FROM employee WHERE (salary, age ) = (SELECT MAX (salary), MAX (age) FROM employee_details) AND dept = 'Electronics';
Instead of: SELECT name FROM employee WHERE salary = (SELECT MAX(salary) FROM employee_details) AND age = (SELECT MAX(age) FROM employee_details) AND emp_dept = 'Electronics';
4) Use operator EXISTS, IN and table joins appropriately in your query. a) Usually IN has the slowest performance. b) IN is efficient when most of the filter criteria is in the sub-query. c) EXISTS is efficient when most of the filter criteria is in the main query. For Example: Write the query as Select * from product p where EXISTS (select * from order_items o where o.product_id = p.product_id)
Instead of: Select * from product p where product_id IN (select product_id from order_items
5) Use EXISTS instead of DISTINCT when using joins which involves tables having one-to-many relationship. For Example: Write the query as SELECT d.dept_id, d.dept FROM dept d WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);
Instead of: SELECT DISTINCT d.dept_id, d.dept FROM dept d,employee e WHERE e.dept = e.dept;
6) Try to use UNION ALL in place of UNION. For Example: Write the query as SELECT id, first_name FROM student_details_class10 UNION ALL SELECT id, first_name FROM sports_team;
Instead of: SELECT id, first_name, subject FROM student_details_class10 UNION SELECT id, first_name FROM sports_team;
7) Be careful while using conditions in WHERE clause. For Example: Write the query as SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of: SELECT id, first_name, age FROM student_details WHERE age != 10;
Write the query as
SELECT id, first_name, age FROM student_details WHERE first_name LIKE 'Chan%';
Instead of: SELECT id, first_name, age FROM student_details WHERE SUBSTR(first_name,1,3) = 'Cha';
Write the query as SELECT id, first_name, age FROM student_details WHERE first_name LIKE NVL ( :name, '%');
Instead of: SELECT id, first_name, age FROM student_details WHERE first_name = NVL ( :name, first_name);
Write the query as SELECT product_id, product_name FROM product WHERE unit_price BETWEEN MAX(unit_price) and MIN(unit_price)
Instead of: SELECT product_id, product_name FROM product WHERE unit_price >= MAX(unit_price) and unit_price <= MIN(unit_price)
Write the query as SELECT id, name, salary FROM employee WHERE dept = 'Electronics' AND location = 'Bangalore';
Instead of: SELECT id, name, salary FROM employee WHERE dept || location= 'ElectronicsBangalore';
Use non-column expression on one side of the query because it will be processed earlier. Write the query as
SELECT id, name, salary FROM employee WHERE salary < 25000;
Instead of: SELECT id, name, salary FROM employee WHERE salary + 10000 < 35000;
Write the query as SELECT id, first_name, age FROM student_details WHERE age > 10;
Instead of: SELECT id, first_name, age FROM student_details WHERE age NOT = 10;
8) Use DECODE to avoid the scanning of same rows or joining the same table repetitively. DECODE can also be made used in place of GROUP BY or ORDER BY clause. For Example: Write the query as SELECT id FROM employee WHERE name LIKE 'Ramesh%' and location = 'Bangalore';
Instead of: SELECT DECODE(location,'Bangalore',id,NULL) id FROM employee WHERE name LIKE 'Ramesh%';
9) To store large binary objects, first place them in the file system and add the file path in the database. 10) To write queries which provide efficient performance follow the general SQL standard rules. a) Use single case for all SQL verbs b) Begin all SQL verbs on a new line c) Separate all words with a single space d) Right or left aligning verbs within the initial SQL verb
JOIN JOIN works in the same way as the SELECT statement above—it returns a result set with
columns from different tables. The advantage of using an explicit JOIN over an implied one is greater control over your result set, and possibly improved performance when many tables are involved. There are several types of JOIN—LEFT, RIGHT, and FULL OUTER; INNER; and CROSS. The type you use is determined by the results you want to see. For example, using a LEFT OUTER JOIN will return all relevant rows from the first table listed, while potentially dropping rows from the second table listed if they d on’t have information that correlates in the first table. This differs from an INNER JOIN or an implied JOIN. An INNER JOIN will only return rows for which there is data in both tables. Use the following JOIN statement for the first SELECT query above: SELECT table1.column1, table2.column2 FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1; Subqueries Subqueries, or subselect statements, are a way to use a result set as a resource in a query. These are often used to limit or refine results rather than run multiple queries or manipulate the data in your application. With a subquery, you can reference tables to determine inclusion of data or, in some cases, return a column that is the result of a subselect. The following example uses two tables. One table actually contains the data I’m interested in returning, while the other gives a comparison point to determine what data is actually interesting. SELECT column1 FROM table1 WHERE EXISTS ( SELECT column1 FROM table2 WHERE table1.column1 = table2.column1 ); One important factor about subqueries is performance. Convenience comes at a price and, depending on the size, number, and complexity of tables and the statements you use, you may want to allow your application to handle processing. Each query is processed separately in full before being used as a resource for your primary query. If possible, creative use of JOIN statements may provide the same information with less lag time. JOIN statements and subqueries For a more detailed explanation of JOINS and concepts that can be used with them, read the articles "Basic and complex SQL joins made easy" and "Master joins with these concepts." For more information about subqueries, read "Use SQL subselects to consolidate queries." UNION The UNION statement is another way to return information from multiple tables w ith a single query. The UNION statement allows you to perform queries a gainst several tables and return the results in a consolidated set, as in the following example.
SELECT column1, column2, column3 FROM table1 UNION SELECT column1, column2, column3 FROM table2; This will return a result set with three columns containing data from both queries. By default, the UNION statement will omit duplicates between the tables unless the UNION ALL keyword is used. UNION is helpful when the returned columns from the different tables don’t have columns or data that can be compared and joined, or when it prevents running multiple queries and appending the results in your application code. If your column names don’t match when you use the UNION statement, use aliases to give your results meaningful headers: SELECT column1, column2 as Two, column3 as Three FROM table1 UNION SELECT column1, column4 as Two, column5 as Three FROM table2; As with subqueries, UNION statements can create a heavy load on your database server, but for occasional use they can save a lot of time. Multiple options When it comes to database queries, there are usually many ways to approach the same problem. These are some of the more frequently used methods for consolidating queries on multiple tables into a single statement. While some of these options may affect performance, practice will help you know when it’s appropriate to use each type of query. Data query types There are four basic types of data queries in SQL: •
• • •
SELECT: This statement asks the database to return a specified result set of data; you use this to retrieve information that is stored in your database. INSERT: You use this type of statement to add a new row of data into a table. UPDATE: This changes existing data in your database. DELETE: This removes rows of data from your database.
Each of these statements has various qualifiers and functions that you can use to define the data set you would like to affect and that control the way the result set is returned. The SELECT statement has the most options. There are a variety of alternate query types used in conjunction with SELECT, such as JOIN and UNION. For now, we’ll just focus on basic usage. Use the SELECT statement to retrieve stored information To get information stored in the database, you use SELECT. The basic function is limited to a single table, although there are constructs for other scopes. To return all the rows of data for particular columns, you use this statement: SELECT column1, column2 FROM table_name; Alternatively, you can get all the columns from a table by using the wildcard, “*”: SELECT * FROM table_name;
While this is useful if you’re planning on parsing the result set in your code, you can further limit it with the handy WHERE clause, which allows you to define a certain condition for selecting data. The following query will return all columns from all rows where “column1” contains the value "3." SELECT * FROM table_name WHERE column1 = 3; Besides the “=” (equal to) condition, you may use any of the following: Table A = Equal <> Not Equal > Greater Than < Less Than >= Greater Than Or Equal To <= Less Than Or Equal To SQL Conditional Statements Additionally, you can use the statements BETWEEN and LIKE for comparison with the WHERE clause, and any combination of comparisons with the operators AND and OR. Note that the OR statement is inclusive. H ere’s an example combining all of these concepts: SELECT * FROM table_name WHERE ((Age < 18) AND (LastName BETWEEN ‘Anderson’ AND ‘Miller’)) OR Company LIKE ‘%School%’; In English, this statement reads “Select all columns from table_name where either the Age column has a value less than 18 and the LastName column has a value alphabetically between and including ‘Anderson’ and ‘Miller’ or the Company column value contains the word ‘School’.” Use the INSERT statement for new rows of data Use the INSERT statement to create a new row of data. While you don’t have to populate every field in the row, if you want to put a value into a row where a field is empty, you must use UPDATE. INSERT syntax is as follows: INSERT INTO table_name (column1, column2, column3) VALUES (‘data1’, ‘data2’, ‘data3’); If you’re going to insert all the values in the same order that the columns of the table exist, you don’t have to specify column names, although for readability this is generally preferred. In addition, if you list the columns, it is no t necessary to include them in the order they appear in the database, as long as the values you list correlate. You do not have to list all of the columns if you aren’t entering information for them. Once you have data in your database, changing it is very similar.
The UPDATE statement and the WHERE clause UPDATE is used to change an existing value or empty field in a row, so it must both match an existing data set and provide acceptable values. Unless you truly want to change the values in all rows, you must use the WHERE clause. UPDATE table_name SET column1 = ‘data1’, column2 = ‘data2’ WHERE column3 = ‘data3’; You are free to match WHERE to any column, including one that you are changing. This is helpful when you’re looking to change one specific value to another: UPDATE table_name SET FirstName = ‘Shelley’ WHERE FirstName = ‘Shelly’ AND LastName = ‘Doll’; Be careful! The DELETE statement zaps whole rows The DELETE statement will erase an entire row from a table in your database. If you want to delete just a single field, you should use the UPDATE statement to change it to some value that represents NULL to your application. Be careful to limit the scope of your DELETE statement with a WHERE clause, or you run the risk of emptying your entire table. DELETE FROM table_name WHERE column1 = ‘data1’; Once a row has been removed from your database, it is gone, so generally speaking it may be desirable to include a column called “IsActive,” or something similar, that you can change to a zero to represent disabled data. You should only use DELETE when you are absolutely sure you will no longer need the affected information. Now you know the basics of SQL database commands SQL is the language of databases, and we’ve reviewed the most basic command statements used in data queries. There are a number of basic concepts that weren’t covered, such as SUM and COUNT, but the few commands listed above should get you up and running. Please post any questions you may have in the discussion area below, and be sure to let us know if you would like to see more tutorial information on SQL.
Selecting Data The select statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement: select "column1" [,"column2",etc] from "tablename" [where "condition"]; [] = optional
The column names that follow the select keyword determine which columns will be returned in the results. You can select as many column names that you'd like, or you can use a "*" to select all columns. The table name that follows the keyword from specifies the table that will be queried to retrieve the desired results. The where clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria described after the keyword where. Conditional selections used in the where clause: =
Equal
>
Greater than
<
Less than
>= Greater than or equal <= Less than or equal <> Not equal to LIKE *See note below The LIKE pattern matching operator can also be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example: select first, last, city from empinfo where first LIKE 'Er%';
This SQL statement will match any first names that start with 'Er'. Strings must be in single quotes. Or you can specify, select first, last from empinfo where last LIKE '%s';
This statement will match any last names that end in a 's'. select * from empinfo where first = 'Eric';
This will only select rows where the first name equals 'Eric' exactly.
Sample Table: empinfo first
last
id
age
city
state
John
Jones
99980 45
Payson
Arizona
Mary
Jones
99982 25
Payson
Arizona
Eric
Edwards 88232 32
San Diego
California
Mary Ann Edwards 88233 32
Phoenix
Arizona
Ginger
Howell
98002 42
Cottonwood Arizona
Sebastian
Smith
92001 23
Gila Bend
Arizona
Gus
Gray
22322 35
Bagdad
Arizona
Mary Ann May
32326 52
Tucson
Arizona
Erica
Williams 32327 60
Show Low
Arizona
Leroy
Brown
32380 22
Pinetop
Arizona
Elroy
Cleaver
32382 22
Globe
Arizona
Enter the following sample select statements in the SQL Interpreter Form at the bottom of this page. Before you press "submit", write down your expected results. Press "submit", and compare the results. select first, last, city from empinfo; select last, city, age from empinfo where age > 30; select first, last, city, state from empinfo where first LIKE 'J%'; select * from empinfo; select first, last, from empinfo where last LIKE '%s'; select first, last, age from empinfo where last LIKE '%illia%'; select * from empinfo where first = 'Eric';
Select statement exercises Enter select statements to: 1. Display the first name and age for everyone that's in the table.
2. 3. 4. 5. 6.
Display the first name, last name, and city for everyone that's not from Payson. Display all columns for everyone that is over 40 years old. Display the first and last names for everyone whose last name ends in an "ay". Display all columns for everyone whose first name equals "Mary". Display all columns for everyone whose first name contains "Mary".
Creating Tables The create table statement is used to create a new table. Here is the format of a simple create table statement: create table "tablename" ("column1" "data type", "column2" "data type", "column3" "data type");
Format of create table if you were to use optional constraints: create table "tablename" ("column1" "data type" [constraint], "column2" "data type" [constraint], "column3" "data type" [constraint]); [ ] = optional
Note: You may have as many columns as you'd like, and the constraints are optional. Example: create table employee (first varchar(15), last varchar(20), age number(3), address varchar(30), city varchar(20), state varchar(20));
Inserting into a Table The insert statement is used to insert or add a row of data into the table. To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values , followed by the list of values enclosed in parenthesis. The values that you enter will be held in the rows and they will match up with the column names that you specify. Strings should be enclosed in single quotes, and numbers should not.
insert into "tablename" (first_column,...last_column) values (first_value,...last_value);
In the example below, the column name first will match up with the value 'Luke', and the column name state will match up with the value 'Georgia' . Example: insert into employee (first, last, age, address, city, state) values ('Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co', 'Georgia');
Note: All strings should be enclosed between single quotes: 'string'
Insert statement exercises It is time to insert data into your new employee table. Your first three employees are the following: Jonie Weber, Secretary, 28, 19500.00 Potsy Weber, Programmer, 32, 45300.00 Dirk Smith, Programmer II, 45, 75020.00 Enter these employees into your table first, and then insert at least 5 more of your own list of employees in the table. After they're inserted into the table, enter select statements to: 1. 2. 3. 4. 5. 6. 7. 8.
Select all columns for everyone in your employee table. Select all columns for everyone with a salary over 30000. Select first and last names for everyone that's under 30 years old. Select first name, last name, and salary for anyone with "Programmer" in their title. Select all columns for everyone whose last name contains "ebe". Select the first name for everyone whose first name equals "Potsy". Select all columns for everyone over 80 years old. Select all columns for everyone whose last name ends in "ith".
Create at least 5 of your own select statements based on specific information that you'd like to retrieve.
Updating Records
The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause. update "tablename" set "columnname" = "newvalue" [,"nextcolumn" = "newvalue2"...] where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; [] = optional
[The above example was line wrapped for better viewing on this Web page.] Examples: update phone_book set area_code = 623 where prefix = 979; update phone_book set last_name = 'Smith', prefix=555, suffix=9292 where last_name = 'Jones'; update employee set age = age+1 where first_name='Mary' and last_name='Williams';
Update statement exercises After each update, issue a select statement to verify your changes. 1. Jonie Weber just got married to Bob Williams. She has requested that her last name be updated to Weber-Williams. 2. Dirk Smith's birthday is today, add 1 to his age. 3. All secretaries are now called "Administrative Assistant". Update all titles accordingly. 4. Everyone that's making under 30000 are to receive a 3500 a year raise. 5. Everyone that's making over 33500 are to receive a 4500 a year raise. 6. All "Programmer II" titles are now promoted to "Programmer III". 7. All "Programmer" titles are now promoted to "Programmer II".
Deleting Records The delete statement is used to delete records or rows from the table.
delete from "tablename" where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; [ ] = optional
[The above example was line wrapped for better viewing on this Web page.] Examples: delete from employee;
Note: if you leave off the where clause, all records will be deleted! delete from employee where lastname = 'May'; delete from employee where firstname = 'Mike' or firstname = 'Eric';
To delete an entire record/row from a table, enter "delete from " followed by the table name, followed by the where clause which contains the conditions to delete. If you leave off the where clause, all records will be deleted.
Delete statement exercises (Use the select statement to verify your deletes): 1. Jonie Weber-Williams just quit, remove her record from the table. 2. It's time for budget cuts. Remove all employees who are making over 70000 dollars. Create at least two of your own delete statements, and then issue a command to delete all records from the table.
Drop a Table The drop table command is used to delete a table and all rows in the table. To delete an entire table including all of its rows, issue the drop table command followed by the tablename. drop table is different from deleting all of the records in the table. Deleting all of the records in the table leaves the table including column and constraint information. Dropping the table removes the table definition as well as all of its rows.
drop table "tablename"
Example: drop table myemployees_ts0211;
Drop Table exercises 1. Drop your employee table.
What is sql SQL is a standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, and other database systems. What is ado ADO is a programming interface to access data in a database from a web site. ADO uses SQL to query data in a database. Can you be precise about sql and What can u do with sql · SQL stands for Structured Query Language · SQL allows you to access a database · SQL is an ANSI standard computer language · SQL can execute queries against a database · SQL can retrieve data from a database · SQL can insert new records in a database · SQL can delete records from a database · SQL can update records in a database Do u know any commands in sql if so tell about them · SELECT - extracts data from a database table · UPDATE - updates data in a database table · DELETE - deletes data from a database table · INSERT INTO - inserts new data into a database table Tell some thing about ddl The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables. Tell some basic ddl statements · CREATE TABLE - creates a new database table · ALTER TABLE - alters (changes) a database table · DROP TABLE - deletes a database table · CREATE INDEX - creates an index (search key) · DROP INDEX - deletes an index What do u know about result set
The result from a SQL query is stored in a result-set Where we use distinct statement The DISTINCT keyword is used to return only distinct (different) values. Tell some thing about aliases With SQL, aliases can be used for column names and table names Tell syntaxes for column name aliases and table name aliases SELECT column AS column_alias FROM table SELECT column FROM table AS table_alias
What do u know about primary key A primary key is a column with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table. Where exactly u use joins? we can select data from two tables with the JOIN keyword what does inner join do? The INNER JOIN returns all rows from both tables where there is a match. Do u know any thing about union? The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. What is union all command The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. Tell some thing about drop index You can delete an existing index in a table with the DROP INDEX statement. What does truncate do? deletes only the data inside the table what does alter table do? The ALTER TABLE statement is used to add or drop columns in an existing table. How do we use fuctions in sql SELECT function(column) FROM table
Write syntax for group by SELECT column,SUM(column) FROM table GROUP BY column What do u know about view In SQL, a VIEW is a virtual table based on the result-set of a SELECT statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from a single table. Tell me what u know about dbms A Database Management System (DBMS) is a computer program that can access data in a database. The DBMS program enables you to extract, modify, or store information in a database. Different DBMS programs provides different functions for querying data, reporting data, and modifying data. Tell me what u know about Rdbms A Relational Database Management System (RDBMS) is a Database Management System (DBMS) where the database is organized and accessed according to the relationships between data. RDBMS is the basis for SQL, and for all modern database systems like Oracle, SQL Server, IBM DB2, Sybase, MySQL, and Microsoft Access. Re: Explain basic SQL queries with SELECT from where Order By, Group ByHaving? Answer #1 Order by clause is used to sort information of a table into specific order either ascending or descending order. syntax:select stmt order by column; ex:select * from emp order by sal; Group by clause is used to divide entire group into subgroups based on the common values columns.we can use group by clause along with group functions. syntax:select group by(exp 1),group by(exp 2),....from table name group by column name; select max(sal),min(sal) from emp group by deptno; Group by clause can allow where clause.
Having:is used to restrict information of the atble based on condition. syntax:select group by (exp 1) ,group by (exp 2) from table name group by column having (condition). Having and Group by clauses can interchange their positions. but when using having along with order by clause having
should be placed before order by clause.
Is This Answer Correct ?
13 Yes
3 No
0 Swapna Re: Explain basic SQL queries with SELECT from where Order By, Group ByHaving? Answer #2 sory i m satisfied with idea.actualy i want to know is this query is right or not.plz inform me. SELECT country,city FROM customers GROUP BY country ORDER BY city DESC
What is a subselect? Is it different from a nested select?
A subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the
inner select passes to the where criteria for the outer select.
What is the difference between group by and order by?
Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the
SELECT statement.
What keyword does an SQL SELECT statement use for a string search?
The LIKE keyword allows for string searches. The % sign is used as a wildcard.
What are some SQL aggregates and other built-in functions?
The common aggregate, built-in functions are AVG, SUM, MIN, MAX, COUNT and DISTINCT.
How is the SUBSTR keyword used in SQL?
SUBSTR is used for string manipulation with column name, first position and string length used as arguments. E.g.
SUBSTR (NAME, 1 3) refers to the first three characters in the column NAME.
Explain the EXPLAIN statement?
The explain statement provides information about the optimizer's choice of access path of the SQL.
What is referential integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every
foreign key value must have a corresponding primary key value.
What is a NULL value? What are the pros and cons of using NULLS?
A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero
value. It's the DB2 equivalent of TBD on an organizational chart and often correctly portrays a business situation.
Unfortunately, it requires extra coding for an application program to handle this situation.
What is a synonym? How is it used?
A synonym is used to reference a table or view by another name. The other name can then be written in the
application code pointing to test tables in the development stage and to production entities when the code is migrated.
The synonym is linked to the AUTHID that created it.
What is an alias and how does it differ from a synonym?
An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location
qualifier of a table or view. The alias is not dropped when the table is dropped.
When can an insert of a new primary key value threaten referential integrity?
Never. New primary key values are not a problem. However, the values of foreign key inserts must have
corresponding primary key values in their related tables. And updates of primary key values may require changes in
foreign key values to maintain referential integrity.
What is the difference between static and dynamic SQL?
Static SQL is hard-coded in a program when the programmer knows the statements to be executed. For dynamic SQL
the program must dynamically allocate memory to receive the query results.
Compare a subselect to a join?
Any subselect can be rewritten as a join, but not vice versa. Joins are usually more efficient as join rows can be
returned immediately, subselects require a temporary work area for inner selects results while processing the outer
select.
What is the difference between IN subselects and EXISTS subselect?
If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN. (IN for index is
the mnemonic).
What is a Cartesian product?
A Cartesian product results from a faulty query. It is a row in the results for every combination in the join tables.
What is a tuple?
A tuple is an instance of data within a relational database.
What is the difference between static and dynamic SQL?
Static SQL is compiled and optimized prior to its execution; dynamic is compiled and optimized during execution.
Any SQL implementation covers data types in couple of main categories. Which of the following are those data
types ? (Check all that apply)
A). NUMERIC
B). CHARACTER
C). DATE AND TIME
D). BLOBS
E. BIT
A,B,C. Not all SQL implementations have a BLOB or a BIT data types.
We have a table with a CHARACTER data type field. We apply a ">" row comparison between this field and
another CHARACTER field in another table. What will be the results for records with field value of NULL?
(Check one that applies the best)
TRUE
B. FALSE
C. UNKNOWN
D. Error.
E. Those records will be ignored
C. NULL in a row when compared will give an UNKNOWN result.
Any database needs to go through a normalization process to make sure that data is represented only once. This
will eliminate problems with creating or destroying data in the database. The normalization process is done
usually in three steps which results in first, second and third normal forms. Which best describes the process to
obtain the third normal form? (Check one that applies the best)
Each table should have related columns.
Each separate table should have a primary key.
We have a table with multi-valued key. All columns that are dependent on only one or on some of the keys should be moved in a different table.
If a table has columns not dependent on the primary keys, they need to be moved in a separate table.
E. Primary key is always UNIQUE and NOT NULL.
D. All columns in a table should be dependent on the primary key. This will eliminate transitive dependencies in
which A depends on B, and B depends on C, but we're not sure how C depends on A.
SQL can be embedded in a host program that uses a relational database as a persistent data repository. Some of
the most important pre-defined structures for this mechanism are SQLDA ("SQL Descriptor Area") and
SQLCA ("SQL Communications Area") SQLCA contains two structures SQLCODE and SQLSTATE.
SQLSTATE is a standard set of error messages and warnings in which the first two characters defines the class
and the last three defines the subclass of the error. Which of the following SQLSTATE codes is interpreted as
"No data returned"?(Check one that applies the best)
A). 00xxx
B). 01xxx
C). 02xxx
D). 22xxx
E). 2Axxx
C. 00 - is successful completion, 01 - warnings, 22 - is data exception and 2A is syntax error. The SQLSTATE code
format returned for "No data returned" is "02xxx".
What are common SQL abend codes? (e.g. : 0,100 etc.,)
-818 time stamp mismatch
-180 wrong data moved into date field
What is meant by dynamic SQL?
Dynamic SQL are SQL statements that are prepared and executed within a program while the program is executing.
The SQL source is contained in host variables rather than being hard coded into the program. The SQL statement may
change from execution to execution.
