1Z0-051 | A Review Of Verified 1Z0-051 exam
Q31. - (Topic 2)
Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME
Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer
You need to display customers' second names where the second name starts with "Mc" or "MC."
Which query gives the required output?
A. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc'
B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%'
C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');
D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1)
FROM customers
WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');
Answer: B
Q32. - (Topic 1)
View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement:
Which statement is true regarding the outcome of the above query?
A. It executes successfully.
B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
Answer: A
Q33. - (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
Answer: A
Explanation:
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
ascending order.
Q34. - (Topic 1)
Where can sub queries be used? (Choose all that apply)
A. field names in the SELECT statement
B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
Answer: A,B,C,F
Explanation:
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING
clauses of a query.
A subquery can have any of the usual clauses for selection and projection. The following
are required clauses:
A SELECT list
A FROM clause
The following are optional clauses: WHERE GROUP BY HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent.
Q35. - (Topic 2)
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which statement accomplishes this task?
A. ALTER TABLE students ADD PRIMARY KEY student_id;
B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id); E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Answer: D
Explanation:
ALTER TABLE table_name
ADD [CONSTRAINT constraint] type (coloumn);
Incorrect Answer:
Awrong syntax
Bwrong syntax
Cwrong syntax
Eno such MODIFY keyword
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-17
Q36. - (Topic 1)
Exhibit contains the structure of PRODUCTS table:
Evaluate the following query:
What would be the outcome of executing the above SQL statement?
A. It produces an error
B. It shows the names of products whose list price is the second highest in the table.
C. It shown the names of all products whose list price is less than the maximum list price
D. It shows the names of all products in the table
Answer: B
Q37. - (Topic 2)
What does the FORCE option for creating a view do?
A. creates a view with constraints
B. creates a view even if the underlying parent table has constraints
C. creates a view in another schema even if you don't have privileges
D. creates a view regardless of whether or not the base tables exist
Answer: D
Explanation:
create a view regardless of whether or not the base tables exist.
Incorrect Answer: Athe option is not valid Bthe option is not valid Cthe option is not valid
Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 11-3
Q38. - (Topic 2)
Which four are valid Oracle constraint types? (Choose four.)
A. CASCADE
B. UNIQUE
C. NONUNIQUE
D. CHECK
E. PRIMARY KEY
F. CONSTANT
G. NOT NULL
Answer: B,D,E,G
Explanation:
Oracle constraint type is Not Null, Check, Primary Key, Foreign Key and Unique Incorrect Answer: AIs not Oracle constraint CIs not Oracle constraint FIs not Oracle constraint Refer: Introduction to Oracle9i: SQL, Oracle University Study Guide, 10-3
Q39. - (Topic 2)
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore');
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the subquery.
D. It ignores the NULL value and generates output for the other values produced by the subquery.
Answer: C
Q40. - (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.
Answer: A,B,E