Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

Structured Query Language - C993 Exam Tips

memorize.ai (lvl 286)

(0)

23 cards

()

Section 1

Preview this deck

Optimizer Indexing

Front

1 / 23

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (23)

Section 1

(23 cards)

Optimizer Indexing

Front

The optimizer will do everything it can to use an index for a given query where the index is beneficial. It avoids an index only if its use would be detrimental, such as in the case of a table with a large number of rows with an indexed column that has low selectivity.

Back

Table privileges

Front

If you grant privileges on a table and then drop the table, the privileges are dropped with the table. If you later re-create the table, you must also grant the privileges again. However, if you restore a dropped table with the FLASHBACK TABLE ... BEFORE DROP statement, you will recover the table, its associated indices, and the table's granted privileges, and you will not need to grant the privileges again

Back

FLASHBACK TABLE

Front

You cannot roll back a FLASHBACK TABLE statement

Back

Exists

Front

EXISTS does not compare anything to the subquery. There is no "expression equals expression" format with EXISTS. Its syntax is simple: the keywords WHERE EXISTS and the subquery. Nothing more

Back

Constraints with unexpected values

Front

Take note of the constraints with unexpected values for CONSTRAINT_TYPE: R for FOREIGN KEY, and C for NOT NULL as well as for CHECK.

Back

ALTER VIEW or DROP?

Front

You cannot change a view's SELECT statement with the ALTER VIEW statement. Instead, you must drop and re-create the view.

Back

Grant a Privilege on an object

Front

Let's say you create an object, then grant a privilege on that object to a role, and then grant the role to a user. If you drop the object, then you also drop the granted object privilege to the role. However, the role still exists, and the grant of the role to the user still exists. If you subsequently re-create the object and then grant the object privilege to the role once again, then you've re-created the situation before the object was dropped. In other words, you do not need to re-create the role or grant the role to the user once again since neither was affected by the act of dropping the object on which the privilege had originally been granted.

Back

Privileges and Roles

Front

Remember that "privileges" may refer to either system privileges or object privileges, which are very different. Roles consist of some combination of one or more system and/or object privileges and/or other roles.

Back

ORDER BY Clause w/ Set Operators

Front

If you combine a series of two or more SELECT statements with set operators, your ORDER BY clause must be the final clause and can specify columns by name only if it uses the column names from the first SELECT statement, regardless of how many SELECT statements might be connected with set operators.

Back

Multitable INSERT requirement

Front

Multitable INSERT statements require a subquery.

Back

Query V$

Front

Remember, only simple queries are recommended when querying the V$ (v-dollar) views directly

Back

Are sub queries exclusive?

Front

No the different types of subqueries aren't mutually exclusive. A single type of subquery may fall into multiple categories of subqueries described in this lesson.

Back

USING clause

Front

The USING clause can base a MERGE on a subquery as well as a table or view.

Back

Constraint...constraint

Front

If a SQL statement attempts to add, modify, or delete a row to/from a table and in so doing violates a constraint, the entire SQL statement will fail with an execution error.

Back

Direct and Indirect privilege

Front

A privilege may be granted directly as a privilege or indirectly as part of a role. If you intend to drop a privilege from a user, use the data dictionary to determine whether that same privilege is granted to a role that is also granted to the same user. If so, then the privilege you dropped directly is still granted to the user indirectly through the role.

Back

NEXTVAL Fail

Front

Any SQL statement that is executed with a call to a sequence's NEXTVAL pseudocolumn will advance the sequence, even if the SQL statement fails. Not even a ROLLBACK will reset a sequence; even the value of the sequence generator's CURRVAL will remain the same after a ROLLBACK.

Back

Where can you use a correlated sub query?

Front

Correlated subqueries are not limited to the SELECT statement. They can also be used in UPDATE or DELETE statements.

Back

Data Dictionary Character Case

Front

The data dictionary uses uppercase letters to record the names of all tables, columns, views, and other objects you create, unless you enclose the name in double quotes when you create the object.

Back

SELECT Statements and set operators

Front

You can combine complex SELECT statements with the set operators. Examples include SELECT statements that involve multitable joins, subqueries, aggregate functions, and/or GROUP BY clauses

Back

Data dictionary views

Front

Be sure you have at least a basic working knowledge of each of the data dictionary views that track the basic objects in the database—tables, views, sequences, synonyms, sequences, constraints—and the difference for each with regard to the USER_, DBA_, and ALL_ prefixes.

Back

WITH clause

Front

The series of one or more subquery blocks defined before the SELECT statement is referred to as the subquery factoring clause. WITH can define one subquery factoring clause; it must be defined before the SELECT statement.

Back

Multitable Insert subquery

Front

Remember, a table alias defined in a subquery of a multitable INSERT is not recognized throughout the rest of the INSERT statement. Also, if a multitable INSERT statement fails for any reason, the entire statement is rolled back, and no rows are inserted in any of the tables.

Back

ALL PRIVILEGES

Front

Note that if you want to grant all privileges, you use the keywords ALL PRIVILEGES. But if you want to grant certain privileges to all users, you do not use the keyword ALL. Instead, you grant to PUBLIC.

Back