Master SQL Certification 3.0
Q.23 - PROD_NAME CHAR (4) EXP_DATE TIMESTAMP (6) NEW_PRODUCTS: Name Null? Type PROD_ID CHAR (4) PROD NAME VARCHAR2 (10) EXP_DATE DATE Which two queries execute successfully? A) SELECT prod_id FROM products UNION ALL SELECT prod_id, prod_name FROM new products; B) SELECT prod_id, prod_name FROM products INTERSECT SELECT 100, prod_name FROM new products, C) SELECT prod_id, exp_date FROM products UNION ALL SELECT prod_id, NULL FROM new_products: D) SELECT * FROM products UNION SELECT * FROM new products; E) SELECT * FROM products MINUS SELECT prod_id FROM new_products;
BC
Q.31 - The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER. Which two queries execute successfully? A) SELECT COUNT (prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id; B) SELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) > 10; C) SELECT prod_id FROM sales WHERE quantity sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10; D) SELECT COUNT (prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000; E) SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10;
AB
Q.41- Which two statements are true about the COUNT function? A) COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column. B) COUNT (*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column. C) It can only be used for NUMBER data types. D A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause. E) COUNT (inv_amt) returns the number of rows in a table including rows with NULL in the INV AMT column.
AB
Q.5 - Evaluate these commands which execute successfully: CREATE SEQUENCE ord_seg INCREMENT BY 1 START WITH 1 MAX VALUE 10000 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? A) Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ. B) 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. C) Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times. D) If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS. E) Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.
AB
Q.64 - 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? A) the LOCATION clause B) the DEFAULT DIRECTORY clause C) the REJECT LIMIT clause D) the ACCESS PARAMETERS clause E) the access driver TYPE clause
AB
Q.15 - Which three are true about granting object privileges on tables, views, and sequences? A) ALTER can be granted only on tables and sequences. B) REFERENCES can be granted only on tables and views C) UPDATE can be granted only on tables and views. D) DELETE can be granted on tables, views, and sequences. E) INSERT can be granted on tables, views, and sequences. F) SELECT can be granted only on tables and views.
ABC
Q.29 - Which three statements are true about single-row functions? A) The data type returned can be different from the data type of the argument. B) The argument can be a column name, variable, literal or an expression. C) They can be nested to any level. D) They return a single result row per table. E) They can accept only one argument. F) They can be used only in the WHERE clause of a SELECT statement.
ABC
Q.8 - An Oracle database server 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? A) when the session logs out successfully B) when a CREATE INDEX statement is executed successfully in the same session C) when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT D) when a COMMIT statement is issued by the same user from another session in the same database Instance E) when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT F) when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session
ABC
Q.58 - Examine this description of the PRODUCTS table: Name Null? Type PROD ID NOT NULL VARCHAR2 (6) QUANTITY NUMBER (8, 2) PRICE NUMBER (10, 2) EXPIRY DATE DATE Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode. Which three commands execute successfully on PRODUCTS? A) ALTER TABLE products DROP UNUSED COLUMNS; B) CREATE INDEX price_idx ON products (price); C) TRUNCATE TABLE products; D) DROP TABLE products; E) ALTER TABLE products SET UNUSED (expiry_date) ; F) ALTER TABLE products DROP COLUMN expiry_date:
ABD
Q.6 - Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? A) A TIMESTAMP data type column contains information about year, month, and day. B) The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC). C) The CURRENT_TIMESTAMP function returns data without time zone information. D) The SESSIONTIME ZONE function can return an offset from Universal Coordinated Time (UTC). E) A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row.
ABE
Q.75 - Which three statements are true about Data Manipulation Language (DML)? A) UPDATE statements can have different subqueries to specify the values for each updated column. B) INSERT statements can insert NULLS explicitly into a column. C) DML statements require a primary key be defined on a table. D) INSERT INTO... SELECT... FROM statements automatically commit. E) DELETE statements can remove multiple rows based on multiple conditions.
ABE
Q.11 - In which three situations does a new transaction always start? A) when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session B) when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session C) when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session D) when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session E) when issuing a TRUNCATE statement after a SELECT statement was issued in the same session F) when issuing a DML statement after a DML statement failed in the same session
ABF
Q.27 - Which three statements are true about dropping and unused columns in an Oracle database? A) Partition key columns cannot be dropped. B) A column that is set to UNUSED still counts towards the limit of 1000 columns per table. C) An UNUSED column's space is reclaimed automatically when the row containing that column is next queried. D) A DROP COLUMN command can be rolled back. E) An UNUSED column's space is reclaimed automatically when the block containing that column is next queried. F) A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
ABF
Q.12 - Which two are true about the data dictionary? A) The SYS user owns all base tables and user-accessible views in the data dictionary. B) All user actions are recorded in the data dictionary. C) The data dictionary is constantly updated to reflect changes to database objects, permissions, and data. D) All users have permission to access all information in the data dictionary by default. E) Base tables in the data dictionary have the prefix DBA_.
AC
Q.14 - 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? A) SELECT ROUND (SYSDATE - TO_DATE ( '~01/JANUARY/2019' )) FROM DUAL; B) SELECT TO_DATE (SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL; C) SELECT SysDATE "" TO_DATE ( '01-JANUARY-2019' ) FROM DUAL; D) SELECT ROUND (SYSDATE - '01-JAN-2019' ) FROM DUAL; E) SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY' ) - '01-JAN-2019' FROM DUAL;
AC
Q.29 - Which two statements are true about conditional INSERT ALL? A) A single WHEN condition can be used for multiple INTO clauses. B) Each row returned by the subquery can be inserted into only a single target table. C) Each WHEN condition is tested for each row returned by the subquery. D) The total number of rows inserted is always equal to the number of rows returned by the subquery. E) It cannot have an ELSE clause.
AC
Q10. JEDITPOTUIT UN CIC CUSTOMERS COU Name Null? Type CUST ID NOT NULL VARCHAR2 (2) CUST_LAST_NAME VARCHAR2 (30) CITY VARCHAR2 (10) CUST_CREDIT_LIMIT NUMBER (6, 2) 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? A) WHERE (UPPER (cust_last_name) LIKE 'A%' OR UPPER (cust_last_name) LIKE 'B%') AND ROUND (cust_credit limit) < 1000; B) WHERE UPPER (cust_last_name) IN ('A%' , 'B%') AND cust_credit_limit < 1000; C) WHERE (INITCAP (cust_last_name) LIKE 'A%' OR INITCAP (cust_last_name) LIKE 'B%') AND cust_credit_limit < 1000; D) WHERE UPPER (cust_last_name) BETWEEN UPPER ('A%' AND 'B%') AND ROUND (cust_credit_limit) < 1000; E) WHERE (UPPER (cust_last_name) LIKE INITCAP ('A') OR UPPER (cust_last_name) LIKE INITCAP ('B')) AND ROUND (cust_credit_limit) < ROUND (1000);
AC
Q.34 - Which three are true about the MERGE statement? A) It can combine rows from multiple tables conditionally to insert into a single table. B) It can update, insert, or delete rows conditionally in multiple tables. C) It can use subqueries to produce source rows. D) It can use views to produce source rows. E) It can merge rows only from tables. F) It can update the same row of the target table multiple times.
ACD
Q.38 - Which three statements are true about defining relations between tables in a relational database? A) Every foreign key value must refer to a matching primary or unique key value. B) Every primary or unique key value must refer to a matching foreign key value. C) Foreign key columns allow null values. D) Unique key columns allow null values. E) Primary key columns allow null values.
ACD
Q.4 - Which three privileges can be restricted to a subset in a table? A) UPDATE B) SELECT C) INSERT D) INDEX E) REFERENCES F) DELETE G) ALTER
ACE
Q.49 - Examine the description of the TRANSACTIONS table: Name Null? Type TRANSACTION ID NOT NULL VARCHAR2 (6) TRANSACTION DATE DATE AMOUNT NUMBER (10,2) CUSTOMER_ID VARCHAR2 (6) Which two SQL statements execute successfully? A) SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions; B) SELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 'DUES' FROM transactions; C) SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions; D) SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions, E) SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;
AD
Q.70 - Which two are true about a SQL statement using SET operators such as UNION? A) 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. B) 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. C) The names and number of columns must be identical for all SELECT statements in the query. D) The number, but not names, of columns must be identical for all SELECT statements in the query. E) 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.
AD
Q.72 - Examine the description of the BOOKS_TRANSACTIONS table: Name Null? Type TRANSACTION ID NOT NULL VARCHAR2 (6) TRANSACTION TYPE VARCHAR2 (3) BORROWED_DATE DATE BOOK ID VARCHAR2 (6) MEMBER ID VARCHAR2 (6) Examine this partial SQL statement: SELECT * FROM books_transactions Which two WHERE conditions give the same result? A) WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101', 'A102'); B) WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR member_id IN ('A101', 'A102')); C) WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101 OR member_id = 'A102'); D) WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101', 'A102'); E) WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'));
AD
Q.22 - 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? A) DELETE FROM orders WHERE order_total < 1000; B) DELETE order_id FROM Orders WHERE order_total < 1000; C) DELETE * FROM orders WHERE order_total < 1000; D) DELETE FROM orders; E) DELETE orders WHERE order_total < 1000;
ADE
Q.47 - Which three statements are true about multiple row subqueries? A) They can contain HAVING clauses. B) They cannot contain a subquery. C) Two or more values are always returned from the subquery. D) They can return multiple columns. E) They can contain GROUP BY clauses.
ADE
Q.61 - Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? A) Insert statements can always be done on a table through a view. B) The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. C) 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. D) 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. E) Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword. F) Delete statements can always be done on a table through a view.
ADE
Q.1 - Which three are true about granting object privileges on tables, views, and sequences? A) REFERENCES can be granted only on tables and views. B) DELETE can be granted on tables, views, and sequences. C) SELECT can be granted only on tables and views. D) UPDATE can be granted only on tables and views. E) INSERT can be granted on tables, views, and sequences. F) ALTER can be granted only on tables and sequences.
ADF
Q.25 - Which three are true about privileges and roles? A) System privileges always set privileges for an entire database. B) All roles are owned by the sys schema. C) PUBLIC can be revoked from a user. D) A user has all object privileges for every object in their schema by default. E) A role is owned by the user who created it. F) A role can contain a combination of several privileges and roles. G) PUBLIC acts as a default role granted to every user in a database.
ADF
Q.43 - Which three are true about dropping columns from a table? A) Multiple columns can be dropped simultaneously using the ALTER TABLE command. B) A column can be removed only if it contains no data. C) A column must be set as unused before it is dropped from a table. D) A column drop is implicitly committed. E) A primary key column cannot be dropped. F) A column that is referenced by another column in any other table cannot be dropped.
ADF
Q.9 - Which three statements are true about indexes and their administration in an Oracle database? A) A DROP INDEX statement always prevents updates to the table during the drop operation. B) If a query filters on an indexed column then it will always be used during execution of the query. C) An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table. D) An index can be created as part of a CREATE TABLE statement. E) A UNIQUE and non-unique index can be created on the same table column. F) A descending index is a type of function-based index.
ADF
Q.14 - 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? A) The subquery is executed for every row in the EMPLOYEES table. B) The subquery is not a correlated subquery. C) All existing rows in the EMPLOYEES table are deleted. D) The subquery is executed before the DELETE statement is executed. E) The DELETE statement executes successfully even if the subquery selects multiple rows.
AE
Q.2 - Which two statements are true about the rules of precedence for operators? A) Arithmetic operators with equal precedence are evaluated from left to right within an expression. B) The concatenation operator l l is always evaluated before addition and subtraction in an expression. C) NULLS influence the precedence of operators in an expression. D) The + binary operator has the highest precedence in an expression in a SQL statement. E) Multiple parentheses can be used to override the default precedence of operators in an expression.
AE
Q.40 - Examine the description of the SALES table Name Null? Type PRODUCT_ID NOT NULL NUMBER (10) CUSTOMER ID NOT NULL NUMBER (10) TIME_ID NOT NULL DATE CHANNEL ID NOT NULL NUMBER (5) PROMO_ID NOT NULL NUMBER (5) QUANTITY_SOLD NUMBER (10,2) PRICE NUMBER (10,2) AMOUNT_SOLD NOT NULL NUMBER (10,2) The SaLes table has 55,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? A) SALES1 is created with 55,000 rows. B) SALES1 is created with no rows. C) SALES1 is created with 1 row. D) SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table. E) SALES1 has NOT NULL constraints on any selected columns which had those constraints in the
AE
Q.48 - Which two statements are true about Oracle synonyms? A) A synonym can have a synonym. B) Any user can create a PUBLIC synonym. C) A synonym can be created on an object in a package. D) All private synonym names must be unique in the database. E) A synonym has an object number.
AE
Q.69 - Examine the description of the CUSTOMERS table: Name Null? туре CUST_ID NOT NULL VARCHAR2 (2) CUST_LAST_NAME VARCHAR2 (30) CITY VARCHAR2 (10) CUST_CREDIT LIMIT NUMBER (6,2) 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? A) WHERE (INITCAP (cust_last_name) LIKE 'A%' OR INITCAP (cust_last_name) LIKE 'B%') AND cust_credit_limit < 1000; B) WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000; C) WHERE (UPPER (cust last name) LIKE INITCAP ('A') OR UPPER (cust last name) LIKE INITCAP ('B')) AND ROUND (cust_credit_limit) < ROUND (1000); D) WHERE UPPER (cust_last_name) BETWEEN UPPER('A%' AND 'B%') AND ROUND (cust credit_limit) <1000; E) WHERE (UPPER(cust_last_name) LIKE 'A%' OR UPPER (cust_last_name) LIKE 'B%') AND ROUND (cust credit_limit) < 1000;
AE
Q.63 - Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database? A) Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions. B) Insert statements can always be done on a table through a view. C) 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. D) The WITH CHECK clause has no effect when deleting rows from the underlying table through the view. " E) Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword. F) 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.
AEF
Q.75 - Which three actions can you perform only with system privileges? A) Log in to a database instance. B) Execute a procedure in another schema. C) Use the WITH GRANT OPTION clause. D) Access flat files via a database, which are stored in an operating system directory. E) Query any table in a database. F) Create stored procedures, functions and packages.
AEF
Q.13 - Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB ID and SALARY. Only the EMPLOYEE_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? A) Employee 100 will have SALARY set to the same value as the SALARY of employee 200. B) Employee 200 will have SALARY set to the same value as the SALARY of employee 100. C) Employee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100. D) Employees 100 and 200 will have the same JOB_ID as before the update command. E) Employees 100 and 200 will have the same SALARY as before the update command. F) Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.
AF
Q.44 Examine the description of the PRODUCT_STATUS table: Name Null? Type PROD ID NOT NULL NUMBER (2) STATUS NOT NULL VARCHAR2 (15) The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row. Which two queries will execute successfully? A) SELECT prod_id' l l q' ('s not available)' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK' ; B) SELECT prod_id q's not available" FROM product_status WHERE status = 'OUT OF STOCK'; C) SELECT prod_id II q" 's not available" FROM product_status WHERE status = 'OUT OF STOCK'; D) SELECT prod_id Il q' ('s not available)' 'CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK' ; E) SELECT prod_id "CURRENT AVAILABILITY" Il q' ('s not available)' FROM product_status WHERE status = 'OUT OF STOCK', F) SELECT prodid Il q' ('s not available)' FROM product_status WHERE status = 'OUT OF STOCK' ;
AF
Q.67 - 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? A) CURRENT_DATE returns the current date and time as per the session time zone. B) SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. C) SYSDATE can be queried only from the DUAL table. D) CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds. E) SYSDATE can be used in expressions only if the default date format is DD-MON-RR. F) CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
AF
Q26 - Which two are true about the WITH GRANT OPTION clause? A) It can be used to pass on privileges to other users by the grantee. B) The grantee can grant the object privilege to any user in the database, with or without including this option. C) The grantee must have the GRANT ANY OBJECT PRIVILEGE System privilege to use this option. D) It cannot be used to pass on privileges to PUBLIC by the grantee. E) It can be used when granting privileges to roles. F) It can be used for system and object privileges.
AF
Q.25 - Which is the default column or columns for sorting output from compound queries using SET operators such as INTERSECT in a SQL statement? A) the first NUMBER Or VARCHAR2 column in the last SELECT of the compound query B) the first column in the first SELECT of the compound query C) the first column in the last SELECT of the compound query D) the first VARCHAR2 column in the first SELECT of the compound query E) the first NUMBER column in the first SELECT of the compound query
B
Q.9 - 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? A) TIMESTAMP WITH LOCAL TIME ZONE B) INTERVAL DAY TO SECOND C) TIMESTAMP D TIMESTAMP WITH TIMEZONE E) INTERVAL YEAR TO MONTH
B
Q.57 - 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? A) 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); B) 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); C) 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); D) 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);
BC
Q.54 - Examine this SQL statement: SELECT cust_id, cust last name "Last Name" FROM customers WHERE country id = 10 UNION SELECT cust_id CUSTNO, cust_last_name FROM customers WHERE country_id = 30 Identify three ORDER BY clauses, any one of which can complete the query successfully. A) ORDER BY "CUST_NO" B) ORDER BY 2, 1 C) ORDER BY "Last Name" D) ORDER BY 2, cust_id E) ORDER BY CUST_NO
BCD
Q.16 - Which three statements are true about GLOBAL TEMPORARY TABLES? A) GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table. B) A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted. C) A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions. D) A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back. E) Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted. F) GLOBAL TEMPORARY TABLE space allocation occurs at session start.
BCE
Q.19 - Which three are true about the CREATE TABLE command? A) The owner of the table should have space quota available on the tablespace where the table is defined. B) A user must have the CREATE ANY TABLE privilege to create tables. C) It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint. D) It implicitly rolls back any pending transactions. E) It implicitly executes a commit. F) The owner of the table must have the UNLIMITED TABLESPACE System privilege.
BCE
Q.26 - 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? A) There is no row containing pen. B) There is no row containing pencil. C) The code for fountain pen is 3. D) The code for pen is 1. E) The code for pen is 10. F) There is no row containing fountain pen.
BCE
Q.3 - Which three statements are true about the DESCRIBE command? A) It displays the PRIMARY KEY constraint for any column or columns that have that constraint. B) It displays the NOT NULL constraint for any columns that have that constraint. C) It can be used to display the structure of an existing view. D) It can be used only from SQL*Plus. E) It displays all constraints that are defined for each column. F) It can be used from SQL Developer.
BCF
Q.33 - Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? A) WHERE and HAVING clauses can be used in the same statement only if applied to different table columns. B) The WHERE clause can be used to exclude rows before dividing them into groups. C) The WHERE clause can be used to exclude rows after dividing them into groups. D) The HAVING clause can be used with aggregating functions in subqueries. E) Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of
BD
Q.53 - Which two statements are true about a self join? A) It must be an equijoin. B) It can be an inner join. C) It must be a full outer join. D) It can be a left outer join. E) The join key column must have an index.
BD
Q.69 - 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? A) STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity. B) "The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships. C) The ER must have a 1-to-many relationship between the STUDENTS and PROJECTS entities. D) 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. E) PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS
BD
Q.74 - You execute this command: TRUNCATE TABLE depts; Which two are true? A) A ROLLBACK statement can be used to retrieve the deleted data. B) It retains the indexes defined on the table. C) It drops any triggers defined on the table. D) It retains the integrity constraints defined on the table. E) A FLASHBACK TABLE statement can be used to retrieve the deleted data. F) It always retains the space used by the removed rows.
BD
Q.32 - Which three are true about multi table INSERT statements? A) They can be performed on external tables using SQL Loader. B) They can insert each computed row into more than one table. C) They can be performed on remote tables. D) They can be performed only by using a subquery. E) They can be performed on relational tables. F) They can be performed on views
BDE
Q.56 - Which three actions can you perform only with system privileges? A) Execute a procedure in another schema. B) Truncate a table in another schema. C) Access flat files via a database, which are stored in an operating system directory. D) Log in to a database. E) Query any table in a database. F) Use the WITH GRANT OPTION clause.
BDE
Q.28 - Which three statements are true about views in an Oracle Database? A) A SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query. B) Views have no segment. C) Views can join tables only if they belong to the same schema. " D) Rows inserted into a table using a view are retained in the table if the view is dropped. E) Views have no object number. F) A view can be created that refers to a non-existent table in its defining query.
BDF
Q.34 - Which two actions can you perform with object privileges? A) Execute a procedure or function in another schema. B) Create FOREIGN key constraints that reference tables in other schemas. C) Set default and temporary table spaces for a user. D) Create roles. E) Delete rows from tables in any schema except SYS.
BE
Q.60 - Name Null? Type ---------------------------------------- PRODUCT ID NOT NULL NUMBER (10) CUSTOMER ID NOT NULL NUMBER (10) TIME ID NOT NULL DATE CHANNEL ID NOT NULL NUMBER (5) PROMO ID NOT NULL NUMBER (5) QUANTITY SOLD NOT NULL NUMBER (10, 2) PRICE NUMBER (10, 2) AMOUNT SOLD NOT NULL NUMBER (10, 2) The SALES table has 55,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? A) SALES1 is created with no rows. B) SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table. C) SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table. D) SALES1 is created with 1 row. E) SALES1 is created with 55,000 rows.
BE
Q.65 - Examine the description of the BOOKS table: Name Null? Type TRANSACTION ID NOT NULL VARCHAR2 (6) TRANSACTION_DATE DATE AMOUNT NUMBER (10, 2) CUSTOMER_ID VARCHAR2 (6) 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); SAVE POINT a; DELETE FROM books; ROLLBACK TO SAVE POINT a; ROLLBACK; Which two statements are true? A) The second ROLLBACK Command does nothing. B) The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed. C) The second ROLLBACK command replays the delete. D) The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row. E) The second ROLLBACK command undoes the insert.
BE
Q.46 - 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? A) SELECT emp_id, NEXT_DAY (MONTHS_BETWEEN (hire_date, SYSDATE), 6) FROM employees; B) SELECT emp_id, ADD_MONTHS (hire_date, 6), NEXT_DAY ('MONDAY') FROM employees; C) SELECT emp_id, NEXT_DAY (ADD_MONTHS (hire_date, 6), 'MONDAY') FROM employees; D) SELECT emp_id, NEXT_DAY (ADD_MONTHS (hire_date, 6), 1) FROM employees;
C
Q.16 -Table ORDER_ITEMS contains columns ORDER_ID, UNIT PRICE and QUANTITY, of data type NUMBER. Examine these SQL statements: 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? A) Both statements will return NULL If either UNIT_PRICE or QUANTITY contains NULL. B) Statement 1 returns only one row of output. C) Statement 2 may return multiple rows of output. D) Both the statements give the same output. E) Statement 2 returns only one row of output.
CE
Q.18 - Which two statements are true about the ORDER BY clause? A) NULLS are not included in the sort operation. B) Numeric values are displayed in descending order if they have decimal positions. C) In a character sort, the values are case-sensitive. D) Only columns that are specified in the SELECT list can be used in the ORDER BY clause. E) Column aliases can be used in the ORDER BY clause.
CE
Q.35 - Examine the description of the PROMOTIONS table: Name Null? Type PROMO ID NOT NULL NUMBER (6) PROMO_NAME NOT NULL VARCHAR2 (30) PROMO_CATEGORY NOT NULL VARCHAR2 (30) PROMO_COST NOT NULL NUMBER (10, 2) You want to display the unique promotion costs in each promotion category. Which two queries can be used? A) SELECT DISTINCT promo_cost l l ' in ' I l DISTINCT promo_category FROM promotions ORDER BY 1; B) SELECT promo_category, DISTINCT promo_cost FROM promotions ORDER BY 2; C) SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1; D) SELECT promo_cost, promo_category FROM promotions ORDER BY by 1; E) SELECT DISTINCT promo_category I I 'has ' l I promo_cost AS COSTS FROM promotions ORDER BY 1;
CE
Q.36 - Which two statements are true regarding a SAVEPOINT? A) Rolling back to a SAVEPOINT can undo a TRUNCATE statement. B) Rolling back to a SAVE POINT can undo a CREATE INDEX statement. C) Rolling back to a SAVE POINT can undo a DELETE statement D) Only one SAVEPOINT may be issued in a transaction. E) A SAVEPOINT does not issue a COMMIT.
CE
Q.40 - Which two statements are true about the results of using the INTERSECT operator in compound queries? A) Reversing the order of the intersected tables can sometimes affect the output. B) INTERSECT ignores NULLS. C) Column names in each SELECT in the compound query can be different. D) The number of columns in each SELECT in the compound query can be different. E) INTERSECT returns rows common to both sides of the compound query.
CE
Q.55 - 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? A) A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023. B) A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236. C) A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024. D) A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023. E) A row whose UNIT PRICE column contains the value 10235.99 will be displayed as # # # # # # #
CE
Q.7 - The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER. Which two queries execute successfully? A) SELECT NVL2 (cust_credit_Limit * .15, 'Not Available') FROM customers; B) SELECT TO_CHAR(NVL (cust_ t limit * .15, 'Not Available')) FROM customers; C) SELECT NVL2 (cust_credit_limit, TO_CHAR (cust_credit_limit *.15), 'Not Available') FROM customers; D) SELECT NVL (cust_credit_limit * .15, 'Not Available') FROM customers; E) SELECT NVL (TO_CHAR (cust_credit_limit * .15), 'Not Available') FROM customers;
CE
Q.73 - Question Exhibit View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks require subqueries? A) Display the minimum PROD_LIST_PRICE for each product status. B) Display the total number of products supplied by supplier 102 which have a product status of obsolete. C) Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable. D) Display suppliers whose PROD_LIST_PRICE is less than 1000. E) Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
CE
Q.8 - Which two statements are true about substitution variables? A) A substitution variable prefixed with & always prompts only once for a value in a session. B) A substitution variable can be used only in a SELECT statement. C) A substitution variable can be used with any clause in a SELECT statement. D) A substitution variable used to prompt for a column name must be enclosed in double quotation marks. E) A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session. F) A substitution variable used to prompt for a column name must be enclosed in Single quotation marks
CE
Q.36 - Examine the description of the MEMBERS table: Name Null? Type MEMBERID NOT NULL VARCHAR2 (6) FIRST NAME VARCHAR2 (50) LAST_NAME NOT NULL VARCHAR2 (50) ADDRESS VARCHAR2 (50) CITY VARCHAR2 (25) Examine the partial query: SELECT city, last_name 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? A) WHERE city = "%AN%' B) ORDER BY last_name DESC, city ASC C) ORDER BY 1, LNAME DESC D) WHERE city IN ('%AN%') E) ORDER BY 1, 2 F) WHERE city LIKE '%AN%'
CF
Q.66 - Examine the description of the PRODUCT DETAILS table: NAME NULL? TYPE PRODUCT ID NOT NULL NUMBER (2) PRODUCT_NAME NOT NULL VARCHAR2 (25) PRODUCT PRICE NUMBER (8, 2) EXPIRY DATE DATE Which two statements are true? A) EXPIRY DATE cannot be used in arithmetic expressions. B) PRODUCT_NAME cannot contain duplicate values. C) PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it. D) EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it. E) PRODUCT_PRICE contains the value zero by default if no value is assigned to it. F) PRODUCT_ID can be assigned the PRIMARY KEY constraint.
CF
Q.12 - The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson. Which query produces output for CUST LAST_NAME containing Oder for the first row and Aus for the second? A) SELECT REPLACE (TRIM (TRAILING 'son' FROM cust_last_name), 'An', 'O') FROM customers; B) SELECT INITCAP (REPLACE (TRIM ('son' FROM cust_last_name), 'An', 'O')) FROM customers; C) SELECT REPLACE (SUBSTR (cust_last_name, -3), 'An', 'O' FROM customers; D) SELECT REPLACE (REPLACE (cust_last_name, 'son', ''), 'An', 'O') FROM customers;
D
Q.77 - Examine the description of the CUSTOMERS table: Name Null? Type CUST ID NOT NULL VARCHAR2 (6) FIRST NAME NOT NULL VARCHAR2 (50) LAST_NAME VARCHAR2 (50) ADDRESS VARCHAR2 (50) CITY VARCHAR2 (25) 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? A) SELECT * FROM customers WHERE city LIKE 'D__' ; B) SELECT * FROM customers WHERE city = ' %D__' ; C) SELECT * FROM customers WHERE city = 'D__%' ; D) SELECT * FROM customers WHERE city LIKE 'D_%' ;
D
Q.78 - 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? A) SELECT &&col1 &&col2 FROM &table WHERE &&Condition = &&cond; B) SELECT &&col1, &&col2 FROM &table WHERE &&condition; C) SELECT '&&coli', '&&co12' FROM &table WHERE '&&condition' = '&cond'; D) SELECT '&&col1, &col2 FROM &&table WHERE &condition; E) SELECT &coli, &col2 FROM "&table" WHERE &condition; 2nd file
D
Q.3 - Which two statements are true about the ORDER BY clause when used with a SQL statement containing a SET operator such as UNION? A) Each SELECT statement in the compound query must have its own ORDER BY clause. B) Each SELECT statement in the compound query can have its own ORDER BY clause. C) Column positions must be used in the ORDER BY clause. D) Only column names from the first SELECT statement in the compound query are recognized. E) The first column in the first SELECT of the compound query with the UNION operator is used by default to sort output in the absence of an ORDER BY clause.
DE
Q.5 - Which two statements are true about the COUNT function? A) It can only be used for NUMBER data types. B) A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause. C) COUNT (inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column. D) COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column. E) COUNT (*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
DE
Q.50 - What is true about non-equijoin statement performance? A) The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax. B) The BETWEEN condition always performs less well than using the >= and <= conditions. C) The BETWEEN condition always performs better than using the >= and <= conditions. D) Table aliases can improve performance. E) The join syntax used makes no difference to performance.
DE
Q.71 - Which two statements are true about a full outer join? A) It includes rows that are returned by a Cartesian product. B) It returns only unmatched rows from both tables being joined. C) The Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause. D) It includes rows that are returned by an inner join. E) It returns matched and unmatched rows from both tables being joined.
DE
Q.74 - You create a table by using this command: CREATE TABLE rate_list (rate NUMBER (6, 2)); Which two are true about executing statements? A) INSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99. B) INSERT INTO rate_list VALUES (87654.556) inserts the value as 87654.6. C) INSERT INTO rate_list VALUES (-10) produces an error. D) INSERT INTO rate_list VALUES (-.9) inserts the value as -.9. E) INSERT INTO rate_list VALUES (0.551) inserts the value as .55. F) INSERT INTO rate_list VALUES (0.999) produces an error.
DE
Q.76 - Examine the data in the CUST_NAME column of the CUSTOMERS table: CUST_NAME Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni 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? A) WHERE SUBSTR (cust_name, INSTR (cust_name, ' ') + 1) LIKE 'Mc%' OR 'MC%' B) WHERE INITCAP (SUBSTR (cust_name, INSTR (cust_name, ' ') + 1)) IN ('MC%', 'Mc%) C) WHERE SUBSTR (cust_name, INSTR (cust_name, ' ') + 1) LIKE 'Mc%' D) WHERE UPPER (SUBSTR (cust_name, INSTR (cust_name, ' ') + 1)) LIKE UPPER (MC%') E) WHERE INITCAP (SUBSTR (cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'Mc%'
DE
Q.52 - Examine this query: SELECT employee_id, first name, salary FROM employees WHERE hire date > Which two methods should you use to prevent prompting for a hire date value when this query is executed? A) Execute the SET VERIFY OFF command before executing the query. B) Use the UNDEFINE command before executing the query. C) Store the query in a script and pass the substitution value to the script when executing it. D) Use the DEFINE command before executing the query. E) Execute the SET VERIFY ON command before executing the query. F) Replace '&1' with '&&1' in the query.
DF
Q.68 - Which three statements are true about the Oracle join and ANSI join syntax? A) The Oracle join syntax supports natural joins. B) The Oracle join syntax only supports right outer joins. C) The Oracle join syntax performs better than the SQL: 1999 compliant ANSI join syntax. D) The Oracle join syntax supports creation of a Cartesian product of two tables. E) The Oracle join syntax performs less well than the SQL: 1999 compliant ANSI join syntax. F) The SQL: 1999 compliant ANSI join syntax supports natural joins. G) The SQL: 1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
DFG
Q.30 - Examine the description of the EMPLOYEES table: Name Null? Type EMP ID NOT NULL NUMBER EMP NAME VARCHAR2 (10) DEPT ID NUMBER (2) SALARY NUMBER (8,2) JOIN_DATE DATE NLS_DATE_FORMAT is set to DD-MON-YY. Which query requires explicit data type conversion? A) SELECT join_date II ' ' ll salary FROM employees; B) SELECT salary + '120.50' FROM employees; C) SELECT join_date + '20' FROM employees; D) SELECT SUBSTR(join_date, 1, 2) - 10 FROM employees: E) SELECT join_date FROM employees WHERE join_date > 10-02-2018' ;
E
Q.42 - Examine this query: SELECT TRUNC (ROUND (156.00,-2),-1) FROM DUAL; What is the result? A) 150 B) 16 C) 100 D) 160 E) 200
E
Q.59 - Which two statements are true about the DUAL table? A) It can display multiple rows and columns. B) It can display multiple rows but only a single column. C) It can be accessed only by the sys user. D) It can be used to display only constants or pseudo columns. E) It consists of a single row and single column of VARCHAR2 data type. F) It can be accessed by any user who has the SELECT privilege in any schema.
EF
Q.62 - Which two statements are true about INTERVAL data types? A) The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value. B) INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year C) INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. D) The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column. E) INTERVAL YEAR TO MONTH columns support yearly intervals. F) INTERVAL DAY TO SECOND columns support fractions of seconds.
EF
Q.1 - 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? A) WHERE MONTHS_BETWEEN (SYSDATE, start_date) <= 25 B) WHERE ADD_MONTHS (start date, 25) <= SYSDATE C) WHERE TO_NUMBER (start_date - SYSDATE) <= 25 D) WHERE MONTHS_BETWEEN (start_date, SYSDATE) <= 25
A
Q.15 -You execute this query: SELECT TO_CHAR (NEXT_DAY (LAST_DAY (SYSDATE), 'MON'), 'dd "Monday for" fmMonth rrrr') FROM DUAL; What is the result? A) It returns the date for the first Monday of the next month. B) It executes successfully but does not return any result. C) It generates an error. D) It returns the date for the last Monday of the current month.
A
Q.20 - Question Exhibit 1 Exhibit 2 View the Exhibits and examine the structure of the costs and PROMOTIONS tables. You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a promotion time interval. Examine this SQL statement: SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo cost < ALL (SELECT MAX (promo_cost) FROM promotions GROUP BY (promo_end_date - promo_begin_date))); What will be the result? A) It executes successfully and gives the required result. B) It gives an error because the ALL keyword is not valid. C) It gives an error because the GROUP BY clause is not valid. D) It executes successfully but does not give the required result
A
Q.24 - Examine the description of the CUSTOMERS table: Name Null? Type ------------------------------------------------------------------------------------------ CUST_ID NOT NULL NUMBER CUST FIRST NAME NOT NULL VARCHAR2 (20) CUST_LAST_NAME NOT NULL VARCHAR2 (30) CUST INCOME LEVEL VARCHAR2 (30) CUST CREDIT_LIMIT NUMBER 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? A) 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; B) SELECT cust first name, cust_credit_limit *.05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level != NULL; C) SELECT cust_first_name, cust_credit_limit *.05 AS DUE_AMOUNT FROM customers WHERE cust_income level <> NULL AND due_amount <> NULL; D) SELECT cust_first_name, cust_credit_limit * .05 ASDUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;
A
Q.37 - Examine this query: SELECT 2 FROM dual di CROSS JOIN dual d2 CROSS JOIN dual d3; What is returned upon execution? A) 1 row B) 0 rows C) 8 rows D) an error E) 3 rows F) 6 rows
A
Q.51 - Examine the description of the SALES1 table: Name Null Type SALES_ID NOT NULL NUMBER STORE_ID NOT NULL NUMBER ITEMS_ID NUMBER QUANTITY NUMBER SALES_DATE DATE 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? A) MINUS B) INTERSECT C) UNION D) SUBTRACT E) UNION ALL
A
Q.61 - Examine the description of the EMPLOYEES table: Name Null? Type EMP_ID NOT NULL NUMBER EMP_NAME VARCHAR2 (40) DEPT ID NUMBER (2) SALARY NUMBER (8, 2) JOIN_DATE DATE Which query is valid? A) SELECT dept_id, join_date, SUM (salary) FROM employees GROUP BY dept_id, join_date; B) SELECT dept_id, MAX (AVG (salary)) FROM employees GROUP BY dept_id; C) SELECT dept_id, join_date, SUM (salary) FROM employees GROUP BY dept_id: D) SELECT dept_id, AVG (MAX (salary)) FROM employees GROUP BY dept_id;
A
Q.9 - Examine the description of the PRODUCT INFORMATION table: Name Null? Туре PROD_ID NOT NULL NUMBER (2) PROD NAME VARCHAR2 (10) LIST_PRICE NUMBER (6, 2) Which query retrieves the number of products with a null list price? A) SELECT COUNT (NVL (list_price, 0)) FROM product_information WHERE list_price is NULL; B) SELECT COUNT (list_price) FROM product_information WHERE list_price = NULL; C) SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price IS NULL; D) SELECT COUNT (list_price) FROM product_information WHERE list price IS NULL;
A
Q.32 - Which two queries execute successfully? A) SELECT NULLIF (NULL, 100) FROM DUAL; B) SELECT NULLIF (100, 'A') FROM DUAL; C) SELECT COALESCE (100, NULL, 200) FROM DUAL; D) SELECT NULLIF (100, 100) FROM DUAL; E) SELECT COALESCE (100, 'A') FROM DUAL;
CD
Q.78 - Examine the description of the BOOKS TRANSACTIONS table: Name Null? туре TRANSACTION_ID NOT NULL VARCHAR2 (6) TRANSACTION TYPE VARCHAR2 (3) DATE BORROWED_DATE VARCHAR2 (6) BOOK ID VARCHAR2 (6) MEMBER_ID Examine this partial SQL statement: SELECT * FROM books_transactions Which two WHERE conditions give the same result? A) WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR member_id IN ('A101', 'A102')); B) WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND (member_id ='A101' OR member_id = 'A102')); C) WHERE (borrowed_date = SYSDATE AND transaction_type = 'RM') OR member_id IN ('A101', 'A102'); D) WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101', 'A102'); E) WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101 OR member_id = 'A102');
CD
Q.9 - Examine the structure of the two tables: PRODUCTS: Name Null? Type PROD_ID CHAR (2) PROD NAME CHAR (4) EXPDATE TIMESTAMP (6) NEW_PRODUCTS: Name Null? Type PROD_ID CHAR (4) PROD_NAME VARCHAR2 (10) EXP_DATE DATE Which two queries execute successfully? A) SELECT * FROM products MINUS SELECT prod_id FROM new_products, B) SELECT prod_id FROM products UNION ALL SELECT prod_id, prod_name FROM new_products, C) SELECT * FROM products UNION SELECT * FROM new_products; D) SELECT prod_id, prod_name FROM products INTERSECT SELECT 100, prod name FROM new_products; E) SELECT prod_id, exp_date FROM products UNION ALL SELECT prod_id, NULL FROM new products;
CD
Q.24 - Which three statements are true about Structured Query Language (SQL)? A) It is the only language that can be used for both relational and object-oriented databases. B) It is used to define encapsulation and polymorphism for a relational table. C) It guarantees atomicity, consistency, isolation, and durability (ACID) features. D) It best supports relational databases. E) It requires that data be contained in hierarchical data storage. F) It provides independence for logical data structures being manipulated from the underlying physical data storage
CDF
Q.39 - Which three statements are true about a self-join? A) It must be an equijoin. B) The ON clause must be used. C) The query must use two different aliases for the table. D) The ON clause can be used. E) It must be an inner join. F) It can be an outer join.
CDF
Q.13 - Examine these SQL statements which execute successfully: CREATE TABLE emp(emp no NUMBER (2) CONSTRAINTemp_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 (empno) ON DELETE SET NULL; ALTER TABLE emp DISABLE CONSTRAINT emp_amp_no_pk CASCADE, ALTER TABLE emp ENABLE CONSTRAINT emp_emp_no_pk; Which two statements are true after execution? A) The primary key constraint will be enabled and DEFERRED. B) The foreign key constraint will be enabled and IMMEDIATE. C) The primary key constraint will be enabled and IMMEDIATE. D) The foreign key constraint will be enabled and DEFERRED. E) The foreign key constraint will be disabled.
CE
