1Z0-051 Exam - Oracle Database: SQL Fundamentals I

certleader.com

Q1. - (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:

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 

Q2. - (Topic 1) 

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this? 

A. GRANT select ON dept TO ALL_USERS; 

B. GRANT select ON dept TO ALL; 

C. GRANT QUERY ON dept TO ALL_USERS 

D. GRANT select ON dept TO PUBLIC; 

Answer:

Explanation: view the columns associated with the constraint names in the USER_CONS_COLUMNS view. 

Incorrect Answer: Atable to view all constraints definition and names Bshow all object name belong to user Cdoes not display column associated Eno such view 

Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-25 

Q3. - (Topic 1) 

Evaluate the following SQL query; 

What would be the outcome? 

A. 200 

B. 16 

C. 160 

D. 150 

E. 100 

Answer:

Explanation: 

Function Purpose ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero 

Q4. - (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; 

Answer:

Explanation: 

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) 

Q5. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS table. Exhibit: 

you issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name 'king' and whose credit limit is less than the maximum credit limit in countries that have customers with the last name 'king'. 

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

A. It produces an error and the < operator should be replaced by < ANY to get the required output 

B. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output 

C. It executes and shows the required result 

D. It produces an error and the < operator should be replaced by < ALL to get the required output 

Answer:

Q6. - (Topic 1) 

Which three are true? (Choose three.) 

A. A MERGE statement is used to merge the data of one table with data from another. 

B. A MERGE statement replaces the data of one table with that of another. 

C. A MERGE statement can be used to insert new rows into a table. 

D. A MERGE statement can be used to update existing rows in a table. 

Answer: A,C,D 

Explanation: The MERGE Statement allows you to conditionally insert or update data in a table. If the rows are present in the target table which match the join condition, they are updated if the rows are not present they are inserted into the target table 

Q7. - (Topic 2) 

What are two reasons to create synonyms? (Choose two.) 

A. You have too many tables. 

B. Your tables names are too long. 

C. Your tables have difficult names. 

D. You want to work on your own tables. 

E. You want to use another schema's tables. 

F. You have too many columns in your tables. 

Answer: B,C 

Explanation: 

Create a synonyms when the names of the tables are too long or the table names are difficult. 

Q8. - (Topic 1) 

Examine these statements: 

CREATE ROLE registrar; 

GRANT UPDATE ON student_grades TO registrar; 

GRANT registrar to user1, user2, user3; 

What does this set of SQL statements do? 

A. The set of statements contains an error and does not work. 

B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users. 

C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users. 

D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role. 

E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object. 

F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar. 

Answer:

Explanation: the statement will create a role call REGISTRAR, grant UPDATE on student_grades to registrar, grant the role to user1,user2 and user3. 

Incorrect Answer: Athe statement does not contain error Bthere is no MODIFY privilege Dstatement does not create 3 users with the role Eprivilege is grant to role then grant to user Fprivilege is grant to role then grant to user 

Q9. - (Topic 2) 

The CUSTOMERS table has these columns: 

The CUSTOMER_ID column is the primary key for the table. 

You need to determine how dispersed your customer base is. 

Which expression finds the number of different countries represented in the CUSTOMERS table? 

A. COUNT(UPPER(country_address)) 

B. COUNT(DIFF(UPPER(country_address))) 

C. COUNT(UNIQUE(UPPER(country_address))) 

D. COUNT DISTINCT UPPER(country_address) 

E. COUNT(DISTINCT (UPPER(country_address))) 

Answer:

Q10. - (Topic 2) 

Examine the structure of the PROMOS table: 

You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day. 

Which query gives the correct output? 

A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') 

FROM promos; 

B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same 

Day') FROM promos; 

C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), 

NULL,'Same Day') 

FROM promos; 

D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), 

NULL,'Same day') FROM promos; 

Answer:

Explanation: 

The NULLIF Function The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested. The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal, comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned ANSWER A - date and String incompatibl;a datatypes for NVL function The Date TRUNC Function The date TRUNC function performs a truncation operation on a date value based on a specified date precision format. The date TRUNC function takes one mandatory and one optional parameter. Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value that can be implicitly converted into a date item. The date precision format parameter specifies the degree of truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time component