Oracle Database 12c SQL 試験
Question No : 1
Which two tasks can be performed by using Oracle SQL statements?
Question No : 2
Evaluate the following two queries:
Which statement is true regarding the above two queries?
Question No : 3
Which statement is true regarding external tables?
Question No : 4
Which two statements are true about sequences created in a single instance database? (Choose two.)
Explanation: Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost. Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes. Because sequences are not tied directly to tables, the same sequence can be used for multiple tables. However, if you do so, each table can contain gaps in the sequential numbers.
Modifying a Sequence
If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement To remove a sequence, use the DROP statement:
DROP SEQUENCE dept_deptid_seq;
Question No : 5
View the Exhibits and examine the structures of the costs and promotions tables?
Evaluate the following SQL statement:
SQL> SELECT prod_id FROM costs
WHERE promo_id IN (SELECT promo_id FROM promotions
WHERE promo_cost < ALL
(SELECT MAX(promo_cost) FROM promotions
GROUP BY (promo_end_datepromo_
What would be the outcome of the above SQL statement?
Question No : 6
Examine the following query:
What is the output of this query?
Question No : 7
Examine the structure of the members table: What is the outcome?
Question No : 8
Which two statements are true regarding multiple-row subqueries? (Choose two.)
Question No : 9
Examine thestructureof the members table:
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?
Question No : 10
Examine the structure of the employees table.
There is a parent/child relationship betweenEMPLOYEE_IDandMANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager asthe employee whoseEMPLOYEE_ID123.
Which query provides the correct output? A)
Question No : 11
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.
The CUSTOMERS table contains the current location of all currently active customers. The 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?
Question No : 12
Examine the structure of the invoice table.
Which two SQL statements would execute successfully? A)
Question No : 13
Which statement is true regarding the INTERSECT operator?
INTERSECT Returns only the rows that occur in both queries’ result sets, sorting them and removing duplicates. The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.
Question No : 14
Which two statements are true regarding the COUNT function? (Choose two.)
Using the COUNT Function
The COUNT function has three formats:
COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfy the condition in the WHERE clause. In contrast, COUNT(expr) returns the number of non-null values that are in the column identified by expr.
COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.
Question No : 15
Which statements are true? (Choose all that apply.)