Introduction to Oracle9i: SQL (8)

Creating Views
##CONTINUE##

• Percentile_Cont is used for obtaining the median value of an ordered group. Regr_Avgx takes in a pair of list of values and then calculates the average of the second list after eliminating all the Nulls.
• Minimum, Count and Variance are valid summary operations.
• A view can be created as a join on two or more tables. This type of view is called complex view.
• (Syntax)
  CREATE [OR REPLACE] [FORCE NOFORCE]
  VIEW [schema.]view [(alias [,alias]...)]
  AS subquery
  [WITH CHECK OPTION [CONSTRAINT constraint]]
  [WITH READ ONLY [CONSTRAINT constraint]] ;
• You cannot remove a row if the view contains the following:
• You cannot modify data in a view if it contains:
• You cannot add data through a view if the view includes:
  - Grouping functions
  - A GROUP BY clause
  - The DISTINCT keyword
  - The pseudocolumn ROWNUM keyword
  - Columns defined by expressions
  - NOT NULL columns in the base tables that are not selected by the view.
• The name and definition of the view is stored in the data dictionary ‘user_view’
• A table is key preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.
• DROP VIEW trans_view;
• A view can be created if the base table does not exist but constraints cannot be defined on the view without the DISABLE NOVALIDATE clause.
• TABLE PRIVILEGES data dictionary view gives the information of the all OBJECT privileges granted to the user.
• USER_OBJECTS : Information of all the objects created by the user.
• USER_COL_PRIVS_RECD : Privileges granted to the users on the specific columns of the table.
• USER_TAB_PRIVS : Privileges granted to the users on the specific tables.
• We cannot index a view
• (Syntax)
  INSERT INTO managerid(id, name, salary, hiredate)
  SELECT empno, ename, sal, hiredate
  FROM emp WHERE job=’MANAGER’;

Introduction to Oracle9i: SQL (7)

Aggregating Data and Group Functions
##CONTINUE##

• Natural [inner] join
• NATURAL join selects rows from the tables that have equal values in all matched columns(same column names, same datatype)
• Set operators such as Union, Union All, Intersect and Minus are used to select data from multiple tables. The basically combine the results of two queries into one and hence are called Compound queries.
• Conversion functions can be used to convert the first column so that the datatype returned by the function is the same as the second column’s datatype. So there is possible join two tables which first columns datatype is different from the first column in the second.
• A Cartesian product is formed when:
  － A join condition is omitted
  － A join condition is invalid
  － All rows in the first table are joined to all rows in the second table
• Group function can be used in SELECT clause and GROUP BY clause.
• AVG, COUNT, MAX, MIN, STDDEV, SUM, and VARIANCE are aggregate functions. Except for COUNT(*), all aggregate functions ignore nulls.
• It is possible to mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. Also it is acceptable to pass column names, expressions, constraints, or other functions as parameters to an aggregate function.
• It is not possible to use columns in the GROUP BY clause which are not included in the select list. But ORDER BY is possible.
• It is not possible to use alias name in the GROUP BY clause. But ORDER BY is possible.
• DISTINCT is used to display unique data.
• HAVING is used to restrict the output of a group function. It is used to further restrict the groups which GROUP BY is to include.
• GROUP BY clause should contain the items listed in the SELECT list that do not use a group function.
• Group_id helps identify duplicate groups. Grouping helps to identify duplicate rows. Keep function returns the first or last row of a sorted group.

Introduction to Oracle9i: SQL (6)

Subqueries
##CONTINUE##

• ‘Rollback’ rolls back the entire transaction. ‘Rollback to savepoint’ rollback the transaction only upto the save point. ‘Rollback work’ is the equvalent of ‘Rollback’, though it is not often used.
• Exclusive Locks and Share Locks are two basic locking modes in Oracle. Share Locks prevent other exclusive locks but allow other share locks. Exclusive Locks prevent other exclusive as well as share locks.
• U can inserted Unlimited tables via a single INSERT use INSERT ALL
• Lexical substitution variable can be used to replace values in the WHERE caluse
• You want all unmatched data from both tables then u use a FULL OUTER JOIN
• A multiple row subquery can be compared by using the “>” operator. IN, ANY, ALL
• You can use IN operator in a condition that involves an outer join.
• The PROCEDURE, SYNONYMS and VIEWS dependent on the TABLE become invalid when you drop it. When you recreate the TABLE, SYNONYMS and VIEWS become valid but the PROCEDURE will have to be recompiled. INDEXES and TRIGGERS are dropped with table itself. SEQUENCE is a separate database object, and is not dependent on any one.
• SELECT e.last_name, e.dept_id, d.name FROM employee e
  RIGHT OUTER JOIN departments d
  ON (e.dept_id = d.dept_id);
  ->
  SELECT e.last_name, e.dept_id, d.name FROM employee e, departments d WHERE d.dept_id = e.dept_id (+);
• A subquery can be used in the CREATE VIEW statement, regardless of the number of rows it returns.
• EXISTS is used in a correlated subquery.
• CURRVAL pseudocolumn is used to get successive sequence numbers from a particular sequence. It is mostly used in the SET clause of an UPDATE command and the VALUES clause of an INSERT statement.
• Indexes are frequently created for columns that are likely to be used in WHERE clauses for simple equalities or join conditions.Arithmetic operators can be used in any clause of SELECT statement except ‘FROM’ Clause.

Introduction to Oracle9i: SQL (5)

Displaying data from multiple tables
##CONTINUE##

• A variable with a single ‘&’ (ampersand) will prompt the user for the number of times that the variable occurs in the query. A variable with a double ‘&&’ will prompt the user for a value only once even if the variable occurs a number of times in the query.
• Ttitle is used for page headings. Btitle is used for page footers. Repheader is used for report headings. Repfooter is used for report footers.
• ‘CLEAR’ command is used to clear customizations on a column. ‘Cl Buff’ is the command to clear the buffer.
• CHAR datatype has a maximum size of 2000 bytes. Varchar datatype has a maximum size of 4000 bytes. There is no default size for VARCHAR. Default Char size is 1 byte.
• ACCEPT TABLE PROMPT ‘table to view foreign key constraint’
  SELECT CONSTRAINT_NAME FROM user_constraints
  WHERE table_name = UPPER(‘&table’)AND constraint_type = ‘R’;

Introduction to Oracle9i: SQL (4)

Controlling user access
##CONTINUE##

• The default parameter set with the NLS_DATA_FORMAT is the DD-MON-YY format. This can be changed with the Alter Session command.
• Nchr(x) returns the character that has the value equivalent to x in the database national character set.
• Profiles can be used to restrict CPU time used, Total time connected to the database, Maximum time a session can be inactive.
• Create a new database is a privilege of the SYSDBA privilege.
• Start up and shut down a database, Alter a database, Create an SP file is SYSOPER privilege.
• USER_TABLES : To view all the tables in your schema
• USER_OBJECTS : To view all the objects owned by the user database.
• ALL_OBJECTS : if you want to see all the objects you can access in your database, whether it is in your schema or not.
• An object privilege is the right to perform a particular action on a Sequence. The only privileges that apply to a sequence are SELECT and ALTER.
• User_Constraints lists all of the constraints on the table. User_Cons_Columns displays the columns associated with a constraint. Both have table_name which lists the table name associated with the constraint.
• For system privileges even if the grantor looses the privileges the grantee will till retain it.
• System privilege can only be granted with ADMIN option
• Grant Any Privilege allows the grantee to grant any system privilege.
• Oracle supports 3 types of user authentication. The default user authentication is database authentication. In external authentication the password is verified by the operating system or network. In global authentication Oracle checks if the user is a legitimate user. In database authentication Oracle checks for the correct password. In global authentication the password is validated by the Oracle Security Service.
• ALL_COL_PRIVS, ALL_TAB_PRIVS, ALL_COL_PRIVS_MADE, ALL_TAB_PRIVS_RECD
• ALL_COL_PRIVS_MADE displays all of the grants in which the user is the grantor or the owner whether through a role, public, or direct grant, while the latter.
• ALL_COL_PRIVS displays all of the grants on columns that have been granted to the user or to public.
• DBA_CONS_COLUMNS displays all the objects in the database.

Introduction to Oracle9i: SQL (3)

Single row functions
##CONTINUE##

• iSQL*Plus commands can be used to access local and remote databases, manipulation of data is not allowed.
• Show followed by a parameter name like xxx show the value set for that parameter. Set can be used for setting values for parameters. ‘Show all’ will display the values of all the parameters
• Set SQLPrompt used to change the default SQL*Plus prompt from ‘SQL>’ to any other value.
• Spool would save the query output to a file whereas Spool Off switches off spooling. Spool out would send the output file to the printer.
• ‘/’ is used to execute a SQL statement in the SQL buffer of SQL*Plus. ‘;’ will simply display the buffer once again. ‘@’ is used to run SQL commands from a script file.
• Change_on_install is the default password for the SYS user. Tiger is the default password for the default user Scott. Manager is the password for the user System.
• In DENSE_RANK the duplicates are not counted for ranking purposes. In Rank the duplicates are counted. Both returns the row’s rank within an ordered group.
• A single-row comparison operator will always return a single value.
• ROUND(45.923,2) == 45.92, ROUND(45.923,0) ==46, ROUND(45.923,-1) == 50
• TRUNC(45.923,2) == 45.92, TRUNC(45.923) == 45, TRUNC(45.923,-2) == 0
• INITCAP(‘SQL Course’) -> Sql Course
• TO_DATE(char [, ‘format_model’])
• TO_CHAR(SYSDATE, ‘FMDay, DD Mouth, YYYY’)
• TRIM(‘H’ FROM ‘Hello World’) -> ello World
• SUBSTR(‘HelloWorld’,1,5) -> Hello
• INSTR(‘HelloWorld’, ‘W’) -> 6
• LPAD(salary,10, ‘*’) -> *****24000
• RPAD(salary,10, ‘*’) -> 24000*****
• REPLACE('JACK and JUE','J','BL') -> BLACK and BLUE
• ASCIISTR takes in a character string as argument and returns the ASCII representation of non-ASCII characters but the ASCII characters remain unchanged.
• Number(4,2) -> 4-2 =2 integer part and 2 is allocated for the decimal part.
• Number(p,s) precision p and scale s. p -> 1 to 38, s -> -84 to 127. default is 38
• Subtraction between two date and time datatypes is allowed. But not addition. Eg. O timestamp, P date -> O-P
• Query USER_CATALOG can view TABLES, VIEWS, SYNONYMS, and SEQUENCES owned by the user. USER_CATALOG has a synonym called CAT.
• Single-row functions can be used in conjunction with ‘Order By’ and ‘Group By’ clauses.
• User_supplied literal string is enclosed within single quotes(‘literal string’). And Arithmetic expressions. Eg. Where sal > 2 * comm.; Can be used in the WHERE clause. Column alias and Column position can not be used in the WHERE clause. You cannot use group function in the WHERE clause.
• SUBSTRB, SUBSTRC, SUBSTR2, SUBSTR4

Introduction to Oracle9i: SQL (2)

Writing Basic SQL SELECT Statements
##CONTINUE##

• Only the truncate command can shrink the size of the table using the Drop Storage clause.
• When creating a temporary table the data inserted to it is available only to that session (ON COMMIT DELETE ROWS). The default option can be changed by using ‘ON COMMIT PRESERVE ROWS’ (transaction-specific)
• (Syntax)
  COMMENT ON TABLE table COLUMN table.column IS ‘test’;
• Comments can be viewed through the data dictionary views
  － ALL_COL_COMMENTS
  － USER_COL_COMMENTS
  － ALL_TAB_COMMENTS
  － USER_TAB_COMMENTS
• The lengths of Char and Varchar columns can be defined in CHAR or BYTE. The default being BYTE.
• Data dictionary views and tables are created at the time of creation of the database and the user SYS is the owner by default. No other user can become the owner of the data dictionary.
• (Syntax)
  TRUNCATE {TABLE [schema.]table CLUSTER
  [schema.]cluster}
  [ {DROP REUSE} STORAGE]
• Rename a table use Rename to
• Select column total from orde; == Select column “total” from orde;
• Select TO_CHAR(joining_date, ‘fmDDth “of” Month YYYY’) from hr;
• Oracle HTTP server and iSQL*Plus Server are required in the Middle layer. Oracle Net and Oracle 9i Database are in the database layer.
• The ORDER BY clause override the default GROUP BY sort.
• Nulls are always sorted higher than other values and this behavior can be changed using NULLS FIRST, NULL LAST.SQL*Plus commands Append, LI, Change, Input, Clear Buffer(CL BUFF), DEL.

Introduction to Oracle9i: SQL (1)

Manipulating Data

##CONTINUE##

• The only way a function can be removed in Oracle is with the ‘Drop Function’ command
• (syntax)
  CREATE SEQUENCE sequence
  [INCREMENT BY n]
  [START WITH n]
  [{MAXVALUE n NOMAXVALUE}]
  [{MINVALUE n NOMINVALUE}]
  [{CYCLE NOCYCLE}]
  [{CACHE n NOCACHE}]
  ps. MAXVALUE -> 10^27 , -1
  MINVALUE -> 1 , -10^26
  CACHE -> 20
  
  CREATE SEQUENCE dep_seq
  INCREMENT BY 10
  START WITH 120
  MAXVALUE 999
  NOCACHE
  NOCYCLE;
  START WITH option can’t be changed using ALTER SEQUENCE command. Must be dropped and re-created.
• Bitmaps are stored in a compressed format, so take less space than a b-tree index.
• Function based indexes can be both b-tree or bitmap indexes. The expression or function has to be specified when the index is created. To use the index Query_Rewrite_Enable must be set to TRUE.
• Only users with special privileges can create Public synonyms.
• The index created by the Primary key column is called Unique Index.
• When an index is based on multiple columns, it is referred to as concatenated or composite index.
• Alter User jack PASSWORD EXPIRE.
• Alter User jack IDENTIFIED BY psword;
• (syntax)
  MERGE INTO table1 AS table_alias
  USING (table2viewsub_query) AS alias
  ON (join_condition)
  WHEN MATCHED THEN
  UPDATE SET
  col1 = value,
  col2 = value2
  WHEN NOT MATCHED THEN
  INSERT (col_list)
  VALUES (col_value);
  
• It is possible even to use numbers to indicate the column position. -> select ggg from emp order by 2 desc;
• You can use SELECT statement to display and to insert data into different table.
• NVL2(expr1, expr2, expr3) -> if expr1 is not null, return expr2, else return expr3
• Command TRUNCATE is used to remove all row data from the table, while leaving the definition of the table intact, including the definition of constraints and any associated database objects as indexes, constraints, and triggers on the table.
• NUFFIF (expr1, expr2) -> if equal return null, else return expr1.
• All character searches are case sensitive.
• Select, Alter System, Lock Table, Set Role can be used in ‘Read Only’ transactions. Select For Update and Alter Sequence requires manipulation of data and hence cannot be executed on a ‘READ ONLY’.
• Number(p,s) datatype is used for fixed point numbers.A delete statement does not reset the high water mark of the table and hence performance after delete is significantly slow.

Oracle 9i DBA -- Introduction to Oracle9i: SQL (1Z0-007)

Oracle 9i DBA 認證考試 -- Introduction to Oracle9i: SQL (1Z0-007)
此考試的內容有:

• Manipulating Data
• Writing Basic SQL Select Statements
• Single-Row Functions
• Displaying Data from Multiple Tables
• Subqueries
• Aggregating Data using Group Functions
• Creating Views
• Restricting and Sorting Data
• Producing Readable Output with iSQL*Plus
• Including Constraints
• Creating Other Database Objects
• Creating and Managing Tables
• Other

接下來幾篇文章為當初我在準備此門考試的筆記.

Update a table from another table

1. update (select a.cellularnumber aaaphone, b.telephone2 cusphone from aaa_customers a, anacustomer b where a.hnno=b.hn) set cusphone=aaaphone

##CONTINUE##

1. update anacustomer b set telephone2 = ( select cellularnumber from aaa_customers a where a.hnno=b.hn ) where b.hn in ( select a.hnno from aaa_customers a where a.hnno=b.hn )


2. update anacustomer b set telephone2 = ( select cellularnumber from aaa_customers a where a.hnno=b.hn ) where EXISTS ( select hnno from aaa_customers a where a.hnno=b.hn )