1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

1Z0 051 Memory Notes

Discussion in 'Training and Certification' started by wyfwong, Jun 4, 2011.

  1. wyfwong

    wyfwong Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    1 Given PROMOTIONS table, which SQL statements are correct?
    = SELECT…, DECODE(NLV( …)) FROM promotions;
    + SELECT … DECODE(… DECODE(…)) FROM promotion;
    2 COALESCE = all expressions must be the same data type
    3 Commands to cause transaction to end = COMMIT + CREATE + ROLLBACK
    4 INTERSECT = no. of columns and data types must be identical
    5 Calculate no. of days from 1st Jan 2007 till date =
    SELECT SYSDATE – TO_DATE(‘01/JANUARY/2007’) FROM DUAL;
    + SELECT SYSDATE – TO_DATE(’01-JANUARY-2007’) FROM DUAL;
    6 SELECT faculty_name, COUNT(smdent_id) FROM student JOIN faculty USING (faculty_id, location_id) GROUP BY faculty_name;
    + SELECT faculty_name, COUNT(smdent_id) FROM student NATURAL JOIN faculty
    = Both statements execute successfully and same result
    7 CUSTOMERS table, those customers who do not have create limit appear last
    = SELECT cust_last_name,cust_credit_limit FROM customers ORDER BY cust_credit_limit;
    + SELECT cust_last_name,cust_credit_limit FROM customers ORDER BY cust_credit_limit NULLS LAST;
    8 Subqueries = NOT operators can be used with IN, ANY, ALL in multiple-row subqueries
    9 COUNT = COUNT(*) returns no. of rows including duplicate rows and rows containing NULL
    + COUNT(DISTINT inv_amt) returns no. of rows excluding rows containing duplicates and NULL values in INV_AMT
    10 USING in table joins = restrict no. of columns in NATURAL join +
    Join tables that have columns with same name and compatible data types
    11 Constraints = UNIQUE constraint can contain NULL + Constraint can be disabled even contains data
    12 Indexes = created on tables and clusters + create more than one index using same columns with different combinations
    13 Savepoints = may be used to ROLLBACK + can be used for only DML statements
    14 Display date for first Mon day of next month,
    SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’MON’),’dd ‘’is the first Monday for ‘’ fmmonth rrrr’)
    FROM DUAL;
    = executes successfully and returns correct result
    15 PROGRAMS table, SQL statements execute successfully =
    SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;
    SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),’Outgoing’) FROM programs;
    16 Single row functions = always return single result + can return data type value different from the reference
    17 SORT BY = values are case-sensitive in a character sort
    18 In SALES table, SELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*)>10
    GROUP BY prod_id having COUNT(*)>10;
    = error because COUNT(*) should be only in HAVING clause but not in WHERE clause
    19 Transaction completes when = ROLLBACK + Data Definition Language is executed + TRUNCATE
    20 In ORDERS table, you want to alter table by creating PRIMARY KEY on ORD_ID =
    Can add primary key even if data exists provided no duplicate values
    21 Views = view created with subquery having pseudo column ROWNUM cannot be updated + DML operation can be performed on view created with subquery having all NOT NULL columns
    22 In CUSTOMERS table, SELECT cust_name as “NAME”, cust_credit_limit/2 AS MIDPOINT, MIDPOINT+100 AS “MAX_LOWER_LIMIT” FROM customers;
    = Alias cannot be used in an expression (MIDPOINT+100)
    23 DROP TABLE products = All data with table structure is deleted + Pending transaction is committed + All view and synonyms remain but invalidated
    24 CREATE TABLE ord_otems (ord_no NUMBER(4) DEFAULT ord_seq.NEXTVAL NOT NULL,
    Expiry_date date CHECK (expiry_date > SYSDATE),

    = cannot use SYSDATE in CHECK + cannot use NEXTVAL sequence as DEFAULT
    25 USING and ON in table joins = ON can join tables on columns having different names but compatible data types + WHERE can apply additional conditions in SELECT containing ON or USING
    26 UNION = NULL not ignored during duplicate checking
    27 SQL functions built in Oracle = display date in nondefault format + Find no. of chars in expression + sunstitute char string with specified string
    28 In ORD and ORDITEMS tables, ORD_NO and ITEM_NO are composite PRIMARY KEY in ORDITEMS =
    CREATE INDEX ord_idx2 ON ord_items(ord_no) +
    CREATE INDEX ord_idx3 ON ord_items (item_no);
    29 In PROMOTIONS, display all promo categories that do not have ‘discount’ =
    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’;
    30 In CUSTOMERS table, increase credit limit by 15%, customers whose credit limit has not been entered should have “Not Available” displayed
    = SELECT NVL(TO_CHAR(cust_credit_limit*.15),’Not Available’) “NEW CREDIT” FROM customers;
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    What in God's name is this?
     
  3. wyfwong

    wyfwong Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    31 Indexes = when table dropped the corresponding indexes are automatically dropped +
    Nondeferrable PRIMARY KEY or UNIQUE KEY automatically creates unique index +
    For each DML performed the corresponding indexes are automatically updated
    32 Dates = default internal storage is numeric + RR date format automatically calculates century from SYSDATE but allow user to enter century
    33 INSERT INTO new_customers (cust_id,cust_name,cust_city)
    VALUES (SELECT cust_id,cust_first_name||’ ‘ || cust_last_name,cust_city
    FROM customers WHERE cust_id > 23004);
    = VALUES cannot be used in INSERT with a subquery
    34 Arithmetic operations using SQL function
    = addition (sum) + raising to power (raise) + lowest value (min)
    35 In SALES, CUSTOMES, PRODUCTS, ITEMS tables,
    CREATE TABLE new_sales(prod_id,cust_id,order_date DEFAULT SYSDATE)
    AS SELECT prod_id,cust_id,time_id FROM sales;
    = NEW_SALES table created and all NOT NULL constraints defined passed to new table
    36 Display 1890.55 as $1,89.55 =
    SELECT TO_CHAR(1890.55,’$0G000D00’) FROM DUAL; +
    SELECT TO_CHAR(1890.55,’$99G999D00’) FROM DUAL; +
    SELECT TO_CHAR(1890.55,’$99G999D99’) FROM DUAL;
    37 Sequences = CURRVAL refers to last sequence number generated + When MAXVALUE reached, increase MAXVALUE by ALTER SEQUENCE
    38 In PRODUCTS table,
    SELECT prod_id, NVL2(prod_expiry_date,prod_expiry_date + 15,’’) FROM products;
    SELECT prod_id, NVL(prod_expiry_date,prod_expiry_date + 15) FROM products;
    = Both statements execute and different results
    39 Display date 11-Oct-2007 in words as Eleventh of October, Two Thousand Seven =
    SELECT TO_CHAR(TO_DATE(’11-oct-2007’),’fmDspth ‘’of’’ Month, Year’) FROM DUAL;
    40 SELECT TRUNC(ROUND(156.00,-1),-1) FROM DUAL; = 160
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Could you care to explain what you are doing here or I will be forced to lock/delete this.