Oracle 1Z0-071 practice test questions fully solved & updated 2024

ORDER BY must be placed at the end of the SQL statements When using ORDER BY reference, the column name in force is whatever column name exists in the first SELECT statement An INDEX object is built on one or more columns in a table The INDEX stores data from its table's columns on which it is built and presorts that data in order to speed future queries When the DML statements INSERT, UPDATE, and DELETE are executed on an indexed table the indexed data is changed, the index is automatically updated by Oracle INDEX objects are built on one or more columns. Multiple-column indexes are composite indexes. You use the keywords VISIBLE and INVISIBLE to specify the visibility of an INDEX. The default is VISIBLE You can specify the visibility of an index in CREATE INDEX and ALTER INDEX statements. An external table is a read-only table within the database that stores data outside of the database The communication between the external table's data storage file and database objects is on the logic of SQL*Loader or Oracle Data Pump. The types of constraints are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK. A UNIQUE constraint requires that if data is added to a column for a row, that data must be unique for any existing value already in the column. A PRIMARY KEY constraint is the combination of NOT NULL and UNIQUE. A PRIMARY KEY may be assigned to one column or a combination of columns. A PRIMARY KEY assigned to a combination of columns is called a composite key. A single table may have only one PRIMARY KEY. Foreign Key correlates one or more columns in one table with a set of similar columns in a second table. A FOREIGN KEY requires that the second table already have either a PRIMARY KEY constraint or a UNIQUE constraint assigned to the correlated columns before the FOREIGN KEY can be created. Once created, the FOREIGN KEY ensures that any values added to the table will match existing values in the PRIMARY KEY columns of the second table. Constraints can be created with the CREATE TABLE statement or within the ALTER TABLE statement. Constraints can be defined as part of the column definitions (in line) or after (out of line). One exception is that NOT NULL can be created in line but not out of line. Each column must be assigned a data type. Data types include numeric, character, and date types, such as VARCHAR2, NUMBER, and DATE. Data types also include large object types, including BLOB. DDL statements include CREATE, ALTER, and DROP. DDL statements are a subset of SQL and are used to create new database objects or alter the structure of existing database objects, including removing them. Subqueries are used to create view objects. You can use a subquery in a SELECT, UPDATE, or DELETE statement. Subqueries can be used as alternatives to expressions. A single-row subquery returns one row of data to the parent query. A multiple-row subquery may return more than one row of data to the parent query. Multiple-column subqueries return two or more columns' worth of data at once to the parent query, which must test for all the columns at once. Correlated subqueries use data from a parent query to determine their own result. Scalar subqueries always return one value, represented in one column of one row, every time. The multiple-column subquery may be of the single-row or multiple-row type of subquery. A correlated subquery might be a single-row, multiple-row, or multiple-column subquery. Group functions are also known as aggregate, or multirow, functions. Multirow functions return one value for every set of zero or more rows considered within a SELECT statement. There are group functions to determine minimum and maximum values, calculate averages, and more. Group functions can be used can be used to determine rank within a group of rows. Aggregate and scalar data cannot be included in the same SELECT statement's select list. The COUNT function counts occurrences of data, as opposed to the SUM function, which adds up numeric values. The MIN and MAX functions can operate on date, character, o