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

rider_major table creation:
________________________

SQL> create table rider_major(major_id number(11),major_name varchar(25),major_description varchar(75),
graduate_only char(1));

Table created.

SQL> desc rider_major
Name     Null?     Type
----------------------------------------- -------- ----------------------------
MAJOR_ID     NUMBER(11)
MAJOR_NAME     VARCHAR2(25)
MAJOR_DESCRIPTION VARCHAR2(75)
GRADUATE_ONLY     CHAR(1)

rider_student table creation:
________________________

SQL> create table rider_student(student_id number(11),first_name varchar (25),last_name varchar (25),major_id number(11),club_id number(11));

Table created.

SQL> desc rider_student
Name     Null? Type
----------------------------------------- -------- ----------------------------
STUDENT_ID NUMBER(11)
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
MAJOR_ID NUMBER(11)
CLUB_ID NUMBER(11)

Question 1):-

SQL> insert into rider_student values(505,'lakshman','rao',101,230);

1 row created.

SQL> select * from rider_student;

STUDENT_ID FIRST_NAME LAST_NAME MAJOR_ID
---------- ------------------------- ------------------------- ----------
CLUB_ID
----------
505 lakshman rao 101
230

504 venky jarjapu 101
231

Question 2):-

SQL> insert into rider_major values(104,'samba siva','commander and 15 years experience','T');

1 row created.

SQL> select * from rider_major;

MAJOR_ID MAJOR_NAME
---------- -------------------------
MAJOR_DESCRIPTION G
--------------------------------------------------------------------------- -
101 kartheek
sub ordinate and 10 years experience T

104 samba siva
commander and 15 years experience T

Question 3):-

SQL> update rider_student set major_id = '102' where student_id = 505;

1 row updated. // Here the student_id = 505 major is changed from 101 to 104.

SQL> select * from rider_student;

STUDENT_ID FIRST_NAME LAST_NAME MAJOR_ID
---------- ------------------------- ------------------------- ----------
CLUB_ID
----------
505 lakshman rao 104
230

Question 4):-

SQL> select first_name,last_name,major_name,major_description from rider_student rs,rider_major rm where rs.major_id = rm.major_id;

FIRST_NAME LAST_NAME MAJOR_NAME
------------------------- ------------------------- -------------------------
MAJOR_DESCRIPTION
---------------------------------------------------------------------------
venky jarjapu kartheek
sub ordinate and 10 years experience

lakshman rao samba siva
commander and 15 years experience

Question 5):-

SQL> select first_name,last_name,major_name,major_description from rider_student rs,rider_major rm where rs.major_id = rm.major_id and student_id = 505;

FIRST_NAME LAST_NAME MAJOR_NAME
------------------------- ------------------------- -------------------------
MAJOR_DESCRIPTION
---------------------------------------------------------------------------
lakshman rao samba siva
commander and 15 years experience

Question 6):-

Inserting additional record to rider_student table:

SQL> insert into rider_student values(503,'naveen','galla','104','235');

1 row created.

SQL> select * from rider_student;

STUDENT_ID FIRST_NAME LAST_NAME MAJOR_ID
---------- ------------------------- ------------------------- ----------
CLUB_ID
----------
505 lakshman rao 104
230

504 venky jarjapu 101
231

503 naveen galla 104
235

Deleting that record:

SQL> delete from rider_student where student_id = 503;

1 row deleted.

SQL> select * from rider_student;

STUDENT_ID FIRST_NAME LAST_NAME MAJOR_ID
---------- ------------------------- ------------------------- ----------
CLUB_ID
----------
505 lakshman rao 104
230

504 venky jarjapu 101
231

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

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