Unix Commands

Commonly used UNIX Commands!!

1.How to display the 10th line of a file?

head -10 filename | tail -1

2. How to remove the header from a file?

sed -i '1 d' filename

3. How to remove the footer from a file?

sed -i '$ d' filename

4. Write a command to find the length of a line in a file?

The below command can be used to get a line from a file.

sed –n '<n> p' filename

We will see how to find the length of 10th line in a file

sed -n '10 p' filename|wc -c

5. How to get the nth word of a line in Unix?

cut –f<n> -d' '

6. How to reverse a string in unix?

echo "java" | rev

7. How to get the last word from a line in Unix file?

echo "unix is good" | rev | cut -f1 -d' ' | rev

8. How to replace the n-th line in a file with a new line in Unix?

sed -i'' '10 d' filename       # d stands for delete

sed -i'' '10 i new inserted line' filename     # i stands for insert

9. How to check if the last command was successful in Unix?

echo $?

10. Write command to list all the links from a directory?

ls -lrt | grep "^l"

11. How will you find which operating system your system is running on in UNIX?

uname -a

12. Create a read-only file in your home directory?

touch file; chmod 400 file

13. How do you see command line history in UNIX?

The 'history' command can be used to get the list of commands that we are executed.

14. How to display the first 20 lines of a file?

By default, the head command displays the first 10 lines from a file. If we change the option of head, then we can display as many lines as we want.

head -20 filename

An alternative solution is using the sed command

sed '21,$ d' filename

The d option here deletes the lines from 21 to the end of the file

15. Write a command to print the last line of a file?

The tail command can be used to display the last lines from a file.

tail -1 filename

Alternative solutions are:

sed -n '$ p' filename

awk 'END{print $0}' filename

16. How do you rename the files in a directory with _new as suffix?

ls -lrt|grep '^-'| awk '{print "mv "$9" "$9".new"}' | sh

17. Write a command to convert a string from lower case to upper case?

echo "apple" | tr [a-z] [A-Z]

18. Write a command to convert a string to Initcap.

echo apple | awk '{print toupper(substr($1,1,1)) tolower(substr($1,2))}'

19. Write a command to redirect the output of date command to multiple files?

The tee command writes the output to multiple files and also displays the output on the terminal.

date | tee -a file1 file2 file3

20. How do you list the hidden files in current directory?

ls -a | grep '^\.'

21. List out some of the Hot Keys available in bash shell?

Ctrl+l - Clears the Screen.

Ctrl+r - Does a search in previously given commands in shell.

Ctrl+u - Clears the typing before the hotkey.

Ctrl+a - Places cursor at the beginning of the command at shell.

Ctrl+e - Places cursor at the end of the command at shell.

Ctrl+d - Kills the shell.

Ctrl+z - Places the currently running process into background.

22. How do you make an existing file empty?

cat /dev/null >  filename

23. How do you remove the first number on 10th line in file?

sed '10 s/[0-9][0-9]*//' < filename

24. What is the difference between join -v and join -a?

join -v : outputs only matched lines between two files.

join -a : In addition to the matched lines, this will output unmatched lines also.

25. How do you display from the 5th character to the end of the line from a file?

cut -c 5- filename

26. Display all the files in current directory sorted by size?

ls -l | grep '^-' | awk '{print $5,$9}' |sort -n|awk '{print $2}'

27. Write a command to search for the file 'map' in the current directory?

find -name map -type f

28. How to display the first 10 characters from each line of a file?

cut -c -10 filename

29. Write a command to remove the first number on all lines that start with "@"?

sed '\,^@, s/[0-9][0-9]*//' < filename

30. How to print the file names in a directory that has the word "term"?

grep -l term *

The '-l' option make the grep command to print only the filename without printing the content of the file. As soon as the grep command finds the pattern in a file, it prints the pattern and stops searching other lines in the file.

31. How to run awk command specified in a file?

awk -f filename

32. How do you display the calendar for the month march in the year 1985?

The cal command can be used to display the current month calendar. You can pass the month and year as arguments to display the required year, month combination calendar.

cal 03 1985

This will display the calendar for the March month and year 1985.

33. Write a command to find the total number of lines in a file?

wc -l filename

Other ways to pring the total number of lines are

awk 'BEGIN {sum=0} {sum=sum+1} END {print sum}' filename

awk 'END{print NR}' filename

34. How to duplicate empty lines in a file?

sed '/^$/ p' < filename

35. Explain iostat, vmstat and netstat?

Iostat: reports on terminal, disk and tape I/O activity.

Vmstat: reports on virtual memory statistics for processes, disk, tape and CPU activity.

Netstat: reports on the contents of network data structures.

36. How do you write the contents of 3 files into a single file?

cat file1 file2 file3 > file

37. How to display the fields in a text file in reverse order?

awk 'BEGIN {ORS=""} { for(i=NF;i>0;i--) print $i," "; print "\n"}' filename

38. Write a command to find the sum of bytes (size of file) of all files in a directory.

ls -l | grep '^-'| awk 'BEGIN {sum=0} {sum = sum + $5} END {print sum}'

39. Write a command to print the lines which end with the word "end"?

grep 'end$' filename

The '$' symbol specifies the grep command to search for the pattern at the end of the line.

40. Write a command to select only those lines containing "july" as a whole word?

grep -w july filename

The '-w' option makes the grep command to search for exact whole words. If the specified pattern is found in a string, then it is not considered as a whole word. For example: In the string "mikejulymak", the pattern "july" is found. However "july" is not a whole word in that string.

41. How to remove the first 10 lines from a file?

sed '1,10 d' < filename

42. Write a command to duplicate each line in a file?

sed 'p' < filename

43. How to extract the username from 'who am i' comamnd?

who am i | cut -f1 -d' '

44. Write a command to list the files in '/usr' directory that start with 'ch' and then display the number of lines in each file?

wc -l /usr/ch*

Another way is

find /usr -name 'ch*' -type f -exec wc -l {} \;

45. How to remove blank lines in a file ?

grep -v ‘^$’ filename > new_filename

46. How to display the processes that were run by your user name ?

ps -aef | grep <user_name>

47. Write a command to display all the files recursively with path under current directory?

find . -depth -print

48. Display zero byte size files in the current directory?

find -size 0 -type f

49. Write a command to display the third and fifth character from each line of a file?

cut -c 3,5 filename

50. Write a command to print the fields from 10th to the end of the line. The fields in the line are delimited by a comma?

cut -d',' -f10- filename

51. How to replace the word "Gun" with "Pen" in the first 100 lines of a file?

sed '1,00 s/Gun/Pen/' < filename

52. Write a Unix command to display the lines in a file that do not contain the word "RAM"?

grep -v RAM filename

The '-v' option tells the grep to print the lines that do not contain the specified pattern.

53. How to print the squares of numbers from 1 to 10 using awk command

awk 'BEGIN { for(i=1;i<=10;i++) {print "square of",i,"is",i*i;}}'

54. Write a command to display the files in the directory by file size?

ls -l | grep '^-' |sort -nr -k 5

55. How to find out the usage of the CPU by the processes?

The top utility can be used to display the CPU usage by the processes.

56. Write a command to remove the prefix of the string ending with '/'.

The basename utility deletes any prefix ending in /. The usage is mentioned below:

basename /usr/local/bin/file

This will display only file

57. How to display zero byte size files?

ls -l | grep '^-' | awk '/^-/ {if ($5 !=0 ) print $9 }'

58. How to replace the second occurrence of the word "bat" with "ball" in a file?

sed 's/bat/ball/2' < filename

59. How to remove all the occurrences of the word "jhon" except the first one in a line with in the entire file?

sed 's/jhon//2g' < filename

60. How to replace the word "lite" with "light" from 100th line to last line in a file?

sed '100,$ s/lite/light/' < filename

61. How to list the files that are accessed 5 days ago in the current directory?

find -atime 5 -type f

62. How to list the files that were modified 5 days ago in the current directory?

find -mtime 5 -type f

63. How to list the files whose status is changed 5 days ago in the current directory?

find -ctime 5 -type f

64. How to replace the character '/' with ',' in a file?

sed 's/\//,/' < filename

sed 's|/|,|' < filename

65. Write a command to find the number of files in a directory.

ls -l|grep '^-'|wc -l

66. Write a command to display your name 100 times.

The Yes utility can be used to repeatedly output a line with the specified string or 'y'.

yes <your_name> | head -100

67. Write a command to display the first 10 characters from each line of a file?

cut -c -10 filename

68. The fields in each line are delimited by comma. Write a command to display third field from each line of a file?

cut -d',' -f2 filename

69. Write a command to print the fields from 10 to 20 from each line of a file?

cut -d',' -f10-20 filename

70. Write a command to print the first 5 fields from each line?

cut -d',' -f-5 filename

71. By default the cut command displays the entire line if there is no delimiter in it. Which cut option is used to supress these kind of lines?

The -s option is used to supress the lines that do not contain the delimiter.

72. Write a command to replace the word "bad" with "good" in file?

sed s/bad/good/ < filename

73. Write a command to replace the word "bad" with "good" globally in a file?

sed s/bad/good/g < filename

74. Write a command to replace the word "apple" with "(apple)" in a file?

sed s/apple/(&)/ < filename

75. Write a command to switch the two consecutive words "apple" and "mango" in a file?

sed 's/\(apple\) \(mango\)/\2 \1/' < filename

76. Write a command to display the characters from 10 to 20 from each line of a file?

cut -c 10-20 filename

77. Write a command to print the lines that has the the pattern "july" in all the files in a particular directory?

grep july *

This will print all the lines in all files that contain the word “july” along with the file name. If any of the files contain words like "JULY" or "July", the above command would not print those lines.

78. Write a command to print the lines that has the word "july" in all the files in a directory and also suppress the filename in the output.

grep -h july *

79. Write a command to print the lines that has the word "july" while ignoring the case.

grep -i july *

The option i make the grep command to treat the pattern as case insensitive.

80. When you use a single file as input to the grep command to search for a pattern, it won't print the filename in the output. Now write a grep command to print the filename in the output without using the '-H' option.

grep pattern filename /dev/null

The /dev/null or null device is special file that discards the data written to it. So, the /dev/null is always an empty file.

Another way to print the filename is using the '-H' option. The grep command for this is

grep -H pattern filename

81. Write a command to print the file names in a directory that does not contain the word "july"?

grep -L july *

The '-L' option makes the grep command to print the filenames that do not contain the specified pattern.

82. Write a command to print the line numbers along with the line that has the word "july"?

grep -n july filename

The '-n' option is used to print the line numbers in a file. The line numbers start from 1

83. Write a command to print the lines that starts with the word "start"?

grep '^start' filename

The '^' symbol specifies the grep command to search for the pattern at the start of the line.

84. In the text file, some lines are delimited by colon and some are delimited by space. Write a command to print the third field of each line.

awk '{ if( $0 ~ /:/ ) { FS=":"; } else { FS =" "; } print $3 }' filename

85. Write a command to print the line number before each line?

awk '{print NR, $0}' filename

86. Write a command to print the second and third line of a file without using NR.

awk 'BEGIN {RS="";FS="\n"} {print $2,$3}' filename

87. How to create an alias for the complex command and remove the alias?

The alias utility is used to create the alias for a command. The below command creates alias for ps -aef command.

alias pg='ps -aef'

If you use pg, it will work the same way as ps -aef.

To remove the alias simply use the unalias command as

unalias pg

88. Write a command to display todays date in the format of 'yyyy-mm-dd'?

The date command can be used to display todays date with time

date '+%Y-%m-%d'

89.For LOOP

1. Rename all ".old" files in the current directory to ".bak":

for i in *.old   do  j=`echo $i|sed 's/old/bak/'`  mv $i $j  done

2. Change all instances of "yes" to "no" in all ".txt" files in the current directory. Back up the original files to ".bak".

for i in *.txt do  j=`echo $i|sed 's/txt/bak/'`  mv $i $j   sed 's/yes/no/' $j > $i  done

3. Loop thru a text file containing possible file names. If the file is readable, print the first line, otherwise print an error message:

for i in `cat file_list.txt` do  if test -r $i   

  then        

   echo "Here is the first line of file: $i"       

   sed 1q $i   

else

echo "file $i cannot be open for reading."      fi  done

How to print/display the first line of a file?

$> head -1 file.txt

$> sed '2,$ d' file.txt

How to print/display the last line of a file?

$> tail -1 file.txt

$> sed -n '$ p' test

How to display n-th line of a file?

$> sed –n '<n> p' file.txt

$> sed –n '4 p' test

$> head -<n> file.txt | tail -1

$> head -4 file.txt | tail -1

How to remove the first line / header from a file?

$> sed '1 d' file.txt

$> sed '1 d' file.txt > new_file.txt

$> mv new_file.txt file.txt

$> sed –i '1 d' file.txt

How to remove the last line/ trailer from a file in Unix script?

$> sed –i '$ d' file.txt

How to remove certain lines from a file in Unix?

$> sed –i '5,7 d' file.txt

How to remove the last n-th line from a file?

$> sed –i '96,100 d' file.txt   # alternative to command [head -95 file.txt]

$> tt=`wc -l file.txt | cut -f1 -d' '`;sed –i "`expr $tt - 4`,$tt d" test

How to check the length of any line in a file?

$> sed –n '<n> p' file.txt

$> sed –n '35 p' file.txt | wc –c

How to check if a file is present in a particular directory in Unix?

$> ls –l file.txt; echo $?

How to check all the running processes in Unix?

$> ps –ef

$> ps aux

$>ps -e -o stime,user,pid,args,%mem,%cpu

Combine multiple Rows to a Column – Oracle

SELECT SUBSTR (SYS_CONNECT_BY_PATH (NAME , ','), 2) FRUITS_LIST

FROM (SELECT NAME , ROW_NUMBER () OVER (ORDER BY NAME ) RN,

COUNT (*) OVER () CNT

FROM FRUITS)

WHERE RN = CNT

START WITH RN = 1

CONNECT BY RN = PRIOR RN + 1;

What is command to check space in Unix

df -k

What is command to kill last background Job

kill $!

What is difference between diff and cmp command

cmp -It compares two files byte by byte and displays first mismatch.

diff -It displays all changes required to make files identical.

What does $# stands for

It will return the number of parameters passed as command line argument.

Basic of SQL

DBMS :( Database Management System): Data Base Management System is the management system or maintenance system on databases. 

(Or)

It is a suite of software program for creating, maintaining & manipulating the data in database.   

(Or)

It allows the users to insert the data, to retrieve the data, to modify the data and to delete the data.

Properties:

  1. Create

  2. Insert

  3. Update

  4. Delete

  5. Select (query)

  6. Low level security etc...

 Ex: FoxPro, ims (information management system)

The first databases of kind existed in the form of files.

A file is nothing but collection of records.

A Record is nothing but collection of information or data.

But the file system was not very efficient .it was crippled by slow data search speed. It includes the following Drawbacks.

1. Security

2. Data Redundancy

3. Data Integrity

4. Concurrency Control

5. Slow in Process

6. File system: in a dbms database, data must be stored in the form of files only

Note: basic property of file system is independent and individual, means each and every file is unique AND relation is not possible between data files

7. Low level security, No Login name No Password.

RDBMS (Relational Database Management System):

·         Data must be stored in the form of tables only

·         Relation between Tables is possible

Table: It is collection of columns and rows, X axis directions are rows and Y axis directions are columns

Relation: Creating internal dependencies between Database Tables

Ex of RDBMS:

1. Sqlserver

2. Oracle

3. Db2 (Database 2000, IBM)

4. Mysql (open source)

5. ms-access

6. Postgresql (open source)

7. Sybase

8. Tera data

Etc.....

Terminology:

1. Table                --       Entity

2. Column (field)   --       Attribute

3. Row (record)     --       Tupple

4. Duplicate data (Repeated data) – Data Redundancy

Metadata: Data about data, it describes characteristics of data

Ex: Description of a table

Syntax: sp_help <TN>

Ex:  sp_help EMP

Note: <TN> stands for Table name

Data models:

1. Hierarchy data model

2. Network data model

3. Relational data model

Note: Currently all RDBMS Databases following RELATIONAL DATA MODEL

Adv. of Relational Data model:

• Relation
• Perfect solution for duplicate data and null values
• Normalization

Normalization: It is a process of eliminating duplicate data from the database.

 SQL -- structured query language

Sequel-- structured English query language

FEATURES OF SQL SERVER:

1.    Security: Provides login and password to interact with data in database

2.    Data Redundancy: Less Data reputation

3.    Data Integrity: Data validation/Data Checking Process facility

4.    Backup and Recovery (Restore)

5.    Introducing support for XML

6.    User defined function are introduced.

7.    OLAP (online analytical process) services available in SQL server 7.0 are now called as SQL server 2005 analysis services

Things to Observe:

1.    While writing the Queries using T-SQL in SQL Server Management Studio we need not to follow any particular case. Because T-SQL is case insensitive language.

2.    After writing the Query, we need to select that query using either mouse or keyboard.

3.    Now Press F5 (Execute Key).

4.    Then the results are displayed in a separate window called Result window or Result Pane.

5.    Use Ctrl+R to Hide/Show the Result window or Result Pane.

6.    Use F8 for Object Explorer

Note:

1.    SQL SERVER can handle nearly 32767 Databases

2.    Each Database can handle nearly 2 billion Database Objects.

3.    Each Table can handle nearly 1024 columns

4.    Each Table can handle nearly 1 million Rows

# SQLSERVER is two parts, they are

1. Tsql :( Transact sql)

2. Tsql programs

Tool:

1. Management studio: It contains two parts, they are

    1. Cui (Character user interface):

• Sql scripting can be written in *newqury* option
• By using execute button or f5 we can execute the script

1.    Gui (Graphical user interface):

• It is Navigations part of the management Studio.

• We can connect to GUI by using *summary* option.

Path: startà programsà sqlserver 2005à Management Studio (click)

Inside management studio window provide following information

1. Server type-- database engine

2. Server name-- database server name

3. authentication--

A. Windows’s authentication

• This user must be an OS user

B. Sqlserver authentication

UN: sa (sysadmin-- DBA)

Pw: ******** (Depends Upon Installation)

Databases:

1. System databases:

·         These are inbuilt or default databases

·         Along with installation of Sqlserver 2005 we can get system databases

·         System databases monitors entire database engine

Note: User can’t create and delete a system database

A. master

B. model

C. msdb

D. tempdb

E. MS resources

	Description
master Database	Records all the system-level information for an instance of SQL Server.
msdb Database	It is used by SQL Server Agent for scheduling alerts and jobs.
model Database	Template database
Resource Database	It is a read-only database that contains system objects that are included with SQL Server 2005. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
tempdb Database	It is a workspace for holding temporary objects or intermediate result sets.

                                                                                                 

2. User databases:

These database created and maintained by the users explicitly

Ex:

CREATE DATABASE Sunil ON PRIMARY

(NAME=Sunil,

FILENAME="C:\MSSQL\vadde\Sunil.mdf",

SIZE=100,

MAXSIZE=200,

FILEGROWTH=25%)

Log on

(

NAME=Sunil_log,

FILENAME="C:\MSSQL\vadde\Sunil_log.ldf",

SIZE=100,

MAXSIZE=200,

FILEGROWTH=25%) 

SQL Server Data Types:

Character strings:

Data type	Description	Storage
char(n)	Fixed-length character string. Maximum 8,000 characters	n
varchar(n)	Variable-length character string. Maximum 8,000 characters
varchar(max)	Variable-length character string. Maximum 1,073,741,824 characters
text	Variable-length character string. Maximum 2GB of text data

Unicode strings:

Data type	Description	Storage
nchar(n)	Fixed-length Unicode data. Maximum 4,000 characters
nvarchar(n)	Variable-length Unicode data. Maximum 4,000 characters
nvarchar(max)	Variable-length Unicode data. Maximum 536,870,912 characters
ntext	Variable-length Unicode data. Maximum 2GB of text data

Binary types:

Data type	Description	Storage
bit	Allows 0, 1, or NULL
binary(n)	Fixed-length binary data. Maximum 8,000 bytes
varbinary(n)	Variable-length binary data. Maximum 8,000 bytes
varbinary(max)	Variable-length binary data. Maximum 2GB
image	Variable-length binary data. Maximum 2GB

Number types:

Data type	Description	Storage
tinyint	Allows whole numbers from 0 to 255	1 byte
smallint	Allows whole numbers between -32,768 and 32,767	2 bytes
int	Allows whole numbers between -2,147,483,648 and 2,147,483,647	4 bytes
bigint	Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807	8 bytes
decimal(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
numeric(p,s)	Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0	5-17 bytes
small money	Monetary data from -214,748.3648 to 214,748.3647	4 bytes
money	Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
float(n)	Floating precision number data from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. Float (24) holds a 4-byte field and float (53) holds an 8-byte field. Default value of n is 53.	4 or 8 bytes
real	Floating precision number data from -3.40E + 38 to 3.40E + 38	4 bytes

Date types:

Data type	Description	Storage
datetime	From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds	8 bytes
datetime2	From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds	6-8 bytes
smalldatetime	From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute	4 bytes
date	Store a date only. From January 1, 0001 to December 31, 9999	3 bytes
time	Store a time only to an accuracy of 100 nanoseconds	3-5 bytes
datetimeoffset	The same as datetime2 with the addition of a time zone offset	8-10 bytes
timestamp	Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable

Other data types:

Data type	Description
sql_variant	Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier	Stores a globally unique identifier (GUID)
xml	Stores XML formatted data. Maximum 2GB
cursor	Stores a reference to a cursor used for database operations
table	Stores a result-set for later processing

TSQL: This is a 4^th generated intermediate language between user and SQL Server. Whenever user wants to interact with SQL Server, he has to interact with SQL Server through T-SQL. It includes the following Sub Languages

Sub languages:

1. DDL (data definition language)

2. DML (data manipulation language)

3. DRL (data retrieval language) or QL (query language)

4. TCL (transaction control language)

5. DCL (data control language) -- DBA

1. DDL:

Commands:

1. Create

2. Truncate

3. Drop

4. Alter

1. Create: used to create database, tables and other database objects

Syntax: create table <tn>(col1 dt(l),col2 dt(l),col3 dt(l))

Ex:  create table tab1 (id int, ename varchar (20), DOB datetime)     

Note:

• Max length of a table name and column name is 30; it may include alphabets, digits 0 to 9 and special character '_' (Underscore).
• It is known as "naming conventions".
• Max of 1024 columns can be included into a table.

2. Truncate: used to delete entire data from a table and its not possible to delete selected or specific rows from a table by using truncate command.

Ex:

Assume that Tab1 is a table, contains 10 rows of data, now

Entire data of Tab1 table= 10rows

Selected or specific data of Tab1 table<entire data

1 to 9 rows are known as selected or specific data

Syntax: truncate table <TN>

Ex:  truncate table tab1

3. Drop: used delete a table from the database

Syntax: drop table <TN>

Ex:  drop table tab1

4. Alter:                                       

Alter+add: used to add a column to the table

Syntax: alter table <TN> add column_name DT (l), column_name DT (l)

Ex:  alter table tab1 add sal numeric (7, 2), comm Numeric (5, 2), address varchar (50)

Alter+drop: used delete columns from the table

Syntax: alter table <TN> drop column column_name

Ex:  alter table tab1 drop column address

Alter+alter: used change the data type and length of data type of a column

Note: Column is empty then only we can change data type

Syntax: alter table <TN> alter column column_name DT (l)

Ex:  alter table tab1 alter column id varchar (20)

Miscellaneous:

System stored procedures: Directly we can execute them whenever required

1. Getting description of a table (metadata)

Syntax: sp_help <TN>

Ex:  sp_help tab1

2. Getting description of a database:

Syntax: sp_helpdb <dbname>

Ex:  sp_helpdb Sunil

3. Displaying list of tables in a database

Syntax: sp_tables

Ex: sp_tables

4. Changing name of a table

Syntax: sp_rename <TN>, <new_name>

Ex:  sp_rename tab1, employ

5. Retrieving list of Databases

Syntax: sp_databases

DML:

Commands:

1. Insert

2. Update

3. Delete

1. Insert: Used to insert (add) data into tables

Syntax: insert into <TN> values (col1, col2, col3)

Ex:  insert into employ values ('a12','ramesh','12-apr-07', 3000, 30)

                                                    Id     name      DOB       sal      comm

Note: while working with char and date time data we should use single quotes

Inserting null values into table:

Student table contains 5 columns; now insert null value into comm column

Ex: insert into employ (id, name, DOB) values ('a13','ravi','21-jul-06', 2000)

Ex: insert into employ values ('a14','ramesh','12-apr-06', 5000, null)

2. Update: used to update (replace) old data with new data, it is two types they are

A. Updating entire data in a column:

Syntax: update <TN> set column_name=new_value

Ex:  update employ set sal=5000

B. Updating selected data in a column:

                                                                                                  

Syntax: update <TN> set column_name=new_value where <condition>

Ex:  update employ set sal=3000 where id='a12'

3. Delete: used to delete entire data and selected data from a table

A. Deleting entire data from a table:

Syntax: delete from <TN>

Ex:  delete from employ

B. Deleting selected data from a table:

Syntax: delete from <TN> where <condition>

Ex:  delete from employ where id='a14'

DRL:

Commands:

1. Select: used to retrieve or display data from table or tables

Note: Retrieving data from single table is 4 types

1. Retrieving entire data (all columns and all rows) from a table

Syntax: select*from <TN>

Ex:  select*from employ

Note: * stands for entire columns of the table

2. Retrieving all columns and selected rows from a table

Syntax: select*from <TN> where <condition>

Ex:  select*from employ where id='a12'                                        

3. Retrieving entire data from selected columns

Syntax:  select col1, col2 from <TN>

Ex:  select ename, fees from employ

4. Retrieving selected rows from selected columns

Syntax: select col1, col2 from <TN> where <condition>

Ex:  select name, fees from employ where id='a13'

DCL: It is used to control the data between different user accounts. It includes the following statements.

1.    GRANT Statement

2.    REVOKE Statement

Grant Statement: This statement is used to grant the permissions (INSERT, SELECT, UPDATE, DELETE) on a specific table to different user accounts.

Syntax:

GRANT {ALL/SPECIFIC PERMISSIONS} ON TABLE NAME

                                                TO USER ACCOUNT (S) [WITH GRANT OPTION]

 WITH GRANT OPTION: When any user got the permissions on a specific table from other user with this option, then that user can grant the permissions on that same table to another user account. At that time sub user acts as owner.

Ex:

GRANT ALL ON EMP TO RAMESH WITH GRANT OPTION

From the above statement RAMESH user account got all permissions on EMP table from SA user account. Mean time RAMESH can give the permissions on EMP to another user account because he got the permissions WITH GRANT OPTION.

Ex:  GRANT INSERT, SELECT ON EMP TO SUNIL

Now SUNIL can perform select and insert operations on EMP table.

But SUNIL cannot perform update and delete operations on EMP table because he does not have the corresponding permissions.

Revoke Statement: This statement is used to revoke the permissions (INSERT, SELECT, UPDATE, DELETE) on a specific table from different user accounts.

Syntax:

REVOKE {ALL/SPECIFIC PERMISSIONS} ON TABLE NAME

                                                FROM USER ACCOUNT (S) [CASCADE]

CASCADE: Using this option we can destroy the communication link between user accounts more over from the main user itself we can revoke the permissions from all sub users.

Ex:

REVOKE ALL ON EMP FROM SUBBU CASCADE

The above statement revokes the permissions on EMP table from SUBBU and SUNIL. Now SUBBU and SUNIL users cannot access EMP table.

TCL:

Transaction is nothing but a unit of work. We can control these transactions using the following statements

ROLLBACK Statement

COMMIT Statement

SAVE TRAN [SACTION]

ROLLBACK Statement: This statement is used to cancel a particular performed transaction. To perform this statement in SQL Server we have to follow any one of the below 2 approaches.

Approach 1: SET IMPLICIT_TRANSATIONS ON

 This approach is only to cancel a single recently performed operation.

Ex:

SET IMPLICIT_TRANSATIONS ON

SELECT * FROM EMP

DELETE FROM EMP

SELECT * FROM EMP

ROLLBACK

SELECT * FROM EMP

Approach 2: Explicit Transactions

To approach is to cancel recently performed multiple operations.

Syntax:  BEGIN TRAN

               -------------

               GO

               -------------

               GO

               -------------

            ROLLBACK TRAN

 Note: GO is query separator

Ex: 

BEGIN TRAN

INSERT INTO DEPT VALUES (50,’TRA’,’AUS’)

GO

UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11

GO

DELETE FROM STUDENT WHERE SNO=101

Select the entire transaction and press F5 for one time

ROLLBACK TRAN

The ROLLBACK statement cancels INSERT on Dept, UPDATE on EMP and DELETE on Student tables.

COMMIT Statement: This statement makes a transaction permanent. It is not possible to roll back the committed transaction.

           

Ex:

SELECT * FROM EMP

DELETE FROM EMP

SELECT * FROM EMP

COMMIT

SELECT * FROM EMP

The COMMIT statement deletes the data from EMP permanently. It is not possible to ROLLBACK the delete operation.

Ex:

BEGIN TRAN

INSERT INTO DEPT VALUES (50,’TRA’,’AUS’)

GO

UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11

GO

DELETE FROM STUDENT WHERE SNO=101

Select the entire transaction and press F5 for one time

COMMIT TRAN

The above COMMIT TRAN makes all three transactions permanent. We cannot ROLLBACK the transactions.

SAVE TRAN Statement: This statement is used to COMMIT/ROLLBACK a particular performed transaction from the set of transactions. It is associated with alphabets in order to save the transactions.

BEGIN TRAN

SAVE TRAN A

INSERT INTO DEPT VALUES (50,’TRA’,’AUS’)

SAVE TRAN B

UPDATE EMP SET SAL=SAL+1000 WHERE EMPNO=11

SAVE TRAN C

DELETE FROM STUDENT WHERE SNO=101

ROLLBACK TRAN C (The delete operation will be cancelled)

COMMIT TRAN B (The update operation performed permanently we cannot rollback)

TABLES FOR PRACTICALS:

a.   CREATE TABLE EMP

       (EMPNO NUMERIC(4) NOT NULL,

        ENAME VARCHAR(10),

        JOB VARCHAR(9),

        MGR NUMERIC(4),

        HIREDATE DATETIME,

        SAL NUMERIC(7, 2),

        COMM NUMERIC(7, 2),

        DEPTNO NUMERIC(2));

DATA:

INSERT INTO EMP VALUES

        (7369, 'SMITH',  'CLERK',     7902,'17-DEC-1980', 800, NULL, 20);

INSERT INTO EMP VALUES

        (7499, 'ALLEN',  'SALESMAN',  7698,'20-FEB-1981', 1600,  300, 30);

INSERT INTO EMP VALUES

        (7521, 'WARD',   'SALESMAN',  7698,'22-FEB-1981', 1250,  500, 30);

INSERT INTO EMP VALUES

        (7566, 'JONES',  'MANAGER',   7839,'2-APR-1981',  2975, NULL, 20);

INSERT INTO EMP VALUES

        (7654, 'MARTIN', 'SALESMAN',  7698,'28-SEP-1981', 1250, 1400, 30);

INSERT INTO EMP VALUES

        (7698, 'BLAKE',  'MANAGER',   7839,'1-MAY-1981',  2850, NULL, 30);

INSERT INTO EMP VALUES

        (7782, 'CLARK',  'MANAGER',   7839,'9-JUN-1981',  2450, NULL, 10);

INSERT INTO EMP VALUES

        (7788, 'SCOTT',  'ANALYST',   7566,'09-DEC-1982',  3000, NULL, 20);

INSERT INTO EMP VALUES

        (7839, 'KING',   'PRESIDENT', NULL,'17-NOV-1981', 5000, NULL, 10);

INSERT INTO EMP VALUES

        (7844, 'TURNER', 'SALESMAN',  7698,'8-SEP-1981',1500,    0, 30);

INSERT INTO EMP VALUES

        (7876, 'ADAMS',  'CLERK',     7788,'12-JAN-1983', 1100, NULL, 20);

INSERT INTO EMP VALUES

        (7900, 'JAMES',  'CLERK',     7698,'3-DEC-1981',   950, NULL, 30);

INSERT INTO EMP VALUES

        (7902, 'FORD',   'ANALYST',   7566,'3-DEC-1981', 3000, NULL, 20);

INSERT INTO EMP VALUES

        (7934, 'MILLER', 'CLERK',     7782,'23-JAN-1982', 1300, NULL, 10);

2. CREATE TABLE DEPT

       (DEPTNO NUMERIC(2),

        DNAME VARCHAR(14),

        LOC VARCHAR(13) );

DATA:

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');

INSERT INTO DEPT VALUES (30,

'SALES', 'CHICAGO');

INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');

3. CREATE TABLE SALGRADE

        (GRADE NUMERIC,

         LOSAL NUMERIC,

         HISAL NUMERIC);

DATA:

INSERT INTO SALGRADE VALUES (1,  700, 1200);

INSERT INTO SALGRADE VALUES (2, 1201, 1400);

INSERT INTO SALGRADE VALUES (3, 1401, 2000);

INSERT INTO SALGRADE VALUES (4, 2001, 3000);

INSERT INTO SALGRADE VALUES (5, 3001, 9999);

Operators:

1. Arithmetic operators: +, -,*, /

1. +:

Select sal, sal+1000 from EMP

2.-:

Select sal, sal-100 from EMP

3.*:

Select sal, sal*12 from EMP

4. /:

Select sal, sal/2 from EMP

2. Assignment operator’s :( relational operators)

=, <> or ! =,>,>=,<,<=

1. =:

Select*from EMP where deptno=20

2. <> or! =:

Select*from EMP where deptno<>20

3.>:

Select*from EMP where sal>3000

4.>=:

Select*from EMP where sal>=3000

5. <:

Select*from EMP where sal<3000

6. <=:

Select*from EMP where sal<=3000

3. Logical operators: and, or, not

Ex: in maths two conditions are there, condition A and condition B

A and B-- both conditions must be satisfied

A or B-- if anyone satisfied then we can get output

1. And:

Select*from EMP where sal>3000 and deptno=20

2. Or:

Select*from EMP where sal>3000 or deptno=20

Between operator :( Range operator)

Ex: select*from EMP where sal between 2000 and 5000

Not between:

Ex: select*from EMP where sal not between 2000 and 5000

In operator :( Minimizes searching time)

Select*from EMP where ename in ('Scott', 'Turner’, ‘Sunil')

Not in:

Select*from EMP where ename not in ('scott','turner','Sunil')

Like operator :( Pattern matching)

Ex: select*from EMP where ename like’s%'

S%-- ename starts with s

%s-- ename ends with s

%s%-- somewhere s in ename

_s%-- second character is s from beginning

%s_ -- second from ending is s in ename

Note: _ (underscore) denotes one character

Not like operator:                                                                                  

Select*from EMP where ename not like’s%'

Alias:

• Second or duplicate name, can be assigned to a table or column
• Alias is temporary
• Select sal*12 from EMP

• Select sal*12 annual Sal from EMP

Note: annual Sal is alias for sal*12

Inbuilt functions:

1. Aggregate functions :( Group functions)

1. Sum ():

Select sum (sal) from EMP                        

2. Average ():

Select avg (sal) from EMP

3. Max ():

                                                              

Select max (sal) from EMP

4. Min ():

Select min (sal) from EMP

5. Count (): it counts number of rows

Select count (*) from EMP

Select count (ename) from EMP

Select count (comm) from EMP

Note:

1. Count Function doesn’t consider null as a value.

2. COUNT function does not count the rows of the column whose data type is BIGINT, In such situations we have to use COUNT_BIG function

6. Standard deviation ():

Select stdev (sal) from EMP

7. Variance ():

Select var (sal) from EMP

DISTINCT ( ): This function displays the different values available for a specific column. It considers one NULL value.

Ex: SELECT DISTINCT (DEPTNO) AS DIFF_VALUES FROM EMP

DIFF_VALUES

            10

            20        

           

Ex: SELECT COUNT (DISTINCT( DEPTNO)) AS NO_OF_ROWS FROM EMP

NO_OF_ROWS

            2

2. Numeric functions:

1. Absolute:

Select abs (-9.5)

O/p: 9.5

2. Ceiling:

Select ceiling (9.01)

O/p: 10

3. Floor:

Select floor (9.9)

O/p: 9

4. Square root:

Select sqrt (144)

O/p: 12

5. Power:

Select power (3, 3)

O/p: 27(3*3*3)

6. Square:

Select square (3)

O/p: 9(3*3)

7. Pi ():

Select pi ()

O/p: 3.14(22/7)

8. Log:

Select log (2)

O/p: 0.693147180559945

9. Exp:

Select exp (2)

O/p: 7.38905609893065

10. ROUND (M, N):

IT WILL ROUND THE VALUE OF M TO NEAREST WHOLE NUMBER OF IT WILL AROUND.

à SELECT ROUND (15.143)

15

           

à SELECT ROUND (16.513)

16

           

à SELECT ROUND (16.816)

17

à SELECT ROUND (21.132,1)

21.1

à SELECT ROUND (25.143)

25

3. Character functions :( String functions)

1. Length:

Select Len ('Hyderabad')

O/p: 9

2. Upper:

Select upper ('Hyderabad')

O/p: HYDERABAD

3. Lower:

Select lower ('HYDERABAD')

O/P: Hyderabad

4. Reverse:

Select reverse ('Malayalam')

O/p: Malayalam

Select reverse ('school')

O/p: loohcs

5. Replace:

Select replace ('hyderabad','hyd','cyb')

O/p: cyberabad

6. Substring:

Select substring ('hyderabad', 3, 5)

O/p: derab

7. ASCII (Character): It gives ASCII value of a character

Select ASCII (‘A’) O/p: 65

Select ASCII (‘a’) O/p: 97

                                                                                                                            8. Char (n): It gives character for given ASCII value

Select char (98) O/p: b

Date time functions:

1. Getdate ():

Select getdate ()

                     

O/p: 2009-07-17 18:11:20.000

2. Getutcdate ():

Select getutcdate ()

O/p: 2009-07-17 12:42:09.263

3. Dateadd:

Select dateadd (dd, 7, getdate ())

O/p: 2010-04-27 15:24:15.827

4. Datediff:

Select datediff (mm,'03/26/04', getdate ())

O/p: 73

5. Date part:

Select datepart (yy, getdate ())

O/p: 2010

Select datepart (mm, getdate ())

O/p: 4

Select datepart (dd, getdate ())

O/p: 20

6. Date name:

Select datename (DW, getdate ())

O/p: Tuesday

Set operators:

a= {1, 2, 3}

b= {3, 4, 5}

1. A union B= {1, 2, 3, 4, 5}

2. A union all B= {1, 2, 3, 3, 4, 5}

Note: union all retrieves duplicate data

3. A intersect B= {3}

4. A except B = A minus B= {1, 2}

1. Union: SELECT EMPNO FROM EMP UNION SELECT DEPTNO FROM DEPT

2. Union all: SELECT EMPNO FROM EMP UNION ALL SELECT DEPTNO FROM DEPT

3. Intersect: SELECT EMPNO FROM EMP INTERSECT SELECT DEPTNO FROM DEPT

4. Except: SELECT EMPNO FROM EMP EXCEPT SELECT DEPTNO FROM DEPT

Group by clause:

·         Used to group similar data in the output

·         Conjunction of the similar data

·         Used to divide the table into number of subgroups based on a specific column.

·         Minimum single group or aggregate function is mandatory

·         If column contains similar data then only we can use it into group by clause

# Display the number of employee working in different jobs of EMP table

Ex: Select job, count (job) from EMP Group by job

# Display max and min salaries of employees who are working in different deptno's of EMP table

Ex: Select deptno, max (sal), min (sal) from EMP Group by (deptno)

Ex: SELECT DEPTNO, MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL, SUM (SAL) AS TOTSAL,     AVG (SAL) AS AVGSAL FROM EMP

GROUP BY DEPTNO

Having clause:

• It is an arithmetical expression

• It can be used as an extension of group by clause and we can’t use where clause after group by clause

• This clause is used to evaluate a condition with group by clause. Generally for evaluating conditions we will use WHERE clause, but where clause does not support by group by clause.

Ex: Select job, count (deptno) from EMP

      Group by job

      Having count (deptno)>2

Ex: SELECT DEPTNO, MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL,

SUM (SAL) AS TOTSAL, AVG (SAL) AS AVGSAL FROM EMP

GROUP BY DEPTNO HAVING AVG (SAL)>10000

Order by clause:

• It is temporary sorting of the data either in ascending order or in Descending order

• Default is ascending order

Ascending:

Ex: Select*from EMP order by sal

Descending:

Ex: Select*from EMP order by sal desc

Clause precedence:

1. Where clause

2. Group by clause

3. Having clause

4. order by clause

Joins:

• Used to retrieve data from multiple tables

• Joins plays major role in performance tuning

• Joins comes under temporary relations

• We need a common column or common data column in multiple tables

• Two tables are there, they are EMP and DEPT am creating joins between them

• Both tables contains a common column i.e. deptno, so that we can create Joins between EMP, DEPT tables

• Formula: no. of joins=no. of tables-1

Types of JOINS:

1. Inner join :( Equi join)

We are retrieving data from EMP, DEPT tables

Ex: Select empno, ename, sal, d.deptno, dname, loc from EMP e

Inner join DEPT d on e.deptno=d.deptno

Note: Query retrieves equal number of rows from both tables

2. Outer join: This is three types

1. Left outer join:

Select empno, ename, sal, d.deptno, dname, loc from EMP e

Left outer join DEPT d on e.deptno=d.deptno

It retrieves condition satisfies and non-satisfied data from left side table of the condition i.e. EMP, it retrieves only condition satisfied data from right side table of the condition i.e. DEPT

2. Right outer join:

Select empno, ename, sal, d.deptno, dname, loc from EMP e

Right outer join DEPT d on e.deptno=d.deptno

It retrieves condition satisfied from left side table of the condition i.e. EMP, it retrieves condition satisfied data and non-satisfied data from right side table of the condition i.e. DEPT

3. Full outer join: This is combination of both right and left outer joins.

Select empno, ename, sal, d.deptno, dname, loc from EMP e

Full outer join DEPT d on e.deptno=d.deptno

It retrieves condition satisfied and non-satisfied data from both tables.

3. Non-equi join: EMP table contains a column sal, in SALGRADE table, we got two columns losal and hisal so that we can define a join condition.

Select empno, ename, sal, grade from EMP e, SALGRADE s where

e.sal between s.losal and s.hisal

By using above query we can map employees and their grades.

4. Cross join :( Cartesian product)

a= (1, 2, 3)

b= (4, 5, 6)

A*B=3*3=9

EMP table contains 14 rows

Dept table contains 4 rows

Cross join of EMP, DEPT tables produces 56 rows

Ex: Select*from EMP cross join DEPT

Creating a table by copying data and structure from another table:

Syntax: Select*into <TN> from <existing_table>

Ex: Select*into EMP1 from EMP

Ex: (Oracle) Create table EMP1 as select*from EMP;

Creating a table by copying structure from another table:

   In this by defining false condition we can avoid copying of data.

Syntax:  Select*into <TN> from <existing_tabel> where <false_condition>

Ex: Select*into EMP2 from EMP where 1=2

SUB-QUERIES:

• It is a Combination of multiple queries or select statements.

• Query with in a query is known as Sub-Query

Syntax: Select statement (Select statement)

         Outer query           inner query

Types:

1. Simple or single row sub query:

• It returns only single row to the user

Ex: Retrieve second highest sal from EMP table

Select max (sal) from EMP where sal< (select max (sal) from EMP)

2. Nested sub query:

• It is also simple sub query

• It returns single row

Ex: Retrieve third highest sal from EMP table

Select max (sal) from EMP where sal< (select max (sal) from EMP where

Sal< (select max (sal) from EMP))

Distinct: It eliminates duplications temporarily.

Ex:

1. Select sal from EMP

2. Select distinct (sal) from EMP

Ø  Formula for retrieving n highest salaries from EMP table:

Select max (sal) from EMP e where n= (select count (distinct (sal)) from EMP b where e.sal<b.sal)

• N is a number which indicates position of the sal

• N starts from zero

• n=n-1

Ø  Formula for Min sal:

Select min (sal) from EMP e where n= (select count (distinct (sal)) from

EMP b where e.sal>b.sal)

3. Co-related sub query: It can return single and multiple rows to the user

I want to retrieve deptno 20 data from EMP table, for that I want Satisfy the condition in dept table

Ex: Select*from EMP where deptno= (select deptno from DEPT where dname='accounting')

Note: If a sub query sends multiple values to its nearest main query then we have to use IN operator between Main query and Sub query.

Ex: WAQ to display employee details, who are working under RAM dept.

SELECT * FROM EMP WHERE DEPTNO= (SELECT DEPTNO FROM EMP WHERE ENAME=’RAM’)

Ex: WAQ To display employee details, whose salary is greater than highest salary of 10^th department

SELECT * FROM EMP WHERE SAL> (SELECT MAX (SAL) FROM EMP WHERE DEPTNO=10)

Ex: WAQ To display employee details, whose salary is greater than average salary of RAM department

SELECT * FROM EMP WHERE SAL> (SELECT AVG (SAL) FROM EMP WHERE DEPTNO=

SELECT DEPTNO FROM EMP WHERE ENAME=’RAM’)

Constraints:

v  Set of rules which are used to improve functionality of tables

v  These are inbuilt rules

v  We can impose constraints on columns

Adv.:

1. Creating permanent relations between tables

2. We can avoid duplications and null values

3. We can create permanent conditions

Note: We can create a constraint along with table creation this is of two types:

1. Column level Constraint: It can be created on single column

2. Table level Constraint: It can be created on multiple columns

Note: We can add a constraint to the existing table

Types:

1. Primary key

2. Unique constraint

3. Check constraint

4. Not null

5. Foreign key (Referential integrity)

1. Primary key:

Properties:

• It is unique
• It is not null

# Column level Primary Key:

Syntaxtax: create table <TN> (col1 DT (l) constraint con_name primary key, col2 DT (l), col3 DT (l))

Ex: create table t1 (id int constraint p_key primary key, name varchar (20), DOB datetime)

·         In this we can create a constraint on single column only

# Table level Primary key:

Syntaxtax: create table <tn>(col1 dt(l),col2 dt(l),col3 dt(l), constraint con_name primary key(col1,col2))  

Ex:  create table t2 (id int, name varchar (20), DOB datetime, constraint p_key3 primary key (id, name))

• We can create single constraint on multiple columns

• Max of single primary key can be created on a table

• If single primary key is working on multiple columns then it is  known as "composite primary key"

2. Unique constraint:

Properties:

• Unique
• It accepts single null value

Syntax: create table <TN> (col1 DT (l) constraint con_name unique, col2 DT (l), col3 DT (l))

Ex:  create table t3 (id int constraint u_con unique, dname varchar (20), loc varchar (20))

3. Check constraint: used to impose permanent conditions on columns

Syntax: create table <tn>(col1 dt(l),col2 dt(l) constraint con_name check(condition),col3 dt(l))

Ex:  create table t4 (id int, sal numeric (7, 2) constraint check_con check (sal>3000), commission numeric (5, 2))

4. Not null: it doesn’t accept null values

Syntax: create table <TN> (col1 DT (l) constraint con_name not null)

Ex: create table t5 (id int constraint n_null not null)

5. Foreign key:

• It always references values from either primary key or unique constraint and there are no properties for foreign key.

• It also known as referential integrity

• This reference will be stored into database permanently, so that
• It is comes under permanent relation

• Generally primary key and foreign are a part of different tables

• Primary key table is known as parent or master table

• Foreign key table is known as child or detail table

• We can create primary key and foreign key in single table, that is known as "self-referential integrity"

Note:

1.       Primary key and foreign key columns data types must be same.

2.       If the value existing in primary key then only we can insert same value into foreign key.

3.       Foreign key accepts null values when it is referencing from unique constraint

Syntax: create table <tn>(col1 dt(l),col2 dt(l),col3 dt(l), constraint con_name foreign key(col1)references <tn2>(col1))

Ex:  create table t6 (id int, address varchar (50), job varchar (20), constraint f_key foreign key (id) references t1 (id))

Adding constraint to a table:

Syntax: alter table <TN> add constraint con_name type_constraint (col_list)

Ex:  alter table t2 add constraint p_key3 primary key (id)

Dropping constraint:

Syntax: alter table <TN> drop constraint con_name

Ex: alter table t2 drop constraint p_key3

Information regarding constraints of a table:

Syntax: sp_helpconstraint <TN>

Ex:  sp_helpconstraint t1

Normalization: Normalization is process of splitting the base table into multiple tables based on the theory of Functional Dependency.

                                    OR

Normalization is repetitive process in order to identify the functional dependencies among the columns and to remove them. If any functional dependency is occurred after the normalization process again we have to start the same process until all functional dependencies have been removed.

To do this Normalization we have to follow rules or conditions called Normal Forms.

Un-Normalized Table

EMPNO PROJNO   ENAME PNAME    SAL BUD DEPTNO DNAME LOC

11              (P1, P2)       ----       (Pn1, Pn2) -----   -----        10           --------      -----

22              (P2, P3)       ----       (Pn2, Pn3) -----   -----        10           --------      -----

33              (P1, P3)       ----       (Pn1, Pn3) -----   -----        20           --------      -----

EMPNO and PROJNO are Primary Keys called ‘COMPOSITE PRIMARY KEY’

FIRST NORMAL FORM (1NF): According to first normal form table should contain only single values columns. But in the above un-normalized table the columns PROJNO and PNAME contains multiple values.

To make the table into first normal form we should have to split the multiple values into single values.

EMPNO PROJNO   ENAME PNAME    SAL BUD DEPTNO DNAME LOC

11              P1                ----          Pn1         -----   -----        10           --------      -----

11              P2                ----          Pn2         -----   -----        10           --------      -----

22              P2                ----          Pn2         -----   -----        10           --------      -----

22              P3                ----          Pn3         -----   -----        10           --------      -----

33              P1                ----          Pn1         -----   -----        20           --------      -----

33              P3                ----          Pn3         -----   -----        20           --------      -----

SECOND NORMAL FORM (2NF):

According to second normal form table should be in 1NF and we should have to remove Partial Functional Dependency.

In the above table DEPTNO non-key column dependent part of the Primary key column i.e. EMPNO. It means there existed Partial functional dependency.

To make the table into second normal form we have to divide the table into multiple tables.

PROJ-INFO     

                                                  

PROJNO          PNAME                 BUD     

   P1                  Pn1                   ------

   P2                  Pn2                   ------

   P3                  Pn3                   ------

EMP-INFO

EMPNO   ENAME   SAL   DEPTNO   DNAME   LOC

11                ----           ----       10              ------        -----

22                ----           ----       10              ------        -----

33                ----           ----       20              ------        -----

THIRD NORMAL FORM (3NF):

According to second normal form table should be in 2NF and we should have to remove Transitive Functional Dependency.

In the above EMP-INFO table non-key column DNAME dependent part on the other non- key column i.e.DEPTNO. It means there existed Transitive functional dependency.

To make the table into third normal form we have to divide the table into multiple tables.

PROJ-INFO                                                       

PROJNO           PNAME               BUD     

   P1                  Pn1                   ------

   P2                  Pn2                   ------

   P3                  Pn3                   ------

EMP-INFO                                 DEPT-INFO              

EMPNO   ENAME   SAL      DEPTNO   DNAME   LOC

11                ----           ----           10              ------        -----

22                ----           ----           20              ------        -----

33                ----           ----      

Views:

A View is nothing but an image table or virtual table, which is created for a base table. A view can be created by taking all values from the base table or by taking only selected values from base table. There are two types’ views available in SQL Server.

1.       Simple Views

2.       Complex Views

Note: If we perform any modifications in base table, then those modifications automatically effected in view and vice-versa.

1. Simple Views: Creating View by taking only one single base table.

Syntax:

CREATE VIEW VIEWNAME [WITH ENCRYPTION]

AS SELECT * FROM TABLENAME [WHERE CONDITION]

   [WITH CHECK OPTION]

Ex:

CREATE VIEW V1 AS SELECT * FROM EMP

INSERT INTO V1 VALUES (55,’RAVI’, 10000, 10)

The above insert statement inserts the values into base table EMP as well as into view V1.

Ex:

CREATE VIEW V2 AS SELECT * FROM EMP WHERE DEPTNO=10

INSERT INTO V2 VALUES (66,’BABBU’, 25000, 10)

The above insert statement inserts the values into base table EMP as well as into view

V2.

INSERT INTO V2 VALUES (77,’AMAR’, 15000, 20)

The above insert statement inserts the values into only base table EMP but not into view

V2 because according to the definition of V2 user supplied values are invalid values. It means invalid values are inserting into base table EMP. To stop this kind of operations we have to create the view with ‘WITH CHECK OPTION’.

Ex:

CREATE VIEW V3 AS SELECT * FROM EMP WHERE DEPTNO=10

        WITH CHECK OPTION

INSERT INTO V3 VALUES (88,’TEJA’, 25000, 20)

The above insert statement cannot insert the values into base table EMP as well as into view V3.

SP_HELPTEXT: This stored procedure is used to display the definition of a specific view.

Syntax: 

 SP_HELPTEXT    VIEWNAME

Ex: SP_HELPTEXT      V1

Output: CREATE VIEW V1 AS SELECT * FROM EMP

WITH ENCRYPTION: Once we create any view with ‘WITH ENCRYPTION’ then we cannot find the definition of that particular view using SP_HELPTEXT stored procedure because this encryption option hides the definition.

Ex:

CREATE VIEW V4 WITH ENCRYPTION

AS SELECT * FROM EMP WHERE DEPTNO=20

SP_HELPTEXT V4

Output: The text for object v4 is encrypted

To decrypt the definition of view V4 we have to follow the below approach

1.       Replace CREATE with ALTER

2.       Remove WITH ENCRYPTION keyword

Ex:

ALTER VIEW V4 AS SELECT * FROM EMP WHERE DEPTNO=20

SP_HELPTEXT V4

CREATE VIEW V4 AS SELECT * FROM EMP WHERE DEPTNO=20

2. Complex Views:

Creating View by taking multiple base tables.

Ex:

CREATE VIEW EMP_DEPT_VIEW AS SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO

Syntax: to create view based on another views:

SQL SERVER enables users to create views based on another view. We can create view based on another view up to 32 levels

Syntax:

CREATE VIEW VIEWNAME [WITH ENCRYPTION] AS SELECT * FROM VIEWNAME [WHERE CONDITION]  [WITH CHECK OPTION]

Ex:

 CREATE VIEW V5 AS SELECT * FROM V1 WHERE DEPTNO=10

Syntax: to Drop the Views:

DROP VIEW VIEWNAME […N]

Ex: DROP VIEW V1, V2, V3, V4, V5

Indexes:

Indexes in SQL server is similar to index in text book... Indexes are used to improve the performance of queries.

# INDEXES ARE GENERALLY CREATED FOR FOLLOWING COLUMNS

1.    Primary key column

2.    Foreign key column:  frequently used in join conditions.

3.    Column which are frequently used in where clause

4.    Columns, which are used to retrieve the data in sorting order.

# INDEXED CANNOT BE CREATED FOR FOLLOWING COLUMNS

1.    The columns which are not used frequently used in where clause.

2.    Columns containing the duplicate and null values

3.    Columns containing images, binary information, and text information.

TYPES OF INDEXES:

o   CLUSTERED INDEX

o   NON-CLUSTERED INDEX

CLUSTERED INDEX: only one clustered index is allowed per table. The order of values in a table order of values in index is also same. When cluster index is created on table data is arranged in ascending order cluster index will occupy 5% of the table.

Syntax:

CREATE [UNIQUE] CLUSTERED INDEX INDEXNAME ON TABLENAME (COLUMN)

Ex:

CREATE CLUSTERED INDEX CI ON EMP (EMPNO)

Note: If we want to maintain unique values in clustered/non clustered indexed column then specify UNIQUE keyword along with CLUSTERED INDEX/NONCLUSTERD INDEX

NONCLUSTERED INDEX: It is the default index created by the server the physical order of the data in the table is different from the order of the values in index. Max no. Of non-clustered indexed allowed for table is 249

Syntax:

CREATE [UNIQUE] NONCLUSTERED INDEX INDEXNAME

 ON TABLENAME (COLUMN1…)

Ex: CREATE NONCLUSTERED INDEX NCI ON EMP (ENAME, SAL)

Ex:  CREATE UNIQUE NONCLUSTERED INDEX UI ON DEPT (DNAME)

COMPOSITE INDEX: If a Unique NonClustered index is created on more than one column then that concept is called composite index.

CREATE UNIQUE NONCLUSTERED INDEX COI ON DEPT (DEPTNO, DNAME)

DEPTNO     DNAME

10               SALES

20               HR

30               IR

10               HR (Accepted)

20               SALES (Accepted)

30               IR (Repeated, Not accepted)

SP_HELPINDEX: This stored procedure is used to display the list of indexes, which have been placed on different columns of a specific table.

Ex: SP_HELPINDEX   EMP

Syntax: to drop the index:

DROP INDEX TABLENAME.INDEXNAME

Ex:

DROP INDEX DEPT.UI

Synonyms:

• It is a database object
• It is a second or duplicate name which can be assigned to a table
• It will not occupies any space, just it is a second name of the table
• Trough Synonyms we can impose securities i.e. we can hide actual table names

Syntax: create Synonym Syn_name for <TN>

Ex:  create Synonyms Syntax_1 for EMP

Retrieving data:

Syntax: select*from Syn_name

Ex:  select*from Syntax_1

Dropping:

Syntax: drop Synonyms <TN>

Ex:  drop Synonyms Syn_1

RULES AND DEFAULTS:

CREATING DEFAULT: Default is one of the database objects used to declare default values globally.

Syntax: CREATE DEFAULT DEFAULTNAME AS NUMERICVALUE/STRING VALUE

Ex: CREATE DEFAULT D1 AS ‘UNKNOWN’

BINDING THE DEFAULT TO COLUMN:

Syntax: SP_BINDDEFAULT   DEFAULTNAME, ‘TABLENAME.COLUMN’

Ex:

SP_BINDDEFAULT   D1, ‘DEPT.LOC’

SP_BINDEFAULT D1, ‘EMP.ENAME’

INSERT INTO DEPT (DEPTNO, DNAME) VALUES (10, ACCOUNTING’)

SELECT * FROM DEPT WHERE DEPTNO =10

            DEPTNO            DNAME              LOC

            10                ACCOUNTING     UNKNOWN

INSERT INTO EMP (EMPNO, SAL, DEPTNO) VALUES (100, 5000,20)

SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE EMPNO =100

            EMPNO              ENAME              DEPTNO

            100                               UNKNOWN                     20        

UNBINDING THE DEFAULT FROM THE BINDING COLUMN:

Syntax:  SP_UNBINDEFAULT ‘TABLENAME...COLUMN’

SP_UNBINDEFAULT         ‘ DEPT.LOC’

SP_UNBINDEFAULT         ‘ EMP.ENAME’

DROPPING THE DEFAULT:

Syntax:  DROP DEFAULT DEFAULTNAME

 DROP DEFAULT D1

CREATING RULES: Rule is just check constraint but it is placed on columns globally.

Syntax: CREATE RULE RULENAME AS   EXPRESSION

Ex: CREATE RULE R1 AS @ X >=10 AND @ X <= 60

BINDING THE RULE:

            SP_BINDEFAULT           RULENAME, ‘ TABLE.COLUMN’

            SP_BINDEFAULT R1, ‘DEPT.DEPTNO’

            INSERT INTO DEPT (DEPTNO) VALUES (70)

ERROR

UNBINDING THE RULES:

SP_UNBIND RULE ‘DEPT.DEPTNO’

DROPPING THE RULES:

DROP RULE RULENAME

DROP RULE    R1

TSQL Programs:

Variable: whose can be varied is known as a variable

Types:

1. Local variable: once we declared local variable then it can be used into single program

Ex: @x int

2. Global variable: once we declared global variable then it can be used into many no. of programs

Ex: @@y int

Constant: whose value can’t be varied?

Ex: 1, 2,3,4,5

Note: tsql programs also known as tsql batches, these are two types

1. Named batches

2. Unnamed batches

Structure:

Declare

Declaration and initiation of variables

Begin

Body of the program

End

1. Declare: User can declare variables according to the requirement of the batch and can assign initial     values to variables

Ex: declare @x int

      Set @x=10  

2. Begin: It contains actual code i.e.

Logic

Exceptions

Conditional statements

Output

Output: print 'output'

3. End: Ending of the batch

Ø  WAP to display the word 'welcome to database'

Begin

Print 'welcome to database'

End

Ø  WAP to calculate sum of two numbers

Declare @x int

 Set @x=20

Declare @y int

 Set @y=10

Declare @z int

Begin

Set @z=@x+@y

Print @z

End

Conditional Statements:

1. If. Else:

Syntax:

Declare

Variable declaration and initiation

Begin

If condition

Output

Else

Output

End

Ø  WAP to find out greatest number among two numbers

Declare @x int

 Set @x=20

Declare @y int

 Set @y=10

Begin

If @x>@y

Print 'x is greatest'

Else

Print 'y is greatest'

End

2. If. Elseif... Else:

Syntax:

Declare

Variable declaration and initiation

Begin

If condition

Output

Else if condition

Output

Else

Output

End

Ø  WAP to find out greatest number among three numbers

Declare @x int

Set @x=20

Declare @y int

Set @y=10

Declare @z int

Set @z=5

Begin

If @x>@y and @x>@z

Print 'x is greatest'

Else if @y>@x and @y>@z

Print 'y is greatest'

Else

Print 'z is greatest'

End

3. While:

Syntax:

Declare

Variable declaration and initiation

While condition

Begin

Body of the program

End

Ø  WAP to display numbers from 1 to 10 with increment of 1

DECLARE @counter INT

SET @counter=1

WHILE @counter <= 10

BEGIN

PRINT @counter

SET @counter=@counter+1

END

Cursors: Cursor is a logical area, which is used to retrieve a particular nth record. Selecting a particular nth record is not possible through physical area (Table). In such situations one logical area (Cursor) we can create and then we can select a particular nth record. Cursors are used to store transaction information temporarily.

Types of Cursors:

1.       STATIC CURSOR

2.       DYNAMIC CURSOR

3.       KEYSET CURSOR

4.       FORWARD_ONLY CURSOR

DEPT_TABLE:

DEPTNO            DNAME             LOC

10                     SALES               HYD

20                     HR                    CHE

30                     IR                     BAN     

40                     A/C                  MUM

Syntaxtax:

Syntaxtax to declare the Cursor:

DECLARE CURSORNAME CURSOR CURSORTYPE

                                   FOR SELECT * FROM TABLENAME

Syntaxtax to open the Cursor:

OPEN CURSORNAME

Syntaxtax to fetch the records from Cursor:

FETCH {FIRST/NEXT/PRIOR/LAST/ABSOLUTE N/RELATIVE N}

              FROM CURSORNAME

Syntaxtax to close the Cursor:

CLOSE CURSORNAME

Syntaxtax to de allocates the Cursor:

DEALLOCATE CURSORNAME

FIRST: Fetches first record from the cursor

NEXT: Fetches next record from the current position of the cursor

PRIOR: Fetches previous record from the current position of the cursor

LAST: Fetches last record from the cursor

ABSOLUTE N: Fetches nth record from the top of the cursor if n is positive fetches the nth record from bottom of the cursor if n is negative. Where n is an integer

RELATIVE N: Fetches nth next record from current position of the cursor if n is positive fetches nth previous record from the current position of the cursor if n is negative where n is an integer.

1. STATIC CURSOR: This is the logical area in which dynamic updations not possible. If we want those updations in logical area we close the cursor and we need to reopen it.

1.DECLARE SC CURSOR STATIC FOR SELECT * FROM DEPT

OPEN SC

FETCH FIRST FROM SC

10         SALES    HYD

UPDATE DEPT SET DEPTNO=15 WHERE DEPTNO=10

FETCH FIRST FROM SC

10         SALES    HYD

CLOSE SC

OPEN SC

FETCH FIRST FROM SC

15         SALES    HYD

FETCH NEXT FROM SC

20         HR        CHE

FETCH PRIOR FROM SC

15         SALES   HYD

FETCH LAST FROMSC

40         A/C      MUM

FETCH ABSOLUTE 2 FROM SC

20         HR        CHE

FETCH ABSOLUTE –2 FROM SC

30         IR         BAN

FETCH FIRST FROM SC

15         SALES    HYD

FETCH RELATIVE 2 FROM SC

30         IR         BAN

FETCH RELATIVE –2 FROM SC

15         SALES    HYD

CLOSE SC

DEALLOCATE SC

2. DYNAMIC CURSOR: This is the logical area in which dynamic updations possible. We need to close and reopen the cursor for the modified values

Ex:

DECLARE DC CURSOR DYNAMIC FOR SELECT * FROM DEPT

OPEN SC

FETCH FIRST FROM DC

15         SALES    HYD

UPDATE DEPT SET DEPTNO=10 WHERE DEPTNO=15

FETCH FIRST FROM DC

10         SALES    HYD

CLOSE DC

DEALLOCATE DC

Note: ABSOLUTE N will not be supported by the dynamic cursor because dynamic updations are possible.

3. KEYSET CURSOR: This is the logical area, which is useful only when there is a primary key in the table. This logical area holds only Primary key column values. Based on the key column values in the logical area (Cursor) the rest column values are coming from physical area (Table).

Ex:

DECLARE KC CURSOR KEYSET FOR SELECT * FROM DEPT

OPEN KC

FETCH FIRST FROM KC

10         SALES    HYD

UPDATE DEPT SET LOC=’SRNAGAR’ WHERE LOC=’HYD’

FETCH FIRST FROM KC

10         SALES    SRNAGAR

UPDATE DEPT SET DEPTNO=15 WHERE DEPTNO=10

FETCH FIRST FROM KC

0          NULL                 NULL

CLOSE KC

OPEN KC

FETCH FIRST FROM KC

15         SALES    HYD

CLOSE KC

DEALLOCATE KC

Note: Dynamic updations are not possible on key column of the keyset cursor, we have to close the cursor and we need to reopen it for the modified values.

4. FORWARD_ONLY CURSOR: This is the most unused logical area because it supports only NEXT operation.

Ex:

DECLARE FC CURSOR FORWARD_ONLY FOR SELECT * FROM DEPT

OPEN FC

FETCH FIRST FROM FC

Error Message

FETCH NEXT FROM FC

15         SALES    HYD

CLOSE SC

DEALLOCATE FC

NOTE:

If the cursor does not found any value in its searching process then it will display the corresponding column values.

1. BATCH TO FETCH MULTIPLE RECORDS FROM STATIC CURSOR

Step1: DECLARE SC CURSOR STATIC FOR SELECT * FROM DEPT

Step2:

DECLARE @N INT

OPEN SC

SET @N=2

WHILE (@N<=4)

BEGIN

FETCH ABSOLUTE @N FROM SC

SET @N=@N+1

END

           

Step3: CLOSE SC

            DEALLOCATE SC

OUTPUT:

DEPTNO DNAME  LOC

20                     HR                    CHE

DEPTNO DNAME  LOC

30                     IR                     BAN

DEPTNO DNAME  LOC

40                     A/C                  MUM

Stored Procedures:

·         It is a database object

·         After successful compilation it will be stored into a precompiled programming unit

·         So that without compilation we can use same stored procedure any number of times

·         User can get reusability

·         It is major part performance tuning

·         Used complete a task in database

Types:

1. System stored procedures:

Ex:

sp_tables

sp_helptable <TN>

sp_helpdb <database name>

sp_rename

sp_addumpdevice

2. User stored procedure:

• defined by the users explicitly

• variable are known as "parameters"

• parameters are two types

1. In :( default) used to pass the values

2. Output:  used to get the values

Note: stored procedure can return multiple values through its output parameter

Syntax:

 CREATE PROC [EDURE] PROCEDURENAME

            [@PARA 1 DATATYPE (SIZE) [=DEFAULT_VALUE] [OUTPUT]

            @PARA 2 DATATYPE (SIZE) [=DEFAULT_VALUE] [VALUE],….]

AS

BEGIN

SELECT STATEMENT

END

Syntax: to execute the user defined stored procedure:

EXEC [UTE] PROCEDURENAME [VALUE1, VALUE2…]

Drop:

Drop procedure pro_name

Programs:

Ø  Create a stored procedure to insert data into dept table

Create procedure insert_dept @dno int,@name varchar (20),

@loc varchar (20)

As

Begin

Insert into dept (deptno, dname, loc) values (@dno, @name, @loc)

End

Execution:

Exec insert_dept 50,'csc','hyderabad'

Result:

Select*from dept

Ø  Create a stored procedure to update dname, loc according to the given deptno of dept table

Create procedure update_dept @dno int, @name varchar (20),

@loc varchar (20)

As

Begin

Update dept set dname=@name,loc=@loc where deptno=@dno

End

Execution:

Exec update_dept 50,'eee','india'

Result:

Select*from dept

Note: The number of values supplied through EXEC statement must be equal to the number parameters.

Ø  Write a procedure to select the data from EMP table.

CREATE PROCEDURE P1

AS

BEGIN

SELECT * FROM EMP

END

      EXEC P1

Ø  Write a procedure to select the data from EMP table based on user supplied DEPTNO.

CREATE PROCEDURE P2 @X INT

AS

BEGIN

SELECT * FROM EMP WHERE DEPTNO=@X

END

EXEC P2 20

Ø  Write a procedure to add two numbers

CREATE PROCEDURE P3 @A INT=10,@B INT=20

AS

BEGIN

DECLARE @C INT

SET @C=@A+@B

PRINT @C

END

EXEC P3

Output: 30

EXEC P3 25, 45

Output: 70

Note: Server will give highest priority to the user supplied values rather than default values.

User Defined Functions :( UDF’S)

• It is a database object
• Used to complete calculations in database
• It is like stored procedure after successful compilation it will stored into database as a pre
• compiled programming unit
• User’s gets reusability facility i.e. without compilation users can go for execution
• It contains only one parameter

In parameter: used to pass the values

1.       Through return statement it can send value to the user

2.       Udf’s can return single value with data type

Functions created by user are called user defined functions

Types of user defined functions:

1.    SCALAR VALUED FUNCTIONS

2.    TABLE VALUED FUNCTIONS

1. Scalar valued functions: These functions will return a scalar value to the calling environment

Syntax:

 CREATE FUNCTION < FUNCTION_NAME> (@PARA 1 DATA TYPE ,

@ PARA 2 DATATYPE…)

RETURNS <DATATYPE>

AS

BEGIN

DECLARE  @VARIABLE  DATATYPE

--------

----------

RETURN  @VARIABLE

END

Syntax: to execute the user defined function:

SELECT/PRINT DBO.FUNCTIONNAME (VALUE1, VALUE2…)

Note: The number of values supplied through PRINT/SELECT statement must be equal to the number parameters.

Ø  Write a function to find the product of two numbers

 CREATE FUNCTION F1 (@ A INT, @B INT)

 RETURNS INT

 AS

BEGIN

DECLARE @ C INT

SET @C = @A * @B

RETURN @C

END

SELECT/PRINT DBO.F1 (3,5)

Ø  Write function to find the net salary of an employee read EMPNO though parameter and display the net to return value

 CREATE FUNCTION F2 (@ VNO INT)

            RETURNS  INT

            AS

BEGIN

            DECLARE @ VSAL INT, @VCOM INT, @NET INT

            SELECT @VSAL =  SAL, @VCOM=COM

FROM EMP WHERE EMPNO =@VNO

IF @ VCOM IS NULL

BEGIN

PRINT ‘COMMISION IS NULL’

SET @NET = @VSAL

END

ELSE

BEGIN

SET @ NET = @VSAL + @VCOM

END

RETURN (@NET)

END

PRINT/SELECT DBO.F2(22)

2. Table valued function: These functions will return entire table to the calling environment.

Syntax:

CREATE FUNCTION <FUNCTION_NAME> (PARA 1 DATA TYPE ……….)

RETURNS TABLE

AS

BEGIN

<FUNCTION BODY>

RETURN (SELECT STATEMENT)

END

Ø  Write a function to return entire dept table

CREATE FUNCTION F3()

RETURNS TABLE

AS

BEGIN

RETURN (SELECT * FROM DEPT)

END

SELECT * FROM F3()

DEPT	DNAME	LOC

Ø   

CREATE FUNCTION F4()

RETURN TABLE

AS BEGIN

RETURN (SELECT ENAME, DNAME FROM EMP, DEPT

                      WHERE EMP.DEPTNO = DEPT.DEPTNO)

END

SELECT * FROM F4()

ENAME                          DNAME

SMITH               RESEARCH

MILLER              ACCOUNTING

Ø  Create function to convert datetime data into char

Default datetime format: 'yyyy-mm-dd HH: MI: SS: MS’

ü  2009-07-31 -- is it datetime data in sqlserver 2005?

             No

CREATE FUNCTION DateOnly (@InDateTime date time)

RETURNS varchar (10)

AS

BEGIN

            DECLARE @MyOutput varchar (10)

            SET @MyOutput = CONVERT (varchar (10), @InDateTime, 101)

            RETURN @MyOutput

END

Note: Convert is inbuilt function used to convert data from one data type to another

To call our function, execute: SELECT dbo.DateOnly (GETDATE ())

Drop:

Syntax: Drop function fun_name

TRIGGERS:

Two types of Triggers are there in SQLSERVER 2005, they are

1. DDL TRIGGERS: Can be created on database Servers and databases.

2. DML TRIGGERS: Can be created on Tables and Views.

DML Triggers: DML triggers respond to user INSERT, UPDATE, DELETE operations against a table or a view. When a data modification event occurs, the trigger performs a set of actions defined within the trigger. Similar to stored procedures, triggers are defined in Transact-SQL and allow a full range of activities to be performed.

A DML trigger can be defined specifically as FOR UPDATE, FOR INSERT, FOR DELETE, or any combination of the three. UPDATE triggers respond to modifications against one or more columns within the table, INSERT triggers respond to new data being added to the database, and DELETE triggers respond to data being deleted from the database.

There are two types of DML triggers: AFTER and INSTEAD OF.

AFTER triggers are only allowed for tables, and they execute after the data modification has been completed against the table.

INSTEAD OF triggers execute instead of the original data modification and can be created for both tables and views.

DML triggers allow you to perform actions in response to data modifications in a table.

For example, you can create a trigger that populates an audit table based on the operation performed, or perhaps use the trigger to decrement the value of a quantity. Although this ability to trigger actions automatically is a powerful feature, there are a few things to keep in mind before your use of triggers proliferates:

·         Triggers can often become a hidden and hence forgotten problem. When troubleshooting

·         Performance or logical issues, DBAs can forget that triggers are executing in the background.

·         Make sure that your use of triggers is “visible” in your data documentation.

·         If you can ensure that all your data modifications flow through a stored procedure, I would

·         Strongly recommend you perform all activities within the stored procedure, rather than use a

·         Trigger. For example, if you need to update a quantity in a related table, after inserting a sales

·         Record; why not put this logic in the stored procedure instead? The advantages are manageability

·         (One place to look) and supportability (one place to troubleshoot), when the Procedure needs modifications or performs unexpected actions.

·         Always keep performance in mind and this means writing triggers that execute quickly.

·         Long-running triggers can significantly slow down data modification operations. Take particular

·         Care in putting triggers into databases with frequent data modifications.

·         Non-logged updates do not cause a DML trigger to fire (for example WRITETEXT, TRUNCATE

·         TABLE and bulk insert operations).

·         Constraints usually run faster than a DML trigger, so if your business requirements can be

·         Fulfilled by a constraint, use constraints instead. AFTER triggers run after the data modification

·         Has already occurred, so they cannot be used to prevent a constraint violation.

·         Don’t allow result sets from a SELECT statement to be returned within your trigger. Most

·         Applications can’t consume these in an elegant fashion, and embedded queries can hurt the Trigger’s performance.

Parts of Trigger:

1.       Triggering Statement

2.       Triggering Restriction

3.       Triggering Action

Note: Triggering Action is different from remaining parts, because Trigger fires implicitly whenever an event occurs against to the Triggering Condition.

Things to Observe:

• When user creates an after Trigger on any table for INSERT operation then server arranges a temporary table “INSERTED” for holding recently inserted values.
• When user creates an after Trigger on any table for DELETE operation then server arranges a temporary table “DELETED” for holding recently deleted values.
• When user creates an after Trigger on any table for UPDATE operation then server arranges two temporary tables “INSERTED, DELETED” for holding old and new values of UPDATE operation.

Syntax of After Trigger:

CREATE TRIGGER TRIGGERNAME ON TABLE NAME

FOR/ AFTER {INSERT/ UPDATE/ DELETE}

 AS

BEGIN

 SQL STATEMENT

END

Syntax for Instead of Trigger:

Create trigger <Trigger_name> on <TN> or <View_name> instead of INSERT OR UPDATE OR DELETE

  As

 Begin

 Body of the program                                                 

 End                                                                                                    

Drop:

Syntax: Drop trigger <Trigger_name>

Retrieve list of Triggers on a Table:

Syntax: sp_helptrigger <TN>

Ex:  sp_helptrigger EMP

To view the SQL behind a specific trigger

Syntax: sp_helptext <Trigger_name>

Create a trigger for generate alert

Create trigger tri_emp on EMP after insert

As

Begin

Print 'one row inserted'

End

Result:

Insert into EMP (empno) values (1)

Create a trigger for copying data from Temp table to Original table

CREATE TRIGGER deleteorder_trigger ON orders

AFTER DELETE

AS

BEGIN

INSERT INTO orders_archive (order_id, order_date, cust_id)

SELECT order_id, order_date, cust_id FROM DELETED

End

Note:

1.    Create Orders, Orders_archive tables before running above program.

2.    Orders, Orders_archive tables contain same structure.

3.    DELETED is a temporary table

Ex:

CREATE TRIGGER vendor_trigger ON EMP

AFTER INSERT, UPDATE

AS

BEGIN

UPDATE EMP SET ename=Upper (ename)

WHERE empno IN (SELECT empno FROM INSERTED)

END

Instead of Trigger:

Ex:

CREATE TRIGGER T4 ON DEPT INSTEAD OF INSERT,UPDATE, DELETE

AS

BEGIN

PRINT ‘THESE OPERATIONS ARE NOT ALLOWED’

END

The above trigger fires automatically and shows a message THESE OPERATIONS ARE NOT ALLOWED, when user try to perform INSERT, UPDATE, DELETE operations on DEPT table.

DDL TRIGGERS (2005 Triggers): These triggers are fired when user performs DDL operations in the database and these triggers belong to database. It means we can define triggers on the current database.

Syntax:

CREATE TRIGGER TRIGGERNAME ON DATABASE

FOR/ AFTER {DROP,/ALTER/ CREATE}

 AS

BEGIN

 SQL STATEMENT.

END

1. USE PUBS

CREATE TRIGGER DROP_TRG ON DATABASE FOR DROP_TABLE

AS

BEGIN

PRINT ‘TABLE DROPPED’

END

2. USE PUBS

CREATE TRIGGER DROP_TRG ON DATABASE INSTEAD OF DROP_TABLE

AS

BEGIN

PRINT ‘U CANNOT DROP THE TABLE’

END

DIFFERENCE BETWEEN ORACLE & SQL Server:

ORACLE %TYPE data type	SQL Server No equivalent	DESCRIPTION The %TYPE data type of Oracle lets you create a variable and have that variable's data type be defined by a table or view column or a PL/SQL package variable. There is no equivalent for Oracle's %TYPE datatype in T-SQL, but it can be simulated (not very conveniently though) using User Defined Data types (UDT). Here is an example: EXEC sp_addtype 'MyType', 'smallint', NULL CREATE TABLE MyTable (i MyType) CREATE PROC MyProc AS BEGIN DECLARE @i MyType END
BEFORE triggers	INSTEAD OF triggers	Use INSTEAD OF trigger in SQL Server as an equivalent to Oracle's BEFORE trigger. For more information on INSTEAD OF triggers, see SQL Server Books Online
DECODE() function	CASE expression	DECODE can be conveniently simulated using the T-SQL CASE expression. Here's an example: SELECT Sport, CASE Sport WHEN 'Cricket' THEN 'England' WHEN 'Hockey' THEN 'India' WHEN 'Base Ball' THEN 'America' ELSE NULL END AS 'Originating Country' FROM Sports
DESCRIBE	sp_help or sp_columns	There are a lot of alternatives for Oracle's DESCRIBE, in SQL Server. You could use the system stored procedure sp_help for detailed information about a table's columns and other properties. If sp_help is providing you with too much information, then try the ODBC catalog stored procedure, sp_columns. There are a bunch of other useful sp_help* stored procedures available in SQL Server. You can find more information about those in SQL Server Books Online. If none of those procedures are suitable for your requirements, then you could query the system view INFORMATION_SCHEMA.COLUMNS, to get the desired information. You could wrap your code inside a stored procedure named DESCRIBE, if you wish. As a last resort, you could even query system tables like sysobjects and syscolumns, but this is not a recommended approach.
DUAL table	No equivalent	There is no DUAL table in SQL Server. In fact, you don't need one in SQL Server, as you can have a SELECT statement without a FROM clause. For example, consider the following SELECT statement in Oracle: SELECT 'Something' FROM DUAL In SQL Server, the same result can be obtained by the following command: SELECT 'Something' If you are porting some code from Oracle into SQL Server and if you don't want to remove all references to DUAL table, then just create a DUAL table in your database using the following commands: CREATE TABLE DUAL ( DUMMY varchar(1) ) INSERT INTO DUAL (DUMMY) VALUES ('X')
INTERSECT operator	Not supported	Use EXISTS clause to generate the same result. The following example illustrates the simulation of Oracle's INTERSECT operator: SELECT OrderID, OrderDate FROM Orders O WHERE EXISTS ( SELECT 1 FROM RefundsTable R WHERE O.OrderID = R.OrderID )
MINUS operator	Not supported	Use NOT EXISTS clause in your SELECT statement to generate the same result. The following example illustrates the simulation of Oracle's MINUS operator: SELECT OrderID, OrderDate FROM Orders O WHERE NOT EXISTS ( SELECT 1 FROM RefundsTable R WHERE O.OrderID = R.OrderID )
Nested tables	Not supported	Oracle 8i and prior versions didn't support this feature and is introduced in Oracle 9i. This feature basically enables you to store a table, within a column. It is like having an array of records in your database columns. SQL Server has no concept of nested tables. As a workaround, You could store your sub-tables or child tables in the form of XML inside a char, nchar, varchar, nvarchar, text or ntext type column, and parse it as needed, at runtime. See OPENXML, sp_xml_preparedocument, sp_xml_removedocument in SQL Server 2000 Books Online. Another possible workaround would be to store comma separated values (CSV). Note that this is against the basic rules of normalization. Columns are nomore atomic, with nested tables. From a design point of view, best thing to do would be, to create different tables for representing different entities and link them with primary and foreign key relationships. This will also make searching easier.
Packages	Not supported	No equivalent in SQL Server for Oracle's Packages and Package variables concept
PL/SQL	T-SQL PROGRAMMING	Every database product implements and extends the standard SQL. Oracle's implementation of SQL is called PL/SQL, while Microsoft's is called T-SQL   (Transact-SQL)
Row level security	No equivalent	Though there is no inbuilt support in SQL Server for row level permissions, you can implement it using view and system functions. For more information and a working example, read this article: Implementing row level permissions/security in SQL Server databases
rownum pseudo column	No equivalent	Though there is no rownum or rowid in SQL Server, there are several ways in which a row number can be generated. For some examples, read this article: Q186133 INF: How to Dynamically Number Rows in a Select Statement
SELECT...FOR UPDATE	UPDLOCK hint	Use the locking hint UPDLOCK in your SELECT statement. See SQL Server Books Online for more information.
Sequences	IDENTITY	It is much simpler in SQL Server to generate a sequence value for a primary key or a non-key column. You just need to turn on the IDENTITY property of column. IDENTITY property can be set to columns of the following data types: tinyint, smallint, int, bigint, decimal, numeric Try this example to see how it works: CREATE TABLE foo ( i int IDENTITY(1, 1) j int ) INSERT INTO foo (j) VALUES (911) INSERT INTO foo (j) VALUES (999)
SQL *Plus	Query Analyzer/ SSMS	For connecting to SQL Server and executing queries and modifying data, use the built-in Query Analyzer. It is much more powerful and friendlier than Oracle's SQL *Plus
START WITH...CONNECT BY clause	No equivalent	Though there's no direct equivalent in T-SQL for Oracle's START WITH...CONNECT BY, there are several ways and efficient techniques for processing and querying hierarcical data. For more information, read this article: Working with hierarchical data in SQL Server databases
Synonyms	Views	You can simulate Oracle Synonyms in SQL Server using Views. For example, the following creates a view that returns the OrderID and OrderDate from Orders table. CREATE VIEW vOrders AS SELECT OrderID, OrderDate FROM Orders Now you can select data from the view, using the following SELECT statement: SELECT * FROM vOrders The following example will create a view which is equivalent to Oracles TABS Synonyms or USER_TABLES data dictionary view (For simplicity, I am only including the table name in the view definition): CREATE VIEW TABS AS SELECT name AS table_name FROM sysobjects WHERE type = 'U' Now you can execute the following SELECT statement to return all user table names: SELECT table_name FROM TABS

T-SQL Enhancements in 2008:

Every new version of SQL Server comes with several T-SQL features and enhancements.

SQL Server 2008 is no exception. In the newest version of SQL Server, Microsoft has responded to developer requests in a big way. SQL Server 2008 supports brand new developer convenience features like single statement declaration and initialization, new data types to manipulate and query date-based and spatial data, new statements like MERGE that add much needed functionality to T-SQL and a whole lot more. Once you’ve made the move to SQL Server 2008, you’ll undoubtedly want to take advantage of the new features and functionality to solve common problems.

1.Separate Variable Declaration and Initialization

The first of these new features is single statement variable declaration and initialization. In the past, developers had to declare new variables in one or more DECLARE statements and assign values to the variables in one or more SET or SELECT statements as shown in below Listing.

DECLARE @x int, @y int, @z int

SET @x=1

SET @y=2

SET @z=3

With single statement declaration and initialization, you can combine these two steps into a single step as shown in below Listing

DECLARE @x int =1,@y int=2, @z int =3

2.Calculating and Assigning Values with C-Style Assignment

T-SQL has further improved with the addition of several C-Style assignment operators.

Operator	Description
+=	Addition with assignment, or string concatenation with assignment
-=	Subtraction with assignment
*=	Multiplication with assignment
/=	Division with assignment
%=	Modulo with assignment

Ex:

DECLARE @x int=4, @y int=25, @s1 varchar(20)=’Sql’

SET @x *= @y

SET @s1+= ‘SERVER’

SELECT @x, @s1

3.NEW INSERT STATEMENT

SYNTAX:

INSERT INTO TABLE_NAME

VALUES

(VALUE1, VALUE2,……),

(VALUE1, VALUE2,…..),

(VALUE1,VALU2,……..)

EX:

INSERT INTO EMP

VALUES

(11,’RAM’, 30000, 10),

(22,’RAJ’, 20000, 20)

Like in SQL Server 2005 we need not to write multiple INSERT statements for multiple records. Within single INSERT statement we can insert multiple records into the table at a time

4. MERGE Statement:

It is one of the DML statement placed newly in SQL Server 2008

Merge Statement is a combination statement that can perform INSERT, UPDATE, DELETE statements based on whether rows that match the selection criteria exist in the target table or not.

This Merge statement takes Target and Source Tables, When Source rows are matched with Target it will update the rows of source table into target table otherwise it will insert the rows of source table into target table. Always merge statement takes a temp table as its source and base table as its target table. Using this Merge statement we can combine the rows of a source table to target table.

Syntax:

MERGE Target_Table as target

USING Source_table as source

ON condition

WHEN MATCHED THEN

UPDATE

SET column=source.column, ………

WHEN TARGET NOT MATCHED THEN

INSERT VALUES (source.column1, source.column2,)

4.New Data Types In 2008

SQL Server 2008 provides new data types that support storage, manipulation and querying of new forms of data. Some, like the date data type which stores a simple date without the time component. Others like the geometry data type, which allows storage and querying of spatial data, have only recently addressed.

Date and Time Data Types

SQL Server supports four brand new date and time data types, which includes

1. date
2. time
3. datetime2
4. datetimeoffset

The date data type finally allows us to store date only without the time component. It can also store a much larger range of dates than datetime and smalldatetime data types.

The date data type can handle dates from January 1, 1 CE (0001-01-01) to December 31, 9999 CE (9999-12-31)

Ex:

DECLARE @d1 date=’0014-08-19’

DECLARE @d2 date=’1983-02-26’

SELECT @d1 as DATE1,@d2 as DATE2

DATEDIFF (YEAR, @d1, @d2) as YEARDIFFERENCE

In contrast to the date data type, the time data type lets you to store time only data.

The range for the time data type is defined on 24 hour clock, from 00:00:00.0000000 through 23:59:59.9999999 with user defined fractional second precision of up to seven digits.

Ex:

DECLARE @start_time time (1) =’06:25:19.1’ --1 digit fractional precision

DECLARE @end_time time=’18:25:19.1234567’ --default fractional precision

SELECT @start_time as STARTTIME, @end_time as ENDTIME

DATEADD (HOUR, 6, @start_time) as STARTTIMEPLUS,

DATEDIFF (HOUR,@start_time,@end_time) as ENDSTARTDIFF

The cleverly named datetime2 data type is an extension to the standard datetime data type. The datetime2 combines the benefits of the new date and time data types, giving you the wide date range of the date data type and the greater fractional second precision of the time data type.

Ex:

DECLARE @start_dt2 datetime2=’1972-07-06T07:13:28.8235670’

DECLARE @end_dt2 datetime2=’2009-12-14T03:14:13.2349872’

SELECT @start_dt2 as start_dt2, @end_dt2 as end_dt2

The new datetimeoffset data type builds on datetime2 by adding the ability to store offsets relative to the International Telecommunication Union (ITU) standard for Coordinated Universal Time(UTC) with your date and time data type.

5. The Hierarchyid Data Type

The hierarchyid data type offers to represent hierarchical data in the database. This new data type offers built-in support for representing your hierarchical data using one of the simplest models available: materialized paths

Hierarchyid Data Type Methods

Method	Description
GetAncestor(n)	Retrieves the nth ancestor of the hierarchyid node instance
GetDescendent(n)	Retrieves the nth descendent of the hierarchyid node instance
GetLevel()	Gets the level of the hierarchyid node instance in the hierarchy
GetRoot()	Gets the root node of the hierarchyid  instance in the hierarchy
IsDescendent(node)	Returns 1 if a specified node is descendent
Parse(String)	Converts the given canonical string, in forward slash- separated format to a hierarchyid path.
Reparent(old_root,new_root)	Reparents a node by moving nodes from old_root to new_root
ToString()	Converts a hierarchyid instance to a canonical forward slash-separated string representation

5.The Spatial Data Type

SQL Server 2008 includes two new data types for storing, querying, and manipulating spatial data.

·         Geometry

·         Geography

Geometry data type is designed to represent flat-earth, or Euclidean, spatial data.

Geography data type supports round earth or ellipsoidal, spatial data.