Exam Code: 1Z0-051 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database: SQL Fundamentals I
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-051 Exam.
2016 Jun 1Z0-051 Study Guide Questions:
Q101. - (Topic 2)
Examine the structure of the PRODUCTS table:
You want to display the names of the products that have the highest total value for UNIT_PRICE *QTY_IN_HAND.
Which SQL statement gives the required output?
SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products);
SELECT prod_name FROM products WHERE (unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);
SELECT prod_name FROM products GROUP BY prod_name HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand) FROM products GROUP BY prod_name);
WHERE (unit_price * qty_in_hand) = (SELECT MAX(SUM(unit_price * qty_in_hand))
GROUP BY prod_name;
Q102. - (Topic 1)
Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
Which DELETE statement is valid?
A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees);
B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees);
C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’);
D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’);
Q103. - (Topic 2)
Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
The SEQ1 sequence has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?
D. an error
But why the answer is not "C" ? Because you didn't specify the MINVALUE for the sequence. If you check the sequence definition that you created it will have the default value of 1, which it reverts to when cycling. If you wanted to keep the minimum value you would need to specify it in the sequence creation. sequence Is the name of the sequence generator INCREMENT BY n Specifies the interval between sequence numbers, where n is an integer (If this clause is omitted, the sequence increments by 1.) START WITH n Specifies the first sequence number to be generated (If this clause is omitted, the sequence starts with 1.) MAXVALUE n Specifies the maximum value the sequence can generate NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and –1 for a descending sequence (This is the default option.) MINVALUE n Specifies the minimum sequence value NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and –(10^26) for a descending sequence (This is the default option.)
CYCLE | NOCYCLE Specifies whether the sequence continues to generate values after reaching its maximum or minimum value (NOCYCLE is the default option.) CACHE n | NOCACHE Specifies how many values the Oracle server preallocates and keeps in memory (By default, the Oracle server caches 20 values.)
Avant-garde 1z0-051 dumps pdf free download:
Q104. - (Topic 1)
Which three statements are true about multiple-row sub queries? (Choose three.)
A. They can contain a subquery within a sub query.
B. They can return multiple columns as well as rows.
C. They cannot contain a sub query within a sub query.
D. They can return only one column but multiple rows.
E. They can contain group functions and GROUP BY and HAVING clauses.
F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.
Q105. - (Topic 2)
Which describes the default behavior when you create a table?
A. The table is accessible to all users.
B. Tables are created in the public schema.
C. Tables are created in your schema.
D. Tables are created in the DBA schema.
E. You must specify the schema when the table is created.
sorted by highest to lowest is DESCENDING order
Incorrect Answer: Agrant the table privilege to PUBLIC Blogin as sysoper Dlogin as DBA or sysdba Eno such option is allow.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 9-9
Q106. - (Topic 1)
You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?
A. SELECT TO_CHAR('11-oct-2007', 'fmDdspth "of" Month, Year') FROM DUAL; B. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdspth of month, year') FROM DUAL;
C. SELECT TO_CHAR(TO_DATE('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL;
D. SELECT TO_DATE(TO_CHAR('11-oct-2007','fmDdspth ''of'' Month, Year')) FROM DUAL;
Using the TO_CHAR Function with Dates TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format of datetime stored in a character string. For example, the datetime format model for the string '11-Nov-1999' is 'DD-Mon-YYYY'. You can use the TO_CHAR function to convert a date from its default format to the one that you specify. Guidelines
The format model must be enclosed with single quotation marks and is case-sensitive.
The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.
The names of days and months in the output are automatically padded with blanks.
To remove padded blanks or to suppress leading zeros, use the fill mode fm element.
Elements of the Date Format Model
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the month
MM Two-digit value for the month
MON Three-letter abbreviation of the month
MONTH Full name of the month
YYYY Full year in numbers
YEAR Year spelled out (in English)
Approved 1z0-051 oracle database 11g sql fundamentals i:
Q107. - (Topic 2)
In the CUSTOMERS table, the CUST_CITY column contains the value 'Paris' for the
Evaluate the following query:
What would be the outcome?
A. Abigail PA B. Abigail Pa
C. Abigail IS
D. an error message
Q108. - (Topic 2)
Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. The column with a UNIQUE constraint can store NULLS .
C. A constraint is enforced only for an INSERT operation on a table.
D. You can have more than one column in a table as part of a primary key.
Q109. - (Topic 1)
Which statement is true regarding the UNION operator?
A. The number of columns selected in all SELECT statements need to be the same
B. Names of all columns must be identical across all SELECT statements
C. By default, the output is not sorted
D. NULL values are not ignored during duplicate checking
The SQL UNION query allows you to combine the result sets of two or more SQL SELECT statements. It removes duplicate rows between the various SELECT statements. Each SQL SELECT statement within the UNION query must have the same number of fields in the result sets with similar data types.
Q110. - (Topic 1)
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
Using the ORDER BY Clause in Set Operations
The ORDER BY clause can appear only once at the end of the compound query.
Component queries cannot have individual ORDER BY clauses.
The ORDER BY clause recognizes only the columns of the first SELECT query.
By default, the first column of the first SELECT query is used to sort the output in an