Questions Tips

■1,Specify DISTINCT or UNIQUE if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.

eg:

SQL> select manager_id from employees;

MANAGER_ID

----------

         1

         2

         2

SQL> select distinct manager_id from employees;

MANAGER_ID

----------

         1

         2

SQL> select unique manager_id from employees;

MANAGER_ID

----------

         1

         2

■2,Foreigh key

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

eg:

①CREATE TABLE t1 (

   pk NUMBER PRIMARY KEY,

   fk NUMBER,

   c1 NUMBER,

   c2 NUMBER,

   CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1,

   CONSTRAINT ck1 CHECK (pk > 0 and c1 > 0),

   CONSTRAINT ck2 CHECK (c2 > 0)

);

②ALTER TABLE dept ADD CONSTRAINT mgr_cons FOREIGN KEY (mgr_ref) REFERENCES emp;

③SQL> edit;

已写入 file afiedt.buf

  1  create table emp_test

  2  ( empno number(4),

  3    ename varchar(35),

  4    deptno number(7,2)

  5*  constraint emp_deptno_fk references dept(deptno))

SQL> /

表已创建。

SQL>

■3,Role

SQL> conn /as sysdba;
已连接。
SQL> create role test_role;
角色已创建。
SQL> grant select on store.employees to test_role;
授权成功。
SQL> grant test_role to scott;
授权成功。
SQL> conn scott/tiger;
已连接。
SQL> select count(*) from  store.employees;
  COUNT(*)
----------
         4
SQL>

■4,Object Naming 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.

eg:

SQL> create table package (t number(2));

表已创建。

SQL> CREATE TABLE object (empid number(2));

表已创建。

SQL> CREATE TABLE synonyms (empid number(2));

表已创建。

SQL> CREATE TABLE select (empid number(2));

CREATE TABLE select (empid number(2))

             *

第 1 行出现错误:

SQL> CREATE TABLE update (empid number(2));

CREATE TABLE update (empid number(2))

             *

第 1 行出现错误:

ORA-00903: 表名无效

ORA-00903: 表名无效

SQL> CREATE TABLE EMP9$# (empid number(2));

表已创建。

■5,Insert check option

SQL> create table employees

  2  ( employee_id number not null,

  3    emp_name varchar2(30),

  4    job_id varchar2(20),

  5    sal number,

  6    mgr_id number,

  7    department_id number)

  8  ;

表已创建。

SQL> create sequence store.emp_id_seq start with 1;

序列已创建。

SQL> insert into (select * from employees

  2               where department_id in (20,50) with check option)

  3        values(emp_id_seq.nextval,'&ename','&jobid',2000,null,&did);

输入 ename 的值:  test

输入 jobid 的值:  test2

输入 did 的值:  2

原值    3:       values(emp_id_seq.nextval,'&ename','&jobid',2000,null,&did)

新值    3:       values(emp_id_seq.nextval,'test','test2',2000,null,2)

      values(emp_id_seq.nextval,'test','test2',2000,null,2)

             *

第 3 行出现错误:

ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规

SQL> edit;

已写入 file afiedt.buf

  1  insert into (select * from employees

  2               where department_id in (20,50) with check option)

  3*       values(emp_id_seq.nextval,'&ename','&jobid',2000,null,&did)

SQL> /

输入 ename 的值:  test

输入 jobid 的值:  test

输入 did 的值:  20

原值    3:       values(emp_id_seq.nextval,'&ename','&jobid',2000,null,&did)

新值    3:       values(emp_id_seq.nextval,'test','test',2000,null,20)

已创建 1 行。

SQL> select * from employees;

EMPLOYEE_ID EMP_N JOB_I        SAL     MGR_ID DEPARTMENT_ID

----------- ----- ----- ---------- ---------- -------------

          4 test  test        2000                       20

SQL> insert into employees values(emp_id_seq.nexval,'&ename','&jobid',2000,null,&did in (20));

输入 ename 的值:  test2

输入 jobid 的值:  test2

输入 did 的值:  20

原值    1: insert into employees values(emp_id_seq.nexval,'&ename','&jobid',2000,null,&did in (20))

新值    1: insert into employees values(emp_id_seq.nexval,'test2','test2',2000,null,20 in (20))

insert into employees values(emp_id_seq.nexval,'test2','test2',2000,null,20 in (20))

                                                                            *

第 1 行出现错误:

ORA-00917: 缺失逗号

■6,alias table

SQL> select employees.last_name,departments.department_id

  2        from employees e,departments d

  3       where e.department_id =d.department_id

  4  /

select employees.last_name,departments.department_id

                           *

第 1 行出现错误:

ORA-00904: "DEPARTMENTS"."DEPARTMENT_ID": 标识符无效

SQL> edit;

已写入 file afiedt.buf

  1  select e.last_name,d.department_id

  2        from employees e,departments d

  3*      where e.department_id =d.department_id

SQL> /

未选定行

■7 insert

 ・The syntax : use either the VALUES keyword or a subquery, but not both

    ×　insert into t1 (c1,c2) values(select c1,c2 from t1);

    ・insert rows into several tables with one statement

■8 Merge

MERGE [ hint ]
   INTO [ schema. ] { table | view } [ t_alias ]
   USING [ schema. ] { table | view | subquery }
     [ t_alias ]
   ON ( condition )
   [ merge_update_clause ]
   [ merge_insert_clause ]
   [ error_logging_clause ]

■9

Which three are true regarding the use of outer joins? (Choose three.)

A. You cannot use IN operator in a condition that involves an outerjoin.

B. You use (+) on both sides of the WHERE condition to perform an outerjoin.

C. You use (*) on both sides of the WHERE condition to perform an outerjoin.

D. You use an outerjoin to see only the rows that do not meet the join condition.

E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.

F. You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.

Answer: A, E, F  ?

･外连接约束

:: 外连接操作符只能出现在表达式一侧—缺少信息的一侧。它从一个表中返回那些在另一个表中没有直接匹配的行。

　eg:table2.column (+) 是外连接符号，它可以放在 WHERE 子句的条件的任一边，但不能两边都放。(跟着没有匹配行的表中列的名字放置外连接符号)。

:: 包括一个外连接的条件不能用 IN 操作符或连接到另一个用 OR 操作符的条件。

■10

What is true about joining tables through an equijoin?

A. You can join a maximum of two tables through an equijoin.

B. You can join a maximum of two columns through an equijoin.

C. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT

statement.

D. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.

E. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

Answer: E

Explanation:

For N joined tables using Oracle or ANSI/ISO syntax for table joins, you need at least N-1 equijoin conditions in the WHERE clause of your SELECT statement or N-1 JOIN table_name ON join_condition clauses in order to avoid a Cartesian product, respectively.

Incorrect Answers

A: There is no maximum on two tables to join them through an equijoin.

B: There is no maximum on two columns to join them through an equijoin.

C: You can specify an equijoin condition only in the FROM clauses of a SELECT statement.

D: There is no limitation about primary key and foreign key for the columns to use them for an equijoin.

■11,Not null

SQL> desc emp

 名称                                      是否为空? 类型

 ----------------------------------------- -------- -------------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

SQL> alter table emp modify ename constraint dfaf not null;

表已更改。

SQL> desc emp;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- -------------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                     NOT NULL VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

SQL> alter table emp drop constraint dfaf;

表已更改。

SQL> desc emp;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- -----------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

SQL> alter table emp add constraint testconsr check (ename is not null);

表已更改。

SQL> desc emp;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

SQL> alter table emp add constraint testconsr check (ename is not null);

alter table emp add constraint testconsr check (ename is not null)

                               *

第 1 行出现错误:

ORA-02264: 名称已被一现有约束条件占用

SQL> insert into emp(empno,ename) values (9999,null);

insert into emp(empno,ename) values (9999,null)

*

第 1 行出现错误:

ORA-02290: 违反检查约束条件 (SCOTT.TESTCONSR)

■11,Lpad

SQL> desc emp;

 名称                                      是否为空? 类型

 ----------------------------------------- -------- --------------------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 MGR                                                NUMBER(4)

 HIREDATE                                           DATE

 SAL                                                NUMBER(7,2)

 COMM                                               NUMBER(7,2)

 DEPTNO                                             NUMBER(2)

SQL> insert into emp(empno,sal) values(1001,17000);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select lpad(sal,10,*) from emp where empno=1001;

select lpad(sal,10,*) from emp where empno=1001

                   *

第 1 行出现错误:

ORA-00936: 缺失表达式

SQL> select lpad(sal,10,'*') from emp where empno=1001;

LPAD(SAL,10,'*')

--------------------

*****17000

■12,update view

Q: What is true about updates through a view?

A. You cannot update a view with group functions.

B. When you update a view group functions are automatically computed.

C. When you update a view only the constraints on the underlying table will be in effect.

D. When you update a view the constraints on the views always override the constraints on the underlying

tables.

Answer: A

■13,join

Q: In which three cases would you use the USING clause? (Choose three.)

A. You want to create a nonequijoin.

B. The tables to be joined have multiple NULL columns.

C. The tables to be joined have columns of the same name and different data types.

D. The tables to be joined have columns with the same name and compatible data types.

E. You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.

Answer: C, D, E

■14,Date

fill mode (fm) operator. By prefixing the format model with the letters fm, Oracle is instructed to trim all spaces from the names of days and months

Q:  Which SQL statement displays the date March 19, 2001 in a format that

appears as "Nineteenth of March 2001 12:00:00 AM"?

A. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;

B. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;

C. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY HH:MI:SS AM') NEW_DATE FROM dual;

D. SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmtDdspth "of" Month YYYY fmtHH:MI:SS AM') NEW_DATE FROM dual;

Answer: A

eg:

SQL> alter session set NLS_DATE_LANGUAGE =english

  2  ;

会话已更改。

SQL> SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;

NEW_DATE

------------------------------------

Nineteenth of March 2001 12:00:00 AM

SQL> SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;

NEW_DATE

--------------------------------------

Nineteenth of March     2001 12:0:0 AM

SQL> SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY HH:MI:SS AM') NEW_DATE FROM dual;

NEW_DATE

----------------------------------

Nineteenth of March 2001 12:0:0 AM

SQL> SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmtDdspth "of" Month YYYY fmtHH:MI:SS AM') NEW_DATE FROM dual;

SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmtDdspth "of" Month YYYY fmtHH:MI:SS AM') NEW_DATE FROM dual

                                                      *

第 1 行出现错误:

ORA-01821: 日期格式无法识别