Transcript Oracle Assessment Tests

Oracle9i 数据库语言
SQL语言与SQL*PLUS
SQL命令
SQL关键字（以下命令为SQL命令，以“；”结束）
Alter、 Audit、Commit
、Comment、
Create、Delete
Drop、Grant、Insert、Lock、NoAudit 、
Rename
Revoke、Rollback、Select 、Update 、
Validate。
[email protected]
SQL*PLUS命令
SQL*PLUS关键字（以下命令以“CR”结束）
@、 #、/、 Accept、
Append、
Break、
Btitle、
Change 、
Clear、
Column、
Compute、 Connect、
Copy、
Define、
Del、
Describe、 Disconnect、 Document、
Edit、
Exit、
Get 、
Help、
Host 、
Input、
List、
Newpage 、
Pause、
Quit、
Remark、
Run、
Save、
Set、
Show、
Spool、
Start、
Timing、
Ttitle、
Undefine、
[email protected]
编辑和运行SQL缓冲区的SQL*PLUS命令
命令
APPEND text
CHANGE
CHANGE
CLEAR Buffer
DEL
INPUT
INPUT text
LINE
LINE n
RUN
缩写
A text
C/old/new
C/text/
CL Buff
解释
行尾增加 text
在当前行中将old换为/new
在当前行中删除 text
清除缓冲区
DEL
I
I text
L
Ln
R
[email protected]
删除当前行
增加一行
增加有text组成的行
显示缓冲区内容
显示第 n 行
运行SQL缓冲区命令
Oracle Quiz
《QL 测 试》
Oracle
Assessment Tests
[email protected]
《Oracle Assessment Tests》

You query the database with this command:
SELECT name
FROM employee
WHERE name LIKE ‘_a%’;
Which names are displayed?
A.
B.
C.
D.
names starting with ‘a’
names starting with ‘a’ or ‘A’
names containing ‘a’ as the second letter
names containing ‘a’ as any letter except the
first
[email protected]
《Oracle Assessment Tests》
 For
which task would it be most
appropriate to use the keyword
DISTINCT?
A.
B.
C.
D.
Identify duplicate rows in a table.
Identify which column has unique data.
Eliminate duplicate columns in a table.
Eliminate duplicate rows in the result
set.
[email protected]
《Oracle Assessment Tests》

You query the database with this SQL
statement:
SELECT name,NVL(salary,0)
FROM employee
WHRER salary IS NULL
ORDER BY name;
What is displayed for the SALARY column
when a NULL value is returned?
A. 0 B. NULL C.spaces D.nothing
[email protected]
《Oracle Assessment Tests》

For which task would you use the BETWEEN
operator?
A. Query the database for unknown values.
B. Query the database for a range of values.
C. Query the database for a character pattern.
D. Query the database for values in a specified
list.
(Answers: C D A B)
[email protected]
Oracle Quiz
《DML,DCL 测 试》
Oracle
Assessment Tests
[email protected]
《Oracle Assessment Tests》

What happens when you update a tables
without a WHERE clause?
A.
B.
C.
D.
The statement will not execute.
Only the rows specified will be updated.
All of the rows in the table will be updated.
The statement will execute,but no change
will be made.
[email protected]
《Oracle Assessment Tests》
 Which
commands cause a
transaction to end?
A.
B.
C.
D.
E.
F.
ALTER
GRANT.
DELETE.
INSERT
UPDATE.
ROLLBACK
[email protected]
《Oracle Assessment Tests》
The Correct Answers
1.C
2.A B F
[email protected]
Oracle Quiz
《DDL 测 试》
Oracle
Assessment Tests
[email protected]
《Oracle Assessment Tests》

You query the database with this command:
SELECT object_name
FROM user_objects
WHERE object_type =‘TABLE’;
Which values are displayed?
A.
B.
C.
D.
names of all objects you own
only the names of tables you own
names of all objects you have privileges to
only the names of tables you have
privileges to access.
[email protected]
《Oracle Assessment Tests》

You query the database with this command:
SELECT object_name
FROM all_objects
WHERE object_type =‘TABLE’;
Which values are displayed?
A. only names of table you own
B. only names of objects you own
C. Only names of all objects you have
privileges to access.
D. only the names of all the tables you can
access.
[email protected]
《Oracle Assessment Tests》

You attempt to create the ALPHA_3000 table
with this statement:
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE alpha_3000
(3000_id
number(9)
CONSTRAINT alpha_3000_id_pk PRIMARY KEY,
name VARCHAR2(25),
title VARCHAR2(25),
Idname VARCHAR2(25)
CONSTRAINT alpha_3000_id_nn NOT NULL);
What line in the statement will cause an error?
A. 1 B. 2 C.3 D.7
[email protected]
《Oracle Assessment Tests》

You alter the database with this command:
ALTER TABLE employee
ADD (end_date DATE);
Which task is accomplished ?
A. A constraint is added to an existing column.
B. A constraint is modified on an existing column.
C. A new column with a constraint is added to the table.
D. A new column with no constraint is added to the table.
[email protected]
《Oracle Assessment Tests》
 You
attempt to create a view with this
command:
CREATE VIEW last_first_vu AS
SELECT first_name||’ ’|| last_name“Employee Names”
FROM employee
ORDER BY last_name,first_name;
Which clause causes an error?
A. FROM employee.
B. ORDER BY last_name,first_name.
C. CREATE VIEW last_first_vu
D. SELECT first_name||’ ’||last_name”Employee Names”
[email protected]
《Oracle Assessment Tests》

You alter the database with this command:
RENAME streets to CITY;
Which task is accomplished ?
A. The streets user is renamed city.
B. The STREETS table is renamed CITY.
C. The STREETS column is renamed CITY.
D. The streets constraints is renamed city.
[email protected]
《Oracle Assessment Tests》
The Correct Answers
B,D,B,D,B,B
[email protected]
使用 SQL*PLUS命令
SQL*PLUS是Oracle对SQL语言的扩充,是一中交互式的表
报生成工具.它使用SQL命令从Oracle中查询信息,用
SQL*PLUS命令设置控制表报的输出格式,对SQL命令加以
扩充,可以对题目,列标题,以及汇总信息进行控制.
定义表头与表尾
定义表头: SQL>Ttitle <option> 描述
定义表尾: SQL>Btitle <option> 描述
option: Left 左对齐
Center 居中
Right 右对齐
使用TITLE 则报表输出在每页加上日期及页号
[email protected]
SQL*PLUS命令总结
[email protected]
聚组函数的使用
聚组函数是从一组中返回汇总信息，聚组函数有SUM,COUNT
COUNT DISTINCT,MAX,MIN,AVG,STDDEV

计算max,min,avg,sum
a.SQL>SELECT MIN(sal),MAX(sal),AVG(sal),
SUM(sal) FROM emp;
b.找出具有最高工资的员工？
SQL>SELECT ename,job,sal FROM emp
WHERE sal=(SELECT MAX(sal) FROM emp);

COUNT与COUNT DISTINCT
a.求emp表中总行数？ SQL>SELECT COUNT(*) FROM emp;
b.求job的行数？
SQL>SELECT COUNT(job) FROM emp;
c.求有多少工中？SQL>SELECT COUNT(DISTINCT job) FROM emp;
[email protected]

SELECT 字句中目标的一致性
不能把单行函数与聚组函数混在一起使用，如
SQL>SELECT ename,SUM(sal) FROM emp;
单行目标

使用GROUP BY 分组
聚组函数
句子语法错误!
a.查询从事每一个工种的员工的工资总和？
SQL>SELECT job,SUM(sal) FROM emp
GROUP BY job;
b.求出从事每一个工种的员工总数？
SQL>SELECT job,COUNT(*) FROM emp
GROUP BY job;
c.查询每个部门的最高工资及工资总和？
[email protected]
SQL>SELECT deptno,SUM(sal),MAX(sal)
FROM emp
GROUP by deptno;

按多个条件分组
SQL>SELECT deptno,job,COUNT(*)
FROM emp
GROUP BY deptno,job;

使用Having,选则满足条件的组
SQL>SELECT deptno,SUM(sal) FROM emp
GROUP BY deptno
HAVING SUM(SAL)>8000;
[email protected]
查询语句语法总结
SQL>select 列1,列2,…
from 基表
where 条件表达式
group by 分组列,分组列２,…
having 分组条件表达式
order by 排序列1,排序列2,… ;
求,除去秘书CLERK职业,那些部门工资总和超过$3000?
SQL>select deptno,sum(Sal)
from emp
where job !=‘CLERK’
group by deptno
having sum(sal)>3000 order by sum(sal);
[email protected]