Advanced SQL Questions and Answers

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.

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.

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.

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.

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.

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.

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.

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.

Answer: C

Correct answer: They have an event, condition, and action.

The three parts define a trigger: the event that fires it, an optional condition restricting when it acts, and the action it performs. It is created with SQL, applies to every route into the database rather than to particular applications, and can perfectly well cause another trigger to fire.

Answer: C

Correct answer: Combines the output from multiple queries and must include the same number of columns.

UNION stacks the result sets, so the queries must agree in the number of columns and in the types of the corresponding ones - the union compatibility requirement. There is no limit of two: any number of SELECTs may be chained with UNION.