1Z0-071 Oracle Database SQL

REPLACE()

REPLACE('text', 'search_string', ['replacement_string']) Searches an expression for a named string and replaces it if found, if replacement is omitted then it removes any occurrences of the search string

What is the syntax for revoking an object privilege from a user?

REVOKE objectprivilege ON object FROM username;

ROUND()

ROUND(column/expression, n) rounds the expression to n decimal places if n is negative, numbers to the left of the decimal are rounded

ROUND() date

ROUND(date, 'format model') format model may be 'MONTH' or 'YEAR' 'MONTH' will round the day of the month up or down -16-31 round up -1-15 round down

RR format vs YY

RR is 21st century format 0-49 = 20xx 50-99= 19xx YY

How do you create a join by using a subquery in the from clause?

SELECT ..... FROM table1 a, (SELECT... FROM table2..) b WHERE a.something=b.something OR a.something>b.something;

DECODE()

SELECT column1, column2, DECODE(column/expression, search1, result1 [, search2, result2,.....,] [, default]) optional alias FROM table; if default value is omitted, then null will be returned in the instance that there are no matches the the column is equal to the search then return the result

SUBSTR() SUBSTR('Austin', 1, 3)

SUBSTR(column/expression, m, n) m- starting position n- number of characters long if m is negative, the count starts from the end Aus

date-date

Subtracts two dates, returns number of days between

What are 7 different object privileges and what can they be used on?

T=Table V=View S=Sequence Alter- T, S Delete- T, V Index- T Insert- T, V References- T Select- T, V, S Update- T, V

How would you write a date as 7 OF July 2017 using to_char?

TO_CHAR(SYSDATE, 'fmdd "OF" Month YYYY') String inside double quotes is case sensitive fm, dd, etc. is also case sensitive

TO_CHAR from DATE

TO_CHAR(date, ['date format']) -format must be enclosed in single quotes -format is case sensitive

number TO_CHAR()

TO_CHAR(number, ['format model'])

TO_DATE()

TO_DATE('string in valid date format', 'format model') converts string to default date format used by Oracle 11g (dd-MON-rr) -oracle will remove any spaces found in the string, to leave spaces and keep output same as input, put fx at the beginning of the date format ('fxdd-MON-rr')

TO_NUMBER()

TO_NUMBER('string', ['format model']) string must be exclusively numeric

TRIM()

TRIM(leading/trailing/both, trim_character FROM trim_source) trim_character, trim_source need to be in single quotes if a string TRIM('string')- removes spaces from beginning and end

TRUNC()

TRUNC(column/expression, n) Truncates the expression to n decimal places

TRUNC() date

TRUNC(date, 'format model') format model may be 'MONTH' or 'YEAR' trunc(date, 'MONTH') -makes the day of the month 1 and keeps month

What is returned by a multi row function? What are some examples?

The function returns one result per set of rows SUM, AVG, MAX, MIN, COUNT

What is returned by a Single Row Function? What are some examples?

The function will return one result per row UPPER, LOWER, CONCAT

Object Privileges

What objects you have access to and how you can manipulate the content of the D.B. objects

What is a cartesian product?

When you obtain data from multiple tables without using a join formed when: -a join condition is omitted -a join condition is invalid -all rows in first table joined to all rows in second table to avoid always put a valid join in a where clause product table is rows in table1 X rows in table2

What are the six most common DBA system privileges?

CREATE USER DROP USER CREATE ANY TABLE BACKUP ANY TABLE SELECT ANY TABLE DROP ANY TABLE Valid for all schemas in D.B.

How do you create a D.B. user?

CREATE USER username IDENTIFIED BY password;

What is the default date display format?

DD-MON-RR

DATE Suffixes TH SP SPTH or THSP

DDTH- 4th (ordinal number with th) DDSP- four (spelled out) SPTH or THSP- fourth (spelled out with suffix)

How do you drop a user and what are the limitations?

DROP USER username; -can't drop a user currently connected -if the user owns objects, add CASCADE to end of syntax to drop their objects -Not commonly used

COALESCE()

COALESCE(expr1, expr2, expr3, .... exprn) returns the first non null expression in the list can also nest NVL function to get same result

CONCAT()

CONCAT(column1/expression1, column2/expression2) equivalent to || operator can only take two parameters, user || operator to concatenate more columns/expressions

How do you create roles, give them privileges, and assign them to users?

CREATE ROLE rolename; GRANT privilege TO rolename; GRANT rolename TO user;

NVL2()

NVL2(expr1, expr2, expr3) if expr1 is not null then it returns expr2 if expr1 is null then it returns expr3 expressions 2 and 3 should have the same datatype

Where do you find system privileges directly assigned to you? (Doesn't show privileges assigned to you through roles)

user_sys_privs

ORA-00979

"not a group by expression" means that you have left out a column in the group by clause that appeared in the select statement

What does the Oracle server return when it fails?

###

Where do you find privileges you've assigned to other users?

user_tab_privs_made

Where do you find your privileges assigned to you on all schemas?

user_tab_privs_recd

YEAR

year spelled out in english

NVL()

NVL(expr1, expr2) converts a null value to an actual value both expressions must be the same datatype

Single Row Function

-returns one result per row (upper, lower) -may modify the datatype -can be nested up to any level -accept arguments that may be either an expression or a column

TO_CHAR(SYSDATE, 'dd-mm-yyyy hh:mi:ss AM'

07-07-2017 6:30:21 PM AM and PM doesn't matter in the to char statement, both will just signal intent of putting the correct one at the end of the date in the result

TO_CHAR(SYSDATE, 'dd.mm.yyyy')

07.07.2017 punctuation is reproduced in result

HH or HH12

12 hour format

HH24

24 hour format

number format models 9 0 $ L D . G , MI PR EEEE U V S B

9- number of 9's determines display width 0- displays leading 0's $- floating dollar sign L- floating local currency D- returns a decimal in specified position .- returns a decimal in specified position G- returns the group separator (,) in specified position ,- comma in specified position MI- specifies a negative sign to the right of number PR- specifies parentheses for negative numbers <> EEEE- scientific notation (must use 4 E's) U- specifies the Euro symbol V- multiply by 10 n times (n is number of nines after V) S-returns a negative or positive value (ex. +12 or -12) B- displays zero values as blank, not 0

SYSDATE

A function that returns the current database server date and time in date format

What is a multiple column subquery? How is one written?

A subquery that returns more than one column for comparison with the parent query SELECT column, column FROM table WHERE (column, column) IN (SELECT column, column, column FROM... WHERE...);

What is a Global Temporary Table and how do you create one?

A table that holds data for only the duration of the transaction (session). CREATE GLOBAL TEMPORARY TABLE tablename (columnname datatype, columnname datatype) ON COMMIT DELETE ROWS; to delete rows on commit or ON COMMIT PRESERVE ROWS; to keep data until end of session (exit SQL)

ADD_MONTHS()

ADD_MONTHS(date, numberofmonthstoadd) date then number datatypes

How do you disable a constraint? What happens to the constraint indexes? What limitations does this have? How do you enable a constraint?

ALTER TABLE tablename DISABLE CONSTRAINT constraint name; The indexes will be dropped Can't disable a constraint if others reference it. May add CASCADE to end of syntax to disable all Enabling uses same syntax with word enable

How do you rename a column?

ALTER TABLE tablename RENAME COLUMN columnname TO newname;

How do you change the name of a constraint?

ALTER TABLE tablename RENAME CONSTRAINT constraintname TO newname;

How do you change a user password?

ALTER USER username IDENTIFIED BY newpassword;

What does a grant to public do?

All DBA users receive the grant

How would you grant update on a column to a user?

GRANT UPDATE(column) ON tablename TO username;

What is the syntax for granting an object privilege?

GRANT objectprivilege ON object TO username [WITH GRANT OPTION]; -allows the user(s) you grant the privilege to to grant that same privilege to other users or roles

How do you grant a privilege to a user?

GRANT privilege TO username; can grant multiple privileges to multiple users in the same grant statement

What is the syntax for assigning a role to a user?

GRANT rolename TO username;

INITCAP()

INITCAP(column/expression) first letter of EACH word is capitalized, the rest are lowercase

How do you insert, update, and delete values in a subquery target table

INSERT INTO (SELECT.... FROM.... WHERE...) Values(.....); UPDATE " " set .....=....; DELETE " ";

INSTR()

INSTR(column/expression, 'string', m, n) string- string you are searching for m- start position n- number of occurrences of the string Returns the numeric position of the named string

LAST_DAY()

LAST_DAY(date) returns date format with last day of the month

LENGTH()

LENGTH(column/expression) Returns number of characters in the expression

LOWER()

LOWER(column/expression) returns all lower case

LPAD/RPAD()

LPAD/RPAD(column, n, 'string') Lpad inserts a string value to the left of selected column up to n characters ex. LPAD(SALARY, 10, '#') ######2400

MOD() MOD(20, 7)

MOD(m, n) returns the remainder of m/n 6

MONTHS_BETWEEN()

MONTHS_BETWEEN(date1, date2) parameters should be date datatype if date1<date2, number will be negative

NEXT_DAY()

NEXT_DAY(date, 'VALID DAY'/number 1-7) returns the date of the next specified valid day

NULLIF()

NULLIF(expr1, expr2) compares two expressions and returns null if they are equal, returns the first expression if they are not equal

What happens when you revoke privileges from a user who has used their grant option on the privilege(s) being revoked?

The revoke will cascade to all users given privileges by the revoked user

UPPER()

UPPER(column/expression) returns uppercase

Correlated Delete

Used for deleting all records in a copy table that are contained in the original table DELETE FROM ecopy WHERE EXISTS (SELECT 1 FROM employees e WHERE e.employee_id=ecopy.employee_id);

Correlated Update

Used when number of records in two tables are equal Uses a correlated subquery to update columns based on the value of columns in another table Requires an alia update emp_copy ecopy set salary = (select salary from emp e where e.employee_id=ecopy.employee_id);

What is a group function?

a function that operates on a set of rows (a bunch of rows in a column) to return one result group_function([DISTINCT/ALL] n) All is default so it may be omitted You may only nest up to 2 group functions

date+number

adds a number of days to the date, returns date datatype

date+number/24

adds a number of hours to a date, returns date datatype

Where do you find all public synonyms?

all_synonyms

GROUP BY limitations

cannot group by using column alias all non group functions appearing in the select statement must be included in the group by statement where group by order by Where statements cannot contain group functions, must use having having statement can be on the line before group by statement, or the line after it although placing it before the group by is not normal practice incorrect: Select...... From...... Where avg(salary)>1000 group by order by correct: select, from, group by Having avg(salary)>1000 Order by

What is a Schema

collection of objects such as tables, views, and sequences

ORA-00937

error code stating "not a single-group group function" use group by clause to fix SELECT DEPT_ID, SUM(SALARY) FROM EMPLOYEES dept_id doesn't have a group function so you should add GROUP BY DEPT_ID to the end of the syntax if there was another column without a group function in the select statement, it would also need to be put in the group by clause

TO_CHAR(SYSDATE, 'fmdd-mon-yyyy')

fm placed at the beginning of the format model will remove all unnecessary spaces and zeros from the result

DAY

full name of day of the week

MONTH

full name of month

YYYY

full year (0100 to 9999)

How do the following group functions handle null values? min(column) max(column) count(column) sum(column) avg(column) count(distinct column) count(*)

min- ignores null values max- ignores null values count(column)- ignores null values sum(column)- ignores null values avg(column)- ignores null values count(distinct column)- ignores null values count(*)- counts all rows including null values

MI

minute (0-59)

CASE()

more flexible and common than DECODE() complies with ANSI SQL SELECT column, column2, column3, CASE expr WHEN comparison_expr1 THEN result [WHEN comparison_expr2 THEN result2 ELSE result3] END optional_alias FROM table; or more flexible method SELECT column, column2, column3, CASE WHEN expr comparison expr1 THEN result WHEN expr comparison expr2 THEN result2 ELSE result3 END optional_alias FROM table; all keywords are mandatory except for the word ELSE Omitting the ELSE will make the else default to NULL

Role

named group of related privileges

DD

numeric day of the month

System Privileges

performing a particular action within the D.B. such as creating a table or a view or gaining access to the DB Dropping and creating users and tables over 200 different system privs.

Multi Row Function

returns one value per set of rows

Where do you find system privileges for various roles?

role_sys_privs

Where do you find privileges on tables assigned to various roles?

role_tab_privs

rr vs yy

rr treats numbers 0-49 as 20xx, and 50-99 as 19xx yy treats all numbers as 20xx

SS

second (0-59)

SSSSS

seconds past midnight (0-86399)

What data dictionary view contains information about your current system privileges including those assigned to you through roles?

session_privs

DY

three letter abbreviation for day of the week

MON

three letter month abbreviation

MM

two digit month value

Where do you find information on the columns withing tables that you have privileges to?

user_col_privs_recd