Scribd
Upload
6 views11 pages

Experiment 4 RDBMS

The document outlines the use of Data Control Language (DCL) and Transaction Control Language (TCL) commands in SQL, including object creation, privilege management, and transaction processing. It provides examples for creating tables, views, users, and sequences, as well as commands for granting and revoking privileges, committing, rolling back, and setting savepoints in transactions. Additionally, it mentions the creation of collections in Oracle's PL/SQL.

Uploaded by

deaddguruu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views11 pages

Experiment 4 RDBMS

The document outlines the use of Data Control Language (DCL) and Transaction Control Language (TCL) commands in SQL, including object creation, privilege management, and transaction processing. It provides examples for creating tables, views, users, and sequences, as well as commands for granting and revoking privileges, committing, rolling back, and setting savepoints in transactions. Additionally, it mentions the creation of collections in Oracle's PL/SQL.

Uploaded by

deaddguruu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Experiment 4

DATA CONTROL LANGUAGE (DCL) and TRANSATIONAL CONTROL


LANGUAGE (TCL) commands.
 Creating objects: tables, views, users, sequences, Collections etc.
 Privilege management through the Grant and Revoke commands
 Transaction processing using Commit, Rollback and Save points.

Object Creation:

Tables:

CREATE TABLE employees (employee_id INT PRIMARY KEY, first_name


VARCHAR(50), last_name VARCHAR(50), salary DECIMAL(10, 2) );

Views:

CREATE VIEW high_salary_employee AS SELECT * FROM employee WHERE


salary > 60000;
Users:

Syntax
CREATE USER user1 IDENTIFIED BY 'password';

Data Control Language (DCL) Commands:

GRANT:

The GRANT command is used to provide specific privileges to a user or a role.


Privileges can include the ability to perform actions such as SELECT, INSERT,
UPDATE, DELETE on tables, or execute stored procedures.

Example:

GRANT SELECT, INSERT ON employees TO user1;


View the priviledge of particular user;

REVOKE:

The REVOKE command is used to remove previously granted privileges from a


user or a role.

Example:

REVOKE SELECT ON employees FROM user1;


Sequences:

CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;


Transaction Control Language (TCL) Commands:

COMMIT:

The COMMIT command is used to permanently save the changes made during
the current transaction.

Example:

-- Start a transaction BEGIN;


-- Make changes to the database
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
-- Commit the changes COMMIT;

ROLLBACK:

The ROLLBACK command is used to undo the changes made during the current
transaction. It is typically used when an error occurs or when you want to discard
the changes for any reason.

Example:

-- Start a transaction BEGIN;


-- Make changes to the database
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
-- Something went wrong, rollback the changes
ROLLBACK;
SAVEPOINT:

The SAVEPOINT command is used to set a savepoint within a transaction.


Savepoints allow you to later roll back to a specific point within the transaction
instead of rolling back the entire transaction.

Example:

-- Start a transaction BEGIN;


-- Make changes to the database
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
-- Set a savepoint
SAVEPOINT before_update;
-- Make more changes UPDATE employees SET salary = salary * 1.05 WHERE
department_id = 20;
-- Something went wrong, rollback to the savepoint
ROLLBACK TO before_update;
Collections (Assuming Oracle's PL/SQL):

CREATE TYPE employee_list AS TABLE OF VARCHAR2(50);

These examples demonstrate the basic usage of DCL and TCL commands and
object creation in SQL databases. The syntax may vary slightly depending on the
specific database system you are using.

You might also like