DML and DCL Commands in RDBMS
AIM:
To execute and verify the DML and TCL Language commands
DML (DATA MANIPULATION LANGUAGE)
v SELECT
v INSERT
v DELETE
v UPDATE
TCL (TRANSACTION CONTROL LANGUAGE)
v COMMIT
v ROLL BACK
v SAVE POINT
PROCEDURE
STEP 1: Start
STEP 2: Create the table with its essential attributes.
STEP 3: Insert the record into table
STEP 4: Update the existing records into the table
STEP 5: Delete the records in to the table
STEP 6: use save point if any changes occur in any portion of the record to undo its original state.
STEP 7: use rollback for completely undo the records
STEP 6: use commit for permanently save the records.
SQL COMMANDS
1. COMMAND NAME: INSERT
COMMAND DESCRIPTION: INSERT command is used to Insert objects
in the database.
2. COMMAND NAME: SELECT
COMMAND DESCRIPTION: SELECT command is used to SELECT the object from the database.
3. COMMAND NAME: UPDATE
COMMAND DESCRIPTION: UPDATE command is used to UPDATE
the records from the table
4. COMMAND NAME: DELETE
COMMAND DESCRIPTION: DELETE command is used to DELETE the
Records form the table
5. COMMAND NAME: COMMIT
COMMAND DESCRIPTION: COMMIT command is used to save the
Records.
6. COMMAND NAME: ROLLBACK
COMMAND DESCRIPTION: ROLL BACK command is used to undo the
Records.
6. COMMAND NAME: SAVE POINT
COMMAND DESCRIPTION: SAVE POINT command is used to undo the
Records in a particular transaction.
INSERT
QUERY: 01
Q1. Write a query to insert the records in to employee.
Syntax for Insert Records in to a table:
SQL :> INSERT INTO <TABLE NAME> VALUES< VAL1, ‘VAL2’,…..);
QUERY: 01
INSERT A RECORD FROM AN EXISTING TABLE:
SQL>INSERT INTO EMP VALUES(101,'NAGARAJAN','LECTURER',15000);
1 row created.
SELECT
QUERY: 02
Q3. Write a query to display the records from employee.
Syntax for select Records from the table:
SQL> SELECT * FROM <TABLE NAME>;
QUERY: 02
DISPLAY THE EMP TABLE:
SQL> SELECT * FROM EMP;
EMPNO ENAME DESIGNATIN SALARY
---------- ------------ ---------- ----------
101 NAGARAJAN LECTURER 15000
INSERT A RECORD USING SUBSITUTION METHOD
QUERY: 03
Q3. Write a query to insert the records in to employee using substitution method.
Syntax for Insert Records into the table:
SQL :> INSERT INTO <TABLE NAME> VALUES< ‘&column name’, ‘&column name 2’,…..);
QUERY: 03
SQL> INSERT INTO EMP VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY');
Enter value for empno: 102
Enter value for ename: SARAVANAN
Enter value for designatin: LECTURER
Enter value for salary: 15000
old 1: INSERT INTO EMP VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY')
new 1: INSERT INTO EMP VALUES(102,'SARAVANAN','LECTURER','15000')
1 row created.
SQL> /
Enter value for empno: 103
Enter value for ename: PANNERSELVAM
Enter value for designatin: ASST. PROF
Enter value for salary: 20000
old 1: INSERT INTO EMP VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY')
new 1: INSERT INTO EMP VALUES(103,'PANNERSELVAM','ASST. PROF','20000')
1 row created.
SQL> /
Enter value for empno: 104
Enter value for ename: CHINNI
Enter value for designatin: HOD, PROF
Enter value for salary: 45000
old 1: INSERT INTO EMP VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY')
new 1: INSERT INTO EMP VALUES(104,'CHINNI','HOD, PROF','45000')
1 row created.
SQL> SELECT * FROM EMP;
EMPNO ENAME DESIGNATIN SALARY
---------- ------------ ---------- ----------
101 NAGARAJAN LECTURER 15000
102 SARAVANAN LECTURER 15000
103 PANNERSELVAM ASST. PROF 20000
104 CHINNI HOD, PROF 45000
UPDATE
QUERY: 04
Q1. Write a query to update the records from employee.
Syntax for update Records from the table:
SQL> UPDATE <<TABLE NAME> SET <COLUMNANE>=<VALUE> WHERE <COLUMN NAME=<VALUE>;
QUERY: 04
SQL> UPDATE EMP SET SALARY=16000 WHERE EMPNO=101;
1 row updated.
SQL> SELECT * FROM EMP;
EMPNO ENAME DESIGNATIN SALARY
---------- ------------ ---------- ----------
101 NAGARAJAN LECTURER 16000
102 SARAVANAN LECTURER 15000
103 PANNERSELVAM ASST. PROF 20000
104 CHINNI HOD, PROF 45000
UPDATE MULTIPLE COLUMNS
QUERY: 05
Q5. Write a query to update multiple records from employee.
Syntax for update multiple Records from the table:
SQL> UPDATE <<TABLE NAME> SET <COLUMNANE>=<VALUE> WHERE <COLUMN NAME=<VALUE>;
QUERY: 05
SQL>UPDATE EMP SET SALARY = 16000, DESIGNATIN='ASST. PROF' WHERE EMPNO=102;
1 row updated.
SQL> SELECT * FROM EMP;
EMPNO ENAME DESIGNATIN SALARY
---------- ------------ ---------- ----------
101 NAGARAJAN LECTURER 16000
102 SARAVANAN ASST. PROF 16000
103 PANNERSELVAM ASST. PROF 20000
104 CHINNI HOD, PROF 45000
DELETE
QUERY: 06
Q5. Write a query to delete records from employee.
Syntax for delete Records from the table:
SQL> DELETE <TABLE NAME> WHERE <COLUMN NAME>=<VALUE>;
QUERY: 06
SQL> DELETE EMP WHERE EMPNO=103;
1 row deleted.
SQL> SELECT * FROM EMP;
EMPNO ENAME DESIGNATIN SALARY
---------- ------------ ---------- ----------
101 NAGARAJAN LECTURER 16000
102 SARAVANAN ASST. PROF 16000
104 CHINNI HOD, PROF 45000
******************************************************************************
Subscribe by Email
Follow Updates Articles from This Blog via Email
No Comments