Read the following and answer the questions that follow. Use the following schema of the academic institution relational database: student (rollno, name, degree, year, sex, deptno, advisor) department (deptid, name, hod, phone) professor (empid, name, sex, startyear, deptno, phone) course (courseid, cname, credits, deptno) enrollment (rollno, courseid, sem, year, grade) teaching (empid, courseid, sem, year, classroom) prerequisite (precourseid, courseid) deptno is foreign key in the student, professor and course relations referring to deptid of department relation; advisor is a foreign key in the student relation referring to empid of professor relation; hod is a foreign key in the department relation referring to empid of professor relation; rollno is a foreign key in the enrollment relation referring to rollno of student relation; courseid is a foreign key in the enrollment, teaching relations referring to courseid of course relation; empid is a foreign key of the teaching relation referring to empid of professor relation; precourseid and courseid are foreign keys in the prerequisite relation referring to courseid of the course relation;
Which of the following queries would retrieve the roll number and names of students who have enrolled for all the prerequisite courses of the course number 324?
Select s.rollno, s.name From student s Where NOT EXISTS (select * from prerequisite p where p.courseid = “324” and NOT EXISTS (select * from enrollment e where e.courseid = p.precourseid and e.rollno = s.rollno))
“Students enrolled for all prerequisites of course 324” is a relational-division query. SQL has no division operator, so it is written with a double negation: select a student for whom there is no prerequisite of 324 that the student has not enrolled in.
Step 1 — The outer NOT EXISTS ranges over the prerequisite rows p with p.courseid = 324, i.e. the set of required courses.
Step 2 — The inner NOT EXISTS looks for an enrollment row e with e.courseid = p.precourseid and e.rollno = s.rollno. It is true exactly when student s has missed prerequisite p.
Step 3 — The outer condition is therefore “no prerequisite of 324 is missed by s”, which is the same as “s has enrolled in every prerequisite of 324”. This is the NOT EXISTS … NOT EXISTS query, with the enrollment subquery nested inside the prerequisite subquery and correlated on p.
The = ANY version returns a student who has enrolled in at least one prerequisite, not all of them. The = ALL version compares the courseid of a single enrollment row with every prerequisite at once, which can hold only when course 324 has at most one prerequisite, so it does not express “all”. The EXISTS … EXISTS version again only requires one matching prerequisite enrollment.
Select s.rollno, s.name From student s Where NOT EXISTS (select * from prerequisite p where p.courseid = “324” and NOT EXISTS (select * from enrollment e where e.courseid = p.precourseid and e.rollno = s.rollno)) option is correct.
Similar Questions
Consider the following three SQL queries (Assume the data in the people table) :
(a) Select Name from people where Age>21;
(b) Select Name from people where Height>180;
(c) Select Name from people where (Age>21) or (Height>180);
If the SQL queries (a) and (b) above, return 10 rows and 7 rows in the result set respectively, then what is one possible number of rows returned by the SQL query (c) ?
Suppose ORACLE relation R(A, B) currently has tuples {(1, 2), (1, 3), (3, 4)} and relation S(B, C) currently has {(2, 5), (4, 6), (7, 8)}. Consider the following two SQL queries SQ1 and SQ2 :
SQ1 : Select *
From R Full Join S
On R.B = S.B;
SQ2 : Select *
From R Inner Join S
On R.B = S.B;
The numbers of tuples in the result of the SQL query SQ1 and the SQL query SQ2 are given by :
Consider a “CUSTOMERS” database table having a column “CITY” filled with all the names of Indian cities (in capital letters). The SQL statement that finds all cities that have “GAR” somewhere in its name, is :
The STUDENT information in a university stored in the relation STUDENT (Name, SEX, Marks, DEPT_Name)
Consider the following SQL Query SELECT DEPT_Name from STUDENT where SEX = ‘M’ group by DEPT_Name having avg (Marks)>SELECT avg (Marks) from STUDENT. It Returns the Name of the Department for which :
An Assertion is a predicate expressing a condition we wish database to always satisfy. The correct syntax for Assertion is :
Match the following :
List - I List - II a. DDL i. LOCK TABLE b. DML ii. COMMIT c. TCL iii. Natural Difference d. BINARY Operation iv. REVOKE Codes :
Given query:
select s.rollno, s.name
from student s
where EXISTS (select c.courseid from course c Where c.deptno=5 and
EXISTS (select e.* from enrollemnt e
where e.courseid = c.courseid and
e.rollno = s.rollno));
Which one of the following statements is correct with respect to the given query?
Which of the following queries would retrieve the dept id and name of all the departments that are such that the total of the credits of all the offered courses by the department is strictly greater than 40?
Which of the following queries would compute, for each department, the total credits of all the courses offered by the department?
Given query: select s.rollno from students where s.sex = ALL (select f.sex from professor f where f.empid = s.advisor)
Consider the following statements about the given query that retrieves the set of all students whose gender is same as the gender of their advisor:
S1. It has a correlated subquery
S2. It has an uncorrelated subquery
S3. If we replace ALL by ANY in the above query, its result will be different
S4. If we replace ALL by ANY in the above query, its result will be same.
Identify all the statement that are TRUE:
Important Questions from SQL
Match the following -
List I List II (a) DDL (i) LOCK TABLE (b) DML (ii) COMMIT (c) TCL (iii) Natural Difference (d) Binary operation (iv) REVOKE Which of the following is NOT a Structured Query Language Data type?
Consider a table Employee(EmpID, TeamID), where the column EmpID (ID of an employee) is the primary key. The column TeamID denotes the team ID of the team of which the employee is a member. TeamID is a NOT NULL column.
We want to display the size of the team (denoted as TeamSize) in which each employee is a member by using SQL. As an example, the desired output for the given Employee table is also shown in tabular form.Which of the following is/are correct?
Employee
EmpID TeamID 1 8 2 8 3 8 4 7 5 7 6 9
Output
EmpID TeamSize 1 3 2 3 3 3 4 2 5 2 6 1 Let Account be a relation as shown.
Account
AccNo Balance A1 5000 A2 5000 A3 10000 A4 15000 A5 18000 Consider the given SQL query.
SELECT AccNo FROM Account AS A
WHERE (SELECT COUNT(*) FROM Account AS B
WHERE A.Balance < B.Balance) >= (SELECT COUNT(*)
FROM Account AS C WHERE A.Balance > C.Balance)
The number of rows returned by the SQL query is __________ . (Answer in integer)
Consider the following two tables named Raider and Team in a relational database maintained by a Kabaddi league. The attribute ID in table Team references the primary key of the Raider table, ID.
Raider ID Name Raids RaidPoints 1 Arjun 200 250 2 Ankush 190 219 3 Sunil 150 200 4 Reza 150 190 5 Pratham 175 220 6 Gopal 193 215 Team City ID BidPoints Jaipur 2 200 Patna 3 195 Hyderabad 5 175 Jaipur 1 250 Patna 4 200 Jaipur 6 200 The SQL query described below is executed on this database:
SELECT *
FROM Raider, Team
WHERE Raider.ID=Team.ID AND City="Jaipur" AND
RaidPoints > 200;
The number of rows returned by this query is ________.
