NEED query on paper DATABASE SCHEMA Professor(ssn, profname, status, salary) Cou

Question

NEED query on paper

DATABASE SCHEMA
Professor(ssn, profname, status, salary)
Course(crscode, crsname, credits)
Taught(crscode, semester, ssn)
Assumptions:
(1) Each course has only one instructor in each semester;
(2) all professors have different salaries;
(3) all professors have different names;
(4) all courses have different names;
(5) status can take values from “Full”, “Associate”, and “Assistant”.
Answer the following Queries, i.e. write the SQL on paper. No need to implement in MySQL.
1. Return those professors’ ssn who have taught ‘csc6710’ but never ‘csc7710’.
2. Return those professors’ ssn who have never taught ‘csc7710’.
3. Return those courses’ crscode that have been taught at least in 10 semesters.
4. Return those courses’ crscode that have been taught by at least 5 different professors.
5. In chronological order, list the number of courses that the professor with ssn =
123456789 taught in each semester.
6. For each professor, return the total number of credits that she/he taught for all
semesters. (Hint: return ssn for the professor

Explanation / Answer

Below is your queries. Let me know if you have any issues in this.

1. Return those professors’ ssn who have taught ‘csc6710’ but never ‘csc7710’.

(SELECT ssn From Taught Where crscode = ‘CSC6710’) EXCEPT (SELECT ssn From Taught Where crscode = ‘CSC7710’))

2. Return those professors’ ssn who have never taught ‘csc7710’.

(SELECT ssn From Professor) EXCEPT (SELECT ssn From Taught T Where T.crscode = ‘CSC7710’)

3. Return those courses’ crscode that have been taught at least in 10 semesters.

SELECT crscode FROM Taught GROUP BY crscode HAVING COUNT(*) >= 10

4. Return those courses’ crscode that have been taught by at least 5 different professors.

SELECT crscode FROM (SELECT DISTINCT crscode, ssn FROM TAUGHT) GROUP BY crscode HAVING COUNT(*) >= 5

5. In chronological order, list the number of courses that the professor with ssn =
123456789 taught in each semester.

SELECT semester, COUNT(*) FROM Taught WHERE ssn = ‘123456789’ GROUP BY semester ORDER BY semester ASC

6. For each professor, return the total number of credits that she/he taught for all
semesters.

SELECT P.ssn, COUNT(*) FROM Professor P, Taught T WHERE P.ssn = T.ssn GROUP BY P.ssn, P.profname ORDER BY P.profname ASC

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.