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