1.
a) Create an employee database table, add constraints (primary key, unique, check, not
null), insert
rows, update and delete rows using SQL DDL and DML commands.
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
department VARCHAR(30) NOT NULL,
salary DECIMAL(10, 2) CHECK (salary > 0),
email VARCHAR(100) UNIQUE
);
-- Insert Sample Data
INSERT INTO employee (emp_id, emp_name, department, salary, email)
VALUES (101, 'Alice Johnson', 'Production', 50000, 'alice@example.com'),
(102, 'Bob Smith', 'HR', 45000, 'bob@example.com'),
(103, 'Charlie Lee', 'Production', 55000, 'charlie@example.com');
-- Update Salary for Employee with emp_id = 101
UPDATE employee
SET salary = 60000
WHERE emp_id = 101;
-- Delete Employee with emp_id = 102
DELETE FROM employee
WHERE emp_id = 102;
b) Write a PL SQL programs to hike the employee salary, those who are all working under
theproduction department by using where clause.
SET SERVEROUTPUT ON;
DECLARE
CURSOR emp_cursor IS
SELECT emp_id, emp_name, salary
FROM employee
WHERE department = 'Production';
BEGIN
FOR emp_record IN emp_cursor LOOP
UPDATE employee
SET salary = salary * 1.10
WHERE emp_id = emp_record.emp_id;
DBMS_OUTPUT.PUT_LINE('Updated Salary for ' || emp_record.emp_name || ': ' ||
emp_record.salary * 1.10);
END LOOP;
COMMIT;
END;
/
2 a) Create a set of tables for student database, add foreign key constraints and incorporate
referential
integrity.
- Create Student Table
CREATE TABLE student (
roll_no INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
course VARCHAR(30) NOT NULL
);
-- Create Subject Table with Foreign Key
CREATE TABLE subject (
sub_code INT PRIMARY KEY,
sub_name VARCHAR(50) NOT NULL,
roll_no INT,
FOREIGN KEY (roll_no) REFERENCES student(roll_no)
);
-- Create Result Table with Foreign Key
CREATE TABLE result (
roll_no INT,
sub_code INT,
marks INT CHECK (marks BETWEEN 0 AND 100),
PRIMARY KEY (roll_no, sub_code),
FOREIGN KEY (roll_no) REFERENCES student(roll_no),
FOREIGN KEY (sub_code) REFERENCES subject(sub_code)
);
b) Write PL SQL Triggers for insertion, deletion operations in a student database table.
Insert Trigger
CREATE OR REPLACE TRIGGER trg_insert_student
AFTER INSERT ON student
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('New student added: ' || :NEW.name);
END;
/
Delete Trigger
CREATE OR REPLACE TRIGGER trg_delete_student
AFTER DELETE ON student
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Student deleted: ' || :OLD.name);
END;
/
3 a) Create a student table, subject_ mark table, result table using DDL, DML commands
and
also compute the minimum, maximum, total, average marks in the above database.
- Create Student Table
CREATE TABLE student (
roll_no INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
-- Create Subject Table
CREATE TABLE subject (
sub_code INT PRIMARY KEY,
sub_name VARCHAR(50) NOT NULL
);
-- Create Result Table
CREATE TABLE result (
roll_no INT,
sub_code INT,
marks INT CHECK (marks BETWEEN 0 AND 100),
PRIMARY KEY (roll_no, sub_code),
FOREIGN KEY (roll_no) REFERENCES student(roll_no),
FOREIGN KEY (sub_code) REFERENCES subject(sub_code)
);
-- Insert Sample Data
INSERT INTO student (roll_no, name) VALUES (1, 'John Doe');
INSERT INTO subject (sub_code, sub_name) VALUES (101, 'Mathematics');
INSERT INTO result (roll_no, sub_code, marks) VALUES (1, 101, 85);
Compute Minimum, Maximum, Total, Average Marks
SELECT
MIN(marks) AS min_marks,
MAX(marks) AS max_marks,
SUM(marks) AS total_marks,
AVG(marks) AS average_marks
FROM result;
b) Write a user defined function to update and release the student’s result with percentage.
CREATE OR REPLACE FUNCTION update_result(roll_no INT, sub_code INT, new_marks
INT)
RETURN VARCHAR IS
BEGIN
UPDATE result
SET marks = new_marks
WHERE roll_no = roll_no AND sub_code = sub_code;
COMMIT;
RETURN 'Result updated successfully';
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'No matching record found';
WHEN OTHERS THEN
RETURN 'Error occurred: ' || SQLERRM;
END;
/
4 a) Create a Customer, Saving_account, Loan_account table in banking database by using
DDL and DML commands
-- Create Customer Table
CREATE TABLE customer (
cust_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(100)
);
-- Create Saving Account Table
CREATE TABLE saving_account (
acc_no INT PRIMARY KEY,
balance DECIMAL(10, 2),
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
);
-- Create Loan Account Table
CREATE TABLE loan_account (
loan_id INT PRIMARY KEY,
amount DECIMAL(15, 2),
cust_id INT,
FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
);
b) Write a procedure to insert 50 records for each table in above banking database.
CREATE OR REPLACE PROCEDURE insert_records IS
BEGIN
FOR i IN 1..50 LOOP
INSERT INTO customer (cust_id, name, address)
VALUES (i, 'Customer ' || i, 'Address ' || i);
INSERT INTO saving_account (acc_no, balance, cust_id)
VALUES (i, 1000.00, i);
INSERT INTO loan_account (loan_id, amount, cust_id)
VALUES (i, 5000.00, i);
END LOOP;
COMMIT;
END;
/
5 a) Create and insert records in student table and course table and also display the records
usingdifferent types of join operation.
-- Create Student Table
CREATE TABLE student (
roll_no INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
-- Create Course Table
CREATE TABLE course (
course_id INT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL
);
-- Create Enrollment Table
CREATE TABLE enrollment (
roll_no INT,
course_id INT,
PRIMARY KEY (roll_no, course_id),
FOREIGN KEY (roll_no) REFERENCES student(roll_no),
FOREIGN KEY (course_id) REFERENCES course(course_id)
);
-- Insert Sample Data
INSERT INTO student (roll_no, name) VALUES (1, 'John Doe');
INSERT INTO course (course_id, course_name) VALUES (101, 'Computer Science');
INSERT INTO enrollment (
::contentReference[oaicite:0]{index=0}
b) Write a procedure to display month’s name while passing the month as number
parameter.(Example: if pass parameter as 1 it should display as January.)
CREATE OR REPLACE PROCEDURE show_month_name(p_month_num IN NUMBER) IS
v_month_name VARCHAR2(20);
BEGIN
IF p_month_num BETWEEN 1 AND 12 THEN
v_month_name := TO_CHAR(TO_DATE(p_month_num, 'MM'), 'Month');
DBMS_OUTPUT.PUT_LINE('Month Name: ' || TRIM(v_month_name));
ELSE
DBMS_OUTPUT.PUT_LINE('Invalid month number. Please enter between 1 and 12.');
END IF;
END;
/
Execution Example:
SET SERVEROUTPUT ON;
EXEC show_month_name(1); -- Output: Month Name: January
6 a) Write a DCL command to allow the user to create, insert, update and delete operation
and also disallow the particular user to perform delete operation.
-- Create user
CREATE USER demo_user IDENTIFIED BY password;
-- Grant privileges except DELETE
GRANT CREATE SESSION, CREATE TABLE, INSERT, UPDATE TO demo_user;
-- Explicitly revoke DELETE if previously granted
REVOKE DELETE ON any_table FROM demo_user;
b) Create XML database to conduct online quiz and declare the quiz results.
<quiz>
<question id="1">
<text>What is the capital of France?</text>
<option>A. Berlin</option>
<option>B. Madrid</option>
<option>C. Paris</option>
<answer>C</answer>
</question>
<result>
<student id="101">
<name>John Doe</name>
<score>80</score>
<status>Passed</status>
</student>
</result>
</quiz>
Note: You can store this in an XMLType column in Oracle:
CREATE TABLE quiz_data (
id NUMBER PRIMARY KEY,
data XMLTYPE
);
7 a) Write and execute complex transaction in a larger database and also realize TCL
commands.
BEGIN
-- Start transaction
SAVEPOINT start_point;
-- Insert into multiple tables
INSERT INTO customer VALUES (201, 'Amit', 'Delhi');
INSERT INTO saving_account VALUES (301, 5000, 201);
-- Intentional error
-- INSERT INTO loan_account VALUES (301, -5000, 201); -- Fails due to CHECK
-- Commit if all good
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO start_point;
DBMS_OUTPUT.PUT_LINE('Transaction failed, rollback performed.');
END;
/
b) Create Document, column and graph based data using NOSQL database tools.
Using MongoDB (Document-based)
// Insert a document in a "students" collection
{
"roll_no": 101,
"name": "John Doe",
"marks": {
"math": 90,
"science": 85
}
}
Using Cassandra (Column-based)
CREATE TABLE student_scores (
roll_no INT PRIMARY KEY,
name TEXT,
math INT,
science INT
);
Using Neo4j (Graph-based)
CREATE (s:Student {name: 'Alice'})
CREATE (c:Course {name: 'Math'})
CREATE (s)-[:ENROLLED_IN]->(c);
8.Develop GUI based application software for Hostel Management.
Description
A GUI-based application could be built using Python (Tkinter or PyQt) or Java (Swing/JavaFX)
with a backend database like MySQL/Oracle.
Basic Features
Student Registration
Room Allocation
Fee Management
Attendance Tracking
Report Generation
Sample Output Using Python Tkinter
import tkinter as tk
def submit():
print("Student Name:", entry_name.get())
print("Room No:", entry_room.get())
app = tk.Tk()
app.title("Hostel Management")
tk.Label(app, text="Student Name").pack()
entry_name = tk.Entry(app)
entry_name.pack()
tk.Label(app, text="Room Number").pack()
entry_room = tk.Entry(app)
entry_room.pack()
tk.Button(app, text="Submit", command=submit).pack()
app.mainloop()
Output:
A GUI form appears with fields for student name and room number and prints data on
submission.
