Quiz
barcos101-200
Create a free account to finish this quiz
Save your progress and see your final score.
Already have an account? Sign in
Questions in this quiz (100)
1Examine this data in the EMPLOYEES table
- ASELECT dept_id, LENGTH (last: name) , SUM(salary) FROM employees GROUP BV dept_id;
- BSELECT dept_id, STDDEV(last_name), SUM( salary) FROM employees GROUP BV dept_id;
- CSELECT dept_id, MAX (last_name) . SUM(salary) FROM employees GROUP BY dept_id;
- DSELECT dept_id( INSTR(last_name, 'A'), SUM (salary) FROM employees GROUP BY dept_id;
2Examine the structure of the SHIPMENTS table:
- ABoth execute successfully and give correct results.
- BOnly the first query executes successfully but gives a wrong result.
- COnly the first query executes successfully and gives the correct result.
- DOnly the second query executes successfully but gives a wrong result.
- EOnly the second query executes successfully and gives the correct result.
3Which two statements are true regarding views? (Choose two.)
- AThe WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
- BThe OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
- CRows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
- DRows added through a view are deleted from the table automatically when the view is dropped.
- EA simple view in which column aliases have been used cannot be updated.
- FA subquery used in a complex view definition cannot contain group functions or joins.
4Which three actions can you perform only with system privileges?
- AQuery any table in a database.
- BLog in to a database.
- CAccess flat files via a database, which are stored in an operating system directory.
- DTruncate a table in another schema.
- EExecute a procedure in another schema.
- FUse the WITH GRANT OPTION clause
5View the exhibit and examine the structure of the EMPLOYEES table.
You want to select all employees having 100 as their MANAGER_ID manages and their manager.
You want the output in two columns: the first column should have the employee’s manager’s LAST_NAME and the second column should have the employee’s LAST_NAME.
Which SQL statement would you execute?
- ASELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE m.manager_id=100;
- BSELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE e.manager_id=100;
- CSELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON e.employee_id = m.manager_idWHERE m.manager_id=100;
- DSELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id AND e.manager_id=100
6View the exhibit and examine the structure of the PROMOTIONS table.
- ASELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= ‘INTERNET’;
- BSELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);
- CSELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = ‘INTERNET’);
- DSELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);
7Examine the description of the TRANSACTIONS table:
- ASELECT customer_id AS “CUSTOMER-ID”, transaction_date AS DATE, amount + 100 “DUES” FROM transactions;
- BSELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 “DUES AMOUNT” FROM transactions;
- CSELECT customer_id CUSTID, transaction_date TRANS_DATE, amount +100 DUES FROM transactions;
- DSELECT customer_id AS “CUSTOMER-ID”, transaction_date AS “DATE”, amount + 100 DUES FROM transactions;
- ESELECT customer_id AS ‘CUSTOMER-ID’, transaction_date AS DATE, amount + 100 ‘DUES’ FROM transactions;
8View the Exhibit and examine the structure of the ORDERS table.
Which UPDATE statement is valid?
- AUPDATE ordersSET order_date = ’12-mar-2007’,order_total IS NULLWHERE order_id = 2455;
- BUPDATE ordersSET order_date = ’12-mar-2007’,AND order_total = TO_NUMBER(NULL)WHERE order_id = 2455;
- CUPDATE ordersSET order_date = ’12-mar-2007’,order_total = NULLWHERE order_id = 2455;
- DUPDATE ordersSET order_date = TO_DATE(’12-mar-2007’,’dd-mon-yyyy’),SET order_total = TO_NUMBER (NULL)WHERE order_id = 2455;
9Which three statements are true about views in an Oracle Database?
- AViews can join tables only if they belong to the same schema.
- BA view can be created that refers to a non-existent table in its defining query.
- CViews have no object number.
- DViews have no segment.
- ERows inserted into a table using a view are retained in the table if the view is dropped.
- FA SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.
10Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1) FROM DUAL;
What would be the outcome?- A150
- B200
- C160
- D16
- E100
11Examine the structure and data in the PRICE_LIST table:
- ASELECT TO_CHAR (prod_price* .25, ‘$99,999.99’)FROM PRICE_LIST
- BSELECT TO_CHAR (TO_NUMBER(prod_price) * .25, ‘$99,999.00’)FROM PRICE_LIST
- CSELECT TO_CHAR (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’)FROM PRICE_LIST
- DSELECT TO_NUMBER (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’)FROM PRICE_LIST
12View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)
- AFULL OUTER JOIN
- BJOIN
- CLEFT OUTER JOIN
- DRIGHT OUTER JOIN
13Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)
- AThe outer query stops evaluating the result set of the inner query when the first value is found.
- BIt is used to test whether the values retrieved by the inner query exist in the result of the outer query.
- CIt is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
- DThe outer query continues evaluating the result set of the inner query until all the values in the result set are processed.
14The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully?- ASELECT NVL (cust_credit_limit * .15, ‘Not Available’) FROM customers;
- BSELECT TO_CHAR(NVL(cust_credit_limit * .15, ‘Not Available’)) FROM customers;
- CSELECT NVL(TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
- DSELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), ‘Not Available’) FROM customers;
- ESELECT NVL2 (cust_credit_limit * .15, ‘Not Available’) FROM customers;
15Which three statements are true about the ALTER TABLE....DROP COLUMN. command? (Choose three.)
- AA column can be dropped only if it does not contain any data.
- BA column can be dropped only if another column exists in the table.
- CA dropped column can be rolled back.
- DThe column in a composite PRIMARY KEY with the CASCADE option can be dropped.
- EA parent key column in the table cannot be dropped.
16You need to list the employees in DEPARTMENT_ID 20 days in a single row, ordered by HIRE_DATE.
- ASELECT LISTAGG(last_name)WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;
- BSELECT LISTAGG(last_name, '; ')WITHIN GROUP ORDER BY (hire_date) “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;
- CSELECT LISTAGG(last_name, '; ') “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;WITHIN GROUP ORDER BY (hire_date);
- DSELECT LISTAGG(last_name, '; ') “Emp_list”, MIN(hire_date) “Earliest”FROM employeesWHERE department_id = 30;ORDER BY (hire_date);
17Examine this SQL statement:
- AAll existing rows in the ORDERS table are updated
- BThe subquery is executed for every updated row in the ORDERS table
- CThe subquery is executed before the UPDATE statement is executed
- DThe subquery is not a correlated subquery
- EThe UPDATE statement executes successfully even if the subquery selects multiple rows
18Evaluate the following SQL statement:
- AIt executes successfully and displays rows in the descending order of PROMO_CATEGORY.
- BIt produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.
- CIt executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.
- DIt produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.
19Which three statements are true about multiple row subqueries?
- AThey can contain GROUP BY clauses.
- BThey can return multiple columns.
- CTwo or more values are always returned from the subquery.
- DThey can contain HAVING clauses.
- EThey cannot contain a subquery.
20Examine the structure of the MEMBERS table:
NameNull?Type
MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM members;
What is the outcome?- AIt fails because the alias name specified after the column names is invalid.
- BIt fails because the space specified in single quotation marks after the first two column names is invalid.
- CIt executes successfully and displays the column details in a single column with only the alias column heading.
- DIt executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.
21The 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?- ASELECT REPLACE (TRIM(TRAILING ‘son’ FROM cust_last_name), ‘An’, ‘o’) FROM customers;
- BSELECT INITCAP (REPLACE(TRIM(‘son’ FROM cust_last_name), ‘An’, ‘o’)) FROM customers;
- CSELECT REPLACE (SUBSTR(cust_last_name, -3), ‘An’, ‘o’) FROM customers;
- DSELECT REPLACE (REPLACE(cust_last_name, ‘son’, ‘’), ‘An’, ‘o’) FROM customers;
22Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
- AThe nested query executes after the outer query returns the row.
- BThe nested query executes first and then the outer query executes.
- CThe outer query executes only once for the result returned by the inner query.
- DEach row returned by the outer query is evaluated for the results returned by the inner query.
23Examine 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 need to display customers' second names where the second name starts with "Mc" or "MC".
Which query gives the required output?- ASELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
- BSELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc';
- CSELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%';
- DSELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)FROM customersWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
24You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, the customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?- ASELECT cust_last_name AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;
- BSELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;
- CSELECT cust_last_name AS Name, cust_credit_limit + 1000“New Credit Limit”FROM customers;
- DSELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;
25Evaluate the following CREATE TABLE command:
- AIt would execute successfully and only ORD_ITM_IDX index would be created.
- BIt would give an error because the USING INDEX clause cannot be used on a composite primary.
- CIt would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.
- DIt would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.
26Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c? (Choose two.)
- AALTER TABLE SPLIT PARTITION
- BALTER TABLE MERGE PARTITION
- CALTER TABLE TRUNCATE PARTITION
- DALTER TABLE ADD PARTITION
- EALTER TABLE DROP PARTITION
- FALTER TABLE MOVE PARTITION
27Which three statements are true about GLOBAL TEMPORARY TABLES?
- AA GLOBAL TEMPORARY TABLE can have multiple indexes.
- BA GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM.
- CA trigger can be created on a GLOBAL TEMPORARY TABLE.
- DA GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
- EA GLOBAL TEMPORARY TABLE can have only one index.
- FData Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.
28Which three statements are true about GLOBAL TEMPORARY TABLES?
- AGLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.
- BGLOBAL TEMPORARY TABLE space allocation occurs at session start.
- CA DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.
- DA GLOBAL TEMPORARY TABLE’s definition is available to multiple sessions.
- EAny GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
- FA TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
29Which statement is true about TRUNCATE and DELETE?
- AYou can never TRUNCATE a table if foreign key constraints will be violated.
- BFor large tables TRUNCATE is faster than DELETE.
- CFor tables with multiple indexes and triggers DELETE is faster than TRUNCATE.
- DYou can never DELETE rows from a table if foreign key constraints will be violated.
30Which statement is true about transactions?
- AA set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
- BEach Data Definition Language (DDL) statement executed forms a single transaction.
- CA set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
- DA combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
31Examine this query:
- AReplace ‘&1’ with ‘&&1’ in the query.
- BUse the DEFINE command before executing the query.
- CUse the UNDEFINE command before executing the query.
- DExecute the SET VERIFY ON command before executing the query.
- EStore the query in a script and pass the substitution value to the script when executing it.
- FExecute the SET VERIFY OFF command before executing the query.
32Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)
- AWHERE and HAVING clauses cannot be used together in a SQL statement.
- BThe HAVING clause conditions can have aggregate functions.
- CThe HAVING clause conditions can use aliases for the columns.
- DThe WHERE clause is used to exclude rows before the grouping of data.
- EThe HAVING clause is used to exclude one or more aggregated results after grouping data.
33In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than 10 years.
- AIt executes successfully but no rows updated.
- BIt executes successfully and updates the records of those employees who have been working in the company for more than 600 days.
- CIt gives an error because multiple NVL functions are used in an expression.
- DIt gives an error because NVL function cannot be used with UPDATE.
34Evaluate 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) );
The above command fails when executed. What could be the reason?- ASYSDATE cannot be used with the CHECK constraint.
- BThe BETWEEN clause cannot be used for the CHECK constraint.
- CThe CHECK constraint cannot be placed on columns having the DATE data type.
- DORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.
35View the exhibit and examine the structure of the CUSTOMERS table
- Afinding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
- Bfinding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'
- Clisting of customers who do not have a credit limit and were born before 1980
- Dfinding the number of customers, in each city, whose marital status is 'married'.
- Elisting of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.
36Examine these SQL statements which execute successfully:
- AThe foreign key constraint will be disabled.
- BThe primary key constraint will be enabled and DEFERRED.
- CThe foreign key constraint will be enabled and DEFERRED.
- DThe foreign key constraint will be enabled and IMMEDIATE.
- EThe primary key constraint will be enabled and IMMEDIATE.
37View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?- AThe NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
- BThe NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
- CThe NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- DThe NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
38Which two statements are true about INTERVAL data types?
- AThe YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
- BINTERVAL DAY TO SECOND columns support fractions of seconds.
- CINTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
- DINTERVAL YEAR TO MONTH columns support yearly intervals.
- EINTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
- FThe value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.
39Which two are true about a SQL statement using SET operators such as UNION?
- AThe number, but not names, of columns must be identical for all SELECT statements in the query.
- BThe 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.
- CThe 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.
- DThe names and number of columns must be identical for all SELECT statements in the query.
- EThe 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.
40View the Exhibit and examine the structure of CUSTOMERS table.
- AIt produces an error because the condition on the CUST_CITY column is not valid.
- BIt produces an error because the condition on the CUST_FIRST_NAME column is not valid.
- CIt produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
- DIt executes successfully
41Which two statements are true regarding subqueries? (Choose two.)
- AOnly two subqueries can be placed at one level.
- BA subquery in the WHERE clause of a SELECT statement can be nested up to three levels only.
- CA subquery can be used to access data from one or more tables or views.
- DThe columns in a subquery must always be qualified with the name or alias of the table used.
- EIf the subquery returns 0 rows, then the value returned by the subquery expression is NULL.
42Examine the structure of the MEMBERS table.
- ASELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';
- BSELECT last_name, city FROM members WHERE state LIKE 'M%';
- CSELECT last_name, city FROM members WHERE state IN ('MO', 'MI');
- DSELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';
43Examine the description of the SALES1 table:
- ASUBTRACT
- BINTERSECT
- CUNION ALL
- DUNION
- EMINUS
44In the customers table, the CUST_CITY column contains the value 'Paris' for the
- AAbigail PA
- BAbigail Pa
- CAbigail IS
- DAn error message
45Which two statements are true regarding constraints? (Choose two.)
- AAll constraints can be defined at the column level and at the table level.
- BA constraint can be disabled even if the constraint column contains data.
- CA column with the UNIQUE constraint can contain NULLs.
- DA foreign key column cannot contain NULLs.
- EA constraint is enforced only for INSERT operations.
46View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)
Evaluate this SQL statement:
SELECT TO_CHAR(list_price,'$9,999') From product_information;
Which two statements are true regarding the output? (Choose two.)- AA row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.
- BA row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.
- CA row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.
- DA row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.
47Which statement is true regarding the INTERSECT operator?
- AThe names of columns in all SELECT statements must be identical.
- BIt ignores NULLs.
- CReversing the order of the intersected tables alters the result.
- DThe number of columns and data types must be identical for all SELECT statements in the query.
48The 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?- AWHERE MONTHS_BETWEEN (SYSDATE, start-_date) <= 25
- BWHERE ADD_MONTHS (3tart_datO, 25) <= SYSDATE
- CWHERE MONTHS_UETWEEN (start_date, SYSDATE) <= 25
- DWHERE TO_NUMBER (start_date - SYSDATE) <= 25
49The BOOKS_TRANSACTIONS table exists in your schema in this database.
You execute this SQL statement when connected to your schema in your database instance.
SQL> SELECT * FROM books_transactions ORDER BY 3;
What is the result?- AThe execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
- BAll table rows are displayed sorted in ascending order of the values in the third column.
- CThe first three rows in the table are displayed in the order that they are stored.
- DOnly the three rows with the lowest values in the key column are displayed in the order that they are stored.
50Examine the create table statements for the stores and sales tables.
SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);
SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));
You executed the following statement:
SQL> DELETE from stores WHERE store_id=900;
The statement fails due to the integrity constraint error:
ORA-02292: integrity constraint (HR.STORE_ID_FK) violated
Which three options ensure that the statement will execute successfully? (Choose three.)- ADisable the primary key in the STORES table.
- BUse CASCADE keyword with DELETE statement.
- CDELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
- DDisable the FOREIGN KEY in SALES table and then delete the rows.
- ECreate the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
51View the Exhibit and examine the data in the PRODUCTS table
- AIt executes successfully but returns no result.
- BIt executes successfully and returns the required result.
- CIt generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
- DIt generates an error because the condition specified for the PROD_CATEGORY column is not valid.
52You 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.- AThe NOT NULL constraint on the ENAME column must be defined as the column level
- BFOREIGN KEY defined on the DEPT_ID column must be at the table level only
- COnly one LONG column can be used per table
- DThe FOREIGN KEY keyword is missing in the constraint definition
- EThe PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at the table level only
53Which statement is true regarding the outcome?
- ABoth the statements execute and give different results
- BOnly the second SQL statement executes successfully
- CBoth the statements execute and give the same result
- DOnly the first SQL statement executes successfully
54View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.
PRODUCT_ID column is the primary key. You create an index using this command: SQL > CREATE INDEX upper_name_idx
ON product_information(UPPER(product_name));
No other indexes exist on the PRODUCT_INFORMATION table.
Which query would use the UPPER_NAME_IDX index? (Choose the best answer.)- ASELECT product_id, UPPER(product_name)FROM product_informationWHERE UPPER(product_name)='LASERPRO' OR list_price > 1000;
- BSELECT UPPER(product_name)FROM product_information;
- CSELECT UPPER(product_name)FROM product_informationWHERE product_id = 2254;
- DSELECT product_idFROM product_informationWHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');
55View the Exhibits and examine PRODUCTS and SALES tables.
Exhibit 1
- AThe statement executes successfully and produces the required output.
- BThe statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
- CThe statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
- DThe statement produces an error because ITEM_CNT cannot be displayed in the outer query.
56Which statement fails to execute successfully?
- AOption A
- BOption B
- COption C
- DOption D
57Examine this query:
SELECT 2 FROM dual d1 CROSS JOIN dual d2 CROSS JOIN dual d3;
What is returned upon execution?- A8 rows
- B1 row
- C6 rows
- D3 rows
- E0 rows
- Fan error
58Which CREATE TABLE statement is valid?
- AOption A
- BOption B
- COption C
- DOption D
59Examine the description of the CUSTOMERS table:
- ALEFT OUTER JOIN with self join
- Bself join
- CRIGHT OUTER JOIN with self join
- DFULL OUTER JOIN with self join
- Esubquery
60Which task can be performed by using a single Data Manipulation Language (DML) statement?
- Aadding a column constraint while inserting a row into a table
- Badding a column with a default value while inserting a row into a table
- Cremoving all data from a single column on which a unique constraint is defined
- Dremoving all data only from a single column on which a primary key constraint is defined
61Which two are true about dropping columns from a table?
- AA column drop is implicitly committed.
- BA column that is referenced by another column in any other table cannot be dropped.
- CA column can be removed only if it contains no data.
- DMultiple columns can be dropped simultaneously using the ALTER TABLE command.
- EA column must be set as unused before it is dropped from a table.
- FA primary key column cannot be dropped.
62Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)
- AIt is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
- BOnly the primary key can be defined at the column and table level
- CThe foreign key columns and parent table primary key columns must have the same names
- DA table can have only one primary key and one foreign key
- EA table can have only one primary key but multiple foreign keys
- FPrimary key and foreign key constraints can be defined at both the column and table level
- GIt 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
63You 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.)- AThe EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.
- BThe EXECUTION_DATETIME must be of TIMESTAMP data type.
- CThe EXECUTION_DATETIME must be of DATE data type.
- DThe COMPONENT_ID must be of ROWID data type.
- EThe COMPONENT_ID must be of VARCHAR2 data type.
- FThe COMPONENT_ID column must be of CHAR data type.
64Which statement is true regarding the default behavior of the ORDER BY clause?
- AIn a character sort, the values are case-sensitive.
- BNULLs are not included in the sort operation.
- COnly columns that are specified in the SELECT list can be used in the ORDER BY clause.
- DNumeric values are displayed in descending order if they have decimal positions.
65View the Exhibit and examine the structure of the EMP table which is not partitioned and not an
index-organized table. (Choose two.)
- AThe FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
- BThe FIRST_NAME column would be dropped provided at least one column remains in the table.
- CThe FIRST_NAME column would be dropped provided it does not contain any data.
- DThe drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.
66Examine these statements:
CREATE TABLE alter_test (c1 VARCHAR2 (10) , c2 NUMBER (10);
INSERT INTO alter-test VALUES (‘123’, 123);
Which is true about modifying the columns in Alter_TEST?
Which is true about modifying the columns in ALTER_TEST?- Ac1 can be changed to varchar2 (5) and c2 can be changed to number (13, 2) .
- Bc1 can be changed to number (10) and c2 can he changed to vahchar2 (10) .
- Cc2 can be changed to varchar2 (10) but el cannot be changed to number (10).
- Dc2 can be changed to number (5) but ci cannot be changed to varchar2 (5) .
- Ec2 can be changed to number (10) but c2 cannot be changed to varchab2 (10) .
67You execute these commands:
CREATE TABLE customers (customer_id INTEGER, customer_name VARCHAR2 (20) ) TNSERT INTO customers VALUES (1, 'Customer 1 ' ) ;
SAVEPOTNT post_insert;
INSERT INTO customers VALUES (2, 'Customer 2');
<TODO>
SELECT COUNT(*) FROM customers;
Which two, used independently, can replace <todo> so the query returns 1?- AROLLBACK TO SAVEPOINT post_insert;
- BROLLBACK TO post_insert;
- CCOMMIT TO SAVEPOINT post_insert;
- Dcommit;
- EROLLBACK,"
68Which statement is true?
- AIt executes successfully and the row is inserted with a rule PUBLISHER_ID.
- BIt executes successfully only if NULL is explicitly specified in the INSERT statement.
- CIt executes successfully only if the PUBLISHER_ID column name is added to the columns list in the INSERT statement.
- DIt executes successfully only if the PUBLISHER_ID column name is added to the columns list and NULL is explicitly specified in the INSERT statement.
69Examine this statement:
- A1 row
- Ban error
- C0 rows
- D2 rows
70Which two are true about the WITH GRANT OPTION clause?
- AThe grantee must have the GRANT ANY OBJECT PRIVILEGE system privilege to use this option.
- BIt can be used when granting privileges to roles.
- CIt cannot be used to pass on privileges to PUBLIC by the grantee.
- DIt can be used for system and object privileges.
- EIt can be used to pass on privileges to other users by the grantee.
- FThe grantee can grant the object privilege to any user in the database, with or without including this option.
71Which three statements are true regarding indexes? (Choose three.)
- AA SELECT statement can access one or more indices without accessing any tables
- BAn update to a table can result in no updates to any of the table’s indexes
- CA table belonging to one user can have an index that belongs to a different user
- DA UNIQUE index can be altered to be non-unique
- EAn update to a table can result in updates to any or all of the table’s indexes
- FWhen a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped
72See the Exhibit and examine the structure of the PROMOTIONS table:
- AIt generates an error because multiple conditions cannot be specified for the WHEN clause.
- BIt executes successfully and gives the required result.
- CIt generates an error because CASE cannot be used with group functions.
- DIt generates an error because NULL cannot be specified as a return value.
73Which two statements are true? (Choose two.)
- AEmployees 100 and 200 will have the same JOB_ID as before the update command
- BEmployees 100 will have JOB_ID set to the same value as the JOB_ID of employee 200
- CEmployees 100 and 200 will have the same SALARY as before the update command
- DEmployee 200 will have SALARY set to the same value as the SALARY of employee 100
- EEmployee 100 will have SALARY set to the same value as the SALARY of employee 200
- FEmployee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100
74Examine the command:
- AWhen the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.
- BWhen the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.
- CWhen a row in the BOOKS table is deleted, the rows in the BOOK_TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the BOOKS table are also deleted.
- DWhen a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.
75What is the correct output of the above query?
- A+00-300, +00-650, +00 11:12:10.123457
- B+25-00, +54-02, +00 11:12:10.123457
- C+00-300, +54-02, +00 11:12:10.123457
- D+25-00, +00-650, +00 11:12:10.123457
76Which three statements are true regarding subqueries? (Choose three.)
- AMultiple columns or expressions can be compared between the main query and subquery.
- BSubqueries can contain ORDER BY but not the GROUP BY clause.
- CMain query and sudbquery can get data from different tables.
- DSubqueries can contain GROUP BY and ORDER BY clauses.
- EMain query and subquery must get data from the same tables.
- FOnly one column or expression can be compared between the main query and subquery.
77You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)
- ASELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN
inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5; - BSELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5;
- CSELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
- DSELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iUSING (product_id) AND quantity_on_hand < 5;
- ASELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN
78Which two statements are true? (Choose two.)
- AThe USER_SYNONYMS view can provide information about private synonyms.
- BThe user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
- CAll the dynamic performance views prefixed with v$ are accessible to all the database users.
- DThe USER_OBJECTS view can provide information about the tables and views created by the user who queries the view.
- EDICTIONARY is a view that contains the names of all the data dictionary views that the user can access.
79Which two statements are true regarding single row functions? (Choose two.)
- AMOD: returns the quotient of a division.
- BTRUNC: can be used with NUMBER and DATE values.
- CCONCAT: can be used to combine any number of values.
- DSYSDATE: returns the database server current date and time.
- EINSTR: can be used to find only the first occurrence of a character in a string.
- FTRIM: can be used to remove all the occurrences of a character from a string.
80What is the primary difference between the relational database (RDB) and object-oriented database (OODB) models?
- AOODB supports multiple objects in the same database, whereas RDB supports only tables.
- BRDB supports only E.F. Codd’s rules, whereas OODB does not support them.
- COODB incorporates methods with data structure definition, whereas RDB does not allow this.
- DRDB allows the definition of relationships between different tables, whereas OODB does not allow this.
81Examine the contents of the EMP table:
- AIt will return the six employees earning the highest salaries. In descending order.
- BIt will return the six employees earning the lowest salaries. In ascending order.
- CIt will return the five employee's earning the lowest salaries. In ascending order.
- DIt will return the five employees earning the highest salaries. In descending order.
82Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date); Which statement is true?- AAfter executing the ALTER TABLE command, a new column called ORDER_DATE can be added to the ORDERS table.
- BThe ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.
- CROLLBACK can be used to restore the ORDER_DATE column.
- DThe DESCRIBE command would still display the ORDER_DATE column.
83View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.
Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.
SELECT employee_id
FROM employees
WHERE job_id = 'SA_MAN'
SELECT employee_id
FROM job_history
WHERE job_id='SA_MAN';
Choose two correct SET operators which would cause the query to return the desired result. (Choose two.)
- AUNION
- BMINUS
- CINTERSECT
- DUNION ALL
84View 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
- AIt gives an error because the GROUP BY clause is not valid
- BIt executes successfully but does not give the required result
- CIt executes successfully and gives the required result
- DIt gives an error because the ALL keyword is not valid
85Examine the description of the EMPLOYYES table:
- ASELECT 'The first-name is ' ' ' | | first_name | | ' ' ' FROM employees;
- BSELECT 'The first-name is ' | | first_name | | ' ' FROM employees;
- CSELECT 'The first-name is ' ' ' | | first_name | | ' ' ' ' FROM employees;
- DSELECT 'The first-name is \ ' ' | | first_name | | ' ' FROM employees;
- ESELECT 'The first-name is \ ' ' | | first_name | | ' \ ' ' FROM employees;
86Examine the structure of the MEMBERS table
- AIt displays all cities in descending order, within which the last names are further sorted in descending order.
- BIt fails because a column alias cannot be used in the ORDER BY clause.
- CIt fails because a column number and a column alias cannot be used together in the ORDER BY clause.
- DIt displays all cities in ascending order, within which the last names are further sorted in descending order.
87Examine the description of the EMP_DETAILS table given below:
- AAn EMP_IMAGE column cannot be included in the ORDER BY clause.
- BYou can alter the table to include the NOT NULL constraint on the EMP_IMAGE column.
- CYou cannot add a new column to the table with LONG as the data type.
- DAn EMP_IMAGE column can be included in the GROUP BY clause.
88Examine this query:
SELECT TRUNC(ROUND(156.00, -2), -1) FROM DUAL;
What is the result?- A16
- B200
- C100
- D160
- E150
89Which three statements indicate the end of a transaction? (Choose three.)
- Aafter a CREATE statement is issued
- Bafter a SAVEPOINT is issued
- Cafter a SELECT statement is issued
- Dafter a ROLLBACK is issued
- Eafter a COMMIT is issued
90You 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?- ASELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) – ’01-JAN-2019’ FROM DUAL;
- BSELECT SYSDATE – TO_DATE(’01-JANUARY-2019’) FROM DUAL;
- CSELECT ROUND(SYSDATE – ’01-JAN-2019’) FROM DUAL;
- DSELECT ROUND(SYSDATE – TO_DATE(‘01/JANUARY/2019’)) FROM DUAL;
- ESELECT TO_DATE(SYSDATE, ‘DD/MONTH/YYYY’) – ‘01/JANUARY/2019’ FROM DUAL;
91Which two statements are true regarding constraints? (Choose two.)
- AA table can have only one primary key and one foreign key.
- BA table can have only one primary key but multiple foreign keys.
- COnly the primary key can be defined at the column and table levels.
- DThe foreign key and parent table primary key must have the same name.
- EBoth primary key and foreign key constraints can be defined at both column and table levels.
92View the Exhibit and examine the data in the EMPLOYEES table.
- AIt executes successfully but does not give the correct output.
- BIt generates an error because the concatenation operator can be used to combine only two items.
- CIt generates an error because the usage of the ROUND function in the expression is not valid.
- DIt generates an error because the alias is not valid.
- EIT executes successfully and gives the correct output.
93Which two statements are true about single row functions?
- AFLOOR : returns the smallest integer greater than or equal to a specified number
- Bmod : returns the quotient of a division operation
- CCONCAT : can be used to combine any number of values
- Dceil : can be used for positive and negative numbers
- ETRUNC : can be used with number and date values
94You are asked to retrieve the ORDER_ID, product_ID, and total price (UNIT_PRICE multiplied by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
SELECT prder_id, product_id, unit_price*quantity “Total Price”
FROM order_items
WHERE unit_price*quantity > 50000
NATURAL JOIN orders;
Which statement is true regarding the execution of the statement?- AThe statement would execute and provide the desired result.
- BThe statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.
- CThe statement would not execute because the WHERE clause is before the NATURAL JOIN clause.
- DThe statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.
95Examine the data in the ORDERS table:
- A3 <null>
- B3 01-JAN-2019
- C5 01-MAR-2019
- D1 <null>
- E4 01-FEB-2019
- F5 <null>
- G2 <null>
96Which statement adds a column called SALARY to the EMPLOYEES table having 100 rows, which cannot contain null?
- AALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;
- BALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
- CALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
- DALTER TABLE EMPLOYEESADD SALARY NUMBER(8,2) NOT NULL;
97You must display details of all users whose username contains the string 'ch_'.
Which query generates the required output? (Choose the best answer.)- ASELECT *FROM usersWHERE user_name LIKE '%ch_';
- BSELECT *FROM usersWHERE user_name LIKE '%ch_%'ESCAPE'%';
- CSELECT *FROM usersWHERE user_name LIKE 'ch\_%' ESCAPE '_';
- DSELECT *FROM usersWHERE user_name LIKE '%ch\_%' ESCAPE '\';
98You must create a table for a banking application.
One of the columns in the table has these requirements:
A column to store the duration of a short team loan
The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
The maximum loan period is 30 days.
Interest must be calculated based on the number of days for which the loan remains unpaid.
Which data type would you use?- ADATE
- BNUMBER
- CTIMESTAMP
- DINTERVAL DAY TO SECOND
- EINTERVAL YEAR TO MONTH
99Examine the structure of the MARKS table:
- ASELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)FROM marksWHERE subject1 > subject2;
- BSELECT student_name subject1FROM marksWHERE subject1 > AVG(subject1);
- CSELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;
- DSELECT student_name,SUM(subject1)FROM marksWHERE student_name LIKE ‘R%’;
100Which two statements are true about Oracle databases and SQL?
- AA user can be the owner of multiple schemas In the same database.
- BThe database guarantees read consistency at select level on user-created tables.
- CUpdates performed by a database user can be rolled back by another user by using the rollback command.
- DA query can access only tables within the same schema.
- EWhen you execute an update statement, the database Instance locks each updated row.
Information
- Description
- Quiz on barcos101-200, created by efraim vallejo on 25/01/2021.
- Author
- efraim vallejo
- Language
- English
- Views
- 1
- Rating
- No ratings yet Rate this quiz