SQL Code

Question 1

How to create a table and insert data with
1. StudentID ( Primary Key )
2. First Name
3. Last Name
4. Date Of Birth
5. Phone Number

Answer 1

CREATE TABLE StudentDetails (
StudentID Int
FirstName nvarchar( 255 )
LastName nvarchar( 255 )
DOB date
PhoneNumber int
PRIMARY KEY ( StudentID )
);

INSERT INTO StudentDetails
VALUES ( 1 , “Ben” , “Cat” , “1999-11-20” , 108766765 )
( 2 , “Jack” , “Cat” , “1999-11-20” , 108766765 )

Question 2

How to add a column , modify Phone number to nvarchar, drop phone number, add Primary Key?

Answer 2

ALTER TABLE StudentDetails
ADD Address nvarchar( 255 );

ALTER TABLE StudentDetails
MODIFY PhoneNumber nvarchar( 55 );

ALTER TABLE StudentDetails
DROP COLUMN PhoneNumber;

ALTER TABLE StudentDetails
ADD PRIMARY KEY ( StudentID );

ALTER TABLE StudentDetails
ADD FOREIGN KEY ( CourseID ) REFERENCES Course ( CourseID )

Question 3

How to use Foreign Keys?
StudentGrade Table
1. StudentID
2. SubjectID
3. Grades

Answer 3

CREATE TABLE StudentGrade(
StudentID Int
SubjectID int
Grades nvarchar ( 25 )
FOREIGN KEY ( StudentID ) REFERENCES StudentDetails ( StudentID )
FOREIGN KEY ( SubjectID ) REFERENCES SubjectDetails ( SubjectID )
);