C993 PDF

Preview

Summary

Study notes and links to content that may be on the exam but is not covered in the text....

Description

Oracle 1Z0-071 SQL Certified Associate Study Notes, links to topics not in the text but are on the exam https://www.examtopics.com/exams/oracle/1z0-071/view/ https://www.dbexam.com/sample-questions/oracle-1z0-071-certification-sample-questions-and-answers https://vceguide.com/oracle/1z0-071-oracle-datbase-12c-sql-v2/ https://www.red-gate.com/simple-talk/sql/oracle/invisible-columns-in-oracle-database-12c/ Index Organized Tables: https://oracle-base.com//articles/8i/index-organized-tables Tablespaces: https://docs.oracle.com/cd/B19306_01/server.102/b14220/physical.htm GRANT UNLIMITED TABLESPACE TO GARY; unlimited tablespace in any current or future tablespace ALTER USER GARY QUOTA UNLIMITES IN USERS; unlimited tablespace in just users WITH CHECK OPTION: https://www.oracletutorial.com/oracle-view/oracle-with-check-option/? fbclid=IwAR2reXWwpN6YPGy9TnBZMr63OHIrvXO72SOQWZKiNYkD1MEsYiMHPTbglzo Nice resource on the differences between DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE: https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm#i1005943 Hierarchical Queries: https://www.complexsql.com/hierarchical-queries/ Connect by is an Oracle-specific way to create data trees using SQL. It has two key clauses, start with and connect by. select * from employees start with manager_id is null connect by prior employee_id = manager_id; more on hierarchical: https://docs.oracle.com/database/121/SQLRF/queries003.htm#SQLRF52335 https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm ROLLUP, CUBE, and GROUPING function: https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions064.htm#SQLRF00647 FLASHBACK Query (AS OF) https://oracle-base.com/articles/10g/flashback-query-10g FLASHBACK VERSION Query https://oracle-base.com/articles/10g/flashback-version-query-10g alter database flashback on ... The database must be in ARCHIVELOG database is mounted in exclusive mode

flashback query: select ... as of SCN scn | TIMESTAMP tmstamp https://oracle-base.com/articles/10g/flashback-version-query-10g VERSIONS BETWEEN TIMESTAMP | SCN minvalue AND maxvalue VERSIONS BETWEEN SCN 725202 AND 725219 https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm VERSIONS_XID pseudocolumn REGEX_REPLACE https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm REGEXP_SUBSTR(CITY,'ville$') = 'ville'; https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions116.htm

NVL(nullable_object, returned_thing) if null, return thing NVL2(obj, return1, return2) if null, return 2, if not null, return 1 return rightmost if null rightmost thing must be convertable into its alternative. e.g.: nvl(number, string) error nvl(string, number) ok nvl2(anything, number, string) error nvl2(anything, string, number) ok Schema objects Tables Constraints Indexes Views Sequences Private synonyms Non-schema objects Users Roles Public synonyms Namespaces: USER and ROLE PUBLIC SYNONYM TABLE, VIEW, SEQUENCE, PRIVATE SYNONYM, and user-defined TYPE objects INDEX CONSTRAINT External tables: no LOB data, no constraints, no UNUSED columns JOIN ON needs Boolean (not ) TRUNCATE TABLE tablename: requires DROP ANY TABLE or in your userspace, is DDL so does not fire triggers

hierarchical query select ename, employee_id, manager_id, prior ename, level from Employee_M connect by prior employee_id = manager_id start with manager_id is null; select lpad(' ',level*5,' ')||ename, employee_id, manager_id, prior ename, level from Employee_M connect by nocycle prior employee_id = manager_id start with manager_id=’2’ order siblings by ename; The DATE datatype cannot be used with AT TIME ZONE System privileges and ROLES: WITH ADMIN OPTION Object privileges: WITH GRANT OPTION roles and tables are in different namespaces (so could have the same name) copy table, explict NOT NULL is carried over but implicit NOT NULL is not ALTER TABLE table_name MODIFY ( column_name NOT NULL); ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); ALTER VIEW MED_BENEFITS COMPILE; TO_TIMESTAMP contains fractional seconds TO_TIMESTAMP_TZ TIMESTAMP WITH TIME ZONE SYS_EXTRACT_UTC pulls UTC data from a datetime value CAST AS TIMESTAMP WITH LOCAL TIME ZONE END is not used with DECODE SELECT * FROM ORDERS FETCH FIRST/NEXT 8 ROWS ONLY; -or- FETCH FIRST/NEXT 50 PERCENT ROWS ONLY; NEXT == FIRST, no difference FETCH … WITH TIES; OFFSET 5 ROWS FETCH FIRST 2 ROWS ONLY; Without ORDER BY, WITH TIES behaves like ONLY.

-- read for a prompted value ACCEPT vRoomNumber PROMPT "Enter a room number: " -- prompt without read PROMPT Remember, you asked for the room number &vRoomNumber. substitution variable &variable needs quotes in query or at runtime define subst. variable DEFINE vWindows = Ocean UNDEFINE vWindows

DEFINE --by itself shows all variables system variables SET and SHOW SHOW ALL SET VERIFY/VER ON/OFF -- shows old and new lines SET DEFINE ON/OFF -- needs to be on to use substit. variables ACCEPT vRoom PROMPT "Enter your room number" PROMPT Nice weather in Room &vRoom DEFINE vRoom = Ocean WHERE Window = '&Window_Type'

underscore _ is a wildcard 5.2 Analytical Functions OVER, PARTITION BY, and ORDER BY https://wgu.ucertify.com/?func=ebook&chapter_no=6#03pD2 Aggregate_function(column) over (order by column..._) Aggregate_function(column) over (partition by column... order by column..._) --or-- LAG/LEAD instead of aggregate VIEW must assign name to calcualted column CREATE INDEX ix1 ON PORTS(PORT_NAME) INVISIBLE; ALTER INDEX ix1 INVISIBLE/VISIBLE; TCL: COMMIT, SAVEPOINT, ROLLBACK FLASHBACK TABLE tablename TO BEFORE DROP [RENAME TO newname] FLASHBACK TABLE HOUDINI TO SCN scn_expression; FLASHBACK TABLE HOUDINI TO TIMESTAMP timestamp_expression; FLASHBACK TABLE HOUDINI TO RESTORE POINT restore_point_expression; cannot FLASHBACK after ALTER PURGE TABLE tablename; PURGE RECYCLEBIN; -- for user PURGE DBA_RECYCLEBIN; -- all users CREATE TABLE HOUDINI (VOILA VARCHAR2(30)) ENABLE ROW MOVEMENT; ALTER TABLE HOUDINI ENABLE ROW MOVEMENT; Using a subquery in a CREATE TABLE statement WITH (subquery) AS subquery_name WITH SHIPPER_INFO AS

(SELECT SHIP_ID FROM SHIPS) SELECT PORT_ID FROM PORTS, SHIPPER_INFO; Multitable INSERT ALL (default) | FIRST A multitable insert statement can replace and perform better than PL/SQL routines MERGE INTO table USING table | subquery ON condition WHEN MATCHED THEN UPDATE SET col = expression | DEFAULT where_clause DELETE where_clause WHEN NOT MATCHED THEN INSERT (col, col2) VALUES (expr1, expr2 | DEFAULT) where_clause WHERE condition; DICTIONARY USER_CATALOG aka CAT - tables, views, synonyms, and sequences owned by the user USER_OBJECTS - information about all objects owned by the user USER_CONSTRAINTS R_CONSTRAINT_NAME - foreign key (R for reference, referential integrity) R_CONSTRAINT_NAME column will show the name of a primary key constraint that the foreign key references 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 USER_IND_COLUMNS view has one row for each of the columns upon which a given INDEX object is based 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 SELECT WINDOW , ROOM_NUMBER , SQ_FT , SUM(SQ_FT) OVER (PARTITION BY WINDOW ORDER BY SQ_FT) "Running Total" , SUM(SQ_FT) OVER (PARTITION BY WINDOW ORDER BY SQ_FT ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) "Subset" FROM SHIP_CABINS ORDER BY SQ_FT;

5.2

PERCENTILE_DISC PERCENTILE_CONT TR=(1+(P*(RN-1))) can be used with PARTITION BY to specify groups of data (CEIL-TR)*(ValueattheFLOOR)+(TR-FLOOR)*(ValueattheCEIL) SELECT WINDOW , ROOM_NUMBER , SQ_FT , PERCENTILE_CONT(.6) WITHIN GROUP (ORDER BY SQ_FT) OVER (PARTITION BY WINDOW) "PCT" FROM SHIP_CABINS ORDER BY SQ_FT; SELECT ... RANK(x) WITHIN GROUP (ORDER BY...) FROM ... HAVING after WHERE, and before or after GROUP BY WHERE cannot use aggregate Nested Aggregate functions require a GROUP BY clause SELECT NUMTODSINTERVAL(120, 'MINUTE') AS "Answer" FROM DUAL; must be in single quote TIMESTAMP WITH TIME ZONE cannot be used with a PRIMARY KEY but TIMESTAMP WITH LOCAL TIME ZONE *can* be used with a PRIMARY KEY If you want to take advantage of automatic management of changes to time due to Daylight Saving, be sure to store datetime data using: Time zone region names Storing time in the TIMESTAMP WITH TIME ZONE datatype using a time zone region name, such as 'America/New York', will enable time values to be adjusted automatically for Daylight Saving time. Oracle Corporation formally advises against using a sequence generator in a multitable INSERT statement. While it will be rejected in the subquery, a sequence generator may be included in the VALUES clause of the INTO statement but with potentially unpredictable or undesirable results. tablespace user_data Select * from scott.emp as of timestamp to_timestamp('11/03/2017 12:00:00','mm/dd/yyyy hh24:mi:ss'); truncate remove data and reset high water mark Index Organized table CONSTRAINT emp_pk PRIMARY KEY (emp_id)) ORGANIZATION INDEX INCLUDING dept_id OVERFLOW TABLESPACE employee_tablespace; INSERT WITH CHECK OPTION: INSERT INTO (SELECT .... FROM tablename WHERE ... WITH CHECK OPTION) VALUES (...)

The following are not contained in a schema: Users Roles Tablespaces Contexts Directories Parameter files (PFILEs) and server parameter files (SPFILEs) Profiles Rollback segments Parallel clause external table creation Data Pump impdp command: import data pump GV_$ GV$ V$ SELECT ... AS OF does not allow subqueries FULL JOIN = FULL OUTER JOIN can't use table alias on using or natural SELECT i.id...