Download Oracle.Pass4sure.1z0-051.2015-04-03.2e.219q.vcex

Download Dump

File Info

Exam Oracle Database 11g: SQL Fundamentals I
Number 1z0-051
File Name Oracle.Pass4sure.1z0-051.2015-04-03.2e.219q.vcex
Size 8.32 Mb
Posted April 03, 2015
Downloaded 4



How to open VCEX & EXAM Files?

Files with VCEX & EXAM extensions can be opened by ProfExam Simulator.

Purchase
Coupon: EXAMFILESCOM

Coupon: EXAMFILESCOM
With discount: 20%





Demo Questions

Question 1
Evaluate the SQL statement:
TRUNCATE TABLE DEPT; 

Which three are true about the SQL statement? (Choose three.)

  • A: It releases the storage space used by the table.
  • B: It does not release the storage space used by the table.
  • C: You can roll back the deletion of rows after the statement executes.
  • D: You can NOT roll back the deletion of rows after the statement executes.
  • E: An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
  • F: You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table



Question 2
You need to design a student registration database that contains several tables storing academic information. 
The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key. 
You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. 
Which statement creates the foreign key?

  • A: CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));
  • B: CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));
  • C: CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));
  • D: CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));



Question 3
Here is the structure and data of the CUST_TRANS table:
Exhibit:

 
Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table. 
Which three SQL statements would execute successfully? (Choose three.)

  • A: SELECT transdate + '10' FROM cust_trans;
  • B: SELECT * FROM cust_trans WHERE transdate = '01-01-07';
  • C: SELECT transamt FROM cust_trans WHERE custno > '11';
  • D: SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
  • E: SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;



Question 4
See the Exhibit and examine the structure of the CUSTOMERS table:
 
Using the CUSTOMERS table, you need to generate a report that shown the average credit limit for customers in WASHINGTON and NEW YORK. 
Which SQL statement would produce the required result?

  • A: SELECT cust_city, AVG(cust_credit_limit) 
    FROM customers 
    WHERE cust_city IN ('WASHINGTON','NEW YORK') 
    GROUP BY cust_credit_limit, cust_city;
  • B: SELECT cust_city, AVG(cust_credit_limit) 
    FROM customers 
    WHERE cust_city IN ('WASHINGTON','NEW YORK') 
    GROUP BY cust_city,cust_credit_limit;
  • C: SELECT cust_city, AVG(cust_credit_limit) 
    FROM customers 
    WHERE cust_city IN ('WASHINGTON','NEW YORK') 
    GROUP BY cust_city;
  • D: SELECT cust_city, AVG(NVL(cust_credit_limit,0)) 
    FROM customers 
    WHERE cust_city IN ('WASHINGTON','NEW YORK');



Question 5
Where can sub queries be used? (Choose all that apply)

  • A: field names in the SELECT statement
  • B: the FROM clause in the SELECT statement
  • C: the HAVING clause in the SELECT statement
  • D: the GROUP BY clause in the SELECT statement
  • E: the WHERE clause in only the SELECT statement
  • F: the WHERE clause in SELECT as well as all DML statements



Question 6
Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

  • A: SELECT TO_CHAR(1890.55,'$99G999D00') FROM DUAL;
  • B: SELECT TO_CHAR(1890.55,'$9,999V99') FROM DUAL;
  • C: SELECT TO_CHAR(1890.55,'$0G000D00') FROM DUAL; 
  • D: SELECT TO_CHAR(1890.55,'$99G999D99') FROM DUAL;
  • E: SELECT TO_CHAR(1890.55,'$9,999D99') FROM DUAL;



Question 7
Evaluate the following SQL statement:

 
Which statement is true regarding the outcome of the above query?

  • A: It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement
  • B: It executes successfully and displays rows in the descending order of PROMO_CATEGORY
  • C: It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement
  • D: It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators



Question 8
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:

   
Which MERGE statement is valid?

  • A: MERGE INTO new_employees c 
    USING employees e 
    ON (c.employee_id = e.employee_id) 
    WHEN MATCHED THEN 
    UPDATE SET
  • B: name = e.first_name ||','|| e.last_name 
    WHEN NOT MATCHED THEN 
    INSERT 
    value 
    S(e.employee_id, e.first_name ||', '||e.last_name);
  • C: MERGE new_employees c 
    USING employees e 
    ON (c.employee_id = e.employee_id) 
    WHEN EXISTS THEN 
    UPDATE SET
  • D: name = e.first_name ||','|| e.last_name 
    WHEN NOT MATCHED THEN INSERT 
    valueS(e.employee_id, e.first_name ||', '||e.last_name); 
  • E: MERGE INTO new_employees cUSING employees e 
    ON (c.employee_id = e.employee_id) 
    WHEN EXISTS THEN 
    UPDATE SET
  • F: name = e.first_name ||','|| e.last_name 
    WHEN NOT MATCHED THEN 
    INSERT 
    value 
    S(e.employee_id, e.first_name ||', '||e.last_name);
  • G: MERGE new_employees c 
    FROM employees e ON (c.employee_id = e.employee_id) 
    WHEN MATCHED THEN 
    UPDATE SET
  • H: name = e.first_name ||','|| e.last_name 
    WHEN NOT MATCHED THEN 
    INSERT INTO 
    new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);



Question 9
The COMMISSION column shows the monthly commission earned by the employee. 
Exhibit 

 
Which two tasks would require sub queries or joins in order to be performed in a single step? (Choose two.)

  • A: listing the employees who earn the same amount of commission as employee 3
  • B: finding the total commission earned by the employees in department 10
  • C: finding the number of employees who earn a commission that is higher than the average commission of the company
  • D: listing the departments whose average commission is more that 600
  • E: listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
  • F: listing the employees whose annual commission is more than 6000



Question 10
Examine the structure of the STUDENTS table:

 
You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. 
Which SQL statement accomplishes this task?

  • A: SELECT student_ id, marks, ROWNUM "Rank" 
    FROM students 
    WHERE ROWNUM <= 10 
    AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99 
    AND course_id = 'INT_SQL' 
    ORDER BY marks DESC;
  • B: SELECT student_id, marks, ROWID "Rank" 
    FROM students 
    WHERE ROWID <= 10 
    AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' 
    AND course_id = 'INT_SQL' 
    ORDER BY marks;
  • C: SELECT student_id, marks, ROWNUM "Rank" 
    FROM (SELECT student_id, marks 
    FROM students 
    WHERE ROWNUM <= 10 
    AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' 
    AND course_id = 'INT_SQL' 
    ORDER BY marks DESC);
  • D: SELECT student_id, marks, ROWNUM "Rank" 
    FROM (SELECT student_id, marks 
    FROM students 
    WHERE (finish_date BETWEEN '01-JAN-99 AND '31-DEC-99' AND course_id = `INT_SQL' 
    ORDER BY marks DESC) 
    WHERE ROWNUM <= 10 ;
  • E: SELECTstudent id, marks, ROWNUM "Rank" 
    FROM(SELECT student_id, marks 
    FROM students 
    ORDER BY marks) 
    WHEREROWNUM <= 10 
    ANDfinish date BETWEEN '01-JAN-99' AND '31-DEC-99' 
    ANDcourse_id = `INT_SQL';









CONNECT US


ProfExam
PROFEXAM WITH A 20% DISCOUNT

You can buy ProfExam with a 20% discount..

Get Now!


HOW TO OPEN VCEX AND EXAM FILES

Use ProfExam Simulator to open VCEX and EXAM files
ProfExam Screen



HOW TO OPEN VCE FILES

Use VCE Exam Simulator to open VCE files
Avanaset