NPTEL Introduction to Database Systems Week 5 Assignment Answers 2025

By /

NPTEL Introduction to Database Systems Week 5 Assignment Answers 2025

1. Which of the following SQL sub-language constructs are used to insert rows into tables?

  • DDL
  • DML
  • Transaction control language
  • None of the above
Answer :- For Answers Click Here

2. Which of the following relational calculus operators does not have an equivalent keyword in SQL?

  • exists (∃)
  • for all (∀)
  • and (∧)
  • None of the above
Answer :-

3. Suppose that in the given schema, we want to change the teaching table and add an extra column called ‘teachingAssistant’. A teaching assistant is a student who assists the professor of a course in clarifying student-doubts, setting up quizzes and evaluating students etc. Assume that each course is allotted at most one teaching assistant. Which of the following commands is suitable to enforce the above requirement?

  • ALTER TABLE teaching ADD teachingAssistant VARCHAR(10);
  • ALTER TABLE teaching ADD teachingAssistant VARCHAR(10) NOT NULL;
  • ALTER TABLE teaching ADD teachingAssistant VARCHAR(10) REFERENCES professor(empId);
  • ALTER TABLE teaching ADD teachingAssistant VARCHAR(10) REFERENCES student(rollNo);
Answer :-

4. Which of the following are unique keys in the teaching table definition?
(i) empId, courseId, year (ii) empId, courseId, sem, year (iii) empId, courseId, sem, year, classRoom

  • Only (i)
  • Only (ii)
  • Only (ii) and (iii)
  • All (i), (ii) and (iii)
Answer :-

5. Suppose that in the given schema, the PRIMARY KEY of preRequisite table is only “courseId”. Then, which of the following statement(s) is(are) TRUE in all data instances of the database?

  • Every course in course table (specified in courseId column) has at least one prerequisite
  • Every course in course table (specified in courseId column) has exactly one prerequisite
  • Every course in preRequisite table (specified in courseId column) has exactly one prerequisite
  • All of the above
Answer :-

6. Suppose we need to find the roll numbers of students whose grades are neither ‘U’ nor ‘W’ in the course having id ‘CS123’. What is the correct SQL query?

  • SELECT rollNo FROM enrollment
    where courseId = ‘CS123’ and grade != ‘U’ or grade != ‘W’
  • SELECT rollNo FROM enrollment
    where courseId = ‘CS123’ and NOT (grade != ‘U’ and grade != ‘W’)
  • SELECT rollNo FROM enrollment
    where courseId = ‘CS123’ and grade NOT EXISTS (‘U’, ‘W’);
  • SELECT rollNo FROM enrollment
    where courseId = ‘CS123’ and grade NOT IN (‘U’, ‘W’);
Answer :- For Answers Click Here

7. Which of the following queries would find the students who enrolled in a course twice (Note that a course is usually offered once in a year but some popular courses are offered in both the semesters of the same year)?

  • SELECT e1.rollNo
    FROM enrollment e1
    WHERE EXISTS (SELECT * FROM enrollment e2
    WHERE e1.rollNo = e2. rollNo and e1.courseId = e2.courseId)
  • SELECT e1.rollNo
    FROM enrollment e1
    WHERE EXISTS (SELECT * FROM enrollment e2
    WHERE e1.rollNo = e2. rollNo and e1.courseId = e2.courseId
    and e1.year = e2.year and e1.sem = e2.sem)
  • SELECT e1.rollNo
    FROM enrollment e1
    WHERE EXISTS (SELECT * FROM enrollment e2
    WHERE e1.rollNo = e2. rollNo and e1.courseId = e2.courseId
    and e1.year != e2.year and e1.sem != e2.sem)
  • SELECT e1.rollNo
    FROM enrollment e1
    WHERE EXISTS (SELECT * FROM enrollment e2
    WHERE e1.rollNo = e2. rollNo and e1.courseId = e2.courseId
    and (e1.year != e2.year or e1.sem != e2.sem))
Answer :-

8. Consider the following query to retrieve the most senior professor (determined based on startYear) :
SELECT p1.name
FROM professor p1
WHERE p1.startYear ———- (SELECT p2.startYear
FROM professor p2)

Which of the following options is the correct filler for the blank ?

  • < ALL
  • <= ALL
  • <= ANY
  • IN
Answer :-

9. Which of the following queries would find the courses with at least two prerequisites?

  • SELECT *
    FROM course c1
    WHERE EXISTS (SELECT * FROM prerequisite p1
    WHERE p1.courseId = c1.courseId)
    AND EXISTS (SELECT * FROM prerequisite p2
    WHERE p2.courseId = c1.courseId)
  • SELECT *
    FROM course c1
    WHERE EXISTS (SELECT * FROM prerequisite p1
    WHERE p1.courseId = c1.courseId)
    AND EXISTS (SELECT * FROM prerequisite p2
    WHERE p2.courseId = c1.courseId
    AND p1.preCourseId <> p2.preCourseId)
  • SELECT *
    FROM course c1
    WHERE EXISTS (SELECT * FROM prerequisite p1, prerequisite p2
    WHERE p1.courseId = c1.courseId
    AND p2.courseId = c1. courseId)
  • SELECT *
    FROM course c1
    WHERE EXISTS (SELECT * FROM prerequisite p1, prerequisite p2
    WHERE p1.courseId = c1.courseId
    AND p2.courseId = c1. courseId
    AND p1.preCourseId <> p2.preCourseId)
Answer :-

10. Consider the following query

SELECT rollNo as identifier
FROM student
UNION
SELECT empId as identifier
FROM professor

Which of the following statements is correct regarding the above query?

  • The query executes only if rollNo of student and empId of professor have same data types
  • The query executes in all cases (irrespective of the data types of rollNo and empId)
  • The query does not execute at all
  • None of the above
Answer :- For Answers Click Here

Related Posts