Write the SQL command to create the above table with constraints.

Q. Answer the questions based on the table given below:

Table: STUDENT

 

 
	 
Column Name
 
	 
Data Type
 
	 
Size
 
	 
Constraint
 
 
 
	 
Roll_No
 
	 
NUMBER
 
	 
4
 
	 
PRIMARY KEY
 
 
 
	 
Name
 
	 
VARCHAR
 
	 
20
 
	 
Not Null
 
 
 
	 
Stipend
 
	 
NUMBER
 
	 
7
 
	 
Stipend is greater than 0
 
 
 
	 
Stream
 
	 
VARCHAR
 
	 
15
 
	 
Not Null
 
 
 
	 
Grade
 
	 
VARCHAR
 
	 
1
 
	 

 
 

(a) Write the SQL command to create the above table with constraints.

(b) Insert 2 records with relevant information in the table Student.

(c) Display all the records of the table Student.

(d) Delete the student whose Roll no is 100.

(e) Change the stream of student to 'Computer' whose Roll no. is 536.

(f) Add one column email of data type VARCHAR and size 30 to the table Student.

(g) View structure of the table created by you.

(h) Drop the table Student.

Answer :-

(a) Create table Student ( Roll_No int (4) Primary key , Name Varchar(20) Not Null, Stipend int(7) check( Stipend > 0 ) , Stream varchar(15) Not null , Grade varchar(1) ) ;

(b)  Insert into Student values ( ( 1, “Path” , 523 , “Science” , “A” ) , ( 2, “Walla”, 3423 , “Arts”,”B”) ) ;

(c) Select * from student ;

(d) Delete from Student where Roll_No = 100 ;

(e) Update Student set Stream = “Computer” where Roll_No = 536 ;

(f) Alter table Student add column Email varchar(30) ;

(g) Desc Student ;

(h) Drop table Student ;