1Z0-051考试笔记

ORACLE 如何建立表外键
例：学生表student (id, name , sex )  
  成绩表score (id ,math )  


如何创建表，要求 有主键，有约束 解:
  CREATE TABLE STUDENT(ID CHAR(10), NAME VARCHAR(8),SEX CHAR(1));  
  ALTER TABLE STUDENT ADD CONSTRAINT PK_STUDENT PRIMARY KEY(ID);  
  CREATE TABLE SCORE( ID CHAR(10),MATH NUMBER(5,2));  
  ALTER TABLE SCORE ADD CONSTRAINT FK_SCROE FOREIGN KEY(ID) REFERENCES STUDENT(ID);
* 主键与外键：
  键是表中的列（可以是一列，也可以是几列），主键用于唯一的标识表中的数据项；外键用于连接父表和子表。而所谓的父表和子表是根据3NF
  范式的要求，为了消除传递依赖，将原表拆成2个相互关联的表，而这个关联就是外键。
 
ALTER TABLE zxt RENAME COLUMN mcc TO mc;


Q2.
CONSTRAINT name FOREIGN KEY (column_name) REFERENCES table_name (column_name);


Q3.


SQL> select hiredate+'10' from emp;  增加10天


HIREDATE+
---------
27-DEC-80
02-MAR-81
04-MAR-81
SQL> select hiredate from emp
  2  ;


HIREDATE
---------
17-DEC-80
20-FEB-81




Q4.
MAX()和MIN()函数不仅可以作用于数值型数据，也可以作用于字符串或是日期时间数据类型的数据。


实例MAX()函数用于字符型数据


可见，对于字符串也可以求其最大值。


说明
 对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。


当然，对与日期时间类型的数据也可以求其最大/最小值，其大小排列就是日期时间的早晚，越早认为其值越小，如下面的实例。


Q5.'
sub  queries
A. Multiple columns or expressions can be compared between the main query and sub query
B. Main query and sub query can get data from different tables
C. Sub queries can contain GROUP BY and ORDER BY clauses




Q6.
IN比较符的多行子查询
在多行子查询中必须使用多行比较运算符 , IN比较符返回子查询的中的每一个值，一旦有与该值相等的数据行则输出这些满足条件的数据行。 
例子10-21查询那些是所在岗位中工资最低的员工信息。
SQL> select ename,job,sal,hiredate
  2  from emp
  3  where sal  in (select min(sal)
  4                                from emp
  5                                group by job); 


Q7.
the two statement produce identical results as ORDER BY 2 will take the second column as sorting column.


Q8.
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following are required clauses:
A SELECT list
A FROM clause
The following are optional clauses:
WHERE
GROUP BY
HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the
parent  


SQL> SELECT   package_id, SUM (package_line_id)
  2      FROM (SELECT package_id, package_line_id, 1 dummy
  3              FROM kdlv_package_lines
  4             WHERE package_id BETWEEN 30006 AND 30028)
  5  GROUP BY (SELECT 'package_id'
  6              FROM DUAL)
  7  /
GROUP BY (SELECT 'package_id'
          *
ERROR at line 5:
ORA-22818: subquery expressions not allowed here


Q9.


Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)
A. SELECT TO_CHAR(1890.55,'$99G999D00')
FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99')
FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$0G000D00')
FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D99')
FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$9,999D99')
FROM DUAL;
Answer: A,C,D


Q10
 It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement
Using the ORDER BY Clause in Set Operations
The ORDER BY clause can appear only once at the end of the compound query. Component queries cannot have individual ORDER BY clauses. The ORDER BY
clause recognizes only the columns of the first SELECT query.
By default, the first column of the first SELECT query is used to sort the output in an ascending order


Q12
SQL have character, numeric, date, conversion function.
Incorrect answer:
ASQL have character, numeric, date, conversion function. CSQL have character, numeric, date, conversion function. DSQL have character, numeric, date, conversion
function. FSQL have character, numeric, date, conversion function.


Q15.AC


Q16 D
You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
D. SELECT student_id, marks, ROWNUM "Rank"
FROM (SELECT student_id, marks
FROM students
WHERE (finish_date BETWEEN '01-JAN-99 AND '31-DEC-99' AND course_id = `INT_SQL'
ORDER BY marks DESC)
WHERE ROWNUM <= 10 ;


如果你要是用 rownum查询只能使用
SQL> select rownum ,empno ,ename,job,mgr,hiredate from emp where rownum < 5; 查询前四条的记录!


ROWNUM EMPNO ENAME JOB MGR HIREDATE
---------- ---------- ---------- --------- ---------- ------------------
1 7369 SMITH CLERK 7902 17-DEC-80
2 7499 ALLEN SALESMAN 7698 20-FEB-81
3 7521 WARD SALESMAN 7698 22-FEB-81
4 7566 JONES MANAGER 7839 02-APR-81




Q17
B
SYSDATE cannot be used with the CHECK constraint


CHECK Constraint
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:
References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns(伪列) Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define
on a column. CHECK constraints can be defined at the column level or table level.
CREATE TABLE employees
(...
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),


Q19
Oracle INSERT WITH CHECK OPTION的用法
insert into (<select clause> WITH CHECK OPTION) values (...)


例如：


SQL> insert into (select object_id,object_name,object_type from xxx where object_id<1000 WITH CHECK OPTION)
2 values(999,'testbyhao','testtype');
这样的语法看起来很特殊，其实是insert进subquery里的这张表里，只不过如果不满足subquery里的where条件的话，就不允许插入。


如果插入的列有不在subquery作为检查的where条件里，那么也会不允许插入。


如果不加WITH CHECK OPTION则在插入时不会检查。


这里注意，subquery其实是不会实际执行的。


Q21
Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement
containing a sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes. Because
sequences are not tied directly to tables, the same sequence can be used for multiple tables.
However, if you do so, each table can contain gaps in the sequential numbers.
Modifying a Sequence
 
If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence
exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement To remove a sequence, use the DROP
statement:
DROP SEQUENCE dept_deptid_seq;


Q22.
Creating a Synonym for an Object
To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym
eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects.
This method can be especially useful with lengthy object names, such as views.
In the syntax:
PUBLIC Creates a synonym that is accessible to all users synonym Is the name of the synonym to
be created object Identifies the object for which the synonym is created Guidelines
The object cannot be contained in a package.
Q24


Q25
1、关于trunc 和round函数比较


整体概括：


round函数 四舍五入
trunc函数 直接截取
对于时间：


Round函数对日期进行“四舍五入”，Trunc函数对日期进行截取。如果我们不指定格式的话，Round会返回一个最接近date参数的日期，而Trunc函数只会简单的截取时分秒部分，返回年月日部分


对于数字：


Round函数对数字同样进行“四舍五入”，Trunc函数截取时不进行四舍五入，可以指明保留小数点后面的位数。
2、Round、Trunc用法


[sql] view plain copy 在CODE上查看代码片派生到我的代码片
/**************************For date****************************/  
--Trunc函数  
SELECT TRUNC(SYSDATE), -- 今天的日期为  2014-05-08  
       TRUNC(SYSDATE, 'YYYY'), --返回当年第一天 2014-01-01  yyyy/yy/year 年  
       TRUNC(SYSDATE, 'MM'), --返回当月第一天  2014-05-01   mm/month     月  
       TRUNC(SYSDATE, 'DD'), --返回当前年月日 2014-05-08    d/day        周  
       TRUNC(SYSDATE, 'D'), --(星期天)返回当前星期的第一天 2014-05-04  
       TRUNC(SYSDATE, 'HH'), --返回当前时间精确小时  2014-05-08 11:00:00  
       TRUNC(SYSDATE, 'MI') -- 返回当前时间精确分钟  2014-05-08 11:06:00  TRUNC()函数没有秒的精确    
  FROM DUAL;  
--Round函数  
select Round(sysdate, 'Q') Rnd_Q, --2014-04-01  
       Round(sysdate, 'Month') Rnd_Month, --2014-05-01  
       Round(sysdate, 'WW') Rnd_Week, --2014-05-07  
       Round(sysdate, 'W') Rnd_Week_again, --2014-05-08  
       Round(sysdate, 'DDD') Rnd_day, --2014-05-08  
       Round(sysdate, 'DD') Rnd_day_again, --2014-05-08  
       Round(sysdate, 'DAY') Rnd_day_of_week, --2014-05-11  
       Round(sysdate, 'D') Rnd_day_of_week_again, --2014-05-11  
       Round(sysdate, 'HH12') Rnd_hour_12, --2014-05-08 12:00:00  
       Round(sysdate, 'HH24') Rnd_hour_24, --2014-05-08 12:00:00  
       Round(sysdate, 'MI') Rnd_minute --2014-05-08 11:52:00  
  from dual;  
  
/****************************For number************************/  
/**  
--TRUNC（number,num_digits）   
Number 需要截尾取整的数字。   
Num_digits  指明需保留小数点后面的位数。可选项，忽略他则截去任何的小数部分,Num_digits 的默认值为 0,  
--此参数够为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。  
TRUNC()函数截取时不进行四舍五入**/  
--TRUNC函数  
select TRUNC(123.458), --123  
       TRUNC(123.458, 0), --123  
       TRUNC(123.458, 1), --123.4  
       TRUNC(123.458, 2), --123.45  
       TRUNC(123.458, 3), --123.458  
       TRUNC(123.458, 4), --123.458  
       TRUNC(123.458, -1), --120  
       TRUNC(123.458, -2), --100  
       TRUNC(123.458, -3), --0  
       TRUNC(123.458, -4), --0  
       TRUNC(123), --123  
       TRUNC(123, 1), --123  
       TRUNC(123, 2), --123  
       TRUNC(123, 3), --123  
       TRUNC(123, 4) --123  
  from dual;  
--Round函数  
select Round(123.458), --123  
       Round(123.458, 0), --123  
       Round(123.458, 1), --123.5  
       Round(123.458, 2), --123.46  
       Round(123.458, 3), --123.458  
       Round(123.458, 4), --123.458  
       Round(123.458, -1), --120  
       Round(123.458, -2), --100  
       Round(123.458, -3), --0  
       Round(123.458, -4), --0  
       Round(123), --123  
       Round(123, 1), --123  
       Round(123, 2), --123  
       Round(123, 3), --123  
       Round(123, 4) --123  
  from dual;  
  
  
  Q26
  A function is a program written to optionally accept input parameters, perform an operation, or return a single value. A function returns only one value per execution.
Three important components form the basis of defining a function. The first is the input parameter list. It specifies zero or more arguments that may be passed to a
function as input for processing. These arguments or parameters may be of differing data types, and some are mandatory while others may be optional. The second
component is the data type of its resultant value. Upon execution, only one value is returned by the function. The third encapsulates the details of the processing
performed by the function and contains the program code that optionally manipulates the input parameters, performs calculations and operations, and generates a return
value.




Q27


The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT
statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.


Q28


The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds.
The default display and input format for any date is DD-MON-RR.
RR Date Format
The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the
return value varies according to the specified two digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element.


Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want.
This data is stored internally as follows:
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43


Q29.
. The VALUES clause cannot be used in an INSERT with a subquery


Q32.
Q34.
Using the COUNT Function
The COUNT function has three formats:
COUNT(*)
COUNT(expr)
COUNT(DISTINCT expr)
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the
columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause.
In contrast,
COUNT(expr) returns the number of non-null values that are in the column identified by expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.


Q35.
1、LONG 数据类型中存储的是可变长字符串，最大长度限制是2GB。


2、对于超出一定长度的文本，基本只能用LONG类型来存储，数据字典中很多对象的定义就是用LONG来存储的。
1、LONG 数据类型中存储的是可变长字符串，最大长度限制是2GB。


2、对于超出一定长度的文本，基本只能用LONG类型来存储，数据字典中很多对象的定义就是用LONG来存储的。


3、LONG类型主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。


4、很多工具，包括SQL*Plus，处理LONG 数据类型都是很困难的。


5、LONG 数据类型的使用中，要受限于磁盘的大小。


能够操作 LONG 的 SQL 语句：
1、Select语句


2、Update语句中的SET语句


3、Insert语句中的VALUES语句


限制：


1、一个表中只能包含一个 LONG 类型的列。


2、不能索引LONG类型列。


3、不能将含有LONG类型列的表作聚簇。


4、不能在SQL*Plus中将LONG类型列的数值插入到另一个表格中,如insert into ...select。


5、不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。


6、不能对LONG类型列加约束条件（NULL、NOT NULL、DEFAULT除外），如：关键字列(PRIMARY KEY)不能是 LONG 数据类型。


7、LONG类型列不能用在Select的以下子句中：where、group by、order by，以及带有distinct的select语句中。


8、LONG类型列不能用于分布查询。




Q36.
PRIMARY KEY Constraint
A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or a set
of columns that uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or column combination and ensures that no column that is
part of the primary key can contain a null value.
Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary
key column or columns


Q38.
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
- NVL(commission_pct,0)
- NVL(hire_date,'01-JAN-97')
- NVL(job_id,'No Job Yet')\


Q39.
（88）You need to calculate the number of days from 1st January 2007 till date. Dates are stored in the default format of dd-mon-rr.
Which SQL statements would give the required output? (Choose two .)
A. SELECT SYSDATE - '01-JAN-2007' FROM DUAL;
B. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;
C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL;
D. SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2007' FROM DUAL;
E. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2007' FROM DUAL;


答案：（B、C）
解析：
--A（X）,会隐式将'11-MAR-14'转换为数字,而不是日期。
scott@TESTDB11>select sysdate - '11-MAR-14' from dual;
select sysdate - '11-MAR-14' from dual
                 *
ERROR at line 1:
ORA-01722: invalid number
-- B, 与默认的日期格式一致（即使指定4位的年也可以)
scott@TESTDB11>select sysdate - to_date('11-MAR-14') from dual;
SYSDATE-TO_DATE('11-MAR-14')
----------------------------
                  -.76417824
-- C, 与默认的日期格式的区别只在分隔符上,也可以正确执行               
scott@TESTDB11>select sysdate - to_date('11/MAR/14') from dual;
SYSDATE-TO_DATE('11/MAR/14')
----------------------------
                  -.76405093
 
Q40.


Q41.
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
- NVL(commission_pct,0)
- NVL(hire_date,'01-JAN-97')
- NVL(job_id,'No Job Yet')
 
MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2 The result can be positive or negative. If date1 is later than date2, the result
is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
MONTHS_BETWEEN returns a numeric value. - answer C NVL has different datatypes - numeric and strings, which is not possible!
The data types of the original and if null parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert if null
to the type of the original parameter. The NVL function returns a value with the same data type as the original parameter.


Q44.
Creating Joins with the USING Clause
Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be usedfor an equijoin.
The Natural JOIN USING Clause
The format of the syntax for the natural JOIN USING clause is as follows:
SELECT table1.column, table2.column
FROM table1
JOIN table2 USING (join_column1, join_column2...);
While the pure natural join contains the NATURAL keyword in its syntax, the JOIN...USING syntax does not.
An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN...USING clause allows one or more equijoin columns to be explicitly
specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on
certain columns, and this format caters to this requirement.


Q45.
Rules for Performing DML Operations on a View
You cannot add data through a view if the view includes:
Group 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


Q46.
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.
DVARCHAR2 Variable-length character data, from 1 byte to 4KB. The data is stored in the database character set. The VARCHAR2 data type must be qualified with a
number indicating the maximum length of the column.
If a value is inserted into the column that is less than this, it is not a problem: the value will only take up as much space as it needs. If the value is longer than this
maximum, the INSERT will fail with an error. VARCHAR2(size)
Variable-length character data (A maximum size must be specified: minimum size is 1; maximum size is 4,000.)
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.
 
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.


Q47.
The MERGE Statement allows you to conditionally insert or update data in a table. If the rows are present in the target table which match the join condition, they are
updated if the rows are not present they are inserted into the target table


Q48.
Rules for Performing DML Operations on a View
You cannot add data through a view if the view includes:
Group 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 views


Q50.


RIGHT OUTER JOIN 
其实这三种都是表链接的方式，right 和left
join是外连接的两种方式。
select t1.col1, t2.col2 from t1
right（或者） left outer join
t2 on t1.id = t2.id。
这两个你用哪个都是无所谓的，关键是看你到底是要用那张表作为驱动表，
说的通俗一点就是如果是left,就要要把左边那张表(t1)里的列数据都查出来，不管跟第二张表有没有匹配.
如果匹配那么，结果集中就会出现col2,如果没有对应的col2,那么这一行记录就会
对应一个null
也就是这一行记录就成了col1, null
相反如果是right:   null, col2
full join,就是把两种情况给综合起来.都放到结果集中.
也就是结果集中可能出现:   col1, null
                                          null,  col2
                                           col1, col2
  
Q52.
ORACLE集合操作函数：UNION、INTERSECT、MINUS
集合操作不适用于LOB，Varray和潜逃表列


Union、intersect、minus操作符不适用于long列


如果选择列表中包含有表达式或者函数，那么必须为表达式或函数定义列别名


1、Uinon：无重并集，并以第一列的结果进行升序排序


2、Uinon all：有重并集，不对结果集排序


3、Intersect：交集，以第一列的结果进行升序排列


4、Minus：差集，以第一列的结果进行升序排列


5、可使用order by，必须放在最后一条select之后，当列名相同时，可以直接用列名排序，如果不同可以用位置排序，也可以使用别名使其相同。


Q53.
Using the NVL2 Function
The NVL2 function examines the first expression. If the first expression is not null, the NVL2 function returns the second expression. If the first expression is null, the third
expression is returned.
Syntax


NVL2(expr1, expr2, expr3)
In the syntax:
expr1 is the source value or expression that may contain a null expr2 is the value that is returned if expr1 is not null expr3 is the value that is returned if expr1 is null


Q58.
日期-日期，返回的是天数，是一个数字，
由本地连接到远程，SYSDATE是一个 SQL 函数，它用于返回当前所连接的远程服务器上数据库的日期和时间。


59.
Note: The IN operator is internally evaluated by the Oracle server as a set of OR conditions, such as a=value1 or a=value2 or a=value3. Therefore, using the IN operator
has no performance benefits and is used only for logical simplicity.   


Q60, 
1、Uinon：无重并集，并以第一列的结果进行升序排序


2、Uinon all：有重并集，不对结果集排序


3、Intersect：交集，以第一列的结果进行升序排列


4、Minus：差集，以第一列的结果进行升序排列


Q62.
Object privilege on VIEW is DELETE, INSERT, REFERENCES, SELECT and UPDATE


Q63.
C. When a data definition language statement is executed
D. When a TRUNCATE statement is executed after the pending transaction
E. When a ROLLBACK command is execute


Q65.
INTERVAL DAY TO SECOND类型可以用来存储单位为天和秒的时间间隔。下面这条语句创建一个名为promotions的表，用来存储促销信息。promotions表包含了一个INTERVAL DAY TO SECOND类型的列duration，该列用来记录促销有效的时间间隔：


 CREATE TABLE promotions (
promotion_id INTEGER CONSTRAINT promotions_pk PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
duration INTERVAL DAY(3) TO SECOND (4)
);
注意此处指定了duration列中天的精度为3，秒的小数部分精度为4。这就是说可以为该列的天存储3位数字，而为该列的秒最多可以在小数点右边存储4位数字。


Q69.
CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END


Q70.


--A(X), USING只能用于等值连接
--B(X), 可以指定多个pair of columns
select e.empno, e.ename, e.deptno, d.loc
from emp e join dept d
on(e.deptno = d.deptno and d.deptno = e.deptno)
where empno = 7788;
--D
--ON可以与WHERE一起使用
select e.empno, e.ename, e.deptno, d.deptno, d.loc
from emp e join dept d
on(e.deptno = d.deptno)
where e.empno = 7788;
     EMPNO ENAME       DEPTNO   DEPTNO LOC
---------- ---------- ---------- ---------- -------------
      7788 SCOTT                20           20  DALLAS
     
--USING可以和WHERE一起使用
select e.empno, e.ename, deptno, d.loc
from emp e join dept d
using(deptno)
where empno = 7788;
     EMPNO ENAME          DEPTNO LOC
---------- ---------- ---------- -------------
      7788 SCOTT              20 DALLAS
 
 
Q75.
Creating a Table Using a Subquery
Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.
CREATE TABLE table
[(column, column...)]
AS subquery;
Match the number of specified columns to the number of subquery columns. Define columns with column names and default values.
Guidelines
The table is created with the specified column names, and the rows retrieved by the SELECT statement are inserted into the table.
The column definition can contain only the column name and default value. If column specifications are given, the number of columns must equal the number of columns
in the subquery SELECT list.
If no column specifications are given, the column names of the table are the same as the column names in the subquery.
The column data type definitions and the NOT NULL constraint are passed to the new table. Note that only the explicit NOT NULL constraint will be inherited. The
PRIMARY KEY column will not pass the NOT NULL feature to the new column. Any other constraint rules are not passed to the new table. However, you can add
constraints in the column definition.


Q77.


Using the TO_CHAR Function with Dates
TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that
describes the format of datetime stored in a character string. For example, the datetime format model for the string '11- Nov-1999' is 'DD-Mon-YYYY'. You can use the
TO_CHAR function to convert a date from its default format to the one that you specify.


Guidelines
· The format model must be enclosed with single quotation marks and is case-sensitive. · The format model can include any valid date format element. But be sure to
separate the date value from the format model with a comma.
· The names of days and months in the output are automatically padded with blanks. · To remove padded blanks or to suppress leading zeros, use the fill mode fm
element.
Elements of the Date Format Model
--------------------------------------------------------------------- DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week