SQL ORACLE

Which three statements are true about indexes and their administration in an Oracle database? (Choose three.). The same table column can be part of a unique and non-unique index. A descending index is a type of function-based index. An INVINSIBLE index is not maintained when DML is performed on its underlying table. If a query filters on an indexed column then it will always be used during execution of the query. An index can be created as part of a CREATE TABLE statement. An UNUSABLE index is maintained when DML is performed on its underlying table.

Which two statements about INVISIBLE indexes are true? (Choose two.). You use ALTER INDEX to make an INVISIBLE index VISIBLE. An INVISIBLE index consumes no storage. The query optimizer never considers INVISIBLE indexes when determining execution plans. You can only create one INVISIBLE index on the same column list. All INSERT, UPDATE, and DELETE statements maintain entries in the index.

You own table DEPARTMENTS, referenced by views, indexes, and synonyms. Examine this command which executes successfully: DROP TABLE departments PURGE; Which three statements are true? (Choose three.). It will remove the DEPARTMENTS table from the database. It will drop all indexes on the DEPARTMENTS table. It will remove all views that are based on the DEPARTMENTS table. It will remove all synonyms for the DEPARTMENTS table. Neither can it be rolled back nor can the DEPARTMENTS table be recovered. It will delete all rows from the DEPARTMENTS table, but retain the empty table.

You issued this command: DROP TABLE hr.employees; Which three statements are true? (Choose three.). Sequences used to populate columns in the HR.EMPLOYEES table are dropped. Synonyms for HR.EMPLOYEES are dropped. Views referencing HR.EMPLOYEES are dropped. All constraints defined on HR.EMPLOYEES are dropped. The HR.EMPLOYEES table may be moved to the recycle bin. All indexes defined on HR.EMPLOYEES are dropped.

Which two statements are true about indexes and their administration in an Oracle database? (Choose two.). A new index can be created or an existing one reused when a primary key constraint is created. An INVINSIBLE index is maintained by DML operations on the underlying table. If a query filters on an indexed column, the index will always be accessed during execution of the query. A DROP INDEX statement always prevents updates to the table during the drop operation. The same table column cannot be part of a unique and non-unique index.

Which three statements are true about Oracle synonyms? (Choose three.). A synonym cannot be created for a PL/SQL package. A synonym can be available to all users. A SEQUENCE can have a synonym. Any user can drop a PUBLIC synonym. A synonym created by one user can refer to an object belonging to another user.

Which two statements are true about Oracle synonyms? (Choose two.). Any user can create a PUBLIC synonym. A synonym has an object number. All private synonym names must be unique in the database. A synonym can be created on an object in a package. A synonym can have a synonym.

Examine this list of requirements for a sequence: 1. Name: EMP_SEQ 2. First value returned: 1 3. Duplicates are never permitted. 4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID column. 5. Reduce the chances of gaps in the values. Which two statements will satisfy these requirements? (Choose two.). CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;. CREATE SEQUENCE emp_seq;. CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE. CREATE SEQUENCE emp_seq NOCACHE;. CREATE SEQUENCE emp_seq START WITH 1 CACHE;.

BOOK_SEQ is an existing sequence in your schema. Which two CREATE TABLE commands are valid? (Choose two.). CREATE TABLE bookings ( bk id NUMBER (4) DEFAULT book seq.NEXTVAL PRIMARY KEY, start date DATE DEFAULT SYSDATE, end_date DATE DEFAULT SYSDATE NOT NULL);. CREATE TABLE bookings ( bk id NUMBER (4) start date DATE DEFAULT SYSDATE, end _date DATE DEFAULT (end_date >= start_date);. CREATE TABLE bookings ( bk id NUMBER (4) NOT NULL DEFAULT book seq.CURRVAL, start _date DATE NOT NULL, end date DATE DEFAULT SYSDATE);. CREATE TABLE bookings ( bk_id NUMBER (4) NOT NULL PRIMARY KEY, start date DATE NOT NULL, end_date DATE DEFAULT SYSDATE);. CREATE TABLE bookings ( bk_id NUMBER (4) DEFAULT book_seq.CURRVAL, start date DATE DEFAULT SYSDATE, end_date DATE DEFAULT start_date);.

Which three statements are true about sequences in a single instance Oracle database? (Choose three.). A sequence’s unallocated cached values are lost if the instance shuts down. A sequence number that was allocated can be rolled back if a transaction fails. A sequence can only be dropped by a DBA. A sequence can issue duplicate values. Sequences can always have gaps. Two or more tables cannot have keys generated from the same sequence.

Examine these statements and the result: CREATE SEQUENCE customer_seq CACHE 10; SELECT customer_sea.NEXTVAL FROM DUAL; NEXTVAL ------------- 1 Now examine this command: ALTER SEQUENCE customer_seq; What must replace MISSING CLAUSE for CUSTOMER_SEQ.NEXTVAL to return 11?. nocache. increment by 10. start with 11. minvalue 11. cycle 11.

CREATE SEQUENCE ord _seq INCREMENT BY 1 START WITH 1 MAXVALUE 100000 CYCLE CACHE 5000; CREATE TABLE ord_items ( ord_no NUMBER(4) DEFAULT ord _seq NEXTVAL NOT NULL, item_no NUMBER(3), qty NUMBER(3), expiry_date DATE, CONSTRAINT it _pk PRIMARY KEY (ord_no, item_no), CONSTRAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord _no)); Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence? (Choose two.). If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times. Sequence ORD_SEQ is guaranteed not to generate duplicate number.

Which statement will return the last sequence number generated by the EMP_SEQ sequence?. SELECT NEXTVAL FROM emp_seq;. SELECT CURRVAL FROM emp_seq;. SELECT emp_seq.CURRVAL FROM DUAL;. SELECT emp_seq.NEXTVAL FROM DUAL;.

Examine this constraint information: Which three statements are true? (Choose three.). The SALARY column must have a value. The DEPTNO column in the EMP table can contain NULLS. The COMMISION column can contain negative values. The DEPTNO column in the EMP table can contain the value 1. The MANAGER column is a foreign key referencing the EMPNO column. The DNAME column has a unique constraint. An index is created automatically in the MANAGER column.

Examine this description of the PRODUCTS table: NAME Null? Type -------------------- ------------------ ----------------- PROD_ID NOT NULL NUMBER(2) QTY NUMBER(5,2) COST NUMBER(8,2) Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode. CREATE INDEX price_idx ON products (price);. ALTER TABLE products SET UNUSED(expiry_date);. DROP TABLE products;. ALTER TABLE products DROP COLUMN expiry_date;. TRUNCATE TABLE products;. ALTER TABLE products DROP UNUSED COLUMNS;.

In which three situations does a new transaction always start?. When issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session. When issuing a CREATE INDEX statement after a CREATE TABLE statement completed unsuccessfully in the same session. When issuing a TRUNCATE statement after a SELECT statement was issued in the same session. When issuing a CREATE TABLE statement after a SELECT statement was issued in the same session. When issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session. When issuing a DML statement after a DML statement filed in the same session.

Which three are true about the CREATE TABLE command?. It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint. The owner of the table should have space quota available on the tablespace where the table is defined. It implicitly executes a commit. It implicitly rolls back any pending transactions. A user must have the CREATE ANY TABLE privilege to create tables. The owner of the table must have the UNLIMITED TABLESPACE system privilege.

Which three actions can you perform by using the ALTER TABLE command?. Drop pseudo columns from a table. Restrict all DML statements on a table. Drop all columns simultaneously from a table. Lock a set of rows in a table. Rename a table. Enable or disable constraints on a table.

Examine the description of the PRODUCT_ DETAILS table Which two statements are true?. PRODUCT_ PRICE can be used in an arithmetic expression even if it has no value stored in it. PRODUCT_ ID can be assigned the PRIMARY KEY constraint. EXPIRY_ DATE cannot be used in arithmetic expressions. EXPIRY_ DATE contains the SYSDATE by default if no date is assigned to it. PRODUCT_ PRICE contains the value zero by default if no value is assigned to it. PRODUCT_ NAME cannot contain duplicate values.

View the exhibit and examine the ORDERS table. Name Null? Type -------------------------- ORDER ID NOT NULL NUMBER(4) ORDATE DATE CUSTOMER_ID NUMBER(3) ORDER_TOTAL NUMBER(7,2) The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?. ALTER TABLE orders MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);. ALTER TABLE orders MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);. ALTER TABLE orders ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);. ALTER TABLE orders ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;.

Which three actions can you perform on an existing table containing data? (Choose three.). Increase the width of a numeric column. Add a new column as the table’s first column. Define a default value that is automatically inserted into a column containing nulls. Change a DATE column containing data to a NUMBER data type. Change the default value of a column. Add a new NOT NULL column with a DEFAULT value.

Which two are true about virtual columns? (Choose two.). They can be indexed. They can be referenced in the column expression of another virtual column. They cannot have a data type explicitly specified. They can be referenced in the set clause of an update statement as the name of the column to be updated. They can be referenced in the where clause of an update or delete statement.

Examine the description of the PRODUCTS table which contains data: Name Null? Type PROD_ID NOT NULL NUMBER(2) PROD_NAME VARCHAR(20) EXPIRY_DATE NOT NULL DATE Which two are true? (Choose two.). The PROD_NAME column cannot have a DEFAULT clause added to it. The EXPIRY_DATE column cannot be dropped. The EXPIRY_DATE column data type can be changed to TIMESTAMP. The PROD_ID column can be renamed. The PROD_ID column data type can be changed to VARCHAR2(2).

You execute the following command: SQL> ALTER TABLE departments SET UNUSED (country) ; Name Null? Type DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(20) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) COUNTRY VARCHAR2(20) Which two statements are true? (Choose two.). Views created in the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid. Unique key constraints defined on the COUNTRY column are removed. A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command. Synonyms existing of the DEPARTMENTS table would have to be re-created. Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.

Which three statements are true about built-in data types?. A VARCHAR2 blank-pads column values only if the data stored is non-numeric and contains no special characters. The default length for a CHAR column is always one character. A VARCHAR2 column definition does not require the length to be specified. A BLOB stores unstructured binary data within the database. A CHAR column definition does not require the length to be specified. A BFILE stores unstructured binary data in operating system files.

Examine this statement: CREATE TABLE orders (serial_no NUMBER UNIQUE, order_id NUMBER PRIMARY KEY, order_date DATE NOT NULL, status VARCHAR2 (10) CHECK (status IN (‘CREDIT’, ‘CASH’)), product_id NUMBER REFERENCES products (product_id), order_total NUMBER) ; On which two columns of the table will an index be created automatically? (Choose two.). ORDER_ID. ORDER_TOTAL. ORDER_DATE. PRODUCT_ID. STATUS. SERIAL_NO.

Which two are true about creating tables in an Oracle database? (Choose two.). Creating an external table will automatically create a file using the specified directory and file name. A system privilege is required. The same table name can be used for tables in different schemas. A primary key constraint is mandatory. A CREATE TABLE statement can specify the maximum number of rows the table will contain.

Evaluate the following CREATE TABLE commands: CREATE TABLE orders (ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY, ord_date DATE, cust_id NUMBER (4)); CREATE TABLE ord_items (ord _no NUMBER (2), item_no NUMBER(3), qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200), expiry_date date CHECK (expiry_date> SYSDATE), CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no), CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) ); Why would the ORD_ITEMS table not get created?. SYSDATE cannot be used with the CHECK constraint. The CHECK constraint cannot be placed on columns having the DATE data type. The BETWEEN clause cannot be used twice for the same table. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Examine these statements: CREATE TABLE alter_test (c1 VARCHAR2 (10), C2 NUMBER (10)) ; INSERI INTO alter_test VALUES (‘123’, 123); COMMIT; Which is true about modifying the columns in ALTER_TEST?. c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAR2(10). c1 can be changed to NUMBER(10) and c2 cannot be changed to VARCHAR2(10). c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAR2(5). c1 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER(10). c1 can be changed to VARCHAR2(5) but c2 can be changed to NUMBER(12,2).

Examine these statements: CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, diname VARCHAR2(10) , mgr NUMBER , CONSTRAINT dept_fkey FOREIGN KEY(mgr) REFERENCES emp (empno)); CREATE TABLE emp ( Empno NUMBER PRIMARY KEY, Ename VARCHAR2 (10) , deptno NUMBER, CONSTRAINT emp_fkey FOREIGN KEY (deptno) REFERENCES dept (deptno) DISABLE); ALTER TABLE emp MODIFY CONSTRAINT emp_fkey ENABLE; Which two are true?. The MGR column in the DEPT table will not be able to contain NULL values. The CREATE TABLE EMP statement must precede the CREATE TABLE DEPT statement for all threestatements to execute successfully. Both foreign key constraint definitions must be removed from the CREATE TABLE statements, andbe added with ALTER TABLE statements once both tables are created, for the two CREATE TABLEstatements to execute successfully in the order shown. The DEFT FKEY constraint definition must be removed from the CREATE TABLE DEF statement.and be added with an AITER TABLE statement once both tables are created, for the two CREATE TABLE statements to execute successfully in the order shown. The Deptno column in the emp table will be able to contain nulls values. All three statements execute successfully in the order shown.

You executed the following CREATE TABLE statement that resulted in an error: SQL> CREATE TABLE employees emp_id NUMBER(10) PRIMARY KEY, ename VARCHAR2(20), email NUMBER(3) UNIQUE, address VARCHAR2(500), phone VARCHAR2(20), resume LONG, hire_date DATE, remarks LONG, dept_id NUMBER (3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOY NULL(ename)); Identify two reasons for the error. FOREIGN KEY defined on the DEPT_ID column must be at the table level only. The PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at the table level only. Only one LONG column can be used per table. The NOT NULL constraint on the ENAME column must be defined as the column level. The FOREIGN KEY keyword is missing in the constraint definition only one long.

Examine the description of the SALES table: The SALES table has 5,000 rows. Examine this statement: CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price) AS SELECT product_id, customer_id, quantity_sold, price FROM sales WHERE 1 = 1; Which two statements are true? (Choose two.). SALES1 is created with 1 row. SALES1 is created with no rows.  SALES1 has primary KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table. SALES1 is created with 5,000 rows.

Examine the description of the SALES table: Evaluate the following créate table statement CREATE TABLE salesl (prod_id, cust_id, quantity_sold, price) AS SELECT product_id, customer_id, quantity_sold, price FROM sales WHERE 1=2; hich two statements are true about the creation of the SALES1 table?. The SALES1 table is created with no rows but only a structure. The SALES1 table would have primary key and unique constraints on the specified columns. The SALES1 table would not be created because of the invalid where clause. The SALES1 table would have not null and unique constraints on the specified columns. The SALES1 table would not be created because column-specified names in the select and create table clauses do not match.

You are designing the structure of a table in which two columns have the specifications: COMPONENT_ ID - must be able to contain a maximum of 12 alphanumeric characters and must uniquely identify the row EXECUTION_DATETIME - contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components. Which two options define the data types that satisfy these requirements most efficiently? (Choose two.). The EXECUTION_DATETIME must be of TIMESTAMP data type. The COMPONENT_ID column must be of CHAR data type. The COMPONENT_ID must be of VARCHAR2 data type. The COMPONENT_ID must be of ROWID data type. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type. The EXECUTION_DATETIME must be of DATE data type.

Which three statements are true about defining relations between tables in a relational database?. Foreign key columns allow null values. Unique key columns allow null values. Primary key columns allow null values. Every primary or unique key value must refer to a matching foreign key value. Every foreign key value must refer to a matching primary or unique key value.

Which three are true about dropping columns from a table? (Choose three.).  A column must be set as unused before it is dropped from a table. A primary key column cannot be dropped. Multiple columns can be dropped simultaneously using the ALTER TABLE command. A column can be removed only if it contains no data. A column that is referenced by another column in any other table cannot be dropped. A column drop is implicitly committed.

Which two are true about unused columns? (Choose two.). A query can return data from unused columns, but no DML is possible on those columns. Unused columns retain their data until they are dropped. Once a column has been set to unused, a new column with the same name can be added to the table. The DESCRIBE command displays unused columns. A primary key column cannot be set to unused. A foreign key column cannot be set to unused.

Which two are true about unused columns? (Choose two.). Setting an indexed column to unused results in an error. You can query the data dictionary to see the names of unused columns. You can specify multiple column names in an ALTER TABLE...SET UNUSED statement. If you set all the columns of a table to unused, the table is automatically dropped. CASCADE CONSTRAINTS must be specified when setting a column to unused if that column is referenced in a constraint on another column.

The first DROP operation is performed on PRODUCTS table using this command: DROP TABLE products PURGE; Then a FLASHBACK operation is performed using this command: FLASHBACK TABLE products TO BEFORE DROP; Which is true about the result of the FLASHBACK command?. It recovers the table structure and data but not the related indexes. It recovers only the table structure. It recovers the table structure, data, and the indexes. It is not possible to recover the table structure, data, or th related indexes.

You execute this command: ALTER TABLE employees SET UNUSED (department_id); Which two are true?. A query can display data from the DEPARTMENT_ID column. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued. The DEPARTMENT_ID column is set to null for all tows in the table. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table. No updates can be made to the data in the DEPARTMENT_ID columna.

Examine this statement which executes successfully: Which statement will violate the CHECK constraint?. UPDATE emp80 SET department_id=90 WHERE department_id=80;. DELETE FROM emp80 WHERE department_id=90;. SELECT * FROM emp80 WHERE department_id=80;. SELECT * FROM emp80 WHERE department_id=90;.

Which three statements are true about dropping and unused columns in an Oracle database?. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option. A DROP COLUMN command can be rolled back. An UNUSED column’s space is remained automatically when the block containing that column is next queried. An UNUSED column’s space is remained automatically when the row containing that column is next queried. Partition key columns cannot be dropped. A column that is set to NNUSED still counts towards the limit of 1000 columns per table.

Which two statements are true about TRUNCATE and DELETE? (Choose two.). DELETE can use a WHERE clause to determine which row(s) should be removed. TRUNCATE can use a WHERE clause to determine which row(s) should be removed. TRUNCATE leaves any indexes on the table in an UNUSABLE state. The result of a TRUNCATE can be undone by issuing a ROLLBACK. The result of a DELETE can be undone by issuing a ROLLBACK.

Examine this command: TRUNCATE TABLE test; Table truncated. Which two are true? (Choose two.). The structure of the TEST table is removed. All the indexes on the TEST table are dropped. All the constraints on the TEST table are dropped. Removed rows can not be recovered using the ROLLBACK command. All the rows in the TEST table are removed.

Which statement is true about TRUNCATE and DELETE?. For tables with multiple indexes and triggers, DELETE is faster thanTRUNCATE. You can never TRUNCATE a table if foreign key constraints would be violated. You can DELETE rows from a table with referential integrity constraints. For large tables, DELETE is faster than TRUNCATE.

You execute this command: TUNCATE TABLE depts; Which two are true?. It drops any triggers defined on the table. It retains the integrity constraints defined on the table. It always retains the space used by the removed rows. A ROLLBACK statement can be used to retrieve the d4elet data. It retains the indexes defined on the table. A FLASHBACK TABLE statement can be used to retrieve t deleted data.

Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements?. DELETE but not TRUNCATE statement can be used to Remove data from selective columns and rows of a table. DELTE and TRUNCATE statements can have a rollback done to restore data into a table. DELETE but not TRUNCATE statement can be used to selectively remove rows from a table. DELETE and TRUNCATE statements remove all indexes for he tables on which they are performed.

Evaluate the SQL statement: TRUNCATE TABLE DEPT; Which three are true about the SQL statement? (Choose three.). It releases the storage space used by the table. It does not release the storage space used by the table. You can roll back the deletion of rows after the statement executes. You can NOT roll back the deletion of rows after the statement executes. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table.

Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.). A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table. GLOBAL TEMPORARY TABLE space allocation occurs at session start. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted. A GLOBAL TEMPORARY TABLE’S definition is available to multiple sessions. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.). GLOBAL TEMPORARY TABLE space allocation occurs at session start. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back. A GLOBAL TEMPORARY TABLE’S definition is available to multiple sessions.

You start a session and execute these commands successfully: CREATE GLOBAL TEMPORARY TABLE invoices_gtt( customer_id INTEGER, invoice_total NUMBER(10,2) ) ON COMMIT PRESERVE ROWS; INSERT INTO INVOICES_GTT VALUES (1,100); COMMIT; Which two are true? (Choose two.). To drop the table in this session, you must first truncate it. Other sessions can view the committed row. You can add a column to the table in this session. You can add a foreign key to the table. When you terminate your session, the row will be deleted.

Examine these statements and results: SOL> SELECT COUNT (*) FROM emp: COUNT (*) 14 SOL> CREATE GLOBAL TEMPORARY TABLE t_emp AS SELECT * FROM emp; Table created. SOL> INSERT INTO t_emp SELECT * FROM emp; 14 rows created. SQL> COMMIT: Commit complete. SQL> INSERT INTO t_emp SELECT * FROM emp; 14 rows created. SOL> SELECT COUNT (*) FROM t_emp; How many rows are retrieved by the last query?. 42. 0. 14. 28.

Which two are true about global temporary tables? (Choose two.). Indexes can be created on them. Backup and recovery operations are available for these tables. Their data is always stored in the default temporary tablespace of the user who created them. If the ON COMMIT clause is transaction-specific, all rows in the table are deleted after each COMMIT OR ROLLBACK. They can be created only by a user with the DBA role, but can be accessed by all users who can create a session. If the ON COMMIT clause is session-specific, the table is dropped when the session is terminated.

Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.). A GLOBAL TEMPORARY TABLE can have only one index. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view. DML on GLOBAL TEMPORARY TABLES generates no REDO. A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM. A GLOBAL TEMPORARY TABLE can have multiple indexes. A trigger can be created on a GLOBAL TEMPORARY TABLE.

Which two are true about external tables that use the ORACLE_DATAPUMP access driver? (Choose two.). When creating an external table, data can be selected only from a table whose rows are stored in database blocks. Creating an external table creates a directory object. When creating an external table, data can be selected from another external table or from a table whose rows are stored in database blocks. Creating an external table creates a dump file that can be used by an external table in the same or a different database. Creating an external table creates a dump file that can be used only by an external table in the same database.

Which three statements are true about external tables? (Choose three.). They can be temporary tables. DML statements can modify them. They can be used in queries containing joins. They can be used in queries containing sorts. They can be indexed. Their metadata is stored in the database.

Which two are true about transactions in the Oracle Database? (Choose two.). DML statements always start new transactions. DDL statements automatically commit only data dictionary updates caused by executing the DDL. A session can see uncommitted updates made by the same user in a different session. A DDL statement issued by a session with an uncommitted transaction automatically commits that transaction. An uncommitted transaction is automatically committed when the user exits SQL*Plus.

Examine this partial command: CREATE TABLE cust( cust_id NUMBER(2), credit_limit NUMBER(10) ) ORGANIZATION EXTERNAL Which two clauses are required for this command to execute successfully? (Choose two.). the access driver TYPE clause. the DEFAULT DIRECTORY clause. the REJECT LIMIT clause. the LOCATION clause. the ACCESS PARAMETERS clause.

Which statement is true regarding external tables?. The default REJECT LIMIT for external tables is UNLIMITED. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table in the database from an external table. The data and metadata for an external table are stored outside the database.

Which two are true about using constraints? (Choose two.). NOT NULL can be specified at the column and at the table level. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parent table must have the same names. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

Examine these SQL statements which execute successfully: CREATE TABLE emp (emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, ename VARCHAR2 (15), salary NUMBER (8,2), mgr_no NUMBER (2)) ; ALTER TABLE emp ADD CONSTRAINT emp_mgr_fk FOREIGN KEY (mgr_no) REFERENCES emp (emp_no) ON DELETE SET NULL; ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk; Which two statements are true after execution? (Choose two.). The primary key constraint will be enabled and IMMEDIATE. The foreign key constraint will be enabled and DEFERRED. The primary key constraint will be enabled and DEFERRED. The foreign key constraint will be disabled. The foreign key constraint will be enabled and IMMEDIATE.

Examine the description of the PRODUCT_DETAILS table: Which two statements are true? (Choose two.). EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it. PRODUCT_NAME cannot contain duplicate values. EXPIRY_DATE cannot be used in arithmetic expressions. PRODUCT_PRICE contains the value zero by default if no value is assigned to it. PRODUCT_ID can be assigned the PRIMARY KEY constraint.

CREATE TABLE emp (emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, ename VARCHAR 2 (15), salary NUMBER (8, 2), mgr_no NUMBER (2) CONSTRAINT emp_mgr_fk REFERENCE emp (emp_no)); ALTER TABLE emp DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk; Examine these SQL statements that are executed in the given order: What will be the status of the foreign key EMP_MGR_FK?. It will be enabled and immediate. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it. It will be enabled and deferred. It will remain disabled and can be re-enabled manually.

Which two statements are true about an Oracle database? (Choose two.). A table can have multiple primary keys. A column definition can specify multiple data types. A table can have multiple foreign keys. A VARCHAR2 column without data has a NULL value. A NUMBER column without data has a zero value.

Which two actions can you perform with object privileges? (Choose two.). Create roles. Create FOREIGN KEY constraints that reference tables in other schemas. Delete rows from tables in any schema except SYS. Set default and temporary tablespaces for a user. execute a procedure or function in another schema.

Which two are true about constraints? (Choose two.). A column with a FOREIGN KEY constraint can never contain a NULL value. A constraint can be disabled even if the constrained column contains data. Constraints are enforced only during INSERT operations. All constraints can be defined at the table or column level. A column with a UNIQUE constraint can contain a NULL value.

The ORDERS table has a primary key constraint on the ORDER_ID column. The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table. The constraint is defined with ON DELETE CASCADE. There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000. Which three DELETE statements execute successfully? (Choose three.). DELETE order_id FROM orders WHERE order_total < 1000;. DELETE orders WHERE order_total < 1000. DELETE * FROM orders WHERE order_total < 1000;. DELETE FROM orders;. DELETE FROM orders WHERE order_total < 1000;.

The PROD_ID column is the foreign key in the SALES table. Which references the PRODUCTS table. Similarly,the CUST_ID and TIME_ID columns are Also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively. Evaluate the following CREATE TABLE command: CREATE TABLE new_sales( prod_id, I cust_id, order AS SELECT I prod_id,cust_id,time_id FROM sales. Which statement is true regarding the above command?. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause I do not match. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.

Examine this statement: CREATE TABLE EMPLOYEES (emp_id NUMBER(5) PRIMARY KEY, ename VARCHAR2(15), email VARCHAR(40) UNIQUE, address LONG, resume LONG, dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments (dept_id), CONSTRAINT ename_nn NOT NULL); Which two things must be changed for it to execute successfully? (Choose two.). The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level. The NOT NULL constraint on ENAME must be defined at the column level instead of the table level. The primary key constraint on EMP_ID must have a name. One of the LONG columns must be changed to a VARCHAR2 or CLOB. The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.). A table can have only one primary key but multiple foreign keys. The foreign key columns and parent table primary key columns must have the same names. A table can have only one primary key and one foreign key. 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. Only the primary key can be defined at the column and table level. 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. Primary key and foreign key constraints can be defined at both the column and table level.

Which four statements are true about constraints on Oracle tables? (Choose four.). A PRIMARY KEY constraint can be added after a table has been created and populated. A FOREIGN KEY column can contain NULLs. A CHECK constraint can refer to values in other rows. A NOT NULL constraint can be defined at the table level. A UNIQUE constraint can use a pre-existing index on the constrained column or columns. A UNIQUE constraint permits NULLs. A column can have only one CHECK constraint.

Which two statements are true regarding constraints?. A constraint can be disabled even if the constraint column contains data. All constraints can be defined at the column level and at the table level. A constraint is enforced only for INSERT operation on the table. A column with the UNIQUE constraint can contain null values. A foreign key column cannot contain null values.

Which three are true about privileges? (Choose three.). A combination of object and system privileges can be granted to a role. All types of schema objects have associated object privileges. Schema owners can grant object privileges on objects in their schema to any other user or role. Only users with the DBA role can create roles. Only users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users. Object privileges granted on a table automatically apply to all synonyms for that table.

Which three actions can you perform only with system privileges?. Create stored procedures, functions and packages. Access flat files via a database, which are stored in an operating system directory. Use the WITH GRANT OPTION clause. Execute a procedure in another schema. Query any table in a database. Log in to a database instance.

Which two object privileges can be restricted to a subset of columns in a table? (Choose two.). index. alter. update. insert. delete.

Table HR.EMPLOYEES contains a row where the EMPLOYEE_ID is 109. User ALICE has no privileges to access HR.EMPLOYEES. User ALICE starts a session. User HR starts a session and successfully executes these statements: GRANT DELETE ON employees TO alice; UPDATE employees SET salary = 24000 WHERE employee_id = 109; In her existing session ALICE then executes: DELETE FROM hr.employees WHERE employee_id = 109; What is the result?. The DELETE command will wait for HR’s transaction to end then return an error. The DELETE command will immediately delete the row. The DELETE command will wait for HR’s transaction to end then delete the row. The DELETE command will immediately return an error.

You and your colleague Andrew have these privileges on the EMPLOYEE_RECORDS table: 1. SELECT 2. INSERT 3. UPDATE 4. DELETE You connect to the database instance and perform an update to some of the rows in EMPLOYEE_RECORDS, but do not commit yet. Andrew connects to the database instance and queries the table. No other users are accessing the table. Which two statements are true at this point? (Choose two.). Andrew will be unable to see the changes you have made. Andrew will be unable to perform any INSERTs, UPDATEs, or DELETEs on the table. Andrew will be able to SELECT from the table, but be unable to modify any existing rows. Andrew will be able to see the changes you have made. Andrew will be able to modify any rows in the table that have not been modified by your transaction.

User HR has CREATE SESSION, CREATE ANY TABLE and UNLIMITED TABLESPACE privileges. User SCOTT has CREATE SESSION, CREATE TABLE and UNLIMITED TABLESPACE privileges. HR successfully executes this statement: CREATE TABLE SCOTT.PRODUCTS ( prod_id NUMBER(2); Prod_name VARCHAR2(20)); HR attempts to execute: 1. INSERT INTO scott.products VALUES (1, ‘LAPTOP’); SCOTT attempts to execute: 2. SELECT * FROM products; 3. INSERT INTO scott.products VALUES (2, ‘HDD’); 4. CREATE SYNONYM prod FOR products; Which will execute successfully?. 1, 2 and 3. 2, 3 and 4. 1 only. 2 and 3 only.

Which two statements are true regarding roles? (Choose two.). The REVOKE command can be used to remove privileges but not roles from other users. A role can be granted to itself. Roles are named groups of related privileges that can be granted to users or other roles. A role can be granted to PUBLIC. A user can be granted only one role at any point of time.

You have the privileges to create any type of synonym. Which stalement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accesible to all users?. CREATE GLOBAL SYNONYM emp FOR hcm.employee_records;. CREATE SYNONYM emp FOR hcm.employee_records;. CREATE SYNONYM PUBLIC.emp FOR hcm.employee_records;. CREATE SYNONYM SYS.emp FOR hcm.employee_records;. CREATE PUBLIC SYNONYM emp FOR hcm. employee_records;.

Which three privileges can be restricted to a subset of columns in a table?. ALTER. REFERENCES. UPDATE. SELECT. INDEX. INSERT. DELETE.

You need to allow user ANDREW to: 1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table. 2. GRANT that permission to other users. Which statement will do this?. GRANT UPDATE ON customers.title, customers.address TO andrew;. GRANT UPDATE (title, address) ON customers TO andrew;. GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION;. GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;. GRANT UPDATE ON customers.title, customers.address TO andrew WITH GRANT OPTION;. GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;.

Examine these statements which execute successfully: CREATE USER finance IDENTIFIED BY pwfin; CREATE USER fin_manager IDENTIFIED BY pwngr; CREATE USER fin_clerk IDENTIFIED BY pwelerk; GRANT CREATE SESSION TO finance, fin_clerk; GRANT SELECT ON scott.emp TO finance WITH GRANT OPTION; CONNECT finance/pwfin GRANT SELECT ON scott.emp TO fin_clerk Which two are true? (Choose two.). User FIN_CLERK can grant SELECT on SCOTT.EMP to user FIN_MANAGER. Dropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK. User FINANCE can grant CREATE SESSION to user FIN_MANAGER. Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK. User FINANCE is unable to grant all on SCOTT.EMP to FIN_MANAGER.

Which two are true about the WITH GRANT OPTION clause?. The grantee can grant the object privilege to any user in the database, with of without including this option. The grantee must have the GRANT ANY OBJECT PRIVILEGE system prvilege to use this option. It can be used when granting privileges to roles. It can be used for system and object privileges. It cannot be used to pass on privileges to PUBLIC by the grantee. It can be used to pass on privileges to other users by the grantee.

In one of your databases, you create a user, HR, and then execute this command: GRANT CREATE SESSION TO hr WITH ADMIN OPTION; Which four actions can HR perform? (Choose four.). Revoke the CREATE SESSION privilege from other users. Revoke the CREATE SESSION privilege from user HR. Log in to the database instance. Grant the CREATE SESSION privilege with ADMIN OPTION to other users. Execute DDL statements in the HR schema. Execute DML statements in the HR schema.

SCOTT is a user in the database. Evaluate the commands issued by the DBA: 1 - CREATE ROLE mgr; 2 - GRANT CREATE TABLE, SELECT ON oe. orders TO mgr; 3- GRANT mgy, create table to SCOTT; Which statement is true regarding the execution of the above commands?. Statement 1 would not execute because the WITH GRANT option is missing. Statement 1 would not execute because the IDENTIFIED BY <password > clause is missing. Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command. Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.

Which statement correctly grants a system privilege?. GRANT CREATE VIEW ON table1 TO user1;. GRANT ALTER TABLE TO PUBLIC;. GRANT CREATE TABLE TO user1, user2;. GRANT CREATE SESSION TO ALL;.

Which three statements about roles are true? (Choose three.). Roles are assigned to users using the ALTER USER statement. Privileges are assigned to a role using the GRANT statement. A role is a named group of related privileges that can only be assigned to a user. A single user can be assigned multiple roles. Privileges are assigned to a role using the ALTER ROLE statement. Roles are assigned to roles using the ALTER ROLE statement. A single role can be assigned to multiple users.

Which two are true about granting privileges on objects? (Choose two.). An object privilege can be granted to other users only by the owner of that object. The owner of an object acquires all object privileges on that object by default. The WITH GRANT OPTION clause can be used only by DBA users. An object privilege can be granted to a role only by the owner of that object. A table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.

Which three are true about privileges and roles?. A role is owned by the user who created it. System privileges always set privileges for an entire database. All roles are owned by the SYS schema. A role can contain a combination of several privileges and roles. A user has all object privileges for every object in their schema by default. PUBLIC can be revoked from a user. PUBLIC acts as a default role granted to every user in a database.

Which three are true about system and object privileges. WITH GRANT OPTION can be used when granting an object privilege to both users and roles. WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC. Revoking a system privilege that was granted with the WITH ADMIN OPTION has a cascading effect. Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect. Adding a primary key constraint to an existing table in another schema requires a system privilege. Adding a foreign key constraint pointing to a table in another schema requires the REFERENCEs object privilege.

Which two are true about granting privilege on objects?. The owner of an object acquires all object privilege on that object by default. The WITH GRANT OPTION clause can be used only by DBA users. A table owner must grant the references privilege to allow other users to create FOREIGN KEY constraints using that table. An object privilege can be granted to a role only by the owner of that object. An object privilege can be granted to other users only by the owner of object.

MANAGER is an existing role with no privileges or roles. EMP is an existing role containing the CREATE TABLE privilege. EMPLOYEES is an existing table in the HR schema. Which two commands execute successfully? (Choose two.). GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION;. GRANT CREATE TABLE, SELECT ON hr. employees TO manager;. GRANT CREATE TABLE, emp TP manager;. GRANT CREATE SEQUENCE TO manager, emp;. GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager;.

Which two are true about granting object privileges on tables, views, and sequences? (Choose two.). INSERT can be granted only on tables and sequences. DELETE can be granted on tables, views, and sequences. SELECT can be granted on tables, views, and sequences. ALTER can be granted only on tables and sequences. REFERENCES can be granted only on tables.

Which two statements are true about *_TABLES views? (Choose two.). USER_TABLES displays all tables owned by the current user. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES. All users can query DBA_TABLES successfully.  You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in DBA_TABLES. ALL_TABLES displays all tables owned by the current user. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.

Which three statements are true?. All the dynamic performance views prefixed with v$ are accessible to all the database users. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access. The USER_OBJECTS view can provide information about the tables and views created by the user who queries the view. The USER_SYNONYMS view can provide information about private synonyms.

You currently have an active transaction in your session and have been granted SELECT access to V$TRANSACTION. Executing: SELECT xid, status FROM v$transaction; in your session returns: XID STATUS 0A0007000A070000 ACTIVATE In which three situations will re-executing this query still return a row but with a different XID, indicating a new transaction has started? (Choose three.). after successfully executing a CREATE TABLE statement followed by a CREATE INDEX statement. after successfully executing a TRUNCATE statement followed by a DML statement. after successfully executing a DML statement following a failed DML statement. after successfully executing a CREATE TABLE AS SELECT statement followed by a SELECT FOR UPDATE statement. after successfully executing a COMMIT or ROLLBACK followed by a DML statement. after successfully executing a COMMIT or ROLLBACK followed by a SELECT statement.

Which two are true about the data dictionary? (Choose two.). The data dictionary is constantly updated to reflect changes to database objects, permissions, and data. All user actions are recorded in the data dictionary. All users have permissions to access all information in the data dictionary by default. The SYS user owns all base tables and user-accessible views in the data dictionary. Base tables in the data dictionary have the prefix DBA_.

Which two statements cause changes to the data dictionary? (Choose two.). DELETE FROM scott.emp;. ALTER SESSION SET NLS_DATE_FORMAT = ‘DD/MM/YYYY’;. GRANT UPDATE ON scott.emp TO fin manager;. SELECT * FROM user_tab_prive;. TRUNCATE TABLE emp;.

Which three statements are true about the Oracle Data Dictionary?. Data dictionary views are created by joins of dictionary base tables and DBA-defined tables. The data dictionary is created and maintained by the database administrator. Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference the same base tables from the data dictionary. Base tables can be queried directly. It is owned by the SYSTEM user. Usernames of all users including database administrators are stored in the data dictionary.

Which statement is true regarding the SESSION_PRIVS dictionary view?. It contains the object privileges granted to other users by the current user session. It contains the system privileges granted to other users by the current User session. It contains the current system privileges available in the user session. It contains the current object privileges available in the user session.

An Oracle Database session has an uncommitted transaction in progress which updated 5000 rows in a table. In which three situations does the transaction complete thereby committing the updates? (Choose three.). When a CREATE TABLE AS SELECT statement is issued in the same session but fails with a syntax error. when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT. when the session logs out successfully. when a CREATE INDEX statement is executed successfully in the same session. when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT. when a COMMIT statement is issued by the same user from another session in the same database instance.

No user-defined locks are used in your database. Which three are true about Transaction Control Language (TCL)? (Choose three.). ROLLBACK without the TO SAVEPOINT clause undoes all the transaction’s changes, releases its locks, and erases all its savepoints. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction’s changes but does not release its locks. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction’s changes but does not erase its savepoints. ROLLBACK TO SAVEPOINT undoes the transaction’s changes made since the named savepoint and then ends the transaction. COMMIT ends the transaction and makes all its changes permanent. COMMIT erases all the transaction’s savepoints and releases its locks.

Which statement is true about transactions?. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a transaction. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction. Each Data Definition Language (DDL) statement executed forms a single transaction. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.

Which two are true about using the FOR UPDATE clause in a SELECT statement? (Choose two.). It can be used with SET operators (UNION, INTERSECT etc.). It cannot be used with the DISTINCT keyword. If the NOWAIT clause is added, the statement will automatically acquire locks from their owning transactions and not wait. The statement skips rows locked by other transactions. It can be used with joins.

Which statement is true about TRUNCATE and DELETE?. For tables with multiple indexes and triggers DELETE is faster than TRUNCATE. For large tables TRUNCATE is faster than DELETE. You can never TRUNCATE a table if foreign key constraints will be violated. You can never DELETE rows from a table if foreign key constraints will be violated.

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.). Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view. The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. Delete statements can always be done on a table through a view. Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword. Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view. Insert statements can always be done on a table through a view.

Which three statements are true about Data Manipulation Language (DML)?. Delete statements can remove multiple rows based on multiple conditions. Insert statements can insert nulls explicitly into a column. insert into. . .select. . .from statements automatically commit. DML statements require a primary key be defined on a table. update statements can have different subqueries to specify the values for each updated column.

Which two are true about savepoints? (Choose two.). After issuing a savepoint, you can roll back to the savepoint name within the current transaction. They make uncommitted updates visible to sessions owned by other users. You can commit updates done between two savepoints without committing other updates in the current transaction. A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error. They make uncommitted updates visible to other sessions owned by the same user. After issuing a savepoint, you cannot roll back the complete transaction.

Which two are true about rollbacks?. The ROLLBACK statement does not release locks resulting from table updates. Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back. A transaction interrupted by a system failure is automatically rolled back. If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted. Data consistency is not guaranteed after a rollback.

Which two statements are true about Oracle databases and SQL? (Choose two.). Updates performed by a database user can be rolled back by another user by using the ROLLBACK command. A query can access only tables within the same schema. The database guarantees read consistency at select level on user-created tables. A user can be the owner of multiple schemas in the same database. When you execute an update statement, the database instance locks each updated row.

Examine these statements executed in a single Oracle session: CREATE TABLE product (pcode NUMBER(2), pname VARCHAR2(20)); INSERT INTO product VALUES (1, ‘pen’); INSERT INTO product VALUES (2, ‘pencil’); INSERT INTO product VALUES (3, ‘fountain pen’); SAVEPOINT a; UPDATE product SET pcode = 10 WHERE pcode = 1; COMMIT; DELETE FROM product WHERE pcode = 2; SAVEPOINT b; UPDATE product SET pcode = 30 WHERE pcode = 3; SAVEPOINT c; DELETE FROM product WHERE pcode = 10; ROLLBACK TO SAVEPOINT b; COMMIT; Which three statements are true? (Choose three.). There is no row containing pencil. The code for pen is 10. There is no row containing fountain pen. The code for pen is 1. The code for fountain pen is 3. There is no row containing pen.

Which two statements are true regarding a SAVEPOINT? (Choose two.). Rolling back to a SAVEPOINT can undo a CREATE INDEX statement. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement. Only one SAVEPOINT may be issued in a transaction. A SAVEPOINT does not issue a COMMIT. Rolling back to a SAVEPOINT can undo a DELETE statement.

You execute these commands: CREATE TABLE customers (customer_id INTEGER, customer_name VARCHAR2 (20)) ; INSERT INTO customers VALUES (1, ‘Customer 1’); SAVEPOINT post_insert; INSERT INTO customers VALUES (2, ‘Customer 2’); <TODO> SELECT COUNT (*) FROM customers; Which two, used independently, can replace so the query returns 1?. ROLLBACK;. ROLLBACK TO SAVEPOINT post_insert;. ROLLBACK TO post_insert;. COMMIT;. COMMIT TO SAVEPOINT post_insert;.

Examine the description of the BOOKS table: The table has 100 rows. Examine this sequence of statements issued in a new session: INSERT INTO books VALUES (‘ADV112’, ‘Adventures of Tom Sawyer’, NULL, NULL); SAVEPOINT a; DELETE FROM books; ROLLBACK TO SAVEPOINT a; ROLLBACK; Which two statements are true? (Choose two.). The second ROLLBACK command replays the delete. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed. The second ROLLBACK command undoes the insert. The second ROLLBACK command does nothing.

Examine the description of the EMPLOYEES table: Which two statements will insert a row into the EMPLOYEES table? (Choose two.). INSERT INTO employees VALUES (101, ‘John’, ‘Smith’, 12000, SYSDATE);. INSERT INTO employees VALUES (101, ‘John’, ‘Smith’, 10, 12000, SYSDATE);. INSERT INTO employees (employee_id, salary, first_name, hiredate, last_name) VALUES (101, 12100, ‘John’, SYSDATE, ‘Smith’);. iNSERT INTO employees (employee_id, first_name, last_name, salary, hiresate) VALUES ( (SELECT 101, ‘John’, ‘Smith’. 12000, SYSDATE FROM dual) );. INSERT INTO employees SELECT 101, ‘John’, ‘Smith’, 12000, (SELECT SYSDATE FROM dual), 10 FROM dual;. INSERT INTO employees VALUES (101, ‘John’, ‘ ‘, 12000, SYSDATE, 10);.

View the Exhibit and examine the description of the tables. You execute this SQL statement: NSERT INTO sales VALUES ( 23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel _desc = ‘Direct Sales’), 12, 1, 500) ; Which three statements are true? (Choose three.). The statement will execute successfully and a new row will be inserted into the SALES table. A product can have a different unit price at different times. The statement will fail if a row already exists in the SALES table for product 23. The statement will fail because a subquery may not be contained in a VALUES clause. A customer can exist in many countries. The SALES table has five foreign keys.

Examine the SQL statement used to create the TRANSACTION table. SQL > CREATE TABLE transaction (trn_id char(2) primary key, Start_date date DEFAULT SYSDATE, End_date date NOT NULL); The value ‘A1’ does not exist for trn_id in this table. Which SQL statement successfully inserts a row into the table with the default value for START_DATE?. INSERT INTO transaction VALUES (‘A1’, DEFAULT, TO_DATE(DEFAULT +10)). INSERT INTO transaction VALUES (‘A1’, DEFAULT, TO_DATE(‘SYSDATE+10’)). INSERT INTO transaction (trn_id, end _date) VALUES (‘A1’, ‘10-DEC-2014’). INSERT INTO transaction (trn_id, start_date, end _date) VALUES (‘A1’, , ‘10-DEC-2014’).

You create a table by using this command: CREATE TABLE rate_list (rate NUMBER(6,2)); Which two are true about executing statements? (Choose two.). INSERT INTO rate_list VALUES (0.999) produces an error. INSERT INTO rate_list VALUES (-.9) inserts the value as -.9. INSERT INTO rate_list VALUES (87654.556) inserts the value as 87654.6. INSERT INTO rate_list VALUES (-10) produces an error. INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99. INSERT INTO rate_list VALUES (0.551) inserts the value as .55.

Examine the command to create the BOOKS table SQL> create table books(book id CHAR(6) PRIMARY KEY, title VARCHAR2(100) NOT NULL, publisher_id VARCHAR2(4), author_id VARCHAR2 (50)); The BOOK ID value 101 does not exist in the table. Examine the SQL statement. insert into books (book id title, author_id values (‘101’,’LEARNING SQL’,’Tim Jones’). It executes successfully and the row is inserted with a null PLBLISHER_ID. It executes successfully only if NULL is explicitly specified in the INSERT statement. It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement. It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.

Which two are true about multitable INSERT statements?. The conditional INSERT FIRST statement always inserts a row into a single table. The unconditional INSERT ALL statement must have the same number of columns in both the source and target tables. They can transform a row from a source table into multiple rows in a target table. The conditional INSERT ALL statement inserts rows into a singe table by aggregating source rows. They always use subqueries.

Which three are true about multitable INSERT statements? (Choose three.). They can be performed on views. They can insert each computed row into more than one table. They can be performed on relational tables. They can be performed only by using a subquery. They can be performed on external tables using SQL* Loader. They can be performed on remote tables.

Which two statements are true about conditional INSERT ALL? (Choose two.). Each row returned by the subquery can be inserted into only a single target table. A single WHEN condition can be used for multiple INTO clauses. Each WHEN condition is tested for each row returned by the subquery. It cannot have an ELSE clause. The total number of rows inserted is always equal to the number of rows returned by the subquery.

INSERT ALL WHEN order_total < 10000 THEN INTO small_ orders WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders WHEN order_total > 200000 AND order_total THEN INTO large_orders SELECT order_id, order_total, customer_id FROM orders; Evaluate the following statement. Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?. Each row is evaluated by the first WheN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses. The INSERT statement will return an error because the ELSE clause is missing. All rows are evaluated by all the three WHEN clauses. Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses.

Examine this statement which executes successfully: INSERT ALL WHEN SAL > 20000 THEN INTO special_sal VALUES (EMP_ID, SAL) ELSE INTO sal history VALUES (EMP_ID, HIREDATE, SAL) INTO mgr_history VALUES (EMP_ID, MGR, SAL) SELECT employee_id EMP_ID, hire_date HIREDATE, salary SAI, manager _id MGR FROM employees WHERE employee_id < 125; Which is true?. Only if the salary is 20000 or less and the employee id is 125 or higher, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table. Regardless of salary and employee id, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table. Regardless of salary, only if the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table. Only if the salary is 20000 or less and the employee id is less than 125, insert EMPLOYEE_ID, MANAGER_ID, and SALARY into the MGR_HISTORY table.

Which two are true about the MERGE statement? (Choose two.). The WHEN NOT MATCHED clause can be used to specify the deletions to be performed. The WHEN NOT MATCHED clause can be used to specify the updates to be performed. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed. The WHEN MATCHED clause can be used to specify the inserts to be performed. The WHEN MATCHED clause can be used to specify the updates to be performed.

Which three are true about the MERGE statement?. It can merge rows only from tables. It can use views to produce source rows. It can combine rows from multiple tables conditionally to insert into a single table. It can use subqueries to produce source rows. It can update the same row of the target table multiple times. It can update, insert, or delete rows conditionally in multiple tables.

Examine this description of the PRODUCTS table: NAME Null? Type ------------------ ------------- ------------------ PROD_ID NOT NULL NUMBER(2) QTY NUMBER(5,2) COST NUMBER(8,2) You successfully execute this command: CREATE TABLE new_prices (prod_id NUMBER(2), price NUMBER(8,2)) Which two statements execute without errors? (Choose two.). MERGE INTO new_prices n USING (SELECT * FROM products WHERE cost > 150) p ON (n.prod_id = p.prod_id) WHEN MATCHED THEN DELETE WHERE (p.cost < 200) WHEN NOT MATCHED THEN INSERT (n.prod_id, n.price) VALUES (p.prod_id, p.cost*.01);. MERGE INTO new_prices n USING (SELECT * FROM products) p WHEN MATCHED THEN UPDATE SET n.price = p.cost*.01 WHEN NOT MATCHED THEN INSERT (n.prod_id, n.price) VALUES (p.prod_id, cost*. 01) WHERE (p.cost < 200);. MERGE INTO new_prices n USING (SELECT * FROM products WHERE cost > 150) p ON (n.prod_id = p.prod_id) WHEN MATCHED THEN UPDATE SET n.price = p.cost*.01 DELETE WHERE (p.cost < 200);. MERGE INTO new_prices n USING products p ON (p.prod_id = n.prod_id) WHEN NOT MATCHED THEN INSERT (n.prod_id, n.price) VALUES (p.prod_id, cost*.01) WHERE (p.cost < 200);.

You want to return the current date and time from the user session, with a data type of TIMESTAMP WITH TIME ZONE. Which function will do this?. SYSDATE. CURRENT_TIMESTAMP. LOCALTIMESTAMP. CURRENT_DATE.

Which two statements are true about CURRENT_TIMESTAMP? (Choose two.). The value varies depending on the setting of SESSIONTIMEZONE. It returns a value of data type TIMESTAMP. The date is in the time zone of DBTIMEZONE. It returns the same date as CURRENT_TIME. The time is in the time zone of DBTIMEZONE. It always returns the same value as SYSTEMTIMESTAMP.

Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database?. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC). A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row. A TIMESTAMP data type column contains information about year, month, and day. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC). The CURRENT_TIMESTAMP function returns data without time zone information.

Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.). CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds. SYSDATE can be queried only from the DUAL table. CURRENT_DATE returns the current date and time as per the session time zone. SYSDATE can be used in expressions only if the default date format is DD-MON-RR. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.

Examine these statements which execute successfully: ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YYYY HH24 MI: SS’ ALTER SESSION SET TIME_ ZONE = ‘-5:00’; SELECT DBTIMEZONE, SYSDATE FROM DUAL Examine the result: DBTIMEZONE SYSDATE -------------------- ---------------------------- +00.00 11-jul-2019 11:00:00 If LOCALTIMESTAMP was selected at the same time what would it. 11-JUL-2019 6,00,00,00000000 AM - 05:00. 11-JUL-2019 11,00,00,00000000 AM. 11-JUL-2019 6,00,00,000000 AM. 11-JUL-2019 11,00,00,000000AM -05:00.

You have been tasked to create a table for a banking application. One of the columns must meet three requirements: 1) Be stored in a format supporting date arithmetic without using conversion functions 2) Store a loan period of up to 10 years 3) Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?. INTERVAL YEAR TO MONTH. TIMESTAMP WITH TIMEZONE. INTERVAL DAY TO SECOND. TIMESTAMP WITH LOCAL TIMEZONE. TIMESTAMP.

Examine this query: SELECT INTERVAL ‘100’ MONTH DURATION FROM DUAL; What will be the output? A. an error B. DURATION - ---------- +100 C. DURATION - ---------- +08 D. DURATION - ---------- +08-04 Which two queries execute successfully? (Choose two.). SELECT INTERVAL ‘1’ DAY - INTERVAL ‘1’ MINUTE FROM DUAL. SELECT SYSTIMESTAMP + INTERVAL ‘1’ DAY FROM DUAL;. SELECT INTERVAL ‘1’ DAY - SYSDATE FROM DUAL;. SELECT INTERVAL ‘1’ DAY + INTERVAL ‘1’ MONTH FROM DUAL;. SELECT SYSDATE * INTERVAL ‘1’ DAY FROM DUAL;.

Examine this incomplete query: SELECT DATE ‘2019-01-01’ + <INTERVAL CLAUSE> FROM DUAL; Which three clauses can replace <INTERVAL CLAUSE> to add 12 hours to the date?. INTERVAL '0.5' DAY. INTERVAL '720' MINUTE. INTERVAL '11:60' HOUR TO MINUTE. INTERVAL '12:00' HOUR TO SECOND. INTERVAL '0 12' DAY TO HOUR. INTERVAL '12' HOUR.

In your session, the NLS_DATE_FORMAT is DD-MM-YYYY. There are 86400 seconds in a day. Examine this result: DATE - ----------- 02-JAN-2020 Which statement returns this?. SELECT TO_CHAR(TO_DATE(‘29-10-2019’) + INTERVAL ‘2’ MONTH + INTERVAL ‘4’ DAY - INTERVAL ‘120’ SECOND, ‘DD-MON-YYYY’) AS "date" FROM DUAL;. SELECT TO_CHAR(TO_DATE(‘29-10-2019’) + INTERVAL ‘3’ MONTH + INTERVAL ‘7’ DAY - INTERVAL ‘360’ SECOND, ‘DD-MON-YYYY’) AS "date" FROM DUAL;. SELECT TO_CHAR(TO_DATE(‘29-10-2019’) + INTERVAL ‘2’ MONTH + INTERVAL ‘5’ DAY - INTERVAL ‘120’ SECOND, ‘DD-MON-YYYY’) AS "date" FROM DUAL;. SELECT TO_CHAR(TO_DATE(‘29-10-2019’) + INTERVAL ‘2’ MONTH + INTERVAL ‘5’ DAY - INTERVAL ‘86410’ SECOND, ‘DD-MON- YYYY’) AS "date" FROM DUAL;. SELECT TO_CHAR(TO_DATE(‘29-10-2019’) + INTERVAL ‘2’ MONTH + INTERVAL ‘6’ DAY - INTERVAL ‘120’ SECOND, ‘DD-MON-YYYY’) AS "date" FROM DUAL;.

Which statement executes successfully?. SELECT TO_NUMBER(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;. SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;. SELECT TO_NUMBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;. SELECT TO_DATE(TO_NUMBER(INTERVAL '800' SECOND)) FROM DUAL;. SELECT TO_DATE(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;.

You have been asked to create a table for a banking application. One of the columns must meet three requirements: 1: Be stored in a format supporting date arithmetic without using conversion functions 2: Store a loan period of up to 10 years 3: Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?. TIMESTAMP WITH TIMEZONE. TIMESTAMP. TIMESTAMP WITH LOCAL TIMEZONE. INTERVAL YEAR TO MONTH. INTERVAL DAY TO SECOND.

Which two statements are true about INTERVAL data types. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value. INTERVAL DAY TO SECOND columns support fractions of seconds. INTERVAL YEAR TO MONTH columns support yearly intervals.

Examine this query: SELECT INTERVAL '100' MONTH DURATION FROM DUAL; What will be the output?. DURATION +08-04. DURATION +100. DURATION +08. an error.

Which three are true about scalar subquery expressions? (Choose three.). They can be nested. They cannot be used in the VALUES clause of an INSERT statement. A scalar subquery expression that returns zero rows evaluates to zero. They can be used as default values for columns in a CREATE TABLE statement. A scalar subquery expression that returns zero rows evaluates to NULL. They cannot be used in GROUP BY clauses.

Which two are true about scalar subquery expressions?. They can return at most one row. You must enclose them in parentheses. You cannot correlate them with a table in the parent statement. They can return two columns. You can use them as a default value for a column.

Which three statements are true regarding single row subqueries?. They must be placed on the left side of the comparison operator or condition. They must return a row to prevent errors in the SQL statement. A SQL statement may have multiple single row subquery blocks. They can be used in the HAVING clause. They must be placed on the right side of the comparison operator or condition. They can be used in the clause.

View the Exhibit and examine the structure of the ORDER_ITEMS table You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table. Which query would produce the desired result?. SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);. SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id);. SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP BY order_id;. SELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity);.

Which three are true about subqueries? (Choose three.). A subquery cannot be used in the select list. < ANY returns true if the argument is less than the highest value returned by the subquery. < ANY returns true if the argument is less than the lowest value returned by the subquery. A subquery can be used in a HAVING clause. A subquery cannot be used in a FROM clause. A subquery can be used in a WHERE clause. = ANY can only evaluate the argument against a subquery if it returns two or more values.

Which three statements are true reading subqueries? (Choose three.). Only one column or expression can be compared between the subquery and main query. Multiple columns or expressions can be compared between the subquery and main query. A subquery can have more than one main query. A Main query can have many subqueries. The subquery and main query must retrieve date from the same table. The subquery and main query can retrieve data from different tables.

Which two statements are true regarding the execution of the correlated subqueries?. The nested query executes first and then the outer query executes. The nested query executes after the outer query returns the row. Each row returned by the outer query is evaluated for the results returned by the inner query. The outer query executes only once for the result returned by the inner query.

Which two statements are true regarding multiple-row subqueries? (Choose two.). They can contain group functions. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery. They can be used to retrieve multiple rows from a single table only. They use the < ALL operator to imply less than the maximum. They always contain a subquery within a subquery.

Which three statements are true about multiple-row subqueries?. They can contain a subquery within a subquery. They can return multiple columns as well as rows. They cannot contain a subquery within a subquery. They can return only one column but multiple rows. They can contain group functions and GROUP BY and HAVING clauses. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Which three statements are true about multiple row subqueries? (Choose three.). Two or more values are always returned from the subquery. They can contain HAVING clauses. They can contain GROUP BY clauses. They can return multiple columns. They cannot contain a subquery.

Examine this SQL statement: UPDATE orders o SET customer_name = (SELECT cust_last_name FROM customers WHERE customer_id = o.customer_id) ; Which two are true? (Choose two.). All existing rows in the ORDERS table are updated. The subquery is executed before the UPDATE statement is executed. The subquery is not a correlated subquery. The subquery is executed for every updated row in the ORDERS table. The UPDATE statement executes successfully even if the subquery selects multiple rows.

Examine this SQL statement: DELETE FROM EMPLOYEES E WHERE EXISTS (SELECT ‘dummy’ FROM emp_history WHERE employee_id = e. employee_id); Which two are true? (Choose two.). The DELETE statement executes successfully even if the subquery selects multiple rows. The subquery is executed before the DELETE statement is executed. The subquery is not a correlated subquery. All existing rows in the EMPLOYEES table are deleted. The subquery is executed for every row in the EMPLOYEES table.

Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY. Only the EMPLOYEES_ID column is indexed. Rows exist for employees 100 and 200. Examine this statement: UPDATE employees SET (job_id, salary) = (SELECT job_id, salary FROM employees WHERE employee_id = 200) WHERE employee id=100; Which two statements are true?. Employees 100 and 200 will have the same SALARY as before the update command. Employee 100 will have SALARY set to the same value as the SALARY of employee 200. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200. Employees 100 and 200 will have the same JOB ID as before the update command. Employee 200 will have SALARY set to the same value as the SALARY of employee 100. Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100.

Which two are true about a SQL statement using SET operators such as UNION? (Choose two.). The data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query. The names and number of columns must be identical for all select statements in the query. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query. The number, but not names, of columns must be identical for all select statements in the query.

Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)? (Choose two.). The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list. None of the set operators can be used when selecting CLOB columns. There must be an equal number of columns in each SELECT list. Each SELECT statement in the query can have an ORDER BY clause. The FOR UPDATE clause cannot be specified.

Which statement is true about the INTERSECT operator used in compound queries?. Multiple INTERSECT operators are not possible in the same SQL statement. It processes NULLs in the selected columns. INTERSECT is of lower precedence than UNION or UNION ALL. It ignores NULLs.

Which two are true about queries using set operators such as UNION? (Choose two.). In a query containing multiple set operators, INTERSECT always takes precedence over UNION and UNION ALL. An expression in the first SELECT list must have a column alias for the expression. All set operators are valid on columns of all data types. CHAR columns of different lengths used with a set operator return a VARCHAR2 whose length equals the longest char value. Queries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric).

Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.). Duplicates are eliminated automatically by the UNION ALL operator. The number of columns selected in each SELECT statement must be identical. The names of columns selected in each SELECT statement must be identical. The output is sorted by the UNION ALL operator. NULLS are not ignored during duplicate checking.

Which two statements are true about the results of using the INTERSECT operator in compound queries? (Choose two.). The number of columns in each SELECT in the compound query can be different. INTERSECT returns rows common to both sides of the compound query. INTERSECT ignores NULLs. Columns named in each SELECT in the compound query can be different. Reversing the order of the intersected tables can sometimes affect the output.

Which statement is true regarding the UNION operator?. Null values are not ignored during duplicate checking. By default, the output is not sorted. The number of columns selected in all select statements need not be the same. Names of all columns must be identical across all select statements.

Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement?. the first VARCHAR2 column in the first SELECT of the compound query. the first column in the first SELECT of the compound query. the first NUMBER column in the first SELECT of the compound query. the first NUMBER or VARCHAR2 column in the last SELECT of the compound query. the first column in the last SELECT of the compound query.

Examine this statement: SELECT cust_id, cust_last_name "Last Name" FROM customers WHERE country_id = 10 UNION SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30 Identify three ORDER BY clauses, any one of which will complete the query successfully. (Choose three.). ORDER BY CUST_NO. ORDER BY 2, cust_id. ORDER BY 2, 1. ORDER BY "Last Name". ORDER BY "CUST_NO".

Which three statements are true about a self join? (Choose three.). It must be an equijoin. The ON clause must be used. It must be an inner join. It can be an outer join. The ON clause can be used. The query must use two different aliases for the table.

Which two statements are true about a self join?. The join key column must have an index. It can be a left outer join. It must be a full outer join. It can be an inner join. It must be an equijoin.

What is true about non-equijoin statement performance? (Choose two.). Table aliases can improve performance. The BETWEEN condition always performs better than using the >= and <= conditions. The join syntax used makes no difference to performance. The BETWEEN condition always performs less well than using the >= and <= conditions. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.

Which two are true about the USING clause when joining tables?. All column names in a USING clause must be qualified with a table name or table alias. It can never be used with o natural join. It is used to specify an equijoin of columns that have the same name in both tables. It can never be used with a full outer join. It is used to specify an explicit join condition involving operators.

Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.). The Oracle join syntax supports creation of a Cartesian product of two tables. The Oracle join syntax only supports right outer joins. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax. The Oracle join syntax supports natural joins. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax. The SQL:1999 compliant ANSI join syntax supports natural joins.

Which three statements are true about inner and outer joins? (Choose three.). A full outer join returns matched and unmatched rows. Outer joins can be used when there are multiple join conditions on two tables. A full outer join must use Oracle syntax. Outer joins can only be used between two tables per query. A left or right outer join returns only unmatched rows. An inner join returns matched rows.

Which two statements are true about outer Joins?. The outer join operator (+) can be used on both sides of the join condition in an outer join. An outer join is used to retrieve only the rows that do not meet the join condition. The IN operator cannot be used in a condition that Involves an outer join. A condition representing an outer join cannot be linked to another condition using the or logical operator. The outer join operator (+) is used next to the column of the table without the matching rows.

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables: CREATE TABLE DEPARTMENT_DETAILS (DEPARTMENT_ID NUMBER PRIMARY KEY, DEPARTMENT_NAME VARCHAR2 (50), MOD VARCHAR2(50)); CREATE TABLE COURSE_DETAILS (COURSE_ID NUMBER PRIMARY KEY, COURSE_NAME VARCHAR2(50), DEPARTMENT_ID VARCHAR2(50)); You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use?. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c.department_id);. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d.department_id);. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);.

Examine this query: SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3; What is returned upon execution?. 0 rows. an error. 8 rows. 6 rows. 1 row. 3 rows.

Examine the description of EMPLOYEES table: Name Null? Type ----------------------- ------------------ ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) EMPLOYEE_NAME NOT NULL VARCHAR2(100) SALARY NOT NULL NUMBER COMMISION NUMBER Which three queries return all rows for which SALARY + COMMISSION is greater than 20000?. SELECT * FROM employees WHERE NVL2(salary + commission, salary + commission, salary) >= 20000;. SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000;. SELECT * FROM employees WHERE NVL(salary + commission, 0) >= 20000;. SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;. SELECT * FROM employees WHERE COALESCE(salary, commission) >= 20000;. SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;.

Which two will execute successfully? (Choose two.). SELECT COALESCE(0, SYSDATE) FROM DUAL;. SELECT NVL('DATE', SYSDATE) FROM DUAL;. SELECT COALESCE('DATE', SYSDATE) FROM DUAL;. SELECT NVL('DATE', 200) FROM (SELECT NULL AS "DATE" FROM DUAL);. SELECT COALESCE('DATE', SYSDATE) FROM (SELECT NULL AS "DATE" FROM DUAL);.

Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.). NVL must have expressions of the same data type. NVL can have any number of expressions in the list. NVL2 can have any number of expressions in the list. COALESCE stops evaluating the list of expressions when it finds the first non-null value. The first expression in NVL2 is never returned. COALESCE stops evaluating the list of expressions when it finds the first null value.

Which two queries execute successfully? (Choose two.). SELECT NULLIF (NULL, 100) FROM DUAL;. SELECT NULLIF (100, ‘A’) FROM DUAL;. SELECT NULLIF (100, 100) FROM DUAL;. SELECT COALESCE (100, NULL, 200) FROM DUAL;. SELECT COALESCE (100, ‘A’) FROM DUAL;.

The CUSTOMERS table has a CUST_CREDT_LIMIT column of data type number. Which two queries execute successfully?. SELECT TO_CHAR(NVL(cust_credit_limit * .15,’Not Available’)) FROM customers;. SELECT NVL2(cust_credit_limit * .15,’Not Available’) FROM customers;. SELECT NVL(cust_credit_limit * .15,’Not Available’) FROM customers;. SLECT NVL(TO_CHAR(cust_credit_limit * .15),’Not available’) from customers;. SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),’NOT Available’) FROM customers;.

In your session NLS_DATE_FORMAT is set to DD-MON-RR. Which two queries display the year as four digits? (Choose two.). SELECT TO_DATE(SYSDATE, ‘RRRR-MM-DD’) FROM DUAL;. SELECT TO_CHAR(SYSDATE, ‘MM/DD/YYYY’) FROM DUAL;. SELECT TO_DATE(ADD_MONTHS(SYSDATE, 6), ‘dd-mon-yyyy’) FROM DUAL;. SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6)) FROM DUAL;. SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6), ‘dd-mon-yyyy’) FROM DUAL;. SELECT TO_DATE(TO_CHAR(SYSDATE, ‘MM/DD/YYYY’), ‘MM/DD/YYYY’) FROM DUAL;.

You need to calculate the number of days from 1st January 2019 until today. Dates are stored in the default format of DD-MON-RR. Which two queries give the required output?. SELECT SYSDATE-TO_DATE (‘01-JANUARY-2019’) FROM DUAL;. SELECT TO_DATE (SYSDATE, ‘DD/MONTH/YYYY’)-’01/JANUARY/2019’ FROM DUAL;. SELECT ROUND (SYSDATE-TO_DATE (‘01/JANUARY/2019’)) FROM DUAL;. SELECT TO_CHAR (SYSDATE, ‘DD-MON-YYYY’)-’01-JAN-2019’ FROM DUAL;. SELECT ROUND (SYSDATE- ‘01-JAN-2019’) FROM DUAL;.

Which two statements execute successfully? (Choose two.). SELECT TO_DATE('2019-DEC-25 15:30', 'YYYY-MON-DD HH24:MI', 'NLS_DATE_LANGUAGE = AMERICAN') FROM DUAL;. SELECT TO_CHAR(TO_DATE('2019-DEC-25 03:30', 'YYYY-MON-DD HH12:MI')) FROM DUAL;. SELECT TO_DATE(TO_CHAR('2019-DEC-25 03:30', 'YYYY-MON-DD HH24:MI')) FROM DUAL;. SELECT TO_CHAR('2019-DEC-25 15:30', 'YYYY-MON-DD HH12:MI') FROM DUAL;. SELECT TO_CHAR('2019-DEC-25 15:30', 'YYYY-MON-DD HH12:MI', 'NLS_DATE_LANGUAGE=AMERICAN') FROM DUAL;.

You execute this query: SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), ‘MON’), ‘dd "Monday for" fmMonth rrrr’) FROM DUAL; What is the result?. It executes successfully but does not return any result. It returns the date for the first Monday of the next month. It returns the date for the last Monday of the current month. It generates an error.

The SYSDATE function displays the current Oracle Server date as: 21-MAY-19 You wish to display the date as - MONDAY, 21 MAY, 2019 - Which statement will do this?. SELECT TO_DATE(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;. SELECT TO_CHAR(SYSDATE, 'FMDD, DAY MONTH, YYYY') FROM DUAL;. SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;. SELECT TO_CHAR(SYSDATE, 'FMDAY, DDTH MONTH, YYYY') FROM DUAL;.

The ORDERS table has a column ORDER_DATE of data type DATE. The default display format for a date is DD-MON-RR. Which two WHERE conditions demonstrate the correct usage of conversion functions? (Choose two.). WHERE order_date > TO_DATE(‘JUL 10 2018’, ‘ MON DD YYYY’). WHERE order_date > TO_DATE(ADD_MONTH(SYSDATE, 6), ‘MON DD YYYY’). WHERE order_date IN (TO_DATE(‘Oct 21 2018’, ‘Mon DD YYYY’), TO_CHAR(‘Nov 21 2018’, ‘Mon DD YYYY’)). WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), ‘MON DD YYYY’). WHERE TO_CHAR(order_date, ‘MON DD YYYY’) = ‘JAN 20 2019’.

Which two are true about the precedence of operators and conditions? (Choose two.). || has a higher order of precedence than + (addition). + (addition) has a higher order of precedence than * (multiplication). NOT has a higher order of precedence than AND and OR in a condition. AND and OR have the same order of precedence in a condition. Operators are evaluated before conditions.

Which two statements are true about the rules of precedence for operators?. Arithmetic operators with equal precedence are evaluated from left to right within an expression. Multiple parentheses can be used to override the default precedence of operators in an expression. The + binary operator has the highest precedence in an expression in a SQL statements. NULLS influence the precedence of operators in an expression. The concatenation operator || is always evaluated before addition and subtraction in an expression.

Examine this query: SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES WHERE HIRE_DATE > '&1'; Which two methods should you use to prevent prompting for a hire date value when this query is executed? (Choose two.). Use the DEFINE command before executing the query. Replace '&1' with '&&1' in the query. Use the UNDEFINE command before executing the query. Execute the SET VERIFY OFF command before executing the query. Execute the SET VERIFY ON command before executing the query. Store the query in a script and pass the substitution value to the script when executing it.

You want to write a query that prompts for two column names and the where condition each time it is executed in a session but only prompts for the table name the first time it is executed. The variables used in your query are never undefined in your session. Which query can be used?. SELECT &&col1, &&col2 FROM &table WHERE &&condition = &&cond;. SELECT ‘&&col1’, ‘&&col2’ FROM &table WHERE ‘&&condition’ = ‘&cond’;. SELECT &&col1, &&col2 FROM &table WHERE &&condition;. SELECT &col1, &col2 FROM &&table WHERE &condition;. SELECT &col1, &col2 FROM "&table" WHERE &condition;.

Which is true about the & and && prefixes with substitution variables? (Choose all that apply.). Both & and && can prefix a substitution variable name in queries and DML statements. An & prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice. & can prefix a substitution variable name only in queries. An && prefix to an undefined substitution variable, which is referenced multiple times in multiple queries, will prompt for a value once per query. The && prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

Which two statements are true about substitution variables? (Choose two.). A substitution variable can be used with any clause in a SELECT statement. A substitution variable used to prompt for a column name must be enclosed in double quotation marks. A substitution variable used to prompt for a column name must be enclosed in single quotation marks. A substitution variable prefixed with & always prompts only once for a value in a session. A substitution variable can be used only in a SELECT statement. A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session.

Which two statements are true about the SET VERIFY ON command? (Choose two.). It displays values for variables used only in the WHERE clause of a query. It displays values for variables created by the DEFINE command. It can be used only in SQL*Plus. It displays values for variables prefixed with &&. It can be used in SQL Developer and SQL*Plus.

Which set of commands will prompt only once for the name of the table to use in the query?. PROMPT Enter table name &x - SELECT employee_id FROM &x WHERE last_name = ‘King’;. DEFINE x = ‘employees’ PROMPT Enter table name &x - SELECT employee_id FROM &x WHERE last_name = ‘King’. PROMPT Enter table name &x - SELECT employee_id FROM &&x WHERE last_name = ‘King’;. PROMPT Enter table name &&x - SELECT employee_id FROM &x WHERE last_name = ‘King’;.

You execute these commands: SQL> DEFINE hiredate = ‘01-APR-2011’; SQL> SELECT employee_id, first_name, salary FROM employees WHERE hire_date > &hiredate AND manager_id >&mgr_id; For which substitution variables will you be prompted?. none. &hiredate and &mgr_id. only &hiredate. only &mgr_id.

Which two statements are true about the ORDER BY clause? (Choose two.). In a character sort, the values are case-sensitive. NULLS are not included in the sort operation. Numeric values are displayed in descending order if they have decimal positions. Column aliases can be used in the ORDER BY clause. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.

Which three statements are true about single-row functions? (Choose three.). They return a single result row per table. They can be nested to any level. They can accept only one argument. The argument can be a column name, variable, literal or an expression. They can be used only in the WHERE clause of a SELECT statement. The data type returned can be different from the data type of the argument.

Which two statements are true? (Choose two.). CASE is a function and DECODE is not. Neither CASE nor DECODE is a function. All conditions evaluated using CASE can also be evaluated using DECODE. All conditions evaluated using DECODE can also be evaluated using CASE. DECODE is a function and CASE is not. Both CASE and DECODE are functions.

In the PROMOTIONS table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query? (Choose two.). TO_NUMBER(PROMO_BEGIN_DATE) – 5 will return a number. PROMO_BEGIN_DATE – SYSDATE will return a number. PROMO_BEGIN_DATE – SYSDATE will return an error. PROMO_BEGIN_DATE – 5 will return a date. TO_DATE(PROMO_BEGIN_DATE * 5) will return a date.

Which three statements are true about Structured Query Language (SQL)? (Choose three.). It requires that data be contained in hierarchical data storage. It best supports relational databases. It provides independence for logical data structures being manipulated from the underlying physical data storage. It is the only language that can be used for both relational and object-oriented databases. It guarantees atomicity, consistency, isolation, and durability (ACID) features. It is used to define encapsulation and polymorphism for a relational table.

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.). Rows from unrelated tables cannot be joined. Relating data from a table with data from the same table is implemented with a self join. Implementing a relationship between two tables might require joining additional tables. Every relationship between the two tables must be implemented in a join condition. An inner join relates rows within the same table.

Which three are key components of an Entity Relationship Model? (Choose three.). an activity. a table. a relationship. an attribute. a unique identifier. an entity.

Examine this business rule: Each student can work on multiple projects and each project can have multiple students. You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format: STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true? (Choose two.). PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the students and projects entities. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into one-to-many relationships. The ER must have a one-to-many relationship between the STUDENTS and PROJECTS entities.

Which three statements are true about the DESCRIBE command? (Choose three.). It can be used from SQL Developer. It displays the PRIMARY KEY constraint for any column or columns that have that constraint. It displays all constraints that are defined for each column. It displays the NOT NULL constraint for any columns that have that constraint. It can be used only from SQL* Plus. It can be used to display the structure of an existing view.

Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.). WHERE and HAVING clauses can be used in the same statement only if applied to different table columns. The WHERE clause can be used to exclude rows after dividing them into groups. The HAVING clause can be used with aggregating functions in subqueries. The WHERE clause can be used to exclude rows before dividing them into groups. Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query.

Which statement is true about using functions in WHERE and HAVING?. Using aggregate functions in the WHERE clause requires a subquery. Using aggregate functions in the HAVING clause requires a subquery. Using single-row functions in the WHERE clause requires a subquery. Using single-row functions in the HAVING clause requires a subquery.

Which three statements are true about an ORDER BY clause? (Choose three.). By default an ORDER BY clause sorts rows in descending order. An ORDER BY clause will always precede a HAVING clause if both are used in the same top-level query. An ORDER BY clause always sorts NULL values last. By default an ORDER BY clause sorts rows in ascending order. An ORDER BY clause can perform a binary sort. An ORDER BY clause can perform a linguistic sort.

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?. SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;.

Which two queries will execute successfully? (Choose two.). SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id HAVING hire_date > ‘01-JAN-19’;. SELECT dept_id, SUM(salary) FROM employees WHERE hire_date > ‘01-JAN-19’ GROUP BY dept_id;. SELECT dept_id, MAX(SUM(salary)) FROM employees GROUP BY dept_id;. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;. SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;.

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?. SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;.

Which GROUP BY clause must be added so the query returns the results shown?. GROUP BY ch.channel_type, ROLLUP(t.month, co.country_code);. GROUP BY ch.channel_type, t.month, ROLLUP(co.country_code);. GROUP BY CUBE(ch.channel_type, t.month, co.country_code);. . GROUP BY ch.channel_type, t.month, co.country_code;.

Which two are SQL features? (Choose two.). providing graphical capabilities. processing sets of data. providing database transaction control. providing update capabilities for data in external files. providing variable definition capabilities.

Which two SQL statements execute successfully?. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;. SELECT customer_id AS ‘CUSTOMER-ID’,transaction_date AS DATE, amount+100 ‘DUES’ from transactions;. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount+100 DUES FROM transactions;. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;. SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;.

Examine the description of the ORDER_ITEMS table: Examine this incomplete query: SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY <clause>; Which two can replace so the query completes successfully?. quantity * unit_price. quantity. total_paid. product_id. quantity, unit_price.

Which two statements are true about the DUAL table?. It can display multiple rows and columns. It can be accessed only by the SYS user. It can be accessed by any user who has the SELECT privilege in any schema. It can display multiple rows but only a single column. It consists of a single row and single column of VARCHAR2 data type. It can be used to display only constants or pseudo columns.

Examine the description of the PROMOTIONS table: You want to display the unique promotion costs in each promotion category. Which two queries can be used? (Choose two.). SELECT DISTINCT promo_category || ‘ has ‘ || promo_cost AS COSTS FROM promotions ORDER BY 1;. SELECT DISTINCT promo_cost || ‘ in ‘ || DISTINCT promo_category FROM promotions ORDER BY 1;. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;. SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;. SELECT promo_cost, promo_category FROM promotions ORDER BY 1;.

Examine the description of the EMPLOYEES table: Which two statements will run successfully? (Choose two.). SELECT ‘The first_name is ‘‘ || first_name || ‘‘ FROM employees;. SELECT ‘The first_name is ‘‘‘ || first_name || ‘‘‘‘ FROM employees;. SELECT ‘The first_name is ‘‘‘ || first_name || ‘‘‘ FROM employees;. SELECT ‘The first_name is ‘ || first_name || ‘‘ FROM employees;. SELECT ‘The first_name is \’’ || first_name || ‘\’’ FROM employees;.

Examine the description of the PRODUCT_STATUS table: The STATUS column contains the values IN STOCK or OUT OF STOCK for each row. Which two queries will execute successfully?. SELECT prod_id || q"‘s not available" FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id || q’(‘s not available)’ ‘CURRENT AVAILABILITY’ FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id q’s not available" FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id "CURRENT AVAILABILITY" || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id || q’(‘s not available)’ "CURRENT AVAILABILITY" FROM product_status WHERE status = ‘OUT OF STOCK’;.

Which two queries return the string Hello! We’re ready? (Choose two.). SELECT "Hello! We’re ready" FROM DUAL;. SELECT ‘Hello! We’re ready’ FROM DUAL;. SELECT q’!Hello! We’re ready!’ FROM DUAL;. SELECT q’[Hello! We’re ready]’ FROM DUAL;. SELECT ‘Hello! We\’re ready’ ESCAPE ‘\’ FROM DUAL;.

Which two statements are true about the COUNT function? (Choose two.). COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column. It can only be used for NUMBER data types. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

Examine the description of the PRODUCTS table: Which three queries use valid expressions? (Choose three.). SELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;. SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;. SELECT product_id, expiry_date * 2 FROM products;. SELECT product_id, unit_price, unit_price + surcharge FROM products;.

Examine the description of the BOOKS_TRANSACTIONS tables Examine this partial SQL statement: SELECT * FROM books_transactions Which two WHERE conditions give the same result?. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND (member_id IN(‘A101’,’A102’));. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ OR member_id IN(‘A101’,’A102’));. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND member_id = ‘A101’ OR member_id = ‘A102’);. WHERE borrowed_date = SYSDATE AND transaction_type = ‘RM’ OR member_id IN(‘A101’,’A102’);. WHERE (borrowed_date = SYSDATE AND transaction_type = ‘RM’) OR member_id IN (‘A101’,’A102’);.

The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted. You only want to display details of rows where START_DATE is within the last 25 months. Which WHERE clause can be used?. WHERE TO_NUMBER(start_date - SYSDATE) <= 25. WHERE MONTHS_BETWEE(start_date, SYDATE) <=25. WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25. WHERE ADD_MONTHS(start_date, 25) <= SYSDATE.

Which two statements will return the names of the three employees with the lowest salaries?. SELECT last_name, salary FROM employees WHERE ROWNUM<=3;. SELECT last_name,salary FROM employees ORDER BY salary FETCH FIRST 3 ROWS ONLY;. SELECT last_name,salary FROM employees WHERE ROWNUM<=3 ORDER BY (SELECT salary FROM employees);. SELECT last_name,salary FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM<=3;. SELECT last_name,salary FROM employees FETCH FIRST 3 ROWS ONLY ORDER BY salary;.

Which two SQL statements execute successfully?. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount+100 "DUES" from transactions;. SELECT customer_id AS ‘CUSTOMER-ID’,transaction_date AS DATE, amount+100 ‘DUES’ from transactions;. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount+100 DUES FROM transactions;. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount+100 DUES FROM transactions;. SELECT customer id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount+100 "DUES AMOUNT" FROM transactions;.

Examine the description of the ORDER_ITEMS table: Examine this incomplete query: SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY <clause>; Which two can replace so the query completes successfully?. quantity * unit_price. quantity. total_paid. product_id. quantity, unit_price.

You create a table named 123. Which statement runs successfully?. SELECT * FROM TABLE(123);.  SELECT * FROM "123";. SELECT * FROM \’123\’;. SELECT * FROM ‘123’;.

Examine the description of the EMPLOYEES table: Which two statements will run successfully? (Choose two.). SELECT 'The first_name is '' || first_name || '' FROM employees;. SELECT 'The first_name is ''' || first_name || '''' FROM employees;. SELECT 'The first_name is ''' || first_name || ''' FROM employees;. SELECT 'The first_name is ' || first_name || '' FROM employees;. SELECT 'The first_name is \'' || first_name || '\'' FROM employees;.

Examine the description of the PRODUCT_STATUS table: The STATUS column contains the values IN STOCK or OUT OF STOCK for each row. Which two queries will execute successfully?. SELECT prod_id || q"‘s not available" FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id || q’(‘s not available)’ ‘CURRENT AVAILABILITY’ FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id q’s not available" FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id "CURRENT AVAILABILITY" || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;. SELECT prod_id || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;.

Which two statements are true about the COUNT function? (Choose two.). COUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column. It can only be used for NUMBER data types. COUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column. COUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

Examine the description of the PRODUCTS table: Which three queries use valid expressions? (Choose three.). SELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;. SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;. SELECT product_id, expiry_date * 2 FROM products;. SELECT product_id, unit_price, unit_price + surcharge FROM products;.

Examine the description of the EMPLOYEES table: Which statement will execute successfully, returning distinct employees with non-null first names?. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name <> NULL;. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name IS NOT NULL;. SELECT DISTINCT * FROM employees WHERE first_name IS NOT NULL;. SELECT DISTINCT * FROM employees WHERE first_name <> NULL;.

Examine the description of the BOOKS_TRANSACTIONS tables Examine this partial SQL statement: SELECT * FROM books_transactions Which two WHERE conditions give the same result?. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND (member_id IN(‘A101’,’A102’));. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ OR member_id IN(‘A101’,’A102’));. WHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND member_id = ‘A101’ OR member_id = ‘A102’);. WHERE borrowed_date = SYSDATE AND transaction_type = ‘RM’ OR member_id IN(‘A101’,’A102’);. WHERE (borrowed_date = SYSDATE AND transaction_type = ‘RM’) OR member_id IN (‘A101’,’A102’);.

Examine the description of the CUSTOMERS table: For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed. Which query should be used?. 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;. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level !=NULL;. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL;. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;. 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;.

Which two statements will return the names of the three employees with the lowest salaries?. SELECT last_name, salary FROM employees WHERE ROWNUM<=3. SELECT last_name,salary FROM employees ORDER BY salary FETCH FIRST 3 ROWS ONLY;. SELECT last_name,salary FROM employees WHERE ROWNUM<=3 ORDER BY (SELECT salary FROM employees);. SELECT last_name,salary FROM (SELECT * FROM employees ORDER BY salary) WHERE ROWNUM<=3;. SELECT last_name,salary FROM employees FETCH FIRST 3 ROWS ONLY ORDER BY salary;.

Examine the description of the MEMBERS table: Examine the partial query: SELECT city, last_name AS lname FROM members ...; You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order. Which two clauses must you add to the query? (Choose two.). ORDER BY 1, 2. ORDER BY 1, lname DESC. WHERE city IN (‘%AN%’). WHERE city = ‘%AN%’. WHERE city LIKE ‘%AN%’. ORDER BY last_name DESC, city ASC.

Which two queries will result in an error? (Choose two.) Examine the description of the EMPLOYEES table: SELECT first name last name FROM employees;. SELECT first name, last name FROM employees;. SELECT last_name, 12 * salary AS annual_salary FROM employees WHERE annual_salary > 100000 ORDER BY 12 * salary;. SELECT last_name, 12 * salary AS annual_salary FROM employees WHERE 12 * salary > 100000 ORDER BY 12 * salary;. SELECT last_name, 12 * salary AS annual_salary FROM employees WHERE annual_salary > 100000 ORDER BY annual_salary;. SELECT last_name, 12 * salary AS annual_salary FROM employees WHERE 12 * salary > 100000 ORDER BY annual_salary;.

Examine the description of the BOOKS table: Name Null? Type --------------- ---------------- --------------- BOOK_ID NOT NULL NUMBER(4) BOOK_TITLE VARCHAR2(250) PRICE NUMBER(5,2) PURCHASE_DATE DATE AUTHOR_NAME VARCHAR2(30) 1. Display book titles for books purchased before January 17, 2007 costing less than 500 or more than 1000. 2. Sort the titles by date of purchase, starting with the most recently purchased book. Which two queries can be used? (Choose two.). SELECT book_title FROM books WHERE (price < 500 OR > 1000) AND (purchase_date < ‘17-JAN-2007’) ORDER BY purchase_date DESC;. SELECT book_title FROM books WHERE (price BETWEEN 500 AND 1000) | AND (purchase_date < ‘17-JAN-2007’) ORDER BY purchase_date;. SELECT book_title FROM books WHERE (price NOT BETWEEN 500 AND 1000) AND (purchase_date < ‘17-JAN-2007’) ORDER BY purchase_date DESC;. SELECT book_title FROM books WHERE (price IN (500, 1000)) AND (purchase_date < ‘17-JAN-2007’) ORDER BY purchase_date ASC;. SELECT book_title FROM books WHERE (price < 500 OR price > 1000) AND (purchase_date < ‘17-JAN-2007’) ORDER BY purchase_date DESC;.

Examine the description of EMPLOYEES table: Name Null? Type ----------------------- ------------------ ---------------- EMPLOYEE_ID NOT NULL NUMBER(6) EMPLOYEE_NAME NOT NULL VARCHAR2(100) SALARY NOT NULL NUMBER COMMISION NUMBER Which three queries return all rows for which SALARY + COMMISSION is greater than 20000?. SELECT * FROM employees WHERE NVL2(salary + commission, salary + commission, salary) >= 20000;. SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000. SELECT * FROM employees WHERE NVL(salary + commission, 0) >= 20000;. SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;. SELECT * FROM employees WHERE COALESCE(salary, commission) >= 20000;. SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;.

Examine the contents of the EMP table: ID NAME SALARY 101 John 26000 102 Neena 24000 103 De Haan 12000 104 Lex 17000 105 Bil 18000 106 Daniel 26000 107 Ben 12000 108 George 25000 Examine this query that executes successfully: SELECT ID, NAME, SALARY FROM EMP ORDER BY SALARY FETCH FIRST 5 ROWS WITH TIES; What is the result?. It will return the five employees earning the lowest salaries, in ascending order. It will return the six employees earning the highest salaries, in descending order. It will return the six employees earning the lowest salaries, in ascending order. It will return the five employees earning the highest salaries, in descending order.

Examine the description of the CUSTOMERS table: You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters. Which query can be used?. SELECT * FROM customers WHERE city LIKE ‘D__%’;. SELECT * FROM customers WHERE city = ‘%D__’;. SELECT * FROM customers WHERE city = ‘D__%’;. SELECT * FROM customers WHERE city LIKE ‘D__’;.

Which two are true? (Choose two.). CONCAT joins two character strings together. CONCAT joins two or more character strings together. FLOOR returns the largest positive integer less than or equal to a specified number. INSTR finds the offset within a character string, starting from position 0. INSTR finds the offset within a string of a single character only. FLOOR returns the largest integer less than or equal to a specified number.

Which two statements are true about single row functions?. CONCAT: can be used to combine any number of values. MOD: returns the quotient of a division operation. CEIL: can be used for positive and negative numbers. FLOOR: returns the smallest integer greater than or equal to a specified number. TRUNC: can be used with NUMBER and DATE values.

Examine the data in the CUST_NAME column of the CUSTOMERS table: CUST_NAME --------------------------- Renske Ladwing Jason Mallin Samuel McCain Allan MCEwen Irene Mikkikineni Julia Nayer You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result? (Choose two.). WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ‘ ‘) + 1)) LIKE ‘Mc%’;. WHERE SUBSTR(cust_name, INSTR(cust_name, ‘ ‘) + 1) LIKE ‘Mc%’;. WHERE SUBSTR(cust_name, INSTR(cust_name, ‘ ‘) + 1 ) LIKE ‘Mc%’ OR ‘MC%’;. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ‘ ‘) + 1)) LIKE UPPER(‘MC%’);. WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ‘ ‘) + 1)) IN (‘MC%’, ‘Mc%);.

Examine the description of the CUSTOMERS table: Which two SELECT statements will return these results: (Choose two.) CUSTOMER_NAME --------------------------- Mandy Mary. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE ‘MA*’;. SELECT customer_name FROM customers WHERE customer_name = ‘*Ma*’;. SELECT customer_name FROM customers WHERE customer_name = ‘*Ma*’;. SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE ‘MA%’;. SELECT customer_name FROM customers WHERE customer_name LIKE ‘%a%’;. SELECT customer_name FROM customers WHERE customer_name LIKE ‘Ma%’;. SELECT customer_name FROM customers WHERE customer_name LIKE ‘*Ma*’;.

The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8,2). Evaluate this SQL statement: SELECT TO_CHAR(unit_price, ‘$9,999’) FROM product_information; Which two statements are true about the output? (Choose two.). A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,0236. A row whose UNIT_PRICE column contains the value 1023.95 will be displayed as $1,024. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,023. A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as #######. A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

Examine the description of the CUSTOMERS table: You need to display last names and credit limits of all customers whose last name starts with A or B in lower or upper case, and whose credit limit is below 1000. Examine this partial query: SELECT cust_last_name, cust_credit_limit FROM customers Which two WHERE conditions give the required result?. WHERE UPPER(cust_last_name) IN (‘A%’, ‘B%’) AND cust_credit_limit < 1000;. WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND ROUND(cust_credit_limit) < 1000;. WHERE UPPER(cust_last_name) BETWEEN UPPER(‘A%’ AND ‘B%’) AND ROUND(cust_credit_limit) < 1000;. WHERE (INITCAP(cust_last_name) LIKE ‘A%’ OR INITCAP(cust_last_name) LIKE ‘B%’) AND cust_credit_limit < 1000;. WHERE (UPPER(cust_last_name) LIKE INITCAP(‘A’) OR UPPER(cust_last_name) LIKE INITCAP(‘B’)) AND ROUND(cust_credit_limit) < ROUND(1000);.

Which two statements will convert the string Hello world to ello world?. SELECT LOWER(SUBSTR('Hello World', 2, 1)) FROM DUAL;. SELECT LOWER(SUBSTR('Hello World', 2)) FROM DUAL;. SELECT LOWER(TRIM('H' FROM 'Hello World')) FROM DUAL;. SELECT SUBSTR('Hello World', 2) FROM DUAL;. SELECT INITCAP(TRIM('H' FROM 'Hello World')) FROM DUAL;.

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose COST_LAST_MANE values are Anderson and Ausson. Which query produces output for CUST_LAST_SAME containing Oder for the first row and Aus for the second?. SELECT REPLACE (REPLACE(cust_last_name,’son’,’’),’An’,’O’) FROM customers;. SELECT REPLACE (TRIM(TRALING’son’ FROM cust_last_name),’An’,’O’) FROM customers;. SELECT INITCAP (REPLACE(TRIM(‘son’ FROM cust_last_name),’An’,’O’)) FROM customers;. SELECT REPLACE (SUBSTR(cust_last_name,-3),’An’,’O’) FROM customers;.

Examine this query: SELECT SUBSTR(SYSDATE, 1, 5) "Result" FROM DUAL; Which statement is true?. It fails unless the expression is modified to SUBSTR(TO_CHAR(SYSDATE), 1, 5). It fails unless the expression is modified to SUBSTR(TO_CHAR(TRUNC(SYSDATE), 1, 5). It fails unless the expression is modified to TO_CHAR(SUBSTR(SYSDATE), 1, 5)). It executes successfully with an implicit data type conversion.

Which is true about the ROUND, TRUNC and MOD functions?. TRUNC(MOD(25,3),-1) is invalid. ROUND(MOD(25,3),-1) is invalid. ROUND(MOD(25,3),-1) and TRUNC(MOD(25,3),-1) are both valid and give the same result. ROUND(MOD(25,3),-1) and TRUNC(MOD(25,3),-1) are both valid but give different results.

Examine the ORDER_ITEMS table: Which two queries return rows where QUANTITY is a multiple of ten? (Choose two.). SELECT * FROM order_items WHERE quantity / 10 - TRUNC(quantity);. SELECT * FROM order_items WHERE MOD(quantity, 10) - 0;. SELECT * FROM order_items WHERE FLOOR(quantity / 10) = TRUNC(quantity / 10);. SELECT * FROM order_items WHERE quantity = TRUNC(quantity, -1);. SELECT * FROM order_items WHERE quantity = ROUND(quantity, 1);.

Examine this list of queries: 1. SELECT ROUND(TRUNC(15.193, 1)) “Results” FROM DUAL; (15) 2. SELECT ROUND(15.193, -1) “Results” FROM DUAL; (20) 3. SELECT TRUNC(15.193, -1) “Results” FROM DUAL; (10) 4. SELECT TRUNC(ROUND(15.193, 1)) “Results” FROM DUAL; (15) Which two statements are true? (Choose two.). 1 and 4 give different results. 2 returns the value 20. 3 returns an error. 1 and 4 give the same result. 2 and 3 give the same result.

Examine this query: SELECT TRUNC(ROUND(156.00,-2),-1) FROM DUAL; What is the result?. 150. 200. 160. 100. 16.

Examine the description of the EMPLOYEES table: For each employee in department 90 you want to display: 1. their last name 2. the number of complete weeks they have been employed The output must be sorted by the number of weeks, starting with the longest serving employee first. Which statement will accomplish this?. SELECT last_name, ROUND ((SYSDATE - hire date) / 7) AS tenure FROM employees WHERE department_id = 90 ORDER BY tenure DESC;. SELECT last_name, TRUNC ((SYSDATE - hire_date) / 7) As tenure FROM employees WHERE delpartment_ id = 90 ORDER BY tenure DESC;. SELECT last_name, ROUND ((SYSDATE - hire date) / 7) AS tenure FROM employees WHERE department_id = 90 ORDER BY tenure;. SELECT last_name, TRUNC ((SYSDATE - hire date) / 7) AS tenure FROM employees WHERE department_id = 90 ORDER BY tenure;.

Which two are true? (Choose two.). ADD_MONTHS adds a number of calendar months to a date. CEIL requires an argument which is a numeric data type. CEIL returns the largest integer less than or equal to a specified number. LAST_DAY returns the date of the last day of the current month only. LAST_DAY returns the date of the last day of the month for the date argument passed to the function. LAST_DAY returns the date of the last day of the previous month only.

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE. You want to display the date of the first Monday after the completion of six months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week. Which query can be used?. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM employees;. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), ‘MONDAY’) FROM employees;.

Which three queries execute successfully? (Choose three.). SELECT 1 - SYSDATE - DATE ‘2019-01-01’ FROM DUAL;. SELECT SYSDATE - DATE ‘2019-01-01’ - 1 FROM DUAL;. SELECT SYSDATE / DATE ‘2019-01-01’ - 1 FROM DUAL;. SELECT SYSDATE - 1 - DATE ‘2019-01-01’ FROM DUAL;. SELECT (SYSDATE - DATE ‘2019-01-01’) / 1 FROM DUAL;. SELECT 1 / SYSDATE - DATE ‘2019-01-01’ FROM DUAL;.

A session’s NLS_DATE_FORMAT is set to DD Mon YYYY. Which two queries return the value 1 Jan 2019? (Choose two.). SELECT TO_DATE('2019-01-01') FROM DUAL;. SELECT DATE '2019-01-01' FROM DUAL;. SELECT '2019-01-01' FROM DUAL;. SELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;. SELECT TO_CHAR('2019-01-01') FROM DUAL;.

Examine the description of the EMPLOYEES table: NLS_DATE_FORMAT is set to DD-MON-YY. Which query requires explicit data type conversion?. SELECT join_date FROM employees WHERE join_date > ‘10-02-2018’;. SELECT join_date || ‘ ‘ || salary FROM employees;. SELECT salary + ‘120.50’ FROM employees;. SELECT join_date + ‘20’ FROM employees;. SELECT SUBSTR(join_date, 1, 2) - 10 FROM employees;.

The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE. NLS_DATE_FORMAT is set to DD-MON-RR. Which two are true about data type conversions involving these columns in query expressions? (Choose two.). invoice_date = ‘15-march-2019’ : uses implicit conversion. qty_sold BETWEEN ‘101’ AND ‘110’ : uses implicit conversion. invoice_date > ‘01-02-2019’ : uses implicit conversion. qty_sold = ‘0554982’ : requires explicit conversion. CONCAT (qty_sold, invoice_date) : requires explicit conversion.

Examine the description of EMPLOYEES table: Name Null? Type EMPLOYEE_NAME NOT NULL VARCHAR2 (5) HIRE_DATE DATE SALARY NUMBER (7, 2) The session time zone is the same as the database server. Which two statements will list only the employees who have been working with the company for more than five years? (Choose two.). SELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 12 > 5;. SELECT employee_name FROM employees WHERE (SYSTIMSSTAMF - hire_date) / 12 > INTERVAL '5' YEAR;. SELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date) / 12 > 5. SELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date) / 365 > 5. SELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 365 > 5;. SELECT employee_name FROM employees WHERE (SYSTIMESTAMP - hire_date) / 365 > INTERVAL '1825' DAY;.

Examine the description of the CUSTOMERS table: Name Null Type ----------------------------- ----------------- ------------------- CUSTOMER_ID NOT NULL NUMBER(38) CUSTOMER_NAME NOT NULL VARCHAR2(100) INSERT_DATE NOT NULL DATE Which two statements will do an implicit conversion? (Choose two.). SELECT * FROM customers WHERE customer_id = ‘0001’;. SELECT * FROM customers WHERE customer_id = 0001;. SELECT * FROM customers WHERE insert_date = DATE ‘2019-01-01’;. SELECT * FROM customers WHERE insert_date = ‘01-JAN-19’;. SELECT * FROM customers WHERE TO_CHAR(customer_id) = ‘0001’;.

Examine the description of the PRODUCT_INFORMATION table: Examine the description of the PRODUCT_INFORMATION table: Which query retrieves the number of products with a null list price?. SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price is NULL;. SELECT COUNT(list_price) FROM product_information WHERE list_price = NULL;. SELECT COUNT(list_price) FROM product_information WHERE list_price IS NULL;. SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price IS NULL;.

Examine this partial statement: SELECT ename, sal, comm FROM emp Now examine this output: Which ORDER BY clause will generate the displayed output?. ORDER BY comm DESC NULLS LAST, ename. ORDER BY NVL(coram, 0) ASC NULLS FIRST, ename. ORDER BY NVL(coram, 0) ASC NULLS LAST, ename. ORDER BY NVL(ccmm, 0) DESC, ename.

You write this failing statement: SELECT dept_no AS department_id, MAX (salary) As max_sal FROM employees WHERE salary >10000 GROUP BY department_id ORDER BY max_sal; Which clause causes the error?. ORDER BY. WHERE. GROUP BY. SELECT.

Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER Statement 1: SELECT MAX (unit price*quantity) "Maximum Order” FROM order items; Statement 2: SELECT MAX (unit price*quantity) "Maximum order" FROM order items GROUP BY order id; Which two statements are true?. Statement 2 returns only one row of output. Both the statement given the same output. Both statements will return NULL if either UNIT PRICE or QUANTITY contains NULL,. Statement 2 may return multiple rows of output. Statement 1 returns only one row of output.

Which statement is true about aggregate functions?. The AVG function implicitly converts NULLS to zero. The MAX and MIN functions can be used on columns with character data types. Aggregate functions can be used in any clause of a SELECT statement. Aggregate functions can be nested to any number of levels.

Examine the description of the EMPLOYEES table: Which two queries return the highest salary in the table?. SELECT MAX(salary) FROM employees - GROUP By department_id - HAVING MAX(salary) = MAX(MAX(salary));. SELECT MAX(salary) FROM employees - GROUP By department_id;. SELECT department_id, MAX(salary) FROM employees - GROUP By department_id;. SELECT MAX(salary) FROM employees;. SELECT MAX(MAX(salary)) FROM employees - GROUP By department_id;.

Examine this description of the EMP table: You execute this query: SELECT deptno AS "departments", SUM(sal) AS "salary" FROM emp GROUP BY 1 HAVING SUM(sal) > 3000; What is the result?. only departments where the total salary is greater than 3000, returned in no particular order. only departments where the total salary is greater than 3000, ordered by department. all departments and a sum of the salaries of employees with a salary greater than 3000. an error.

The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? (Choose two.). SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10;. SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) > 10;. SELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;. SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10;. SELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;.

You execute this query: SELECT deptno AS “Department”, AVG(sal) AS AverageSalary, MAX(sal) AS “Max Salary” FROM emp WHERE sal >=12000 GROUP BY “Department” ORDER BY AverageSalary; Why does an error occur?. An alias name must not contain space characters. An alias name must always be specified in quotes. An alias name must not be used in an ORDER BY clause. An alias name must not be used in a GROUP BY clause.

Examine the description of the EMPLOYEES table: Which statement will fail?. SELECT department_id, COUNT (*) FROM employees WHEN department_id <> 90 AND COUNT(*) = 3 GROUP BY department_id;. SELECT department_id, COUNT (*) FROM employees HAVING department_id <> 90 AND COUNT (*) >= 3; GROUP BY department_id;. SELECT department_id, COUNT(*) FROM employees WHERE department_id <> 90 HAVING COUNT (*) > 3 GROUP BY department_id;. SELECT department_id, COUNT (*) FROM employees WHERE department_id <> 90 GROUP BY department_id HAVING COUNT (*) >= 3;.

Examine this data in the EMPLOYEES table: Which statement will execute successfully?. SELECT dept_id, INSTR(last_name, ‘A’), SUM(salary) FROM employees GROUP BY dept_id;. SELECT dept_id, STDDEV(last_name), SUM(salary) FROM employees GROUP BY dept_id;. SELECT dept_id, LENGTH(last_name), SUM(salary) FROM employees GROUP BY dept_id;. SELECT dept_id, MAX(last_name), SUM(salary) FROM employees GROUP BY dept_id;.

Examine the description of the CUSTOMERS table: CUSTNO is the PRIMARY KEY. You must determine if any customers’ details have been entered more than once using a different CUSTNO, by listing all duplicate names. Which two methods can you use to get the required result? (Choose two.). self join. RIGHT OUTER JOIN with self join. LEFT OUTER JOIN with self join. FULL OUTER JOIN with self join. subquery.

Examine the description of the EMPLOYEES table: Which two queries return rows for employees whose manager works in a different department? (Choose two.). SELECT emp. * FROM employees emp WHERE manager_id NOT IN ( SELECT mgr. employee_id FROM employees mgr WHERE emp.department_id <> mgr.department_id );. SELECT emp.* FROM employees emp JOIN employees mgr ON emp.manager_id = mgr.employee_id AND emp.department_id <> mgr.department_id;. SELECT emp.* FROM employees emp LEFT JOIN employees mgr ON emp.manager_id = mgr.employee_id AND emp.department_id <> mgr.department_id;. 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 );. 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;.

Examine the BRICKS table: COLOUR SHAPE WEIGHT -------------- --------------- -------------- Red cube 5 Red cylinder 10 Blue cube 15 Blue cylinder 20 You write this query: SELECT * FROM BRICKS B1 CROSS JOIN BRICKS B2 WHERE B1.WEIGHT < B2.WEIGHT; How many rows will the query return?. 4. 6. 16. 0. 1. 10.

Examine data in the BRICKS table: SHAPE WEIGHT --------------- --------- cube 5 cuboid 10 cylinder 15 Examine the BOXES table: Nombre Tipo ---------------- ------------ BOX_SIZE VARCHAR2(20) MIN_WEIGHT NUMBER(38) MAX_WEIGHT NUMBER(38) Which two queries only return CUBE? (Choose two.). SELECT shape FROM bricks JOIN boxes ON weight BETWEEN min_weight AND max_weight;. SELECT shape FROM bricks JOIN boxes ON weight > min_weight;. SELECT shape FROM bricks JOIN boxes ON weight >= min_weight AND weight < max_weight;. SELECT shape FROM bricks JOIN boxes ON weight < max_weight;. SELECT shape FROM bricks JOIN boxes ON NOT (weight > max_weight);.

SELECT * FROM bricks,colors; Which two statements are true?. You can add an ON clause with a join condition. You can add a WHERE clause with filtering criteria. It returns the number of rows in BRICKS plus the number of rows in COLORS. You can add a USING clause with a join condition. It returns the same rows as SELECT * FROM bricks CROSS JOIN colors.

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables: CREATE TABLE DEPARTMENT_DETAILS (DEPARTMENT_ID NUMBER PRIMARY KEY, DEPARTMENT_NAME VARCHAR2 (50), MOD VARCHAR2(50)); CREATE TABLE COURSE_DETAILS (COURSE_ID NUMBER PRIMARY KEY, COURSE_NAME VARCHAR2(50), DEPARTMENT_ID VARCHAR2(50)); You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use?. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c.department_id);. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d.department_id);. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);.

View the exhibit and examine the data in the PROJ_TASK_DETAILS table. The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them. The BASED_ON column indicates dependencies between tasks. Some tasks do not depend on the completion of other tasks. You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends. Which query would give the required result?. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p LEFT OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p FULL OUTER JOIN proj_task_details d ON (p.based_on = d.task_id);. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.task_id = d.task_id);. SELECT p.task_id, p.based_on, d.task_in_charge FROM proj_task_details p JOIN proj_task_details d ON (p.based_on = d.task_id);.

Examine the data in the COLORS table: RGB_HEX_VALUE COLOR_NAME FF0000 red 00FF00 green 0000FF blue Examine the data in the BRICKS table: BRICK_ID COLOR_RGB_HEX_VALUE 1 FF0000 2 00FF00 3 FFFFFF Which two queries return all the rows from COLORS? (Choose two.). SELECT * FROM BRIKS b RIGHT JOIN COLORS c ON b.COLOR_RGB_HEX_VALUE = c.RGB_HEX_VALUE;. SELECT * FROM BRIKS b FULL JOIN COLORS c ON b.COLOR_RGB_HEX_VALUE = c.RGB_HEX_VALUE;. SELECT * FROM c FULL JOIN BRIKS b USING(RGB_HEX_VALUE);. SELECT * FROM COLORS c LEFT JOIN BRIKS b ON b.COLOR_RGB_HEX_VALUE = c.RGB_HEX_VALUE WHERE b.brick_id > 0 ;. SELECT * FROM BRIKS b LEFT JOIN COLORS c ON b.COLOR_RGB_HEX_VALUE = c.RGB_HEX_VALUE;.

Now examine this query: SELECT ename, dname FROM emp CROSS JOIN dept WHERE job = ‘MANAGER’ AND dept.deptno IN (10, 20); How many rows will be displayed?. 64. 6. 3. 12.

View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks require subqueries? (Choose two.). Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE. Display suppliers whose PROD_LIST_PRICE is less than 1000. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable. Display the total number of products supplied by supplier 102 which have a product status of obsolete. Display the minimum PROD_LIST_PRICE for each product status.

Examine the description of the EMPLOYEES table: Which two queries return all rows for employees whose salary is greater than the average salary in their department? (Choose two.). SELECT * FROM employees WHERE salary > AVG(salary) OVER (PARTITION BY department_id);. SELECT * FROM employees e1 WHERE salary > ( SELECT AVG (salary) FROM employees e2 WHERE e1.department_id = e2.department_id) ;. SELECT * FROM employees WHERE salary> ( SELECT AVG (salary) FROM employees GROUP BY department_id) ;. SELECT * FROM employees WHERE salary > ANY ( SELECT AVG (salary) FROM employees GROUP BY department_id ) ;. SELECT * FROM ( SELECT e.*, AVG(salary) OVER (PARTITION BY department_id) avg_sal FROM employees e ) WHERE salary > avg_sal;.

View the exhibit and examine the structure of the PROMOTIONS table. You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the ‘INTERNET’ category. Which query would give you the required output?. SELECT promo _name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category= ‘INTERNET’);. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category= ‘INTERNET’);. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin _date > ALL (SELECT MAX (promo_begin_date) FROM promotions) AND promo_category= ‘INTERNET’;. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = ‘INTERNET’);.

Which query returns the currencies in CURRENCIES that are not present in INVOICES?. SELECT * FROM currencies WHERE NOT EXISTS ( SELECT NULL FROM invoices WHERE currency_code = currency_code );. SELECT * FROM currencies MINUS SELECT * FROM inovoices;. SELECT currency_code FROM currencies MINUS SELECT currency_code FROM invoices;. SELECT currency_code FROM currencies INTERSECT SELECT currency_code FROM invoices;.

Which two queries execute successfully? (Choose two.). SELECT brick id, shape FROM bricks MINUS SELECT weight, color FROM bricks_stage;. SELECT * FROM bricks MINUS SELECT * FROM bricks_stage;. SELECT shape, color FROM bricks MINUS SELECT weight, color FROM bricks_stage;. SELECT shape, color FROM bricks MINUS SELECT color, shape FROM bricks_stage;. SELECT shape, color, weight FROM bricks MINUS SELECT * FROM bricks_stage;.

Which two queries execute successfully?. SELECT prod_id FROM products - UNION ALL - SELECT prod_id, prod_name FROM new_products;. SELECT prod_id, exp_date FROM products UNION ALL - SELECT prod_id, NULL FROM new_products;. SELECT * FROM products - MINUS - SELECT prod_id, FROM new_products;. SELECT prod_id, prod_name FROM products INTERSECT - SELECT 100, prod_name FROM new_products;. SELECT * FROM products - UNION - SELECT * FROM new_products;.

Which three statements execute successfully? (Choose three.). SELECT * FROM orders ORDER BY order_id INTERSECT - SELECT * FROM invoices ORDER BY invoice_id;. (SELECT * FROM orders UNION ALL - SELECT * FROM invoices) ORDER BY order_id;. SELECT order_id, order_date FROM orders UNION ALL - SELECT invoice_id, invoice_date FROM invoices ORDER BY order_id;. SELECT * FROM orders - MINUS - SELECT * FROM invoices ORDER BY 1;. SELECT order_id invoice_id, order_date FROM orders MINUS - SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;. SELECT * FROM orders ORDER BY order_id UNION - SELECT * FROM invoices;. SELECT order_id, order_date FROM orders INTERSECT - SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;.

Examine the description of the SALES1 table: SALES2 is a table with the same description as SALES1. Some sales data is duplicated in both tables. You want to display the rows from the SALES1 table which are not present in the SALES2 table. Which set operator generates the required output?. INTERSECT. UNION ALL. UNION. SUBTRACT. MINUS.