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

（A）SELECT dept_ id, AVG (MAX(salary)) FROM employees GROUP By dept_id HAVING hire_date> ' O1-JAN-19';

（B）SELECT dept_ id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;

（C）SELECT dept id, MAX (SUM(salary)) FROM employees GROUP BY dept_id;

（D）SELECT dept_ iD, sum(salary) FROM employees WHERE hire_date > '01-JAN-9' GROUP BY dept_id;

（E）SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;

（A）You can never TRUNCATE a table if foreign key constraints will be violated.

（B）You can never tows from a table if foreign key constraints will be violated.

（C）For tables with multiple indexes and triggers is faster than TRUNCATE.

（D）For large tables TRUNCATE is faster than DELETE.

（A）Views with the prefix all_, dba_ and useb_ are not all available for every type of metadata.

（B）The data dictionary is accessible when the database is closed.

（C）The data dictionary does not store metadata in tables.

（D）Views with the prefix dba_ display only metadata for objects in the SYS schema.

（E）Views with the prefix all_ display metadata for objects to which the current user has access.

（A）The DEPARTMENT_ID column is set to null for all tows in the table

（B）The DEPARTMENT_ID column can be recovered from the recycle bin

（C）A query can display data from the DEPARTMENT_ID column.

（D）The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

（E）No updates can be made to the data in the DEPARTMENT_ID column.

（F）A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.

（A）table1 join table2 using (column1, column2)

（B）table1 join table2 on (table1.column between table2.column] and table2.column2)

（C）table1 natural JOIN table2

（D）tablet join table2 ON (table1.column = table2.column) where table2.column LIKE 'A'

（E）table1 join table2 ON (table1.column >= table2.column)

（A）USER TABLES displays all tables owned by the current user.

（B）You must have SELECT privileges on a table to view it in USER TABLES.

（C）All users can query DBA TABLES successfully.

（D）You must have SELECT privileges on a table to view it in DBA TABLES.

（E）You must have SELECT privileges on a table to view it in ALL TABLES.

（F）All TABLES displays all tables owned by the current user.

（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）DELETE

（B）ALTER

（C）INDEX

（D）SELECT

（E）INSERT

（F）REFERENCES

（G）UPDATE

（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.

（A）If the ON COMMIT clause is session-specific,the table is dropped when the session is terminated.

（B）If the ON COMMIT clause Is transaction-specific, all rows in the table are deleted alter each COMMIT or ROLLBACK.

（C）Indexes can be created on them.

（D）Their data is always stored in the default temporary tablespace of the user who created them.

（E）They can be created only by a user with the DBA role,but can be accessed by all users who can create a session.

（F）Backup and recovery operations are available for these tables.

（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）It can be an inner join.

（B）The join key column must have an index.

（C）It can be a left outer join.

（D）It must be an equijoin.

（E）It must be a full outer join.

（A）An alias name must always be specified in quotes.

（B）An allas name must not contain space characters.

（C）An alias name must not be used in an ORDER BY clause.

（D）An alias name must not be used in a GROUP BY clause.

（A）SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '6' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;

（B）SELECT TO_ CHAR(TO_ DATE('29-10-2019') +INTERVAL '2'; MONTH + INTERVAL '5'; DAY - INTERVAL '86410' SECOND, ' DD-MON-YYYY') AS "date" FROM DUAL;

（C）SELECT-TO_CHAR(TO _DATE('29-10-2019'+ INTERVAL '2' MONTH+INTERVAL '4' DAYINTERVAL
'120' SECOND, 'DD-MON-YY') AS "daTe"
FROM DUAL;

（D）SELECT To CHAR(TO _DATE('29-10-2019') + INTERVAL '2' NONTH + INTERVAL '5' DAY INEERVAL '120' SECOND, ' DD-MON-YYY) AS "date" FROM DUAL;

（E）SELECT TO_ CHAR(TO_ DATE('29-10-2019') + INTERVAL '3' MONTH + INTERVAL '7' DAY - INTERVAL '360' SECOND, ' DD-MON-YYYY') AS "date" FROM DUAL;

（A）All remaining employee names will appear in ascending order

（B）All remaining employee names will appear in descending order

（C）All remaining employee names will appear in an ascending order

（D）The names of employees maximum salary will appear fist to ascending order

（E）The names of employees maximum salary will appear fist to descending order

（F）The names of employees remaining the maximum salary will appear first in an ascending order