Question 24 - Teachoo Sample Paper - Computer Science Class 12 - Solutions to CBSE Sample Paper - Computer Science Class 12

Mr. Ravi has just created a table named “Student” containing columns Sname, Class and Marks. 

After creating the table, he realized that he has forgotten to add a foreign key column RollNo that references another table named “Enroll”. Help him in writing an SQL command to add a foreign key column RollNo of integer type to the table Student.

Thereafter, write the command to insert the following record in the table:

RollNo- 101

Sname- Raj

Class: XII

Marks: 85

Answer:

Answer by student

SQL command to add a foreign key column RollNo of integer type to the table Student is:

ALTER TABLE Student ADD RollNo integer CONSTRAINT fk_Student_Enroll FOREIGN KEY (RollNo) REFERENCES Enroll (RollNo);

The SQL command to insert the record in the table is:

INSERT INTO Student (RollNo, Sname, Class, Marks) VALUES (101, 'Raj', 'XII', 85);

Detailed answer by teachoo

-lock-

• To add a foreign key column to an existing table in SQL, we can use the ALTER TABLE and ADD CONSTRAINT commands. The syntax for adding a foreign key column is:

ALTER TABLE table_name ADD column_name data_type CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column);

• In this syntax, 
• table_name is the name of the table to which we want to add the foreign key column
• column_name is the name of the foreign key column
• data_type is the data type of the foreign key column
• constraint_name is the name of the foreign key constraint
• parent_table is the name of the table that is referenced by the foreign key column
• parent_column is the name of the column in the parent table that is referenced by the foreign key column. 
• In this case, we want to add a foreign key column RollNo of integer type to the table Student that references another table named Enroll . Therefore, we can write:

ALTER TABLE Student ADD RollNo integer CONSTRAINT c1 FOREIGN KEY (RollNo) REFERENCES Enroll (RollNo);

• In this command, we have named the foreign key constraint as c1, which indicates that it is a foreign key from Student to Enroll. We have also specified that the foreign key column RollNo references the RollNo column in the Enroll table. This command will add a new column RollNo to the Student table and create a foreign key relationship with the Enroll table.

• To insert a record into the Student table, we can use the INSERT INTO command. The syntax for inserting a record into a table is:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

• In this syntax, 
• table_name is the name of the table into which we want to insert the record
• column1, column2, … are the names of the columns for which we want to provide values
• value1, value2, … are the values that we want to insert into those columns. 
• In this case, we want to insert a record into the Student table with the following values:

• Therefore, we can write:

INSERT INTO Student (RollNo, Sname, Class, Marks) VALUES (101, 'Raj', 'XII', 85);

This command will insert a new record into the Student table with the given values. 

-endlock-