1Z0-051
Number: 1Z0-051
Passing Score: 600
Time Limit: 120 min
Oracle 1Z0-051
Questions: 180
http://51pass.taobao.com
Exam A
QUESTION 1
View the Exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS, and TIMES 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 CREATE TABLE command:
CREATE TABLE new_sales(prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
A. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the
column definition.
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 column names in the CREATE TABLE
command and the SELECT clause do not match.
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
Section: (none)
Explanation/Reference:
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.
QUESTION 2
View the Exhibit to examine the description for the SALES table.
Which views can have all DML operations performed on it? (Choose all that apply.)
A. CREATE VIEW v3
AS SELECT * FROM SALES
WHERE cust_id = 2034
WITH CHECK OPTION;
B. CREATE VIEW v1
AS SELECT * FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
C. CREATE VIEW v2
AS SELECT prod_id, cust_id, time_id FROM SALES
WHERE time_id <= SYSDATE - 2*365
WITH CHECK OPTION;
D. CREATE VIEW v4
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES
WHERE time_id <= SYSDATE - 2*365
GROUP BY prod_id, cust_id
WITH CHECK OPTION;
Answer: AB
Section: (none)
Explanation/Reference:
Creating a View
You can create a view by embedding a subquery in the CREATE VIEW statement.
In the syntax:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
OR REPLACE Re-creates the view if it already exists
FORCE Creates the view regardless of whether or not the base tables exist
NOFORCE Creates the view only if the base tables exist (This is the default.)
view Is the name of the view
alias Specifies names for the expressions selected by the view’s query (The number of aliases must match
the number of expressions selected by the view.)
subquery Is a complete SELECT statement (You can use aliases for the columns in the SELECT list.)
WITH CHECK OPTION Specifies that only those rows that are accessible to the view can be inserted or
updated ANSWER D
constraint Is the name assigned to the CHECK OPTION constraint
WITH READ ONLY Ensures that no DML operations can be performed on this view
Rules for Performing DML Operations on a View
You cannot add data through a view if the view includes:
Group functions
A GROUP BY clause
The DISTINCT keyword
The pseudocolumn ROWNUM keyword
Columns defined by expressions
NOT NULL columns in the base tables that are not selected by the view – ANSWER C
QUESTION 3
You need to extract details of those products in the SALES table where the PROD_ID column contains the
string '_D123'.
Which WHERE clause could be used in the SELECT statement to get the required output?
A. WHERE prod_id LIKE '%_D123%' ESCAPE '_'
B. WHERE prod_id LIKE '%\_D123%' ESCAPE '\'
C. WHERE prod_id LIKE '%_D123%' ESCAPE '%_'
D. WHERE prod_id LIKE '%\_D123%' ESCAPE '\_'
Answer: B
Section: (none)
Explanation/Reference:
A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol)
using the ESCAPE identifier in conjunction with an ESCAPE character. The second example in Figure 3-12
shows the SQL statement that retrieves the JOBS table records with JOB_ID values equal to SA_MAN and
SA_REP and which conforms to the original requirement:
select job_id from jobs
where job_id like 'SA\_%' escape '\';
QUESTION 4
Which two statements are true regarding single row functions? (Choose two.)
A. They accept only a single argument.
B. They can be nested only to two levels.
