C993 SQL 1Z0-071 - PT 2

System privilege

The ability to perform a particular task in the database System privileges differ from object privileges in that system privileges are what a user account must have to create database objects, among other things.

Object privilege

The ability to perform a particular task on a particular database object Then, once a database object has been created, object privileges on that database object can be granted to other users. an object privilege is the right to do something to a particular object. Object privileges exist for all DML statements—SELECT, INSERT, UPDATE, and DELETE—as well as any DDL statement that is relevant to an existing object, such as ALTER -for tables created by other users, the user should prefix the tablenames with the owners name; can be negated by creating a public synonym: CREATE PUBLIC SYNONYM WEBINARS FOR LISA.WEBINARS;

V$PARAMETER

The current settings for system parameters, such as NLS_LANGUAGE, NLS_DATE_LANGUAGE, NLS_CURRENCY, NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, SQL_TRACE, and much more

SCN

The system change number is a numeric stamp that the database automatically increments for every committed transaction that occurs in the database. SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; Each time a transaction is committed in the database, the SCN is incremented and stored with each row in each table. The SCN for a given row can be found in the pseudocolumn ORA_ROWSCN. SELECT ORA_ROWSCN, VOILA FROM HOUDINI;

V$OBJECT_USAGE:

Useful for monitoring the usage of INDEX objects

USER _VIEWS

Views owned by USER

ANY

When a system privilege includes the keyword ANY in its title, it means that the privilege will authorize a user to perform the task as though they were any user account.

CREATE, ALTER, DELETE, DROP, INSERT, SELECT, UPDATE || ANY TABLE

any table with any account

flashback

can: -Recovering complete tables you may have dropped. -Recovering data changes within one or more tables resulting from a series of DML statements. -Performing data analysis on data that's been changed over periods of time. -Comparing data that existed at one point in time with data that existed at another point in time. -Performing queries as of a prior time period. points to restore from: -Immediately prior to when a table was dropped -A specific time identified by a value of data type TIMESTAMP -A specific transaction identified by the system change number (SCN) -A predetermined event identified by a database object known as the RESTORE POINT -FLASHBACK TABLE restores everything but foreign keys syntax: FLASHBACK TABLE table1, table2, table3 TO BEFORE DROP -tables are put in recycle bin when dropped; flashback restores them SELECT * FROM USER_RECYCLEBIN; SELECT * FROM RECYCLEBIN; -a single failure will error the entire statement PURGE TABLE tableName; PURGE RECYCLEBIN; PURGE DBA_RECYCLEBIN; FLASHBACK TABLE HOUDINI TO SCN scn_expression; FLASHBACK TABLE HOUDINI TO TIMESTAMP timestamp_expression; FLASHBACK TABLE HOUDINI TO RESTORE POINT restore_point_expression; -permanent; invokes a implicit commit -You cannot use the FLASHBACK TABLE statement to restore older data to an existing table if the table has been structurally altered with the ALTER TABLE statement in such a way that it can't accept the full definition of older data. -requires rowmovement to be enabled ALTER TABLE HOUDINI ENABLE ROW MOVEMENT; **TO BEFORE DROP doesnt require rowmovement to be enabled** SCN_TO_TIMESTAMP(SCN) TIMESTAMP_TO_SCN(timestamp) FLASHBACK TABLE tableName TO RESTORE POINT restorePointName;

UNION

combines everything in both sets and eliminates duplicates -combines the columns and looks for duplicates in that; so it can contain duplicate values in individual columns

UNION ALL

combines everything in both sets and keeps duplicates

ALL_TABLES

contains information about all the tables USER has access to -OWNER: The owner of the table in question -similar columns as USER_TABLES

DBA_TABLES

contains information about all the tables USER has access to in the whole database -OWNER: The owner of the table in question -similar columns as USER_TABLES

USER_TABLES

contains information about the tables owned by the current user -TABLE_NAME -STATUS -ROW MOVEMENT -AVG_ROW_LEN

INTERSECT

only includes rows that are in both sets and excludes duplicates

MINUS

only keeps the rows that are unique to set A

CREATE, ALTER, DROP || USER

permissions

Data Dictionary

stores: The names of database objects, their owners, and when they were created The names of each table's columns, along with data types, precision, and scale Any constraints Views, indexes, and sequences -The data dictionary consists of tables and views that are owned by the user account SYS -users generally don't get direct access to the tables of the data dictionary; they get access to the views. DESC DICTIONARY;

ALTER, DROP, GRANT || ANY ROLE

with any account

CREATE, ALTER, DROP || ANY VIEW

with any account

CREATE, ALTER, DROP, SELECT || ANY SEQUENCE

with any account

CREATE, DROP || ANY DIRECTORY

with any account

CREATE, DROP || ANY SYNONYM

with any account

CREATE, DROP || ANY VIEW

with any account

EXISTS NOT EXISTS

-EXISTS returns true if a query returns rows -NOT EXISTS returns true if a query DOES NOT return any rows -also called a semi-join

multirow operators

-IN: returns true if the value matches any of the values in the group. -NOT: can be combined with multirow ops; will return no rows if the subquery returns any nulls. -SOME or ANY: can be used with singlerow ops; returns true if any matches are found. -ALL: can be used with single row ops; returns true if ALL rows match

Inner Join

-Inner joins connect rows in two or more tables if and only if there are matched rows in all the tables being joined. -a row must find a match to be included -table aliases are rquired if the common columns have the same names

Outer Join

-Outer joins connect rows in two or more tables in a way that is more inclusive—if data exists in one table that has no matching values in another, the unmatched row will still be included in the output. -for the old syntax: the side with (+) is the side that only includes matches

set operators

-The number of expressions selected in the select lists must be identical in each SELECT statement. -ORDER BY can only occur at the end -BLOB or CLOB cannot be used -the number of expressions in each set must be the same -order for datatypes must be the same in both sets -will execute from top to bottom with equal precedence, unless parentheses are used. -ORDER BY can reference columns by position or by the name of columns in the first set

Oracle Database Optimizer

-The optimizer determines how best to process any given SQL statement according to a combination of factors. -determines how to leverage indexes to speed up processing -If a column tends to include data that is less repetitive and more unique, it is said to have a higher degree of selectivity by the optimizer -The Oracle Database optimizer will consider the use of an index in any query that specifies an indexed column in the WHERE clause or ORDER BY clause, depending on how the index column is specified. -For best results, the indexed column should be specified in a comparison of equality. -A "greater than" or some other comparison may work. -A "not equals" will not invoke an index. -The LIKE comparison may invoke an index as long as the wildcard character is not in the leading position. In other words, the expression LIKE '%SMITH' will not invoke an index, but LIKE 'SMITH%' may invoke an index. -A function on a column will prevent the use of an index—unless the index is a function-based index. (Function-based indexes are not addressed in the exam.) -rule of thumb: no more than five indexes on the average table

checking privileges

-USER_SYS_PRIVS: System privileges granted to the current user -USER_TAB_PRIVS: Granted privileges on objects for which the user is the owner, grantor, or grantee -USER_ROLE_PRIVS: Roles granted to the current user -DBA_SYS_PRIVS: System privileges granted to users and roles -DBA_TAB_PRIVS: All grants on objects in the database -DBA_ROLE_PRIVS: Roles granted to users and roles -ROLE_SYS_PRIVS: System privileges granted to roles -ROLE_TAB_PRIVS: Table privileges granted to roles -SESSION_PRIVS: Session privileges that the user currently has set

indexes on the same columnset

-You can create a unique index on a column set and a nonunique index on the same column set. -you can create indexes on the same column set if they differ with regard to index type. -Multiple indexes may exist on the same column set provided that certain partitioning characteristics are different: Partitioning type (hash or range) Local versus global partitioning Partitioning versus nonpartitioning ****only one may be visible in the database at any given point in time****

Self-Join

-a foreign key is advised but not required to self join on -references the same table twice in the FROM clause -uses table aliases

VIEW

-a permanent WITH clause -can be referenced and used like a table -can utilize complex joins in the structure and can be used in a join -describe can be used on a view like a table syntax: CREATE OR REPLACE VIEW viewName AS **query** -OR REPLACE optional -complex expressions in columnlist MUST have an alias or proper name CREATE OR REPLACE VIEW VW_EMPLOYEES (ID, NAME, PHONE) AS **query** -constraints can be made, but aren't enforced without much setup -insert will only work on a view if it has a notnull column -update and delete will still work on a view that doesnt have a not null column -INSERT, DELETE, and UPDATE will error, if the constraints of the underlying columns aren't satisfied. -cannot use INSERT, DELETE, and UPDATE if: Distinct, aggregate functions, set operators, joins, subqueries.

skip scanning

-a skip scan will do an index scan once for each unique value in the first column. -a WHERE clause that references some, but not all, of the columns in a composite index may invoke the index, even if the leading column is not referenced in the WHERE clause.

Correlated subquery

-can be in SELECT, UPDATE, and DELETE

correlated subquery

-can be single row, multirow, or multicolumn -can be nested as deep as desired -SELECT, DELETE, UPDATE -CANNOT be executed without the parent -an alias is not required for the column reference, if the data does not conflict -corr subquery can be placed in: SET and WHERE of UPDATE, Executes for each row in parent: 1. the parent loads referenced value into the subquery. 2. subquery produces the query value 3. the parent compares the query value to see if the row should be included or not

Subqueries

-can be used in SELECT, INSERT, UPDATE, MERGE, DELETE, CREATE TABLE, and CREATE VIEW -if a single row operator is used and the subquery returns more than 1 row, an error occurs -single row returns can be promoted by: referencing a specific PKcolumn value in the where clause, or using an aggregate function with no group by, or referencing the ROWNUM -if no rows are found, then a null is returned

merge'

-combine the functionality of INSERT, UPDATE, and DELETE -results in a single pass through the database syntax: MERGE INTO tableName/view USING sourceTable/subquery/view ON condition WHEN MATCHED THEN UPDATE SET columnName = expression/DEFAULT where_clause DELETE where_clause WHEN NOT MATCHED THEN INSERT (col, col2) VALUES (expr1, expr2 | DEFAULT) where_clause WHERE condition;

COMPOSITE INDEX

-composite indexes combine column data by first column then second -if all columns are used in WHERE or the first column, the optimizer will likely use the index; if the second column is referenced, then the optimizer may not use it because it is farther down the index list

dynamic performance views

-display information about current database activity in real time. -GV_$ global dynamic performance views -V_$ dynamic performance views -should only be viewed with simple queries

Unique Index

-helps ensure that a column in a table will contain unique information. -a PRIMARY KEY or UNIQUE constraint on a table, a unique index will automatically be created along with the constraint.

INVISIBLE, VISIBLE

-invisible columns are ommited from a describe syntax in create table: columnName NUMBER(14,2) INVISIBLE, -in order to insert into a invisible column, it must be named in the insert statement -in order for a invisible column to be visible in a view, it must be named in the query

NATURAL JOIN

-joins based on similarly named columns -defaults to a inner join; can be left or right outer or full outer, if specified. SELECT STREET_ADDRESS FROM EMPLOYEES NATURAL LEFT OUTER JOIN ADDRESSES; -cant use aliases or tableprefixes

Visible & Invisible Indexes

-must be visible to be used by the optimizer CREATE INDEX indexName ON tableName(columnName) INVISIBLE; or ALTER INDEX indexName INVISIBLE; SELECT VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'indexName';

WITH

-names queries -exist only within the same select statement; the colon should be only at the end of the select statement -treats WITH clause as a temporary table or inline view -WITH name can be referenced anywhere, other than its own query

ROWNUM

-pseudocolumn that can be referenced in a SELECT clause -numbers are assigned before orderby sorts

single row subqueries

-return 1 row -can have any number of COLUMNS

multiple column subquery

-return more than 1 COLUMN of data -can be multi or single row

multiple row subquery

-returns zero or more rows -parent should use IN instead of =; = will result in an error

INDEX

-separately stores the values of a specified column for fast lookup -is automatically maintained -You cannot create an index on columns of LOB or RAW data types. -created automatically as a byproduct of a primary key or unique constraint -if you drop a table upon which an index is based, the index is automatically dropped.

USING

-similar to natural join, it uses a common column to join the tables -can be used with inner and outer joins -can't use aliases or tableprefixes -can utilize multiple columns

Scalar subqueries

-single row, and single column -can be correlated -can be used anywhere a expression can

inline view

-subquery in the FROM clause

table aliases

-used in INSERT, UPDATE, MERGE, DELETE, and SELECT

ALTER VIEW

-used to: Create, modify, or drop constraints on a view, Recompile an invalid view -if you alter an underlying table in such a way that the original create view statement wouldnt execute, then any attempts to access the view will error; if the opposite is true, then the view will be recompiled successfully automatically. manual recompilation syntax: ALTER VIEW viewName COMPILE; -a warning will issue if the compilation wont work.

Role

A collection of one or more system privileges and/or object privileges and/or other roles

USER _CONS_COLUMNS

Accessible columns in constraint definitions for tables owned by USER

USER_CATALOG

All tables, views, synonyms, and sequences owned by USER

comments

COMMENT ON TABLE tableName IS ''; COMMENT ON objectType fullObjectName IS c1; -objectType is one of the keywords TABLE, COLUMN, or some other objects that are not subjects of the certification exam, such as INDEXTYPE, OPERATOR, MATERIALIZED VIEW, and others. -fullObjectName is the name of the object for which you want to add a comment. If it's a TABLE, name the table. But if it's a column, use the TABLE.COLUMN syntax. -c1 is the full text of the comment you want to add.

SEQUENCE

CREATE SEQUENCE sequenceName; or with options: CREATE SEQUENCE sequenceName sequenceOptions; sequenceOptions: INCREMENT BY integer; default = 1 START WITH integer; default = minvalue for asc, maxvalue for desc if both ommitted, it = 1 MAXVALUE integer NOMAXVALUE MINVALUE integer NOMINVALUE CYCLE NOCYCLE -cannot use CURRVAL directly after sequence creation; NEXTVAL must be used first -even if a insert fails, the sequence used, will still go to the next val -cannot be used in default -cannot invoke CURRVAL or NEXTVAL in the subquery of a CREATE VIEW statement or of a SELECT, UPDATE, or DELETE statement. -You cannot invoke CURRVAL or NEXTVAL in the WHERE clause of a SELECT statement. -You cannot use CURRVAL or NEXTVAL in a CHECK constraint. -You cannot combine CURRVAL or NEXTVAL with the set operators UNION, INTERSECT, and MINUS. -You can call a sequence pseudocolumn from anywhere within a SQL statement that you can use any expression.

USER _TAB_COLUMNS

Columns in USER's own tables and views

USER _IND_COLUMNS

Columns in user tables used in indexes owned by USER

CREATE SESSION

Connect to the database.

USER _CONSTRAINTS

Constraints on tables owned by USER

CREATE ROLE

Create a role. Includes ALTER and DROP.

CREATE SEQUENCE

Create a sequence in your user account. Includes ALTER and DROP.

CREATE PUBLIC SYNONYM | DROP PUBLIC SYNONYM

Create a synonym in the PUBLIC account. Does not include DROP, which is separate. Drop a synonym from the PUBLIC account.

CREATE SYNONYM

Create a synonym in your user account. Includes ALTER and DROP. Does not include PUBLIC synonyms (see CREATE PUBLIC SYNONYM).

CREATE TABLE

Create a table in your user account. Includes the ability to use ALTER and DROP TABLE. Also includes the ability to use CREATE, ALTER, and DROP INDEX on objects.

CREATE VIEW

Create a view in your user account. Includes ALTER and DROP.

USER _ERRORS

Current errors on stored objects owned by USER

V$RESERVED_WORDS:

Current list of reserved words, including information indicating whether the keyword is always reserved, and if not, under what circumstances it is reserved

USER _DEPENDENCIES

Dependencies to and from a user's objects

ALL_COL_COMMENTS

Displays comments for all columns of all tables and views in the database

ALL_TAB_COMMENTS

Displays comments for all objects in the database

Equijoin

Equijoins connect data in two or more tables by looking for common data among the tables' columns. In other words, an equijoin looks for an exact match of data.

WITH GRANT OPTION

GRANT ALL PRIVILEGES ON tableName TO userName WITH GRANT OPTION; -allows users to grant that particular privilege to other users. REVOKE SELECT, UPDATE ON WEBINARS FROM HENRY; -revokes on with grant option, revoke from the user as well as anyone they granted that privilege to. - REVOKE statement does not require the WITH GRANT OPTION clause

ALL PRIVILEGES (object privileges)

GRANT ALL PRIVILEGES ON tableName TO userName; -does not include with grant option -keyword PRIVILEGES is optional on grant as well as revoke

PUBLIC

GRANT CREATE ANY TABLE TO PUBLIC; public account is anyone who connects to the db

GRANT

GRANT privilege TO userOrRole REVOKE privilege FROM userOrRole; GRANT privilege TO user WITH ADMIN OPTION; -When any system privilege is granted with the WITH ADMIN OPTION option, then the recipient receives the system privilege itself, along with the right to grant the system privilege to another user GRANT ALL PRIVILEGES TO userOrRole;

GRANT ANY PRIVILEGE

Grant any system privilege to any user account in the database.

GRANT ANY OBJECT PRIVILEGE

Grant, to any other user account in the database, any object privilege that the object's owner is also able to grant.

USER _COL_PRIVS

Grants on columns of tables owned by USER

USER_TAB_PRIVS

Grants on objects for which the user is the grantor, grantee, or owner

USER _TAB_PRIVS

Grants on objects owned by USER

unconditional multitable insert

INSERT ALL INTO tab1 VALUES (col_list1) INTO tab2 VALUES (col_list2) INTO tab3 VALUES (col_list3) subquery -must be at least one INTO clause. -VALUES list is optional; if omitted, the select list from the subquery will be used. subquery is a SELECT statement that can stand alone. -any failure will scrap the entire process -Each VALUES list in a multitable INSERT can specify any column names or expressions that are in the subquery select list. -**table aliases are not usable outside of the subquery, however column aliases are** -You cannot execute a multitable INSERT on a view; it can be used only with a table.

conditional multitable insert

INSERT option WHEN expression THEN INTO tab1 VALUES (col_list1) WHEN expression THEN INTO tab2 VALUES (col_list2) ... ELSE INTO tab3 VALUES (col_list3) subquery; -For each row returned by the subquery, each WHEN condition is evaluated and determined to be either true or false. -The option is one of two keywords: ALL or FIRST. -ALL is default -FIRST: WHEN conditions are evaluated until the first true condition is encountered. -Each INTO may have its own VALUES clause; if omitted, the subquery's select list must match the number and data types of the INTO table's columns. -Each expression evaluates to true or false and should involve one or more columns from the subquery. -any failure will scrap the entire process -**table aliases are not usable outside of the subquery, however column aliases are**

V$DATABASE

Includes information about the database itself, including the database name, the date created, the current operating system platform, and much more

V$INSTANCE:

Includes the instance name, the host name, the startup time, and much more

V$TIMEZONE_NAMES:

Includes two columns: TZNAME, which is time zone region, and TZABBREV, which is the time zone abbreviation

USER _INDEXES

Indexes owned by USER

V$SESSION:

Many current settings for each individual user session, showing active connections, login times, machine names that users are logged in to, the current state of transactions, and much more

Non-Equijoin

Non-equijoins connect data by looking for relationships that don't involve equality, such as "less than" or "greater than" relationships, or situations where data in one table is within a range of values in another. SELECT S.SCORE_ID, S.TEST_SCORE, G.GRADE FROM SCORES S JOIN GRADING G ON S.TEST_SCORE BETWEEN G.SCORE_MIN AND G.SCORE_MAX;

USER _OBJECTS

Objects owned by USER

FLASHBACK ANY TABLE

Perform flashback operations on any table in the database.

USER _SYNONYMS

Private synonyms owned by USER (public synonyms are displayed in ALL_SYNONYMS and DBA_SYNONYMS)

USER _SEQUENCES

Sequences owned by USER

USER_SYS_PRIVS

System privileges granted to current user

DBA_SYS_PRIVS

System privileges granted to users and roles