EMP表
EMP表
列名称
定义
列名称
定义
Empno
NUMBER(4),PK
Deptno
NUMBER(2)
Ename
VARCHAr2(10)
Dname
VARCHAr2(14)
Job
VARCHAr2(9)
Loc
VARCHAr2(13)
Mgr
NUMBER(4)
Hiredate
DATE
Sal
NUMBER(7,2)
Comm
NUMBER(7,2)
Deptno
NUMBER(2),FK
- Empno—-Employee
- Ename—-Employee Name
- Job—-Job Title
- Mgr—-Manager of Employee
- Hiredate—-Hire date
- Sal—-Salary(每月)
- Comm—-Commission(销售)
- Deptno—-Department Number
- Dname—-Department Name
- Loc—-Location of Operation
1.下面哪两个查询可以用来确定员工Blake所在部门的名称?
a.SELECt dname FROM dept WHERe ename =(‘BLAKE’)；
b.SELECt d. dname FROM dept d NATURAL JOIN emp e
WHERe e.ename =‘BLAKE’；
c.SELECt dname FROM dept d,emp e
WHERe e.ename =（‘BLAKE’）
AND d.deptno = e.deptno；
d.SELECt dname FROM dept JOIN emp USING (dname)
WHERe ename =‘BLAKE’；
e.SELECt dname FROM dept JOIN emp
ON dept.deptno = emp.deptno
WHERe ename =‘BLAKE’；
2.如果Sal包含各个员工的月薪,那么下面哪一个查询将显示EMP表中的各个员工的年薪?
a.SELECt sal * 12 ‘Annual Salary’FROM emp;
b.SELECt salary * 12 ‘annual FROM emp;
c.SELECt annual sal * 12 FROM emp;
d.SELECt sal * 12 FROM emp;
3.下面哪一个查询将显示存储在EMP表中的所有数据?
a.SELECt * FROM emp;
b.SELECt % FROM emp;
c.SELECt ^ FROM emp;
d.SELECt _ FROM emp;
4.在执行下面的查询时,下面哪能一种说法反映了将会发生的情况?
SELECt ename
FROM emp e,emp m
WHERe e.mgr = m.empno;
a.这个查询将产生一个自我联接,显示各个员工的经理的姓名
b.将显示一个多义性错误,并且不会执行这个语句
c.这个查询将执行一个完全外部联接,并显示没有为其指定经理的员工的姓名
d.这个查询将执行一个右外部联接,显示不是经理的员工的姓名
5.下面哪些关键字可以用来创建一个不等连接?选出所有正确的选项.
a.NATURAL JOIN
b.JOIN… USING
c.OUTER JOIN
d.JOIN… ON
e.以上都不可以—-使用任何JOIN关键字都不能创建不等连接
6.下面哪一个查询只返回包含DEPT表中而没有在EMP表中列出的部门号?
a.SELECt deptno FROM dept NATURAL JOIN emp
WHERe deptno NOT IN emp;
b.SELECt deptno FROM dept MINUS deptno FROM emp;
c.SELECt deptno FROM dept MINUS
SELECt deptno FROM emp;
d.SELECt deptno FROM dept JOIN emp
ON dept.deptno<>emp.deptno;
e.SELECt deptno FROM emp MINUS
SELECt deptno FROM dept;
7.下面哪一个查询将显示名为King的员工的工号?
a.SELECt empno FROM emp WHERe ename = ‘% KING’;
b.SELECt empno FROM emp WHERe ename = ‘_ ING’;
c.SELECt empno FROM emp WHERe ename LIKE KING;
d.SELECt empno FROM emp WHERe ename = KING;
e.有上查询都不对
8.下面哪一个查询将显示销售部在1981年聘用的所有员工?
a.SELECt * FROM emp
WHERe dname =’SALES’AND hiredate LIKE’%81′;
b.SELECt * FROM emp NATURAL JOIN dept
WHERe dname =’SALES’AND hiredate LIKE’%81′;
c.SELECt * FROM emp
WHERe dname =’SALES’OR hiredate LIKE’%81′;
d.SELECt * FROM emp NATURAL JOIN dept
WHERe dname =’SALES’AND hiredate LIKE’%1981′;
9.下面哪一个查询将显示存储在EMP表中的各个员工的姓名和职务?选择所有正确的查询.
a.SELECt ename,job AS”Job Title”FROM emp;
b.SELECt ename,job “Job Title”FROM emp;
c.SELECt ename,job FROM emp;
d.SELECt ename,job ‘Job TITLE’FROM emp;
e.SELECt ename,job ‘Job Title’FROM emp;
10.下面哪一种说法最准确地说明了下面的SELECt语句的结果?
SELECT DISTINCT job,ename FROM emp;
a.结果中返回的每一行都是惟一的
b.各个职务在结果中只显示一次
c.各个职务将只显示一次,并随之显示具有该职务的各个员工的姓名
d.结果将按照员工姓名排序
11.下面哪一个子句用来选择表中的某些列?
a.SELECt
b.FROM
c.WHERe
d.ORDER BY
12.下面哪一个查询将显示年薪至少为10000美元的所有员工的姓名?
a.SELECt ename FROM emp WHERe sal*12>10,000;
b.SELECt ename FROM emp WHERe sal*12>’10,000′;
c.SELECt ename FROM emp WHERe sal*12=>10000;
d.SELECt ename FROM emp WHERe sal*12>=10000.00;
e.上面的语句都不正确
13.下面哪些查询将按照员工姓名的顺序显示各个员工的号码?选出所有正确的查询.
a.SELECt ename,ename FROM emp ORDER BY empno;
b.SELECt ename,ename FROM emp ORDER BY ename;
c.SELECt ename,ename FROM emp ORDER BY 1;
d.SELECt ename,ename FROM emp ORDER BY 2;
e.SELECt ename,ename ORDER BY ename;
14.下面哪一个查询将显示月薪至少为1200美元但是少于2000美元的各个员工的姓名?
a.SELECT ename FROM emp
WHERe sal BETWEEN(1200,2000);
b.SELECt ename FROM emp
WHERe sal BETWEEN 1200 and 2000;
c.SELECt ename FROM emp
WHERe sal>=1200 AND <2000;
d.SELECt ename FROM emp
WHERe sal>=1200 AND sal <2000;
e.SELECt ename FROM emp
WHERe sal>1200 AND sal <2000;
15.下面哪一个子句用来限制查询返回的行?
a.SELECt
b.FROM
c.WHERe
d.ORDER BY
16.下面哪一个运算符用来执行模式搜索?
a.IN
b.BETWEEN
c.IS NULL
d.LIKE
17.下面哪些查询在结果中不包括部门30中的任何员工?选出所有正确的答案.
a.SELECt * FROM emp WHERe deptno! =30;
b.SELECt * FROM emp WHERe deptno<>30;
c.SELECt * FROM emp WHERe deptno^30;
d.SELECt * FROM emp WHERe deptno =30;
18.下面哪一个查询将显示没有佣金的所有员工?
a.SELECt ename FROM emp WHERe comm = NULL;
b.SELECt ename FROM emp WHERe comm IS NULL;
c.SELECt ename FROM emp WHERe comm LIKE NULL;
d.SELECt ename FROM emp WHERe comm LIKE ‘NULL’;
19.下面哪一个查询将返回在销售部或会计部工作并且月薪至少为2000美元的所有员工的姓名?选出所有正确的答案.
a.SELECt ename FROM emp NATURAL JOIN dept
WHERe dname IN(‘SALES’,’ACCOUNTING’)
AND sal>=2000;
b.SELECt ename FROM emp JOIN dept
ON emp.deptno = dept.deptno
WHERe sal >= 2000 AND dname = ‘SALES’
OR dname =’ACCOUNTING’;
c.SELECt ename FROM emp JOIN dept USING(deptno)
WHERe sal >= 2000 AND (dname = ‘SALES’OR dname =
‘ACCOUNTING’);
d.SELECt ename FROM emp NATURAL JOIN dept
WHERe sal >= 2000 AND (dname = ‘SALES’OR dname =
‘ACCOUNTING’);
20.下面哪一个子句用来按照某种顺序显示查询的结果?
a.SELECt
b.FROM
c.WHERe
d.ORDER BY
