-- This setup provides a starting foundation for managing tutoring services, tracking sessions, monitoring revenue, and collecting valuable feedback for business growth.






--Copy code below



--first view the data to see if it contains data:


SELECT
    STUDENT_ID,
    FIRST_NAME,
    LAST_NAME,
    EMAIL,
    PHONE,
    GRADE_LEVEL
FROM
    JUAN.INTERM_STUDENTS;




--Insert Sample Data for Students Table




INSERT  INTO INTERM_STUDENTS (First_Name, Last_Name, Email, Phone, Grade_Level)
    VALUES ('Pedro', 'Martinez', 'pedromartinez@gmail.com', '555-1235', 'Grade 9')
INSERT  INTO INTERM_STUDENTS (First_Name, Last_Name, Email, Phone, Grade_Level)
    VALUES ('Sarah', 'Johnson', 'sarah@gmail.com', '555-1234', 'Grade 10')
INSERT  INTO INTERM_STUDENTS (First_Name, Last_Name, Email, Phone, Grade_Level)
    VALUES ('Michael', 'Lee', 'michael@gmail.com', '555-5678', 'Grade 8')
INSERT  INTO INTERM_STUDENTS (First_Name, Last_Name, Email, Phone, Grade_Level)
    VALUES ('Emily', 'Davis', 'emily@gmail.com', '555-9876', 'Grade 12')


-- Insert Sample Data for Tutors Table
INSERT INTO INTERM_Tutors (First_Name, Last_Name, Subject, Hourly_Rate, Experience_Years) VALUES
('John', 'Smith', 'Math', 50, 5);
INSERT INTO INTERM_Tutors (First_Name, Last_Name, Subject, Hourly_Rate, Experience_Years) VALUES
('Amanda', 'Brown', 'Math', 45, 3);


select * from interm_tutors;


select * from interm_students;

-- Insert Sample Data for Sessions Table
-- NOTE if you get an error, check the student and tutor ids in each line below
INSERT INTO INTERM_Sessions (Student_ID, Tutor_ID, Session_Date, Duration, Cost) VALUES
(2, 1, TO_DATE('2024-12-10', 'YYYY-MM-DD'), 2, 100); -- Sarah with John
INSERT INTO INTERM_Sessions (Student_ID, Tutor_ID, Session_Date, Duration, Cost) VALUES
(3, 2, TO_DATE('2024-12-12', 'YYYY-MM-DD'), 1.5, 67.5); -- Michael with Amanda
INSERT INTO INTERM_Sessions (Student_ID, Tutor_ID, Session_Date, Duration, Cost) VALUES
(4, 1, TO_DATE('2024-12-15', 'YYYY-MM-DD'), 2, 100); -- Emily with John


select * from INTERM_Sessions;



select * from interm_sessions;

--Insert Sample Data for Feedback Table

INSERT INTO interm_Feedback (Session_ID, Rating, Comments) VALUES
(2, 5, 'Excellent session, very helpful.');
INSERT INTO interm_Feedback (Session_ID, Rating, Comments) VALUES
(3, 4, 'Good session, but needs examples.');
INSERT INTO interm_Feedback (Session_ID, Rating, Comments) VALUES
(4, 5, 'Great tutoring!');



-- Insert Sample Data for Payments Table
INSERT INTO interm_Payments (Student_ID, Payment_Date, Amount, Payment_Method) VALUES
(2, TO_DATE('2024-12-11', 'YYYY-MM-DD'), 100, 'Credit Card');
INSERT INTO interm_Payments (Student_ID, Payment_Date, Amount, Payment_Method) VALUES
(3, TO_DATE('2024-12-13', 'YYYY-MM-DD'), 67.5, 'Cash');
INSERT INTO interm_Payments (Student_ID, Payment_Date, Amount, Payment_Method) VALUES
(4, TO_DATE('2024-12-16', 'YYYY-MM-DD'), 100, 'Credit Card');
 




-- Sample Business Queries
-- 1. Calculate Total Revenue Earned by Each Tutor

SELECT 
    t.First_Name || ' ' || t.Last_Name AS Tutor_Name,
    SUM(s.Cost) AS Total_Revenue
FROM 
    Interm_Tutors t
JOIN 
    Interm_Sessions s ON t.Tutor_ID = s.Tutor_ID
GROUP BY 
    t.First_Name, t.Last_Name;
-- Output:
-- Tutor_Name	Total_Revenue
-- John Smith	200
-- Amanda Brown	67.5
 


-- 2. Find Students with Outstanding Feedback (Rating < 5)
SELECT 
    s.First_Name || ' ' || s.Last_Name AS Student_Name,
    f.Rating,
    f.Comments
FROM 
    interm_Feedback f
JOIN 
    interm_Sessions sess ON f.Session_ID = sess.Session_ID
JOIN 
    interm_Students s ON sess.Student_ID = s.Student_ID
WHERE 
    f.Rating < 5;
-- Output:
-- Student_Name	Rating	Comments
-- Michael Lee	4	Good session, needs examples





-- 3. List Payments by Payment Method
SELECT 
    Payment_Method,
    SUM(Amount) AS Total_Amount
FROM 
    Interm_Payments
GROUP BY 
    Payment_Method;
-- Output:
-- Payment_Method	Total_Amount
-- Credit Card	200
-- Cash	67.5
 
-- This setup provides a starting foundation for managing tutoring services, tracking sessions, monitoring revenue, and collecting valuable feedback for business growth.