Oracle Database 12c SQL Fundamentals I Exam Guide Exam 1Z0-061 学习笔记

I. any arithmatic operator with null will result null
   concate null will not result in null, will remain the same without null
II, for ' in literal value, could use '' to escape the it.
    or could use q'<conent ''''''>' to use escape the ' in the literal value, here <> could be any other charactors or bracket paris.
--------3-----------
I.Like wildcard symbol, we could use _(for 1 letter) or %(for 0 or more letters)
  to escapse like wildcard, we could use '\%test' escape '\'; in where clause
  
II, for ampersand(&), we could double ampersand(&&) to define a session variable, so we could reuse this value in current session.
    for ampersand, we could use it in select &col_name from rcv_supply;
    for ampersand, we even could use select &rest_statment;
    if use ampersand for literal values and date, we could to use single quotes, like '&t1'
III. for double ampersand, we could define command to define a variable to let it works the same
     define t1= 'test1'      <=>    select col1 from table1 where col1='&&t1' or col2 = '&t1';
IV undefine could use to clear the variable we defined.
   verify could use to show the value of defined variable while actually running the scripts.

--------------4------------------
TRIM([trailing|leading|both] trimstring from s),
The string to be trimmed (s) is mandatory. The following points list the rules governing the use of this function:
image  TRIM(s) removes spaces from both sides of the input string. When no direction of trimming is specified, then spaces are trimmed from both sides of the string.
image  TRIM(trailing trimstring from s) removes all occurrences of trimstring from the end of the string s if it is present.
image  TRIM(leading trimstring from s) removes all occurrences of trimstring from the beginning of the string s if it is present.
image  TRIM(both trimstring from s) removes all occurrences of trimstring from the beginning and end of the string s if it is present.
SELECT TRIM('aababcca'), TRIM(leading 'a' from 'aababcca'), TRIM(trailing 'a' from 'aababcca'), TRIM(both 'a' from 'aababcca')   from dual;
aababccababccaaababccbabcc
  
-------------------5---------------
nls_parameter always is omitted, and we could use the following command to alter it in session
alter session set nls_currency='USD';
TO_CHAR(number1, [format], [nls_parameter])
The TO_CHAR function, fm modifier, and the sp, th, and spth format models are commonly examined. 


Format Element  Description and Format Mask                         Result
fm              remove relevant space                               select to_char(sysdate, 'fmMonth')||'123' from dual; return 'May123'; If don't use fm, it returns 'May      123'; it Month by default return 9 letters.
TH              Positional or ordinal text:'DDth''of'' Month'       12th of September
SP              Spelled out number: 'MmSP Month Yyyysp'             Nine September Two Thousand Eight
THSP or SPTH    Spelled out positional or ordinal number:'hh24SPTh' Fourteenth


1.NVL(original, ifnull); -- if original is null, then return ifnull expression;
2.NVL2(original, ifnotnull, ifnull); --if orignal is not null, return ifnotnull expression; or else, return ifnull expression;
  ifnotnull and ifnull should have the same data type(or could convert ifnull to same data type as ifnotnull implicitly, nvl2(1, 'a', 4) is valid, nvl2(2, 4, 'a') is invalid.
3.NULLIF(ifunequal, comparison_term);--if ifunequal expression is equals with comparison_term, return null; or else return ifunequal.
4.COALESCE(expr1, expr2,…,exprn); --return the 1st non-null parameter; if all parameters are null, return null; it takes 2 mandatory parameters, and any number of optional parameters 
   all parameters should be the same data type, coalesce(1234, '1',4) is invalid
5.DECODE(expr1,comp1, iftrue1, [comp2,iftrue2…[compN,iftrueN]], [iffalse])
  -- decode function, at least 3 parameters are mandatory.  If the iffalse parameter does not exist and no matches are found, a null value is returned.
  -- decode(1, 1, 3, 'a') is invalid, and will throw exception; but decode(1,1,3, 'a') is valid, since 'a' will never be executed;
  -- all iftruex expression are expected the same data type as iftrue1(or could be converted implicitely), iftruex or iffalse have different data type as iftrue1, but they are not executed, then it is fine, and no exception.
6. CASE EXPRESSION, The CASE expression takes at least three mandatory parameters but can take many more.
CASE search_expr
WHEN comparation_expr1 then iftrue1
[ WHEN comparation_expr2 then iftrue2
......
WHEN comparation_exprN then iftrueN
ELSE iffalse]
END
iftruex and iffalse should be the same data type, even it is not executed(and no implicitly conversion).


  
You are required to return a set of rows from the EMPLOYEES table with DEPARTMENT_ID values of 100. 
The set must contain FIRST_NAME and LAST_NAME values and an expression aliased as NAME_LENGTHS. 
This expression must return the string 'Different Length' 
if the length of the FIRST_NAME differs from that of the LAST_NAME, 
else the string 'Same Length' must be returned.


select FIRST_NAME, LAST_NAME, nvl2(nullif(length(first_name), length(last_name)), 'Different Length', 'Same length'  )
from EMPLOYEES
where DEPARTMENT_ID = 100


-----------------------6-----------------------
a. COUNT({*|[DISTINCT|ALL] expr}) ;
This syntax may be deconstructed into the following forms:
  1. COUNT(*)       --count all values, no matter it is null or not.
  2. COUNT(DISTINCT expr)--only count distinct expr values
  3. COUNT(ALL expr) --this is the same as 4. Only count non-null values.
  4. COUNT(expr)
b. AVG([DISTINCT|ALL] expr) ;
This syntax may be deconstructed into the following forms:
  1. AVG(DISTINCT expr) -- only sum and divide by the num of unique occurrences of expr.
  2. AVG(ALL expr) -- same as 3, only counts the non-null values.
  3. AVG(expr)
c.SUM([DISTINCT|ALL] expr) ;
This syntax may be deconstructed into the following forms:
  1. SUM(DISTINCT expr) --only sum distinct expr values.
  2. SUM(ALL expr)  -- same as 3, only count non-null expr value. null values are ignored.
  3. SUM(expr)
d.SUM([DISTINCT|ALL] expr) ;
This syntax may be deconstructed into the following forms:
  1. SUM(DISTINCT expr)
  2. SUM(ALL expr) --same as 3.only count non-null expr value. null values are ignored.
  3. SUM(expr)
e.VARIANCE([DISTINCT|ALL] expr);
This syntax may be deconstructed into the following forms:
  1. VARIANCE(DISTINCT expr)
  2. VARIANCE(ALL expr)
  3. VARIANCE(expr)
f.STDDEV([DISTINCT|ALL] expr);
This syntax may be deconstructed into the following forms:
  1. STDDEV(DISTINCT expr)
  2. STDDEV(ALL expr)
  3. STDDEV(expr)


Group functions may only be nested two levels deep. Three formats using group functions are shown here:
G1(group_item) = result
G1(G2(group_item) = result
G1(G2(G3(group_item))) is NOT allowed.


--------------7-------------
a. Inner join
The inner join is implemented using three possible join clauses that use the following keywords in different combinations: NATURAL JOIN, USING, and ON.
select * from A natural join B; --the join will the columns with same name from tables to perform join 
select * from A join B using (col1); --the join will use the col1 on both tables to perform join, so even they may have many other columns with same column name;
select * from A join B on (A.col1=B.col2);--The join will use the A.col1 and B.col2 to perform join, as two tables may have different column names.
b. Cross join, is also call Cartesian.
   select * from A cross join B;--this is the same as select * from A, B;
c.Oracle Join Syntax
  there is no need to use keyword 'natural join', 'join using', 'join on' and 'cross join'
d.Joining Tables Using ANSI SQL Syntax
SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2 ON (table1.column_name = table2.column_name)] |
[LEFT | RIGHT | FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)] |
[CROSS JOIN table2];


SELECT table1.column, table2.column
FROM table1, table2
[WHERE (table1.column_name = table2.column_name)] |
[WHERE (table1.column_name(+)= table2.column_name)] |
[WHERE (table1.column_name)= table2.column_name) (+)] ;
e.Nonequijoins
A nonequijoin is specified using the JOIN…ON syntax, but the join condition contains an inequality operator instead of an equal sign.


SELECT table1.column, table2.column
FROM table1
[JOIN table2 ON (table1.column_name < table2.column_name)]|
[JOIN table2 ON (table1.column_name > table2.column_name)]|
[JOIN table2 ON (table1.column_name <= table2.column_name)]|
[JOIN table2 ON (table1.column_name >= table2.column_name)]|
[JOIN table2 ON (table1.column BETWEEN table2.col1 AND table2.col2)]
f. SELF join--Joining a Table to Itself Using the JOIN…ON Clause
g.



------8------------
1.Define Subqueries, subquery could be used ,
In the SELECT list used for column projection
In the FROM clause
In the WHERE clause
In the HAVING clause
A scalar subquery is a query that returns exactly one value: a single row, with a single column. Scalar subqueries can be used in most places in a SQL statement where you could use an expression or a literal value.
Subqueries can be nested to an unlimited depth in a FROM clause but to “only” 255 levels in a WHERE clause.


2.Describe the Types of Problems That the Subqueries Can Solve
a.Use of a Subquery Result Set for Comparison Purposes.--Using NOT IN is fraught with problems because of the way SQL handles NULLs. As a general rule, do not use NOT IN unless you are certain that the result set will not include a NULL.
b.Star Transformation-- used in where clause
There is an instance initialization parameter, STAR_TRANSFORMATION_ENABLED, which (if set to true) will permit the Oracle query optimizer to rewrite code into star queries.
c.Generate a Table from Which to SELECT -- used in from clause
d.Generate Values for Projection -- used in select clause
e.Generate Rows to be Passed to a DML Statement--A subquery can be used to select rows for insertion but not in a VALUES clause of an INSERT statement.
Note that it is not legal to use a subquery in the VALUES clause of an insert statement; 
Insert into test select sysdate from dual;--this is fine
Insert into test values(select sysdate from dual); -- not fine.


3.List the Types of Subqueries
  Single-row subqueries --returns one row. A special case is the scalar subquery, which returns a single row with one column
  Multiple-row subqueries --return sets of rows. These queries are commonly used to generate result sets that will be passed to a DML or SELECT statement for further processing
  Correlated subqueries--subquery references columns in the parent query, then its result will be dependent on the parent query.
  Correlated subqueries can be a very inefficient construct, due to the need for repeated execution of the subquery. Always try to find an alternative approach.
  The comparison operators valid for single-row subqueries are =, >, >=, <, <=, and <>. The comparison operators valid for multiple-row subqueries are IN, NOT IN, ANY, and ALL.


-----------------9-------------------
1.Describe the Set Operators
UNION Returns the combined rows from two queries, sorting them and removing duplicates.
UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates.
MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.--ISO use EXCEPT keyword insted of minus


The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
The corresponding columns in the queries that make up a compound query must be of the same data type group.
The sorting is based on all the columns, from left to right


------------------10----------------
a. Describe Each Data Manipulation Language (DML) Statement
1.INSERT--An INSERT command can insert one row, with column values specified in the command, or a set of rows created by a SELECT statement.
INSERT INTO table [(column [,column…])] VALUES (value [,value…]);
INSERT INTO table [(column [, column…])]
subquery;
2.UPDATE--One UPDATE statement can change rows in only one table, but it can change any number of rows in that table.
UPDATE table SET column=value [,column=value…] [WHERE condition];


3.DELETE--There are no “warning” prompts for any SQL commands. If you instruct the database to delete a million rows, it will do so. Immediately. There is none of that “Are you sure?” business that some environments offer.
DELETE FROM table
[WHERE condition];
4.MERGE-- It could do the insert, update and delete; There are many occasions where you want to take a set of data (the source) and integrate it into an existing table (the target).
5.TRUNCATE-- Not DML(data manupulation language), actually it is DDL(data definition language). Its result looks like DELETE to delete all rows.
             Since it is DDL, user can't use TCL to control it, that means, we can't rollback or commit the changes. However, in comparison to DML, they are very fast.
Transactions consisting of INSERT, UPDATE, and DELETE (or even MERGE) commands can be made permanent (with a COMMIT) or reversed (with a ROLLBACK). A TRUNCATE command, like any other DDL command, is immediately permanent: it can never be reversed.


b.Control Transactions--
A transaction consists of one or more DML statements, followed by either a ROLLBACK or a COMMIT command
It is possible to use the SAVEPOINT command to give a degree of control within the transaction.
ACID test: it must guarantee atomicity, consistency, isolation, and durability.


The explicit transaction control statements are COMMIT, ROLLBACK, and SAVEPOINT. There are also circumstances other than a user-issued COMMIT or ROLLBACK that will implicitly terminate a transaction:
 Issuing a DDL or DCL statement
 Exiting from the user tool (SQL*Plus or SQL Developer or anything else)
 If the client session dies
 If the system crashes
 
 SELECT FOR UPDATE:One last transaction control statement is SELECT FOR UPDATE
 The FOR UPDATE clause will place a lock on all the rows retrieved. No changes can be made to them by any session other than that which issued the command, 
 
-----------------------11---------------------
Naming Schema Objects
A schema object is an object that is owned by a user. All schema object names must conform to certain rules:
  The name may be from 1 to 30 characters long (with the exception of database link names that may be up to 128 characters long).
  Reserved words (such as SELECT) cannot be used as object names.
  All names must begin with a letter from A through Z.
  The characters in a name can only be letters, numbers, an underscore (_), the dollar sign ($), or the hash symbol (#).
  Lowercase letters will be converted to uppercase
  
  Object names must be no more than 30 characters. The characters can be letters, digits, underscore, dollar, or hash.


Object NameSpaces
It is often said that the unique identifier for an object is the object name, prefixed with the schema name.
A namespace defines a group of object types, within which all names must be uniquely identified by schema and name. Objects in different namespaces can share the same name.
These object types all share the same namespace:
  Tables
  Views
  Sequences
  Private synonyms


b.List the Data Types That Are Available for Columns
VARCHAR2 Variable-length character data, from 1 byte to 4000 bytes if MAX_STRING_SIZE=STANDARD or 32767 bytes if MAX_STRING_SIZE=EXTENDED. The data is stored in the database character set.
NVARCHAR2 Like VARCHAR2, but the data is stored in the alternative national language character set, one of the permitted Unicode character sets.
CHAR Fixed-length character data, from 1 byte to 2000 bytes, in the database character set. If the data is not the length of the column, then it will be padded with spaces.


For ISO/ANSI compliance, you can specify a VARCHAR data type, but any columns of this type will be automatically converted to VARCHAR2.


RAW   Variable-length binary data, from 1 byte to 4000 bytes if MAX_STRING_SIZE=STANDARD or 32767 bytes if MAX_STRING_SIZE=EXTENDED. Unlike the CHAR and VARCHAR2 data types, RAW data is not converted by Oracle Net from the database’s character set to the user process’s character set on SELECT or the other way on INSERT.


NUMBER   Numeric data, for which you can specify precision and scale. The precision can range from to 1 to 38, the scale can range from –84 to 127.
FLOAT   This is an ANSI data type, floating-point number with precision of 126 binary (or 38 decimal). Oracle also provides BINARY_FLOAT and BINARY_DOUBLE as alternatives.
INTEGER   Equivalent to NUMBER, with scale zero.


The following are the data types for date and time data, all fixed length:
  DATE   This is either length zero, if the column is empty, or 7 bytes. All DATE data includes century, year, month, day, hour, minute, and second. The valid range is from January 1, 4712 BC to December 31, 9999 AD.
  TIMESTAMP   This is length zero if the column is empty, or up to 11 bytes, depending on the precision specified. Similar to DATE, but with precision of up to 9 decimal places for the seconds, 6 places by default.
  TIMESTAMP WITH TIMEZONE   Like TIMESTAMP, but the data is stored with a record kept of the time zone to which it refers. The length may be up to 13 bytes, depending on precision. This data type lets Oracle determine the difference between two times by normalizing them to UTC, even if the times are for different time zones.
  TIMESTAMP WITH LOCAL TIMEZONE   Like TIMESTAMP, but the data is normalized to the database time zone on saving. When retrieved, it is normalized to the time zone of the user process selecting it.
  INTERVAL YEAR TO MONTH   Used for recording a period in years and months between two DATEs or TIMESTAMPs.
  INTERVAL DAY TO SECOND   Used for recording a period in days and seconds between two DATEs or TIMESTAMPs.


The following are the large object data types:
  CLOB   Character data stored in the database character set, size effectively unlimited: (4GB –1) multiplied by the database block size.
  NCLOB   Like CLOB, but the data is stored in the alternative national language character set, one of the permitted Unicode character sets.
  BLOB   Like CLOB, but binary data that will not undergo character set conversion by Oracle Net.
  BFILE   A locator pointing to a file stored on the operating system of the database server. The size of the files is limited to 4GB.
  LONG   Character data in the database character set, up to 2GB. All the functionality of LONG (and more) is provided by CLOB; LONGs should not be used in a modern database, and if your database has any columns of this type they should be converted to CLOB. There can only be one LONG column in a table.
  LONG RAW   Like LONG, but binary data that will not be converted by Oracle Net. Any LONG RAW columns should be converted to BLOBs.


The following is the ROWID data type:
  ROWID   A value coded in base 64 that is the pointer to the location of a row in a table. Encrypted within it is the exact physical address. ROWID is an Oracle proprietary data type, not visible unless specifically selected.


All examinees will be expected to know about these data types: VARCHAR2, CHAR, NUMBER, DATE, TIMESTAMP, INTERVAL, RAW, LONG, LONG RAW, CLOB, BLOB, BFILE, and ROWID. Detailed knowledge will also be needed for VARCHAR2, NUMBER, and DATE.


c.Create a Simple Table
1.heap table
2.Index organized tables   Store rows in the order of an index key.
3.Index clusters   Can denormalize tables in parent-child relationships so that related rows from different tables are stored together.
4.Hash clusters   Force a random distribution of rows, which will break down any ordering based on the entry sequence.
5.Partitioned tables   Store rows in separate physical structures, the partitions, allocating rows according to the value of a column.


Creating Tables with Column Specifications
To create a standard heap table, use this syntax:
CREATE TABLE [schema.]table [ORGANIZATION HEAP]
(column datatype [DEFAULT expression]
[,column datatype [DEFAULT expression]…);


CREATE TABLE [schema.]table AS subquery;
1.Adding columns-- alter table Test1(tcol1 varchar(20);
2.Modifying columns -- alter table Test1 MODIFY(tcol1 number(7,2) default 0.1);
3.dropping columns -- alter table Test1 drop column tcol1
4.Marking columns as unused --alter table Test1 set unused column tcol1;
5.Renaming columns--alter table Test1 rename column tcol1 to tcol2;
6.Making the table as read only.--alter table Test1 read only.
7.Dropping all unused columns.-- alter table Test1 drop unused columns;


Drop table
DROP TABLE [schema.]tablename;


Oracle 12c includes a recycle bin option, which is enabled by default. This allows any dropped table to be restored unless it was dropped with the PURGE option or if the recycle bin option has been disabled.


Types of Constraints
The constraint types supported by the Oracle database are as follows:
  UNIQUE-- a unique constraint nominates a column (or combination of columns) for which the value must be different for every row in the table. 
  NOT NULL--The not null constraint forces values to be entered into the key column
  PRIMARY KEY--The primary key is the means of locating a single row in a table;A primary key constraint is a unique constraint combined with a not null constraint.
  FOREIGN KEY--a foreign key constraint is defined on the child table in a parent-child relationship.A foreign key constraint in a child table must reference the columns of either a unique constraint or a primary key constraint in the parent table.
  CHECK-- check constraint can be used to enforce simple rules, such as that the value entered in a column must be within a range of values. 
        The not null constraint is in fact implemented as a preconfigured check constraint.