A bank's database records a fund transfer of ₹10,000 from Account A to Account B. The debit operation from Account A succeeds, but due to an unexpected system crash, the credit operation in Account B fails.
Explanation: A logical fund transfer transaction consists of two operations: Debit(A) and Credit(B). Atomicity mandates that either all operations of the transaction are committed or none are reflected in the database. When the system crashes after Debit(A) but before Credit(B), the transaction is left partially executed. If the debit persists without the credit, ₹10,000 is lost from the system.
Consistency Enforcement:
Write-Ahead Logging (WAL): Undo and redo log records must be flushed to non-volatile storage before dirty buffers are written to disk.
Crash Recovery (Undo Phase): Upon reboot, the recovery manager scans active transactions. Because the transfer lacks a COMMIT log record, an UNDO pass reverses the debit on Account A (Balance(A) := Balance(A) + 10000), restoring total balance consistency.
In an online shopping app, a customer places an order for 3 items. The stock count should decrease accordingly. After the transaction, the database shows the order was created, but the stock count remains unchanged.
Compromised Property: Consistency (and Atomicity of the composite transaction).
Explanation: Consistency ensures that a transaction takes the database from one valid state to another, satisfying all declared schema constraints and business invariants:
Stocknew=Stockold−QuantityOrdered
Impact: Recording the order without deducting stock leaves the database in an inconsistent state. The business faces overselling, phantom inventory discrepancies, and unfulfillable orders. Both updates must be bound within an atomic transaction:
BEGIN TRANSACTION;UPDATE inventory SET stock = stock - 3 WHERE item_id = 101;INSERT INTO orders (order_id, customer_id, item_id, quantity) VALUES (501, 12, 101, 3);COMMIT;
Two students simultaneously register for the last available seat in a university course. Both transactions read the seat as "available" before either commits. At the end, both registrations show as successful.
Transaction 1 updates available_seats = 0 and commits.
Transaction 2 updates available_seats = 0 and commits.
Both students receive confirmations for a single physical seat because neither transaction was isolated from the other's uncommitted state.
Resolution: Enforce Serializable isolation or pessimistic row-level locking:
BEGIN TRANSACTION;SELECT available_seats FROM courses WHERE course_id = 'CSE301' FOR UPDATE;-- If available_seats > 0, proceed; otherwise rollbackUPDATE courses SET available_seats = available_seats - 1 WHERE course_id = 'CSE301';INSERT INTO enrollments (student_id, course_id) VALUES (201, 'CSE301');COMMIT;
A ride-booking app confirms a cab booking to a customer. Right after confirmation, the server crashes, and when the system restarts, the booking record is missing from the database.
Violated Property: Durability.
Explanation: Durability guarantees that once a transaction commits, its changes survive any subsequent crash or power outage. If the server acknowledged confirmation to the user while the log records were still buffered in volatile memory and not flushed to persistent disk, the transaction is lost on restart.
Resolution:
Force-Log-at-Commit: The database must write and flush log records to physical disk (fsync) before transmitting commit success to the client application.
Redo Logging: During crash recovery, the recovery manager re-executes all committed actions logged prior to the crash.
In a banking system approving a loan, the database first deducts the processing fee from the customer's account and then records the loan details in the Loans table. If the system crashes after deducting the fee but before inserting loan details:
Violated Property: Atomicity.
Impact on Financial Records and Trust:
The customer's account balance decreases without their loan being generated, leading to customer disputes, audit discrepancies, and regulatory penalties.
The general ledger shows unallocated revenue with no offsetting loan liability.
Resolution: Group fee deduction and loan creation inside an atomic block. If the server crashes before commit, the recovery manager rolls back the fee deduction during restart.
In a railway reservation system, when booking a ticket, the database first marks a seat as reserved in the Seats table and then records passenger details in the Bookings table. If Passenger A and Passenger B attempt to book the same last seat simultaneously, both see it as available, and the system confirms both:
Violated Property: Isolation.
Operational Conflicts:
Two passengers are issued valid tickets for the identical physical berth/seat, causing boarding conflicts and train departure delays.
Resolution: Use atomic conditional updates or exclusive lock acquisition:
In a hospital billing system, once a patient's treatment is completed, the system calculates the bill amount and stores it in the Billing table. If the calculation is completed in memory but the bill record is never saved to disk due to a system crash:
Violated Property: Durability (and Atomicity of the discharge workflow).
Discrepancies Caused:
Medical treatments, medications, and physician services are rendered without corresponding accounts receivable.
Hospital accounting suffers revenue leakage and audit discrepancies between medical logs and financial accounts.
Resolution: The billing insert must commit to durable disk storage before the patient's checkout state is confirmed.
In a ride-sharing application, once a driver accepts a ride, the system updates the ride status in the Rides table and saves driver assignment details in the Assignments table. If the ride status is updated to 'Accepted' but the driver assignment insert fails:
Violated Property: Atomicity.
Resulting Confusion:
Passenger: Sees "Ride Accepted" but no assigned driver, vehicle number, or driver location.
Driver: Has no record of the ride in their active queue and is reassigned other trips.
System: The ride remains stranded in 'Accepted' status with no driver assigned to service it.
Resolution: Enclose both the status update and assignment insert inside an atomic database transaction. If the assignment insert fails, roll back the ride status back to 'Requested'.
A database records a fund transfer from Account A to Account B. The debit operation from Account A succeeds, but the credit operation in Account B fails due to a network timeout.
Violated Property: Atomicity.
Explanation: The transfer transaction is an all-or-nothing unit of work. Leaving Account A debited without crediting Account B violates atomicity and compromises ledger balance integrity. The database management system must execute an automatic rollback upon failure or during restart recovery.
An online food delivery company stores data about customers, restaurants, and orders. A customer has a customer ID, name, phone, and address. A restaurant has a restaurant ID, name, location, and cuisine type. A customer can place multiple orders. Each order has an order ID, date, and total amount. An order can contain multiple food items, and each food item has an item ID, name, and price. Restaurants prepare many food items.
An airline company maintains information about flights, passengers, and bookings. Each passenger has a passenger ID, name, gender, passport number, and contact number. A flight has a flight number, source, destination, and departure time. A passenger can book multiple flights, and each booking has a booking ID, date, and seat number. Each flight is operated by a pilot, and each pilot has a pilot ID, name, and license number.
A hospital keeps records of patients, doctors, and treatments. A patient has a patient ID, name, age, address, and phone. A doctor has a doctor ID, name, specialization, and phone number. Patients can consult multiple doctors, and doctors can treat many patients via appointments with details including appointment ID, date, and time. Patients may also be admitted, recording admission ID, admission date, and ward number.
An e-learning platform stores data about students, courses, and instructors. A student has a student ID, name, email, and phone. An instructor has an instructor ID, name, qualification, and email. A course has a course ID, title, and credits. A student can enroll in many courses, and an instructor can teach multiple courses. Each enrollment has an enrollment date and status.
A library keeps records of books, members, and borrowings. A member has a member ID, name, address, and phone. Each book has a book ID, title, author, and publication year. A member can borrow multiple books, and each borrowing has a borrow date and return date.
An online store records customers, products, and orders. Each customer has an ID, name, email, and address. Each product has an ID, name, category, and price. A customer can place many orders, and each order includes order ID, date, and total cost. Orders can contain multiple products.
A bank stores information about customers, accounts, and transactions. A customer has an ID, name, address, and phone. An account has an account number, type, and balance. A customer can hold multiple accounts. Each transaction has a transaction ID, date, amount, and type (deposit/withdrawal).
A hotel manages guests, rooms, and reservations. A guest has a guest ID, name, address, and phone. A room has a room number, type, and price. Guests can book many rooms, and each reservation has a reservation ID, check-in date, and check-out date.
A school stores details about students, teachers, and classes. A student has an ID, name, age, and grade. A teacher has a teacher ID, name, and subject specialization. A class has a class ID, name, and schedule. Students enroll in many classes, and teachers can teach multiple classes.
A cinema records customers, movies, and bookings. A customer has an ID, name, email, and phone. A movie has a movie ID, title, genre, and duration. A booking has a booking ID, date, and seat number. A customer can book multiple movies, and each movie has many bookings.
A hospital pharmacy maintains medicines, suppliers, and purchase orders. A medicine has a medicine ID, name, type, and price. A supplier has a supplier ID, name, and contact info. A purchase order has an order ID, date, and total amount. Medicines can be supplied by multiple suppliers.
A company stores information about employees, departments, and salaries. An employee has an employee ID, name, designation, and phone. A department has a department ID, name, and location. Each employee belongs to one department. Each salary record has a salary ID, date, and amount.
A hostel keeps records of students, rooms, and allocations. A student has a student ID, name, and department. A room has a room number, type, and capacity. A student is allocated to one room, and each room may hold multiple students. Allocations have start date and end date.
A streaming service stores users, playlists, and songs. A user has a user ID, name, and subscription type. A playlist has a playlist ID, name, and creation date. A song has a song ID, title, artist, and duration. Users can create multiple playlists, and playlists contain multiple songs.
A tournament records teams, players, and matches. A team has a team ID, name, and coach. A player has a player ID, name, age, and role. A match has a match ID, date, and venue. Teams participate in matches, and players belong to teams.
An insurance company maintains information about policyholders, policies, and claims. A policyholder has a holder ID, name, address, and phone. A policy has a policy ID, type, and premium amount. A policyholder can have multiple policies. A claim has a claim ID, date, and claim amount.
A learning portal keeps records of students, instructors, and assignments. A student has an ID, name, and email. An instructor has an ID, name, and expertise. Each assignment has an assignment ID, title, and deadline. Students submit assignments to instructors.
A real estate company stores data about clients, properties, and agents. A client has a client ID, name, and contact info. A property has a property ID, location, type, and price. An agent has an agent ID, name, and license number. Clients can buy or rent properties through agents.
A gym records members, trainers, and workout sessions. A member has a member ID, name, and membership type. A trainer has a trainer ID, name, and specialization. A workout session has a session ID, date, and duration. Members can attend multiple sessions, and trainers can conduct multiple sessions.
Model Exam as an entity set related to Course_Offering (N:1).
A student enrolls in Course_Offering via a binary relationship Enrollment.
Marks are recorded in Exam_Result(student_id [FK], exam_id [FK], marks, PRIMARY KEY (student_id, exam_id)), ensuring only a single binary relationship exists between student and course offering while supporting multiple exam evaluations.
A company is organized into DEPARTMENTs. Each department has a unique name, number, a manager with start date, and several locations.
A department controls a number of PROJECTs, each with a unique name, number, and location.
EMPLOYEEs have a name, SSN, address, salary, sex, and birth date. Each employee is assigned to one department, may work on several projects with specified hours/week, and has an immediate supervisor.
DEPENDENTs are tracked for insurance: dependent name, birth date, relationship to employee.
Both dependencies are partial dependencies on parts of the candidate key. Because no transitive dependencies exist between non-prime attributes, removing partial dependencies achieves both 2NF and 3NF:
CREATE TABLE students ( student_id NUMBER PRIMARY KEY, name VARCHAR2(50), department VARCHAR2(20), age NUMBER, marks NUMBER);CREATE TABLE courses ( course_id NUMBER PRIMARY KEY, course_name VARCHAR2(50), student_id NUMBER REFERENCES students(student_id), grade VARCHAR2(5));INSERT INTO students VALUES (1, 'Rahim', 'CSE', 21, 85);INSERT INTO students VALUES (2, 'Karim', 'EEE', 22, 75);INSERT INTO students VALUES (3, 'Anika', 'CSE', 19, 65);INSERT INTO students VALUES (4, 'Rafiq', 'BBA', 23, 55);INSERT INTO students VALUES (5, 'Arman', 'CSE', 20, 95);INSERT INTO students VALUES (6, 'Nadia', 'EEE', 18, 60);INSERT INTO students VALUES (7, 'Sajid', 'CSE', 22, 70);INSERT INTO students VALUES (8, 'Salman', 'LAW', 24, 50);INSERT INTO students VALUES (9, 'Farhan', 'EEE', 20, 80);INSERT INTO students VALUES (10, 'Mim', 'BBA', 19, NULL);INSERT INTO courses VALUES (101, 'Database', 1, 'A');INSERT INTO courses VALUES (102, 'Networking', 1, 'B');INSERT INTO courses VALUES (103, 'Database', 2, 'B');INSERT INTO courses VALUES (104, 'Operating Systems', 3, 'C');INSERT INTO courses VALUES (105, 'Networking', 4, 'A');INSERT INTO courses VALUES (106, 'Database', 5, 'A');INSERT INTO courses VALUES (107, 'AI', 5, 'A');INSERT INTO courses VALUES (108, 'Machine Learning', 6, 'B');INSERT INTO courses VALUES (109, 'Cybersecurity', 7, 'C');INSERT INTO courses VALUES (110, 'Database', 9, 'B');COMMIT;
-- 1. Retrieve all student names and their departmentsSELECT name, department FROM students;-- 2. Display the names of students whose age is greater than 20SELECT name FROM students WHERE age > 20;-- 3. List students with marks between 60 and 80SELECT * FROM students WHERE marks BETWEEN 60 AND 80;-- 4. Find students whose name starts with 'A'SELECT * FROM students WHERE name LIKE 'A%';-- 5. Show students whose department is either 'CSE' or 'EEE' using INSELECT * FROM students WHERE department IN ('CSE', 'EEE');-- 6. Display students not belonging to the 'CSE' departmentSELECT * FROM students WHERE department <> 'CSE';-- 7. Retrieve the list of distinct departments from the STUDENTS tableSELECT DISTINCT department FROM students;-- 8. Show all students with null values in the marks columnSELECT * FROM students WHERE marks IS NULL;-- 9. Display students sorted by marks in descending orderSELECT * FROM students ORDER BY marks DESC;-- 10. Retrieve the top 3 students with the highest marksSELECT * FROM ( SELECT * FROM students WHERE marks IS NOT NULL ORDER BY marks DESC) WHERE ROWNUM <= 3;-- 11. Find the total number of studentsSELECT COUNT(*) AS total_students FROM students;-- 12. Calculate the average marks of all studentsSELECT AVG(marks) AS average_marks FROM students;-- 13. Find the maximum marks in the STUDENTS tableSELECT MAX(marks) AS max_marks FROM students;-- 14. Display the minimum age among all studentsSELECT MIN(age) AS min_age FROM students;-- 15. Show the total marks obtained by students in the CSE departmentSELECT SUM(marks) AS total_cse_marks FROM students WHERE department = 'CSE';-- 16. Count how many students belong to the EEE departmentSELECT COUNT(*) AS total_eee_students FROM students WHERE department = 'EEE';-- 17. Display the department-wise average marksSELECT department, AVG(marks) AS avg_marks FROM students GROUP BY department;-- 18. Find the number of distinct departmentsSELECT COUNT(DISTINCT department) AS distinct_dept_count FROM students;-- 19. Show the student_id and their total number of courses takenSELECT student_id, COUNT(*) AS total_courses FROM courses GROUP BY student_id;-- 20. List departments having more than 2 studentsSELECT department, COUNT(*) AS student_count FROM students GROUP BY department HAVING COUNT(*) > 2;-- 21. Display student names along with the courses they are enrolled inSELECT s.name, c.course_name FROM students s JOIN courses c ON s.student_id = c.student_id;-- 22. Show students who are not enrolled in any course (LEFT JOIN)SELECT s.student_id, s.name FROM students s LEFT JOIN courses c ON s.student_id = c.student_id WHERE c.course_id IS NULL;-- 23. List all courses and the students who take them (INNER JOIN)SELECT c.course_name, s.name FROM courses c INNER JOIN students s ON c.student_id = s.student_id;-- 24. Display the names of students and their grades in each courseSELECT s.name, c.course_name, c.grade FROM students s JOIN courses c ON s.student_id = c.student_id;-- 25. Retrieve student names and course names using JOIN ONSELECT s.name AS student_name, c.course_name FROM students s JOIN courses c ON s.student_id = c.student_id;-- 26. Find students who got grade 'A' in any courseSELECT DISTINCT s.student_id, s.name FROM students s JOIN courses c ON s.student_id = c.student_id WHERE c.grade = 'A';-- 27. Show courses with no student enrolled (RIGHT JOIN)SELECT c.course_id, c.course_name FROM students s RIGHT JOIN courses c ON s.student_id = c.student_id WHERE s.student_id IS NULL;-- 28. List all students along with course names (FULL OUTER JOIN)SELECT s.name, c.course_name FROM students s FULL OUTER JOIN courses c ON s.student_id = c.student_id;-- 29. Display student names who are enrolled in more than one courseSELECT s.name FROM students s JOIN courses c ON s.student_id = c.student_id GROUP BY s.student_id, s.name HAVING COUNT(c.course_id) > 1;-- 30. Retrieve student and course information where course_name is 'Database'SELECT s.student_id, s.name, s.department, c.course_id, c.grade FROM students s JOIN courses c ON s.student_id = c.student_id WHERE c.course_name = 'Database';-- 31. Find students whose marks are above the average marksSELECT * FROM students WHERE marks > (SELECT AVG(marks) FROM students);-- 32. Display students who scored the highest marksSELECT * FROM students WHERE marks = (SELECT MAX(marks) FROM students);-- 33. Show the names of students who are enrolled in a course named 'Networking'SELECT name FROM students WHERE student_id IN (SELECT student_id FROM courses WHERE course_name = 'Networking');-- 34. Retrieve the department of the student who scored the lowest marksSELECT department FROM students WHERE marks = (SELECT MIN(marks) FROM students);-- 35. Find students older than the student named 'Rahim'SELECT * FROM students WHERE age > (SELECT age FROM students WHERE name = 'Rahim');-- 36. List students who have the same marks as student ID = 5SELECT * FROM students WHERE marks = (SELECT marks FROM students WHERE student_id = 5) AND student_id <> 5;-- 37. Show the student(s) who enrolled in the maximum number of coursesSELECT s.student_id, s.name, COUNT(*) AS course_count FROM students s JOIN courses c ON s.student_id = c.student_id GROUP BY s.student_id, s.name HAVING COUNT(*) = (SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM courses GROUP BY student_id));-- 38. Find the second-highest marks from STUDENTS using a subquerySELECT MAX(marks) AS second_highest_marks FROM students WHERE marks < (SELECT MAX(marks) FROM students);-- 39. List the names of students who are not enrolled in any course (subquery)SELECT name FROM students WHERE student_id NOT IN (SELECT student_id FROM courses WHERE student_id IS NOT NULL);-- 40. Retrieve students whose marks are greater than all students in the EEE departmentSELECT * FROM students WHERE marks > ALL (SELECT marks FROM students WHERE department = 'EEE' AND marks IS NOT NULL);-- 41. Retrieve students whose names end with the letter 'n'SELECT * FROM students WHERE name LIKE '%n';-- 42. Show students whose name contains the substring 'ar'SELECT * FROM students WHERE name LIKE '%ar%' OR name LIKE '%Ar%';-- 43. Find students aged between 18 and 22 using BETWEENSELECT * FROM students WHERE age BETWEEN 18 AND 22;-- 44. Display students whose marks are not between 50 and 70SELECT * FROM students WHERE marks NOT BETWEEN 50 AND 70;-- 45. Retrieve students whose names are exactly 5 characters longSELECT * FROM students WHERE name LIKE '_____';-- 46. List students who belong to departments starting with 'C'SELECT * FROM students WHERE department LIKE 'C%';-- 47. Show students whose marks are greater than 70 OR age less than 20SELECT * FROM students WHERE marks > 70 OR age < 20;-- 48. Display students whose department is not nullSELECT * FROM students WHERE department IS NOT NULL;-- 49. Find the students with marks in the set {55, 65, 75}SELECT * FROM students WHERE marks IN (55, 65, 75);-- 50. Retrieve student names and marks ordered first by department, then by marks descendingSELECT name, department, marks FROM students ORDER BY department ASC, marks DESC;
-- 1. Display name and marks of student with student_id = 5SET SERVEROUTPUT ON;DECLARE v_name students.name%TYPE; v_marks students.marks%TYPE;BEGIN SELECT name, marks INTO v_name, v_marks FROM students WHERE student_id = 5; DBMS_OUTPUT.PUT_LINE('Student: ' || v_name || ' | Marks: ' || v_marks);END;/-- 2. Display all student names from STUDENTS table using a loopDECLARE CURSOR c_students IS SELECT name FROM students; v_name students.name%TYPE;BEGIN OPEN c_students; LOOP FETCH c_students INTO v_name; EXIT WHEN c_students%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Student Name: ' || v_name); END LOOP; CLOSE c_students;END;/-- 3. Calculate and display the average marks of all studentsDECLARE v_avg NUMBER(5,2);BEGIN SELECT AVG(marks) INTO v_avg FROM students; DBMS_OUTPUT.PUT_LINE('Overall Average Marks: ' || v_avg);END;/-- 4. Check if student's marks > 80: display "Excellent", otherwise "Keep Trying"DECLARE v_marks students.marks%TYPE;BEGIN SELECT marks INTO v_marks FROM students WHERE student_id = 1; IF v_marks > 80 THEN DBMS_OUTPUT.PUT_LINE('Excellent'); ELSE DBMS_OUTPUT.PUT_LINE('Keep Trying'); END IF;END;/-- 5. Increase the marks of student_id = 3 by 10BEGIN UPDATE students SET marks = marks + 10 WHERE student_id = 3; COMMIT; DBMS_OUTPUT.PUT_LINE('Marks updated for student 3.');END;/-- 6. Find and display the department of a student named 'Rahim'DECLARE v_dept students.department%TYPE;BEGIN SELECT department INTO v_dept FROM students WHERE name = 'Rahim'; DBMS_OUTPUT.PUT_LINE('Department of Rahim: ' || v_dept);END;/
-- 7. Cursor to display all student names and their marksDECLARE CURSOR cur_all IS SELECT name, marks FROM students;BEGIN FOR r IN cur_all LOOP DBMS_OUTPUT.PUT_LINE('Name: ' || r.name || ' | Marks: ' || NVL(TO_CHAR(r.marks), 'N/A')); END LOOP;END;/-- 8. Cursor to display names of students who scored above 70DECLARE CURSOR cur_above70 IS SELECT name, marks FROM students WHERE marks > 70;BEGIN FOR rec IN cur_above70 LOOP DBMS_OUTPUT.PUT_LINE(rec.name || ' scored ' || rec.marks); END LOOP;END;/-- 9. Cursor to display student_id and total number of courses takenDECLARE CURSOR cur_course_count IS SELECT student_id, COUNT(*) AS total FROM courses GROUP BY student_id;BEGIN FOR rec IN cur_course_count LOOP DBMS_OUTPUT.PUT_LINE('StudentID: ' || rec.student_id || ' | Courses: ' || rec.total); END LOOP;END;/-- 10. Cursor to display all course names of student_id = 1DECLARE CURSOR cur_s1_courses IS SELECT course_name FROM courses WHERE student_id = 1;BEGIN FOR rec IN cur_s1_courses LOOP DBMS_OUTPUT.PUT_LINE('Course: ' || rec.course_name); END LOOP;END;/-- 11. Cursor to find students who have NULL marksDECLARE CURSOR cur_null_marks IS SELECT student_id, name FROM students WHERE marks IS NULL;BEGIN FOR rec IN cur_null_marks LOOP DBMS_OUTPUT.PUT_LINE('Student with NULL marks: ' || rec.name || ' (ID: ' || rec.student_id || ')'); END LOOP;END;/-- 12. Cursor to display students and their grades from COURSES tableDECLARE CURSOR cur_grades IS SELECT s.name, c.course_name, c.grade FROM students s JOIN courses c ON s.student_id = c.student_id;BEGIN FOR r IN cur_grades LOOP DBMS_OUTPUT.PUT_LINE(r.name || ' | ' || r.course_name || ' | Grade: ' || r.grade); END LOOP;END;/
-- 13. Function to return the marks of a student given student_idCREATE OR REPLACE FUNCTION get_student_marks(p_id IN NUMBER)RETURN NUMBER IS v_marks NUMBER;BEGIN SELECT marks INTO v_marks FROM students WHERE student_id = p_id; RETURN v_marks;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;END;/-- 14. Function to return the department of a given student nameCREATE OR REPLACE FUNCTION get_student_dept(p_name IN VARCHAR2)RETURN VARCHAR2 IS v_dept VARCHAR2(20);BEGIN SELECT department INTO v_dept FROM students WHERE name = p_name; RETURN v_dept;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Not Found';END;/-- 15. Function to calculate grade based on marks (>=80 A, 70-79 B, 60-69 C, else F)CREATE OR REPLACE FUNCTION calculate_grade(p_marks IN NUMBER)RETURN VARCHAR2 ISBEGIN IF p_marks IS NULL THEN RETURN 'N/A'; ELSIF p_marks >= 80 THEN RETURN 'A'; ELSIF p_marks >= 70 THEN RETURN 'B'; ELSIF p_marks >= 60 THEN RETURN 'C'; ELSE RETURN 'F'; END IF;END;/-- 16. Function to count how many students are in a given departmentCREATE OR REPLACE FUNCTION count_dept_students(p_dept IN VARCHAR2)RETURN NUMBER IS v_count NUMBER;BEGIN SELECT COUNT(*) INTO v_count FROM students WHERE department = p_dept; RETURN v_count;END;/-- 17. Function to calculate average marks of CSE department studentsCREATE OR REPLACE FUNCTION get_cse_avg_marksRETURN NUMBER IS v_avg NUMBER;BEGIN SELECT AVG(marks) INTO v_avg FROM students WHERE department = 'CSE'; RETURN ROUND(v_avg, 2);END;/-- 18. Function to return number of courses taken by given student_idCREATE OR REPLACE FUNCTION get_course_count(p_id IN NUMBER)RETURN NUMBER IS v_count NUMBER;BEGIN SELECT COUNT(*) INTO v_count FROM courses WHERE student_id = p_id; RETURN v_count;END;/
-- 19. Procedure to display student details (name, department, marks) for given student_idCREATE OR REPLACE PROCEDURE display_student_details(p_id IN NUMBER) IS v_name students.name%TYPE; v_dept students.department%TYPE; v_marks students.marks%TYPE;BEGIN SELECT name, department, marks INTO v_name, v_dept, v_marks FROM students WHERE student_id = p_id; DBMS_OUTPUT.PUT_LINE('ID: ' || p_id || ' | Name: ' || v_name || ' | Dept: ' || v_dept || ' | Marks: ' || v_marks);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Student ID ' || p_id || ' not found.');END;/-- 20. Procedure to update marks of a student by adding 5CREATE OR REPLACE PROCEDURE add_five_marks(p_id IN NUMBER) ISBEGIN UPDATE students SET marks = marks + 5 WHERE student_id = p_id; COMMIT; DBMS_OUTPUT.PUT_LINE('Added 5 marks to Student ID: ' || p_id);END;/-- 21. Procedure to display all students in CSE departmentCREATE OR REPLACE PROCEDURE display_cse_students ISBEGIN FOR r IN (SELECT student_id, name, marks FROM students WHERE department = 'CSE') LOOP DBMS_OUTPUT.PUT_LINE('ID: ' || r.student_id || ' | ' || r.name || ' | Marks: ' || r.marks); END LOOP;END;/-- 22. Procedure to insert a new student into STUDENTS tableCREATE OR REPLACE PROCEDURE insert_student( p_id IN NUMBER, p_name IN VARCHAR2, p_dept IN VARCHAR2, p_age IN NUMBER, p_marks IN NUMBER) ISBEGIN INSERT INTO students (student_id, name, department, age, marks) VALUES (p_id, p_name, p_dept, p_age, p_marks); COMMIT; DBMS_OUTPUT.PUT_LINE('Inserted student: ' || p_name);END;/-- 23. Procedure to delete student by student_idCREATE OR REPLACE PROCEDURE delete_student(p_id IN NUMBER) ISBEGIN DELETE FROM courses WHERE student_id = p_id; DELETE FROM students WHERE student_id = p_id; COMMIT; DBMS_OUTPUT.PUT_LINE('Deleted student ID: ' || p_id);END;/-- 24. Procedure to display all course names for given student_idCREATE OR REPLACE PROCEDURE display_student_courses(p_id IN NUMBER) ISBEGIN DBMS_OUTPUT.PUT_LINE('Courses for Student ' || p_id || ':'); FOR r IN (SELECT course_name, grade FROM courses WHERE student_id = p_id) LOOP DBMS_OUTPUT.PUT_LINE('- ' || r.course_name || ' (Grade: ' || r.grade || ')'); END LOOP;END;/
-- 25. Trigger to prevent deletion of any student recordCREATE OR REPLACE TRIGGER trg_prevent_student_deleteBEFORE DELETE ON studentsFOR EACH ROWBEGIN RAISE_APPLICATION_ERROR(-20001, 'Deletion of student records is prohibited.');END;/-- 26. Trigger to prevent insertion if marks > 100CREATE OR REPLACE TRIGGER trg_check_marks_insertBEFORE INSERT ON studentsFOR EACH ROWBEGIN IF :NEW.marks > 100 THEN RAISE_APPLICATION_ERROR(-20002, 'Marks cannot exceed 100.'); END IF;END;/-- 27. Trigger to log whenever a student's marks are updatedCREATE TABLE student_marks_audit ( audit_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, student_id NUMBER, old_marks NUMBER, new_marks NUMBER, changed_by VARCHAR2(50), changed_at DATE);CREATE OR REPLACE TRIGGER trg_log_marks_updateAFTER UPDATE OF marks ON studentsFOR EACH ROWBEGIN INSERT INTO student_marks_audit (student_id, old_marks, new_marks, changed_by, changed_at) VALUES (:OLD.student_id, :OLD.marks, :NEW.marks, USER, SYSDATE);END;/-- 28. Trigger to automatically set department = 'Unknown' if NULL is insertedCREATE OR REPLACE TRIGGER trg_default_deptBEFORE INSERT ON studentsFOR EACH ROWBEGIN IF :NEW.department IS NULL THEN :NEW.department := 'Unknown'; END IF;END;/-- 29. Trigger to prevent insertion of student with age < 18CREATE OR REPLACE TRIGGER trg_prevent_underageBEFORE INSERT ON studentsFOR EACH ROWBEGIN IF :NEW.age < 18 THEN RAISE_APPLICATION_ERROR(-20003, 'Student age must be at least 18.'); END IF;END;/-- 30. Trigger to print a message when a new course is insertedCREATE OR REPLACE TRIGGER trg_notify_new_courseAFTER INSERT ON coursesFOR EACH ROWBEGIN DBMS_OUTPUT.PUT_LINE('New course assigned: ' || :NEW.course_name || ' to Student ID: ' || :NEW.student_id);END;/
-- Implicit Cursor Attributes Demo (%FOUND, %ROWCOUNT)BEGIN UPDATE students SET marks = marks + 2 WHERE department = 'EEE'; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated successfully.'); ELSE DBMS_OUTPUT.PUT_LINE('No records updated.'); END IF;END;/-- Explicit Cursor Fetching Top 3 RecordsDECLARE CURSOR c_top3 IS SELECT name, marks FROM students WHERE marks IS NOT NULL ORDER BY marks DESC; v_name students.name%TYPE; v_marks students.marks%TYPE;BEGIN OPEN c_top3; LOOP FETCH c_top3 INTO v_name, v_marks; EXIT WHEN c_top3%NOTFOUND OR c_top3%ROWCOUNT > 3; DBMS_OUTPUT.PUT_LINE(v_name || ' - Marks: ' || v_marks); END LOOP; CLOSE c_top3;END;/
-- Validation Trigger: Prevent negative salary updateCREATE OR REPLACE TRIGGER trg_emp_salaryBEFORE UPDATE OF salary ON employeesFOR EACH ROWBEGIN IF :NEW.salary < 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.'); END IF;END;/-- Audit Trigger: Record deleted employeesCREATE TABLE emp_audit ( emp_id NUMBER, deleted_on DATE);CREATE OR REPLACE TRIGGER trg_emp_auditAFTER DELETE ON employeesFOR EACH ROWBEGIN INSERT INTO emp_audit (emp_id, deleted_on) VALUES (:OLD.employee_id, SYSDATE);END;/-- Trigger to prevent inserting employee with salary < 1000CREATE OR REPLACE TRIGGER prevent_low_salaryBEFORE INSERT ON employeesFOR EACH ROWBEGIN IF :NEW.salary < 1000 THEN RAISE_APPLICATION_ERROR(-20002, 'Salary must be at least 1000.'); END IF;END;/-- Statement-Level Trigger: Display message after employee insertionCREATE OR REPLACE TRIGGER show_insert_messageAFTER INSERT ON employeesBEGIN DBMS_OUTPUT.PUT_LINE('A new employee has been added to the database.');END;/-- Trigger to auto-increase salary by 10% on updateCREATE OR REPLACE TRIGGER auto_increase_salaryBEFORE UPDATE OF salary ON employeesFOR EACH ROWBEGIN :NEW.salary := :NEW.salary * 1.10;END;/-- INSTEAD OF Trigger on Complex ViewCREATE OR REPLACE TRIGGER emp_dept_insertINSTEAD OF INSERT ON emp_dept_viewFOR EACH ROWBEGIN INSERT INTO employees (emp_id, name, dept_id) VALUES (:NEW.emp_id, :NEW.name, :NEW.dept_id);END;/
Find employees who are part of a project with a budget greater than 50,000.
SELECT emp_id, emp_nameFROM EmployeeWHERE emp_id IN ( SELECT emp_id FROM Works_On WHERE project_id IN ( SELECT project_id FROM Project WHERE budget > 50000 ));
Display the department name of the employee with the highest salary.
SELECT ( SELECT d.dept_name FROM Department d WHERE d.dept_id = e.dept_id) AS department_nameFROM Employee eWHERE e.salary = ( SELECT MAX(salary) FROM Employee);
List all projects handled by employees from the HR department.
SELECT project_id, project_nameFROM ProjectWHERE project_id IN ( SELECT project_id FROM Works_On WHERE emp_id IN ( SELECT emp_id FROM Employee WHERE dept_id IN ( SELECT dept_id FROM Department WHERE dept_name = 'HR' ) ));
Retrieve details of employees who have joined after the earliest joining date in the IT department.
SELECT *FROM EmployeeWHERE join_date > ALL ( SELECT MIN(e.join_date) FROM Employee e JOIN Department d ON e.dept_id = d.dept_id WHERE d.dept_name = 'IT');
List all employees who are not working on projects that have a budget below 60,000.
SELECT emp_id, emp_nameFROM EmployeeWHERE emp_id NOT IN ( SELECT w.emp_id FROM Works_On w JOIN Project p ON w.project_id = p.project_id WHERE p.budget < 60000);
List the names of employees who are involved in all projects handled by the IT department.
SELECT e.emp_nameFROM Employee eWHERE NOT EXISTS ( SELECT p.project_id FROM Project p JOIN Department d ON p.dept_id = d.dept_id WHERE d.dept_name = 'IT' MINUS SELECT w.project_id FROM Works_On w WHERE w.emp_id = e.emp_id);
Find employees who are working on some projects that employees from the Marketing department are also working on.
SELECT DISTINCT e.emp_id, e.emp_nameFROM Employee eJOIN Works_On w ON e.emp_id = w.emp_idWHERE w.project_id = SOME ( SELECT w2.project_id FROM Works_On w2 JOIN Employee e2 ON w2.emp_id = e2.emp_id JOIN Department d ON e2.dept_id = d.dept_id WHERE d.dept_name = 'Marketing')AND e.dept_id <> ( SELECT dept_id FROM Department WHERE dept_name = 'Marketing');
List all employees whose salary is greater than the salary of any employee in the HR department.
SELECT emp_id, emp_name, salaryFROM EmployeeWHERE salary > ANY ( SELECT e.salary FROM Employee e JOIN Department d ON e.dept_id = d.dept_id WHERE d.dept_name = 'HR');
Using a Common Table Expression (WITH), list the total budget of projects handled by each department.
WITH DeptProjectBudget AS ( SELECT dept_id, SUM(budget) AS total_project_budget FROM Project GROUP BY dept_id)SELECT d.dept_id, d.dept_name, NVL(dpb.total_project_budget, 0) AS total_budgetFROM Department dLEFT JOIN DeptProjectBudget dpb ON d.dept_id = dpb.dept_id;
Find employees who are part of projects where the budget is either the highest or the lowest across all projects.
SELECT DISTINCT e.emp_id, e.emp_name, p.project_name, p.budgetFROM Employee eJOIN Works_On w ON e.emp_id = w.emp_idJOIN Project p ON w.project_id = p.project_idWHERE p.budget IN ( (SELECT MAX(budget) FROM Project), (SELECT MIN(budget) FROM Project));