SQL DML ,
DCL,
TCL
DML DCL TCL
SELECT GRANT COMMIT
INSERT REVOKE SAVEPOINT
UPDATE ROLLBACK
DELETE
SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM Name_of_table;
SELECT * FROM Name_of_table;
SELECT * FROM Student;
Student_ID Student_name Student_marks
1 SAKSHI 95
2 MUSKAN 97
3 MOHIT 93
4 SHREYA 96
SELECT Emp_Id, Emp_Salary FROM Employee;
Emp_Id Emp_Salary
101 25000
102 32000
103 67000
104 21000
105 46000
Student_Id Student_Name Student_Marks
1 Abc 70
2 Bcd 89
3 Pqr 76
4 Xyz 69
SELECT * FROM Student WHERE Student_Marks >80;
Student_Id Student_Name Student_Marks
2 Bcd 89
COUNT() FUNCTION: The COUNT() function returns the number of rows that
matches a specified criterion.
Syntax:
SELECT COUNT(column_name) FROM table_name WHERE condition;
SELECT COUNT(column_name) FROM table_name WHERE condition;
SUM () FUNCTION: The SUM() function returns the total sum of a numeric
column.
Syntax:
SELECT SUM(column_name) FROM table_name WHERE condition;
AVG () FUNCTIONS: The AVG() function returns the average value of a numeric
column.
Syntax:
SELECT AVG(column_name) FROM table_name WHERE condition;
MIN () FUNCTION: The MIN() function returns the smallest value of the selected column.
Syntax:
SELECT MIN(column_name) FROM table_name WHERE condition;
MAX () FUNCTION: The MAX() function returns the largest value of the selected column.
Syntax:
SELECT MAX(column_name) FROM table_name WHERE condition;
STDEV() FUNCTION: The STDEV() function is used to calculate the Standard Deviation of
total records (or rows) selected by the SELECT Statement.
Syntax:
SELECT STDEV(column_name) FROM table_name;
VARIANCE() FUNCTION: It returns the population standard variance of all the fields in a
particular column.
Syntax:
SELECT VARIANCE(column_name) FROM table_name;
INSERT INTO TABLE_NAME(col_Name1,col_Name2,..col_NameN)VALUES(val_1,val_2,..val_N
)
Stu_Id Stu_Name Stu_Marks Stu_Age
1 Abc 94 16
2 Xyz 89 17
INSERT INTO Student(Stu_Id , Stu_Name , Stu_Marks , Stu_Age) VALUES(3,Pqr,86,15) ;
Stu_Id Stu_Name Stu_Marks Stu_Age
1 Abc 94 16
2 Xyz 89 17
3 Pqr 86 15
UPDATE Table_name SET [col_name1= val_1, ….., col_nameN = val_N] WHERE CONDITION;
Stu_Id Stu_Name Stu_Age Stu_Marks
1 Muskan 21 45
2 Shreya 23 43
3 Asmita 24 48
4 Sudha 23 42
UPDATE Student SET [Stu_Age=22,Stu_Marks=46] WHERE Stu_Id=1 AND Stu_Id=4;
Stu_Id Stu_Name Stu_Age Stu_Marks
1 Muskan 22 46
2 Shreya 23 43
3 Asmita 24 48
4 Sudha 22 46
DELETE FROM Table_Name WHERE condition;
Stu_Id Stu_Name Stu_Age Stu_Marks
1 Muskan 21 45
2 Shreya 23 43
3 Asmita 24 48
4 Sudha 23 42
DELETE FROM Student WHERE Stu_Age<22;
Stu_Id Stu_Name Stu_Age Stu_Marks
2 Shreya 23 43
3 Asmita 24 48
4 Sudha 23 42
GRANT privilege_name ON object_name TO {user_name|public|role_name}
Here privilege_name is which permission has to be granted, object_name is the name
of the database object, user_name is the user to which access should be provided, the
public is used to permit access to all the users.
GRANT CREATE SESSION TO name_of_user;
D
GRANT sysdba TO name_of_user;
D
REVOKE privilege_name ON object_name FROM {user_name|public|role_name}
REVOKE INSERT,SELECT ON Accounts FROM Ram;
By the above command user Ram’s permissions like query or insert on accounts
database object has been removed.
COMMIT;
Stu_Id Stu_Name Stu_Age
1 Sakshi 22
2 Shreya 23
INSERT INTO Student(Stu_Id , Stu_Name , Stu_Age) VALUES(3,Muskan,22) ;
COMMIT;
SAVEPOINT savepoint_name;
Stu_Id Stu_Name Stu_Age
1 Mohit 23
2 Sudha 24
UPDATE Student SET Stu_Age=22 WHERE Stu_Id=1 ;
SAVEPOINT upd ;
ROLLBACK TO savepoint_name ;
Stu_Id Stu_Name Stu_Marks
1 Muskan 98
2 Shreya 96
SAVEPOINT ins ;
ROLLBACK TO ins;