Premium

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



Question : Examine the data in the ORD_ITEMS table:
Evaluate the given query:
Which statement is true regarding the outcome of the above query?
: Examine the data in the ORD_ITEMS table:
1. It gives an error because the having clause should be specified after the group by clause.
2. It gives an error because all the aggregate functions used in the having clause must be specified in the select list.
 3. Access Mostly Uused Products by 50000+ Subscribers
double the minimum quantity of that item in the table.
4. It displays the item nos with their average quantity where the average quantity is more than
double the overall minimum quantity of all the items in the table.




Correct Answer : Get Lastest Questions and Answer :
Exp:





Question : View the Exhibit and examine the data in the promotions table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the post
category that were launched before January 1, 2000.
Which SQL statement would you use?
: View the Exhibit and examine the data in the promotions table.
1. A
2. B
 3. Access Mostly Uused Products by 50000+ Subscribers
4. D


Correct Answer : Get Lastest Questions and Answer :




Question : You need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Status (character data type), that contains the value 'active1 if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by the
employee
Which is the correct syntax to create this table?
: You need to create a table with the following column specifications:
1. It fails because the DB_8K_CACHE_SIZE parameter cannot be changed dynamically.
2. It succeeds only if memory is available from the autotuned components if SGA.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It fails because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE.

Correct Answer : Get Lastest Questions and Answer :

Explanation: CLOB Character data (up to 4 GB)
NUMBER [(p, s)] Number having precision p and scale s (Precision is the total number of decimal
digits and scale is the number of digits to the right of the decimal point; precision can range from 1
to 38, and scale can range from -84 to 127.)


Related Questions

Question : Examine 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?

A. Disable the primary key in the STORES table.
B. Use CASCADE keyword with DELETE statement.
C. DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
D. Disable the FOREIGN KEY in SALES table and then delete the rows.
E. Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.
 : Examine the create table statements for the stores and sales tables.
1. A,C,D

2. C,D,E
3. B,C,E

4. C,D,E
5. A,C,E


Question : You want to create a sales
table with the following column
specifications and data types:

SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified
PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified

Which statement would create the table?

 : You want to create a sales
1. A
2. B
3. C
4. D


Question : Examine the data in the CUST_NAME column of the customers table.
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
 : Examine the data in the CUST_NAME column of the customers table.
1. A
2. B
3. C
4. D


Question : : Evaluate the following query:
SQL> SELECT TRUNC(ROUND(156.00, -1), -1) FROM DUAL;
What would be the outcome?
 : : Evaluate the following query:
1. 16
2. 100
3. 160
4. 200
5. 150


Question : You want to display percent of the rows from
the sales table for products with the lowest
AMOUNT_SOLD and also want to include the rows that
have the same AMOUNT_SOLD even if
this causes the output to exceed 5 percent of the rows.
Which query will provide the required result?
 : You want to display percent of the rows from
1. A
2. B
3. C
4. D


Question : You need to list the employees
in DEPARTMENT_ID 30 in a single row, ordered by HIRE_DATE.
Examine the sample output:
Which query will provide the required output?
 : You need to list the employees
1. A
2. B
3. C
4. D