Insem2023.txt

 Q3.a Consider the following schemas
 Emp(Emp_no, Emp_name, Dept_no)
 Dept(Dept_no, Dept_name)
 Address(Dept_name, Dept_location)
 Write SQL queries for the following

CREATE TABLE Department(
    Dept_no int PRIMARY KEY,
    Dept_Name varchar(20)
);

CREATE TABLE Emp(
    Emp_no int PRIMARY KEY,
    Emp_name varchar(20),
    Dept_no int REFERENCES Department(Dept_no) ON DELETE CASCADE
);

CREATE TABLE Address(
    Dept_Name varchar(20),
    Dept_location varchar(20),
    PRIMARY KEY(Dept_name, Dept_location)
);

 i. Display the location of department where employee ‘Ram’ is working.
 SELECT Dept_location FROM Address 
 WHERE Dept_Name=(
    SELECT Dept_Name FROM Department 
    NATURAL JOIN Emp
    WHERE Emp_name='Ram'
 );

 ii. Create a view to store total no of employees working in each
 department in ascending order.
CREATE OR REPLACE VIEW Emp_Count AS
SELECT Dept_no, COUNT(Emp_no) AS Emp_Count FROM Emp
GROUP BY Dept_no ORDER BY Emp_Count;

 iii. Find the name of the department in which no employee is working
 SELECT d.Dept_Name FROM Department d
 LEFT OUTER JOIN Emp e
 ON d.Dept_no=e.Dept_no
 WHERE Emp_no IS NULL;

 --------------------------------------------------------------------------------------
 Q3.c. Consider following schema
 Student_fee_details (rollno, name, fee_deposited, date)
 Write a trigger to preserve old values of student fee details before updating
 in the table

 CREATE TABLE Student_fee_details (
    rollno INT,
    name VARCHAR2(100),
    fee_deposited NUMBER(10,2),
    payment_date DATE
)
/

INSERT INTO Student_fee_details (rollno, name, fee_deposited, payment_date) VALUES (1, 'Alice Smith', 1500.00, TO_DATE('2024-01-15', 'YYYY-MM-DD'))/
INSERT INTO Student_fee_details (rollno, name, fee_deposited, payment_date) VALUES (2, 'Bob Johnson', 2000.00, TO_DATE('2024-02-20', 'YYYY-MM-DD'))/
INSERT INTO Student_fee_details (rollno, name, fee_deposited, payment_date) VALUES (3, 'Charlie Brown', 1800.00, TO_DATE('2024-03-10', 'YYYY-MM-DD'))/
INSERT INTO Student_fee_details (rollno, name, fee_deposited, payment_date) VALUES (4, 'Diana Prince', 1700.00, TO_DATE('2024-04-05', 'YYYY-MM-DD'))/
INSERT INTO Student_fee_details (rollno, name, fee_deposited, payment_date) VALUES (5, 'Eve Adams', 1600.00, TO_DATE('2024-05-15', 'YYYY-MM-DD'))/
/

CREATE TABLE Student_Copy AS
(SELECT * FROM Student_fee_details)/

CREATE OR REPLACE TRIGGER prev_info
AFTER UPDATE ON Student_fee_details
FOR EACH ROW
BEGIN
    INSERT INTO Student_Copy (rollno, name, fee_deposited, payment_date)
    VALUES (:OLD.rollno, :OLD.name, :OLD.fee_deposited, :OLD.payment_date);

    dbms_output.put_line('Old data inserted into Student_Copy Table');
END;
/


UPDATE Student_fee_details SET name='Farkhanda Dalal' WHERE rollno=1;

select * from Student_Copy;

------------------------------------------------------------------------------------------------------
 04.a. Write a PL/SQL block of code which accepts the rollno from user. The
 attendance of rollno entered by user will be checked in
 student_attendance(RollNo, Attendance) table and display on the screen.

 CREATE TABLE student_attendence (
    RollNo INT,
    Attendance NUMBER(5,2)
)
/

INSERT INTO student_attendence (RollNo, Attendence) VALUES (1, 85.50)/
INSERT INTO student_attendence (RollNo, Attendence) VALUES (2, 92.75)/
INSERT INTO student_attendence (RollNo, Attendence) VALUES (3, 78.25)/
INSERT INTO student_attendence (RollNo, Attendence) VALUES (4, 88.00)/
INSERT INTO student_attendence (RollNo, Attendence) VALUES (5, 95.10)/
/


SET SERVEROUTPUT ON;
SET SQLTERMINATOR /

DECLARE
roll student_attendence.RollNo%type;
attendence student_attendence.Attendence%type;

BEGIN
roll:=&roll;
SELECT Attendence INTO attendence FROM student_attendence WHERE RollNo=roll;

dbms_output.put_line('Attendence of '||roll||' = '||attendence);

END;
/