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

（A）It can be used only from SQL*Plus.

（B）It can be used from SQL Developer.

（C）It displays the NOT NULL constraint for any columns that have that constraint.

（D）It can be used to display the structure of an existing view.

（E）It displays the PRIMARY KEY constraint for any column or columns that have that constraint.

（F）It displays all constraints that are defined for each column.

（A）150

（B）16

（C）160

（D）200

（E）100

（A）they must be placed on the left side of the comparison operator or condition.

（B）THEY MUST BE PLACED ON THE RIGHT SIDE OF THE COMPARISON OPERATOR OR CONDITION.

（C）THEY CAN BE USED IN THE HAVING CLAUSE

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

（E）THEY CAN BE USED in the where clause.

（F）A SQL STATEMENT MAY HAVE MULTIPLE SINGLE ROW SUBQUERY BLOCKS.

（A）SELECT
FROM bricks b
FULL JOIN colors C
ON b. color rgb _ hex_ value = c. rgb _hex_ value;

（B）SELECT.
FROM bricks b
RIGHT JOIN colors c
ON b. color _rgb_ hex_ value = c. rgb hex_ value;

（C）SELECT
EROM colors C
LEET JOIN bricks b
ON b. color_ rgb_ hex value = c. rgb. hex.

（D）SELECT *
EROM bricks | b
JOIN colors C
ON b. color_ rgb_ hex_ value =c. rgb _hex value;

（E）SELECT
EROM colors C
LEFT JOIN bricks on
USING (rgb _ hex_ value) ;

（A）WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE UPPER('MC%')

（B）WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%'

（C）WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) LIKE 'Mc%'

（D）WHERE SUBSTR(cust_name, INSTR(cust_name,' ') + 1) LIKE 'Mc%' OR 'MC%'

（E）WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ') + 1)) IN ('MC%','Mc%')

（A）The names of employees earning the maximum salary will appear first in descending order.

（B）The names of employees earning the maximum salary will appear first In ascending order.

（C）All remaining employee names will appear in descending order.

（D）All remaining employee names will appear in ascending order.

（E）The names of employees earning the maximum salary will appear first in an unspecified order.

（F）All remaining employee names will appear in an unspecified order.

（A）ORDER BY"CUST NO"

（B）ORDER BY CUSTNO

（C）ORDERBY2, 1

（D）ORDER BY 2, cust_id

（E）ORDER BY "Last Name"

（A）A primary key constraint is manadatory.

（B）A create table statement can specify the maximum number of rows the table will contain.

（C）The same table name can be used for tables in different schemas.

（D）A system privilege is required.

（E）Creating an external table will automatically create a file using the specified directory and file name.

（A）COMMIT erases all the transaction's savepoints and releases its locks.

（B）ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.

（C）ROLLBACK to SAVEPOTNT undoes the transaction's changes made since the named savepoint and then ends the transaction.

（D）ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.

（E）ROLLBACK without the TO SAVEPOINT clause undoes alt the transaction's changes, releases its locks, and erases all its savepoints.

（F）COMMIT ends the transaction and makes all its changes permanent.

（A）SELECT last_name,salary
FROM (SELECT * FROM employees ORDER BY salary)

（B）SELECT last_name,salary
FROM employees
WHERE ROWNUM<=3
ORDER BY (SELECT salary FROM employees);

（C）SELECT last_name,salary
FROM employees
FETCH FIRST 3 ROWS ONLY
ORDER BY salary;

（D）SELECT last_name,salary
FROM employees
ORDER BY salary
FETCH FIRST 3 ROWS ONLY;

（E）SELECT last_name, salary
FROM employees
WHERE ROWNUM<=3

（A）COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.

（B）COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.

（C）COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column

（D）A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

（E）It can only be used for NUMBER data types.

（A）The EXPIRY DATE column data type can be changed to TIME STAMP.

（B）The PROD ID column can be renamed.

（C）The PROD_ ID column data type can be changed to VARCHAR2 (2).

（D）The PROD NAME column cannot have a DEFAULT clause added to it.

（E）The EXPIRY DATE column cannot be dropped.

（A）sorted by DEPARTMENT_NAME and AVGSAL

（B）sorted by DEPARTMENT_NAME

（C）Sorted by MAXSAL

（D）sorted by DEPARTMENT_NAME and MAXSAL

（E）sorted by AVGSAL

（A）Add a new NOT NULL Column with a DEFAULT value.

（B）Increase the width of a numeric column.

（C）Change the default value of a column.

（D）Add a new column as the table's first column.

（E）Change a DATE Column containing data to a NUMBER data type.

（F）Define a default value that is automatically inserted into a column containing nulls.

（A）SELECT COALESCE('DATE',SYSDATE) FROM (SELECT NULL AS "DATE" FROM DUAL) ;

（B）SELECT NVL('DATE',SYSDATE) FROM DUAL;

（C）SELECT COALESCE(O,SYSDATE) TRCH DUAL;

（D）SELECT COALESCR('DATE', SYSDATE) FROM DUAL;

（E）SELECT NVL('DATE',200) FROM (SELECT NULL AS "DATE" FROM DUAL);

（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）The ON clause must be used.

（B）It can be an outer join.

（C）The ON clause can be used.

（D）It must be an inner join.

（E）The query must use two different aliases for the table.

（F）It must be an equijoin.

（A）SELECT emp. *
FROM employees emp
WHERE manager_ id NOT IN (
SELECT mgr.employee_ id
FROM employees mgr
WHERE emp. department_ id < > mgr.department_ id
);

（B）SELECT emp. *
FROM employees emp
JOIN employees mgr
ON emp. manager_ id = mgr. employee_ id
AND emp. department_ id<> mgr.department_ id;

（C）SELECT emp. *
FROM employees emp
RIGHT JOIN employees mgr
ON emp.manager_ id = mgr. employee id
AND emp. department id <> mgr.department_ id
WHERE emp. employee_ id IS NOT NULL;

（D）SELECT emp.*
FROM employees emp
WHERE NOT EXISTS (
SELECT NULL
FROM employees mgr
WHERE emp.manager id = mgr.employee_ id
AND emp.department_id<>mgr.department_id
);

（E）SELECT emp.*
FROM employees emp
LEFT JOIN employees mgr
ON emp.manager_ id = mgr.employee_ id
AND emp. department id < > mgr. department_ id;

（A）Roles are assigned to roles using the ALTER ROLE statement.

（B）A single role can be assigned to multiple users.

（C）Roles are assigned to users using the ALTER USER statement.

（D）Privileges are assigned to a role using the ALTER ROLE statement.

（E）A role is a named group of related privileges that can only be assigned to a user.

（F）Privileges are assigned to a role using the GRANT statement.

（G）A single user can be assigned multiple roles.