AMRITA SCHOOL OF ENGINEERING, CHENNAI

AMRITA VISHWA VIDYAPEETHAM

CHENNAI - 601103
2024-25 Even Semester

LAB REPORT

DBMS Laboratory
23CCE284
CCE
II year/IV Sem

Student Name SRI SUBHASH P

Register Number CH.EN.U4CCE23049

Degree/Program B.TECH CCE

Semester 4TH SEM

Academic Year 2024-2025 Even

Exp1 : DATA DEFINITION LANGUAGE
AIM:
To Execute Data Definition Language (DDL) commands available in RDBMS by using SQL.
PROCEDURE:
It is used to communicate with database. DDL is used to: o Create an object o Alter the structure
of an object o To drop the object created. The commands used are: o Create o Alter o Drop o
Truncate
CREATE TABLE STUDENT_MASTER (
ROLL_NO INT PRIMARY KEY,
NAME VARCHAR(15),
SEX VARCHAR(6),
DOB DATE,
DOAD DATE,
CLASS VARCHAR(6),
GUARDIAN VARCHAR(30),
ADDRESS VARCHAR(100)
);

DESC STUDENT_MASTER;

ALTER TABLE STUDENT_MASTER MODIFY COLUMN NAME VARCHAR(25);

DESC STUDENT_MASTER;

ALTER TABLE STUDENT_MASTER ADD COLUMN TEL_NO INT(7);

DESC STUDENT_MASTER;

ALTER TABLE STUDENT_MASTER DROP COLUMN GUARDIAN;

DESC STUDENT_MASTER;
TRUNCATE TABLE STUDENT_MASTER;

DROP TABLE STUDENT_MASTER;

RESULT:
Thus the Data Definition Language (DDL) commands were studied and executed.
2. DATA MANIPULATION LANGUAGE

AIM:
To Execute Data Manipulation Language (DML) commands available in RDBMS by using SQL.
PROCEDURE:
DML commands are the most frequently used SQL commands and is used to query and manipulate
the existing database objects. Some of the commands are o Insert o Select o Update o Delete
CREATE TABLE STU (
ID VARCHAR(5),
NAME VARCHAR(20),
CITY VARCHAR(20),
MARKS INT
);

INSERT INTO STU VALUES ('S3', 'Sup3', 'Blore', 10);

SELECT * FROM STU;

INSERT INTO STU VALUES

('S1', 'Rahul', 'Mumbai', 85),
('S2', 'Anita', 'Delhi', 90),
('S3', 'Sup3', 'Blore', 10),
('S4', 'Vikram', 'Chennai', 75),
('S5', 'Neha', 'Kolkata', 88);
SELECT * FROM STU;

INSERT INTO STU (ID, NAME, CITY)

VALUES ('S6', 'Arjun', 'Pune');

INSERT INTO STU (ID, NAME)

VALUES ('S7', 'Pooja');

SELECT NAME, CITY FROM STU;

SELECT DISTINCT CITY FROM STU;

SELECT NAME, CITY FROM STU WHERE CITY = 'Delhi';

SELECT NAME, MARKS FROM STU WHERE MARKS > 80;

SELECT NAME, MARKS FROM STU ORDER BY MARKS DESC;

UPDATE STU
SET MARKS = 50
WHERE ID = 'S3';

UPDATE STU
SET CITY = 'Bangalore', MARKS = 95
WHERE ID = 'S1';

UPDATE STU
SET MARKS = 60;

UPDATE STU
SET MARKS = MARKS + 5
WHERE MARKS < 80;
SELECT * FROM STU;

DELETE FROM STU

WHERE ID = 'S3';

DELETE FROM STU

WHERE CITY = 'Delhi';

DELETE FROM STU;

SELECT * FROM STU;

Problem 1:
CREATE TABLE dept (
DNO INT PRIMARY KEY,
DNAME VARCHAR(50),
 LOCATION VARCHAR(50)
);

INSERT INTO dept VALUES (10, 'MANAGEMENT', 'MAIN BLOCK');

INSERT INTO dept VALUES (20, 'DEVELOPMENT', 'HEAD OFFICE');

INSERT INTO dept VALUES (30, 'MAINTAINANCE', 'MAIN BLOCK');

INSERT INTO dept VALUES (40, 'TRANSPORT', 'ADMIN BLOCK');
INSERT INTO dept VALUES (50, 'SALES', 'HEAD OFFICE');

INSERT INTO dept (DNO, DNAME) VALUES (60, 'RESEARCH');

INSERT INTO dept (DNO, DNAME) VALUES (70, 'HR');

SELECT * FROM dept;

UPDATE dept
SET LOCATION = 'ADMIN BLOCK'
WHERE DNAME = 'RESEARCH';

SELECT * FROM dept;

DELETE FROM dept WHERE DNAME = 'HR';

SELECT * FROM dept;

RESULT: Thus the Data Manipulation Language (DML) commands were studied and executed.
3.experiment
CREATION OF A DATABASE AND WRITING SQL QUERIES TO RETRIEVE INFORMATION FROM
THE DATABASE
AIM: To Create a Database and Writing SQL Queries to Retrieve Information From The Database.
Sql:
CREATE TABLE emp (
EMPNO INT PRIMARY KEY,
ENAME VARCHAR(20),
JOB VARCHAR(20),
MGR INT,
HIREDATE DATE,
SAL INT,
COMM INT,
DEPTN INT
);

INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 20);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
SELECT * FROM emp;

SELECT empno, ename, sal FROM emp;

SELECT empno, ename, sal FROM emp
WHERE sal BETWEEN 2500 AND 5000;

SELECT ENAME, JOB, SAL, DEPTN FROM emp WHERE SAL NOT BETWEEN 1500 AND 5000;
SELECT EMPNO, ENAME, SAL FROM emp WHERE SAL IN (800, 5000);

SELECT EMPNO, ENAME, SAL FROM emp WHERE SAL NOT IN (800, 1250, 3000, 5000);

SELECT EMPNO, ENAME, SAL FROM emp WHERE COMM IS NULL;

SELECT EMPNO, ENAME, SAL FROM emp WHERE COMM IS NOT NULL AND COMM <> 0;

SELECT EMPNO, ENAME, JOB, SAL FROM emp WHERE ENAME LIKE 'S%';

SELECT EMPNO, ENAME, JOB, SAL FROM emp WHERE ENAME NOT LIKE 'S%';
SELECT EMPNO, ENAME, JOB, SAL FROM emp WHERE SAL > 2500;

SELECT EMPNO, ENAME, JOB, SAL FROM emp WHERE SAL < 2500;
SELECT EMPNO, ENAME, JOB, SAL FROM emp ORDER BY SAL;

SELECT EMPNO, ENAME, JOB, SAL FROM emp ORDER BY SAL DESC;
Aggregate function:
SELECT COUNT(*) FROM EMP;

SELECT SUM(SAL) FROM EMP;

SELECT AVG(SAL) FROM EMP;

SELECT MAX(SAL) FROM EMP;

SELECT MIN(SAL) FROM EMP;

Grouping:
SELECT DEPTN, MAX(SAL) FROM EMP GROUP BY DEPTN;

SELECT DEPTN, MIN(SAL) FROM EMP GROUP BY DEPTN;

SELECT DEPTN, MAX(SAL)
FROM EMP
GROUP BY DEPTN
HAVING MAX(SAL) > 3000;

SELECT DEPTN, MIN(SAL)

FROM EMP
GROUP BY DEPTN
HAVING MIN(SAL) > 1000;

SELECT DEPTN, COUNT(*)

FROM EMP
GROUP BY DEPTN;
SELECT COMMANDS USING SUBQUERIES (APPLYING IN, ALL, ANY, EXISTS, NOT EXISTS)
SELECT EMPNO, ENAME
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP);

SELECT EMPNO, ENAME, DEPTN

FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTN);

SELECT EMPNO, ENAME, JOB, SAL

FROM EMP
WHERE SAL > ALL (SELECT AVG(SAL) FROM EMP GROUP BY DEPTN);
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE SAL < ALL (SELECT AVG(SAL) FROM EMP GROUP BY DEPTN);

SELECT ENAME, JOB, SAL

FROM EMP
WHERE SAL > ANY (SELECT SAL FROM EMP WHERE JOB = 'CLERK');
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL < ANY (SELECT SAL FROM EMP WHERE JOB = 'CLERK');

SELECT ENAME, JOB, SAL

FROM EMP
WHERE SAL = ANY (SELECT SAL FROM EMP WHERE JOB = 'CLERK');
SELECT DNO, DNAME
FROM DEPT D
WHERE EXISTS (SELECT * FROM EMP E WHERE D.DNO = E.DEPTN);

SELECT DNO, DNAME

FROM DEPT D
WHERE NOT EXISTS (SELECT * FROM EMP E WHERE D.DNO = E.DEPTN);

SELECT COMMANDS USING (UNION, INTERSECT, MINUS)

SELECT SNAME FROM S1
UNION
SELECT SNAME FROM S2;

SELECT SNAME
FROM S1
INNER JOIN S2 USING (SNAME);
SELECT SNAME
FROM S1
LEFT JOIN S2 USING (SNAME)
WHERE S2.SNAME IS NULL;

RESULT: Thus the Program Creation of a Database and Writing SQL Queries to Retrieve
Information from the Database is executed successfully.
Ex.No.4

BUILT IN FUNCTIONS
AIM:
To execute built in functions available in Oracle by using SQL

PROCEDURE:
The following are the built-in functions in Oracle. All of its operations were
discussed in this experiment.
 CONVERSION FUNCTION
 STRING FUNCTION
 DATE FUNCTION
 NUMBER FUNCTION

CONVERSION FUNCTIONS:

 SELECT DATE_FORMAT(NOW(), '%W') AS Day_of_Week;

 SELECT DATE_FORMAT(NOW(), '%M') AS Month_Name;

 SELECT DATE_FORMAT(NOW(), '%Y') AS Year;

STRING FUNCTIONS
 SELECT CONCAT('ORACLE', 'CORPORATION');

 SELECT LPAD('ORACLE', 15, '*');

 SELECT RPAD('ORACLE', 15, '*');

 SELECT TRIM(LEADING 'S' FROM 'SSMITHSS');

 SELECT TRIM(TRAILING 'S' FROM 'SSMITHSS');

 SELECT LOWER('DBMS');

 SELECT UPPER('dbms');

 SELECT CONCAT(UCASE(LEFT('oracle',1)), LCASE(SUBSTRING('oracle',2)));

 SELECT LENGTH('DATABASE');
 SELECT SUBSTRING('ABCDEFGHIJ', 3, 4);

 SELECT LOCATE('OR', 'CORPORATE FLOOR', 3);

 SELECT REPLACE('JACK', 'J', 'B');

  SELECT REPLACE('JACK AND JUE', 'J', 'BL');

DATE FUNCTIONS

 SELECT NOW();

 SELECT DATE_ADD(NOW(), INTERVAL (7 - WEEKDAY(NOW()) + 3) % 7 DAY);

 SELECT DATE_ADD(NOW(), INTERVAL 2 MONTH);

 SELECT LAST_DAY(NOW());

 SELECT TIMESTAMPDIFF(MONTH, '2009-02-12', NOW());

 SELECT LEAST('2007-01-10', '2007-10-12');

  SELECT GREATEST('2007-01-10', '2007-10-12');

NUMBER FUNCTIONS
 SELECT ROUND(12.36), ROUND(14.63);

 SELECT FLOOR(12.87), FLOOR(11.23);

 SELECT CEIL(16.23), CEIL(12.78);

 SELECT TRUNCATE(56.63, 0);

 SELECT MOD(11, 4);

 SELECT POW(2, 3);

 SELECT SIGN(0), SIGN(34), SIGN(-56);

 SELECT ABS(12), ABS(-89);

 SELECT SQRT(25);
Exp.No: 7
TCL COMMANDS
AIM:
To Execute Transaction Control Language commands available in oracle by using SQL
Code :
Table employee:
CREATE TABLE employee ( ENO INT(5), ENAME CHAR(4), DEPTNO INT(3), SAL
INT(5), DESIG CHAR(6), DESIGNO INT(4) );
Inserting values :
INSERT INTO employee (ENO, ENAME, DEPTNO, SAL, DESIG, DESIGNO)
-> VALUES
-> (10001, 'John', 101, 50000, 'Mng', 2001),
-> (10002, 'Jane', 102, 45000, 'Clerk', 2002),
-> (10003, 'Mike', 103, 48000, 'Anlst', 2003),
-> (10004, 'Anna', 104, 55000, 'Lead', 2004);
Rollback command :
Code : mysql> rollback;

Commit command :
Code : mysql> commit;

After changes :
Save point :
Code : mysql> SAVEPOINT sp2;

RESULT:
Thus the Program TCL Commands is executed successfully
Exp.No.8
CREATING RELATIONSHIP BETWEEN THE DATABASES
Aim : To Creating relationship between the databases in Oracle using Structured Query
Language commands
PROBLEM STATEMENT:
 A branch contain many account holder
 A branch provide more than one Loan
 A Loan can be availed by more than customer.
 A customer can get more than one Loan.
 A customer can have more than one account

 A account can have more than one customer

Code :
Branch :
CREATE TABLE branch ( branch_name VARCHAR2(30) PRIMARY KEY, branch_city
VARCHAR2(20), assets NUMBER );
Customer :
CREATE TABLE customer ( customer_id VARCHAR2(10) PRIMARY KEY,
customer_name VARCHAR2(20), customer_street VARCHAR2(15), customer_city
VARCHAR2(15) );
Account :
CREATE TABLE account ( account_no VARCHAR2(10) PRIMARY KEY, branch_name
VARCHAR2(30) REFERENCES branch(branch_name), balance NUMBER );
Loan :
CREATE TABLE loan ( loan_no VARCHAR2(20) PRIMARY KEY, branch_name
VARCHAR2(30) REFERENCES branch(branch_name), amount NUMBER );
Borrower :
CREATE TABLE borrower ( customer_id VARCHAR2(11) REFERENCES
customer(customer_id), loan_no VARCHAR2(4) REFERENCES loan(loan_no) );
Depositer :
CREATE TABLE depositor ( customer_id VARCHAR2(11) REFERENCES
customer(customer_id), account_no VARCHAR2(11) REFERENCES account(account_no)
);

Inserting data :
-- Inserting data into Customer Table
INSERT INTO customer VALUES ('C-101', 'Arun', '1 st street', 'Chrompet');
INSERT INTO customer VALUES ('C-102', 'Sekar', 'Nehru street', 'Tambaram');
INSERT INTO customer VALUES ('C-103', 'Prabhu', 'Water tank', 'Pallavaram');
INSERT INTO customer VALUES ('C-104', 'Selvam', 'Gandhi street', 'Velachery');
INSERT INTO customer VALUES ('C-105', 'Pandi', 'Periyar street', 'T-Nagar');
INSERT INTO customer VALUES ('C-106', 'Kathir', 'Mgr street', 'Chrompet');

-- Inserting data into Branch Table

INSERT INTO branch VALUES ('Adayar', 'Chennai-13', 400000);
INSERT INTO branch VALUES ('Chrompet', 'Chennai-10', 300000);
INSERT INTO branch VALUES ('Guindy', 'Chennai-9', 500000);
INSERT INTO branch VALUES ('Saidapet', 'Chennai-11', 300000);
INSERT INTO branch VALUES ('Tnagar', 'Chennai-14', 700000);

-- Inserting data into Account Table

INSERT INTO account VALUES ('A-101', 'Adayar', 7000);
INSERT INTO account VALUES ('A-102', 'Chrompet', 12000);
INSERT INTO account VALUES ('A-103', 'Guindy', 8000);
INSERT INTO account VALUES ('A-104', 'Saidapet', 5000);
INSERT INTO account VALUES ('A-105', 'Tnagar', 10000);
INSERT INTO account VALUES ('A-106', 'Tnagar', 20000);
INSERT INTO account VALUES ('A-107', 'Adayar', 9000);
INSERT INTO account VALUES ('A-108', 'Guindy', 15000);

-- Inserting data into Loan Table

INSERT INTO loan VALUES ('L-101', 'Adayar', 500000);
INSERT INTO loan VALUES ('L-102', 'Chrompet', 700000);
INSERT INTO loan VALUES ('L-103', 'Saidapet', 600000);
INSERT INTO loan VALUES ('L-104', 'Tnagar', 900000);
INSERT INTO loan VALUES ('L-105', 'Saidapet', 200000);

-- Inserting data into Borrower Table

INSERT INTO borrower VALUES ('C-101', 'L-105');
INSERT INTO borrower VALUES ('C-102', 'L-103');
INSERT INTO borrower VALUES ('C-104', 'L-101');
INSERT INTO borrower VALUES ('C-102', 'L-104');
INSERT INTO borrower VALUES ('C-106', 'L-102');

-- Inserting data into Depositor Table

INSERT INTO depositor VALUES ('C-101', 'A-107');
INSERT INTO depositor VALUES ('C-103', 'A-101');
INSERT INTO depositor VALUES ('C-104', 'A-104');
INSERT INTO depositor VALUES ('C-103', 'A-102');
INSERT INTO depositor VALUES ('C-102', 'A-103');
INSERT INTO depositor VALUES ('C-105', 'A-105');
INSERT INTO depositor VALUES ('C-108', 'A-106');
INSERT INTO depositor VALUES ('C-107', 'A-108');

Problem 1: For all customer who have loan from the bank find their ID’s , loan number
and loan amount.(Join)
Code :
SELECT b.customer_id, b.loan_no, l.amount
FROM borrower b
JOIN loan l ON b.loan_no = l.loan_no;
Output :
Problem 2: For all customers who have loan at tnagar branch find their ID’s,loan ID,loan
amount.(Join)
Code :
SELECT b.customer_id, b.loan_no, l.amount
FROM borrower b
JOIN loan l ON b.loan_no = l.loan_no
WHERE l.branch_name = 'tnagar';
Output :

Problem 3: Find the number of depositor at each branch.(Join)

Code :
SELECT a.branch_name, COUNT(DISTINCT d.customer_id) AS num_depositors
FROM depositor d
JOIN account a ON d.account_no = a.account_no
GROUP BY a.branch_name;
Output :

RESULT:
Thus the Program Creating Relationship between the Databases is executed
successfully
 STUDY OF PL/SQL BLOCK

Ex.No.10

AIM:

To study the working principles of PL/SQL block

THEORY:

PL/SQL Block consists of three sections:

 The Declaration section (optional).

 The Execution section (mandatory).

 The Exception (or Error) Handling section (optional).

Declaration Section:

The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE.
This section is optional and is used to declare any placeholders like variables, constants,
records and cursors, which are used to manipulate data in the execution section.
Placeholders may be any of Variables, Constants and Records, which stores data
temporarily. Cursors are also declared in this section.

Execution Section:

The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and
ends with END. This is a mandatory section and is the section where the program logic is
written to perform any task. The programmatic constructs like loops, conditional
statement and SQL statements form the part of execution section. Sequence of statements
can be executed on satisfying certain condition. If statements are being used and different
forms of if are:

Exception Section:

The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION.
This section is optional. Any errors in the program can be handled in this section, so that
the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that
cannot be handled, the Block terminates abruptly with errors. Every statement in the
above three sections must end with a semicolon; PL/SQL blocks can be nested within
other PL/SQL blocks. Comments can be used to document code.

Sample PL/SQL Block Looks

DECLARE

Variable declaration
BEGIN

Program Execution

EXCEPTION

Exception handling

END;

BRANCHING in PL/SQL:

Sequence of statements can be executed on satisfying certain condition. If statements are

being used and different forms of if are:

 Simple IF

 ELSIF

SIMPLE IF:

Syntax:

IF condition THEN statement1; statement2;

END IF;

IF-THEN-ELSE STATEMENT:

Syntax:

IF condition THEN statement1;

ELSE statement2;

END IF;

ELSIF STATEMENTS:

Syntax:

IF condition1 THEN statement1;

ELSIF condition2 THEN statement2;

ELSIF condition3 THEN statement3;

ELSE statementn;

END IF;
ITERATIONS IN PL/SQL

Sequence of statements can be executed any number of times using loop construct. It is
broadly classified into:

 Simple Loop

 For Loop

 While Loop

SIMPLE LOOP

Syntax:

LOOP

statement

1;

EXIT [ WHEN

Condition];

END LOOP;

WHILE LOOP

Syntax

WHILE condition

LOOP

statement1;

statement2;

END LOOP;

FOR LOOP

Syntax:

FOR counter IN
 LowerBound..UpperBound

LOOP

statement1;

statement2;

END

LOOP;

Problem Statement:

Write a PL/SQL block that accepts a student's marks as input, determines the grade based
on the following criteria, and handles any exceptions:

 Above 90: Grade A

 80 - 90: Grade B
 70 - 79: Grade C
 60 - 69: Grade D
 Below 60: Grade F

If the input is invalid (negative or greater than 100), handle the exception.

PL/SQL Code Solution:

DECLARE

marks NUMBER;

grade CHAR(1);

BEGIN

-- Accepting Marks

marks := &Enter_Marks;

-- Checking for valid marks range

IF marks < 0 OR marks > 100 THEN

RAISE_APPLICATION_ERROR(-20001, 'Invalid marks entered!');

 END IF;

-- Assigning grade based on marks

IF marks > 90 THEN

grade := 'A';

ELSIF marks BETWEEN 80 AND 90 THEN

grade := 'B';

ELSIF marks BETWEEN 70 AND 79 THEN

grade := 'C';

ELSIF marks BETWEEN 60 AND 69 THEN

grade := 'D';

ELSE

grade := 'F';

END IF;

-- Display the grade

DBMS_OUTPUT.PUT_LINE('Student Grade: ' || grade);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END;

Sample Output:
Enter_Marks: 85

Student Grade: B

Enter_Marks: 105

Error: ORA-20001: Invalid marks entered!

RESULT:

Thus the Program Study of PL/SQL Block is executed successfully

 WRITE A PLISQL BLOCK TO SATISFY SOME CONDITIONS BY

ACCEPTING INPUT FROM THE USER

Ex.No.11

Date:

AIM:

Write a PL/SQL block to satisfy some conditions by accepting input from the user

using oracle

PROGRAMS:

1. SUM AND PRODUCT BETWEEN TWO NUMBERS

declare

startno number(4);

endno number (4);

s number:=0;

i number:=0;

p number:=0;

begin

startno:=&no;

endno:=&no;

for i in startno..endno

loop

s:=s+i;

p:=p*i;

end loop;

dbms_output.put_line('sum='||s);

dbms_output.put_line('product='||p);
end;

OUTPUT:

SQL> set serveroutput on;

SQL> @sum&pdt.sql;

Enter value for no: 1

old 8: startno:=&no;

new 8:

Enter value for no: 5

old 9: endno:=&no;

new 9: endno:=5;

PL/SQL procedure successfully completed.

2. BIGGEST OF THREE NUMBERS

declare

a number;

b number;

c number;

begin

a:=&a;

b:=&b;

c:=&c;

if a=b and b=c and c=a then

dbms_output.put_line('all are equal) ;

elsif a>b and then

dbms_output.put_line(all'is greatest no.); then

elsif
dbms_output.put_line(bll'is greatest no.) ;

else

dbms_output.put_line(cll'is greatest no.

end if;

end;

OUTPUT:

SQL> set serveroutput on;

SQL> @big.sql;

Enter value for a: 56

old 6: a:=&a;

new 6:

Enter value for b: 43

old 7:

new 7:

Enter value for c: 12

old 8: c:=&c;

new 8: c:=12;

PL/SQL procedure successfully completed.

3. REVERSE A NUMBER

declare

a number;

rev number;

d number;

begin

a:=&a;

rev:=();
while a>()

loop

d:=mod(a,l());

(all 0);

end loop;

dbms_output.put_line('reverse no. is 'llrev);

end;

OUTPUT:

SQL> set serveroutput on;

SQL> @reverse.sql;

Enter value for a: 369

old 6: a:=&a;

new 6: a:=369;

PL/SQL procedure successfully completed.

4. GIVEN NUMBER IS PRIME OR NOT

declare

a number;

c number:=();

i number;

begin

a:=&a;

for i in l..a

loop

if mod(a,i)=() then

c:=C+1;

end if;
end loop;

if c=2 then dbms_output.put_line(all'is a prime no.) ;

else

dbms_output.put_line(all'is not a prime no.)

end if;

end;

OUTPUT:

SQL> set serveroutput on;

SQL> @prime.sql;

Enter value for a: 57

old 6: a:=&a;

new 6: a:=57;

57is not a prime no.

PL/SQL procedure successfull completed.

5.FACTORIAL OF GIVEN NUMBER

declare

n number;

i number;

begin

for i in l..n loop

p:=p*i;

end loop;

dbms_output.put_line(n II' !

= 'Ilp);

end;
OUTPUT:

SQL> @fact.sql;

Enter value for n: 5

old 6: n:=&n;

new 6: n:=5;

5! = 120

PL/SQL procedure successfully completed.

6. FIND THE DEPARTMENT NAME FROM DEPARTMENT TABLE USING

PL/SQL BLOCK

To execute this program need to create a table called department, which consist of the

following fields deptid,dname,location.

After the table creation and values inserted the table become,

SQL>Select * from Dept;

Deptid dname location

101 cse main block

102 it I block

The PL/SQL program is,

1 declare

2 deptname varchar2(10);

3 begin

4 select department_name into deptname from employees where

department_id=&departmentid;

5 dbms_output.put_line(The Department name is 'Ildeptname) ;

6 end;

OUTPUT:

SQL>@prog.sql;

Enter value for departmentid: 101

old 4: select department_name into deptname from employees where

department_id=&departmentid;

new 4: select department_name into deptname from employees where

department_id=l()l;

The Department name is CSE

PL/SQL procedure successfully completed.

RESULT:

Thus the program PL/SQL block to satisfy some conditions by accepting input from the
user is

executed successfully
 WRITE A PL/SQL BLOCK THAT HANDLES ALL TYPES OF

EXCEPTIONS

Exp.No.12

Date:

AIM:

To implement and execute PIJSQL Block that handles all types of exceptions in

Oracle Database using Procedural Language concepts.

EXCEPTIONS:

In PL/SQL, the user can catch certain runtime errors. Exceptions can be internally

defined by Oracle or the user. Exceptions are used to handle errors that occur in your
PL/SQL

code. A PL/SQL block contains an EXCEPTION block to handle exception.

There are three types of exceptions:

I. Predefined Oracle errors

2. Undefined Oracle errors

3. User-defined errors

The different parts of the exception.

I. Declare the exception.

2. Raise an exception.

3. Handle the exception.

An exception has four attributes:

I. Name provides a short description of the problem.

2. Type identifies the area of the error.

3. Exception Code gives a numeric representation of the exception.

4. Error message provides additional information about the exception.

The predefined divide-by-zero exception has the following values for the attributes:

1. Name = ZERO_DIVIDE

2. Type = ORA (from the Oracle engine)

3. Exception Code = C01476

4. Error message = divisor is equal to zero

TYPES OF MORE COMMONLY USED EXCEPTIONS

NO_DATA_FOUND Singleton SELECT statement returned no data.

TOO_MANY_ROWS Singleton SELECT statement returned more
than one row of data.
INVALID_CURSOR Illegal cursor operation occurred.
VALUE_ERROR Arithmetic, conversion, or truncation error
occurred.
INVALID_NUMBER Conversion of a number to a character string
failed.
ZERO_DIVIDE Attempted to divide by zero.
DUP_VAL_ON_INDEX Attempted to insert a duplicate value into a
column that has a unique index
CURSOR_ALREADY_OPEN Attempted to open a cursor that was
previously opened.
NOT_LOGGED_ON A database call was made without being logged
into Oracle.
TRANSACTION_BACKED_OUT Usually raised when a remote portion of a
transaction is rolled back.
LOGIN_DENIED Login to Oracle failed.
PROGRAM_ERROR If PL/SQL encounters an internal problem.
STORAGE_ERROR If PL/SQL runs out of memory or if memory is
corrupted.
TIMEOUT_ON_RESOURCE Timeout occurred while Oracle was waiting for
a resource.
OTHERS For all of the rest.

ZERO_DIVIDE EXCEPTION

Example: PL/SQL Program without Exception Handling.

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE(1 / 0);

3 END;

4/

BEGIN
ERROR at line 1:

ORA-01476: divisor is equal to zero

ORA-06512: at line 2

Example: PL/SQL Program using Exception Handling.

SQL>BEGIN

3 EXCEPTION

4 WHEN ZERO_DIVIDE THEN

5 by zero);

6 END;

7/

OUTPUT:

Division by zero

PL/SQL procedure successfully completed.

INVALID_NUMBER EXCEPTION

To Execute this exception need to create a table called department, which consist of

the following fields and corresponding data types are deptid number,dname

varchar2 (I O) ,location varchar? (15).

After the table creation and values inserted the table become,

SQL>Se1ect * from Dept;

Deptid dname location

101 cse main block

102 it 1 block

The PIJSQL program become,

SQL>BEGIN

2 INSERT INTO dept(deptid) VALUES ('1010;

3 EXCEPTION

4 WHEN INVALID_NUMBER THEN

5 DBMS_OUTPUT.PUT_LINE('Conversion of string to number failed');

6 end; /

OUTPUT:

Conversion of string to number failed

PL/SQL procedure successfully completed.

NO_DATA_FOUND EXCEPTION

The PL/SQL program become,

SQL>Declare

Deptname varchar2(15);

BEGIN

select dname into deptname from dept where deptid=103;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('There is no such a department’);

end; /

OUTPUT:

There is no such a department

PL/SQL procedure successfully completed.

OTHERS EXCEPTION

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE(1 / 0);

3 EXCEPTION
4 WHEN OTHERS THEN

5 DBMS_OUTPUT.PUT_LINE('An exception occurred);

6* END;

7/

OUTPUT:

An exception occurred

PL/SQL procedure successfully completed.

RESULT:

Thus the program PIJSQL Block that handles all types of Exceptions is executed

Successfully
 Exp.No.13 CREATION OF PROCEDURES

Date:

AIM:
To implement and execute Procedures in Oracle Database using Procedural Language
concepts.

PROCEDURES:

1. A procedure is a subprogram used to perform an action.

2. The Replace command recreates the procedure if it already exists.

3 MODES:

1. IN – Requires a value for the argument to be specified at the time of execution of

the procedure.
2. OUT – Passes a value back to the calling program.
3. IN OUT – Requires a value to be specified when calling the procedure, and
the procedure will pass a value back to the calling program.

SYNTAX:

Create or replace procedure <procedure_name> (argument {in, out, in out}

data_type) {is, as}

Variable declaration

Begin

PL/SQL Subprogram body.

 Exception

Exception PL/SQL Block.

End;

Example: In Procedure
Write a procedure to include a phone number for the customers in the customer table.

Create or replace procedure pro(phoneno in number, cust_id in varchar2) is

a varchar2(20);

b exception;
 begin

select customer_name into a from customer where customer_id =

cust_id; if a is null then

raise

b; else

update customer set phone_no = phoneno where customer_id =

cust_id; end if;

exception

when no_data_found then

dbms_output.put_line('no such data');

when b then

dbms_output.put_line('customer name is null');

end;

SQL> /

Procedure created.

Before procedure execution, the customer table looks like this:

SQL> select * from customer;

CUSTOMER_ID CUSTOMER_NAME CUSTOMER_CITY CUSTOMER_DOB

PHONE_NO

cus_101 suresh chennai-45 12-JAN-86

cus_102 selva chennai-20 05-NOV-87

cus_103 prem chennai-17 09-JAN-88
 cus_104 javid chennai-45 27-AUG-77

cus_105 pradeep chennai-15 08-MAR-88

cus_106 gopal chennai-43 06-MAY-84

cus_107 raja chennai-43 07-MAY-86

cus_108 krishnan chennai-15 05-SEP-00

cus_109 mohammed chennai-32 03-

APR-99

9 rows selected.

SQL> execute pro(9940065845, 'cus_101');

PL/SQL procedure successfully completed.

After the procedure has executed, the table is updated as follows:

SQL> select * from customer;

CUSTOMER_ID CUSTOMER_NAME CUSTOMER_CITY CUSTOMER_DOB
PHONE_NO

cus_101 suresh chennai-45 12-JAN-86

994006584
5
cus_102 selva chennai-20 05-NOV-87

cus_103 prem chennai-17 09-JAN-88

cus_104 javid chennai-45 27-AUG-77

cus_105 pradeep chennai-15 08-MAR-88

cus_106 gopal chennai-43 06-MAY-84

cus_107 raja chennai-43 07-MAY-86

cus_108 krishnan chennai-15 05-SEP-00

cus_109 mohammed chennai-32 03-APR-99

9 rows selected.

SQL> execute pro(9940065845, 'cus_110');

no such data

PL/SQL procedure successfully completed.

Example: Out Procedure

Consider the below table:

SQL> select * from dept131;

DEPTNO DNAME LOC

10 manager chennai

20 sales mumbai

30 executer trichy

40 product tuty

50 marketing hyderabad
Program:

create or replace procedure outpro(dno number, depname out varchar2, city out
varchar2) as

begin

select dname, loc into depname, city from dept131 where deptno =

dno; end;
Output:

SQL> set serveroutput on

SQL> @outpro.sql;

Procedure created.

Program:

declare

varchar2(20);

varchar2(30);

ch number;

begin

ch := &ch;

outpro(ch, x,

y);

dbms_output.put_line(x || ' ' ||

y); end;

Output:

SQL> @ajay.sql

Enter value for ch:

10 old 6: ch :=
&ch; new 6: ch :=

10; Manager

chennai

PL/SQL procedure successfully completed.

Result:
Thus, the program Creation of Procedures is executed successfully.

Problem:

Create a procedure in Oracle that updates the email address for a customer in the
customer table, using their customer_id. The procedure should perform the following:

1. Check if the customer exists based on the provided customer_id.

2. If the customer exists, update their email field with the new email provided.
3. If the customer doesn't exist, raise an exception and output an error message.
4. If the email field is already populated, raise an exception and output a
message indicating that the email is already updated.

Solution:

Step 1: Define the procedure

We will create a procedure named update_email. The procedure will take two
parameters:

● customer_id (IN parameter, to search for the customer).

● new_email (IN parameter, to update the customer's email address).

The logic will:

● Check if the customer exists.

● If they exist, check if the email is already updated.
● If not, update the email and raise a success message.

SQL Code:
CREATE OR REPLACE PROCEDURE update_email(customer_id IN VARCHAR2,
new_email IN VARCHAR2) IS
v_customer_name VARCHAR2(50);
v_current_email VARCHAR2(100);
email_exists EXCEPTION;
BEGIN
-- Check if the customer exists
SELECT customer_name, email INTO v_customer_name, v_current_email
FROM customer
WHERE customer_id = customer_id;

-- If the customer exists but the email is already updated, raise an

exception IF v_current_email IS NOT NULL THEN
RAISE email_exists;
ELSE
-- Update the customer's email if it's null

UPDATE customer
SET email =
new_email
WHERE customer_id = customer_id;

DBMS_OUTPUT.PUT_LINE('Email updated successfully for customer ' ||

v_customer_name);
END IF;

EXCEPTION
-- If no data found, customer doesn't exist
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Customer not found.');
-- If the email is already
updated WHEN email_exists
THEN
DBMS_OUTPUT.PUT_LINE('Email is already updated for this
customer.'); END;
/

Explanation of Code:

1. The procedure update_email takes two input parameters (customer_id

and new_email).
2. It tries to fetch the customer's name and email from the customer table.
3. If the email is already populated (v_current_email IS NOT NULL), it raises
 an exception email_exists to indicate that the email is already updated.
4. If no customer is found with the given customer_id, it raises a
NO_DATA_FOUND exception and outputs an appropriate message.
5. If the customer is found and the email is not set, it updates the email field
with the new value.

Step 2: Execute the Procedure

Here is how you can execute the procedure to update the email address of a customer
with customer_id = 'cus_101':

EXEC update_email('cus_101', 'newemail@example.com');

Example Execution:

Before execution:
SELECT * FROM customer WHERE customer_id = 'cus_101';

Output:
 CUSTOMER_ID CUSTOMER_NAME CUSTOMER_CIT CUSTOMER_DO EMAIL

cus_101 Suresh Chennai-45 12-JAN-86 NULL

After execution (if the email was empty):

EXEC update_email('cus_101', 'newemail@example.com');

Output:

Email updated successfully for customer Suresh

SELECT * FROM customer WHERE customer_id = 'cus_101';

Output:

CUSTOMER CUSTOMER_NA CUSTOMER_ CUSTOMER_D EMAIL

ME CIT OB
_ID

cus_101 Suresh Chennai-45 12-JAN-86 newemail@exampl

e.com

If customer doesn't exist:

EXEC update_email('cus_999', 'newemail@example.com');

Output:

Customer not found.

If the email is already updated:

EXEC update_email('cus_101', 'anotheremail@example.com');

Output:

Email is already updated for this customer.

Result:

The procedure update_email successfully checks the existence of a customer, validates if

their email is already updated, and provides appropriate messages or updates as needed.
Exp No: 13 CREATION OF DATABASE TRIGGERS AND FUNCTIONS
Date:

AIM:
To implement and execute triggers and functions in Oracle Database using Procedural
Language concepts.

TRIGGERS:

1. A trigger is a special kind of procedure that Oracle executes when an insert,

modify, or delete operation is performed on a given table.
2. It is a stored sub-program associated with a table.
3. It is used to maintain an audit trail for a table, prevent invalid transactions,
enforce complex security authorization, and automatically generate data.

SYNTAX:

CREATE OR REPLACE TRIGGER <TRIGGER NAME> {BEFORE/AFTER/INSTEAD OF}

{INSERT/UPDATE/DELETE} ON <TABLENAME/VIEWNAME> REFERENCING

{OLD AS OLD /NEW AS NEW} [FOR EACH STATEMENT /FOR EACH ROW

[WHEN <CONDITION>]]

DECLARE

Variable

declaration

Constant

declaration BEGIN

PL/SQL Sub-program

body. EXCEPTION

Exception PL/SQL Block (or) user-defined exception.

END;
FUNCTION:
A function is a sub-program that accepts an argument and returns a unique value to the
caller.

FUNCTION SYNTAX:
CREATE OR REPLACE FUNCTION <function_name> (parameter {in, out, in out}) RETURN
<data type>

IS

Variable declaration

BEGIN

PL/SQL Subprogram

body. EXCEPTION

Exception PL/SQL

Block. RETURN

statement END;
Example: Trigger for Insertion
Below program creates a trigger to calculate the total and average of a student for every
record insertion.

SQL> CREATE TABLE student(

regno NUMBER PRIMARY KEY,

name VARCHAR2(20),

tamil NUMBER,

english NUMBER,

maths NUMBER,

science NUMBER,

social NUMBER,

total NUMBER,

average

NUMBER);

Table Created

TRIGGER PROGRAM

CREATE OR REPLACE TRIGGER totavg BEFORE INSERT ON student FOR

EACH ROW
DECLARE

BEGIN

:new.total := :new.tamil + :new.english + :new.maths + :new.science + :new.social;

:new.average := ROUND(:new.total / 5);

END;

SQL> /

Trigger created.

SQL> DESC student;

Name Null? Type

REGNO NOT NULL NUMBER

NAME VARCHAR2(20)

TAMIL NUMBER

ENGLISH NUMBER

MATHS NUMBER

SCIENCE NUMBER

SOCIAL NUMBER

TOTAL NUMBER

AVERAGE NUMBER
SQL> INSERT INTO student(REGNO, NAME, TAMIL, ENGLISH, MATHS, SCIENCE, SOCIAL)

VALUES(101, 'Sachin', 70, 65, 80, 83, 90);

1 row created.
 SQL> SELECT * FROM student;

REGNO NAME TAMIL ENGLISH MATHS SCIENCE SOCIAL TOTAL AVERAGE

101 Sachin 70 65 80 83 90 388 78

Example: Trigger for Deletion

The following code prevents the deletion of a record if its quantity value is greater than

zero. SQL> DESC stock131;

Name Null? Type

ITEMID NUMBER(4)

NAME VARCHAR2(7)

QTY NUMBER(10)

SQL> INSERT INTO stock131 VALUES(10, 'Vimal', 50);

1 row created.

SQL> SELECT * FROM stock131;

ITEMID NAME QTY

10 Vimal 50
TRIGGER PROGRAM

CREATE OR REPLACE TRIGGER tv130 BEFORE DELETE ON stock131 FOR EACH ROW

DECLARE
 BEGIN

IF :old.qty > 0 THEN

RAISE_APPLICATION_ERROR(-20010, 'DELETION IS POSSIBLE ONLY IF THE QTY IS

ZERO');

END IF;

END;

Output:

SQL> SET SERVEROUTPUT ON

SQL>

@tr130.sql

Trigger created.

SQL> DELETE FROM stock131 WHERE itemid = 10;

ERROR at line 1:

ORA-20010: DELETION IS POSSIBLE ONLY IF THE QTY IS ZERO

ORA-06512: at "SCOTT.TR130", line 4

ORA-04088: error during execution of trigger 'SCOTT.TR130'

Example: Trigger for Update

The following code prevents the update operation if the old salary is less than the new
salary of the employee.

SQL> DESC emp1310;

Name Null? Type

ENO NUMBER(2)
ENAME VARCHAR2(10)

SALARY NUMBER(8)

SQL> INSERT INTO emp1301 VALUES(20, 'FGF', 4000);

 1 row created.

SQL> SELECT * FROM emp1301;

EMPID NAME SALARY

20 FGF 4000

TRIGGER PROGRAM

CREATE OR REPLACE TRIGGER tr130 BEFORE UPDATE ON emp1301 FOR EACH ROW

DECLARE

BEGIN

IF :new.salary <= :old.salary THEN

RAISE_APPLICATION_ERROR(-20010, 'NEW SALARY MUST BE GREATER THAN OLD

SALARY');

END IF;

END;

Output:

SQL>

@tv130.sql

Trigger created.

SQL> UPDATE emp1301 SET salary = 2000 WHERE empid = 20;

ERROR at line 1:

ORA-20010: NEW SALARY MUST BE GREATER THAN OLD SALARY

ORA-06512: at "SCOTT.TV130", line 4

ORA-04088: error during execution of trigger 'SCOTT.TV130'

FUNCTION PROGRAM

CREATE OR REPLACE FUNCTION fact(n INTEGER) RETURN NUMBER AS

BEGIN

IF n = 1 THEN

RETURN 1;

ELSE

RETURN (n * fact(n - 1));

END IF;

END;

Output:

SQL> SET SERVEROUTPUT ON

SQL> @fact.sql

Function

created.

CALLING PROGRAM

DECLARE

n NUMBER;

BEGIN

n := &n;

DBMS_OUTPUT.PUT_LINE('fact value is ' || fact(n));

END;
SQL> @main.sql

Enter value for n: 3

 fact value is 6

PL/SQL procedure successfully completed.

RESULT:
Thus, the program for the creation of database triggers and functions is executed
successfully.

Problem:

You are working with a database system where you need to enforce certain business
rules using triggers. You need to:

1. Calculate the total and average marks for a student whenever a new
record is inserted.
2. Prevent the deletion of a record from the stock131 table if the quantity is greater
than zero.
3. Prevent an update to an employee's salary unless the new salary is greater than
the old salary.

Solution:

1. Trigger for Insertion (Calculate Total and Average Marks):

○ A trigger is created on the student table to automatically calculate the

total and average marks whenever a new student's record is inserted.
○ Trigger Logic: The total marks are the sum of Tamil, English, Maths,
Science, and Social marks. The average is calculated by dividing the total
by 5.

CREATE OR REPLACE TRIGGER totavg

BEFORE INSERT ON student

FOR EACH ROW

DECLARE

BEGIN

:new.total := :new.tamil + :new.english + :new.maths + :new.science + :new.social;

:new.average := ROUND(:new.total / 5);

END;
2. Example:
 ○ A new student Sachin with marks for various subjects is inserted. The
trigger calculates the total and average.

3. Trigger for Deletion (Prevent Deletion If Quantity > 0):

○ A trigger is created on the stock131 table to prevent deletion of a record

if the quantity is greater than zero.
○ Trigger Logic: If the quantity of the record to be deleted is greater than
zero, an error is raised to prevent the deletion.

CREATE OR REPLACE TRIGGER tv130

BEFORE DELETE ON stock131

FOR EACH ROW

DECLARE

BEGIN

IF :old.qty > 0 THEN

RAISE_APPLICATION_ERROR(-20010, 'DELETION IS POSSIBLE ONLY IF THE QTY IS

ZERO');

END IF;

END;

4. Example:

○ Attempting to delete an item Vimal from the stock when the quantity
is 50 raises an error and prevents the deletion.

5. Trigger for Update (Prevent Update If New Salary <= Old Salary):

○ A trigger is created on the emp1301 table to prevent an update

operation on an employee's salary unless the new salary is greater than
the old salary.
○ Trigger Logic: If the new salary is less than or equal to the old salary,
the update is not allowed, and an error is raised.

CREATE OR REPLACE TRIGGER tr130

BEFORE UPDATE ON emp1301

FOR EACH ROW

DECLARE

BEGIN

IF :new.salary <= :old.salary THEN

 RAISE_APPLICATION_ERROR(-20010, 'NEW SALARY MUST BE GREATER THAN OLD
SALARY');

END IF;

END;

6. Example:

○ Attempting to update an employee's salary to a lower value (e.g.,

2000 instead of 4000) raises an error and prevents the update.

Exp.No: 14 STUDENT DATABASE

Date:
AIM:
To develop a student database application using Oracle as the backend and Visual Basic
as the frontend
The form design is as follows:

The table structure is:

Name
varchar2(10)
Rollno number(5)
Age number(5)

The source code is:

Private Sub Command1_Click() 'code for

insert Adodc1.Recordset.AddNew

End Sub

Private Sub Command2_Click() 'code for

delete Adodc1.Recordset.Delete

If Adodc1.Recordset.EOF = True Then

Adodc1.Recordset.MovePrevious

End If

MsgBox("RECORD DELETED")

End Sub

Private Sub Command3_Click() 'code for

update Adodc1.Recordset.Update

MsgBox("RECORD UPDATED")

End Sub

Private Sub Command4_Click() 'code for

exit Unload Me

End Sub
RESULT:
Thus, the Student Database Application program is executed successfully.

Problem:

A student database application needs to be developed that connects a Visual Basic

frontend with an Oracle database backend. The application should allow users to
perform CRUD (Create, Read, Update, Delete) operations on student records in the
database. The student data includes the following fields: Name (varchar2), Rollno
(number), and Age (number).

Solution:

The solution involves developing the student database application by setting up the
Oracle database connection in Visual Basic using ADO (ActiveX Data Objects) and
Oracle ODBC drivers. The necessary steps include:

1. Setting up the Oracle ODBC Data Source:

○ Open Control Panel → Administrative Tools → Data Source (ODBC).

○ Select the System DSN Tab and press the "Add" button.
○ Choose Oracle in OraDB11g_home1 and press "Finish."
○ Enter the Data Source Name (DSN) as "Student," provide the Oracle
username and password, then test the connection to ensure it's
successful.

2. Designing the VB Form:

○ Add the Microsoft ADO Data Control 6.0 to the form via the Toolbox.
○ Configure the ADO Data Control to use the Oracle DSN created
earlier, setting up the connection string, user credentials, and
record source to connect to the "Studentdb" table in Oracle.

3. Coding the CRUD Operations:

○ Insert Operation: Use Adodc1.Recordset.AddNew to add new

student records.
○ Delete Operation: Use Adodc1.Recordset.Delete to delete selected
student records.
○ Update Operation: Use Adodc1.Recordset.Update to update the
existing records.
○ Exit Operation: Unload the form to exit the application.

By following these steps, the student database application allows users to manage
student records effectively. Upon executing the program, it successfully connects to the
Oracle database and performs the required CRUD operations.
Result:

The student database application was successfully developed and executed, providing an
interface for adding, updating, and deleting student records stored in the Oracle
database.

Exp.No.17 SPORTS MEET

Date:

PROBLEM STATEMENT

● A College is conducting a sports meet.

● Teams from recognized colleges are allowed.
● A team should have players from the same college.
● A player can play for more than one team.
● Events take place in various grounds in the college.
● Winning teams receive awards.
● A captain is a player of a team.
● A player is a student of a college.
● Many teams can participate in a game.
● A game takes place in a ground.
● A college can have many teams.
● Only the first two teams are awarded.

Code :

CREATE TABLE College (

college_id INT PRIMARY KEY AUTO_INCREMENT,

college_name VARCHAR(100) NOT NULL

);

CREATE TABLE Player (

player_id INT PRIMARY KEY AUTO_INCREMENT,

player_name VARCHAR(100) NOT NULL,

college_id INT,

FOREIGN KEY (college_id) REFERENCES College(college_id)

);
CREATE TABLE Team (

team_id INT PRIMARY KEY AUTO_INCREMENT,

team_name VARCHAR(100) NOT NULL,

college_id INT,

captain_id INT,

FOREIGN KEY (college_id) REFERENCES College(college_id), FOREIGN

KEY (captain_id) REFERENCES Player(player_id)

);

CREATE TABLE Ground (

ground_id INT PRIMARY KEY AUTO_INCREMENT,

ground_name VARCHAR(100) NOT NULL,

location VARCHAR(100) NOT NULL

);

CREATE TABLE Game (

game_id INT PRIMARY KEY AUTO_INCREMENT,

game_name VARCHAR(100) NOT NULL,

ground_id INT,

FOREIGN KEY (ground_id) REFERENCES Ground(ground_id)

);

CREATE TABLE Team_Game (

team_id INT,

game_id INT,
rank INT,
 PRIMARY KEY (team_id, game_id),

FOREIGN KEY (team_id) REFERENCES Team(team_id),

FOREIGN KEY (game_id) REFERENCES Game(game_id)

);

CREATE TABLE Awards (

award_id INT PRIMARY KEY AUTO_INCREMENT,

team_id INT,

game_id INT,

position INT CHECK (position IN (1,2)),

FOREIGN KEY (team_id) REFERENCES Team(team_id),

FOREIGN KEY (game_id) REFERENCES Game(game_id)

);

INSERT INTO College (college_name) VALUES ('ABC College');

INSERT INTO College (college_name) VALUES ('XYZ College');

INSERT INTO Player (player_name, college_id) VALUES ('John',

1); INSERT INTO Player (player_name, college_id) VALUES

('Alice', 2);

INSERT INTO Team (team_name, college_id, captain_id) VALUES ('Team A', 1, 1);

INSERT INTO Team (team_name, college_id, captain_id) VALUES ('Team B', 2, 2);

INSERT INTO Ground (ground_name, location) VALUES ('Main Ground', 'ABC College');
INSERT INTO Ground (ground_name, location) VALUES ('Sports Arena', 'XYZ College');
INSERT INTO Game (game_name, ground_id) VALUES ('Football', 1);

INSERT INTO Game (game_name, ground_id) VALUES ('Basketball', 2);

INSERT INTO Team_Game (team_id, game_id, rank) VALUES (1, 1, 1);

INSERT INTO Team_Game (team_id, game_id, rank) VALUES (2, 1, 2);

INSERT INTO Awards (team_id, game_id, position) VALUES (1, 1, 1);

INSERT INTO Awards (team_id, game_id, position) VALUES (2, 1, 2);

SELECT Team.team_name, Game.game_name, Awards.position

FROM Awards

JOIN Team ON Awards.team_id = Team.team_id

JOIN Game ON Awards.game_id = Game.game_id;

Er diagram :