Q. Create a database called STUDENT_PROJECT having the following tables. Choose appropriate data type and apply necessary constraints.


Table: STUDENT


 
RollNo Name Stream Section RegistrationID

* The values in Stream column can be either Science, Commerce, or Humanities.
* The values in Section column can be either I or II.



Table: PROJECT_ASSIGNED

 
RegistrationID ProjectID AssignDate


Table: PROJECT

 
ProjectID ProjectName SubmissionDate TeamSize GuideTeacher

(a) Populate these tables with appropriate data.

(b) Write SQL queries for the following.

(c) Find the names of students in Science Stream.

(d) What will be the primary keys of the three tables?

(e) What are the foreign keys of the three relations?

(f) Finds  names  of  all  the  students  studying  in  class  ‘Commerce stream’ and are guided by same teacher, even if they are assigned different projects.


Answer :-

Create Database STUDENT_PROJECT ;

Create table STUDENT ( RollNo int primary key ,Name char(50),Stream char(10) , Section char(1) , RegistrationID char (10) FOREIGN KEY ( RegistrationID ) REFERENCES PROJECT_ASSIGNED ( RegistrationID ) ) ;

Create table PROJECT_ASSIGNED ( RegistrationID char(10) Primary key , ProjectID char(10) , AssignDate date FOREIGN KEY ( ProjectID ) REFERENCES Project ( ProjectID ) ) ;

Create table Project ( ProjectID char(10 ) Primary key , ProjectName char(50) , SubmissionDate date , TeamSize int , GuideTeacher char(50) );


(a) Insert the data of your choice.

(c) Select Name from STUDENT where stream = “Science” .

(d) RollNo in student table.

RegistrationID in PROJECT_ASSIGNED table .
ProjectID in project table.

(e) RegistrationID in student table.
ProjectID in PROJECT_ASSIGNED table.

(f) Select name from student ,  PROJECT_ASSIGNED ,  project group by GuideTeacher
having student.RegistrationID = PROJECT_ASSIGNED.RegistrationID and PROJECT_ASSIGNED. ProjectID = Project.ProjectID ;

4 Comments

You can help us by Clicking on ads. ^_^
Please do not send spam comment : )

  1. SQL queries doesn't means displaying 'ok' 😑 it means to write the command to make the table

    ReplyDelete
  2. Numbering of answer is not proper

    ReplyDelete

Post a Comment

You can help us by Clicking on ads. ^_^
Please do not send spam comment : )

Previous Post Next Post