NPTEL Introduction to Database Systems Week 6 Assignment Answers 2025
1. Under which of the following conditions, the update operations on views are NOT allowed?
(i) if the view definition has an aggregate operator
(ii) if the view definition includes two or more tables
(iii) if the view is defined on a single table without group by, aggregates and the SELECT clause includes the primary key of the table
- Only (ii)
- Only (iii)
- Only (i) and (ii)
- All (i), (ii) and (iii)
Answer :- For Answers Click Here
2. HAVING clause can be used in a query only if GROUP BY clause is used
- True
- False
Answer :-
3. GROUP BY clause can be used in a query only if HAVING clause is used
- True
- False
Answer :-
4. It is mandatory to use an aggregate function in a query if GROUP BY clause is used
- True
- False
Answer :-
5. Aggregate functions can be used in GROUP BY clauses
- True
- False
Answer :-
6. In which of the following SQL clauses of a main query can a sub-query be made use of ?
(i) FROM (ii) WHERE (iii) HAVING
- Only (ii)
- Only (iii)
- Only (ii) and (iii)
- All (i), (ii) and (iii)
Answer :-
7. Which of the following queries would select the courses with at least 10 ‘W’ grades in some offering of the course ?
- SELECT courseId
FROM enrollment
WHERE grade = ‘W’
GROUP BY courseId
HAVING count(rollNo) >= 10 - SELECT courseId
FROM enrollment
WHERE grade = ‘W’
GROUP BY courseId, sem, year
HAVING count(rollNo) >= 10 - SELECT courseId
FROM enrollment
WHERE grade = ‘W’ AND count(rollNo) >= 10
GROUP BY courseId - SELECT courseId
FROM enrollment
WHERE grade = ‘W’ AND count(rollNo) >= 10
GROUP BY courseId, sem, year
Answer :-
8. Which of the following queries will retrieve students whose name has ‘p’ as the second letter ?
- SELECT rollNo FROM student where name = ‘_p’;
- SELECT rollNo FROM student where name LIKE ‘_p’;
- SELECT rollNo FROM student where name LIKE ‘_p%’;
- SELECT rollNo FROM student where name IN ‘_p%’;
Answer :-
9. Consider two instances of the relations R1(A, B) and R2 (C, D) with the number of rows 10 and 8, respectively. What is the least possible number of tuples in the result of the following query?
SELECT *
FROM R1 FULL OUTER JOIN R2 ON R1.A = R2.C
- 0
- 18
- 8
- 10
Answer :- For Answers Click Here
10. Consider the following queries. Which of the following queries is correct to find the courses with no prerequisites?
(i) SELECT courseId
FROM preRequisite
GROUP BY courseId
HAVING count(preCourseId) = 0
(ii) SELECT courseId
FROM preRequisite a
WHERE NOT EXISTS (
SELECT preCourseId FROM preRequisite b WHERE a.courseId = b.courseId)
- Only (i)
- Only (ii)
- Both (i) and (ii)
- Neither (i) nor (ii)
Answer :-
11. Which of the following queries would find the id and name of the senior-most hod(s)?
- SELECT p1.empId, p1.name
FROM professor p1
WHERE p1.startYear IN (
SELECT min(p.startYear)
FROM professor p, department d
WHERE p.empId = d.hod) - SELECT p.empId, p.name
FROM professor p, department d
WHERE p.empId = d.hod
WHERE p.startYear IN (
SELECT min(p1.startYear)
FROM professor p1) - SELECT p1.empId, p1.name
FROM professor p1, department d1
WERE p1.empId = d1.hod
AND p1.startYear IN (
SELECT min(p.startYear)
FROM professor p, department d
WHERE p.empId = d.hod) - None of the above
Answer :-
12. Which one of the following queries would find the teacher(s) who taught CS1100 the highest number of times?
Answer :-
13. Consider the following sets about different approaches to programmatic access of databases and the properties that may apply to these approaches.
{1: Embedded SQL approach; 2: API based approach; 3: Database language approach}
{p: Only one connection to a DB server can be active at any time q: Open Database Connectivity (ODBC); r: Cursors; s: Programmers need to learn a new language ; t: Multiple connections to DB servers can be active at any time}
Identify the correct matching between the sets:
- 1–p; 2–t; 3–q
- 1–t; 2–q; 3–s
- 1–s; 2–r; 3–p
- 1–p; 2–t; 3–s
Answer :- For Answers Click Here
