Exp.

no:1 DDL AND DML COMMANDS

Date:
AIM
To execute the Data Definition Language (DDL) commands and Data ManipulationLanguage
(DML) Commands.

DATA DEFINITION LANGUAGE

1. Creating a Table:

Create command is used to create a table in the database.

Syntax:

create table tablename (column 1 datatype 1, …., column n datatype n);

2. DESC COMMAND

This command is used to view the structure of the table.

Syntax:

desc tablename; (or)

describe tablename;
3. ALTERING A TABLE

The structure of the table can be changed using this command like add new
column, change the width of a data type, change the data type of a column.
a.Modify
Syntax:
alter table tablename modify (column datatype, …);
B. Add / Drop
Syntax:

alter table tablename add (column datatype, …);alter

table tablename drop columnname;
4. TRUNCATING A TABLE

This command is used to delete all records stored in a table, but the structure ofthe table is
retained.

1
 Syntax:

truncate table tablename;

5. DROPPING A TABLE

This command is used to remove a table from the database.

Syntax: drop table tablename;

DATA MANIPULATION LANGUAGE

1. INSERT COMMANDS

1. Insert a row with values for columns in a table.

This insert command is used to add rows to a table in the database.

Syntax:

insert into tablename values (value1, value2,……, value n);

insert into tablename(column 1,column 2) values(value 1,value 2);

2. Insert a large number of rows.

This insert command is used to add multiple rows to a table in theDatabase in
user friendly manner..
Syntax:

insert into tablename values (&column1, &column 2,……, &column n);

2. SELECT COMMANDS

1. Select all rows from a table.

This select command is used to retrieve all the values stored in the table.

Syntax: select * from tablename;

Select column name(s) from tablename; Select

distinct column name from tablename;
2. Select using where command:

This select command is used to retrieve the particular field values, storedin the
table, which satisfy a required condition.
Syntax: select column name(s) from tablename where search condition;
2
select * from table name where search condition;
3. UPDATE COMMAND

This command is used to update (changing values in) one or two columnsof a row
in a table. Specific rows can be updated based on some condition.
Syntax:

update tablename set column1=expression, column 2=expression, ……,

column n=expression;
update tablename set column1=expression, column 2=expression, ……,
column n=expression where search condition;
4. DELETE COMMAND
A delete query is expressed in much the same way as Query. We can delete wholetuple
(rows) we can delete values on only particulars attributes.
1. Deletion of all rows:
Syntax delete from tablename ;
2. Deletion of specified number of rows:
Syntax delete from tablename where search condition ;

RENAME COMMAND
Renaming Table name
Syntax rename oldtablename to newtablename;

Renaming columns used with Expression ListsSyntax

Select columnname result_columnname, Columnname result_columnname from
tablename;

OUTPUT
1. Create EMP table.
SQL>create table emp(empno number(4), ename varchar2(10), job varchar2(9), mgr number(4),
hiredate date, sal number(7,2), comm Number(7,2), deptno number(3), age number(3), esal
number(10));

3
2. Get the description EMP table.
Name Null? Type

EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(3)
AGE NUMBER(3)
ESAL NUMBER(10)

3. Create DEPT table.

SQL>create table dept(deptno number(2) , dname varchar2(14), loc varchar2(13));

4. Get the description DEPT table.

SQL>desc dept;
Name Null? Type

DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)

5. Insert records into EMP & DEPT.

SQL> insert into emp values(&empno,’&ename’,’&job’,&mgr,’&date’,&sal,&comm.
deptno,&age, &esal);

SQL>insert into dept values(&deptno,’&dname’,’&loc’);

4
6. List all employee details.
SQL>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO AGE ESAL

7369 CLERK 7902 17-DEC-80 800 0 20 25 0

SMITH
7499 ALLE SALESM 7698 20-FEB-81 1600 300 30 25 0
N AN
7521 WARD SALESM 7698 22-FEB-81 1250 500 30 25 0
AN
7566 JONES MANAG 7839 02-APR-81 2975 500 20 25 0
ER
7698 BLAK MANAG 7839 01-MAY-81 2850 1400 30 25 0
E ER

7. List all dept details

SQL>select * from dept;
DťPTNO DNAMť LOC
------------ ------------ --------
10 accounting New york
20 research Dallas
30 Sales Chicago
40 operations Boston

8. List the employees belonging to the department 20.SQL>

select * from emp where deptno=20;
ENAME
SMITH
JONES
9. List the name and salary of the employees whose salary is more than 1000.SQL
>select ename,sal from emp where sal>1000;
ENAM SAL
E

ALLE 1600
N
5
WARD 1250
JONES 2975
BLAK 2850
E

10. List the names of employees who are managers SQL

>select ename from emp where job = ‘MANAGER’
ENAME

JONES
BLAK
E

11. List the different jobs available in employee table.

SQL>select distinct job from emp;
JOB

CLERK
MANAGE
R
SALESMA
N
3 rows selected.

12. Alter emp table to add city column which is of string data type.
SQL>Alter table emp add city varchar2(20);
Table altered

13. Alter dept table to change loc column size as 20.

SQL>Alter table dept modify city varchar2(30);
Table altered

6
14. Update the salary of the employee as 2500 whose job as manager.
SQL>update emp set esal=2500 where job =‘manager’;

15. Delete the employee’s record whose salary is above 2800.

SQL>delete emp where esal>2800;
1 row deleted.

16. Change the name of emp table as EMPLOYEE.

SQL>rename emp to employee;
Table renamed.

17. Delete all records from dept table.

SQL>truncate table dept;
Table truncated.

18. Delete dept table.

SQL>drop table dept;
Table dropped.

RESULT :

Thus the DDL and DML commands are executed successfully.

7
Ex.No:2 INTEGRITY CONSTRAINTS
Date:

AIM:

To implement INTEGRITY CONSTRAINTS using SQL.

CONSTRAINTS:

Integrity Constraints ensure that changes made to the database by authorized users do not result in loss
of data consistency. Thus, integrity constraints guard against accidental damage to the database.
You can use constraints to do the following:

• Enforce rules on the data in a table whenever a row is inserted, updated, or deleted fromthat
table. The constraint must be satisfied for the operation to succeed.
• Prevent the deletion of a table if there are dependencies from other tables.

• Provide rules for Oracle tools, such as Oracle Developer.

• The following constraint types are valid:

➢ NOT NULL
➢ UNIQUE
➢ PRIMARY KEY
➢ FOREIGN KEY
➢ CHECK
➢ Reference
➢ Default

8
 CONSTRAINTS CAN BE CREATED IN THREE WAYS:

1)Column level constraints

2)Table level constraints
3)Using DDL statements-alter table command
OPERATION ON CONSTRAINT:
i) ENABLE

ii) DISABL
Eiii)DROP

I.DOMAIN INTEGRITY CONSTRAINTS

1. NOT NULL CONSTRAINT

The NOT NULL constraint ensures that the column contains no null values.

Columns without the NOT NULL constraint can contain null values by default.

Syntax

create table <table name> (column 1 datatype 1 not null,

……., column n datatype n);

Example

SQL>create table emp7(empno number(4),ename varchar2(20) constraint emp7_enameNOT

NULL, design varchar2(20), sal number(3));
Table created.

SQL> desc emp7;

Name Null? Type

EMPNO NUMBER(4)
ENAME NOT NULL VARCHAR2(20)
DESIGN VARCHAR2(20)
SAL NUMBER(3)
9
 To add a NOT NULL constraint, use the ALTER TABLE MODIFY syntax:

ALTER TABLE employees MODIFY (ename constraint emp_ename_nn NOT NULL);

2. CHECK CONSTRAINT

The CHECK constraint defines a condition that each row must satisfy. Check constraints
contain an expression the database will evaluate when you modify or insert a row. If the expression
evaluates to false, the database will not save the row. Building a check constraint is similar to
building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in
additional to BETWEEN, IN, LIKE, and NULL. You can also build expressions around AND
and OR operators.

create table <table name> (column1 datatype, column2 datatype constraint

constraintname check(condition),….. column n datatype );

To add a CHťCK constraint, use the ALTťR TABLť ADD syntax:

alter table <table name> add constraint constraint_name check(condition);

Example:

SQL>create table emp7(empno number(7),ename varchar2(20),sal number(10) constraintemp_sal

check(sal>500 and sal<10000));
Table created.

SQL> alter table emp7 add constraint check (sal between 500 and 10000);

ENTITY INTEGRITY CONSTRAINTS

1. UNIQUE CONSTRAINT

Unique key constraint is used to prevent duplication of values within the row of aspecified
column or a group of table.

10
 Syntax
create table <table name> (column1 datatype, column2 datatype constraint
constraintname UNIQUE,….. ,column n datatype );

alter table <table name> add/modify constraint constraint_name unique (column name);

Example:
SQL> create table emp7(empno number(3),ename varchar2(20), desgin
varchar2(15)constraint emp_des unique,sal number(5));
Table created.

SQL> alter table emp7 add constraint emp_des unique (desgin);

2. PRIMARY KEY CONSTRAINT

A primary key is one or more column in a table which identifies each row in the table
uniquely. The constraint does not allow null values and duplicate values in columns. primary
key constraint is simply a combination of a unique constraint and a not-null constraint.
Syntax

Column level constraints Using Primary key:

Create table tablename(column1 datatype primary key, column2 datatype,………);

Column level constraints Using Primary key with naming convention:

Create table tablename(column1 datatype constraint constraint name primary key,column2

datatype,………);

11
Table level constraints Using Primary key:

Create table tablename(column1 datatype, column2 datatype,………, constraint constraintname

primary key (COLUMN NAME);

Table level constraint with alter command (primary key):

alter table <table name> add/modify constraint constraint_name
primary key (column name);

Example:
SQL> create table employee(empno number(4) primary key, ename varchar2(10), job
varchar2(6), sal number(5), deptno number(7));
Table created.
(or)
SQL> create table employee(empno number(4) constraint emp_empno primary key,ename
varchar2(10), job varchar2(6), sal number(5), deptno number(7));
Table created.

SQL> desc employee;

Null? Type
Name

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)
JOB VARCHAR2(6)
SAL NUMBER(5)
DEPTNO NUMBER(7)

SQL> alter table employee add constraint emp_eno primary key (empno);

RESULT :

Thus the Integrity constraints commands are executed successfully.

12
Ex.no:3 REFERENTIAL INTEGRITY CONSTRAINTS

Date:

FOREIGN KEY
Reference /foreign key constraint:

A foreign key constraint specifies that the values in a column (or a group of columns)
must match the values appearing in some row of another table. We say this maintains the
referential integrity between two related tables.
The FOREIGN KEY, or referential integrity constraint, designates a column or
combination of columns as a foreign key and establishes a relationship between a primary key or a
unique key in the same table or a different table.

FOREIGN KEY constraints can be defined at the column or table constraint level. A
composite foreign key must be created by using the table-level definition.

Parent Table:
SQL> create table employee(empno number(40) primary key, ename varchar2(10), job
varchar2(6), sal number(5), deptno number(7));
Column level foreign key constraint:
Child Table:

Syntax

Column level foreign key constraint

SQL:> Create table tablename (column1 datatype, column2 datatype FOREIGN KEYreferences
Parenttable name(Columnname> .......................................................... );
Column level foreign key constraint with naming conversions:

SQL:> Create table tablename (column1 datatype, column2 datatype Constraint constraintname
References .......................... Parenttablename(Columnname> ,column3datatype, );
Table Level Foreign Key Constraints

SQL:> Create table tablename (column1 datatype, column2 datatype ,column3 datatype,
Constraint constraintname FOREIGN KEY(columnname) References
Parenttablename(Columnname) ................................ );
13
Table Level Foreign Key Constraints with Alter command

SQL>Alter table tablename Add Constraint constraintname FOREIGN KEY(columnname)

References Parenttablename(Columnname));
Example:

SQL> Create table dept(deptno number(2) primary key, dname varchar2(20), location
varchar2(15));
Table created.

SQL> desc dept;

Name Null? Type

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(20)
LOCATION VARCHAR2(15)

SQL>create table emp4a (empno number(3), deptno number(2) constraint

emp4a_deptno_fk references dept(deptno), design varchar2(10));
Table created.
(or)
SQL>create table emp4a (empno number(3), deptno number(2) FOREIGN KEY references
dept(deptno), design varchar2(10));Table
created.
SQL> desc emp4a;
Name Null? Type

EMPNO NUMBER(3)
DEPTNO NUMBER(2)
DESIGN VARCHAR2(10)
Default:

The DEFAULT constraint is used to insert a default value into a column.The defaultvalue
will be added to all new records, if no other value is specified.

14
 Syntax:

SQL:>Create table tablename(column1 datatype, column2 datatype default(value),……...

column datatype);

Example:
SQL> create table emp1(sno number(10),sname varchar(10),score number(3) default 80);Table
created.

SQL> insert into emp1(sno,sname) values(10,'john');1 row

created.

SQL> select * from emp1; SNO

SNAME SCORE

10 john 80

Dropping a Constraint
To drop a constraint, you can identify the constraint name from the USER_CONSTRAINTS
and USER_CONS_COLUMNS data dictionary views. Then use the ALTER TABLE statement
with the DROP clause. The CASCADE option of the DROP clause causes any dependent
constraints also to be dropped.
Syntax:
ALTER TABLE tablname DROP PRIMARY KEY | UNIQUE (column) |CONSTRAINT constraint
[CASCADE];
In the syntax:
table is the name of the table
column is the name of the column affected by the constraint
constraint is the name of the constraint

Example :

Alter table employee DROP constraint emp_empno;

Disabling a Constraint

You can disable a constraint without dropping it or re-creating it by using the ALTERTABLE
statement with the DISABLE clause.

15
Syntax :

SQL>ALTER TABLE <TABLE-NAME> DISABLE CONSTRAINT <CONSTRAINT NAME>

Example:

Alter table employee DISABLE constraint emp_empno;

Enabling a Constraint

You can enable a constraint without dropping it or re-creating it by using the ALTER
TABLE statement with the ENABLE clause.
Syntax :

SQL>ALTER TABLE <TABLE-NAME> ENABLE CONSTRAINT <CONSTRAINT NAME>

Example:

Alter table employee ENABLE constraint emp_empno;

Result:
Thus the implementation of various constraints was performed successfully.

16
EX No:4 SET OPERATIONS, JOINS AND NESTED QUERIES

Date:
AIM
To query multiple tables by using set operators, joins and sub queries.

SET OPERATIONS:

The SET operators combine the results of two or more component queries into oneresult.
Queries containing SET operators are called compound queries. Some of set operators are,

UNION - ows of first query plus rows of second query

UNION ALL - Rows of first query plus rows of second query, including all duplicatesINTERSECT
– common rows from all queries
MINUS - All distinct rows that are selected by the first SELECT statement and not selectedin the
second SELECT statement
Syntax:
select query set operator select query
JOINS:
An SQL join clause combines records from two or more tables in a database. It creates a
set that can be saved as a table or used as is. A JOIN is a means for combining fields from two
tables by using values common to each. ANSI standard SQL specifies four types of Joins: INNER,
OUTER, LEFT, and RIGHT. As a special case, a table (base table, view, or joined table) can JOIN
to itself in a self-join.
A programmer writes a JOIN predicate to identify the records for joining. If the evaluated
predicate is true, the combined record is then produced in the expected format, a record set or a
temporary table.
SQL specifies two different syntactical ways to express joins: "explicit join notation" and
"implicit join notation".

The "explicit join notation" uses the JOIN keyword to specify the table to join, and the
ON keyword to specify the predicates for the join.

The "implicit join notation" simply lists the tables for joining (in the FROM clause ofthe
SELECT statement), using commas to separate them

17
Inner Join

Inner join creates a new result table by combining column values of two tables (Aand B)
based upon the join-predicate. The query compares each row of A with each row ofB to find all
pairs of rows which satisfy the join-predicate. When the join-predicate issatisfied, column
values for each matched pair of rows of A and B are combined into aresult row. The result of
the join can be defined as the outcome of first taking theCartesian product (or Cross join) of all
records in the tables (combining every record intable A with every record in table B)—then
return all records which satisfy the joinpredicate. Inner join is further classified as equi-joins, as
natural joins, or as cross-joins
Explicit Notation:

Select * from table name1 inner join table name2 on table1.fieldname=table2.fieldname;Select *

from table1 inner join table2 using(fieldname);
Implicit Notation
Select * from table1, table2 where table1.fieldname=table2.fieldname;Equi
Join
An equi-join is a specific type of comparator-based join, or theta join that uses only equality
comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join
as an equi-join. The query shown above has already provided an example of an equi-join:
Explicit Notation:

Select * from table1 join table2 on table1.fieldname=table2.fieldname;

Natural join
A natural join offers a further specialization of equi-joins. The join predicate arises
implicitly by comparing all columns in both tables that have the same column-names in the
joined tables. The resulting joined table contains only one column for each pair of equally-
named columns
Explicit Notation:
Select * from table1 natural join table2 on table1.fieldname=table2.fieldname; Cross
join
Cross Join returns the Cartesian product of rows from tables in the join. In other words, it
will produce rows which combine each row from the first table with each row from the second
table.

18
Explicit Notation:

Select * from table1 cross join table2 ;

Implicit Notation
Select * from table1,table2;

Outer join
An outer join does not require each record in the two joined tables to have a matching
record. The joined table retains each record—even if no other matching recordexists. Outer joins
subdivide further into left outer joins, right outer joins, and full outer joins, depending on which
table(s) one retains the rows from (left, right, or both).
Left outer join
The result of a left outer join (or simply left join) for table A and B always contains
all records of the "left" table (A), even if the join-condition does not find any matching record
in the "right" table (B). This means that a left outer join returns all the values from the left table,
plus matched values from the right table (or NULL in case of no matchingjoin predicate).
Explicit Notation:

Select * from table1 left outer join table2 on table1.fieldname=table2.fieldnameImplicit

Notation
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column =
table2.column (+);
Right outer join
A right outer join (or right join) closely resembles a left outer join, except with the
treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined
table at least once. If no matching row from the "left" table (A) exists, NULL will appear in
columns from A for those records that have no match in B.
Explicit Notation:

Select * from table1 right outer join table2 on table1.fieldname=table2.fieldname

Implicit Notation

SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column(+) =

table2.column;
Full outer join
Conceptually, a full outer join combines the effect of applying both left and right outer
joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have
NULL values for every column of the table that lacks a matching row.
19
Explicit Notation:
Select * from table1 full outer join table2 on table1.fieldname=table2.fieldname Self-
join
A self-join is joining a table to itself means each row of the table is combined with itself
and with every other row of the table. The self join is viewed as join of two copies of the same
table.

SET OPERATIONS

1. Display the different designation in department 20 and 30.

SQL> select designation from emp where deptno=20

union
select designation from emp where deptno=30;

SQL> select designation from emp where deptno=20 union all select designation from
emp where deptno=30;

2. List the jobs common to department 20 and 30.

SQL> select designation from emp where deptno=20 intersect select designation fromemp
where deptno=30;

3. List the jobs unique to department 20.

SQL> select designation from emp where deptno=20 minus select designation from empwhere
deptno=10 minus select designation from emp where deptno=30;

(Or)
SQL> select designation from emp where deptno=20 minus select designation from empwhere
deptno in (10,30);

JOINS:

Tables:
SQL> create table two(deptno number(4) primary key,deptname varchar(10));
Table created.

20
SQL> create table one(name varchar(10),deptno references two(deptno));
Table created.
Insert these values into table one and table two

SQL> select * from one;

NAME DEPTNO

anand 31
babu 32
chitra 32

3 rows selected.

SQL> select * from two;

DEPTNO
DEPTNAME
31 sales
32 enggg

Inner join
i. SQL> select * from one, two where one.deptno=two.deptno;
ii. SQL> select * from one inner join two on one.deptno=two.deptno
iii. Equi join
i. SQL> select * from one join two on one.deptno=two.deptno;
ii. SQL> select * from one inner join two using(deptno);

Natural join
i.SQL> select * from one natural join two;

Cross Join
i.SQL> select * from one cross join two;
ii.SQL> select * from one,two;
Left outer join
i. SQL> select * from one left outer join two on one.deptno=two.deptno;
ii. SQL> select * from one,two where one.deptno=two.deptno(+);

21
Right outer join
i. SQL> select * from one right outer join two on one.deptno=two.deptno;
ii. SQL> select * from one,two where one.deptno(+)=two.deptno;

Full outer join

i. SQL> select * from one full outer join two on one.deptno=two.deptno;

Self join
SQL> create table selfj(empno number(5),ename varchar(7),mgr number(5));
Table created.

SQL> select * from selfj;

EMPNO ENAME MGR

7839 kalyan
7566 jones 7839
7876 anand 7788
7934 babu 7782
7777 raja 7934

List out the names of manager with the employee record.

SQL> select worker.ename,manager.ename from selfj worker,selfj manager where
worker.mgr=manager.empno;

SUB QUERIES
A subquery is a SELECT statement that is embedded in a clause of another SELECT
statement. You can build powerful statements out of simple ones by using subqueries. ACommon
use of subquery is to perform tests for
1. set memberships,
2.setcomparisons
3.set cardinality.

22
 ➢ The subquery (inner query) executes once before the main query.

➢ The result of the subquery is used by the main query (outer query).

➢ A subquery must be enclosed in parentheses.

➢ Place the subquery on the right side of the comparison condition for readability.

Note: Comparison conditions fall into two classes:

1. single-row operators (>, =, >=, <, <>, <=) and

2. multiple-row operators (IN, ANY, ALL).

The subquery is often referred to as a nested SELECT, sub-SELECT, or inner

SELECT statement. The subquery generally executes first, and its output is used to complete
the query condition for the main or outer query.

Subquery Syntax
SELECT select_list FROM table WHťRť expr operator (SELECT select_list FROM table);

Types of Subqueries

• Single-row subqueries: Queries that return only one row from the inner SELECTstatement
• Multiple-row subqueries: Queries that return more than one row from the inner SELECT
Statement.
A single-row subquery

Subqueries that returns one row from the inner SELECT statement. This type of
subquery uses a single-row operator.

23
Multiple-Row Subqueries

Subqueries that return more than one row are called multiple-row subqueries. You
use a multiple-row operator, instead of a single-row operator, with a multiple-row subquery.The
multiple-row operator expects one or more values.

LOAN (Loanno branchname amount)

BORROWER (Loanno cusname)

STUDENT (Sname rollno dept marks)

DEPOSITOR (Cusname Accno)

1. Select loanno, branchname, amount from loan where amount=(select min(amount) fromloan)

2. Select sname, rollno, dept, marks from student where marks=(select min(marks) fromstudent
group by dept;

3. Select sname, rollno, dept, marks from student where marks IN (select min(marks) fromstudent
group by dept)
4. Select sname, rollno, dept, marks from student where marks NOT IN (select min(marks)from
student)

24
5. select * from loan where amount > ANY (select amount from loan where
branchname=’chennai’)

6. select * from loan where amount > ALL (select amount from loan where
branchname=’chennai’)

7. select * from loan where EXISTS (select * from borrower where

loan.loanno=borrower.loanno)

8. select * from loan where NOT EXISTS (select * from borrower where
loan.loanno=borrower.loanno)

9. select * from loan where EXCEPT (select * from borrower where

loan.loanno=borrower.loanno)

10. Find all customers who have both an account and a loan at the bank.
Select distinct cusname from customer where cusname IN (select cusname from
depositor)

11. Find all customers who have a loan at the bank but do not have an account at thebank.
Select distinct cusname from customer where cusname NOT IN (select cusname fromdepositor)

12. Find the loanno,amount,branchname who have get second maximum loan amount at the bank.
Select loanno, branchname, max(amount) from loan where amount<=(select max(amount)from loan )

13. Write a query that displays the roll numbers and student names of all student whostudied
in a department with any student whose name contains a S.
SELECT rollno, sname FROM student WHERE dept IN (SELECT dept FROM student WHERE
sname like ’%s%’);
14. List the employee names whose salary is greater than the lowest salary of anemployee
belonging to department number 30.
SQL> select ename from emp
where sal>any(select sal from
emp where deptno=30);

25
15. List the employee details of those employees whose salary is greater than any of thesalesman.
SQL> select empno, ename, sal from emp where sal>any(select
sal from emp where job='asstprof');

16. List the employee names whose salary is greater than the highest salary of anemployee
belonging to department number 20.
SQL> select ename from emp
where sal>all(select sal from emp where deptno=20);

RESULT :

Thus the implementation of Set Operations, Joins and Nested Queries commandare
executed successfully.

26
Ex. No:5 CREATION OF PROCEDURES
Date:

Aim:

To create and implement the PL/SQL procedures.

QUERIES:

i.)Create a stored procedure which takes empno as parameter, and display the name and job of the
employee.
SQL>create or replace procedure emppro(eno in number)is begin
for v in (select * from emp where empno=eno)
loop dbms_output.put_line('Name: '||v.ename||'
Designation: '||v.designation);end loop;
end;
/
Procedure created.

OUTPUT:
SQL> exec emppro('7369');
Name: Smith Designation:Clerk

PL/SQL procedure successfully completed.

ii.)Create a stored procedure which takes employee’s job as parameter, and display the details of
the all employees with salary greater than max salary.
create or replace procedure max(s in varchar2)isbegin
for v in (select * from emp where sal>
all(select sal from emp where job=s))
loop
dbms_output.put_line('Name: '||v.ename||' Salary: '||v.sal);end
loop;
end;
/

27
Procedure created.OUTPUT:
SQL> exec max('Salesman');
Name: jones Salary: 2975
Name: blake Salary: 2850
PL/SQL procedure successfully completed.
iii.)Create a procedure that location for the given department number.create or
replace procedure loc(no in number)is
begin
for v in (select * from dept where deptno=no)
loop
dbms_output.put_line('Department Number: '||v.deptno||'Location: '||v.loc);end
loop;
end;/
Procedure created.

declare
no number;
begin
no:=&a;
loc(no);
end;/
Enter value for a: 10
old 4: no:=&a;
new 4: no:=10;
Department Number:10 Location: New york

PL/SQL procedure successfully completed.

Result:

Thus the above given queries were solved using procedures.

28
Ex.No:6 CREATION OF FUNCTIONS
Date:

Aim:

To create and implement the PL/SQL functions.

QUERIES:

i.)Write a function in PL/SQL to check the given number is even or odd.create or

replace function nochk(no number)return varchar2 is
status varchar2(20);
begin
if(mod(no,2)=0) then
status:='Even';
else
status:='Odd';

end if;
return status;
end;
/
Function created.declare
n number(2);
begin
n:=&a;
dbms_output.put_line('Number is '||nochk(n));
end;
/
Enter value for a: 19
old 4: n:=&a;
new 4: n:=19;
Number is Odd
PL/SQL procedure successfully completed.

29
 ii.)
SQL> create or replace function F1 (n number)
return number is
k number;
begin
k: = n*100;
return k;
end;
/
OUTPUT:
function created.

SQL> select F1(3) from dual;

F1 (3)
300
iii.) SQL> create or replace function F2 (a number)
return char is
o varchar 2(25):= ‘The number is odd’;e
varchar 2(25):= ‘The number is even’;r
number;
begin
r: = mod (a, 2);
if r=0 then return e;
else
return o;
endif;
end;
/

OUTPUT:
function created.
SQL> select F2 (9) from dual;F2 (9)
The number is odd.
iv.) Create a procedure to update the salary for the employee based on their job. The main
procedure should contain update statement. The main procedure should call second procedure
30
that contain rate of employee.
SQL>create or replace function rate_sub(s in varchar2)return number israte
number(2);
begin if(trim(s)='clerk')
thenrate:=0.2;
else
rate:=0.1;
end if;
return rate;
end;/
Function created.
Main Procedure:
create or replace procedure rate_main(s in varchar2) israte
number(2);
begin
for a in(select * from emp where job=s)
loop
rate:=rate_sub(s);
update emp set sal=sal+(sal*rate) where job=s;
dbms_output.put_line('Name: '||a.ename||' Salary: '||a.sal);end loop;
end;
/
Procedure created.
OUTPUT:
SQL> exec rate_main('salesman');Name:
Allen Salary: 1600
Name: Ward Salary: 1250
PL/SQL procedure successfully completed.

Result:

Thus the above given queries were solved using functions.

31
Ex.No:7 IMPLEMENTATION OF TRIGGER
Date:

Objective:

To implement the concept of Trigger.

Theory & Concepts:
Database Triggers:
Database triggers are procedures that are stored in the database and are implicitlyexecuted (fired)
when the contents of a table are changed.

Use of Database Triggers:

Database triggers support Oracle to provide a highly customized database management

system. Some of the uses to which the database triggers can be put to customize management
information in Oracle are as follows:-
• A Trigger can permit DML statements against a table any if they are issued, during regularbusiness
hours or on predetermined weekdays.
• A trigger can also be used to keep an audit trail of a table along with the operation
performed and the time on which the operation was performed.
• It can be used to prevent invalid transactions.

• Enforce complex security authorizations.

How to apply Database Triggers:A

trigger has three basic parts:

1. A triggering event or statement.

2. A trigger restriction

3. A trigger action.

Types of Triggers:

Using the various options , four types of triggers can be created:

1. Before Statement Trigger:

Before executing the triggering statement, the trigger action is executed.

32
2. Before Row Trigger:

Before modifying the each row affected by the triggering statement and before appropriate integrity
constraints, the trigger is executed if the trigger restriction either evaluated to TRUE or was not
included.
3. After Statement Trigger:

After executing the triggering statement and applying any deferred integrity constraints, the trigger
action is executed.
4. After row Trigger:

After modifying each row affected by the triggering statement and possibly applying
appropriate integrity constraints, the trigger action is executed for the current row if the trigger
restriction either evaluates to TRUE or was not included.

Elements in a Trigger:

• Trigger timing
o For table: BEFORE, AFTER
o For view: INSTEAD OF
• Trigger event: INSERT, UPDATE, OR DELETE
• Table name: On table, view
• Trigger Type: Row or statement
• When clause: Restricting condition
• Trigger body: PL/SQL block

“Before triggers” execute the trigger body before the triggering DML event on a table. These
are frequently used to determine whether that triggering statement should be allowed to
complete. This situation enables you to eliminate unnecessary processing of the triggering
statement and it eventual rollback in cases where an exception is raised inthe triggering action.
“After triggers” are used when the triggering statement is to be completed before the
triggering action and to perform a different action on the same triggering statement if a BEFORE
trigger is already present.
“Instead of Triggers” are used to provide a transparent way of modifying views that cannot
be modified directly through SQL DML statements because the view is not inherently
modifiable. You can write INSERT, UPDATE, and DELETE statements against the view. The
33
INSTEAD OF trigger works invisibly in the background performing the action coded in the
trigger body directly on the underlying tables.

Triggering user events:

o INSERT
o UPDATE
o DELETE
Trigger Components:

o Statement: The trigger body executes once for the triggering event. This is thedefault.
A statement trigger fires once, even if no rows are affected at all.
o Row: The trigger body executes once for each row affected by the triggering event.A row
trigger is not executed if the triggering event affects no rows.

Trigger Body:

The trigger body is a PL/SQL block or a call to a procedure.Syntax:

Create or replace Trigger<Triggername> {Before,After} {Delete, Insert, Update } On
<Tablename> For Each row when Condition
Declare
<Variable declarations>;
<Constant Declarations>;Begin
<PL/SQL> Subprogram Body;
Exception
Exception Pl/SQL block;
End;

How to Delete a Trigger:

The syntax for Deleting the Trigger is as follows:

Drop Trigger <Triggername>;

Queries and Output
sql> set serveroutput on;

34
PROGRAM (A):
Create a trigger which displays an error message when the employee records with salarygreater than
2000 are deleted.

SQL>create or replace trigger emptrig

before delete on emp referencing
old as nrow
for each row
when(nrow.salary >2000)
begin
raise_application_error(-20001,'Salary greater than 2000');end;
/
Trigger created.

SQL> delete from emp where salary>2000;delete

from emp where salary>2000

PROGRAM (B):
Create a trigger which displays message whenever anyone attempts to change dept table.

SQL>create or replace trigger dept trig

before update on dept
for each row
begin
dbms output.put_line('Location being Updated from '||(:old.loc)||' to '||(:new.loc));end;
/
Trigger created.

SQL> update dept set loc='Madurai' where deptno=10;

Location being Updated from Dindigul to Madurai

1 row updated.

35
PROGRAM (C):

INPUT:
EMP

EMPNO SALARY
10 0
11 1000
12 1200

SALDET

EMPNO DEPT
TOTAL10 11
900

SQL> create trigger triplet

after update of salary on emp for each row
begin
update saldet set total = total+:new.salary where
empno =:new.empno;
end;
/
OUTPUT:
trigger created.

SQL> update emp set salary=1000 where empno=10;

1 row updated.

36
 EMP

EMPNO SALARY
10 1000
11 1000
12 1200
SALDET
PROGRAM (D):

EMPNO DEPT TOTAL10 11

1900

SQL> create or replace trigger second before

update on emp for each rowbegin
if :new.salary>1000 or :new.dept =10;
then raise_application_error (-20001,’You are not allowed to insert.’);end if;
end;
/

OUTPUT:
Trigger created.

SQL> update emp set salary =1200 where dept =10;Error at line 1:
ORA -20001: You are not allowed to insert.

RESULT:

Thus triggers were implemented successfully.

37
EX No: 8 CREATION OF VIEWS, SYNONYMS, SEQUENCE, INDEXES, SAVE POINT.

AIM:
To create views, synonyms, sequences and indexes using DDL, DML and DCLstatements

DESCRIPTION:
Views
• A database view is a logical or virtual table based on a query. It is useful to think ofa view
as a stored query. Views are queried just like tables.
• A DBA or view owner can drop a view with the DROP VIEW command.
TYPES OF VIEWS
• Updatable views – Allow data manipulation
• Read only views – Do not allow data manipulationTO
CREATE THE TABLE ‘FVIEWS’
SQL> create table fviews( name varchar2(20),no number(5), sal number(5), dno
number(5));
Table created.
SQL> insert into fviews values('xxx',1,19000,11);1
row created.
SQL> insert into fviews values('aaa',2,19000,12);1
row created.
SQL> insert into fviews values('yyy',3,40000,13);1
row created.
SQL> select * from fviews;
NAME NO SAL DNO

xxx 1 19000 11
aaa 2 19000 12
yyy 3 40000 13
TO CREATE THE TABLE ‘DVIEWS’
SQL> create table dviews( dno number(5), dname varchar2(20));
Table created.
SQL> insert into dviews values(11,'x');
1 row created.
SQL> insert into dviews values(12,'y');1
38
row created.
SQL> select * from dviews;
DNO DNAME

11 x
12 y
CREATING THE VIEW ‘SVIEW’ ON ‘FVIEWS’ TABLE
SQL> create view sview as select name,no,sal,dno from fviews where dno=11;View
created.
SQL> select * from sview;
NAME NO SAL DNO

xxx 1 19000 11
Updates made on the view are reflected only on the table when the structure of the tableand the
view are not similar -- proof
SQL> insert into sview values ('zzz',4,20000,14);1
row created.
SQL> select * from sview;
NAME NO SAL DNO

xxx 1 19000 11
SQL> select * from fviews;
NAME NO SAL DNO

xxx 1 19000 11
aaa 2 19000 12
yyy 3 40000 13
zzz 4 20000 14
Updates made on the view are reflected on both the view and the table when the structureof the
table and the view are similar – proof
CREATING A VIEW ‘IVIEW’ FOR THE TABLE ‘FVIEWS’
SQL> create view iview as select * from fviews;
View created.
SQL> select * from iview;
39
NAME NO SAL DNO

xxx 1 19000 11
aaa 2 19000 12
yyy 3 40000 13
zzz 4 20000 14

PERFORMING UPDATE OPERATION

SQL> insert into iview values ('bbb',5,30000,15);1
row created.
SQL> select * from iview;
NAME NO SAL DNO

xxx 1 19000 11
bbb 5 30000 15
SQL> select * from fviews;
NAME NO SAL DNO

xxx 1 19000 11
aaa 2 19000 12
yyy 3 40000 13
zzz 4 20000 14
bbb 5 30000 15

CREATE A NEW VIEW ‘SSVIEW’ AND DROP THE VIEW

SQL> create view ssview( cusname,id) as select name, no from fviews where dno=12;View created.
SQL> select * from ssview;
CUSNAME ID

aaa 2
SQL> drop view ssview;
View dropped.
TO CREATE A VIEW ‘COMBO’ USING BOTH THE TABLES ‘FVIEWS’ AND ‘DVIEWS’
SQL> create view combo as select name,no,sal,dviews.dno,dname from fviews,dviewswhere
40
fviews.dno=dviews.dno;
View created.
SQL> select * from combo;
NAME NO SAL DNO DNAME

xxx 1 19000 11 x
aaa 2 19000 12 y
TO PERFORM MANIPULATIONS ON THIS VIEW
SQL> insert into combo values('ccc',12,1000,13,'x');insert
into combo values('ccc',12,1000,13,'x')
Synonyms
• A synonym is an alias, that is, a form of shorthand used to simplify the task of
referencing a database object.

• There are two categories of synonyms, public and private.

• A public synonym can be accessed by any system user.

• The individual creating a public synonym does not own the synonym – rather, it willbelong
to the PUBLIC user group that exists within Oracle.

• Private synonyms, on the other hand, belong to the system user that creates themand reside
in that user's schema.

• A system user can grant the privilege to use private synonyms that they own toother
system users.

• In order to create synonyms, we will need to have the CREATE SYNONYM privilege.

• This privilege will be granted to us by the DBA.

• We must have the CREATE PUBLIC SYNONYM privilege in order to create public
synonyms.

41
• If we own a synonym, we have the right to drop (delete) the synonym. The DROP
SYNONYM command is quite simple.

• DROP SYNONYM synonym_name;

• In order to drop a public synonym we must include the PUBLIC keyword in theDROP
SYNONYM command.

• In order to drop a public synonym, we must have the DROP PUBLIC SYNONYM
privilege.

• DROP PUBLIC SYNONYM synonym_name;

Examples:

SQL> select * from class;

NAME ID

anu 1

brindha 2

chinthiya 3

divya 4

ezhil 5

fairoz 7

hema 9

7 rows selected.

42
Create synonym:

SQL> create synonym c1 for class;

Synonym created.
SQL> insert into c1 values('kalai',20);
1 row created.

SQL> select * from class;

NAME ID

anu 1

brindha 2

chinthiya 3

divya 4

ezhil 5

fairoz 7

hema 9

kalai 20

8 rows selected.

SQL> select * from c1;

NAME ID

anu 1

brindha 2

chinthiya 3

43
divya 4

ezhil 5

fairoz 7

hema 9

kalai 20

8 rows selected.

SQL> insert into class values('Manu',21);

1 row created.

SQL> select * from c1;

NAME ID

anu 1

brindha 2

chinthiya 3

divya 4

ezhil 5

fairoz 7

hema 9

kalai 20

Manu 21

9 rows selected.

44
 Drop Synonym:
SQL> drop synonym c1;
Synonym dropped.

SQL> select * from c1;

select * from c1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Sequences
• Oracle provides the capability to generate sequences of unique numbers, and theyare called
sequences.

• Just like tables, views, indexes, and synonyms, a sequence is a type of databaseobject.

• Sequences are used to generate unique, sequential integer values that are used as
primary key values in database tables.

• The sequence of numbers can be generated in either ascending or descendingorder.

Creation of table:

SQL> create table class(name varchar(10),id number(10));Table

created.
Insert values into table:

SQL> insert into class values('&name',&id);Enter

value for name: anu
Enter value for id: 1

old 1: insert into class values('&name',&id)new

1: insert into class values('anu',1)
1 row created.

45
SQL> /

Enter value for name: brindha

Enter value for id: 02
old 1: insert into class values('&name',&id)new
1: insert into class values('brindha',02)
1 row created.
SQL> /

Enter value for name: chinthiya

Enter value for id: 03
old 1: insert into class values('&name',&id) new
1: insert into class values('chinthiya',03)
1 row created.

SQL> select * from class;

NAME ID

anu1
brindha 2
chinthiya3

Create Sequence:

SQL> create sequence s_12

start with 4
3 increment by 1
4 maxvalue 100
5 cycle;

Sequence created.
SQL> insert into class values('divya',s_1.nextval);1
row created.

46
SQL> select * from class;

NAME ID

anu 1

brindha 2

chinthiya 3

divya 4

Alter Sequence:

SQL> alter sequence s_12

increment by 2; Sequence
altered.
SQL> insert into class values('fairoz',s_1.nextval);
1 row created.

SQL> select * from class;

NAME ID

anu 1

brindha 2

chinthiya 3

divya 4

ezhil 5

fairoz 7

47
Drop Sequence:

SQL> drop sequence s_1;

Sequence dropped.

Indexes

• An index can be created in a table to find data more quickly and efficiently.
• The users cannot see the indexes; they are just used to speed up searches/queries.
• Updating a table with indexes takes more time than updating a table without; because
the indexes also need an update. So we should only create indexes on columns (and
tables) that will be frequently searched against.

Syntax:

Create Index:

CREATE INDEX index_name ON table_name (column_name)

SQL> create table splr(sname varchar(10),sid number(10),scity varchar(10));

Table created.

SQL> insert into splr values('hcl',01,'chennai');

1 row created.
SQL> insert into splr values('dell',04,'madurai');
1 row created.

SQL> insert into splr values('HP',02,'kovai');

1 row created.

SQL> insert into splr values('Lenovo',03,'trichy');

1 row created.

SQL> select * from splr;

48
 SNAME SID SCITY

hcl 1 chennai

dell 4 madurai

HP 2 kovai

Lenovo 3 trichy

SQL> create index sp1 on splr(sid);

Index created.

SQL> create index sp2 on splr(sid,scity);

Index created.
Drop Index:

SQL> drop index sp1;

Index dropped.

SQL> drop index sp2;

Index dropped.

CONCULSION:

Thus views, synonyms, sequences and indexes using DDL, DML and DCL statementswas
executedd successfully.

49
Ex.No:9 DCL & TCL Commands
Date:
AIM:

To study Data Control Language statements and Transactional Control Language

statements.

TRANSACTION CONTROL LANGUAGE:

All changes made to the database is defined as a transaction. It is logical unit ofwork. The
transaction can made permanent to a database only they are committed. A transaction begins with
an executable SQL statement.
The two transaction control commands are:

1. COMMIT

2. SAVEPOINT

3. ROLLBACK

1. COMMIT:

This command is used to make all transaction changes permanent to the database.

Syntax:

COMMIT ;

2. SAVEPOINT:

It is only a marker. Save point are used to divide a lengthy transaction into smaller

ones.
Syntax:
Savepoint id;3.
ROLLBACK:
Rollback commands undo all the changes made in the current transaction.

Syntax:

a. Rollback;

b. Rollback to Savepoint id1;

50
The first one will rollback (undo) the entire transaction and the last one will undo all
changes made after the creation of the save point id1.
DATA CONTROL LANGUAGE:

Data control language, the previous user with privileges commands the database object
(table, view, etc) of a user can’t be accessed by another user without the permission of a user.
The owner can allow other user to access his object as per his diversion the permission given by
a user to another is called privilege.
The Data Control Commands are:

i. GRANT Privilege

ii. REVOKE
PrivilegeTypes of Privilege:
A permission granted to execute the various data definition command like CreateTable, Create
Sequence, create session are called System Privilege.
Granting System Privilege:

Grant Command is used to give System Privilege to an oracle user.

Syntax:

GRANT system privilege TO user;

Object Privilege:

An Object Privilege enables a user to execute some commands on the databaseobject like table
view sequence etc.
• Object privileges vary from object to object.

• An owner has all the privileges on the object.

• An owner can give specific privileges on that owner’s object.

Some of the object privileges are
i. Alter

ii. Insert

iii. Update

iv. Delete

v. Select

51
Syntax:

GRANT object_priv [(columns)]

ON object
TO {user|role|PUBLIC}
[WITH GRANT
OPTION];

object_priv -- is an object privilege to be granted

ALL --specifies all object
privileges
columns --specifies the column from a table or view on which privileges are granted
ON object -- is the object on which the privileges are granted
TO --identifies to whom the privilege is granted
PUBLIC --grants object privileges to all users
WITH GRANT OPTION --allows the grantee to grant the object privileges to other users and roles.

Example:
• Grant query privileges on the EMPLOYEES table.
GRANT select ON employees TO itc20,itc30; Grant
succeeded.

• Grant privileges to update specific columns to users and roles. GRANT

update (department_name, location_id) ON departments TO itc43,
itc50;
Grant succeeded.

Give a user authority to pass along privileges.

GRANT select, insert ON departments TO itc45 WITH GRANT OPTION;Grant
succeeded.
Revoking the permission:
Permission granted to a user can also be taken back by the granter. This can bedone by the
REVOKE command.
• Privileges granted to others through the WITH GRANT OPTION clause are also

52
revoked.

Syntax:

REVOKE {privilege [, privilege...]|ALL}

ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];

Example:

REVOKE select, insert ON departments FROM itc20;Revoke

succeeded.

OUTPUT :

TCL

SQL> desc stud;

Name Null? Type

SNO NUMBER(3)

SNAME VARCHAR2(10)

BRANCH VARCHAR2(4)

SQL> insert into stud values(01,'Arun','cse');1

row created.
SQL> insert into stud values(02,'babu','IT');1
row created.
SQL> commit;
Commit complete.
SQL> select * from stud;
SNO SNAME BRAN

1 Arun cse
53
 2 babu IT

SQL> insert into stud values(03,'chitra','IT');

1 row created.
SQL> select * from stud;

SNO SNAME BRAN

1 Arun cse

2 babu IT

3 chitra IT
SQL> rollback;
Rollback complete.

SQL> select * from stud;

SNO SNAME BRAN

1 Arun cse

2 babu IT
SQL> savepoint s1;
Savepoint created.
SQL> insert into stud values(04,'Devi','CSE'); 1
row created.
SQL> select * from stud;
SNO SNAME BRAN

1 Arun cse

2 babu IT

4 Devi CSE
SQL> rollback to s1;
Rollback complete. SQL>
54
 select * from stud;
SNO SNAME BRAN

DCL

1 Arun cse

2 Babu

3 IT

User it2a40:

SQL> grant all on student to it2a36;

Grant succeeded.
User it2a36:

SQL> select * from it2a40.student;

ROLL_NO NAME DEPT SEM1 SEM2 SEM3

1 alex ece 92.3 90.5 89.3

2 bala ece 88.2 85 89.3

3 booba it 91.1 85 93

insert into it2a40.student values(&roll_no,'&name','&dept',&sem2,&sem2,&sem3);

Enter value for roll_no: 4

Enter value for name: chitra
Enter value for dept: it
Enter value for sem2: 88.5
Enter value for sem2: 90
Enter value for sem3: 92
old 1: insert into it2a40.student values(&roll_no,'&name','&dept',&sem2,&sem2,&sem3)new 1:
insert into it2a40.student values(4,'chitra','it',88.5,90,92)
1 row created.
55
SQL> select * from it2a40.student;

ROLL_NO NAME DEPT SEM1 SEM2 SEM3

4 chitra it 88.5 90 92
1 alex ece 92.3 90.5 89.3
2 bala ece 88.2 85 89.3
3 booba it 91.1 85 93

SQL> alter table it2a40.student add(avg number(10,3));

Table altered.
SQL> update it2a40.student set avg=91.2 where roll_no=1;1 row
updated.
SQL> update it2a40.student set avg=88.5 where roll_no=2;1 row
updated.
SQL> update it2a40.student set avg=89.9 where roll_no=3;1 row
updated.
SQL> update it2a40.student set avg=90.2 where roll_no=4;1 row
updated.
SQL> select * from it2a40.student;

ROLL_NO NAME DEPT SEM1 SEM2 SEM3 AVG

4 chitra it 88.5 90 92 90.2

1 alex ece 92. 90. 9 89.3 91.2

2 bala ece 88.2 85 89.3 88.5

3 booba it 91.1 85 93 89.9

SQL> delete from it2a40.student where roll_no=4;

1 row deleted.

56
SQL> select * from it2a40.student;

ROLL_NO NAME DEPT SEM1 SEM2 SEM3 AVG

1 alex ece 92.3 90.5 89.3 91.2

2 bala ece 88.2 85 89.3 88.5
3 booba it 91.1 85 93 89.9
User it2a40:

SQL> revoke delete on student from it2a36;

Revoke succeeded.
User it2a36:

SQL> delete from it2a40.student where roll_no=2;

delete from it2a40.student where roll_no=2
*
ERROR at line 1:
ORA-00942: table or view does not exist
User it2a40:
SQL> revoke all on student from it2a36;
Revoke succeeded.
User it2a36:

SQL> select * from it2a40.student;

select * from it2a40.student
ERROR at line 1:
ORA-00942: table or view does not exist
User it2a40:
SQL> grant select on student to it2a36;
Grant succeeded.
User - it2a36:

SQL> select * from it2a40.student;

ROLL_NO NAME DEPT SEM1 SEM2 SEM3 AVG

57
 1 alex ece 92.3 90.5 89.3 91.2

2 bala ece 88.2 85 89.3 88.5

3 booba it 91.1 85 93 89.9

User it2a40:

SQL> revoke select on student from it2a36;

Revoke succeeded.

User it2a36:

SQL> select * from it2a40.student;

select * from it2a40.student
*
ERROR at line 1:
ORA-00942: table or view does not exist

Result:

Thus the study of DCL commands and TCL commands was performed successfully.

58
Ex. No: 10 DATABASE DESIGN USING ER MODELING, NORMALIZATION.

AIM:
To Create a database using ER modeling, normalization.

ER diagram:

Chen Notation

• ORDER (OrderNum (key), OrderDate, SalesPerson)

ORDERITEMS (OrderNum (key)(fk) , ItemNum (key), PartNum, Quantity, Cost)
• In the above example, in the ORDERITEMS Relation: OrderNum is the
Foreign Key and OrderNum plus ItemNum is the CompositeKey.

Chen Notation

In the ORDER Relation: OrderNum is the Key.

59
Representing Relationships

• 1:1 Relationships. The key of one relation is stored in the second

relation. Look at example queries to determine which key is queried
most often.
• 1:N Relationships.
Parent - Relation on the "1" side.
Child - Relation on the "Many" side.
• Represent each Entity as a relation.
Copy the key of the parent into the child relation.
• CUSTOMER (CustomerID (key), Name, Address, ...)
ORDER (OrderNum (key), OrderDate, SalesPerson, CustomerID (fk))

• M:N Relationships. Many to Many relationships can not be directly

implemented in relations.
• Solution: Introduce a third Intersection relation and copy keys from
original two relations.

Chen Notation

• SUPPLIER (SupplierID (key), FirmName, Address, ...)

• COMPONENT (CompID (key), Description, ...) S
• SUPPLIER_COMPONENT (SupplierID (key), CompID(key))
• Note that this can also be shown in the ER diagram. Also, look for potentialadded
attributes in the intersection relation.

RESULT:

Thus the ER Database design using E-R model and Normalization was
implemented successfully.
60
61