Given the following DDL and INSERT statements: CREATE VIEW v1 AS SELECT col1 FROM t1 WHERE col1 > 10; CREATE VIEW v2 AS SELECT col1 FROM v1 WITH CASCADED CHECK OPTION; CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1 < 100; INSERT INTO v1 VALUES(5); INSE

题目

Given the following DDL and INSERT statements: CREATE VIEW v1 AS SELECT col1 FROM t1 WHERE col1 > 10; CREATE VIEW v2 AS SELECT col1 FROM v1 WITH CASCADED CHECK OPTION; CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1 < 100; INSERT INTO v1 VALUES(5); INSERT INTO v2 VALUES(5); INSERT INTO v3 VALUES(20); INSERT INTO v3 VALUES(100); How many of these INSERT statements will be successful?（）

  • A、0
  • B、1
  • C、2
  • D、3
相似考题

1.在SQL Server 2000中，有学生表(学号，姓名，年龄，所在系)，学号是主码。在这个表上建有视图V1,V1视图的定义语句为： CREATE VIEW V1 AS SELECT姓名，年龄，所在系FROM学生表 WHERE年龄＞=(SELECT AVG(年龄)FROM学生表) 有下列操作语句： Ⅰ.UPDATE V1 SET年龄=年龄+1 Ⅱ.SELECT*FROM V1 WHERE年龄＞20 Ⅲ.DELETE FROM V1 WHERE年龄＞20 Ⅳ.INSERT INTO V1 VALUES(，张三，20,‘计算机系’) 以上语句能正确执行的是A．仅Ⅰ和ⅡB．仅ⅡC．仅Ⅱ和ⅣD．仅Ⅰ、Ⅱ和Ⅲ

2.根据“歌手”表建立视图myview，视图中含有包括了“歌手号”左边第一位是“1”的所有记录，正确的SQL语句是 ______。A．CREATE VIEW myview AS SELECT*FROM歌手WHERE LEFT(歌手号，1)=“1”B．CREATE VIEW myview AS SELECT*FROM歌手WHERE LIKE(“1”，歌手号)C．CREATE VIEW myview SELECT*FROM歌手WHERE LEFT(歌手号，1)＝“1”D．CREATE VIEW myview SELECT*FROM歌手WHERE LIKE(“1”，歌手号)

3.设有关系模式：student(sno, sname, age, dept)，现利用命令： CREATE VIEW stu AS SELECT *FROM student WHERE dept="cs"WITH CHECK OPTION 创建了一个视图。则以下SQl命令中能正确执行的是A．UPDATE stu SET dept="maths"B．UPDATE stu SET dept="maths"WHERE age＞=20C．INSERT INTO stu VALUES ("2004001", "John", 30, "cs")D．INSERT INTO stu VALUES ("2004001", "John", 50, "maths")

4.(34）查询所有目前年龄是22岁的学生信息：学号，姓名和年龄，正确的命令组是A）CREATE VIEW AGE LIST AS; SELECT学号，姓名,YEAR(DATEQ)-YEAR(出生日期）年龄FROM学生； SELECT学号，姓名，年龄FROM AGE LIST WHERE年龄=22B）CREATE VIEW AGE LIST AS； SELECT学号，姓名，YEAR（出生日期）FROM学生；SELECT学号，姓名，年龄FROM AGE -L IST WHERE YEAR（出生日期）=22C）CREATE VIEW AGE LIST AS； SELECT学号，姓名,YEAR(DATEQ)-YEAR(出生日期）年龄FROM学生； SELECT学号，姓名，年龄FROM学生WHERE YEAR(出生日期)=22D）CREATE VIEW AGE LIST AS STUDENT； SELECT学号，姓名，YEAR(DATE（））-YEAR(出生日期）年龄FROM学生；SELECT学号，姓名，年龄FROM STUDENT WHERE年龄=22

更多“Given the following DDL and INSERT statements: CREATE VIEW v1 AS SELECT col1 FROM t1 WHERE col1 10; CREATE VIEW v2 AS SELECT col1 FROM v1 WITH CASCADED CHECK OPTION; CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1 100; INSERT INTO v1 VALUES(5); INSERT ”相关问题

  • 第1题：

    根据 “ 歌手 ” 表建立视图 myview, 视图中含有包括了 “ 歌手号 ” 左边第一位是 “ 1 ” 的所有记录，正确的 SQL 语句是

    A)CREATE VIEW myview AS SELECT * FROM 歌手 WHERE LEFT( 歌手号 ,1)="1"

    B)CREATE VIEW myview AS SELECT * FROM 歌手 WHERE LIKE("1" , 歌手号 )

    C)CREATE VIEW myview SELECT * FROM 歌手 WHERE LEFT( 歌手号 ,1)="1"

    D)CREATE VIEW myview SELECT * FROM 歌手 WHERE LIKE("1" , 歌手号 )


    正确答案：A

  • 第2题：

    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

  • 第3题：

    A view is created with the following statement:CREATE VIEW v1 AS SELECT col1, col2, col3 FROM t1 WHERE col4 > 1000 ; When will DB2 access the data from table T1 for view V1?()

    A.When view V1 is created

    B.Each time the REFRESH VIEW v1 statement is executed

    C.Each time an SQL statement is executed against view V1

    D.Only the first time an SQL statement is executed against view V1


    参考答案：C

  • 第4题：

    Given the following statements:CREATE TABLE tab1 (col1 INT); CREATE TABLE tab2 (col1 INT); CREATE TRIGGER trig1 AFTER UPDATE ON tab1 REFERENCING NEW AS new1 FOR EACH ROW MODE DB2SQL INSERT INTO tab2 VALUES(new1.col1); INSERT INTO tab1 VALUES(2),(3);What is the result of the following query? SELECT count(*) FROM tab2;（）

    A.3

    B.2

    C.1

    D.0


    参考答案：D

  • 第5题：

    根据“歌手”表建立视图myview，视图中含有“歌手号”左边第一位是"1”的所有记录，正确的SQL语句是（ ）。

    A)CREATE VIEW myview AS SELECT * FROM 歌手 WHERE LEFT(歌手号,1)="1"

    B)CREATE VIEW myview AS SELECT * FROM 歌手 WHERE LIKE("1",歌手号)

    C)CREATE VIEW myview SELECT * FROM 歌手 WHERE LEFT(歌手号,1)="1"

    D)CREATE VIEW myview SELECT * FROM 歌手 WHERE LIKE("1",歌手号)


    正确答案：A
    建立视图的SQL命令格式是：
    CREATE VIEW视图名AS SELECT语句，故可以排除选项C和选项D)，而“歌手号’左边第一位是‘1”’应该用LEFT函数实现。

  • 第6题：

    A view is created with the following statement:CREATE VIEW v1 AS SELECT col1, col2, col3, col4 FROM t1 WHERE col4 > 1000 WITH CHECK OPTIONWhat is the effect of the CHECK OPTION clause?（）

    • A、Any row inserted or updated through view V1 must meet the condition that col4 > 1000.
    • B、From now on, any row inserted or updated in table T1 must meet the condition that col4 > 1000, but existing rows in the table are not checked.
    • C、At view creation, DB2 will check the data in table T1, and if in any row doesn't meet the condition col4 > 1000, the view creation will be rejected.
    • D、Any row inserted or updated through view V1 must meet the condition that col4 > 1000 and no row in table T1 can be updated such that col4 <= 1000, but new rows in the table can be inserted with col4 <= 1000.

    正确答案:A

  • 第7题：

    有关系模式：学生表（学号，姓名，所在系），建立统计每个系的学生人数的视图的正确语句是（）

    • 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

  • 第8题：

    Given the following statements: CREATE TABLE tab1 (col1 INT); CREATE TABLE tab2 (col1 INT); CREATE TRIGGER trig1 AFTER UPDATE ON tab1 REFERENCING NEW AS new1 FOR EACH ROW MODE DB2SQL INSERT INTO tab2 VALUES(new1.col1); INSERT INTO tab1 VALUES(2),(3); What is the result of the following query? SELECT count(*) FROM tab2;（）

    • A、3
    • B、2
    • C、1
    • D、0

    正确答案:D

  • 第9题：

    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

  • 第10题：

    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

  • 第11题：

    单选题
    A view is created with the following statement:CREATE VIEW v1 AS SELECT col1, col2, col3, col4 FROM t1 WHERE col4 > 1000 WITH CHECK OPTIONWhat is the effect of the CHECK OPTION clause?（）
    A

    Any row inserted or updated through view V1 must meet the condition that col4 > 1000.

    B

    From now on, any row inserted or updated in table T1 must meet the condition that col4 > 1000, but existing rows in the table are not checked.

    C

    At view creation, DB2 will check the data in table T1, and if in any row doesn't meet the condition col4 > 1000, the view creation will be rejected.

    D

    Any row inserted or updated through view V1 must meet the condition that col4 > 1000 and no row in table T1 can be updated such that col4 <= 1000, but new rows in the table can be inserted with col4 <= 1000.


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

  • 第12题：

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


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

  • 第13题：

    根据SQL标准，创建一个视图abc，通过该视图只能对表student中系dept为‘IS’的记录进行更新操作。下面哪条语句适用?()

    A create view abc as select * from student where dept=’IS’

    B create view abc as select * from student where dept=’IS’ with check option

    C create view abc as student where dept=’IS’

    D create view abc as select dept=’IS’ from student


    参考答案B 单选题

  • 第14题：

    查询所有目前年龄是22岁的学生信息：学号，姓名和年龄，正确的命令组是( )。

    A．CREATE VIEW AGE LIST AS； SELECT 学号，姓名，YEAR(DATE())-YEAR(出生日期)年龄FROM学生； SELECT 学号，姓名，年龄FROM AGE LIST WHERE年龄=22

    B．CREATE VIEW AGE LIST AS； SELECT 学号，姓名，YEAR(出生日期) FROM 学生； SELECT 学号，姓名，年龄FROM AGE LIST WHERE YEAR(出生日期)=22

    C．CREATE VIEW AGE LIST AS； SELECT 学号，姓名，YEAR(DATE())-YEAR(出生日期)年龄FROM学生； SELECT 学号，姓名，年龄FROM学生WHEREYEAR(出生日期)=22

    D．CREATE VIEW AGE LIST AS STUDENT； SELECT学号，姓名，YEAR(DATE())-YEAR(出生日期)年龄FROM学生； SELECT学号，姓名，年龄FROM STUDENT WHERE年龄=22


    正确答案：A
    解析：选项B和选项C是从建立的AGE LIST视图中查询数据，但AGE LIST视图中没有出生日期字段。选项D是从STUDENT表或视图中查询数据，但是STUDENT表或视图不存在。选项A是正确答案。

  • 第15题：

    Given the following DDL and INSERT statements:CREATE VIEW v1 AS SELECT col1 FROM t1 WHERE col1 > 10; CREATE VIEW v2 AS SELECT col1 FROM v1 WITH CASCADED CHECK OPTION; CREATE VIEW v3 AS SELECT col1 FROM v2 WHERE col1 < 100; INSERT INTO v1 VALUES(5); INSERT INTO v2 VALUES(5); INSERT INTO v3 VALUES(20); INSERT INTO v3 VALUES(100);How many of these INSERT statements will be successful?（）

    A.0

    B.1

    C.2

    D.3


    参考答案：C

  • 第16题：

    The following statements:CREATE TABLE t1 (col1 INT NOT NULL, PRIMARY KEY(col1)); CREATE TABLE t2 (col1 INT NOT NULL, col2 CHAR(1) NOT NULL, PRIMARY KEY (col1, col2), FOREIGN KEY (col1) REFERENCES t1 (col1) ON DELETE CASCADE ON UPDATE RESTRICT); CREATE TABLE t3 (col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (col1, col2),FOREIGN KEY (col1) REFERENCES t1 (col1) ON DELETE NO ACTION ON UPDATE RESTRICT);INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (1, ‘a‘), (1, ‘b‘), (2,‘c‘); INSERT INTO t3 VALUES (1, 100), (2, 200), (2,300);How many rows will be deleted by the following DELETE statement? DELETE FROM t1 WHERE col1= 1;（）

    A.4

    B.3

    C.1

    D.0


    参考答案：D

  • 第17题：

    客服业务受到SQL语句的影响非常大，以下哪些是执行效率比较低的SQL语句，可以进行优化（）

    • A、select*from record where substrb（CardNo，1，4）=’5378’；
    • B、表tab1中的列col1是字符型（char），执行select col1，col2 from tab1 where col1>’10’；
    • C、select count（*）from stuff where id_no in（’0’，’1’）；
    • D、UPDATE SERVICEINFO SET STATE=0WHERE STATE<>0。

    正确答案:A,C,D

  • 第18题：

    设有关系模式商品（商品号，商品名称，单价，数量，类别），建立统计每类商品总数量的视图的正确语句是（）

    • A、CREATE VIEW v1AS SELECT类别，SUM（数量）FROM商品GROUP BY类别
    • B、CREATE VIEW v1AS SELECT类别，COUNT（数量）FROM商品GROUP BY类别
    • C、CREATE VIEW v1（类别，总数量）AS SELECT类别，SUM（数量）FROM商品GROUP BY类别
    • D、CREATE VIEW v1（类别，总数量）AS SELECT类别，COUNT（数量）FROM商品GROU PBY类别

    正确答案:C

  • 第19题：

    存在两个结构相同的数据库表T1（col1，col2，col3）、T2（col1，col2，col3），写出一SQL语句将所有T1数据导入到T2表（）

    • A、select col1，col2，col3 from T1 into T2（col1，col2，col3）
    • B、insert T1 （col1，col，col3） into T2（col1，col2，col3）
    • C、insert into T2 （col1，col2，col3） as select col1，col2，col3 from T1
    • D、insert into T2（col1，col2，col3） select col1，col2，col3 from T1；

    正确答案:D

  • 第20题：

    The following statements: CREATE TABLE t1 (col1 INT NOT NULL, PRIMARY KEY(col1)); CREATE TABLE t2 (col1 INT NOT NULL, col2 CHAR(1) NOT NULL, PRIMARY KEY (col1, col2), FOREIGN KEY (col1) REFERENCES t1 (col1) ON DELETE CASCADE ON UPDATE RESTRICT); CREATE TABLE t3 (col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (col1, col2),FOREIGN KEY (col1) REFERENCES t1 (col1) ON DELETE NO ACTION ON UPDATE RESTRICT);INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (1, 'a'), (1, 'b'), (2,'c'); INSERT INTO t3 VALUES (1, 100), (2, 200), (2,300); How many rows will be deleted by the following DELETE statement? DELETE FROM t1 WHERE col1= 1;（）

    • A、4
    • B、3
    • C、1
    • D、0

    正确答案:D

  • 第21题：

    Examine the structure if the EMPLOYEES table: Column name Data Type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2(30) JOB_ID VARCHAR2(20) NOT NULL SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the DEPARTMENTS table You need to create a view called EMP_VU that allows the user to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the user to insert rows? （）

    • A、CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, department_id FROM employees WHERE mgr_id IN (102, 120);
    • B、CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id department_id FROM employees WHERE mgr_id IN (102, 120);
    • C、CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTALSAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_ id;
    • D、CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees;

    正确答案:B

  • 第22题：

    单选题
    存在两个结构相同的数据库表T1（col1，col2，col3）、T2（col1，col2，col3），写出一SQL语句将所有T1数据导入到T2表（）
    A

    select col1，col2，col3 from T1 into T2（col1，col2，col3）

    B

    insert T1 （col1，col，col3） into T2（col1，col2，col3）

    C

    insert into T2 （col1，col2，col3） as select col1，col2，col3 from T1

    D

    insert into T2（col1，col2，col3） select col1，col2，col3 from T1；


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

  • 第23题：

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


    正确答案： E
    解析： 暂无解析

相关内容