1z1-071 無料問題集「Oracle Database SQL」

（A）Aggregate functions can be used in any clause of a SELECT statement

（B）The MAX and MIN functions can be used on columns with character data types

（C）The AVG function implicitly converts NULLS to zero

（D）Aggregate functions can be nested to any number of levels

（A）They must be placed on the right side of the comparison operator or condition.

（B）They can be used in the clause.

（C）They must be placed on the left side of the comparison operator or condition.

（D）They must return a row to prevent errors in the SQL statement.

（E）They can be used in the HAVING clause.

（F）A SQL statement may have multiple single row subquery blocks.

（A）The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.

（B）The second ROLLBACK command replays the delete.

（C）The second ROLLBACK command undoes the insert.

（D）The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.

（E）The second ROLLBACK command does nothing.

（A）SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE 12 * SALARY > 100000
ORDER BY 12 * SALARY;

（B）SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE ANNUAL_SALARY > 100000
ORDER BY ANNUAL_SALARY;

（C）SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES;

（D）SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE ANNUAL_SALARY > 100000
ORDER BY 12 * SALARY ;

（E）SELECT FIRST_NAME LAST_NAME FROM EMPLOYEES;

（F）SELECT LAST_NAME,12 * SALARY AS ANNUAL_SALARY
FROM EMPLOYEES
WHERE 12 * SALARY > 100000
ORDER BY ANNUAL_SALARY;

（A）MERGE INTO employees e
USING new employees ne
ON (e.employee_id = ne.employee_id)
WHEN FOUND THEN
UPDATE SET e.name =ne.name, e.job_id=ne.job_id, e.salary =ne.salary
WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_id,ne.name,ne.job_id,ne.salary) ;

（B）MERGE INTO employees e
USING new_employees n
WHERE e.employee_id = ne.employee_id
WHEN FOUND THEN
UPDATE SET e.name=ne.name,e.job_id =ne.job_id, e.salary=ne.salary
WHEN NOT FOUND THEN
INSERT VALUES (ne.employee_ id,ne.name,ne.job id,ne.salary) ;

（C）MERGE INTO employees e
USING new_employees n
ON (e.employee_id = ne.employee_id)
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job id = ne.job_id,e.salary =ne. salary
WHEN NOT MATCHED THEN
INSERT VALUES (ne. employee_id,ne.name,ne.job_id,ne.salary);

（D）MERGE INTO employees e
USING new employees ne
WHERE e.employee_id = ne.employee_ id
WHEN MATCHED THEN
UPDATE SET e.name = ne.name, e.job_id = ne.job_id,e.salary =ne. salary
WHEN NOT MATCHED THEN
INSERT VALUES (ne. employee_id,ne.name, ne.job_id,ne.salary) ;

（A）SELECT * FROM customers WHERE insert_date'01-JAN-19';

（B）SELECT * FROM customers WHERE TO_DATE(insert_date)=DATE'2019-01-01';

（C）SELECT * FROM customers WHERE customer_id=0001;

（D）SELECT * FROM customers WHERE insert_date=DATE'2019-01-01';

（E）SELECT * FROM customers WHERE TO_CHAR(customer_id)='0001';

（F）SELECT * FROM customers WHERE customer_id='0001';

（A）The DESCRIBE command displays unused columns

（B）A primary key column cannot be set to unused.

（C）A query can return data from unused columns, but no DML is possible on those columns.

（D）A foreign key column cannot be set to unused.

（E）Once a column has been set to unused, a new column with the same name can be added to the table.

（F）Unused columns retain their data until they are dropped

（A）CREATE SYNONYM emp FOR hcm.employee_records;

（B）CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;

（C）CREATE SYNONYM SYS.emp FOR hcm.employee_records;

（D）CREATE PUBLIC SYNONYM emp FOR hcm. employee_records;

（E）CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;

（A）A condition representing an outer join cannot be linked to another condition using the or logical operator.

（B）An outer join is used to retrieve only the rows that do not meet the join condition.

（C）The outer join operator (+) can be used on both sides of the join condition in an outer join.

（D）The IN operator cannot be used in a condition that Involves an outer join.

（E）The outer join operator (+) is used next to the column of the table without the matching rows.

（A）a unique identifier

（B）an attribute

（C）an entity

（D）label

（E）an optionality

（F）a cardinality

（A）It can use views to produce source rows.

（B）It can update, insert, or delete rows conditionally in multiple tables.

（C）It can combine rows from multiple tables conditionally to insert into a single table.

（D）It can use subqueries to produce source rows.

（E）It can update the same row of the target table multiple times.

（F）It can merge rows only from tables.

（A）SELECT employee_ name FROM employees WHERE (CUNACV_ DATE - hire_ data / 12> 3

（B）SELECT employee_ name FROM employees WHERE (SYSNAYW - hire_ data / 12> 3

（C）SELECT employee_ name FROM employees WHERE (CUARENT_ DATE - hire_ data / 365>5

（D）SELECT employee_ name FROM employees WHERE (SYSDATE - hire_ data) / 365>5

（E）There are typos in this option (CUNACV_DATE should be CURRENT_DATE and hire_data should be hire_date). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

（F）SELECT employee_ name FROM employees WHERE (SYSNAYW - hire_ data / 12> 3

（G）SELECT employee_ name FROM employees WHERE (SYSTIMESTAMP - hire_ data) / 365>

（A）A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

（B）An UNUSED column's space is remained automatically when the row containing that column is next queried.

（C）A column that is set to NNUSED still counts towards the limit of 1000 columns per table.

（D）An UNUSED column's space is remained automatically when the block containing that column is next queried.

（E）Partition key columns cannot be dropped.

（F）A DROP COLUMN command can be rolled back.

（A）PRODUCT_ NAME cannot contain duplicate values.

（B）PRODUCT_ ID can be assigned the PRIMARY KEY constraint.

（C）EXPIRY_ DATE cannot be used in arithmetic expressions.

（D）PRODUCT_ PRICE contains the value zero by default if no value is assigned to it.

（E）PRODUCT_ PRICE can be used in an arithmetic expression even if it has no value stored in it.

（F）EXPIRY_ DATE contains the SYSDATE by default if no date is assigned to it.

（A）It consists of a single row and single column of VARCHAR2 data type.

（B）It can be accessed only by the SYS user.

（C）It can display multiple rows and columns.

（D）It can be used to display only constants or pseudo columns.

（E）It can display multiple rows but only a single column.

（F）It can be accessed by any user who has the SELECT privilege in any schema

（A）SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL
AND cust_credit_limit IS NOT NULL;

（B）SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level IS NOT NULL
AND due_amount IS NOT NULL;

（C）SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND due_amount !=NULL;

（D）SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND cust credit_level !=NULL;

（E）SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level <> NULL
AND due_amount <> NULL;

（A）It provides independence for logical data structures being manipulated from the underlying physical data storage

（B）It guarantees atomicity, consistency, isolation, and durability (ACID) features

（C）It requires that data be contained in hierarchical data storage

（D）It is used to define encapsulation and polymorphism for a relational table

（E）It is the only language that can be used for both relational and object-oriented databases

（F）It best supports relational databases

（A）INTERSECT ignores NULLs

（B）The number of columns in each SELECT in the compound query can be different.

（C）INTERSECT returns rows common to both sides of the compound query.

（D）Column names in each SELECT in the compound query can be different.

（E）Reversing the order of the intersected tables can sometimes affect the output.

（A）A table can have only one primary key and one foreign key.

（B）A table can have only one primary key but multiple foreign keys.

（C）Only the primary key can be defined the column and table level.

（D）The foreign key columns and parent table primary key columns must have the same names.

（E）It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted.

（F）Primary key and foreign key constraints can be defined at both the column and table level.

（G）It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.