Advanced SQL Questions and Answers

Q: On which of the following, the events in a trigger can be fired ?(i) Select(ii) Update(iii) Delete(iv) Insert

Trigger events are (ii), (iii) and (iv), so the answer is (ii), (iii) and (iv).A DML trigger is fired by a statement that changes data in its table: an INSERT, an UPDATE or a DELETE. It can run before or after the change, once per statement or once per affected row, for example to keep an audit log or enforce a rule the constraints cannot express.A SELECT reads data without changing it, so in standard SQL and in the major database systems it does not fire a trigger. Options that include (i) are therefore wrong, and the option naming only update and delete leaves out insert.

Q: Which SQL-based mechanism prevents a privilege from being granted indirectly through a role ?

The answer is secure application roles.Normally, granting a role to a user makes the role's privileges available to that user indirectly. A secure application role (Oracle's CREATE ROLE ... IDENTIFIED USING package) can be enabled only by its designated PL/SQL package, which first checks conditions such as the application, IP address or time. Holding the role is not enough, so its privileges cannot be picked up indirectly outside the approved path.WITH ADMIN OPTION does the reverse: it lets the grantee pass the role on to others. A CASCADE DELETE constraint is a referential action on foreign keys and has nothing to do with privileges, and 'mandatory constraints' is not an SQL privilege mechanism.

Q: In SQL authorization, which privilege allows creation of objects in another user's schema ?

The privilege is CREATE ANY TABLE.In Oracle-style SQL authorization, CREATE TABLE lets a user create tables in his own schema, while the system privilege CREATE ANY TABLE lets him create tables in any schema, including another user's. The ANY privileges are powerful and are normally given only to administrators.ALTER SYSTEM changes instance-level settings and does not create objects. RESOURCE and DBA are predefined roles rather than single privileges; RESOURCE allows object creation only in one's own schema, and DBA is a bundle of many privileges, of which CREATE ANY TABLE is the one that does this job.

Q: Fine-grained access control (FGAC) in SQL is implemented using

FGAC is implemented using virtual private databases (VPD) and security policies.Fine-grained access control restricts access row by row, or column by column, depending on who is asking. In Oracle's Virtual Private Database, a security policy function is attached to a table; whenever a user queries it, the database silently adds a predicate to the WHERE clause, such as limiting a sales representative to his own customers, so each user sees only the rows he is entitled to.GRANT and REVOKE work at the level of whole objects (a table or view), which is coarse-grained. CHECK constraints validate data values and do not control who can read them. Table partitioning is a storage and performance technique, not an access-control mechanism.

Q: SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below :select * from R where a in (select S.a from S)

The equivalent query is select R.* from R, (select distinct a from S) as S1 where R.a=S1.a.The nested query returns each row of R once if its a value occurs anywhere in S, keeping any duplicate rows that R itself has. A plain join of R with S repeats an R row once for every matching S row, so option A can give extra copies.Taking the distinct values of S.a first means each R row matches at most one row of S1, so the join returns exactly the rows the nested query does. Option B wrongly removes duplicates that are genuinely in R, and option D is not valid SQL.

Q: Trigger is

A trigger is a statement that is executed automatically by the system as a side effect of a modification of the database.A trigger is defined by an event (such as an insert, update or delete on a table), an optional condition, and an action. When the event occurs and the condition holds, the DBMS runs the action without any explicit call.It does not start the DBMS, it is not something a user runs while debugging, and it is not a check of a user's validity.

Q: In SQL, what does the WITH CHECK OPTION clause do in a view ?

WITH CHECK OPTION ensures inserted or updated rows satisfy the view condition.When a view is created with a WHERE clause and WITH CHECK OPTION, any INSERT or UPDATE made through the view is rejected if the resulting row would not satisfy that WHERE clause, so a row can never be changed in a way that makes it vanish from the view.It does not block all modification: changes that keep rows within the view's condition are allowed. It does not create indexes, and restricting what is displayed is what the view's own WHERE clause does, with or without the option.

Q: What type of join is needed when you wish to include rows that do not have matching values?

Correct answer: Outer joinAn outer join keeps the rows that have no match on the other side and fills the missing columns with nulls. Equi-joins and natural joins discard them, so a customer with no orders would simply disappear - which is precisely the case an outer join exists to report.

Q: What type of join is needed when you wish to return rows that do have matching values?

Correct answer: All of the above.All three return the matching rows; what distinguishes them is what else they do. The equi-join returns matching rows with both join columns, the natural join drops the duplicate column, and the outer join adds the unmatched rows besides. The matching rows appear in every case.

On which of the following, the events in a trigger can be fired ?

(i) Select
(ii) Update
(iii) Delete
(iv) Insert

A.(i), (ii) and (iii)
B.(ii) and (iii)
C.(ii), (iii) and (iv)
D.(i), (ii), (iii) and (iv)

Exam Year(s)

MH SET - 2021, SET Exam - 2021,

Answer: C

Trigger events are (ii), (iii) and (iv), so the answer is (ii), (iii) and (iv).

A DML trigger is fired by a statement that changes data in its table: an INSERT, an UPDATE or a DELETE. It can run before or after the change, once per statement or once per affected row, for example to keep an audit log or enforce a rule the constraints cannot express.

A SELECT reads data without changing it, so in standard SQL and in the major database systems it does not fire a trigger. Options that include (i) are therefore wrong, and the option naming only update and delete leaves out insert.

Discuss in Forum

Which SQL-based mechanism prevents a privilege from being granted indirectly through a role ?

A.Mandatory constraints
B.CASCADE DELETE constraint
C.WITH ADMIN OPTION
D.Secure application roles

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: D

The answer is secure application roles.

Normally, granting a role to a user makes the role's privileges available to that user indirectly. A secure application role (Oracle's CREATE ROLE ... IDENTIFIED USING package) can be enabled only by its designated PL/SQL package, which first checks conditions such as the application, IP address or time. Holding the role is not enough, so its privileges cannot be picked up indirectly outside the approved path.

WITH ADMIN OPTION does the reverse: it lets the grantee pass the role on to others. A CASCADE DELETE constraint is a referential action on foreign keys and has nothing to do with privileges, and 'mandatory constraints' is not an SQL privilege mechanism.

Discuss in Forum

In SQL authorization, which privilege allows creation of objects in another user's schema ?

A.CREATE ANY TABLE
B.ALTER SYSTEM
C.RESOURCE
D.DBA

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: A

The privilege is CREATE ANY TABLE.

In Oracle-style SQL authorization, CREATE TABLE lets a user create tables in his own schema, while the system privilege CREATE ANY TABLE lets him create tables in any schema, including another user's. The ANY privileges are powerful and are normally given only to administrators.

ALTER SYSTEM changes instance-level settings and does not create objects. RESOURCE and DBA are predefined roles rather than single privileges; RESOURCE allows object creation only in one's own schema, and DBA is a bundle of many privileges, of which CREATE ANY TABLE is the one that does this job.

Discuss in Forum

Fine-grained access control (FGAC) in SQL is implemented using

A.GRANT and REVOKE commands only
B.CHECK constraints
C.Virtual private databases (VPD) and security policies
D.Role-based table partitioning

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: C

FGAC is implemented using virtual private databases (VPD) and security policies.

Fine-grained access control restricts access row by row, or column by column, depending on who is asking. In Oracle's Virtual Private Database, a security policy function is attached to a table; whenever a user queries it, the database silently adds a predicate to the WHERE clause, such as limiting a sales representative to his own customers, so each user sees only the rows he is entitled to.

GRANT and REVOKE work at the level of whole objects (a table or view), which is coarse-grained. CHECK constraints validate data values and do not control who can read them. Table partitioning is a storage and performance technique, not an access-control mechanism.

Discuss in Forum

SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below :

select * from R where a in (select S.a from S)

A.select R.* from R, S where R.a=S.a
B.select distinct R.* from R,S where R.a=S.a
C.select R.* from R, (select distinct a from S) as S1 where R.a=S1.a
D.select R.* from R,S where R.a=S.a and is unique R.

Exam Year(s)

Tripura Public Service Commission - 2025,

Answer: C

The equivalent query is select R.* from R, (select distinct a from S) as S1 where R.a=S1.a.

The nested query returns each row of R once if its a value occurs anywhere in S, keeping any duplicate rows that R itself has. A plain join of R with S repeats an R row once for every matching S row, so option A can give extra copies.

Taking the distinct values of S.a first means each R row matches at most one row of S1, so the join returns exactly the rows the nested query does. Option B wrongly removes duplicates that are genuinely in R, and option D is not valid SQL.

Discuss in Forum

Trigger is

A.Statement that enables to start any DBMS
B.Statement that is executed by the user when debugging an application program
C.The condition that the system tests for the validity of the database user
D.Statement that is executed automatically by the system as a side effect of a modification of the database.

Exam Year(s)

Tripura Public Service Commission - 2025,

Answer: D

A trigger is a statement that is executed automatically by the system as a side effect of a modification of the database.

A trigger is defined by an event (such as an insert, update or delete on a table), an optional condition, and an action. When the event occurs and the condition holds, the DBMS runs the action without any explicit call.

It does not start the DBMS, it is not something a user runs while debugging, and it is not a check of a user's validity.

Discuss in Forum

In SQL, what does the WITH CHECK OPTION clause do in a view ?

A.Prevents any data modification through the view completely
B.Ensures inserted or updated rows satisfy the view condition
C.Restricts the view to only display existing filtered records
D.Automatically creates indexes to optimize view performance

Exam Year(s)

Tripura Public Service Commission - 2026,

Answer: B

WITH CHECK OPTION ensures inserted or updated rows satisfy the view condition.

When a view is created with a WHERE clause and WITH CHECK OPTION, any INSERT or UPDATE made through the view is rejected if the resulting row would not satisfy that WHERE clause, so a row can never be changed in a way that makes it vanish from the view.

It does not block all modification: changes that keep rows within the view's condition are allowed. It does not create indexes, and restricting what is displayed is what the view's own WHERE clause does, with or without the option.

Discuss in Forum

The employee information of an Organization is stored in the relation :
Employee (name, sex, salary, deptname)

Consider the following SQL query

Select deptname from Employee\nWhere sex = 'M'\ngroup by deptname\nhaving avg (salary) > {select avg (salary) from Employee}

Output of the given query corresponds to

A.Average salary of employee more than average salary of the organization.
B.Average salary less than average salary of the organization.
C.Average salary of employee equal to average salary of the organization.
D.Average salary of male employees in a department is more than average salary of the organization.

Exam Year(s)

UGC NET - 2013,

Answer: D

The query returns the names of departments whose average salary of male employees is more than the average salary of the organisation, which is option (D). The condition sex = 'M' keeps only the male rows, the group by gathers them department by department, and the having clause keeps the departments where the average of these salaries exceeds the average salary taken over the whole table in the subquery.

Option (A) ignores the restriction to males, (B) reverses the comparison, and (C) says equal, whereas the having clause tests for greater than.

Discuss in Forum

What type of join is needed when you wish to include rows that do not have matching values?

A.Equi-join
B.Natural join
C.Outer join
D.All of the above.

Answer: C

Correct answer: Outer join

An outer join keeps the rows that have no match on the other side and fills the missing columns with nulls. Equi-joins and natural joins discard them, so a customer with no orders would simply disappear - which is precisely the case an outer join exists to report.

Discuss in Forum

10.

What type of join is needed when you wish to return rows that do have matching values?

A.Equi-join
B.Natural join
C.Outer join
D.All of the above.

Answer: D

Correct answer: All of the above.

All three return the matching rows; what distinguishes them is what else they do. The equi-join returns matching rows with both join columns, the natural join drops the duplicate column, and the outer join adds the unmatched rows besides. The matching rows appear in every case.

Discuss in Forum

Loading…

Topics

Other Subjects

Tags: Advanced SQL Questions and Answers || Advanced SQL MCQ Questions and Answers || Advanced SQL GK Questions and Answers || Advanced SQL GK MCQ Questions || Advanced SQL Multiple Choice Questions and Answers || Advanced SQL GK || GK on Advanced SQL || Database Questions and Answers || Database MCQ Questions and Answers || Database GK Questions and Answers || GK on Database

Advanced SQL Questions and Answers

Topics

Other Subjects

About Us

Stay informed about all important Dates & Notifications