Name                                Null?                         Type

--------------------------    --------------------    --------------------------

PROMO_ID                        NOT NULL               NUMBER(6)

PROMO_NAME                 NOT NULL               VARCHAR2(30)

PROMO_CATEGORY          NOT NULL               VARCHAR2(30)

PROMO_COST                   NOT NULL               NUMBER(10,2)

You want to display the unique promotion costs in each promotion category.

Which two queries can be used?

Name                      Null?                 Type

--------------------- ----------------- ------------------

PROD_ID                 NOT NULL         NUMBER(2)

STATUS                    NOT NULL        VARCHAR2(15)

The STATUS column contains the values 'IN STOCK' or 'OUT OF STOCK' for each row.

Which two queries will execute successfully?

Name                               Null?                     Type

--------------------------- ------------------- -----------------------

CUST_ID                           NOT NULL            NUMBER

CUST_FIRST_NAME          NOT NULL            VARCHAR2(20)

CUST_LAST_NAM             NOT NULL            VARCHAR2(30)

CUST_INCOME_LEVEL                                   VARCHAR2(30)

CUST_CREDIT_LIMIT                                      NUMBER

For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.

Which query should be used?

Name                          Null?                   Type

-------------------- ---------------- -----------------------

EMP_ID                      NOT NULL          NUMBER

EMP_NAME                                           VARCHAR2(10)

DEPT_ID                                                NUMBER(2)

SALARY                                                NUMBER(8,2)

JOIN_DATE                                          DATE

NLS_DATE_FORMAT is set to DD-MON-YY.

Which query requires explicit data type conversion?

Examine these SQL statements:

Statement 1:

SELECT MAX(unit_price ∗ quantity) "Maximum Order" FROM order_items;

Statement 2:

SELECT MAX(unit_price ∗ quantity) "Maximum Order" FROM order_items GROUP BY order_id;

Which two statements are true?

Name

-------------------------------------

CUST_ID

FIRST_NAME

LAST_NAME

ADDRESS

CITY

You want to display details of all customers who reside in cities starting with the letter D followed by at least two characters.

Which query can be used?

Which two queries execute successfully?

Name                   Null?               Type

------------------- --------------- -----------------------

MEMBER_ID          NOT NULL      VARCHAR2(6)

FIRST_NAME                                VARCHAR2(50)

LAST_NAME          NOT NULL      VARCHAR2(50)

ADDRESS                                      VARCHAR2(50)

CITY                                              VARCHAR2(25)

Examine the partial query:

SELECT city, last_name LNAME FROM members …;

You want to display all cities that contain the string AN. 

The cities must be returned in ascending order, with the last names further sorted in descending order.

Which two clauses must you add to the query?

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, any one of which can complete the query successfully.

Name                        Null?                       Type

---------------------- --------------------- ------------------------

EMP_ID                      NOT NULL               NUMBER

EMP_NAME                                                VARCHAR2(40)

DEPT_ID                                                      NUMBER(2)

SALARY                                                       NUMBER(8,2)

JOIN_DATE                                                  DATE

Which query is valid?

Name                                 Null?                     Type

---------------------------- -------------------- -------------------------

PROD_ID                            NOT NULL             NUMBER

PROD_NAME                                                    VARCHAR2(40)

COST                                                                 NUMBER(8,2)

RELEASE_DATE                                                  DATE

Which query is valid?

Executing:

SELECT xid, status FROM V$$TRANSACTION;

In your session returns:

XID                                    STATUS

------------------------ -------------------

0A0007000A070000      ACTIVE

In which three situations will re-executing this query still return a row but with a different XID, indicating a new transaction has started?

Only the MPLOYEE_ID column is indexed.

Rows exist for employees 100 and 200.

Examine this statement:

UPDATE employees

        SET (job_id, salary) =

                (SELECT job_id, salary

                        FROM employees

                        WHERE employee_id = 200)

WHERE employee_id = 100;

Which two statements are true?

（含图）

You execute this SQL statement:

INSERT INTO sales VALUES (

        23,2300, SYSDATE,

                (SELECT channel_id

                        FROM channels

                        WHERE channel_desc = 'Direct Sales '),

        12,1,500);

Which three statements are true?

Name                                   Null?                   Type

------------------------------ ------------------ -----------------------

TRANSACTION_ID                NOT NULL          VARCHAR2(6)

TRANSACTION_DATE                                      DATE

AMOUNT                                                         NUMBER(10,2)

CUSTOMER_ID                                                 VARCHAR2(6)

The table has 100 rows

Examine this sequence of statements issued in a new session:

INSERT INTO books VALUES ('ADV112 ', 'Adventures of Tom Sawyer ', NULL, NULL);

SAVEPOINT a;

DELETE FROM books;

ROLLBACK TO SAVEPOINT a;

ROLLBACK;

Which two statements are true?

Name                      Null?                  Type

--------------------- ----------------- --------------------------

TRANSACTION_ID   NOT NULL         VARCHAR2(6)

TRANSACTION_DATE                        DATE

AMOUNT                                           NUMBER(10,2)

CUSTOMER_ID                                   VARCHAR2(6)

Now examine this sequence of statements issued in a new session:

INSERT INTO books VALUES ('ADV112', 'Adventures of Tom Sawyer', NULL, NULL);

SAVEPOINT a;

DELETE FROM books;

ROLLBACK TO SAVEPOINT a;

ROLLBACK;

Which three statements are true?