通过创建“雇员”表和“部门”表的视图来显示每个部门的薪金信息。 执行以下语句时会发生什么情况() CREATE OR REPLACE VIEW sal_dept AS SELECT SUM(e.salary) sal, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name ORDER BY d.department_name.A

题目

通过创建“雇员”表和“部门”表的视图来显示每个部门的薪金信息。 执行以下语句时会发生什么情况() CREATE OR REPLACE VIEW sal_dept AS SELECT SUM(e.salary) sal, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name ORDER BY d.department_name.

  • A、创建一个复杂视图,返回按部门名称排序的每个部门的薪金总和
  • B、创建一个简单视图,返回按部门名称排序的每个部门的薪金总和
  • C、创建一个复杂视图,返回按部门标识排序的每个部门的薪金总和
  • D、不创建任何视图,因为该语句包含错误,该语句将失败

相似考题
参考答案和解析
正确答案:C
更多“通过创建“雇员”表和“部门”表的视图来显示每个部门的薪金信息。 执行以下语句时会发生什么情况() CREATE OR REPLACE VIEW sal_dept AS SELECT SUM(e.salary) sal, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name ORDER BY d.department_name.A”相关问题
  • 第1题:

    检索每个部门的职工工资的总和,要求显示“部门名称”和“基本工资”.正确的语句是( )。

    A.SELECT部门名称,SUM(基本工资)FR0M部门, 职工 WHERE职工.部门号=部门.部门号 ORDER BY部门号

    B.SELECT部门名称,SUM(基本工资)FROM部门, 职1:WHERE职工.部门号=部门.部门号 GROUP BY部门号

    C.SELECT部门名称,SUM(基本工资)FROM部门,职工 WHERE职工部门号=部门.部门号 0RDRE BY职工部门号

    D.SELECT部门名称,SUM(基本工资)FROM部门, 职工 WHERE职工.部门号=部门.部门号 GROUP BY职工.部门号


    正确答案:D
    D。【解析】进行SQL简单计算查询时,还可以加上GROUPBY子句进行分组计算查询。通常来说,一个计算函数的范围是满足WHERE子句指定条件的所有记录。当加上GRUPBY子句后,系统会将查询结果按指定列分成集合组。当一个计算函数和一个GROUPBY子句一起使用时,计算函数的范围变为每组所有的记录。本题通过“GROUPBY职工.部门号”可以先将记录按“部门号”分组,然后再通过函数“SUM(基本工资)”对每组记录求和;另外,在指定分组条件时,因为两个表中都存在相同的字段,即“部门号”,因此要指定分组的字段来自哪个数据表,否则系统出现“部门号不唯一”的错误提示;0RDERBY是对查询结果进行排序的短语,没有分组的功能。

  • 第2题:

    评估以下SELECT语句:SELECT雇员标识,姓氏,名字,薪金‘年薪’ FROM雇员 WHERE薪金ISNOTNULL ORDERBY姓氏,3;以下哪个子句有错误?()

    • A、SELECT雇员标识,姓氏,名字,薪金’年薪’
    • B、FROM雇员
    • C、WHERE薪金IS NOT NULL
    • D、ORDER BY姓氏,3;

    正确答案:A

  • 第3题:

    Evaluate this SQL statement: SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed?()

    • A、Selection, projection, join
    • B、Difference, projection, join
    • C、Selection, intersection, join
    • D、Intersection, projection, join
    • E、Difference, projection, product

    正确答案:A

  • 第4题:

    “雇员”表包含以下列: 雇员标识NUMBER 姓氏VARCHAR2(25) 名字VARCHAR2(25) 部门标识NUMBER 职务标识NUMBER 经理标识NUMBER 薪金NUMBER(9,2) 佣金NUMBER(7,2) 聘用日期DATE 以下哪条SELECT语句可用于显示部门70中薪金最低的10位员工()

    • A、SELECT ROWNUM"排名",姓氏,名字"雇员",薪金"薪金"FROM(SELECT 姓氏,名字,薪金 FROM 雇员ORDER BY 薪金)WHERE ROWNUM<=10 AND job_id LIKE’CLERK’AND department_id=70
    • B、SELECT ROWNUM"排名",姓氏,名字"雇员",薪金"薪金"FROM(SELECT 姓氏,名字,薪金,职务标识FROM 雇员 WHERE 职务标识 LIKE’CLERK’AND 部门标识=70ORDER BY 薪金)WHERE ROWNUM<=10
    • C、SELECT ROWNUM"排名",姓氏,名字"雇员",薪金"薪金"FROM(SELECT 姓氏,名字,薪金,职务标识,部门标识 FROM 雇员 WHERE ROWNUM<=10ORDER BY 薪金)WHERE 职务标识 LIKE’CLERK’AND 部门标识=70
    • D、唯一的方法是使用数据字典

    正确答案:B

  • 第5题:

    有关系模式:学生表(学号,姓名,所在系),建立统计每个系的学生人数的视图的正确语句是()

    • A、CREATE VIEW v1AS SELECT所在系,COUNT(*)FROM学生表GROUP BY所在系
    • B、CREATE VIEW v1AS SELECT所在系,SUM(*)FROM学生表GROUP BY所在系
    • C、CREATE VIEW v1(系名,人数)AS SELECT所在系,SUM(*)FROM学生表GROUP BY所在系
    • D、CREATE VIEW v1(系名,人数)AS ELECT所在系,COUNT(*)FROM学生表GROUP BY所在系

    正确答案:D

  • 第6题:

    Click the Exhibit button and examine the data in the EMPLOYEES and DEPARTMENTS tables.You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?()

    • A、SELECT last_name, department_name FROM employees NATURAL JOIN departments;
    • B、SELECT last_name, department_name FROM employees JOIN departments ;
    • C、SELECT last_name, department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id);
    • D、SELECT last_name, department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);
    • E、SELECT last_name, department_name FROM employees FULL JOIN departments ON (e.department_id = d.department_id);
    • F、SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

    正确答案:F

  • 第7题:

    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.

    正确答案:E

  • 第8题:

    Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES EMPLOYEE_ID NUMBER DEPARTMENT_ID NUMBER MANAGER_ID NUMBER LAST_NAME VARCHAR2(25) DEPARTMENTS DEPARTMENT_ID NUMBER MANAGER_ID NUMBER DEPARTMENT_NAME VARCHAR2(35) LOCATION_ID NUMBER You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?()

    • A、SELECT last_name, department_name, location_id FROM employees , department ;
    • B、SELECT employees.last_name, departments.department_name, departments.location_id FROM employees e, departments D WHERE e.department_id = d.department_id;
    • C、SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id = manager_id;
    • D、SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id = d.department_id;

    正确答案:D

  • 第9题:

    单选题
    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 manager_id NUMBER);

    B

    MODIFY VIEW emp_dept_vu (ADD manager_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.


    正确答案: A
    解析: 暂无解析

  • 第10题:

    单选题
    “雇员”表包含以下列: 雇员标识NUMBER 姓氏VARCHAR2(25) 名字VARCHAR2(25) 部门标识NUMBER 职务标识NUMBER 经理标识NUMBER 薪金NUMBER(9,2) 佣金NUMBER(7,2) 聘用日期DATE 以下哪条SELECT语句可用于显示部门70中薪金最低的10位员工()
    A

    SELECT ROWNUM排名,姓氏,名字雇员,薪金薪金FROM(SELECT 姓氏,名字,薪金 FROM 雇员ORDER BY 薪金)WHERE ROWNUM<=10 AND job_id LIKE’CLERK’AND department_id=70

    B

    SELECT ROWNUM排名,姓氏,名字雇员,薪金薪金FROM(SELECT 姓氏,名字,薪金,职务标识FROM 雇员 WHERE 职务标识 LIKE’CLERK’AND 部门标识=70ORDER BY 薪金)WHERE ROWNUM<=10

    C

    SELECT ROWNUM排名,姓氏,名字雇员,薪金薪金FROM(SELECT 姓氏,名字,薪金,职务标识,部门标识 FROM 雇员 WHERE ROWNUM<=10ORDER BY 薪金)WHERE 职务标识 LIKE’CLERK’AND 部门标识=70

    D

    唯一的方法是使用数据字典


    正确答案: B
    解析: 暂无解析

  • 第11题:

    单选题
    通过创建“雇员”表和“部门”表的视图来显示每个部门的薪金信息。 执行以下语句时会发生什么情况() CREATE OR REPLACE VIEW sal_dept AS SELECT SUM(e.salary) sal, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY d.department_name ORDER BY d.department_name.
    A

    创建一个复杂视图,返回按部门名称排序的每个部门的薪金总和

    B

    创建一个简单视图,返回按部门名称排序的每个部门的薪金总和

    C

    创建一个复杂视图,返回按部门标识排序的每个部门的薪金总和

    D

    不创建任何视图,因为该语句包含错误,该语句将失败


    正确答案: B
    解析: 暂无解析

  • 第12题:

    单选题
    Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES EMPLOYEE_ID NUMBER DEPARTMENT_ID NUMBER MANAGER_ID NUMBER LAST_NAME VARCHAR2(25) DEPARTMENTS DEPARTMENT_ID NUMBER MANAGER_ID NUMBER DEPARTMENT_NAME VARCHAR2(35) LOCATION_ID NUMBER You want to create a report displaying employee last names, department names, and locations. Which query should you use?()
    A

    SELECT e.last_name, d. department_name, d.location_id FROM employees e NATURAL JOIN departments D USING department_id ;

    B

    SELECT last_name, department_name, location_id FROM employees NATURAL JOIN departments WHERE e.department_id =d.department_id;

    C

    SELECT e.last_name, d.department_name, d.location_id FROM employees e NATURAL JOIN departments d;

    D

    SELECT e.last_name, d.department_name, d.location_id FROM employees e JOIN departments d USING (department_id );


    正确答案: D
    解析: 暂无解析

  • 第13题:

    评估以下语句:SELECT 部门标识,AVG(薪金) FROM 雇员 WHERE 职务标识<>69879 GROUP BY职务标识,部门标识 HAVINGAVG(薪金)>35000 ORDERBY 部门标识;哪些子句用于限定结果?()

    • A、SELECT部门标识,AVG(薪金)
    • B、WHERE职务标识<>69879
    • C、GROUP BY职务标识,部门标识
    • D、HAVING AVG(薪金)>35000

    正确答案:B,D

  • 第14题:

    评估以下 SELECT 语句:  SELECT 雇员标识,姓氏,名字,薪金,年薪’   FROM 雇员   WHERE 薪金 IS NOT NULL   ORDER BY 姓氏,3; 以下哪个子句有错误?()

    • A、SELECT 雇员标识,姓氏,名字,薪金’年薪’
    • B、FROM 雇员
    • C、WHERE 薪金IS NOT NULL
    • D、ORDER BY姓氏,3;

    正确答案:A

  • 第15题:

    用户SUSAN创建了“雇员”表,然后创建了视图“雇员视图”,此视图只显示雇员的“名字”和“姓氏”列。用户RUDI需要能访问“雇员”表中的“雇员”姓名但不能访问其它数据。SUSAN应执行以下哪条语句才允许此操作()

    • A、SELECT*FROM雇员视图FOR rudi
    • B、CREATE SYNONYM雇员视图FOR雇员
    • C、GRANT SELECTON雇员视图TO rudi
    • D、GRANT SELECTON雇员视图ONLY TO rudi

    正确答案:C

  • 第16题:

    用户 SUSAN 创建了一个“雇员”表,然后又创建了一个“雇员_视图”视图,仅显示“雇员”表的“名字”列和“姓氏”列。用户 RUDI 需要能访问雇员的姓名,但不能访问“雇员”表中的其他数据。SUSAN 应使用哪条语句来实现这一点?()

    • A、SELECT*FROM雇员_视图FOR rudi;
    • B、CREATE SYNONYM雇员_视图FOR雇员;
    • C、GRANT SELECT ON雇员_视图TO rudi;
    • D、GRANT SELECT ON雇员_视图ONLY TO rudi;

    正确答案:C

  • 第17题:

    You need to create a view EMP_VU. The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20.Which SQL statement would you use to create the view EMP_VU?()

    • A、CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20);
    • B、CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH READ ONLY;
    • C、CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH CHECK OPTION;
    • D、CREATE FORCE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20);
    • E、CREATE FORCE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) NO UPDATE;

    正确答案:C

  • 第18题:

    Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES EMPLOYEE_ID NUMBER DEPARTMENT_ID NUMBER MANAGER_ID NUMBER LAST_NAME VARCHAR2(25) DEPARTMENTS DEPARTMENT_ID NUMBER MANAGER_ID NUMBER DEPARTMENT_NAME VARCHAR2(35) LOCATION_ID NUMBER You want to create a report displaying employee last names, department names, and locations. Which query should you use?()

    • A、SELECT e.last_name, d. department_name, d.location_id FROM employees e NATURAL JOIN departments D USING department_id ;
    • B、SELECT last_name, department_name, location_id FROM employees NATURAL JOIN departments WHERE e.department_id =d.department_id;
    • C、SELECT e.last_name, d.department_name, d.location_id FROM employees e NATURAL JOIN departments d;
    • D、SELECT e.last_name, d.department_name, d.location_id FROM employees e JOIN departments d USING (department_id );

    正确答案:D

  • 第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 manager_id NUMBER);
    • B、MODIFY VIEW emp_dept_vu (ADD manager_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.

    正确答案:E

  • 第20题:

    You need to create a view EMP_VU. The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20. Which SQL statement would you use to create the view EMP_VU? ()

    • A、CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department _ id IN (10,20);
    • B、CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH READ ONLY;
    • C、CREATE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) WITH CHECK OPTION;
    • D、CREATE FORCE VIEW emp_vu AS SELECT * FROM employees WITH department_id IN (10,20);
    • E、CREATE FORCE VIEW emp_vu AS SELECT * FROM employees WHERE department_id IN (10,20) NO UPDATE;

    正确答案:C

  • 第21题:

    单选题
    Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES EMPLOYEE_ID NUMBER DEPARTMENT_ID NUMBER MANAGER_ID NUMBER LAST_NAME VARCHAR2(25) DEPARTMENTS DEPARTMENT_ID NUMBER MANAGER_ID NUMBER DEPARTMENT_NAME VARCHAR2(35) LOCATION_ID NUMBER You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join?()
    A

    SELECT last_name, department_name, location_id FROM employees , department ;

    B

    SELECT employees.last_name, departments.department_name, departments.location_id FROM employees e, departments D WHERE e.department_id = d.department_id;

    C

    SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id = manager_id;

    D

    SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id = d.department_id;


    正确答案: D
    解析: 暂无解析

  • 第22题:

    单选题
    评估此CREATE VIEW语句: CREATE VIEW emp_view AS SELECT SUM(salary) FROM employees; 以下哪个说法是的()
    A

    使用“雇员视图”视图不能更新“雇员”表中的数据

    B

    使用“雇员视图”视图可以更新“雇员”表中的任何数据

    C

    使用“雇员视图”视图不能删除“雇员”表中的记录

    D

    使用“雇员视图”视图仅能更新“雇员”表中的“薪金”列


    正确答案: A
    解析: 暂无解析

  • 第23题:

    单选题
    有关系模式:学生表(学号,姓名,所在系),建立统计每个系的学生人数的视图的正确语句是()
    A

    CREATE VIEW v1AS SELECT所在系,COUNT(*)FROM学生表GROUP BY所在系

    B

    CREATE VIEW v1AS SELECT所在系,SUM(*)FROM学生表GROUP BY所在系

    C

    CREATE VIEW v1(系名,人数)AS SELECT所在系,SUM(*)FROM学生表GROUP BY所在系

    D

    CREATE VIEW v1(系名,人数)AS ELECT所在系,COUNT(*)FROM学生表GROUP BY所在系


    正确答案: B
    解析: 暂无解析

  • 第24题:

    ( 难度:中等)以下哪些Mysql语句是正确的?
    A.select * from emp; 
    B.select ename,hiredate,sal from emp;  
    C.select * from emp order deptno;  
    D.select * from where deptno=1 and sal<300;
    E.select * from where deptno=1 group by sal;

    答案:ABD