Quiz
barcos301-400
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 (99)
1Which three statements are true reading subqueries? (Choose three.)
- AA Main query can have many subqueries.
- BA subquery can have more than one main query.
- CThe subquery and main query must retrieve date from the same table.
- DThe subquery and main query can retrieve data from different tables.
- EOnly one column or expression can be compared between the subquery and main query.
- FMultiple columns or expressions can be compared between the subquery and main query.
2You need to display the first names of all customers from the CUSTOMERS table that contain the character ‘e’ and have the character ‘a’ in the second last position.
Which query would give the required output?- ASELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, ‘e’)<>0 ANDSUBSTR(cust_first_name, -2, 1)=’a’;
- BSELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, ‘e’)<>'' ANDSUBSTR(cust_first_name, -2, 1)=’a’;
- CSELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, ‘e’)IS NOT NULL ANDSUBSTR(cust_first_name, 1, -2)=’a’;
- DSELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, ‘e’)<>0 ANDSUBSTR(cust_first_name, LENGTH(cust_first_name), -2)=’a’;
3Which two statements are true about the rules of precedence for operators? (Choose two.)
- AThe concatenation operator | | is always evaluated before addition and subtraction in an expression
- BMultiple parentheses can be used to override the default precedence of operators in an expression
- CArithmetic operators with equal precedence area evaluated from left to right within an expression
- DNULLS influence the precedence of operators in an expression
- EThe + binary operator has the highest precedence in an expression in a SQL statement
4Which statement is true about Data Manipulation Language (DML)?
- ADML automatically disables foreign ley constraints when modifying primary key values in the parent table.
- BEach DML statement forms a transaction by default.
- CA transaction can consist of one or more DML statements.
- DDML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint.
5You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.
Which two DELETE statements are valid (Choose two.)
- ADELETE *FROM order_itemsWHERE order_id IN (SELECT order_id)FROM ordersWHERE order_status IN (0,1));
- BDELETEFROM (SELECT * FROM order_items I,orders oWHERE i.order_id = o.order_id AND order_status IN (0,1));
- CDELETE FROM order_items iWHERE order_id = (SELECT order_id FROM orders oWHERE i.order_id = o.order_id AND order_status IN (0,1));
- DDELETEFROM order_itemsWHERE order_id IN (SELECT order_idFROM ordersWHERE orders_status in (0,1));
6A subquery is called a single-row subquery when .
- AThere is only one subquery in the outer query and the inner query returns one or more values
- BThe inner query returns a single value to the outer query.
- CThe inner query uses an aggregating function and returns one or more values.
- DThe inner query returns one or more values and the outer query returns a single value.
7Which two queries execute successfully? (Choose two.)
- AOption A
- BOption B
- COption C
- DOption D
- EOption E
8Examine the data in the CUST_NAME column of the CUSTOMERS table:
- AWHERE SUBSTR (cust_name, INSTR (cust_name, ‘’) +1) LIKE ‘Mc%’
- BWHERE INITCAP (SUBSTR (cust_name, INSTR(cust_name, ‘’)+1)) IN (‘MC%’, ‘Mc%)
- CWHERE UPPER (SUBSTR (cust_name, INSTR(cust_name, ‘’)+1)) LIKE UPPER (‘MC%’)
- DWHERE SUBSTR (cust_name, INSTR (cust_name, ‘’) +1) LIKE ‘Mc%’ OR ‘MC%’
- EWHERE INITCAP (SUBSTR (cust_name, INSTR(cust_name, ‘’)+1)) LIKE ‘Mc%’
9Examine this statement:
- ASTATUS
- BORDER_ID
- CPRODUCT_ID
- DSERIAL_NO
- EORDER_TOTAL
- FORDER_DATE
10Which two statements are true about substitution variables? (Choose two.)
- AA substitution variable can be used with any clause in a SELECT statement
- BA substitution variable used to prompt for a column name must be enclosed in a single quotation marks
- CA substitution variable prefixed with & always prompts only once for a value in a session
- DA substitution variable can be used only in a SELECT statement
- EA substitution variable used to prompt for a column name must be enclosed in double quotation marks
- FA substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session
11Examine the structure of the INVOICE table.
- ASELECT inv_no,NVL2(inv_date,'Pending','Incomplete')FROM invoice;
- BSELECT inv_no,NVL2(inv_amt,inv_date,'Not Available')FROM invoice;
- CSELECT inv_no,NVL2(inv_date,sysdate-inv_date,sysdate)FROM invoice;
- DSELECT inv_no,NVL2(inv_amt,inv_amt*.25,'Not Available')FROM invoice;
12View the Exhibit and examine the structure of the CUSTOMERS table.
- ASELECT NVL(TO_CHAR(cust_credit_limit*.15),‘Not Available’) “NEW CREDIT”FROM customers;
- BSELECT TO_CHAR(NVL(cust_credit_limit*.15,‘Not Available’)) “NEW CREDIT”FROM customers;
- CSELECT NVL(cust_credit_limit*.15,‘Not Available’) “NEW CREDIT”FROM customers;
- DSELECT NVL(cust_credit_limit,‘Not Available’)*.15 “NEW CREDIT”FROM customers;
13You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.
Which method or feature should you use?- ACompare Period ADDM report.
- BAWR Compare Period report.
- CActive Session History (ASH) report.
- DTaking a new snapshot and comparing it with a preserved snapshot.
14Which two statements are true regarding a SAVEPOINT? (Choose two.)
- AA SAVEPOINT does not issue a COMMIT
- BOnly one SAVEPOINT may be issued in a transaction
- CRolling back to a SAVEPOINT can undo a TRUNCATE statement
- DRolling back to a SAVEPOINT can undo a CREATE INDEX statement
- ERolling back to a SAVEPOINT can undo a DELETE statement
15Which two are true about constraints?
- AA constraint can be disabled even if the constrained column contains data.
- BConstraints are enforced only during insert operations.
- CA column with a FOREIGN key constraint can never contain a null value.
- DA column with a unique constraint can contain a null value.
- EAll constraints can be defined at the table or column level.
16
Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)- ADuring parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
- BDuring execution, the Oracle server may read data from storage if the required data is not already in memory.
- CDuring row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
- DDuring optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution
17You must write a query that prompts users for column names and conditions every time it is executed.
The user must be prompted only once for the table name.
Which statement achieves those objectives?- ASELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';
- BSELECT &col1, &col2FROM "&table"WHERE &condition = &cond;
- CSELECT &col1, &col2FROM &&tableWHERE &condition = &cond;
- DSELECT &col1, &col2FROM &&tableWHERE &condition = &&cond
18The SALES table has columns PROD_ID and QUANTITY_SOLD of data type NUMBER.
Which two queries execute successfully?- ASELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id HAVING COUNT(*) >10;
- BSELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY prod_id HAVING COUNT(*) >10;
- CSELECT COUNT (prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;
- DSELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) >10;
- ESELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;
19View the Exhibit and examine the description of the tables.
TABLE SH
- AThe statement will execute successfully and a new row will be inserted into the SALES table.
- BA product can have a different unit price at different times.
- CThe statement will fail because a subquery may not be contained in a VALUES clause.
- DThe statement will fail if a row already exists in the SALES table for product 23.
- EA customer can exist in many countries.
- FThe SALES table has five foreign keys.
20Examine 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:
STUDEN_ID FIRST_NAMK LASTNAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true?- AThe ER must have a many-to-many relationship between the students and projects entitles tb.it must be resolved Into one-to-many relationships.
- BAn associative table must be created with a composite key of student_id and project_id, which is the foreign key linked to the Stand projects entitles.
- CSTUDENT_ID must be the primary key In the students entity and foreign key In the projects entity.
- DPORJECTI_ID must be the primary key In the projects entity and foreign key in the students The ER must have a one-to-many relationship between the students and projects entitles.
21Which two statements are true regarding the DELETE and TRUNCATE commands?
- ADELETE can be used to remove rows from only one table in one statement.
- BDELETE can be used to remove rows from multiple tables in one statement.
- CDELETE can be used to remove rows only for tables that are parents for a child table that has a referential integrity constraint referring to the parent.
- DDELETE can be used to remove data from specific columns as well as complete rows.
- EDELETE and TRUNCATE can be used for tables that are parents for a child table that has a referential integrity constraint having an ON DELETE rule.
22What will be the result?
- AIt gives an error because the ROUP BY clause is not valid.
- BIt executes successfully and gives the required result.
- CIt gives an error because the all keyword is not valid.
- DIt executes successfully but does not give the required result.
23Which three statements are true about inner and outer joins?
- AOuter joins can be used when there are multiple join conditions on two tables.
- BOuter joins can only be used between two tables per query.
- CA full outer join returns matched and unmatched rows.
- DAn inner join returns matched rows.
- EA full outer join must use Oracle syntax.
- FA left or right outer join returns only unmatched rows.
24Which three statements are true about external tables?
- AThey can be used In queries containing sorts.
- BThey can be used in queries containing Joins.
- CThey can be indexed.
- DDML statements can modify them.
- EThey can be temporary tables.
- FTheir metadata Is stored In the database.
25Examine the structure of the SALES table. (Choose two.)
- AIt will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.
- BIt will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.
- CIt will not be created because of the invalid WHERE clause.
- DIt is created with no rows.
- EIt has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.
26Examine the structure of the PROGRAMS table:
- ASELECT NVL(ADD_MONTHS(END_DATE,1)SYSDATE)FROM programs;
- BSELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))FROM programs;
- CSELECT NVL(MONTHS_BETWEEN(start_date,end_date),‘Ongoing’)FROM programs;
- DSELECT NVL(TO_CHAR(MONTHS_BETWEEN(start-date,end_date)),‘Ongoing’)FROM programs;
27What is true about non-equijoin statement performance?
- AThe BETWEEN condition always performs less well than using the >= and <= conditions.
- BThe join syntax used makes no difference to performance.
- CTable aliases can improve performance.
- DThe BETWEEN condition always performs better than using the >= and <= conditions.
- EThe Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
28An 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?- Awhen a DBA issues a successful shutdown transactional statement and the user then issues a commit
- Bwhen a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session
- Cwhen a commit statement is issued by the same user from another session in the same database instance
- Dwhen the session logs out successfully
- Ewhen a create index statement is executed successfully in the same session
- Fwhen a DBA issues a successful shutdown immediate statement and the user then issues a commit
29Examine the following two SQL statements: Statement 1
SQL>SELECT faculty_name, COUNT(student_id)
FROM student
JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student
NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?
- AOnly statement 2 executes successfully and gives the required result.
- BOnly statement 1 executes successfully and gives the required result.
- CBoth statements 1 and 2 execute successfully and give different results.
- DBoth statements 1 and 2 execute successfully and give the same required result.
30Examine the structure of the EMPLOYEES table:
- ASELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) ManagerFROM employees e JOIN employees mON (e.manager_id = m.employee_id);
- BSELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) ManagerFROM employees e LEFT OUTER JOIN employees mON (e.manager_id = m.employee_id);
- CSELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) ManagerFROM employees e RIGHT OUTER JOIN employees mON (e.manager_id = m.employee_id);
- DSELECT e.last_name, e.hire_date, NVL(m.last_name, ‘No Manager’) ManagerFROM employees e NATURAL JOIN employees mON (e.manager_id = m.employee_id).
31View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks require subqueries?
- ADisplay the number of products whose PROD_LIST_PRlCE is more than the average prod_list_price.
- BDisplay the total number of products supplied by supplier 102 which have a product status of obsolete.
- CDisplay the minimum prod_list_price for each product status.
- DDisplay products whose prod_min_PRICE is more than the average PROD_LIST_PRlCE of all products, and whose status is orderable.
- EDisplay suppliers whose PR0D_LIST_PRICE is less than 1000.
32Which two statements are true regarding savepoints? (Choose two.)
- ASavepoints may be used to ROLLBACK.
- BSavepoints can be used for only DML statements.
- CSavepoints are effective only for COMMIT.
- DSavepoints are effective for both COMMIT and ROLLBACK.
- ESavepoints can be used for both DML and DDL statements.
33Examine the SQL statement used to create the TRANSACTION table.
SQL > CREATE TABLE transaction
(trn_id char(2) primary key,
start_date date DEFAULT SYSDATE,
end_date date NOT NULL);
The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for START_DATE?- AINSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10));
- BINSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'));
- CINSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014');
- DINSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1',, '10-DEC-2014');
34You issue the following command to drop the PRODUCTS table:
SQL > DROP TABLE products;
Which three statements are true about the implication of this command? (Choose three.)- AAll data along with the table structure is deleted.
- BA pending transaction in the session is committed.
- CAll indexes on the table remain but they are invalidated.
- DAll views and synonyms on the table remain but they are invalidated.
- EAll data in the table is deleted but the table structure remains.
35Which query provides the correct output?
- ASELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;
- BSELECT e.last_name, m.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;
- CSELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;
- DSELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;
36Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_credit_limit FROM customers WHERE (UPPER(cust_last_name) LIKE ‘A%’ OR
UPPER (cust_last_name) LIKE ‘B%’ OR UPPER (cust_last_name) LIKE ‘C%’) AND cust_credit_limit < 10000;
SQL>SELECT cust_last_name, cust_credit_limit FROM customers WHERE UPPER (cust_last_name) BETWEEN ‘A’ AND ‘C’
AND cust_credit_limit < 10000;
Which statement is true regarding the execution of the above queries?
- AOnly the second query gives the correct result
- BBoth execute successfully but do not give the required result
- COnly the first query gives the correct result
- DBoth execute successfully and give the same result
37Which two statements are true regarding the COUNT function? (Choose two.)
- AA SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.
- BCOUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column.
- CCOUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column.
- DCOUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any column.
- EIt can only be used for NUMBER data types.
38Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS?
- AThe name off each column in the first Select list must match the name of the corresponding column In each subsequent select list.
- BThe must be an equal number of columns In each select list.
- CNone of the set operators can be used when selecting CLOB columns.
- DEach select statement In the query can have an order by clause.
- EThe FOR update clause cannot be specified.
39Examine these statements and the result:
CREATE SEQUENCE customer_seq CACHE 10;
SELECT customer_seq. NEXTVAL FROM DUAL; NEXTVAL 1
Now examine this command:
ALTER SEQUENCE customer_soq <MISaiNG CLAUSE>;
What must replace missing clause for customer_seq. nfxtvai. to return 11?- Acycle 11
- BNOCACHE
- CSTART WITH 11
- DMINVALUE 11
- EINCREMENT BY 10
40The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?
- AINTERSECT
- BUNION ALL
- CMINUS
- DUNION
41Which two statements are true about a full outer join?
- AIt includes rows that are returned by an inner join.
- BIt returns only unmatched rows from both tables being joined.
- CIt includes rows that are returned by a Cartesian product.
- DIt returns matched and unmatched rows from both tables being joined.
- EThe Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.
42Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name "Last name" FROM customers
WHERE country_id = 10 UNION
SELECT cust_id CUST_NO, cust_last_name FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query. (Choose three.)- AORDER BY "Last name"
- BORDER BY 2, cust_id
- CORDER BY CUST_NO
- DORDER BY 2,1
- EORDER BY "CUST_NO"
43You 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 SYSDATE - TO_DATE('01-JANUARY-2019•) FROM DUAL;
- BSELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;
- CSELECT ROUND(SYSDATE - TO_DATE('01/JANUARY/2019')) FROM DUAL;
- DSELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY") - '01/JANUARY/2019" FROM DUAL;
- ESELECT TO_CHAR(SYSDATE, *DD-MON-YYYY•) - '01-JAN-2019' FROM DUAL;
44Which three are true about multitable INSERT statements?
- AThey can be performed on external tables using SQL* Loader.
- BThey can be performed on relational tables.
- CThey can be performed only by using a subquery.
- DThey can insert each computed row into more than one table.
- EThey can be performed on views.
- FThey can be performed on remote tables.
45The 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?- ADELETE order_id FROM orders WHERE order_total < 1000;
- BDELETE FROM orders WHERE order_total < 1000;
- CDELETE orders WHERE order_total < 1000;
- DDELETE * FROM orders WHERE order_total < 1000;
- EDELETE FROM orders;
46The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?
- AALTER TABLE ordersMODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
- BALTER TABLE ordersADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
- CALTER TABLE ordersMODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
- DALTER TABLE ordersADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
47Examine the description of the CUSTOMERS table:
- AOption A
- BOption B
- COption C
- DOption D
- EOption E
48Which query is valid?
- ASELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;
- BSELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;
- CSELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;
- DSELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
49
Which three queries use valid expressions? (Choose three.)
- ASELECT product_id, (expiry_date – delivery_date) * 2 FROM products;
- BSELECT product_id, unit_price | | 5 “Discount”, unit_price + surcharge – discount FROM products;
- CSELECT product_id, unit_price, 5 “Discount”, unit_price + surcharge – discount FROM products;
- DSELECT product_id, unit_price, unit_price + surcharge FROM products;
- ESELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
- FSELECT product_id, expiry_date * 2 FROM products;
50Which statement is true regarding the UNION operator?
- ABy default, the output is not sorted.
- BNull values are not ignored during duplicate checking.
- CNames of all columns must be identical across all select statements.
- DThe number of columns selected in all select statements need not be the same.
51Which two statements are true about the SET VERIFY on command?
- AIt displays values for variables created by the DEFINE command.
- BIt can be used in SQL Developer and SQL*Plus.
- CIt displays values for variables prefixed with &&.
- DIt can be used only in SQL*Plus.
- EIt displays values for variables used only in the where clause of a query.
52Which SQL statement must you use?
- ASELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)
- BSELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN
.department_details d ON (c.depatrment_id=d.department_id) - CSELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)
- DSELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id)
53Which two are the minimal requirements for a self-join? (Choose two.)
- AOnly equijoin conditions may be used in the query.
- BOuter joins must not be used in the query.
- CThere must be a condition on which the self-join is performed.
- DNo other condition except the self-join may be specified.
- EThe table used for the self-join must have two different alias names in the query.
54Which two statements will do an implicit conversion?
- ASELECT FROM + customers WHERE TO-CHAR (customer_id) = ‘0001’;
- BSELECT FROM + customers WHERE (customer_id) = 0001;
- CSELECT FROM + customers WHERE (customer_id) = '0001';
- DSELECT FROM + customers WHERE insert_date = DATE ‘2019-01-01’;
- ESELECT FROM + customers WHERE insert_date = ’01-JAN-19’;
55Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?- ASELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM customers;
- BSELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50AS “50% Credit Limit”FROM customers;
- CSELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50AS “50% Credit Limit”FROM customers;
- DSELECT cust_income_level, DISTINCT cust_credit_limit * 0.50AS “50% Credit Limit”FROM customers;
56Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?
- ASelect deptno. no LISTAGG(ename, ',') WITHIN GROUP (GROUP BY deptno) AS employee-list FROM emp
ORDER BY emp - BSelect deptno. LISTAGG(ename, ',') WITHIN GROUP AS employee-list FROM emp
GROUP BY emp - CSelect deptno. LISTAGG(ename, ',') WITHIN GROUP AS employee-list FROM emp
ORDER BY ename; - DSelect deptno. no LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employee-list FROM emp
ORDER BY emp
- ASelect deptno. no LISTAGG(ename, ',') WITHIN GROUP (GROUP BY deptno) AS employee-list FROM emp
57Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?
- AThey are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any
other WHEN clause. - BThey are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
- CThey are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
- DThe insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.
- AThey are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any
58Which three statements are true about indexes and their administration in an Oracle database?
- AAn index can be created as part of a CREATE TABLE statement.
- BA DROP INDEX statement always prevents updates to the table during the drop operation.
- CA UNIQUE and non-unique index can be created on the same table column.
- DA descending index is a type of function-based index.
- EIf a query filters on an indexed column then it will always be used during execution of the query.
- FAn INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table.
59Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)
- Adisplaying a date in a nondefault format
- Bfinding the number of characters in an expression
- Csubstituting a character string in a text expression with a specified string
- Dcombining more than two columns or expressions into a single column in the output
60Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)
- AWHERE order_date_IN ( TO_DATE('OCT 21 2003','MON DD YYYY'), TO_CHAR('NOV 21 2003','MON DD YYYY') )
- BWHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MON DD YYYY')
- CWHERE TO_CHAR(order_date,'MON DD YYYY') = 'JAN 20 2003'
- DWHERE order_date > TO_DATE('JUL 10 2006','MON DD YYYY')
61Which two statements are true regarding the creation and storage of data in the above table structure? (Choose two.)
- AThe CUST_STATUS column would store exactly one character.
- BThe TRANS_VALIDITY column would have a maximum size of one character.
- CThe CUST_CREDIT_LIMIT column would be able to store decimal values.
- DThe CUST_STATUS column would give an error.
- EThe TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
- FThe TRANS_VALIDITY column would give an error.
62Which two are true about multitable insert statements?
- AThe conditional insert all statement inserts rows into a single table by aggregating source rows.
- BThe unconditional insert all statement must have the same number of columns In both the source and target tables.
- CThe conditional insert first statement always Inserts a row Into a single table.
- DThey always use subqueries.
- EThey can transform a row from a source table Into multiple rows In a target table.
63Which statement is true regarding the above two queries?
- APerformance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column.
- BThere would be no change in performance.
- CPerformance would degrade in query 2.
- DPerformance would improve in query 2.
64Which three statements are true about performing Data Manipulation Language (DML) operations on a view in an Oracle Database?
- AViews cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
- BViews 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.
- CViews 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.
- DThe WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
- EInsert statements can always be done on a table through a view.
- FViews cannot be used to add or modify rows in an underlying table if the defining query of the view contains aggregating functions.
65Examine this SQL statement:
SELECT department_id "DEPT_ID", department_name, 'b'
FROM departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a'
FROM departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output? (Choose two.)
- AORDER BY DEPT_NAME;
- BORDER BY DEPT_ID;
- CORDER BY 'b';
- DORDER BY 3;
66Which two statements are true regarding the USING and ON clauses in table joins?
- ABoth USING and ON clauses can be used for equijoins and nonequijoins.
- BA maximum of one pair of columns can be joined between two tables using the ON clause.
- CThe ON clause can be used to join tables on columns that have different names but compatible data types.
- DThe WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
67Evaluate the following CREATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE, cust_id NUMBER (4));
CREATE TABLE ord_items (ord _no NUMBER (2), item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );
Why would the ORD_ITEMS table not get created?- ASYSDATE cannot be used with the CHECK constraint.
- BThe BETWEEN clause cannot be used twice for the same table.
- 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.
68The ORDERS table a primary key constrain 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 CASCADS.
There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000. Which three DELETE statements execute successfully?- ADELETE * FROM orders WHERE order_total < 1000;
- BDELETE FROM orders;
- CDELETE order_id FROM orders WHERE order_total < 1000;
- DDELETE orders WHERE order_total < 1000;
- EDELETE FROM orders WHERE order_total < 1000;
69Which two statements are true?
- APRODUCT_PRICE contains the value zero by default if no value is assigned to it.
- BPRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
- CEXPIRY_DATE cannot be used in arithmetic expressions.
- DPRODUCT_ID can be assigned the PRIMARY KEY constraint.
- EEXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.
- FPRODUCT_NAME cannot contain duplicate values.
70Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?
- AEach row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent WHEN clauses.
- BAll rows are evaluated by all the three WHEN clauses.
- CEach row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
- DThe INSERT statement will return an error because the ELSE clause is missing.
71Which two WHERE conditions give the same result?
- AWHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ OR member_id IN (‘A101’, ‘A102’));
- BWHERE (borrowed_date = SYSDATE AND transaction_type = ‘RM’) OR member_id IN (‘A101’, ‘A102’);
- CWHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND (member_id = A101’ OR member_id = ‘A102’));
- DWHERE borrowed_date = SYSDATE AND transaction_type = ‘RM’ OR member_id IN (‘A101’, ‘A102’);
- EWHERE borrowed_date = SYSDATE AND (transaction_type = ‘RM’ AND member_id = ‘A101’ OR member_id = ‘A102’);
72Which query retrieves the number of products with a null list price?
- ASELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price IS NULL;
- BSELECT COUNT (list_price) FROM product_information WHERE list_price IS NULL;
- CSELECT COUNT (list_price) FROM product_information WHERE list_price = NULL;
- DSELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price IS NULL;
73BOOK_SEQ is an existing sequence in your schema.
Which two CREATE TABLE commands are valid?
- AOption A
- BOption B
- COption C
- DOption D
- EOption E
74View and Exhibit and examine the structure and data in the INVOICE table.
- Ainv_date = '15-february-2008' :uses implicit conversion
- Binv_amt = '0255982' : requires explicit conversion
- Cinv_date > '01-02-2008' : uses implicit conversion
- DCONCAT(inv_amt, inv_date) : requires explicit conversion
- Einv_no BETWEEN '101' AND '110' : uses implicit conversion
75View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
- AINSERT INTO ordersVALUES(1, ’10-mar-2007’, ‘direct’,(SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), 1000);
- BINSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), order_total)VALUES(1, ’10-mar-2007’, ‘direct’, &&customer_id, 1000);
- CINSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_idAND c.cust_last_name=’Roberts’ AND c.credit_limit=600 )VALUES (1, ’10-mar-2007’, ‘direct’, (SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), 1000);
- DINSERT INTO orders (order_id, order_data, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name=’Roberts’ ANDcredit_limit=600), order_total)VALUES (1, ’10-mar-2007’, ‘direct’, &customer_id, 1000).
76View the exhibit and examine the structure of the STORES table.
- ASELECT name, concat(address||', '||city||', ',country) AS full_address, start_date, property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,'01-JAN-2000') <=36;
- BSELECT name, concat(address||', '||city||', ',country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE
TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36; - CSELECT name, address||', '||city||', '||country AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
- DSELECT name, concat(address||', '||city||', ', country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
7777
- AIt drops any triggers defined on the table.
- BIt always retains the space used by the removed rows.
- CA ROLLBACK statement can be used to retrieve the deleted data.
- DIt retains the integrity constraints defined on the table.
- EIt retains the indexes defined on the table.
- FA FLASHBACK TABLE statement can be used to retrieve the deleted data.
78In which three situations does a new transaction always start?
- Awhen issuing a TRUNCATE statement after a SELECT statement was issued in the same session
- Bwhen issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session
- Cwhen issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
- Dwhen issuing the first Data Manipulation Language (DML) statement after a COMMIT or ROLLBACK statement was issued in the same session
- Ewhen issuing a DML statement after a DML statement failed in the same session
- Fwhen issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
79Which two statements are true about single-row functions? (Choose two.)
- ACEIL: can be used for positive and negative numbers
- BFLOOR: returns the smallest integer greater than or equal to a specified number
- CTRUNC: can be used with NUMBER and DATE values
- DCONCAT: can be used to combine any number of values
- EMOD: returns the quotient of a division operation
80Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.)
- ARead data from an external table and load it into a table in the database
- BCreate a directory object for an external table
- CExecute DML statements on an external table
- DQuery data from an external table
- ERead data from a table in the database and insert it into an external table
- FCreate a directory object for a flat file
81In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?
- Asecond normal form
- Bfirst normal form
- Cthird normal form
- Dfourth normal form
82You create a table by using this command:
CREATE TABLE rate_list (rate NUMBER(6,2)); Which two are true about executing statements?- AINSERT INTO rate_list VALUES (-10) produces an error.
- BINSERT INTO rate_list VALUES (87654.556) inserts the value as 87654.6.
- CINSERT INTO rate_list VALUES (0.551) inserts the value as .55.
- DINSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99.
- EINSERT INTO rate_list VALUES (0.999) produces an error.
- FINSERT INTO rate_list VALUES (-.9) inserts the value as -.9.
83Examine the data in the CUST_NAME column of the CUSTOMERS table.
- ASELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') “CUST NAME”FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;
- BSELECT LPAD(SUBSTR(cust_name, INSTR(cust_name, ' ')),LENGTH(cust_name),'*') “CUST NAME”FROM customersWHERE INSTR(cust_name, ' ',-1,2)<>0;
- CSELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') “CUST NAME”FROM customersWHERE INSTR(cust_name, ' ',1,-2)<>0;
- DSELECT LPAD(SUBSTR(cust_name, INSTR (cust_name ' ')),LENGTH(cust_name) - INSTR(cust_name, ' '), '*') “CUST NAME”FROM customersWHERE INSTR(cust_name, ' ',1,2)<>0;
84Examine the structure of the ORDERS table:
- AIt executes successfully but does not give the correct output.
- BIt executes successfully and gives the correct output.
- CIt returns an error because the TO_CHAR function is not valid.
- DIt return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.
85Which three are true about system and object privileges? (Choose three.)
- AWITH GRANT OPTION can be used when granting an object privilege to both users and roles
- BAdding a primary key constraint to an existing table in another schema requires a system privilege
- CAdding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege
- DRevoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect
- ERevoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.
- FWITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC
86View the Exhibit and examine the data in the PROMOTIONS table.
- ASELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
- BSELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
- CSELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
- DSELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';
87Which set operator generates the required output?
- AINTERSECT
- BUNION
- CPLUS
- DMINUS
- ESUBTRACT
88Which two tasks can be performed by using Oracle SQL statements? (Choose two.)
- Achanging the password for an existing database user
- Bconnecting to a database instance
- Cquerying data from tables in different databases
- Dstarting up a database instance
- Eexecuting operating system (OS) commands in a session
89View the Exhibit and examine the structure of the PRODUCTS table.
- AIt generates an error because = is not valid and should be replaced by the IN operator.
- BIt executes successfully but does not give the correct output.
- CIt executes successfully and gives the correct output.
- DIt generate an error because the subquery does not have a GROUP BY clause.
90Which three are true about the MERGE statement?
- AIt can combine rows from multiple tables conditionally to insert into a single table.
- BIt can merge rows only from tables.
- CIt can use subqueries to produce source rows.
- DIt can update, insert, or delete rows conditionally in multiple tables.
- EIt can update the same row of the target table multiple times.
- FIt can use views to produce source rows.
91The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id FROM sales;
Which statement is true?
- AThe NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
- BThe NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
- CThe NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
- DThe NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
9292
- A0 rows
- Ban error
- C1 row
- D2 rows
93Which three statements are true? (Choose three.)
- AThe data dictionary is created and maintained by the database administrator.
- BData dictionary views consist of joins of dictionary base tables and user-defined tables.
- CThe usernames of all users including database administrators are stored in the data dictionary.
- DThe USER_CONS_COLUMNS view should be queried to find the names of columns to which constraints apply.
- EBoth USER_OBJECTS and CAT views provide the same information about all objects that are owned by the user.
- FViews with the same name but different prefixes, such as DBA, ALL and USER, reference the same base tables from the data dictionary.
94Which three statements are true about multiple-row subqueries? (Choose three.)
- AThey can contain a subquery within a subquery.
- BThey can return multiple columns as well as rows.
- CThey cannot contain a subquery within a subquery.
- DThey can return only one column but multiple rows.
- EThey can contain group functions and GROUP BY and HAVING clauses.
- FThey can contain group functions and the GROUP BY clause, but not the HAVING clause.
95View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.
- AINSERT INTO(SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600)VALUES (1,'10-mar-2007', 'direct', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
- BINSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total)VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);
- CINSERT INTO ordersVALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
- DINSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total)VALUES (1,'10-mar-2007', 'direct', &&customer_id, 1000);
96View the Exhibit and examine the structure of the PRODUCT table.
- Adisplay all products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable
- Bdisplay the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’
- Cdisplay the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.
- Ddisplay suppliers whose PROD_LIST_PRICE is less than 1000
- Edisplay the minimum PROD_LIST_PRICE for each product status
9797
- A1-d, 2-b, 3-a, and 4-c
- B1-c, 2-d, 3-a, and 4-b
- C1-a, 2-b, 3-c, and 4-d
- D1-c, 2-a, 3-b, and 4-d
98You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’.
Which SQL statement would give the required result?- ASELECT TO_CHAR(TO_DATE(’11-oct-2007’), ‘fmDdthsp “of” Month, Year’)FROM DUAL;
- BSELECT TO_CHAR(‘11-oct-2007’, ‘fmDdspth “of” Month, Year’)FROM DUAL;
- CSELECT TO_CHAR(TO_DATE(‘11-oct-2007’), ‘fmDdspth of month, year’)FROM DUAL;
- DSELECT TO_DATE(TO_CHAR(’11-oct-2007’), ‘fmDdspth “of” Month, Year’))FROM DUAL;
99Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1 START WITH 100
INCREMENT BY 10
MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?- A100
- Ban error
- C10
- D1
Information
- Description
- Quiz on barcos301-400, created by efraim vallejo on 01/02/2021.
- Author
- efraim vallejo
- Language
- English
- Views
- 0
- Rating
- No ratings yet Rate this quiz