DATA MANIPULATION LANGUAGE

DATA MANIPULATION LANGUAGE: This sub language concentrates on the values of a specific table. It includes the following statements.

1. INSERT Statement
2. SELECT Statement
3. UPDATE Statement
4. DELETE Statement

1. INSERT Statement: This statement is used for inserting the values into a specific table.

Syntax to INSERT Statement:

 INSERT INTO TABLENAME [(columns list)] VALUES (VALUE1, VALUE2 …)

NOTE: While inserting the values into a specific table we should know that table definition (number of columns).

In the above syntax “columns list” optional part specifies that “List of columns for which user supplying the values”.

E.g1. INSERT INTO EMP VALUES (11, ‘RAM’, 15000, 10)

E.g2. INSERT INTO EMP VALUES (22, ‘RAJ’, 5000, 20)

E.g3. INSERT INTO EMP VALUES (33, ‘ANIL’, 10000, 10)

E.g4. INSERT INTO EMP VALUES (44, ‘ABIRAM’, 150000, 20)

E.g5. INSERT INTO EMP (EMPNO, ENAME, DEPTNO)

VALUES (55, ‘DP’, 10)

E.g6. INSERT INTO EMP (EMPNO, ENAME,SAL)

VALUES (66, ‘INDU’, 12000)

In the above example 5 user was unable to supply the value for SAL column, then user have to mention the columns list for which he can supply the values.

In the above example 6 user was unable to supply the value for DEPTNO column, then user have to mention the columns list for which he can supply the values.

Note: Whenever user unable to supply the values for any column then server will arrange an unpredictable or garbage value called NULL value. Null is different from zero and empty. We cannot compare null with any other values.

In the above E.g5 and E.g6 case SAL and DEPTNO column value will be given as  NULL.

2. SELECT Statement: This statement is used for retrieving the data from a specific table. It is also known as Data Retrieval Statement.

Syntax:

SELECT {*/ columns list} FROM TABLENAME

In the above syntax the symbol ‘* ‘displays all columns and their corresponding rows and the ‘columns list’ displays specific columns and their corresponding rows.

E.g.: SELECT * FROM EMP

The above statement displays all columns and their corresponding rows from EMP table it means whole EMP table will be displayed

Ex1: DISPLAYING THE DATA IN THE EMP TABLE:

SELECT * FROM EMP

EMPNO	ENAME	SAL	DEPTNO
11	RAM	15000	10
22	RAJ	5000	20
33	ANIL	10000	10
44	ABIRAM	15000	20
55	DP	NULL	10
66	INDU	12000	NULL

E.g.: SELECT EMPNO, ENAME FROM EMP

The above statement displays only EMPNO, ENAME columns and their rows from EMP table.

E.g.: SELECT SAL, DEPTNO FROM EMP

The above statement displays only SAL, DEPTNO columns and their rows from EMP table.

OPERATORS:

                              Arithmetic Operators: +, -, *, /, %

                              Relational Operators: <, >, <=, >=, =,! =,! <,! >

                              Logical Operators: AND, OR, NOT

Truth table for AND      Truth table for OR   Truth table for NOT

C1         C2           R         C1       C2       R         C         R

T            T              T          T          T          T          T          F

T            F              F          T          F          T          F          T         

F            T              F          F          T          T                                 

F            F              F          F          F          F

*C1: Condition 1

*C2: Condition 2

*R: Result

WHERE CLAUSE: This clause used for placing  a condition on a specific column of a specific table. It is associated with SELECT, UPDATE, DELETE statements.

Syntax:

SELECT {*/Columns list} FROM TABLENAME [WHERE Condition]

E.g.: Write a Query to select employ details who are working under 10^th department

SELECT * FROM EMP WHERE DEPTNO=10

E.g.: Write a Query to select employ details who are earning salaries between 5000 and 25000

SELECT * FROM EMP WHERE SAL>5000 AND SAL<25000

E.g.: Write a Query to select employ details whose employ number is 22

SELECT * FROM EMP WHERE EMPNO=22

E.g.: Write a Query to select employ details whose department is null

SELECT * FROM EMP WHERE DEPTNO IS NULL

Note: In the above example we used a special operator called IS operator , which used to compare NULL values.

3. UPDATE Statement: Update statement in SQL Server is used for modifying the data, which is available in a specific table.

Syntax:

UPDATE TABLENAME SET COLUMNNAME =NEWVALUE

                                                                       [, COLUMNNAME= NEWVALUE….]

E.g.: Write a Query to modify (increase) the salaries of all the employees in EMP table

UPDATE EMP SET SAL=SAL+1000

The above statement (modifies) increases all employees salaries by 10000

This type of updating operation is called HIGH LEVEL UPDATE operation.

E.g.: Write a Query to modify (increase) the salaries of all employees who are working under 10^th department.

UPDATE EMP SET SAL=SAL+500 WHERE DEPTNO=10

Write a Query to modify the salary of an employ whose employ number 11 and who is working under 20^th department

UPDATE EMP SET SAL= SAL+300 WHERE EMPNO=11 AND DEPTNO=20

CASE Statement: In SQL Server CASE statement is used for evaluating multiple conditions on a specific column. It is mostly associated with UPDATE statement.

Syntax: UPDATE TABLENAME SET COLUMN NAME= CASE

             WHEN CONDITION1 THEN RESULT1

             WHEN CONDITION2 THEN RESULT2

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

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

            WHEN CONDITIONN THEN RESULTN

            [ELSE RESULT]

            END

E.g.: Write Query to arrange BONUS column values according to the following specification

            SAL                            BONUS

<=5000                        1000

>5000 and <=10000               2000

            >10000                        3000

            UPDATE EMP SET BONUS=CASE

            WHEN SAL<=5000 THEN 1000

            WHEN SAL>5000 AND SAL<=10000 THEN 2000

            WHEN SAL>10000 THEN 3000 (OR) ELSE 3000

END

The above statement arranges BONUS column values according to the salaries.

4. DELETE Statement: Delete statement is used to delete the data from a specific table in ROW-BY-ROW (one by one) manner with out disturbing its structure (columns).

Syntax:

DELETE FROM TABLE_NAME [WHERE (CONDITION)]

E.g.: DELETE FROM EMP

The statement deletes all records from EMP table with out disturbing its structure (columns). This is called high level deletion

E.g.: Write a Query to delete all employees who are working under 10^th department

DELETE FROM EMP WHERE DEPTNO=10

E.g.: Write a Query to delete all employ who is working under 20^th department and employ number 33

DELETE FROM EMP WHERE DEPTNO=20 AND EMPNO=33

veticD � f ( �� � ,1)) = "c" Then

cnt = cnt+1

End If

Next

msgbox cnt

13 Replace space with tab in between the words of a string.

str = "Replace space with tab in between the words of a string"

replstr = "       "

Set oregexp = new regexp

With oregexp

.pattern = " "

.global = true

.ignorecase = true

End With

newstring = oregexp.replace(str,replstr)

msgbox newstring

14 Write a program to return ASCII value of a given character

msgbox asc("A")
15 Write a program to return character corresponding to the given ASCII value

msgbox Chr(65)
16 Convert string to Upper Case

msgbox ucase(“string”)

17 Convert string to lower case

Msgbox lcase(“string”)
18 Write a program to Replace a word in a string with another word

str = "Replace space with tab in between the words of a string"

replstr = "strings"

Set oregexp = new regexp

With oregexp

.pattern = "string"

.global = true

.ignorecase = true

End With

newstring = oregexp.replace(str,replstr)

msgbox newstring
19 Check whether the string is a POLYNDROM
20 Verify whether given two strings are equal

str1 = "the"

str2 = "the"

msgbox strcomp(str1,str2)
if result is 0 then equal

21 Print all values from an Array

a =array("a","b","c")

For each s in a

msgbox s

Next

22 Sort Array elements
23 Add two 3X3 matrices
24 Multiply Two Matrices of size 2X2
25 Convert a String in to an array
26 Convert a String in to an array using ‘I’ as delimiter
27 Find number of words in string

str = "count how many words in a string"

s = split(str," ")

'For i = 0 to ubound(s)

'msgbox s(i)

'Next

'msgbox i

msgbox ubound(s)+1

28 Write a program to reverse the words of a given string.

str = "count how many words in a string"

s = split(str," ")

For i = ubound(s) to 0 step -1

revstring = revstring&" "&s(i)

Next

msgbox revstring

29 Print the data as a Pascal triangle
30 Join elements of an array as a string

31 Trim a given string from both sides
32 Write a program to insert 100values and to delete 50 values from an array

dim a(100)

For i = 1 to 100

a(i) = i

If a(i) = 51 Then

            Exit for

            Erase a(i)

End If

print a(i)

Next

33 Write a program to force the declaration of variables
34 Write a program to raise an error and print the error number. 
35 Finding whether a variable is an Array

Isarray(a) = true ‘if the variable is really an array’

Isarray(a) = false ‘if the variable is not an array’

36 Write a program to Convert value into a currency
37 Write a program to Convert an expression to a date
38 Display current date and Time
39 Find difference between two dates.
40 Add time interval to a date
41 Print current day of the week
42 Convert Date from Indian Format to US format
43 Find whether current month is a long month
44 Find whether given year is a leap year
45 Format Number to specified decimal places
46 Write a program to Generate a Random Number
47 Write a program to show difference between Fix and Int 
48 Write a program to find subtype of a variable
49 Write a program to print the decimal part of a given number
50 Write a Function to return a random number
51 Write a Function to add and multiply two numbers
52 Write a Sub Procedure to print “Hello World”
53 Write a Sub Procedure to add and multiply two numbers
54 Write a program to list the Timezone offset from GMT

55 what is the difference between ByVal and ByVal

Function getval(byval x, byref y)

   x = x+1

   y = y+1

print "x="&x&"           "&"y="&y

End Function

Dim a: a = 5

dim b:  b = 5

call getval( a, b )

print "a="&a&"           "&"b="&b