The database schema contains a rider_student table and a rider_major table. The

Question

The database schema contains a rider_student table and a rider_major table. The rider_student table contains some basic information about rider students. The rider_major table contains basic information about majors offered at Rider. The clubs table contains a club_id and the name/description of the corresponding club.The structure of these tables is as follows.

rider_student

rider_major

clubs

SQL Subqueries

1. Write a SQL subquery which determines which records in the rider_student table have majors which are not in the rider_major table.
2. Write a SQL subquery which determines which records in the rider_major table have a major which is not currently selected by any students.

3. Write an outer join to retrieve records from the rider_student table and the clubs table. This select statement should retrieve rider_student table records whether or not they have corresponding records in the clubs table.

4. Write a non-correlated subquery to determine which records in the rider_student table have no corresponding records in the clubs table.

Column Data Type Description student_id integer the primary key first_name varchar(25) student first name last_name varchar(25) student lats name major_id integer the ID of the student's major; a foreign key for the major_id in the rider_major table

Explanation / Answer

Here is the answer for Q1 and Q2.

Q1.

SELECT * FROM rider_student
WHERE major_id NOT IN (SELECT major_id FROM rider_major);

Here we first list all major_id from the rider_major table and then use outer select
to retrieve the records from rider_student where student's major_id is not in the list.

Q2.
SELECT * FROM rider_major
WHERE major_id NOT IN (SELECT DISTINCT major_id FROM rider_student);

Here we list only unique major_id from rider_student and then look for records in
rider_major where the major_id is not in the list.

For Q3 and Q4, there is not field connecting rider_student to clubs ! Is there something missing in rider_student like club_id field ? Please check the structure of both tables. In order to join, we need a common field.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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