Program 1. Create a table Student with the following fields: RollNo, Name, DOB, Department, Marks.
Insert at least 5 records
Display all records
Update marks for a specific student
Delete a student record
1. Create the student table
SQL>Create table Student ( Rollno int Primary Key, Name varchar(100), Dob date, Department varchar(50), Marks
int);
SQL>Desc student;
2. Insert at least 5 records
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values (101, ‘Ali Mohammed’, ‘21-May-2004’,
‘Computer Science’, 85);
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values(102, ‘Bob Johnson’, ‘9-Dec-2003’,
‘Mechanical’, 78);
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values(103, ‘Chandan Sharma’, ‘30-Jan-2005’,
‘Electrical’, 92);
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values(104, ‘Krishna K’, ‘5-Nov-2004, ‘Civil’,
88);
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values(105, ‘Shankar M’,’17-Jun-2002’,
‘Computer Science’, 81);
3. Display all records
SQL>select * from student;
4. Update marks for a specific student
SQL>update Student set marks = 95 where rollno = 103;
SQL>select * from student;
5. Delete a student record
SQL>delete from student where rollno = 102;
SQL>select * from student;
Program 2. Create a table Course with CourseID, CourseName, Credits.
Alter the table to add a field Department.
Drop the field Credits
1. Create the Course table
SQL>Create table Course (CourseId int Primary Key, CourseName varchar(100), Credits int);
SQL>Desc Course;
2. Alter the table to add a field Department
SQL>Alter table Course add Department varchar(50);
SQL>Desc Course;
3. Drop the Field Credits
SQL>Alter table Course drop column Credits;
SQL>Desc Course;
Program 3. Create two tables Employee1 and Employee2 with the following attributes: (Fname, Mname,
Lname, Ssn, Bdate, Address, Gender, Salary, SuperSsn, Dno).
Insert Sample Records
Perform Union, Intersect, and Minus Operations on them.
1. Create Tables Employee1 and Employee2
SQL>Create table Employee1 ( Fname varchar(50), Mname varchar(50), Lname varchar(50), Ssn char(9) Primary
Key, Bdate date, Address varchar(100), Gender char(1), Salary decimal(10,2), Superssn char(9), Dno int);
SQL>Create table Employee2 ( Fname varchar(50), Mname varchar(50), Lname varchar(50), Ssn char(9) Primary
Key, Bdate date, Address varchar(100), Gender char(1), Salary decimal(10,2), Superssn char(9), Dno int);
SQL>Desc Employee1;
SQL>Desc Employee2;
2: Insert Sample Records
SQL>Insert into Employee1 Values(‘John’, ‘K’, ‘King’, ‘111111111’, ‘01-Jan-1990’, ‘123 Main St’, ‘M’,
60000.00, ‘222222222’, 1);
SQL>Insert into Employee1 Values(‘Janu’, ‘A’, ‘Smitha’, ‘222222222’, ‘2-Feb-1991’, ‘456 Park Ave’, ‘F’,
65000.00, ‘333333333’, 2);
SQL>Insert into Employee1 Values(‘Abhi’, ‘J’, ‘Tanuj’, ‘333333333’, ‘3-Mar-1989’, ‘789 Elm St’, ‘M’, 70000.00,
‘111111111’, 1);
SQL>Select * from Employee1;
SQL>Insert into Employee2 Values(‘Janu’, ‘A’, ‘Smitha’, ‘222222222’, ‘2-Feb-1991, ‘456 Park Ave’, ‘F’,
65000.00, ‘333333333’, 2);
SQL>Insert into Employee2 Values(‘Chris’, ‘M’, ‘Roy’, ‘444444444’, ‘4-Sep-1992’, ‘321 Oak St’, ‘M’, 55000.00,
‘111111111’, 3),
SQL>Insert into Employee2 Values(‘Ram’, ‘R’, ‘Shashi’, ‘555555555’, ‘10-Dec-1990’, ‘654 Pine St’, ‘F’,
62000.00, ‘222222222’, 2);
SQL>Select * from Employee2;
3: Perform Set Operations
UNION (All distinct rows from both tables)
SQL>Select * from Employee1 Union Select * from Employee2;
INTERSECT (Only common rows in both tables)
SQL>Select * from Employee1 Intersect Select * from Employee2;
MINUS (Rows in Employee1 but not in Employee2)
SQL>Select * from Employee1 Minus Select * from Employee2;
Program 4. Use the STUDENT table to perform Aggregate functions and Scalar functions
Create a table STUDENT with the following fields: RollNo, Name, DOB, Department, Marks.
Insert at least 5 records
1: Creating Student table
SQL>Create table Student ( Rollno int Primary Key, Name varchar(100), Dob date, Department varchar(50), Marks
int);
SQL>Desc student;
2. Insert at least 5 records
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values (101, ‘Ali Mohammed’, ‘21-May-2004’,
‘Computer Science’, 85);
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values(102, ‘Bob Johnson’, ‘9-Dec-2003’,
‘Mechanical’, 78);
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values(103, ‘Chandan Sharma’, ‘30-Jan-2005’,
‘Electrical’, 92);
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values(104, ‘Krishna K’, ‘5-Nov-2004, ‘Civil’,
88);
SQL>insert into Student (Rollno, Name, Dob, Department, Marks) values(105, ‘Shankar M’,’17-Jun-2002’,
‘Computer Science’, 81);
3. Performing Aggregate functions
SQL> Select Count(*) As TotalStudents From Student;
SQL> Select Avg(Marks) As AverageMarks From Student;
SQL>Select Min(Marks) As LowestMarks From Student;
SQL>Select Sum(Marks) As TotalMarks From Student;
SQL>Select Department, Avg(Marks) As AvgDeptMarks From Student Group By Department;
4. Performing Scalar Functions
SQL>Select Rollno, Upper(Name) As NameInUppercase From Student;
SQL> Select Rollno, Name, Length(Name) As NameLength From Student;
SQL> Select Concat(Name, Department) As StudentInfo From Student;
SQL> Select Name, Replace(Name, 'A', '@') As ReplacedName From Student;
SQL>Select Rollno, Mod(Rollno, 2) As EvenOrOdd From Student;
SQL> Select Marks, Round(Marks,0) As RoundedMarks From Student;
SQL> Select Marks, Sqrt(Marks) As RootMarks From Student;
Program 5. Using the Student and Course Tables,
Find students who scored above average marks using a subquery.
List students enrolled in ,”Computer Science‟ using a subquery.
( Create a table Course with Courseid, Coursename, Credits.
Create a table Student with: Rollno, Name, Dob, Courseid, Marks. )
1. a. Creating table Course with Courseid, Coursename, Credits.
SQL>Create table Course ( Courseid int Primary Key, Coursename varchar(50), Credits int);
SQL> Desc Course;
1.b. Creating table student
SQL>Create table Student ( Rollno int Primary Key, Name Varchar(50), Dob Date, Courseid int, Marks int,
Foreign Key (Courseid) References Course(Courseid));
SQL>Desc Student;
1.c. Inserting values into course and student table
SQL>Insert into Course (Courseid, Coursename, Credits) Values (1, 'Bcom', 4);
SQL>Insert into Course (Courseid, Coursename, Credits) Values (2, 'Ba’, 3);
SQL>Insert into Course (Courseid, Coursename, Credits) Values (3, 'BCA’, 4);
SQL>Insert into Student (Rollno, Name, Dob, Courseid, Marks) Values (101, ‘Ali Mohammed’, ‘21-May-2004’,1,
85);
SQL>Insert into Student (Rollno, Name, Dob, Courseid, Marks) Values(102, ‘Bob Johnson’, ‘9-Dec-2003’, 2, 78);
SQL>Insert into Student (Rollno, Name, Dob, Courseid, Marks) Values(103, ‘Chandan Sharma’, ‘30-Jan-2005’,3,
92);
SQL>Insert into Student (Rollno, Name, Dob, Courseid, Marks) Values(104, ‘Krishna K’, ‘5-Nov-2004, 1, 88);
SQL>Insert into Student (Rollno, Name, Dob, Courseid, Marks) Values(105, ‘Shankar M’,’17-Jun-2002’, 3, 81);
2. Using the student and course table, find students who scored above average marks using a subquery.
SQL>Select * From Student Where Marks > ( Select Avg(Marks) From Student);
3. List students enrolled in BCA using subquery
SQL>Select * From Student Where Courseid in(Select Courseid From Course Where Coursename=’BCA');
Program 6. Create a table Department with Deptid, Departmentname
Create a table Student with: Rollno, Name, Dob, Deptid, Marks.
Write queries to display student names along with their department names using JOIN operations.
1. Creating Department table
SQL>Create table Department ( Deptid int Primary Key, DepartmentName Varchar(50));
SQL> Desc Course;
2. Creating Student table and inserting the values
SQL>Create table Student ( Rollno int Primary Key, Name Varchar(50), Dob Date, Deptid int, Marks int, Foreign
Key (Deptid) References Department(Deptid));
SQL>Desc Student;
SQL>Insert into Department (Deptid, Departmentname) Values (1,’Computer Science’);
SQL> Insert into Department (Deptid, Departmentname) Values (2,’Arts’);
SQL> Insert into Department (Deptid, Departmentname) Values (3,’Mathematics’);
SQL>Insert into Student (Rollno, Name, Dob, Deptid, Marks) Values (101, ‘Ali Mohammed’, ‘21-May-2004’,1,
85);
SQL>Insert into Student (Rollno, Name, Dob, Deptid, Marks) Values(102, ‘Bob Johnson’, ‘9-Dec-2003’, 2, 78);
SQL>Insert into Student (Rollno, Name, Dob, Deptid, Marks) Values(103, ‘Chandan Sharma’, ‘30-Jan-2005’,3,
92);
SQL>Insert into Student (Rollno, Name, Dob, Deptid, Marks) Values(104, ‘Krishna K’, ‘5-Nov-2004, 1, 88);
SQL>Insert into Student (Rollno, Name, Dob, Deptid, Marks) Values(105, ‘Shankar M’,’17-Jun-2002’, 3, 81);
3. Query to display student names along with their department names using JOIN operations.
SQL>Select Student.Name As Studentname, Department.Departmentname From Student Join Department On
Student.Deptid = Department.Deptid;
Program 7. Create a view to show student names and marks from the STUDENT table where Marks >75.
Query the view and update the view.
1. Query to create the View
SQL>Create View Highscorers As Select Name, Marks From Student Where Marks > 75;
2. Query the select from the view.
SQL>Select * From HighScorers;
3. Query to update the view.
SQL>Alter View Highscorers As Select Name, Marks From Student Where Marks > 80;
SQL>Select * From Highscorers;
Program 8. a. Demonstrate the use of GRANT and REVOKE on the STUDENT table.
b. Use COMMIT And ROLLBACK After INSERT And DELETE Commands.
Steps 1. Demonstrate the use of GRANT and REVOKE on the STUDENT table.
SQL>Create User Shashi Identified By System;
SQL>Grant Create Session To Shashi;
SQL>Grant Select, Insert, Update on STUDENT To Shashi;
=>Login using Shashi username and System password
=>Execute the following command for Select, insert and update. All the commands will be executed
SQL>select * from student;
SQL>insert into Student (RollNo, Name, DOB, DeptID, Marks) values (201, 'Ravi Kumar', DATE '2003-05-15', 1,
88);
SQL>update Student set marks = 95 where rollno = 103;
The delete command will not be executed as permission is not given for Delete statement
SQL>delete from Student where RollNo = 201;
b. Use COMMIT and ROLLBACK after INSERT and DELETE Commands.
1. Insert a new student
SQL>insert into Student (RollNo, Name, DOB, DeptID, Marks) values (201, 'Ravi Kumar', DATE '2003-05-15', 1,
88);
SQL>select * from student;
SQL>rollback;
Previously inserted row will be rolled back
SQL>select * from student;
SQL>insert into Student (RollNo, Name, DOB, DeptID, Marks) values (201, 'Ravi Kumar', DATE '2003-05-15', 1,
88);
SQL>select * from student;
SQL>commit;
Previously inserted row will be permanently stored
SQL>rollback;
Previously inserted row will not be rolled back as it is permanently stored using commit statement
SQL>select * from student;
Using ROLLBACK after DELETE statement
SQL>delete from Student where RollNo = 201;
The row with Rollno=201 will be deleted
SQL> select * from student;
SQL> rollback;
The deletion of row with Rollno=201 will be undone
SQL> select * from student;
Part B
Program 9. Write a PL/SQL block to accept a number and check if it is even or odd.
SQL>set serveroutput on;
declare
num number := &input_number; -- accept input from the user
begin
if mod(num, 2) = 0 then
dbms_output.put_line('the number ' || num || ' is even.');
else
dbms_output.put_line('the number ' || num || ' is odd.');
end if;
end;
Working procedure
&input_number → prompts you for a number at runtime.
mod(num, 2) → calculates the remainder when dividing by 2.
if remainder is 0 → the number is even, else odd.
dbms_output.put_line → prints the result to the console (make sure set serveroutput on is enabled).
Output 1
Enter value for input_number: 56
old 2: num number := &input_number; -- Accept input from the user
new 2: num number := 56; -- Accept input from the user
The number 56 is even.
Output 2
Enter value for input_number: 77
old 2: num number := &input_number; -- Accept input from the user
new 2: num number := 77; -- Accept input from the user
The number 77 is odd.
Program 10. Write a PL/SQL program to divide two numbers and handle the exception if the denominator is
zero.
SQL>set serveroutput on;
declare
numerator number := &num1; -- prompt for numerator
denominator number := &num2; -- prompt for denominator
result number;
begin
-- attempt division
result := numerator / denominator;
dbms_output.put_line('Result: ' || result);
exception
when zero_divide then
dbms_output.put_line('Error: division by zero is not allowed.');
end;
Working procedure
&num1 and &num2 → accept input from the user at runtime.
Division is attempted with result := numerator / denominator;.
If denominator is 0, oracle raises the built-in exception zero_divide, which we handle in the exception
block.
dbms_output.put_line is used to display the result or the error message.
Output1
Enter value for num1: 23
old 2: numerator number := &num1; -- prompt for numerator
new 2: numerator number := 23; -- prompt for numerator
Enter value for num2: 12
old 3: denominator number := &num2; -- prompt for denominator
new 3: denominator number := 12; -- prompt for denominator
Result: 1.91667
Output2
Enter value for num1: 12
old 2: numerator number := &num1; -- prompt for numerator
new 2: numerator number := 12; -- prompt for numerator
Enter value for num2: 0
old 3: denominator number := &num2; -- prompt for denominator
new 3: denominator number := 0; -- prompt for denominator
Error: Division by zero is not allowed.
Program 11. Use a parameterized cursor to display students based on department input.
SQL>set serveroutput on;
declare
-- Variable to hold the department ID entered by the user
v_dept_id Department.DeptID%TYPE := &dept_id;
-- Parameterized cursor to fetch students for a given department
Cursor c_students(p_dept_id Department.DeptID%type) IS
Select RollNo, Name, Marks
From Student
Where DeptID = p_dept_id;
-- Variables to store fetched data
v_rollno Student.RollNo%type;
v_name Student.Name%type;
v_marks Student.Marks%type;
begin
-- Open and fetch from the parameterized cursor
open c_students(v_dept_id);
loop
fetch c_students into v_rollno, v_name, v_marks;
exit when c_students%notfound;
dbms_output.put_line('Roll No: ' || v_rollno ||
', Name: ' || v_name ||
', Marks: ' || v_marks);
end loop;
close c_students;
end;
/
Working procedure
v_dept_id prompts the user to enter the Department ID.
CURSOR c_students(p_dept_id) is parameterized — it takes a department ID as input.
The SELECT inside the cursor filters students by the given department.
The LOOP fetches and prints each matching student until there are no more rows.
Output
Enter value for dept_id: 1
old 3: v_dept_id Department.DeptID%TYPE := &dept_id;
new 3: v_dept_id Department.DeptID%TYPE := 1;
Roll No: 101, Name: Ali Mohammed, Marks: 85
Roll No: 201, Name: Ravi Kumar, Marks: 88
Program 12. Create a stored function to calculate grade based on marks:
Above 80: Distinction
60–79: First Class
40–59: Second Class
Below 40: Fail
Step1. Creating stored function in Oracle PL/SQL to calculate a student’s grade based on marks:
SQL>create or replace function calculate_grade(p_marks number)
return varchar2
is
v_grade varchar2(20);
begin
if p_marks > 80 then
v_grade := 'Distinction';
elsif p_marks between 60 and 79 then
v_grade := 'First Class';
elsif p_marks between 40 and 59 then
v_grade := 'Second Class';
else
v_grade := 'Fail';
end if;
return v_grade;
end;
/
Output1 Calling the function with dual table(special dummy table in Oracle used for evaluating expressions)
SQL>select calculate_grade(85) as Grade from dual;
GRADE
--------------
Distinction
Output2 Calling the function in existing table
SQL>select Name, Marks, calculate_grade(Marks) as Grade from Student;
Program 13. Write a stored procedure to update the marks of a student given their RollNo and new marks.
Step1: Creating Stored procedure
SQL>create or replace procedure update_student_marks (
p_rollno in Student.RollNo%type,
p_newmarks in Student.Marks%type
as
begin
update Student
set Marks = p_newmarks
where RollNo = p_rollno;
if sql%rowcount = 0 then
dbms_output.put_line('No student found with RollNo ' || p_rollno);
else
dbms_output.put_line('Marks updated for RollNo ' || p_rollno);
end if;
commit;
end;
2.Select the existing student details
SQL>select * from Student;
3.Update the student marks to 90 where the rollno=101 by executing the stored procedure
SQL>exec update_student_marks(101,90);
4.Check the updated student details
SQL>select * from Student;
Working process
p_rollno → input parameter for the student roll number.
p_newmarks → input parameter for the new marks.
SQL%ROWCOUNT → checks how many rows were updated (0 means no match).
COMMIT → saves the changes permanently.
Program 14. Create a BEFORE INSERT trigger on STUDENT to ensure marks are not entered as negative.
Step1: Creating trigger
SQL>create or replace trigger check_marks_before_insert
before insert on Student
for each row
begin
if :new.Marks < 0 then
raise_application_error(-20001, 'Marks cannot be negative.');
end if;
end;
2.Inserting negative value for marks raises error
SQL>insert into Student (RollNo, Name, DOB, DeptID, Marks) values (302, 'Kirani Kumar', DATE '2004-06-10',
2, -10);
Output
ORA-20001: Marks cannot be negative.
Program 15. Create a trigger to log changes into a table STUDENT_LOG whenever the marks are updated.
For Oracle 12c and later
1. Create the StudentLog table
SQL>Create Table Studentlog (
Logid Number Primary Key,
Rollno Int,
Oldmarks Int,
Newmarks Int,
Changedate Date,
Changedby Varchar(30)
);
2. Create Trigger to Log Changes in Student Table
SQL>Create or Replace Trigger Trg_Log_Student_Marks
After Update of Marks on Student
For Each Row
Begin
Insert Into Studentlog (Rollno, Oldmarks, Newmarks, Changedate, Changedby)
Values (:Old.Rollno, :Old.Marks, :New.Marks, Sysdate, User);
End;
/
3. Testing
-- Update Student Marks
SQL>Update Student
Set Marks = 88
Where Rollno = 101;
-- Check The Log Table
SQL>Select * From Studentlog;
For Oracle below 12c follow the below steps
1. Create the StudentLog table
SQL>Create Table Studentlog (
Logid Number Primary Key,
Rollno Int,
Oldmarks Int,
Newmarks Int,
Changedate Date,
Changedby Varchar(30)
);
2. Create the Sequence
SQL>Create Sequence Seq_Studentlog
Start With 1
Increment By 1
Nocache;
3. Create Trigger To Auto-Generate Logid
SQL>Create Or Replace Trigger Trg_Studentlog_Id
Before Insert On Studentlog
For Each Row
Begin
If :New.Logid Is Null Then
:New.Logid := Seq_Studentlog.Nextval;
End If;
End;
/
4. Create Trigger To Log Changes In Student Table
SQL>Create Or Replace Trigger Trg_Log_Student_Marks
After Update Of Marks On Student
For Each Row
Begin
Insert Into Studentlog (Rollno, Oldmarks, Newmarks, Changedate, Changedby)
Values (:Old.Rollno, :Old.Marks, :New.Marks, Sysdate, User);
End;
/
5. Testing
-- Update Student Marks
SQL>Update Student
Set Marks = 88
Where Rollno = 101;
-- Check The Log Table
SQL>Select * From Studentlog;
Program 16. Use a cursor with a loop to count and display the number of students in each department.
Step 1:Creating the cursor
SQL>set serveroutput on;
declare
cursor dept_cursor is
select DeptID, DepartmentName
from Department;
v_dept_id Department.DeptID%type;
v_dept_name Department.DepartmentName%type;
v_count number;
begin
open dept_cursor;
loop
fetch dept_cursor into v_dept_id, v_dept_name;
exit when dept_cursor%notfound;
-- Count students in this department
select count(*)
into v_count
from Student
where DeptID = v_dept_id;
dbms_output.put_line('Department: ' || v_dept_name || ' | Students: ' || v_count);
end loop;
close dept_cursor;
end;
/
Output
Department: Computer Science | Students: 2
Department: Arts | Students: 1
Department: Mathematics | Students: 1
