PART A

SQL
1. A. Create a table student with the following fields: Rollno, Name,
DOB, Department, Marks.
B. Insert at least 5 records
C. Display all records
D. Update marks for a specified student
E. Delete a student record
CREATE TABLE STUDENT (
Rollno number(10) Primary key,
Name Varchar(50) Not null,
DOB varchar(20) Not null,
Department varchar(30) Not null,
Marks number(5,2) check (Marks>=0 AND Marks<=100)
);

Insert into STUDENT values(101,'Alice','2004-05-12','BCA',86);

Insert into STUDENT values(102,'Bob','2004-07-12','BA',100);
Insert into STUDENT values(103,'Carol','2004-09-12','BBA',90);
Insert into STUDENT values(104,'Harry','2004-11-12','BCom',75);
Insert into STUDENT values(105,'Wilson','2004-13-12','BSc',57);

select * from STUDENT;

Update STUDENT set marks=90 where rollno=101;

Delete from STUDENT where rollno=105;

2. A. Create table COURSE with coursed, coursename, credits.

B. Alter the table to add a field Department
C. Drop the field Credits.

CREATE TABLE COURSE ( courseid number(5) Primary key,

coursename varchar Not null, Credits number(2) );

ALTER TABLE COURSE add Department varchar(20);

ALTER TABLE COURSE drop column Credits;

3. Create two tables EMPLOYEE1 and EMPLOYEE2 with the
following attributes: (FNAME, MNAME, LNAME, SSN, BDATE,
ADDRESS, SEX, SALARY, SUPERSSN, DNO). Perform UNION,
INTERSECT and MINUS operations on them.

Create table EMPLOYEE1 ( FNAME Varchar(20), MNAME

Varchar(20), LNAME Varchar(20), SSN number(10) Primary key,
BDATE Date, ADDRESS Varchar(60), SEX char(1), SALARY
Number(25), SUPERSSN Number(10), DNO Number(10) );

Create table EMPLOYEE2 ( FNAME Varchar(20), MNAME

Varchar(20), LNAME Varchar(20), SSN number(10) Primary key,
BDATE Date, ADDRESS Varchar(60), SEX char(1), SALARY
Number(25), SUPERSSN Number(10), DNO Number(10) );

Insert into EMPLOYEE1 values (‘Stephen’,

‘A’,’Damon’,101,to_date(‘2000-08-09’, ‘YYYY-MM-DD’),
‘Bengaluru’, ‘M’, 55000, NULL, 10);
Insert into EMPLOYEE1 values (‘John’, ‘B’,’Mary’,102,to_date(‘2000
03-16’,’YYYY-MM-DD’), ‘Bengaluru’, ‘F’, 55000, NULL, 20);
Insert into EMPLOYEE2 values (‘Stephen’,
‘A’,’Damon’,101,to_date(‘2000-08-09’, ‘YYYY-MM-DD’),
‘Bengaluru’, ‘M’, 55000, NULL, 10);

SELECT * from EMPLOYEE1 UNION SELECT * from EMPLOYEE2;

SELECT * from EMPLOYEE1 INTERSECT SELECT * from

EMPLOYEE2;

SELECT * from EMPLOYEE1 MINUS SELECT * from EMPLOYEE2;

4. Use the STUDENT table to perform Aggregate functions and Scalar

functions.
Aggregate function
Select count(*) from STUDENT;
Select AVG(marks) from STUDENT;
Select MAX(marks) from STUDENT;
Select MIN(marks) from STUDENT;
Select SUM(marks) from STUDENT;
 Scalar function
Select name, lower (Name) as Name from STUDENT;
Select name, upper (Name) as Name from STUDENT;
Select name, length(name) as length from STUDENT;
Select name, marks, round(marks) as Roundedmarks from STUDENT;
Select name, marks, floor(marks) as Marks from STUDENT;
Select Sysdate from dual;

5. Using the STUDENT and COURSE tables, Find students who

secured above average marks using a subquery. List students
enrolled in ‘Computer Science’ using a subquery.

Create table COURSE (

Courseid number(5) primary key,
Coursename varchar(20) not null
);

Create table STUDENT1 (

Rollno number(10) primary key,
Name varchar(20),
DOB varchar(10),
Courseid number(5) references COURSE(courseid),
Marks number(5,2) check (Marks>=0 and Marks<=100)
);

Insert into COURSE values ( 1, ‘Computer Science’);

Insert into COURSE values ( 2, ‘Electrical Engineering’);
Insert into COURSE values ( 3, ‘Electronics’);
Insert into COURSE values ( 4, ‘IT’);
Insert into COURSE values ( 5, ‘Mechanics’);

Insert into STUDENT1 values ( 101, ‘John’,to_date(‘2004-05 12’,

‘YYYY-MM-DD’),1,90);
Insert into STUDENT1 values ( 102, ‘Andrew’,to_date(‘2004-06 26’,
‘YYYY-MM-DD’),2,75);
Insert into STUDENT1 values ( 104, ‘Stephen’,to_date(‘2004-08 10’,
‘YYYY-MM-DD’),3,100);

Select rollno, name, marks from STUDENT1 where Marks>(select

AVG(Marks) from STUDENT1);
 Select rollno, courseid from STUDENT1 where courseid in ( select
courseid from course where coursename='Computer Science');

6. Create DEPARTMENT and STUDENT tables. Write queries to

display student names along with their department names using
JOIN operations.

Create table DEPARTMENT (

Deptid number(5) primary key,
Deptname varchar(20) not null
);

Create table STUDENT2 (

Rollno number(10) primary key,
Name varchar(20) not null,
Deptid number(5),
Marks number(5,2),
Foreign key(Deptid) references DEPARTMENT (Deptid)
);

Insert into DEPARTMENT values ( 10,'Computer Science');

Insert into DEPARTMENT values ( 20,'Electrical Engineer');
Insert into DEPARTMENT values ( 30, 'Electronics');
Insert into DEPARTMENT values ( 40, 'IT');
Insert into DEPARTMENT values ( 50, 'Mechanics');

Insert into STUDENT2 values (101,'John',10,90);

Insert into STUDENT2 values (102,'Andrew', 20,75);
Insert into STUDENT2 values (104,'Stephen', 30,100);

Inner join:
Select [Link], [Link] from student2 S inner join department D
On [Link]=[Link];

Left outer join:

Select [Link], [Link] from student2 S left join department D
On [Link]=[Link] order by [Link];

Right outer join:

 Select [Link], [Link] from student2 S Right join department D
On [Link]=[Link] order by [Link];

Full outer join:

Select [Link], [Link] from student2 S Full outer join department
D On [Link]=[Link] order by [Link];

7. Create a view to show student names and marks from the student
table where marks>75 Query the view and update the view.

Create view Meritlist as

Select name, marks from STUDENT where marks>75;

Select * from Meritlist order by name;

Update Meritlist set marks=95 where name='alice';

Select * from Meritlist order by name;

Select * from STUDENT;

8. Demonstrate the use of grant and revoke on the student table. Use
commit and rollback after insert and delete command

insert into student2 values(105,'ABC',10,95);

rollback;

insert into student2 values(106,'DEF',20,95);

select * from student2;

commit;

grant select,insert on student to public;

revoke insert on student from public;

 PART-B
PL/SQL

1. Write a PL/SQL block to accept a number and check if it is even

or odd
SET SERVEROUTPUT ON;
DECLARE
V_num NUMBER:=&Enter_Number;
BEGIN
IF MOD(V_num,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(‘The number’ || V_num || ‘is Even’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘The number’ || V_num || ‘is Odd’);
END IF;
END;
/

2. Write a PL/SQL program to divide two numbers and handle the

exception if the denominator is zero
SET SERVEROUTPUT ON;
DECLARE numerator NUMBER;
denominator NUMBER;
result NUMBER;
BEGIN numerator := &numerator;
denominator := &denominator;
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;
/

3. Use a parameterized cursor to display students based on

department input.

CREATE TABLE department ( dept_id NUMBER PRIMARY KEY,

dept_name VARCHAR2(30) UNIQUE );
CREATE TABLE student4 ( student_id NUMBER PRIMARY KEY,
student_name VARCHAR2(50), dept_id NUMBER, FOREIGN KEY
(dept_id) REFERENCES department(dept_id) );

-- Insert departments
INSERT INTO department VALUES (1, 'CSE');
INSERT INTO department VALUES (2, 'ECE');
INSERT INTO department VALUES (3, 'MECH');
INSERT INTO department VALUES (4, 'CIVIL');

-- Insert students
INSERT INTO student4 VALUES (101, 'Rahul Sharma', 1);
INSERT INTO student4 VALUES (102, 'Priya Mehta', 1);
INSERT INTO student4 VALUES (103, 'Arjun Nair', 2);
INSERT INTO student4 VALUES (104, 'Sneha Iyer', 2);
INSERT INTO student4 VALUES (105, 'Vikram Patel', 3);
INSERT INTO student4 VALUES (106, 'Aditi Rao', 4);
COMMIT;

SET SERVEROUTPUT ON;

DECLARE
v_dept_name VARCHAR2(50);

-- Cursor with department parameter

CURSOR c_students (p_dept VARCHAR2) IS
SELECT
s.student_id,
s.student_name,
d.dept_name
FROM
student4 s
JOIN department d ON s.dept_id = d.dept_id
WHERE
UPPER(d.dept_name) = UPPER(p_dept);

v_student_id student4.student_id%TYPE;
v_student_name student4.student_name%TYPE;
v_department department.dept_name%TYPE;
 BEGIN
-- Take user input for department
v_dept_name := '&dept_name';

OPEN c_students(v_dept_name);

LOOP
FETCH c_students
INTO v_student_id, v_student_name, v_department;
EXIT WHEN c_students%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(
'Student ID: ' || v_student_id ||
', Name: ' || v_student_name ||
', Department: ' || v_department
);
END LOOP;

CLOSE c_students;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
4. Create a stored function to calculate grade based on marks:
Above 80:Distinction 60-79: First Class 40-59: Second Class Below
40: Fail

CREATE TABLE student5 ( student_id NUMBER PRIMARY KEY,

student_name VARCHAR2(50), marks NUMBER );

INSERT INTO student5 VALUES (101, 'Rahul Sharma', 85);

INSERT INTO student5VALUES (102, 'Priya Mehta', 78);
INSERT INTO student5 VALUES (103, 'Arjun Nair', 55);
INSERT INTO student5 VALUES (104, 'Sneha Iyer', 39);
INSERT INTO student5 VALUES (105, 'Vikram Patel', 92);
COMMIT;

CREATE OR REPLACE FUNCTION get_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;
/
SELECT student_id, student_name, marks, get_grade(marks) AS
grade FROM student5;

5. Write a stored procedure to update the marks of a student given

their Rollno and new marks.

CREATE TABLE student6 ( student_id NUMBER PRIMARY KEY,

student_name VARCHAR2(50), marks NUMBER );

INSERT INTO student6 VALUES (101, 'Rahul Sharma', 85);

INSERT INTO student6 VALUES (102, 'Priya Mehta', 78);
INSERT INTO student6 VALUES (103, 'Arjun Nair', 55);
INSERT INTO student6 VALUES (104, 'Sneha Iyer', 39);
INSERT INTO student6 VALUES (105, 'Vikram Patel', 92);
COMMIT;

CREATE OR REPLACE PROCEDURE update_marks ( p_rollno IN

NUMBER, p_new_marks IN NUMBER )
IS
BEGIN
UPDATE student6 SET marks = p_new_marks WHERE student_id =
p_rollno;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('No student found with Roll No: ' ||
p_rollno);
ELSE
DBMS_OUTPUT.PUT_LINE('Marks updated successfully for Roll
No: ' || p_rollno);
END IF;
 COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

SET SERVEROUTPUT ON;

EXEC UPDATE_MARKS(103,72);

SELECT student_id, student_name, marks FROM student6 ORDER

BY student_id;

6. Create a before insert trigger on student to ensure marks are not

entered as negative.
CREATE TABLE student7 ( student_id NUMBER PRIMARY KEY,
student_name VARCHAR2(50), marks NUMBER );

CREATE OR REPLACE TRIGGER trg_check_marks1 BEFORE

INSERT ON student7 FOR EACH ROW
BEGIN
IF :[Link] < 0 THEN RAISE_APPLICATION_ERROR(-
20001, 'Error: Marks cannot be negative.');
END IF;
END;
/
Valid insert: INSERT INTO student7 VALUES (101, 'Rahul Sharma',
85);
Negative insert: INSERT INTO student7 VALUES (102, 'Priya
Mehta', -45);
Select * from student7;

7. Create a trigger to log changes into a student_log whenever the

marks are updated.
create table stud7(studid number primary key, stdname
varchar(20),marks number);
-- 1⃣ Create the table
CREATE TABLE std_log (
log_id NUMBER PRIMARY KEY,
stdid NUMBER,
 old_marks NUMBER,
new_marks NUMBER,
changed_by VARCHAR2(20),
changed_on DATE
);

-- 2⃣ Create a sequence for auto-increment

CREATE SEQUENCE std_log_seq START WITH 1 INCREMENT
BY 1;

-- 3⃣ Create a trigger to use the sequence automatically

CREATE OR REPLACE TRIGGER trg_std_log_id
BEFORE INSERT ON std_log
FOR EACH ROW
BEGIN
:NEW.log_id := std_log_seq.NEXTVAL;
END;
/
INSERT INTO std_log (stdid, old_marks, new_marks, changed_by,
changed_on)
VALUES (101, 60, 75, 'teacher1', SYSDATE);
SELECT * FROM std_log;
INSERT INTO stud7 VALUES (1, 'Rahul', 70);
UPDATE stud7 SET marks = 85 WHERE studid = 1;

8. Use a cursor with a loop to count and display the number of

students in each department.
CREATE TABLE dept1 ( dept_id NUMBER PRIMARY KEY,
dept_name
VARCHAR2(30) );
CREATE TABLE stud8 ( student_id NUMBER PRIMARY KEY,
student_name VARCHAR2(50), dept_id NUMBER REFERENCES
department(dept_id) );
-- Insert departments
INSERT INTO dept1 VALUES (1, 'CSE');
INSERT INTO dept1 VALUES (2, 'ECE');
INSERT INTO dept1 VALUES (3, 'MECH');
INSERT INTO dept1 VALUES (4, 'CIVIL');
-- Insert students
 INSERT INTO stud8 VALUES (101, 'Rahul Sharma', 1);
INSERT INTO stud8 VALUES (102, 'Priya Mehta', 1);
INSERT INTO stud8 VALUES (103, 'Arjun Nair', 2);
INSERT INTO stud8 VALUES (104, 'Sneha Iyer', 2);
INSERT INTO stud8 VALUES (105, 'Vikram Patel', 3);
COMMIT;
SET SERVEROUTPUT ON;
DECLARE
-- Cursor to get department name and student count
CURSOR c_dept_count IS SELECT d.dept_name,
COUNT(s.student_id) AS total_students FROM dept1 d LEFT JOIN
stud8 s ON d.dept_id = s.dept_id GROUP BY d.dept_name;
-- Variables to hold fetched data
v_dept_name dept1.dept_name%TYPE;
v_total_students NUMBER;
BEGIN
OPEN c_dept_count;
LOOP FETCH c_dept_count INTO v_dept_name, v_total_students;
EXIT WHEN c_dept_count%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department: ' || v_dept_name || ' →
Students: ' || v_total_students);
END LOOP;
CLOSE c_dept_count;
END;
/