Question

1. Write CREATE TABLE statements for the following tables (foreign keys are in italic and bold). Make sure you have all needed constraints and appropriate datatypes for attributes:

Student (stID, stName, dateOfBirth, advID, majorName, GPA)

Advisor (advID, advName, specialty)

2.  Insert several records in each table.

Answer 1

CREATING TABLES :

FOR STUDENT:

CREATE TABLE Student ( stID number(5) , stName varchar2( 20 ) , dateOfBirth date , advId number( 5 ) , majorName varchar2( 30 ) , GPA varchar2(2 , 5 )); // COMMAND FOR CREATING TABLE IN SQL USING CREATE TABLE FOLLOWED BY TABLE NAME AND DATA.

FOR ADVISOR :

CREATE TABLE Advisor (advID number(5) , advName varchar2(30) , speciality varchar2(20) ); // COMMAND FOR CREATING TABLE IN SQL USING CREATE TABLE FOLLOWED BY TABLE NAME AND DATA.

INSERTING SEVERAL RECORDS IN EACH TABLE :

FOR STUDENT TABLE :

• INSERT INTO Student VALUES (101 , 'VINAY' , 23-11-1999 , 501 , 'VISHAL' , 9.1 ); //COMMAND FOR INSERTING DATA INTO TABLE STUDENT USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .
• INSERT INTO Student VALUES (102 , 'KUMAR' , 29-01-1997 , 502 , 'VIMAL' , 9.5 );  //COMMAND FOR INSERTING DATA INTO TABLE STUDENT USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .
• INSERT INTO Student VALUES (103 , 'LOKESH' , 13-10-1995 , 503 , 'VIJAY' , 8.3 ); //COMMAND FOR INSERTING DATA INTO TABLE STUDENT USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .
• INSERT INTO Student VALUES (104 , 'VISHNU' , 15-08-1993 , 501 , 'SHANMUKH' , 7.5 );  //COMMAND FOR INSERTING DATA INTO TABLE STUDENT USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .
• INSERT INTO Student VALUES (105 , 'MOULEE' , 08-04-2000 , 502 , 'CHARAN' , 8.8 );  //COMMAND FOR INSERTING DATA INTO TABLE STUDENT USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .

FOR ADVISOR TABLE :

• INSERT INTO Advisor VALUES ( 505 , 'SALAM' , 'ARTIFICAIL INTELLIGENCE' );  //COMMAND FOR INSERTING DATA INTO TABLE ADVISOR USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .
• INSERT INTO Advisor VALUES ( 504 , 'VENKATESH' , 'MACHINE LEARNING' ); //COMMAND FOR INSERTING DATA INTO TABLE ADVISOR USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .
• INSERT INTO Advisor VALUES ( 503 , 'MAHESHWAR RAO' , 'NEURAL NETWORKS' ); //COMMAND FOR INSERTING DATA INTO TABLE ADVISOR USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .
• INSERT INTO Advisor VALUES ( 502 , 'JAY KRISHNA' , 'CLOUD COMPUTING' ); //COMMAND FOR INSERTING DATA INTO TABLE ADVISOR USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .
• INSERT INTO Advisor VALUES ( 501 , 'JAY RAM' , 'BIG DATA' );   //COMMAND FOR INSERTING DATA INTO TABLE ADVISOR USING INSERT INTO TABLE NAME FOLLOWED BY VALYUES FOR DATA COLUMNS .