rider major major_id Int (11) major_name varchar (25) major_description varchar

Question

rider major

major_id Int (11)
major_name varchar (25)
major_description varchar (75)
graduate_only char(1)

rider student

student_id Int (11)
first_name varchar (25)
last_name varchar (25)
major_id Int (11)
club_id Int (11)

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 choose 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 the rider_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 the rider_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.

Explanation / Answer

1.INSERT INTO rider_student(
                   student_id,
                   first_name,
                   last_name,
                   major id,
                   club_id)
                   VALUES ('11547', 'venkanna', 'koothada', '11557', '400');
                  
2.INSERT INTO rider_major(
                   major_id,
                   major_name,
                   major_description,
                   graduate_only)
                   VALUES(101,'Prudhvi','Creative','B.TECH')
                   WHERE NOT EXISTS (SELECT major_id
                   FROM rider_major
                   WHERE major_id=101,major_name = prudhvi,major_description=Creative
                   AND graduate_only = B.TECH)
          
3. UPDATE rider_major
                   SET major_name = 'Pashupati', major_description= 'ManagingDirector' graduate_only='P.HD'
                   WHERE major_id = 101;      
4. SELECT    first_name,
                   last_name,
                   FROM    rider_student    t1
                   JOIN   rider_major   t2
                   ON   t1.student_id = t2.major_id
                   WHERE   t1.lastname   = 'koothada';   
          
5.SELECT    first_name,
                   last_name,
                   FROM    rider_student    t1
                   JOIN   rider_major   t2
                   ON   t1.student_id = t2.major_id
                   WHERE   t1.lastname   = 'koothada';

6.INSERT INTO rider_student(new_row)VALUES('WELCOME')

                   DELETE FROM rider_student
                   WHERE new_row='WELCOME';

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

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