/*q1: Return those professors who have taught 'CSCI203' but never 'CSCI240'*/
.print
.print 'q1: Return those professors who have taught CSCI203 but never CSCI240'
SELECT ssn
From Taught
Where crsname = 'CSCI203'
EXCEPT
SELECT ssn
From Taught
Where crsname = 'CSCI240';

.print
.print 'q2: Return those professors who have taught both CSCI203 and CSCI204'
/*q2: Return those professors who have taught both 'CSCI203' and 'CSCI204'*/
SELECT T1.ssn 
From Taught T1, Taught T2
Where T1.crsname = 'CSCI203' AND T2.crsname='CSCI204' AND T1.ssn=T2.ssn;

.print
.print 'q3: Return those professors who have never taught CSCI204'
/*q3: Return those professors who have never taught 'CSCI204'. */
SELECT ssn 
From Professor
EXCEPT
SELECT ssn 
From Taught T
Where T.crsname = 'CSCI204';

.print
.print 'q4: Return those professors who taught CSCI203 and CSCI204 in the same semester'
/*q4: Return those professors who taught CSCI203 and CSCI204 in the same semester */
SELECT T1.ssn 
From Taught T1, Taught T2
Where T1.crsname = 'CSCI203' AND T2.crsname = 'CSCI204' AND T1.ssn = T2.ssn AND T1.semester = T2.semester;

.print
.print 'Return those professors who taught CSCI204 or CSCI315 but not both.'
/*q5: Return those professors who taught CSCI204 or CSCI315 but not both.*/
SELECT ssn
FROM Taught T
WHERE T.crsname='CSCI204' OR T.crsname='CSCI315'
Except
SELECT T1.ssn 
From Taught T1, Taught T2
Where T1.crsname = 'CSCI204' AND T2.crsname='CSCI315' AND T1.ssn=T2.ssn;


.print
.print 'Return those courses that have never been taught.'
/*q6: Return those courses that have never been taught.*/
SELECT crsname
FROM Course
EXCEPT
SELECT crsname
FROM Taught;

.print
.print 'Return those courses that have been taught at least in two semesters.'
/*q7: Return those courses that have been taught at least in two semesters.*/
SELECT distinct T1.crsname
FROM Taught T1, Taught T2
WHERE T1.crsname=T2.crsname AND T1.semester <> T2.semester;

.print
.print 'Return those courses that have been taught at least in 2 semesters.'
/*q8: Return those courses that have been taught at least in 2 semesters.*/
SELECT crsname
FROM Taught
GROUP BY crsname
HAVING COUNT(*) >= 2;

.print
.print 'Return those courses that have been taught by at least 3 different professors.'
/*q9: Return those courses that have been taught by at least 3 different professors.*/
SELECT crsname
FROM (SELECT DISTINCT crsname, ssn FROM TAUGHT)  
GROUP BY crsname
HAVING COUNT(*) >= 3;

.print
.print 'Return the names of professors who ever taught CSCI204.'
/*q10: Return the names of professors who ever taught CSCI204.*/
SELECT P.profname
FROM Professor P, Taught  T
WHERE P.ssn = T.ssn AND T.crsname = 'CSCI204';

.print
.print 'Return the names of senior professors who ever taught CSCI204.'
/*q11: Return the names of senior professors who ever taught CSCI204.*/
SELECT P.profname
FROM Professor P, Taught  T
WHERE P.status = 'senior' AND P.ssn = T.ssn AND T.crsname = 'CSCI204';

.print
.print 'Return the names of senior professors who ever taught more than one courses in one semester.'
/*q12: Return the names of senior professors who ever taught more than one courses in one semester.*/
SELECT P.profname
FROM Professor P
WHERE ssn IN(
SELECT ssn
FROM Taught
GROUP BY ssn, semester
HAVING COUNT(*) >= 2);

.print
.print 'Print those professors who never taught a course.'
/*q13: Print those professors who never taught a course.*/
SELECT * FROM Professor
WHERE ssn NOT IN
(SELECT ssn
FROM Taught
);

.print
.print 'Change all the credits to 4 for those courses that are taught in F2006 semester.'
/*q14: Change all the credits to 4 for those courses that are taught in F2006 semester.*/
UPDATE Course
SET credits = 4
WHERE crsname IN(
    SELECT crsname 
    FROM Taught 
    WHERE semester = 'F2006'
);
