common_queries.sql

-- In this SQL file, write (and comment!) the typical SQL queries users will run on your database

-- INSERTs
-- -- Add student
INSERT INTO "students" ("student_number", "first_name", "last_name", "email")
VALUES ('123456789', 'Vitor', 'Betmann', 'vitorbetmann@harvard.edu');
-- -- Add company
INSERT INTO "companies" ("name", "industry", "city", "province")
VALUES ('Night''s Watch', 'film and tv', 'Toronto', 'ON');
-- -- Add supervisor
INSERT INTO "supervisors" ("first_name", "last_name", "company_id", "position", "phone_number", "email")
VALUES ('Jon',
        'Snow', (
        SELECT "id" FROM "companies" WHERE "name" = 'Night''s Watch'),
        'Lord Commander',
        '(555) 555-5555',
        'jonsnow@ravenmail.ws');
-- -- Add internship
INSERT INTO "internships" ("student_id", "supervisor_id", "student_position", "work_type")
VALUES ((SELECT "id" FROM "students" WHERE "student_number" = '123456789'),
        (SELECT "id" FROM "supervisors" WHERE "email" = 'jonsnow@ravenmail.ws'),
        'squire',
        'in person');
-- -- Add student feedback
INSERT INTO "student_feedback" ("internship_id", "internship_score", "comment")
VALUES ((SELECT "id" FROM "internships" WHERE "student_id" = 1 AND "supervisor_id" = 1),
        1,
        'Never again...'); -- student_id and supervisor_id arbitraty to shorten query example
-- -- Add supervisor feedback
INSERT INTO "supervisor_feedback" (
    "internship_id",
    "initiative_score",
    "quality_of_work_score",
    "productivity_score",
    "reliability_score",
    "comment")
VALUES (5, 4, 4, 5, 4, 'Would love to offer a full-time position!'); -- internship_id arbitraty to shorten query example


-- SELECTs
-- Find the contact info of a supervisor
SELECT "phone_number", "email"
FROM "supervisors" AS "s"
JOIN "internships" AS "i"
ON "s"."id" = "i"."supervisor_id"
WHERE "student_id" = (
    SELECT "id"
    FROM "students"
    WHERE "student_number" = '898989898'
);
-- -- Find how a given student was evaluated
SELECT "internship_id",
       ROUND(("initiative_score" + "quality_of_work_score" + "productivity_score" + "reliability_score") / 4, 2)
       AS 'average_score',
       "initiative_score",
       "quality_of_work_score",
       "productivity_score",
       "reliability_score"
FROM "supervisor_feedback"
WHERE "internship_id" = (
    SELECT "id" FROM "internships"
    WHERE "student_id" = (
        SELECT "id" FROM "students" WHERE "student_number" = '123456789'
    )
    AND "start_date" = '2026-01-09'
);
-- -- Find students working in person
SELECT "s"."student_number", "s"."last_name", "s"."first_name", "i"."student_position"
FROM "students" AS "s"
JOIN "internships" AS "i" ON "s"."id" = "i"."student_id"
WHERE "i"."work_type" = 'in person'
ORDER BY "s"."last_name";
-- -- Find students who didn't yet find an internship
SELECT * FROM "students" WHERE "id" NOT IN (
    SELECT "id" FROM "students"
    INTERSECT
    SELECT "student_id" FROM "internships"
);
-- -- Find all jobs a given company has offered in the last year
SELECT "i"."student_position" AS 'hbo_internships' FROM "internships" AS "i"
JOIN "supervisors" AS "s" ON "i"."supervisor_id" = "s"."id"
JOIN "companies" AS "c" ON "s"."company_id" = "c"."id"
WHERE "i"."start_date" BETWEEN '2025-01-01' AND '2025-12-31' -- Using BETWEEN over LIKE to take advantage of the idx_start_date INDEX
AND "c"."name" = 'HBO MAX'
GROUP BY "student_position";
-- Find the top 5 companies most likely to hire remotely
SELECT "c"."id", "c"."name", COUNT("i"."work_type") AS 'amount'
FROM "companies" AS "c"
JOIN "supervisors" AS "s" ON "c"."id" = "s"."company_id"
JOIN "internships" AS "i" ON "s"."id" = "i"."supervisor_id"
WHERE "i"."work_type" = 'remote'
GROUP BY "c"."id"
ORDER BY "amount" DESC LIMIT 5;
-- -- Find all students who already fulfilled over 10 days of work across any numbers of internships
SELECT "s"."student_number", "s"."last_name", "s"."first_name"
FROM "students" AS "s"
JOIN "internships" AS "i" ON "s"."id" = "i"."student_id"
GROUP BY "s"."id"
HAVING SUM(julianday(COALESCE("i"."end_date", CURRENT_DATE)) - julianday("i"."start_date")) > 10
ORDER BY "s"."last_name";


-- DELETEs
-- -- Blacklist a company
DELETE FROM "companies" WHERE "name" = 'HBO MAX'; -- This will trigger a soft delete


-- UPDATEs
-- -- Whitelist a company from blacklisted
UPDATE "companies" SET "blacklisted" = NULL WHERE "name" = 'HBO MAX';
-- -- Update end_date after internship is over
UPDATE "internships" SET "end_date" = '2027-01-09' WHERE "id" = 6;