Class 11 Informatics Practices Chapter 8 Introduction to Structured Query Language SQL NCERT Exercise Solution

NCERT Exercise Solution Ch 8 – SQL

Introduction to Structured Query Language NCERT Exercise Solution

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

Table: STUDENT

• 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

Table: PROJECT

Answer: Creating DATABASE and TABLES

CRETAE DATABASE Student_Project;

USE Student_Project;


CREATE TABLE student 
(
  RollNo INTEGER PRIMARY KEY,
  Name VARHCAR(30),
  Stream VARCHAR(20) CHECK (Stream IN ('Science', 'Commerce', 'Humanities'),
  Section CHAR(2) CHECK (Section IN ('I', 'II')),
  RegistrationID INTEGER NOT NULL 
);

CREATE TABLE project
(
  ProjectID INTEGER NOT NULL PRIMARY KEY,
  ProjectName VARCHAR(80) NOT NULL,
  SubmissionDate DATE,
  TeamSize INTEGER,
  GuideTeacher VARCHAR(30)
);

CREATE TABLE Project_Assigned
(
  RegistrationID INTEGER REFERENCES Student(Registration),
  ProjectID INTEGER REFERENCES Project(ProjectID),
  AssignDate DATE,
  PRIMARY KEY(RegistrationID, ProjectID)
);   

OR

CREATE TABLE Project_Assigned
(
  RegistrationID INTEGER ,
  ProjectID INTEGER,
  AssignDate DATE,
  PRIMARY KEY(RegistrationID, ProjectID),
  FOREIGN KEY(RegistrationID) REFERENCES Student(Registration),
  FOREIGN KEY(ProjectID)REFERENCES Project(ProjectID)
); 

a) Populate these tables with appropriate data.

Answer: Inserting Records into table Student

INSERT INTO Student
VALUES(1, 'AMRIT', 'SCIENCE', 'I', 12546);
..

Inserting Records into table Project

INSERT INTO Project
VALUES(231, 'Employee Management', '2021-12-31', 1, 'Mr. Anjeev Singh');
... 
...

Inserting Records into Project_Assigned

INSERT INTO Project_Assigned
VALUES (12546, 231, '2021-12-01');
...
...

b) Write SQL queries for the following.

i) Find the names of students in Science Stream.

Answer :-

SELECT * FROM STUDENT
WHERE Stream = 'Science';

ii) What will be the primary keys of the three tables?

Answer :- Table Student : RollNo ,

Table Project : ProjectID,

Table Project_Assigned : RegistrationID + ProjectID

iii) What are the foreign keys of the three relations?

Answer :- Table Student : No Foreign Key,

Table Project : No Foreign Key,

Table Project_Assigned : RegistrationID references with Student table, ProjectID references with Project table.

iv) 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 :-

SELECT NAME FROM Student WHERE Stream  = 'Commerce' AND RegistrationID IN(Select RegistrationID FROM Project_Asssined Where Proejct ID IN (Select PojectID form Project Where GuideTeacher = 'Anjeev Singh')

NCERT Book Exercise Solution – Class 11 Informatics Practices

• Class 11 – Informatics Practices – NCERT Exercise Solution
  by mycstutorial
• Class 11 Informatics Practices – Ch 1. Computer System NCERT Solution
  by mycstutorial
• Class 11 Informatics Practices – Ch. 2 Emerging Trends NCERT Solutions
  by mycstutorial
• Class 11 Informatics Practices Chapter 3 Brief Overview of Python NCERT Solution
  by mycstutorial
• Class 11 Informatics Practices Chapter 4 Working with Lists and Dictionaries NCERT Solution
  by mycstutorial
• Class 11 Informatics Practices Chapter 5 Understanding Data NCERT Solution
  by mycstutorial
• Class 11 Informatics Practices Chapter 6 Introduction of NumPy NCERT Exercise Solution
  by mycstutorial
• Class 11 Informatics Practices Chapter 7 Database Concepts NCERT Exercise Solution
  by mycstutorial
• Class 11 Informatics Practices Chapter 8 Introduction to Structured Query Language SQL NCERT Exercise Solution
  by mycstutorial