--Queries

--Q1

SELECT DISTINCT Name
FROM PROF
WHERE Working-Dept = 'IS' AND Salary > 1000 AND
EXISTS(
    SELECT C-Code
    FROM PREREQUESTS
    WHERE Pre-Code = 'CS101' AND C-Code in
    (
        SELECT C-Code FROM TEACHES WHERE TEACHES.P-ID = PROF.P-ID
    )
)


--Q2

SELECT Major, AVG(GPA) as AverageGPA
FROM STUDENTS
GROUP BY Major
HAVING COUNT(ST_ID) > 100


--Q3

SELECT S1.ST-ID, S2.ST-ID
FROM STUDENTS S1, STUDENTS S2
WHERE S1.GPA = S2.GPA
AND S1.Major <> S2.Major
AND S1.Address = S2.Address


--Q4

SELECT ST-ID, Name
FROM STUDENTS
WHERE NOT EXISTS(
    SELECT C-Code FROM TEACHES WHERE P-ID = 'P7'
    INTERSECT
    SELECT Course# FROM ATTEND WHERE STID = ST-ID
)