Question

Problems and Exercises 1 through 9 are based on the dass scheduling 3NF relations along with some sample data shown in Figure 6-11. Not shown in this figure are data for an ASSIGNMENT relation, which represents a many-to-many relationship between faculty and sections.

4. Write SQL data definition commands for each of the following queries:

a. How would you add an attribute, Class, to the Student table?

b. How would you remove the Registration table?

c. How would you change the FacultyName field from 25 characters to 40 characters?

5. Write SQL commands for the following:

a. Create two different forms of the INSERT command to add a studen t with a student ID of 65798 and last name Lopez to the Student table.

b. Now write a command that will remove Lopez from the Student table.

c. Create an SQL command that will modify the name of course lSM 4212 from Database to Lntroduction to Relational Databases.

6. Write SQL queries to answer the following questions:

a. Which students have an lD number that is less than 50000?

b. What is the name of the faculty member whose lD is 4756?

c. What is the smallest section number used in the first semester of 2008?

7. Write SQL queries to answer the following questions:

a. How many students are enrolled in Section 2714 in the first semester of 2008?

b. Which faculty members have qualified to teach a course since 1993? List the faculty lD, course, and date of qualification.

8. Write SQL queries to answer the following questions:

a. Which students are enrolled in Database and Networking? (Hint: Use SectionNo for each class so you can determine the answer from the Registration table by itself.)

b. Which instructors cannot teach both Syst Analysis and Syst Design?

c. Which courses were taught in the first semester of 2008 but not in the second semester of 2008?

Answer 1

8.

a. Which students are enrolled in Database and Networking? (Hint: Use SectionNo for each class so you can determine the answer from the REGISTRATION table by itself.)

                   SELECT Course.ID, Section.[No], Registration.StudentID

                   FROM Course, [Section], Registration

                    WHERE (((Course.ID)="DB212")) AND (((Course.ID)="NT210"));

Here we are taking DB212 as Database and NT210 as Networking/

By using this query we can get which students are enrolled in Database and Networking.

b. Which instructors cannot teach both Syst Analysis and Syst Design?

SELECT FacultyID, COUNT(*)AS TotalCount

FROM Qualified_T

WHERE CourseID NOT IN('SA212','SD210')

GROUP BY FacultyID;

Here we are taking SA212 as System Analysis and SD210 as System Design

c. Which courses were taught in the first semester of 2018 but not in the second semester of 2018?

SELECT CourseID FROM section WHERE semester=’1’ AND year=’2008’ AND CourseID NOT IN (SELECT CourseID FROM section WHERE semester=’2’ AND year=’2008’)