150 likes | 423 Views
SQL: DDL and DML. DDL Data Definition Language DML Data Manipulation Language. DDL. Create Drop Alter Grant Revoke No Commit is needed. DDL. Three Standard User Roles Connect Resource DBA Create User CS363 identified by database; Grant Connect to cs363; -- End Users
E N D
SQL: DDL and DML DDL Data Definition Language DML Data Manipulation Language
DDL • Create • Drop • Alter • Grant • Revoke • No Commit is needed.
DDL Three Standard User Roles Connect Resource DBA Create User CS363 identified by database; Grant Connect to cs363; -- End Users -- Cannot create tables -- Need other rights to be granted Grant Resource to cs363; -- Create tables and other objects -- All students are granted the role Grant DBA to cs363; Revoke DBA from CS363;
DDL Access Control Grant Select (Fname, Lname) on Staff to public; Grant Insert, Update (Fname, Lname) on Staff to CS363;
DDL Create Table Staff ( Sno Char(4) Primary Key, Fname Varchar2(20) not null, ... ); Alter Table Staff Modify (Fname Varchar2(25) not null) Add (Email Varchar2(20));
DML Insert (commit) Update (commit) Delete (commit) Select (no commit)
DML Update Staff Set Bno = 'B123'; Update Staff Set Salary = Salary * 1.05; Update Staff Set Bno = 'B123', Salary = Salary * 1.05; All records are updated
DML Update Staff Set Salary = Salary * 1.05 Where Position != 'Manager' and Bno = 'B363'; Use Where clause to specify records.
DML Delete From Staff Where Sno = '1234'; Delete From Viewing Where VDate <= '31-Dec-1990'; Delete From Viewing; -- delete all records, keep the table