Submitted By: SWEN221101044

Chapter 4

Lab 4: TCL,DCL and Constraints

Objectives
Introduction to TRANSATIONAL CONTROL LANGUAGE (T.C.L), DATA CONTROL LAN-
GUAGE (D.C.L) and Constraints

TRANSATIONAL CONTROL LANGUAGE (T.C.L)

A transaction is a logical unit of work. All changes made to the database can be referred to
as a transaction. Transaction changes can be mode permanent to the database only if they
are committed a transaction begins with an executable SQL statement ends explicitly with
either role back or commit statement.

COMMIT
This command is used to end a transaction only with the help of the commit command trans-
action changes can be made permanent to the database.
Syntax
SQL>COMMIT;
Example
SQL>COMMIT;

SAVE POINT
Save points are like marks to divide a very lengthy transaction to smaller once. They are
used to identify a point in a transaction to which we can latter role back. Thus, save point is
used in conjunction with role back.
Syntax
SQL>SAVE POINT ID;
Example
SQL>SAVE POINT xyz;

ROLE BACK
 A role back command is used to undo the current transactions. We can role back the entire
transaction so that all changes made by SQL statements are undo (or) role back a transaction

to a save point so that the SQL statements after the save point are role back.
Syntax
ROLE BACK( current transaction can be role back)
ROLE BACK to save point ID;
Example
SQL>ROLE BACK;
SQL>ROLE BACK TO SAVE POINT xyz;

DATA CONTROL LANGUAGE (D.C.L)

DCL provides uses with privilege commands the owner of database objects (tables), has the
soul authority ollas them. The owner (data base administrators) can allow other data base
uses to access the objects as per their requirement

GRANT
The GRANT command allows granting various privileges to other users and allowing them
to perform operations with in their privileges For Example, if a uses is granted as ’SELECT’
privilege then he/she can only view data but cannot perform any other DML operations on
the data base object GRANTED privileges can also be withdrawn by the DBA at any time.
Syntax
SQL>GRANT PRIVILEGES on object-name To user-name;
Example
SQL>GRANT SELECT, UPDATE on emp To hemanth;

REVOKE
To with draw the privileges that has been GRANTED to a uses, we use the REVOKE com-
mand
Syntax
SQL>REVOKE PRIVILEGES ON object-name FROM user_name;
Example
SQL>REVOKE SELECT, UPDATE ON emp FROM ravi;
1. Creation, altering and dropping of tables and inserting rows into a table (use constraints
while creating tables) examples using SELECT command.

CREATE
CREATE TABLE
This is used to create a new relation.
Syntax
CREATE TABLE relation-name
(field-1 data-type(Size),field-2 data-type(Size), .. . );
Example
SQL>CREATE TABLE Student (sno NUMBER(3) PRIMARY KEY ,sname CHAR(10),class
CHAR(5);
ALTER
(a) ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.
Syntax
ALTER TABLE relation-name ADD(new field-1 data-type(size), new field-2 data-type(size),..);
Example
SQL>ALTER TABLE std ADD(Address CHAR(10));
(b) ALTER TABLE...MODIFY...: This is used to change the width as well as data type of fields
of existing relations.
Syntax
ALTER TABLE relation-name MODIFY (field-1 newdata-type(Size), field-2 newdata-type(Size),..field-
newdata-type(Size));
Example
SQL>ALTER TABLE student MODIFY(sname VARCHAR(10),class VARCHAR(5));

Constraints
There are 5 constraints available in ORACLE:

NOT NULL
When a column is defined as NOTNULL, then that column becomes a mandatory column.
It implies that a value must be entered into the column if the record is to be accepted for
storage in the table.
Syntax
CREATE TABLE Table-Name(column-name data-type(size) NOT NULL, );
Example
CREATE TABLE student (sno NUMBER(3)NOT NULL, name CHAR(10));

UNIQUE
The purpose of a unique key is to ensure that information in the column(s) is unique i.e. a
value entered in column(s) defined in the unique constraint must not be repeated across
the column(s). A table may have many unique keys.
Syntax
CREATE TABLE Table-Name(column-name data-type(size) UNIQUE, —);
Example
CREATE TABLE student (sno NUMBER(3) UNIQUE, name CHAR(10));

CHECK
Specifies a condition that each row in the table must satisfy. To satisfy the constraint, each
row in the table must make the condition either TRUE or unknown (due to a null).
Syntax
CREATE TABLE Table-Name(column-name data-type(size) CHECK(logical expression),–);
Example
CREATE TABLE student (sno NUMBER (3), name CHAR(10),class CHAR(5),CHECK(class
IN(’CSE’,’CAD’,’VLSI’));

PRIMARY KEY
A field which is used to identify a record uniquely. A column or combination of columns
can be created as primary key, which can be used as a reference from other tables. A table
contains primary key is known as Master Table.
• It must uniquely identify each record in a table.

• It must contain unique values.

• It cannot be a null field.

• It cannot be multi port field.

• It should contain a minimum no. of fields necessary to be called unique.

Syntax
CREATE TABLE Table-Name(column-name data-type(size) PRIMARY KEY,—); Example
CREATE TABLE faculty (fcode NUMBER(3) PRIMARY KEY, fname CHAR(10));

FOREIGN KEY
It is a table level constraint. We cannot add this at column level. To reference any primary
key column from other table this constraint can be used. The table in which the foreign key
is defined is called a detail table. The table that defines the primary key and is referenced by
the foreign key is called the master table.
Syntax
CREATE TABLE Table-Name(column-name data-type(size) FOREIGN KEY(column-name)
REFERENCES table-name);
Example
CREATE TABLE subject (scode NUMBER (3) PRIMARY KEY,subname CHAR(10),fcode NUM-
BER(3),FOREIGN KEY(fcode) REFERENCE faculty );
Defining integrity constraints in the alter table command:
Syntax
ALTER TABLE Table-Name ADD PRIMARY KEY (column-name);
Example
ALTER TABLE student ADD PRIMARY KEY (sno);
(Or)
Syntax
ALTER TABLE table-name ADD CONSTRAINT constraint-name PRIMARY KEY(colname)
Example
ALTER TABLE student ADD CONSTRAINT SN PRIMARY KEY(SNO)
Dropping integrity constraints in the alter table command:
Syntax
ALTER TABLE Table-Name DROP constraint-name;
Example
ALTER TABLE student DROP PRIMARY KEY;
(or)
Syntax
 ALTER TABLE student DROP CONSTRAINT constraint-name;
Example
ALTER TABLE student DROP CONSTRAINT SN;
Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAV-
ING and Creation and dropping of Views.

Aggregative operators
In addition to simply retrieving data, we often want to perform some computation or sum-
marization. SQL allows the use of arithmetic expressions. We now consider a powerful class
of constructs for computing aggregate values such as MIN and SUM
1. Count
COUNT following by a column name returns the count of tuple in that column. If DISTINCT
keyword is used then it will return only the count of unique tuple in the column. Otherwise,
it will return count of all the tuples (including duplicates) count (*) indicates all the tuples of
the column.
Syntax
COUNT (Column name)
Example
SELECT COUNT (Sal) FROM emp;
2. SUM
SUM followed by a column name returns the sum of all the values in that column.
Syntax
SUM (Column name)
Example
SELECT SUM (Sal) From emp;
3. AVG
AVG followed by a column name returns the average value of that column values.
Syntax
AVG (n1,n2..)
Example
Select AVG(10, 15, 30) FROM DUAL;
4. MAX
MAX followed by a column name returns the maximum value of that column.
Syntax
MAX (Column name)
Example
SELECT MAX (Sal) FROM emp;
SQL> select deptno,max(sal) from emp group by deptno;

DEPTNO MAX(SAL)

10 5000
20 3000
30 2850

SQL> select deptno,max(sal) from emp group by deptno having max(sal)<3000;

DEPTNO MAX(SAL)
30 2850

5. MIN
MIN followed by column name returns the minimum value of that column.
Syntax
MIN (Column name)
Example
SELECT MIN (Sal) FROM emp;
SQL>select deptno,min(sal) from emp group by deptno having min(sal)>1000;

DEPTNO MIN(SAL)

10 1300

VIEW
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from
one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as
if the data were coming from one single table.
A view is a virtual table, which consists of a set of columns from one or more tables. It is
similar to a table but it doest not store in the database. View is a query stored as an object.
Syntax
CREATE VIEW view-name AS SELECT set of fields FROM relation-name
WHERE (Condition)
Example
SQL>CREATE VIEW employee AS SELECT empno,ename,job FROM EMP WHERE job =
’clerk’;
View created.

SQL> SELECT * FROM EMPLOYEE;

EMPNO ENAME JOB

7369 SMITH CLERK

7876 ADAMS CLERK
7900 JAMES CLERK
7934 MILLER CLERK

Example
CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products
WHERE Discontinued=No
DROP VIEW
This query is used to delete a view , which has been already created.
Syntax
DROP VIEW View-name;
Example
SQL> DROP VIEW EMPLOYEE;
View dropped
Queries using Conversion functions (to_char, to_number and to_date),
string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper,
initcap, length, substr and instr), date functions (Sysdate, next_day,
add_months, last_day, months_between, least, greatest, trunc, round,
to_char, to_date)
 Conversion functions
To-char
TO-CHAR (number) converts n to a value of VARCHAR2 data type, using the optional num-
ber format fmt. The value n can be of type NUMBER, BINARY-FLOAT, or BINARY-DOUBLE.
SQL>select to-char(65,’RN’)from dual;
LXV
To-number
TO-NUMBER converts expr to a value of NUMBER data type.
SQL> Select to-number(’1234.64’) from Dual;
1234.64
To-date
TO-DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a
value of DATE data type.
SQL>SELECT TO-DATE(’January 15, 1989, 11:00 A.M.’)FROM DUAL;
TO-DATE(’
———
15-JAN-89

String functions

Concat
CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the
datatypes
SQL>SELECT CONCAT(’ORACLE’,’CORPORATION’)FROM DUAL;
ORACLECORPORATION
Lpad
LPAD returns expr1, left-padded to length n characters with the sequence of characters in
expr2.
SQL>SELECT LPAD(’ORACLE’,15,’*’)FROM DUAL;
*********ORACLE
Rpad
RPAD returns expr1, right-padded to length n characters with expr2, replicated as many
times as necessary.
SQL>SELECT RPAD (’ORACLE’,15,’*’)FROM DUAL;
ORACLE*********
Ltrim
Returns a character expression after removing leading
blanks. SQL>SELECT LTRIM(’SSMITHSS’,’S’)FROM DUAL;
MITHSS
Rtrim
Returns a character string after truncating all trailing
blanks SQL>SELECT RTRIM(’SSMITHSS’,’S’)FROM DUAL;
SSMITH
Lower
Returns a character expression after converting uppercase character data to lowercase.
SQL>SELECT LOWER(’DBMS’)FROM DUAL;
dbms
Upper
Returns a character expression with lowercase character data converted to uppercase.
SQL>SELECT UPPER(’dbms’)FROM DUAL;
DBMS
Length
Returns the number of characters, rather than the number of bytes, of the given string ex-
pression, excluding trailing blanks.
SQL>SELECT LENGTH(’DATABASE’)FROM DUAL;
8
Substr
Returns part of a character, binary, text, or image
expression. SQL>SELECT
SUBSTR(’ABCDEFGHIJ’3,4)FROM DUAL; CDEF
Instr
The INSTR functions search string for substring. The function returns an integer indicating
the position of the character in string that is the first character of this occurrence.
SQL>SELECT INSTR(’CORPORATE FLOOR’,’OR’,3,2)FROM DUAL;
14

Date functions

Sysdate
SQL>SELECT SYSDATE FROM DUAL;
29-DEC-08
next_day
SQL>SELECT NEXT_DAY(SYSDATE,â A˘ Z´ WEDâ A˘ Z´ )FROM
DUAL; 05-JAN-09
add_months
SQL>SELECT ADD_MONTHS(SYSDATE,2)FROM DUAL;
28-FEB-09
last_day
SQL>SELECT LAST_DAY(SYSDATE)FROM DUAL;
31-DEC-08
months_between
SQL>SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE)FROM EMP;
4
Least
SQL>SELECT LEAST(’10-JAN-07’,’12-OCT-07’)FROM DUAL;
10-JAN-07
Greatest
SQL>SELECT GREATEST(’10-JAN-07’,’12-OCT-07’)FROM DUAL;
10-JAN-07
Trunc
SQL>SELECT TRUNC(SYSDATE,’DAY’)FROM DUAL;
28-DEC-08
Round
SQL>SELECT ROUND(SYSDATE,’DAY’)FROM DUAL;
28-DEC-08
to_char
SQL> select to_char(sysdate, "dd mm yy") from dual;
24-mar-05.
to_date
SQL> select to _date(sysdate, "dd mm yy") from dual;
24-mar-o5.
Practise Exercise
1- Display all employee names and salary whose salary is greater than minimum salary of
the company and job title starts with M.
2- Issue a query to find all the employees who work in the same job as
Arjun. 3- Display all the dept numbers available with the dept and emp
tables.

use Task1;
CREATE TABLE emp(eName VARCHAR(100), eStatus VARCHAR(100), designation VARCHAR(100), salary
numeric, deptNo numeric Foreign Key references dept(deptNo));
CREATE TABLE dept(deptNo NUMERIC PRIMARY KEY, deptName VARCHAR(100) )
INSERT INTO emp (eName, eStatus, designation , salary, deptNo)
VALUES ('Abc', 'Permanent', 'Manager', 12000, 1),
('Def', 'Contract', 'Manager', 14000, 1),
('Arjun', 'Permanent', 'Clerk', 120000, 1),
('Ali', 'Permanent', 'Clerk', 120000, 1),
('Shah', 'Contract', 'Teacher', 150000, 2),
('Zaib', 'Permanent', 'Teacher', 160000, 2),
('Hod', 'Permanent', 'HOD', 1200000, 3);
INSERT INTO dept (deptNo, deptName) VALUES (1, 'CS'), (1, 'IT'), (3,'CP');
select * from dept, emp;
--1- Display all employee names and salary whose salary is greater than minimum salary of the
company and job title starts with M.
SELECT eName, salary from emp where salary = (Select min(salary) from emp) AND designation LIKE
'M%';
--2- Issue a query to find all the employees who work in the same job as Arjun.
SELECT * FROM emp where designation = (SELECT designation where ename = 'Arjun')
--3- Display all the dept numbers available with the dept and emp tables.
SELECT DISTINCT deptno
FROM (
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept
) AS combined_depts;