Oracle Database 1Z0-051 Practice Test Questions

Share more Oracle Database 1Z0-051 Practice Test Questions

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

Which two statements are true regarding single row functions? (Choose two.)
A. They a ccept only a single argument.
B. They c an be nested only to two levels.
C. Arguments can only be column values or constants.
D. They a lways return a single result row for every row of a queried table.
E. They c an return a data type value different from the one that is referenced.
Answer: DE

Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three .)
A. SELECT TO_CHAR(1890.55,’$0G000D00′)
FROM DUAL;
B. SELECT TO_CHAR(1890.55,’$9,999V99′)
FROM DUAL;
C. SELECT TO_CHAR(1890.55,’$99,999D99′)
FROM DUAL;
D. SELECT TO_CHAR(1890.55,’$99G999D00′)
FROM DUAL;
E. SELECT TO_CHAR(1890.55,’$99G999D99′)
FROM DUAL;
Answer: ADE

Which two statements are true regarding the USING and ON clauses in table joins? (Choose two.)
A. Both USING and ON clauses can be used for equijoins and nonequijoins.
B. A maximum of one pair of columns can be joined between two tables using the ON clause.
C. The ON clause can be used to join tables on columns that have different names but compatible data types.
D. The WHERE clause can be used to apply additional conditions in SELECT statements containing the ON or the USING clause.
Answer: CD

Which two statements are true regarding constraints? (Choose two.)
A. A table can have only one primary key and one foreign key.
B. A table can have only one primary key but multiple foreign keys.
C. Only the primary key can be defined at the column and table levels.
D. The foreign key and parent table primary key must have the same name.
E. Both primary key and foreign key constraints can be defined at both column and table levels.
Answer: BE

View the Exhibit and examine the structure of the CUSTOMERS table.
Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two.)
A. listing of customers who do not have a credit limit and were born before 1980
B. finding the number of customers, in each city, whose marital status is ‘married’
C. finding the average credit limit of male customers residing in ‘Tokyo’ or ‘Sydney’
D. listing of those customers whose credit limit is the same as the credit limit of customers residing in the city ‘Tokyo’
E. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers
Answer: DE

Which three statements are true regarding the data types in Oracle Database 10g/11g? (Choose three.)
A. Only one LONG column can be used per table.
B. A TIMESTAMP data type column stores only time values with fractional seconds.
C. The BLOB data type column is used to store binary data in an operating system file.
D. The minimum column width that can be specified for a VARCHAR2 data type column is one.
E. The value for a CHAR data type column is blank-padded to the maximum defined column width.
Answer: ADE

You need to create a table for a banking application. One of the columns in the table has the following requirements:
1) You want a column in the table to store the duration of the credit period.
2) The data in the column should be stored in a format such that it can be easily added and subtracted with DATE data type without using conversion functions.
3) The maximum period of the credit provision in the application is 30 days.
4) The interest has to be calculated for the number of days an individual has taken a credit for.
Which data type would you use for such a column in the table?
A. DATE
B. NUMBER
C. TIMESTAMP
D. INTERVAL DAY TO SECOND
E. INTERVAL YEAR TO MONTH
Answer: D

ou need to create a table with the following column specifications:
1. Employee ID (numeric data type) for each employee
2. Employee Name (character data type) that stores the employee name
3. Hire date, which stores the date of joining the organization for each employee
4. Status (character data type), that contains the value ‘ACTIVE’ if no data is entered
5. Resume (character large object [CLOB] data type), which contains the resume submitted by the employee
Which is the correct syntax to create this table?
A. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
e_status VARCHAR2(10) DEFAULT ‘ACTIVE’,
resume CLOB(200));
B. CREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT ‘ACTIVE’,
resume CLOB);
C. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT “ACTIVE”,
resume CLOB);
D. CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10) DEFAULT ‘ACTIVE’,
resume CLOB);
Answer: D

Which is the valid CREATE TABLE statement?
A. CREATE TABLE emp9$# (emp_no NUMBER (4));
B. CREATE TABLE 9emp$# (emp_no NUMBER(4));
C. CREATE TABLE emp*123 (emp_no NUMBER(4));
D. CREATE TABLE emp9$# (emp_no NUMBER(4), date DATE);
Answer: A

Which two statements are true regarding tables? (Choose two.)
A. A table name can be of any length.
B. A table can have any number of columns.
C. A column that has a DEFAULT value cannot store null values.
D. A table and a view can have the same name in the same schema.
E. A table and a synonym can have the same name in the same schema.
F. The same table name can be used in different schemas in the same database.
Answer: EF

Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL values.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All constraints can be defined at the column level as well as the table level.
Answer: BD

Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. The column with a UNIQUE constraint can store NULLS.
C. A constraint is enforced only for an INSERT operation on a table.
D. You can have more than one column in a table as part of a primary key.
Answer: BD

Evaluate the following CREATE TABLE commands:
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The above command fails when executed. What could be the reason?
A. SYSDATE cannot be used with the CHECK constraint.
B. The BETWEEN clause cannot be used for the CHECK constraint.
C. The CHECK constraint cannot be placed on columns having the DATE data type.
D. ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.
Answer: A

Want to know more about 1Z0-052 exam, please read articleOracle 1Z0-051 Practice Test

Leave a Reply