【1Z0-051点滴积累】Q1~Q10 (2011-09-10)

Q1.  SELECT promo_category FROM promotions

        MINUS

        SELECT promo_category FROM promotions

        WHERE promo_subcategory = 'discount';

        SELECT promo_category FROM promotions

        INTERSECT

        SELECT promo_category FROM promotions

        WHERE promo_subcategory <> 'discount';

Q2. UPDATE promotions SET promo_cost = promo_cost + 100

       WHERE TO_CHAR(promo_end_date,'yyyy') > '2000';

       SELECT promo_begin_date FROM promotions

       WHERE TO_CHAR(pro_begin_date,'mon dd yy') = 'jul 01 98';

Q3. CURRVAL is used to refer to the last sequence number that has been generated.

       When the MAXVALUE  limit for a sequence for reached , you can increase the MAXVALUE limit by     using the ALTER SEQUENCE statement.

Q4. CREATE TABLE product

       (pcode                  number(2),

        pname                 varchar2(10)

       );

       INSERT INTO product VALUES (1, 'pen');

       INSERT INTO product VALUES (2, 'pencil');

       SAVEPOINT a;

       UPDATE product SET pcode = 10 WHERE pcode = 1;

       SAVEPOINT b;

       DELETE FROM product WHERE pcode = 2;

       COMMIT;

       DELETE FROM product WHERE pcode = 10;

       ROLLBACK to SAVEPOINT a;

       The ROLLBACK generates an error.

       NO SQL statements are rolled back.

Q5. COMMIT, CREATE, ROLLBACK statement/commands would cause a transaction to end.

Q6. SELECT INTERVAL '300' MONTH,

                         INTERVAL '54-2' YEAR TO MONTH,

                         INTERVAL '11:12:10.1234567' HOUR TO SECOND

        FROM dual;

        OUTPUT: +25-00, +54-02, +00 11:12:10.1234567

Q7. Subqueries can contain GROUP BY and ORDER BY clauses

       Main query and subquery can get data from different tables

       Multiple columns or expressions can be compared between the main query and subquery

Q8. UPDATE customers SET cust_credit_limit = null 

       WHERE cust_income_level IS NULL;

Q9. The default internal storage of dates is in the numeric format.

       The RR date format automatically calculates the century from the SYSDATE function but allows the user to entry the century if required.

Q10. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot be updated.

         A Data Manipulation Language (DML) operation can be performed on a view that is created with the subquery having all the NOT NULL columns of a table.