1Z0-051 | Pinpoint 1Z0-051 Exam Questions and Answers 2021
Want to know 1z0 051 dumps pdf features? Want to lear more about 1z0 051 practice test experience? Study 1z0 051 dumps pdf. Gat a success with an absolute guarantee to pass Oracle 1Z0-051 (Oracle Database: SQL Fundamentals I) test on your first attempt.
Free demo questions for Oracle 1Z0-051 Exam Dumps Below:
NEW QUESTION 1
Which constraint can be defined only at the column level?
- A. UNIQUE
- B. NOT NULL
- C. CHECK
- D. PRIMARY KEY
- E. FOREIGN KEY
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
NEW QUESTION 2
You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table.
Which set of statements accomplishes this?
- A. CREATE ROLE registrar; GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3
- B. CREATE NEW ROLE registrar; GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3
- C. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3
- D. CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;
- E. CREATE registrar; GRANT CHANGE ON student_grades TO registrar; GRANT registrar;
this is the correct solution for the answer. GRANT role_name to users;
Incorrect Answer: Athere is no such MODIFY keyword Binvalid CREATE command, there is no such NEW keyword Cinvalid GRANT command, there is no such ROLE keyword Einvalid GRANT command, there is no such CHANGE keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 13-10
NEW QUESTION 3
Which four are types of functions available in SQL? (Choose 4)
- A. string
- B. character
- C. integer
- D. calendar
- E. numeric
- F. translation
- G. date
- H. conversion
Explanation: Explanation: SQL have character, numeric, date, conversion function.
ASQL have character, numeric, date, conversion function.
CSQL have character, numeric, date, conversion function.
DSQL have character, numeric, date, conversion function.
FSQL have character, numeric, date, conversion function.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 3-3
NEW QUESTION 4
Which two statements are true regarding working with dates? (Choose two.)
- A. The default internal storage of dates is in the numeric format
- B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required
- C. The default internal storage of dates is in the character format
- D. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century
Working with Dates The Oracle Database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-RR. RR Date Format The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element.
untitled Note the values shown in the last two rows of the above table. As we approach the middle of the century, then the RR behavior is probably not what you want. This data is stored internally as follows: CENTURY YEAR MONTH DAY HOUR MINUTE SECOND 19 87 06 17 17 10 43
NEW QUESTION 5
View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
WHEN promo_cost >=(SELECT AVG(promo_cost)
Which statement is true regarding the outcome of the above query?
- A. It shows COST_REMARK for all the promos in the tabl
- B. It produces an error because the subquery gives an erro
- C. It shows COST_REMARK for all the promos in the promo category 'TV'.
- D. It produces an error because subqueries cannot be used with the CASE expressio
NEW QUESTION 6
View the Exhibit and examine the structure of the CUSTOMERS table.
You have been asked to produce a report on the CUSTOMERS table showing the customers details sorted in descending order of the city and in the descending order of their income level in each city. Which query would accomplish this task?
- A. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city desc, cust_income_level DESC;
- B. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_income_level desc, cust_city DESC;
- C. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY (cust_city, cust_income_level) DESC;
- D. SELECT cust_city, cust_income_level, cust_last_name FROM customers ORDER BY cust_city, cust_income_level DESC;
NEW QUESTION 7
View the Exhibit and examine the structure of the PROMOTIONS table.
You need to generate a report of all promos from the PROMOTIONS table based on the following conditions:
The promo name should not begin with 'T' or 'N'.
The promo should cost more than $20000.
The promo should have ended after 1st January 2001.
Which WHERE clause would give the required result?
- A. WHERE promo_name NOT LIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
- B. WHERE (promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000 OR promo_end_date > '1-JAN-01'
- C. WHERE promo_name NOT LIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000 AND promo_end_date > '1-JAN-01'
- D. WHERE (promo_name NOT LIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost > 20000 AND promo_end_date > '1-JAN-01')
NEW QUESTION 8
A SELECT statement can be used to perform these three functions:
Choose rows from a table.
Choose columns from a table
Bring together data that is stored in different tables by creating a link between
Which set of keywords describes these capabilities?
- A. difference, projection, join
- B. selection, projection, join
- C. selection, intersection, join
- D. intersection, projection, join
- E. difference, projection, product
Explanation: Explanation: choose rows from a table is SELECTION,
Choose column from a table is PROJECTION
Bring together data in different table by creating a link between them is JOIN.
Aanswer should have SELECTION, PROJECTION and JOIN.
Canswer should have SELECTION, PROJECTION and JOIN.
Danswer should have SELECTION, PROJECTION and JOIN.
Eanswer should have SELECTION, PROJECTION and JOIN.
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 1-6
NEW QUESTION 9
Which two statements are true about sequences created in a single instance database? (Choose two.)
- A. CURRVAL is used to refer to the last sequence number that has been generated
- B. DELETE <sequencename> would remove a sequence from the database
- C. The numbers generated by a sequence can be used only for one table
- D. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement
- E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted
Gaps in the Sequence
Although sequence generators issue sequential numbers without gaps, this action occurs
independent of a commit or rollback. Therefore, if you roll back a statement containing a
sequence, the number is lost.
Another event that can cause gaps in the sequence is a system crash. If the sequence
caches values in memory, those values are lost if the system crashes.
Because sequences are not tied directly to tables, the same sequence can be used for
However, if you do so, each table can contain gaps in the sequential numbers.
Modifying a Sequence
If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement To remove a sequence, use the DROP statement:
DROP SEQUENCE dept_deptid_seq;
NEW QUESTION 10
View the Exhibit and examine the structure of the PROMOTIONS table. Using the PROMOTIONS table, you need to display the names of all promos done after January 1, 2001, starting with the latest promo. Which query would give the required result? (Choose all that apply.)
- A. SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begiii_date > '01-JAN-01' ORDER BY 2 DESC;
- B. SELECT promo_nam
- C. promo_begiii_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY promo_name DESC:
- D. SELECT promo_nam
- E. promo_begin_date FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY 1DESC:
- F. SELECT promo_name, promo_begin_date "START DATE" FROM promotions WHERE promo_begin_date > '01-JAN-01' ORDER BY "START DATE" DESC;
NEW QUESTION 11
In which two cases would you use an outer join? (Choose two.)
- A. The tables being joined have NOT NULL column
- B. The tables being joined have only matched dat
- C. The columns being joined have NULL value
- D. The tables being joined have only unmatched dat
- E. The tables being joined have both matched and unmatched dat
- F. Only when the tables have a primary key/foreign key relationshi
You use an outer join to also see rows that do not meet the join condition.
Incorrect Answer: Ameet a join condition Bmeet a join condition Dmeet non join condition only Fdoes not take into consideration of primary key and foreign key relationship
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 4-17
NEW QUESTION 12
When does a transaction complete? (Choose all that apply.)
- A. When a PL/SQL anonymous block is executed
- B. When a DELETE statement is executed
- C. When a data definition language statement is executed
- D. When a TRUNCATE statement is executed after the pending transaction
- E. When a ROLLBACK command is executed
NEW QUESTION 13
Which two are true about aggregate functions? (Choose two.)
- A. You can use aggregate functions in any clause of a SELECT statemen
- B. You can use aggregate functions only in the column list of the select clause and in the WHERE clause of a SELECT statemen
- C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row column
- D. You can pass column names, expressions, constants, or functions as parameter to an aggregate functio
- E. You can use aggregate functions on a table, only by grouping the whole table as one single grou
- F. You cannot group the rows of a table by more than one column while using aggregate function
NEW QUESTION 14
Examine the following SQL commands:
Which statement is true regarding the execution of the above SQL commands?
- A. Both commands execute successfull
- B. The first CREATE TABLE command generates an error because the NULL constraint is not vali
- C. The second CREATE TABLE command generates an error because the CHECK constraint is not vali
- D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY constraints cannot be used for the same colum
- E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used in the PRIMARY KEY and FOREIGN KEY constraint
Defining Constraints The slide gives the syntax for defining constraints when creating a table. You can create
constraints at either the column level or table level. Constraints defined at the column level
are included when the column is defined. Table-level constraints are defined at the end of
the table definition and must refer to the column or columns on which the constraint
pertains in a set of parentheses. It is mainly the syntax that differentiates the two;
otherwise, functionally, a columnlevel constraint is the same as a table-level constraint.
NOT NULL constraints must be defined at the column level.
Constraints that apply to more than one column must be defined at the table level.
NEW QUESTION 15
What is true about sequences?
- A. The start value of the sequence is always 1.
- B. A sequence always increments by 1.
- C. The minimum value of an ascending sequence defaults to 1.
- D. The maximum value of descending sequence defaults to 1.
NEW QUESTION 16
See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:
You issue the following query:
Which statement is true regarding the outcome of this query?
- A. It produces an error because the NATURAL join can be used only with two tables
- B. It produces an error because a column used in the NATURAL join cannot have a qualifier
- C. It produces an error because all columns used in the NATURAL join should have a qualifier
- D. It executes successfully
Creating Joins with the USING Clause
Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin.
The Natural JOIN USING Clause
The format of the syntax for the natural JOIN USING clause is as follows: SELECT table1.column, table2.column FROM table1 JOIN table2 USING (join_column1, join_column2…); While the pure natural join contains the NATURAL keyword in its syntax, the JOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.
NEW QUESTION 17
Which statements are true regarding single row functions? (Choose all that apply.)
- A. MOD : returns the quotient of a division
- B. TRUNC : can be used with NUMBER and DATE values
- C. CONCAT : can be used to combine any number of values
- D. SYSDATE : returns the database server current date and time
- E. INSTR : can be used to find only the first occurrence of a character in a string
- F. TRIM : can be used to remove all the occurrences of a character from a string
ROUND: Rounds value to a specified decimal TRUNC: Truncates value to a specified decimal MOD: Returns remainder of division SYSDATE is a date function that returns the current database server date and time.
Date functions operate on Oracle dates. All date functions return a value of the DATE data type except MONTHS_BETWEEN, which returns a numeric value. MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month. ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative. NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string. LAST_DAY(date): Finds the date of the last day of the month that contains date The above list is a subset of the available date functions. ROUND and TRUNC number functions can also be used to manipulate the date values as shown below: ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day. TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.
The CONCAT Function
The CONCAT function joins two character literals, columns, or expressions to yield one larger character expression. Numeric and date literals are implicitly cast as characters when they occur as parameters to the CONCAT function. Numeric or date expressions are evaluated before being converted to strings ready to be concatenated. The CONCAT function takes two parameters. Its syntax is CONCAT(s1, s2), where s1 and s2 represent string literals, character column values, or expressions resulting in character values. The INSTR(source string, search item, [start position],[nth occurrence of search item]) function returns a number that represents the position in the source string, beginning from the given start position, where the nth occurrence of the search item begins: instr('http://www.domain.com','.',1,2) = 18 The TRIM function literally trims off leading or trailing (or both) character strings from a given source string:
Recommend!! Get the Full 1Z0-051 dumps in VCE and PDF From Certleader, Welcome to Download: https://www.certleader.com/1Z0-051-dumps.html (New 292 Q&As Version)