Problems and Exercises 1 through 9 are based on the class scheduling 3NF relations...

For Registration_T table, refer to FIGURE 6-11 in the textbook. 

Output:

(b)

The command used to select the instructors who cannot teach both system analysis and system design is given below:

Query:

SELECT FacultyID, COUNT(*)AS TotalCount

FROM Qualified_T

WHERE CourseID NOT IN ('ISM 3113','ISM 3112')

GROUP BY FacultyID;

Explanation:

• SELECT – To query the database and get back the specified fields SQL uses the select statement.

o Faculty_ID is a column name.

o The function COUNT(*)retrieves the number of records in the table.

o TotalCount is a label.

• FROM – To query the database and get back the preferred information by specifying the table name.

o Qualified_T is a table name.

• WHERE – This clause states which data values or rows will be displayed.

o The condition is to check whether the CourseID is not ISM 3113 and ISM 3112.

• GROUP BY – This clause is used to group the result of a SELECT statement done on a table, where the tuple values are similar for more than one column.

For Qualified_T table, refer to FIGURE 6-11 in the textbook. 

Output:

(c)

The command used to select the courses covered in first semester of 2008 but not in second semester of 2008 is given below:

Query:

SELECT CourseID,Semester

FROM Section_T

WHERE Semester = ‘I-2008’

AND CourseID NOT IN (SELECT CourseID

FROM Section_T WHERE Semester = ‘II-2008’);

• SELECT – To query the database and get back the specified fields SQL uses the select statement.

o CourseID and Semester are column names.

o TotalCount is a label.

• FROM – To query the database and get back the preferred information by specifying the table name.

o Section_T is a table name.

• WHERE – This clause states which data values or rows will be displayed.

• The condition is to check whether the semester is equal to first semester and at the same time CourseID is not in second semester.

For Section_T table, refer to FIGURE 6-11 in the textbook. 

Output: