Questions 11-14 refer to the following schema (all attributes are varchar): MA|O
ID: 2247431 • Letter: Q
Question
Questions 11-14 refer to the following schema (all attributes are varchar): MA|OR: (id: integer, name: varchar) STUDENT (id: integer, name: varchar, major_id: integer (FK major.id)) COURSE (id: integer, name: varchar) ENROLLMENT (sid: integer (FK student id), cid (FK course id), quarter: integer Write SQL that returns the names of all students with the major "Art" Write SQL to return the count of all students without a currently defined major Write SQL that returns the list of student names, course names, and quarters they have enrolled in. For example, one of the rows returned could be: Write SQL to return the name of each major and the count of students who have currently declared that major, sorted by the name of the major (from A to Z). For example, one of the rows returned could be: Which of the following join algorithms is typically the fastest! Outer nested loop b) Index nested loop c) Block nested loop d) Nested loop e) Left nested loopExplanation / Answer
11)
select student.name as student, major.name as major
from student, major
where major.name = 'Art'
and student.major_id = major.id
12)
select student.name
from student
where student.major_id not in (
select distinct id from major;
);
13)
select student.name as student, course.name as course,
enrollment.quarter
from student, course, enrollment
where major.name = 'Art'
and student.id = enrollment.sid
and course.id = enrollment.cid
14)
select major.name, count(student.id)
from student, major
where student.major_id = major.id
group by major.id, major.name
15)Index nested loop is the fastest algortihm. (Option B)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.