Transcription
1Z0-051Oracle Database 11g - SQL Fundamentals IExam Summary – Syllabus – Questions
1Z0-051 Sample Questions and Exam SummaryTable of ContentsIntroduction to 1Z0-051 Exam on Oracle Database 11g - SQL Fundamentals I 2Oracle 1Z0-051 Certification Details: . 2Oracle 1Z0-051 Exam Syllabus: . 31Z0-051 Sample Questions: . 4Answers to 1Z0-051 Exam Questions: . 81Z0-051-Oracle Database 11g - SQL Fundamentals Ipg. 1
1Z0-051 Sample Questions and Exam SummaryIntroduction to 1Z0-051 Exam on OracleDatabase 11g - SQL Fundamentals IYou can use this document to collect all the information about Oracle Database 11g - SQLFundamentals I (1Z0-051) certification. The Oracle 1Z0-051 certification is mainlytargeted to those candidates who are from Database background and want to flourishtheir career with Oracle Database 11g Administrator Certified Associate (OCA) credential.The Oracle Database 11g - SQL Fundamentals I certification exam validates yourunderstanding of the Oracle Database technology and sets the stage for your futureprogression.Oracle 1Z0-051 Certification Details:Exam NameOracle Database 11g: SQL Fundamentals IExam Code1Z0-051Exam Product VersionOracle Database 11gExam PriceUSD 125 (Pricing may vary by country or by localizedcurrency)Duration120 MinsNumber of Questions64Passing Score60%Validated AgainstThis exam has been validated against Oracle Database 11gRelease 2 version 11.2.0.1.0.FormatMultiple ChoiceRecommended Training Oracle Database: Introduction to SQLSchedule ExamPearson VUE - OracleRecommended Practice 1Z0-051 Online Practice Exam1Z0-051-Oracle Database 11g - SQL Fundamentals Ipg. 2
1Z0-051 Sample Questions and Exam SummaryOracle 1Z0-051 Exam Syllabus:Retrieving Data usingthe SQL SELECTStatement- Explain the capabilities of SQL SELECT statements- Execute a basic SELECT statement- Limit the rows that are retrieved by a queryRestricting and Sorting - Sort the rows that are retrieved by a queryData- Use ampersand substitution to restrict and sort output atruntimeUsing Single-Row- Describe various types of functions available in SQLFunctions to Customize - Use character, number, and date functions in SELECTOutputstatements- Describe various types of conversion functions that areUsing Conversionavailable in SQLFunctions and- Use the TO CHAR, TO NUMBER, and TO DATE conversionConditional Expressions functions- Apply conditional expressions in a SELECT statementReporting AggregatedData Using the GroupFunctions-Identify the available group functionsDescribe the use of group functionsGroup data by using the GROUP BY clauseInclude or exclude grouped rows by using the HAVING clauseDisplaying Data FromMultiple Tables- Write SELECT statements to access data from more than onetable using equijoins and nonequijoins- Join a table to itself by using a self-join- View data that generally does not meet a join condition byusing OUTER joins- Generate a Cartesian product of all rows from two or moretablesUsing Subqueries toSolve Queries-Using the SETOperators- Describe set operators- Use a set operator to combine multiple queries into a singlequery- Control the order of rows returnedDefine subqueriesDescribe the types of problems that the subqueries can solveList the types of subqueriesWrite single-row and multiple-row subqueries1Z0-051-Oracle Database 11g - SQL Fundamentals Ipg. 3
1Z0-051 Sample Questions and Exam Summary- Describe each data manipulation language (DML) statement- Insert rows into a tableManipulating Data- Update rows in a table- Delete rows from a table- Control transactionsUsing DDL Statementsto Create and ManageTables- Categorize the main database objects- Review the table structure- List the data types that are available for columns- Create a simple table- Explain how constraints are created at the time of tablecreation- Describe how schema objects workCreating Other SchemaObjects-Create simple and complex viewsRetrieve data from viewsCreate, maintain, and use sequencesCreate and maintain indexesCreate private and public synonyms1Z0-051 Sample Questions:01) Examine the structures of the PATIENT, PHYSICIAN, and ADMISSIONtables.Which SQL statement will produce a list of all patients who have more thanone physician?1Z0-051-Oracle Database 11g - SQL Fundamentals Ipg. 4
1Z0-051 Sample Questions and Exam Summarya) SELECT p.patient idFROM patient pWHERE p.patient id IN (SELECT patient idFROM admissionGROUP BY patient idHAVING COUNT(*) 1);b) SELECT DISTINCT a.patient idFROM admission a, admission a2WHERE a.patient id a2.patient idAND a.physician id a2.physician id;c) SELECT patient idFROM admissionWHERE COUNT(physician id) 1;d) SELECT patient idFROM patient FULL OUTER JOIN physician;02) The transaction table contains these columns:SQL DESCRIBE transactionTRANSACTION ID NUMBER(9)TRANS CODE VARCHAR2(5)CUST ACCOUNT VARCHAR2(12)A new standard was adopted in your department affecting reports produced byquerying the transaction table. When creating reports, a dash (-) followed bythe three characters 'ANI' must be appended to all transaction codes thatcontain only three characters. Any leading 'W' in a transaction code must beremoved from the resulting data display.Which query will return the desired results?a) SELECT TRIM('W' (RPAD(trans code, 7, '-ANI')))FROM transaction WHERELENGTH(trans code) 3;b) SELECT TRIM('W' FROM (RPAD(trans code, 3, '-ANI')))FROM transaction WHERELENGTH(trans code) 3;c) SELECT TRIM(LEADING 'W' FROM (RPAD(trans code, 7, '-ANI')))FROM transactionWHERE LENGTH(trans code) 3;d) SELECT TRIM(LEADING 'W' FROM (RPAD(trans code, 3, '-ANI')))FROM transactionWHERE LENGTH(trans code) 3;03) The current date is January 1, 2009. You need to store this date value:19-OCT-99Which statement about the date format for this value is TRUE?a) Both the YY and RR date formats will interpret the year as 1999.b) Both the YY and RR date formats will interpret the year as 2099.c) The RR date format will interpret the year as 2099, and the YY date format willinterpret the year as 1999.d) The RR date format will interpret the year as 1999, and the YY date format willinterpret the year as 2099.04) Which two statements would cause an implicit COMMIT to occur? (Choosetwo.)a) GRANTb) SELECT1Z0-051-Oracle Database 11g - SQL Fundamentals Ipg. 5
1Z0-051 Sample Questions and Exam Summaryc) RENAMEd) COMMITe) UPDATEf) ROLLBACK05) The EMPLOYEE table contains these columns:EMP ID NUMBER(9)FNAME VARCHAR2(25)LNAME VARCHAR(30)SALARY NUMBER(7,2)BONUS NUMBER(5,2)DEPT ID NUMBER(9)You need to calculate the average bonus for all the employees in eachdepartment. The average should be calculated based on all the rows in thetable, even if some employees do not receive a bonus.Which group function should you use to calculate this value?a) AVGb) SUMc) MAXd) MEANe) COUNTf) AVERAGE06) Which statement concerning SQL functions is TRUE?a) All date functions return DATE datatype values.b) Character functions can return character or number values.c) Single-row functions can only be used in SELECT and WHERE clauses.d) Conversion functions convert a column definition from one datatype to anotherdatatype.07) Examine the data from the DONATION table.This statement fails when executed:SELECT amount pledged, amount paidFROM donationWHERE donor id (SELECT donor idFROM donationWHERE amount pledged 1000.00OR pledge dt '05-JAN-2002');1Z0-051-Oracle Database 11g - SQL Fundamentals Ipg. 6
1Z0-051 Sample Questions and Exam SummaryWhich two changes could correct the problem? (Choose two. Each correctanswer is a separate solution.)a) Remove the subquery WHERE clause.b) Change the outer query WHERE clause to WHERE donor id IN.c) Change the outer query WHERE clause to WHERE donor id LIKE.d) Include the DONOR ID column in the select list of the outer query.e) Remove the single quotes around the date value in the inner query WHERE clause.f) Change the subquery WHERE clause to WHERE amount pledged 1000.00 ANDpledge dt '05-JAN-2002'.08) Which statements concerning the creation of a view are TRUE? (Choose allthat apply.)a) View columns that are the result of derived values must be given a column alias.b) A constraint name must be provided when using the WITH CHECK OPTION clause orthe statement will fail.c) A view may have column names that are different from the actual base table(s)column names by using column aliases.d) When the view already exists, using the OR REPLACE option requires regranting theobject privileges previously granted on the view.09) You want to query employee information and display the results sorted bythe employee's department, then by their salaries from highest to lowest.When multiple employees within the same department share a last name, theymust be displayed in alphabetical order by first name.Which ORDER BY clause should you use in your query?a) ORDER BY department id, salary, last name, first nameb) ORDER BY department id, salary ASC, last name, first namec) ORDER BY department id, salary DESC, last name, first named) ORDER BY department id, salary DESC, first name ' ' last name ASC10) Examine the data in the LINE ITEM table.You query the database and return the value 23.Which SELECT statement did you use?a) SELECT SUBSTR(product id, 3)FROM line itemWHERE line item id 2AND order id 1494;b) SELECT SUBSTR(product id, 3, -2)1Z0-051-Oracle Database 11g - SQL Fundamentals Ipg. 7
1Z0-051 Sample Questions and Exam SummaryFROM line itemWHERE line item id 2AND order id 1494;c) SELECT SUBSTR(product id, -3, 2)FROM line itemWHERE line item id 2AND order id 1494;d) SELECT SUBSTR(product id, 3, 2)FROM line itemWHERE line item id 2AND order id 1494;Answers to 1Z0-051 Exam Questions:QUESTION: 01 QUESTION: 02 QUESTION: 03 QUESTION: 04 QUESTION: 05Answer: bAnswer: cAnswer: dAnswer: a, cAnswer: aQUESTION: 06 QUESTION: 07 QUESTION: 08 QUESTION: 09 QUESTION: 10Answer: bAnswer: b, fAnswer: a, cAnswer: cAnswer: dNote: If you find any typo or data entry error in these sample questions, we requestyou to update us by commenting on this page or write an email onfeedback@oraclestudy.com1Z0-051-Oracle Database 11g - SQL Fundamentals Ipg. 8
Oracle Database 11g - SQL Fundamentals I Keywords: 1Z0-051 PDF, 1Z0-051 VCE, 1Z0-051 Dumps 1Z0-051 Questions, Oracle OCA Dumps, Oracle Database 11g Ce
