下面的查询中哪一个会产生笛卡尔集（）A、SELECT e.empno，e.ename， e.deptno， d.deptno，d.loc  FROM  emp e，dept  d WHERE e.deptno=d.deptnoB、SELECT e.empno， e.ename， e.deptno， d.deptno，d.loc FROM emp  e，dept  dC、SELECT e.empno， e.ename， e.deptno， d.deptno，d.loc FROM emp e，dept d W

第1题：

第2题：

第3题：

答案：A

解析：

本题考查关系数据库中关系模式和SQL查询基础知识。作为主键，其值能唯一地标识元组的一个或多个属性，主键通常也称为主码。所谓外键是指如果关系模式R中的属性或属性组非该关系的码，但它是其他关系的码，那么该属性集对关系模式R而言是外键，通常也称外码。根据题意分析，员工关系中的主键是员工号，部门关系中的主键是部门号。显然，员工关系中的外键是部门号。但是，部门关系中的外键是负责人代码，为什么？因为题中说明部门负责人也是一个员工，这样负责人代码的取值域为员工号，所以根据外键定义部门关系中的外键是负责人代码。正确查询每个部门中月薪资最高的员工号、姓名、部门名和月薪资的SQL査询语句如下：

第4题：

关于HQL的聚合函数使用，说法正确的是（）。

• A、select count(*) from Dept d用于统计部门个数
• B、select sum(e.salary) from Emp e用于汇总员工工资总额
• C、select max(e.hiredate) from Emp e用于找到最新入职的员工的入职时间
• D、select min(e.hiredate) from Emp e用于找到最早入职的员工的入职时间

第5题：

Which of the following statements eliminates all but one of each set of duplicate rows in the DEPT column in the STAFF table?（）

• A、SELECT UNIQUE dept FROM staff
• B、SELECT DISTINCT dept FROM staff
• C、SELECT (dept) UNIQUE FROM staff
• D、SELECT (dept) DISTINCT FROM staff

第6题：

A number of applications issue the following SQL statement:SELECT d.deptno, e.empno, e.salary FROM department d INNER JOIN employee e ON d.deptno = e.deptnoA database administrator wishes to store this query within the database. Which of the following database objects can be used to accomplish this?（）

• A、Alias
• B、View
• C、Schema
• D、Trigger

第7题：

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) Which statement produces the number of different departments that have employees with last name Smith?（）

• A、SELECT COUNT(*) FROM employees WHERE last_name='Smith';
• B、SELECT COUNT(dept_id) FROM employees WHERE last_name='Smith';
• C、SELECT DISTINCT(COUNT(dept_id)) FROM employees WHERE last_name='Smith';
• D、SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';
• E、SELECT UNIQUE(dept_id) FROM employees WHERE last_name='Smith';

第8题：

查询出所有名字以’S’开始的员工（）

• A、select * from emp where ename in 'S%';
• B、select * from emp where ename='S%';
• C、select * from emp where ename like 'S%';
• D、select * from emp where ename like 'S_';

第9题：

Examine the structure of the EMP_DEPT_VU view: Column Name Type Remarks EMPLOYEE_ID NUMBER From the EMPLOYEES table EMP_NAME VARCHAR2(30) From the EMPLOYEES table JOB_ID VARCHAR2(20) From the EMPLOYEES table SALARY NUMBER From the EMPLOYEES table DEPARTMENT_ID NUMBER From the DEPARTMENTS table DEPT_NAME VARCHAR2(30) From the DEPARTMENTS table Which SQL statement produces an error?（）

• A、SELECT * FROM emp_dept_vu;
• B、SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department _ id;
• C、SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department _ id, job_id;
• D、SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM (salary) > 20000
• E、None of the statements produce an error; all are valid.

第10题：

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?（）

• A、DROP emp_dept_uv;
• B、DELETE emp_dept_uv;
• C、REMOVE emp_dept_uv;
• D、DROP VIEW emp_dept_uv;
• E、DELETE VIEW emp_dept_uv;
• F、REMOVE VIEW emp_dept_uv;

第11题：

列出EMP表中，从事每个工种（JOB）的员工人数（）

• A、select job from emp;
• B、select job,count(*) from emp;
• C、select distinct job,count(*) from emp;
• D、select job,count(*) from emp group by job;
• E、select job,sum(empno) from emp group by job;

第12题：

第13题：

第14题：

第15题：

使用HQL查询所有部门信息，以下正确的是（）。

• A、from Dept
• B、select*from cn.jbit.demo.entity.Dept
• C、select Dept from cn.jbit.demo.entity.Dept d
• D、select d from Dept d

第16题：

查询出EMP表中1982年及以后入职的员工信息（）（注：字段hiredate为入职日期，数据类型为DATE型）

• A、select * from emp where hiredate>='19820101';
• B、select * from emp where hiredate>=to_char('19820101','YYYYMMDD');
• C、select * from emp where to_date(hiredate,'YYYYMMDD')>='19820101';
• D、select * from emp where to_char(hiredate,'YYYYMMDD')>='19820101';

第17题：

语句SELECT * FROM dept WHERE NOT EXISTS （SELECT * FROM emp WHERE deptno=dept.deptno）执行后的结果为（）

• A、只显示存在于EMP表中的部门全部信息
• B、只显示不存在于EMP表中的部门全部信息
• C、未返回任何数据
• D、显示DEPT表中的全部信息

第18题：

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHAR2(30) SALARY NUMBER(8,2) Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only of the minimum salary is less then 5000 and the maximum salary is more than 15000?（）

• A、SELECT dept_id, MIN(salary(, MAX(salary) FROM employees WHERE MIN(salary) <5000 AND MAX (salary) > 15000;
• B、SELECT dept_id, MIN(salary), MAX(salary) FROM employees WHERE MIN(salary) < 5000 AND MAX(salary) > 15000 GROUP BY dept_id;
• C、SELECT dept_id, MIN(salary), MAX(salary) FROM employees HAVING MIN(salary) <5000 AND MAX (salary) > 15000;
• D、SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id HAVING MIN (salary) < 5000 AND MAX(salary)
• E、SELECT dept_id, MIN(salary), MAX(salary) FROM employees GROUP BY dept_id, salary HAVING MIN (salary) <5000 AND MAX (salary) > 15000;

第19题：

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables. How can you accomplish this task?（）

• A、ALTER VIEW EMP_dept_vu (ADD manger_id NUMBER);
• B、MODIFY VIEW EMP_dept_vu (ADD manger_id NUMBER);
• C、ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department _ id = d.department_id;
• D、MODIFY VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department _ id = d.department_id;
• E、CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department _ id = d.department_id;
• F、You must remove the existing view first, and then run the CREATE VIEW command with a new column list to modify a view.

第20题：

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) JOB_CAT VARCHARD2(30) SALARY NUMBER(8,2) Which statement shows the maximum salary paid in each job category of each department?（）

• A、SELECT dept_id, job_cat, MAX(salary) FROM employees WHERE salary > MAX (salary);
• B、SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id,job_cat;
• C、SELECT dept_id, job_cat, MAX(salary) FROM employees;
• D、SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept_id;
• E、SELECT dept_id, job_cat, MAX(salary) FROM employees GROUP BY dept _ id job _ cat salary;

第21题：

限制从EMP表中只选出前5条记录的查语句为（）。

• A、SELECT TOP 5 * FROM EMP
• B、SELECT DISTINCT 5 * FROM EMP
• C、SELECT * FROM EMP WHERE ROWNUM<6
• D、SELECT * FROM EMP WHERE ROWNUM=5

第22题：

Examine the structure of the EMP_DEPT_VU view: Column Name Type Remarks EMPLOYEE_ID NUMBER From the EMPLOYEES table EMP_NAME VARCHAR2(30) From the EMPLOYEES table JOB_ID VARCHAR2(20) From the EMPLOYEES table SALARY NUMBER From the EMPLOYEES table DEPARTMENT_ID NUMBER From the DEPARTMENTS table DEPT_NAME VARCHAR2(30) From the DEPARTMENTS table Which SQL statement produces an error?（）

• A、SELECT * FROM emp_dept_vu;
• B、SELECT department_id, SUM(salary) FROM emp_dept_vu GROUP BY department _ id;
• C、SELECT department_id, job_id, AVG(salary) FROM emp_dept_vu GROUP BY department _ id, job_id;
• D、SELECT job_id, SUM(salary) FROM emp_dept_vu WHERE department_id IN (10,20) GROUP BY job_id HAVING SUM (salary) > 20000
• E、None of the statements produce an error; all are valid.

第23题：