Oracle qualification may be the earths most well-respected global qualification. Thus 1Z0-051 is very essential for those who are anxious to enter the particular That business. However it is extremely tough if youre active operating or studying to pass the particular Oracle Database: SQL Fundamentals I test. Actualtests Oracle 1Z0-051 research instructions will help you saving a large amount of time,power and resource in the 1Z0-051 test. Youll be able to manage this particular 1Z0-051 test successfully through our own 1Z0-051 pdf and test powerplant. We are able to keep your item on time and invite individuals to discover Oracle information effortlessly. Also you can download the particular Oracle 1Z0-051 pdf test version free.

2016 Jul oracle database 11g sql fundamentals 1 1z0-051 videos:

Q171. - (Topic 1) 

See the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and ITEMS tables: 


The PROD_ID column is the foreign key in the SALES table, which references the PRODUCTS table. Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively. 

Evaluate the following the CREATE TABLE command: 

Exhibit: 

Which statement is true regarding the above command? 

A. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match 

B. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table 

C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition 

D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table 

Answer: B 

Explanation: 

Creating a Table Using a Subquery 

Create a table and insert rows by combining the CREATE 

TABLE statement and the AS subquery option. 

CREATE TABLE table 

[(column, column...)] 

AS subquery; 

Match the number of specified columns to the number of subquery columns. 

Define columns with column names and default values. 

Guidelines 

The table is created with the specified column names, and the rows retrieved by the 

SELECT statement are inserted into the table. 

The column definition can contain only the column name and default value. 

If column specifications are given, the number of columns must equal the number of 

columns in the subquery SELECT list. 

If no column specifications are given, the column names of the table are the same as the 

column names in the subquery. 

The column data type definitions and the NOT NULL constraint are passed to the new 

table. Note that only the explicit NOT NULL constraint will be inherited. The PRIMARY KEY 

column will not pass the NOT NULL feature to the new column. Any other constraint rules 

are not passed to the new table. However, you can add constraints in the column definition. 


Q172. - (Topic 1) 

Evaluate the following SQL commands: 


The command to create a table fails. Identify the reason for the SQL statement failure? 

(Choose all that apply.) 

A. You cannot use SYSDATE in the condition of a CHECK constraint. 

B. You cannot use the BETWEEN clause in the condition of a CHECK constraint. 

C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column. 

D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD NO is also the FOREIGN KEY. 

Answer: A,C 

Explanation: 

CHECK Constraint The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions: References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns Calls to SYSDATE, UID, USER, and USERENV functions Queries that refer to other values in other rows A single column can have multiple CHECK constraints that refer to the column in its 

definition. 

There is no limit to the number of CHECK constraints that you can define on a column. 

CHECK constraints can be defined at the column level or table level. 

CREATE TABLE employees 

(... 

salary NUMBER(8,2) CONSTRAINT emp_salary_min 

CHECK (salary > 0), 


Q173. - (Topic 2) 

In which four clauses can a sub query be used? (Choose four.) 

A. in the INTO clause of an INSERT statement 

B. in the FROM clause of a SELECT statement 

C. in the GROUP BY clause of a SELECT statement 

D. in the WHERE clause of a SELECT statement 

E. in the SET clause of an UPDATE statement 

F. in the VALUES clause of an INSERT statement 

Answer: A,B,D,E 

Explanation: 

A: a sub query is valid on the INTO clause of an ISERT Statement 

B: a sub query can be used in the FROM clause of a SELECT statement 

D: a sub query can be used in the WHERE clause of a SELECT statement, 

E: a sub query can be used in the SET clauses of an UPDATE statement, 

Incorrect Answer: 

Csub query cannot be used 

F: is incorrect. 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 6-5 


Q174. - (Topic 1) 

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. 


There is only one customer with the CUST_LAST_NAME column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? 

A. 

INSERT INTO orders VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000); 

B. 

INSERT INTO orders (order_id,order_date,order_mode, 

(SELECT customer_id 

FROM customers 

WHERE cust_last_name='Roberts' AND 

credit_limit=600),order_total) 

VALUES(1,'10-mar-2007', 'direct', &&customer_id, 1000); 

C. 

INSERT INTO(SELECT o.order_id, o.order_date,o.order_mode,c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' ANDc.credit_limit=600 ) VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000); 

D. 

INSERT INTO orders (order_id,order_date,order_mode, 

(SELECT customer_id 

FROM customers 

WHERE cust_last_name='Roberts' AND 

credit_limit=600),order_total) 

VALUES(1,'10-mar-2007', 'direct', &customer_id, 1000); 

Answer: A 


Q175. - (Topic 2) 

Which constraint can be defined only at the column level? 

A. UNIQUE 

B. NOT NULL 

C. CHECK 

D. PRIMARY KEY 

E. FOREIGN KEY 

Answer: B 

Explanation: 

The NOT NULL constraint can be defined only at the column level. It enforces that a value must be defined for this column such that the column may not be NULL for any row. 

Incorrect Answers A:The UNIQUE constraint enforces uniqueness on values in the constrained column. It can be defined not only at the column level. C:The CHECK constraint enforces that values added to the constrained column must be present in a static list of values permitted for the column. 

D:The PRIMARY KEY constraint stipulates that values in the constrained column(s) must be unique and not NULL. If the primary key applies to multiple columns, then the combination of values in the columns must be unique and not NULL. E:The FOREIGN KEY constraint enforces that only values in the primary key of a parent table may be included as values in the constrained column(s) of the child table. 

OCP Introduction to Oracle 9i: SQL Exam Guide, Jason Couchman, p. 227-232 Chapter 5: Creating Oracle Database Objects 


certifyforall.com

Refresh 1z0-051 books:

Q176. - (Topic 2) 

You own a table called EMPLOYEES with this table structure: 

EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE What happens when you execute this DELETE statement? 

DELETE employees; 

A. You get an error because of a primary key violation. 

B. The data and structure of the EMPLOYEES table are deleted. 

C. The data in the EMPLOYEES table is deleted but not the structure. 

D. You get an error because the statement is not syntactically correct. 

Answer: C 

Explanation: Explanation: You can remove existing rows from a table by using the DELETE statement. DELETE [FROM] table [WHEREcondition]; Incorrect Answer: AStatement will not cause error BDelete statement will not delete the table structure DStatement will not cause error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 8-19 


Q177. - (Topic 1) 

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

Answer: D 


Q178. - (Topic 1) 

Examine the structure and data of the CUSTJTRANS table: 

CUSTJRANS 

Name Null? Type 

CUSTNO NOT NULL CHAR(2) TRANSDATE DATE TRANSAMT NUMBER(6.2) CUSTNO TRANSDATE TRANSAMT 

11 01-JAN-07 1000 

22 01-FEB-07 2000 

33 01-MAR-07 3000 

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

A. SELECT transdate + '10' FROM custjrans; 

B. SELECT * FROM custjrans WHERE transdate = '01-01-07': 

C. SELECT transamt FROM custjrans WHERE custno > '11': 

D. SELECT * FROM custjrans WHERE transdate='01-JANUARY-07': 

E. SELECT custno - 'A' FROM custjrans WHERE transamt > 2000: 

Answer: A,C,D 


Q179. - (Topic 2) 

Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all that apply.) 

A. It locks only the columns specified in the SELECT list. 

B. It locks the rows that satisfy the condition in the SELECT statement. 

C. It can be used only in SELECT statements that are based on a single table. 

D. It can be used in SELECT statements that are based on a single or multiple tables. 

E. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued. 

Answer: B,D 

Explanation: 

FOR UPDATE Clause in a SELECT Statement Locks the rows in the EMPLOYEES table where job_id is SA_REP. Lock is released only when you issue a ROLLBACK or a COMMIT. If the SELECT statement attempts to lock a row that is locked by another user, the database waits until the row is available, and then returns the results of the SELECT statement. FOR UPDATE Clause in a SELECT Statement When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is required because the number of records locked at any given time is (by default) kept to the absolute minimum: only those records that have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the “before image” of the data). There are times, however, when you may want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking. When you issue a SELECT...FOR UPDATE statement, the relational database management system (RDBMS) automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, thereby holding the records “for your changes only.” No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT. You can append the optional keyword NOWAIT to the FOR UPDATE clause to tell the Oracle server not to wait if the table has been locked by another user. In this case, control will be returned immediately to your program or to your SQL Developer environment so that you can perform other work, or simply wait for a period of time before trying again. Without the NOWAIT clause, your process will block until the table is available, when the locks are released by the other user through the issue of a COMMIT or a ROLLBACK command. 


Q180. - (Topic 2) 

The STUDENT_GRADES table has these columns: 

STUDENT_ID NUMBER(12) 

SEMESTER_END DATE 

GPA NUMBER(4,3) 

Which statement finds the highest grade point average (GPA) per semester? 

A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL; 

B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL; 

C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end; 

D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades; 

E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL; 

Answer: C 

Explanation: Explanation: For highest gpa value MAX function is needed, for result with per semester GROUP BY clause is needed 

Incorrect Answer: Aper semester condition is not included Bresult would not display the highest gpa value Dinvalid syntax error Einvalid syntax error Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 5-7