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

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

All in One

Exam Oracle Database 11g : SQL Fundamentals I
Number 1Z0-051
File Name Oracle.Pass4sure.1z0-051.2015-04-03.2e.219q.zip
Size 15.53 Mb
Posted February 02, 2018

Download




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';



Comments