DATA
MANIPULATION LANGUAGE: This sub language concentrates on the values of a specific
table. It includes the following statements.
- INSERT
Statement
- SELECT
Statement
- UPDATE
Statement
- DELETE
Statement
- 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 10th
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 10th 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 20th 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 10th department
DELETE FROM EMP WHERE DEPTNO=10
E.g.: Write a Query to delete all employ who is working under
20th department and employ number 33
DELETE FROM EMP WHERE DEPTNO=20 AND EMPNO=33
Step3: Add your required constraints to columns
Syntax:
ALTER
TABLE TABLENAME ADD CONSTRAINTTYPE (COLUMN1),
CONSTRAINTTYPE (COLUMN2),………..
E.g.:
ALTER
TABLE DEPT ADD PRIMARY KEY (DEPTNO),
UNIQUE (DNAME),
DEFAULT
‘HYD’ FOR LOC
E.g.:
ALTER
TABLE EMP ADD PRIMARY KEY (EMPNO),
CHECK (SAL>=1000),
FOREIGN
KEY (DEPTNO) REFERENCES DEPT (DEPTNO)
Adding
constraints for the existing table with constraint names:
Step1 and Step2 are common here also
Step3: Add your required constraints to columns
Syntax:
ALTER
TABLE TABLENAME ADD CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE (COLUMN1),
CONSTRAINT CONSTRAINTNAME CONSTRAINTTYPE (COLUMN2),………………..
E.g.:
CONSTRAINT UQ UNIQUE (DNAME),
CONSTRAINT
DF DEFAULT ‘HYD’ FOR LOC
E.g.:
ALTER
TABLE EMP ADD CONSTRAINT PRK PRIMARY KEY (EMPNO),
CONSTRAINT CK CHECK
(SAL>=1000),
CONSTRAINT FK FOREIGN KEY (DEPTNO)
REFERENCES DEPT (DEPTNO)
Dropping The Constraints:
Syntax:
ALTER TABLE TABLENAME DROP CONSTRAINT
CONSTRAINTNAME
E.g.: ALTER TABLE DEPT DROP CONSTRAINT PK
E.g.: ALTER TABLE EMP DROP CONSTRAINT PRK
Note: If you create any table with out any
constraint names then server will arrange the constraint names in its own
format. Those constraint names will be displayed when you execute a stored
procedure SP_HELPCONSTRAINT Table_Name.
SP_HELPCONSTRAINT:
This Stored Procedure is used to display
the description of constraints which have been placed on different columns of a
specific table.
Syntax:
SP_HELPCONSTRAINT Table-Name
Ex:
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
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
No comments:
Post a Comment