Question

rider_student

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

rider_major

Column Data Type Description
major_id integer the primary key
major_name varchar(50) student first name
major_description varchar(100) student last name

Use the Tables above to answer the questions.

Questions:
1. Write a SQL statement to add a student record to the rider_student table for you. This should be a student with your name. Add data to all table columns. Examine the records in the rider_major table and use a major currently in the table.

2. Write a SQL statement to add a major record for a major not currently listed in the rider_major table. Add data to all table columns. Be creative and assume you can create a major for anything of interest to you. You must a major that does not currently exist to the table.

3. Assume you want to change your major to the major you added in the previous question. Write a SQL statement to change the major.

4. Write a SQL statement to retrieve the student first_name, last_name, major_name, and major_description for all records in the rider_student table. This will require an appropriate join between therider_student and rider_major tables.

5. Write a SQL statement to retrieve the student first_name, last_name, major_name, and major_description for the rider_student record you added for yourself. This will require an appropriate join between therider_student and rider_major tables.

6. Add an additional record to the rider_student table (see question 1 above). Use any name. Then write a delete statement to delete that record. Note: you are deleting only one record and you need to write your delete statement correctly to do that.

Answer 1

1. INSERT INTO rider_student VALUES (<student id,student first name,student last name>);
2. INSERT INTO rider_major VALUES (<student course>);
3. UPDATE rider_student SET major_id = <studnt id> WHERE student_id = <student id>;
4. SELECT first_name, last_name, major_name, major_description FROM rider_student LEFT JOIN rider_major on rider_student.major_id = rider_major.major_id;
5. SELECT first_name, last_name, major_name, major_description FROM rider_student LEFT JOIN rider_major on rider_student.major_id = rider_major.major_id WHERE student_id = <your student_id>;