Set-8

1) ER Diagram for Flipkart:
Entities: User, Product, Order, Payment, Cart, Review
Relationships:

User places Order (one-to-many)
User adds Product to Cart (one-to-many)
Order contains Product (many-to-many)
Payment is related to Order (one-to-one)
User writes Review for Product (one-to-many)


2) Trigger to Update Instructor's Name in Courses Table:

CREATE OR REPLACE TRIGGER update_instructor_name
AFTER UPDATE OF instructor_name ON Students
FOR EACH ROW
BEGIN
    UPDATE Courses
    SET instructor_name = :new.instructor_name
    WHERE instructor_id = :new.student_id;
END;


3) SQL Queries:
A)
ALTER TABLE Students
ADD Email VARCHAR2(100);
B)
UPDATE Students
SET Email = 'example@email.com'
WHERE StudentID = 12345;
C)
SELECT CourseID, CourseName
FROM Courses
WHERE CourseID IN (
    SELECT CourseID
    FROM Enrollments
    WHERE StudentID IN (
        SELECT StudentID
        FROM Students
        WHERE GPA < 2.5
    )
);
D)
SELECT CourseID, CourseName
FROM Courses
WHERE CreditHours > (
    SELECT AVG(CreditHours)
    FROM Courses
);
E)
SELECT *
FROM Students
WHERE LastName LIKE '%son';
F)
SELECT BirthDate, COUNT(*)
FROM Students
GROUP BY BirthDate;
G)
SELECT BirthDate
FROM Students
GROUP BY BirthDate
HAVING COUNT(*) > 1;
H)
UPDATE Courses
SET Instructor = 'New Instructor'
WHERE CourseID = 'SpecificCourseID';