1 / 9

SQL: DDL and DML

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

marshall-george
Télécharger la présentation

SQL: DDL and DML

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL: DDL and DML DDL Data Definition Language DML Data Manipulation Language

  2. DDL • Create • Drop • Alter • Grant • Revoke • No Commit is needed.

  3. 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;

  4. DDL Access Control Grant Select (Fname, Lname) on Staff to public; Grant Insert, Update (Fname, Lname) on Staff to CS363;

  5. 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));

  6. DML Insert (commit) Update (commit) Delete (commit) Select (no commit)

  7. 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

  8. DML Update Staff Set Salary = Salary * 1.05 Where Position != 'Manager' and Bno = 'B363'; Use Where clause to specify records.

  9. DML Delete From Staff Where Sno = '1234'; Delete From Viewing Where VDate <= '31-Dec-1990'; Delete From Viewing; -- delete all records, keep the table

More Related