Question

Problems and Exercises 1 through 5 are based on the class schedule 3NF relations along with some sample data in Figure 7-16. For Problems and Exercises 1 through 5, draw a Venn or ER diagram and mark it to show the data you expect your query to use to produce the results.

Figure 7-16: Class scheduling relations (for Problems and Exercises 1-5

Figure 7-17 Adult literacy program (for Problems and Exercises 6-14)

1. Write SQL retrieval commands for each of the following queries:

a. Display the course lD and course name for all courses with an ISM prefix.

b. Display all courses for which Professor Berndt has been qualified.

c. Display the class roster, including student name, for ail students enrolled in section 2714 of ISM 42U.

2. Write an SQL query to answer the following question: Which instructors are qualified to teach ISM 3113?

3. Write an SQL query to answer the following question: ls any instructor qualified to teach ISM 3113 and not qualified to teach ISM 4930?

4. Write SQL queries to answer the following questions:

a. How many students were enrolled in section 2714 during semester I-2008?

b. How many students were enrolled in lSM 3113 during sernesterl-2008?

5. Write an SQL query to answer the following question: Which students were not enrolled in any courses during semester l-2008?

Problems and Exercises 6 through 14 are based on Figure 7-17. This problem set continues from Chapter 6, Problems and Exercises 10 through 15, which were based on Figure 6-12.

6. Determine the relationships among the four entities in Figure 7-17. List primary keys for each entity and any foreign keys necessary to establish the relationships and maintain referential integrity. Pay particular attention to the data contained in TUTOR REPORTS when you set up its primary key.

7. Write the SQL command to add MATH SCORE to the STUDENT table.

8. Write the SQL command to add SUBJECT to TUTOR. The only values allowed for SUBJECT will be Reading, Math, and ESL.

10. Write the SQL command to find any tutors who have not submitted a report for July.

12. List all active students in June by name. (Make up names and other data if you are actually building a prototype database.) Include the number of hours students received tutoring and how many lessons they completed.

13. Which tutors, by name, are available to tutor? Write the SQL command.

14. Which tutor needs to be reminded to turn in reports? Write the SQL command. Show how you constructed this query using a Venn or other type of diagram.

Answer 1

1.

a. Select CourseID, CourseName from Course where CourseName like 'ISM%';

//​​​​​​ In this query only single table named course has been used also we have used like operator for string matching.where clause is used to filter the results. Select clause has been used to pick the desired column

b. Select Course.CourseId ,CourseName from Course, Qualified, Faculty where Course.CourseID =Qualified.CourseID and Qualified.FacultyID = Faculty.FacultyID and FacultyName='Berndt';

// in this query we have used three tables course qualified and faculty. the course and qualified table have been joined based on the common column CourseID while qualified and faculty table have been joined based on the facultyId column.

c. Select Student.StudentId ,StudentName from Student ,Course , Registration where Section.SectionNo = 2714 and CourseID = ‘ISM 4214’ and Student.StudentID = Registration.StudentId and Registration.SectionNo = Section.SectionNo;

// also three tables have been joined then after we have also used where clause for filtering the results.in where clause we have specified condition for section No and courseId.

2.

select FacultyName from Faculty , Qualified where CourseId='ISM 3113' and Faculty.FacultyId = Qualified.FacultyId;

//only two tables have been joined .Faculty and qualified table have been joined on column facultyId