Paper 2 CS · UNIT 04 · MOCK TEST

Mock 5 – SQL (Part 2)

Unit 4: Database Management Systems · 18 questions · about 21 minutes. Press End Mock any time to see your score and the correct answers.

All 11 topic-wise mocks in this unit

0 / 18 answered 00:00

Directions (Q96–100): Academic database schema: 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 in student, professor and course refers to department.deptid; advisor refers to professor.empid; hod refers to professor.empid; enrollment.rollno refers to student.rollno; courseid in enrollment and teaching refers to course.courseid; teaching.empid refers to professor.empid; precourseid and courseid in prerequisite refer to course.courseid.

Q1.

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 ?

ASelect s.rollno, s.name From student s, enrollment e Where e.rollno = s.rollno and e.courseid = ANY (select precourseid from prerequisite p where p.courseid = "324") BSelect s.rollno, s.name From student s, enrollment e Where e.rollno =s.rollno and e.courseid = ALL (select precourseid from prerequisite p where p.courseid = "324") CSelect s.rollno, s.name From student s Where NOT EXISTS (select * prerequisite p where p.courseid = " 324 ") and NOT EXISTS (select * from enrollment e where e.courseid = p.precourseid and e.rollno = s.rollno)) DSelect s.rollno, s.name From student s Where EXISTS (select * prerequisite p where p.courseid = "324") and EXISTS (select * from enrollment e where e.courseid = p.precourseid and e.rollno = s.rollno))

Q2.

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:

AS1 and S3 are true BS1 and S4 are true CS2 and S3 are true DS2 and S4 are true

Q3.

Which of the following queries would compute, for each department, the total credits of all the courses offered by the department?

Aselect deptid, name, sum(credits) as totalcredits from department, course where deptid=deptno group by deptid, name; Bselect deptid, name, count(credits) as totalcredits from department, course where deptid=deptno Cgroup by deptid, name; select deptid, name, sum(credits) as totalcredits from department, course Dselect deptid, name, sum(credits) as totalcredits from department, course group by deptid, name having deptid=deptno;

Q4.

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 ?

Aselect deptid, name, sum(credits) as totalcredits from department, course where totalcredits >40 group by deptid, name having deptid = deptno; Bselect deptid, name, sum(credits) as totalcredits from department, course where deptid = deptno and totalcredits >40 group by deptid, name; Cselect deptid, name, sum(credits) as totalcredits from department, course group by deptid, name having deptid = deptno and totalcredits >40 Dselect deptid, name, sum(credits) as totalcredits from department, course where deptid = deptno group by deptid, name having totalcredits >40

Q5.

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 enrollment 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?

AIt retrieves the roll number and names of students who have not enrolled for all course offered by department number 5 . BIt retrieves the roll number and names of students who have enrolled for all course offered by department number 5 . CIt retrieves the roll number and names of students who have enrolled for at least one course offered by department number 5 DIt retrieves the roll number and names of students who are such that there exists at least one course offered by department number 5 which is not enrolled by the student.

Q6.

A trigger is

AA statement that enables the DBMS to start BA statement executed by the user when debugging an application program CA condition the system tests for the validity of the database user DA statement that is executed automatically by the system as a side effect of a modification to the database

Q7.

Which of the following is used to create a database schema?

ADML BDDL CHTML DXML p

Directions (Q96–100): A university database has the tables EMPLOYEE (EID, NAME, SALARY, DEPTNO, HODEID …; PRIMARY KEY EID; HODEID references EMPLOYEE(EID); DEPTNO references DEPARTMENT(DID)), DEPARTMENT (DID, DNAME, HODID; PRIMARY KEY DID; UNIQUE DNAME; HODID references EMPLOYEE(EID)) and PROJECTWORK (EMPID, PROJNO; PRIMARY KEY (EMPID, PROJNO); EMPID references EMPLOYEE(EID)).

Q8.

On the basis of above given table structures, retrieve the distinct employee ID (EMPID) of all employees of university who are working on project No.20, 30 and 40.

ASELECT EMPID FROM PROJECTWORK WHERE PROJNO(=(20,30,40); BSELECT EMPID FROM PROJECTWORK WHERE PROJNO IN (20,30,40); CSELECT DISTINCT EMPID FROM PROJECTWORK WHERE PROJNO IN (20,30,40); DSELECT DISTINCT EMPID FROM PROJECTWORK WHERE PROJNO=20,30,40;

Q9.

Given below are two statements to find the sum of salaries of all employees of the English department as well as the maximum, minimum and average salary in English department. STATEMENT I ： SELECT SUM (SALARY), MAX (SALARY), MIN (SALARY), AVG (SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DEPTNO=DID AND DNAME='ENGLISH'; STATEMENT II: SELECT SUM (SALARY), MAX (SALARY), MIN (SALARY), AVG (SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DNAME='ENGLISH'; In the light of the above statements, choose the correct answer from the options given below

ABoth Statement I and Statement II are true BBoth Statement I and Statement II are false CStatement I is correct but Statement II is false DStatement I is incorrect but Statement II is true

Q10.

Which of the following query/queries

return the employee ID and name of
employees whose salary is greater than

the salary of all employees in department number 20 of university? Order results by employee ID (refer to table structures given above).

(A) SELECT EID, NAME FROM EMPLOYEE WHERE SALARY>(SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20)

ORDER BY EID;
(B) SELECT EID, NAME
FROM EMPLOYEE WHERE SALARY>
(SELECT SALARY FROM EMPLOYEE
WHERE DEPTNO =20 );
(C) SELECT EID, NAME
FROM EMPLOYEE
WHERE SALARY>ALL(SELECT SALARY
FROM EMPLOYEE WHERE DEPTNO =20)
ORDER BY EID

A(A) and (B) only B(A) and (C) only C(B) only D(C) only

Q11.

In reference to the above given table

structures, which of the following
query/queries will drop the 'SALARY'
column from 'EMPLOYEE' table?
(A) ALTER TABLE EMPLOYEE DROP
SALARY CASCADE;
(B) ALTER TABLE EMPLOYEE DROP
SALARY RESTRICT;
(C) ALTER EMPLOYEE DROP SALARY;

A(A) and (B) only B(A) and (C) only C(B) and (C) only D(A) only

Q12.

Refer table, structures given above. The university decided to give all employees in the 'SCIENCE' department a 20% rise in salary. Which of the following query/queries will compute the above results?

(A) UPDATE EMPLOYEE SET SALARY = SALARY * 1.20 WHERE DEPT NO. IN (SELECT DID FROM DEPARTMENT WHERE DNAME='SCIENCE');

(B) UPDATE TABLE EMPLOYEE SET SALARY = SALARY * 1.20 WHERE DNAME='SCIENCE';

(C) ALTER TABLE EMPLOYEE SET SALARY=SALARY * 1.20 WHERE DEPTNO. IN (SELECT DNAME FROM DEPARTMENT WHERE DNAME = 'SCIENCE')

A(A) and (B) only B(A) only C(B) and (C) only D(C) only

Q13.

Given two tables EMPLOYEE (EID, ENAME, DEPTNO) DEPARTMENT (DEPTNO, DEPTNAME) Find the most appropriate statement of the given query : Select count (*) 'total' from EMPLOYEE where DEPTNO IN (D1, D2) group by DEPTNO having count (*) > 5.

ATotal number of employees in each department D1 and D2 BTotal number of employees of departments D1 and D2 if their total is > 5. CDisplay total number of employees in both departments D1 and D2 DThe output of the query must have atleast two rows

Q14.

Which of the following statements are DML statements?

(a) Update [tablename] = Set [ columnname] VALUE

(b) Delete [tablename]

A(a) and (b) B(a) and (d) C(a), (b) and (c) D(b) and (c)

Q15.

_____command is used to remove a relation from an SQL database.

ADrop table BDelete table CRemove table DUpdate table

Q16.

Consider the tables Students(Roll-No, Name) and Performance(Roll-No, Course, Marks) shown below; Students also contains the row (18CS103, Ramesh). Primary keys are underlined. Consider the query: SELECT S.Name, SUM(P.Marks) FROM Students S, Performance P WHERE S.Roll-No = P.Roll-No GROUP BY S.Name The number of rows returned is

A0 B1 C2 D3

Q17.

Consider a relation book (title, price) which contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list ? Select title from book as B where (select count (*) from book as T where T.price > B.price) < 7

ATitles of the six most expensive books. BTitle of the sixth most expensive books. CTitles of the seven most expensive books. DTitle of the seventh most expensive books.

Q18.

Database systems that store each relation in a separate operating system file may use the operating system's authorization scheme, instead of defining a special scheme themselves. In this case, which of the following is false?

AThe administrator enjoys more control on the grant option. BIt is difficult to differentiate among the update, delete and insert authorizations. CCannot store more than one relation in a file. DOperations on the database are speeded up as the authorization procedure is carried out at the operating system level. R (a, b, c) R (x, y, z)

Next: Mock 6 – Functional Dependencies & Normalisation (Part 1) →