Premium

Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)



Question : Examine the structure and data of the CUST_TRANS table:
Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table. Which three
SQL statements would execute successfully?
1. SELECT transdate + '10' FROM cust_trans;
2. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
3. Access Mostly Uused Products by 50000+ Subscribers
4. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
5. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;

 : Examine the structure and data of the CUST_TRANS table:
1. 2, 4, 5
2. 2, 1, 4, 3, 5
3. Access Mostly Uused Products by 50000+ Subscribers
4. 1, 2, 4, 5
5. 1, 3,4

Correct Answer : Get Lastest Questions and Answer :
Exp:




Question : View the Exhibit and examine the structure of the customers table.
NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY
that have the same data types and size as the corresponding columns in the customers table.
Evaluate the following insert statement:
The insert statement fails when executed.
What could be the reason?
 : View the Exhibit and examine the structure of the customers table.
1. The values clause cannot be used in an INSERT with a subquery.
2. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.
3. Access Mostly Uused Products by 50000+ Subscribers
4. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS table.



Correct Answer : Get Lastest Questions and Answer :
Exp: Copying Rows from Another Table, Write your INSERT statement with a subquery:
Do not use the VALUES clause.
Match the number of columns in the INSERT clause to those in the subquery.
Inserts all the rows returned by the subquery in the table, sales_reps.





Question : YOU need to display the date ll-oct- in words as 'Eleventh of October, Two Thousand
Seven'.
Which SQL statement would give the required result?

 : YOU need to display the date ll-oct- in words as 'Eleventh of October, Two Thousand
1. A
2. B
3. Access Mostly Uused Products by 50000+ Subscribers
4. D



Correct Answer : Get Lastest Questions and Answer :
Exp:




Related Questions


Question : Which statement adds a column called salary
to the employees table having 100 rows, which
cannot contain null?

 : Which statement adds a column called salary
1. A
2. B
3. C
4. D


Question : Which two statements are true regarding single row functions?
A. MOD: returns the quotient of a division
B. TRUNC: can be used with number and date values
C. CONCAT: can be used to combine any number of values
D. SYSDATE: returns the database server current date and time
E. INSTR: can be used to find only the first occurrence of a character in a string
F. TRIM: can be used to remove all the occurrences of a character from a string
  : Which two statements are true regarding single row functions?
1. A,C
2. A,B
3. D,E
4. B,F
5. B,D




Question : Which two statements are true regarding the count function?
A. The count function can be used only for CHAR, VARCHAR2, and NUMBER data types.
B. Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns.
C. Count (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
D. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.
E. A select statement using the COUNT function with a DISTINCT keyword cannot have a where clause.
 :  Which two statements are true regarding the count function?
1. A,C
2. A,B
3. D,E
4. B,E
5. B,D



Question : View the Exhibits and examine the
structures of the products, sales,
and customers tables.
You need to generate a
report that gives details of the customer's
last name, name of the product,
and the quantity sold
for a customers in 'Tokyo'.
Which two queries
give the required result?
  : View the Exhibits and examine the
1. A,D
2. A,E
3. B,C
4. B,D
5. A,C



Question : View the Exhibit and examine the structure of the products table.
Evaluate the following query:
What would be the outcome of executing the above SQL statement?
 : View the Exhibit and examine the structure of the products table.
1. It produces an error.
2. It shows the names of all products in the table.
3. It shows the names of products whose list price is the second highest in the table.
4. It shows the names of all products whose list price is less than the maximum list price.



Question : You issued the following command:
SQL> DROP TABLE employees;
Which three statements are true?
A. All uncommitted transactions are committed.
B. All indexes and constraints defined on the table being dropped are also dropped.
C. Sequences used in the employees table become invalid.
D. The space used by the employees table is reclaimed immediately.
E. The employees table can be recovered using the rollback command.
F. The employees table is moved to the recycle bin.
  :   You issued the following command:
1. A,C,D
2. A,B,F
3. C,D,E
4. B,D,E
5. B,C,F