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
No comments:
Post a Comment