Question 1.
When could an index decrease the speed of a query?
A. The table is small.
B. The column is used in a WHERE clause.
C. The column contains a wide range of values.
D. The column contains a large number of
...
Question 1.
When could an index decrease the speed of a query?
A. The table is small.
B. The column is used in a WHERE clause.
C. The column contains a wide range of values.
D. The column contains a large number of null values.
Ans: A
Question 2.
What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?
A. ALTER TABLE table_name
DROP PRIMARY KEY CASCADE;
B. ALTER TABLE table_name
REMOVE CONSTRAINT PRIMARY KEY CASCADE;
C. ALTER TABLE table_name
DISABLE CONSTRAINT PRIMARY KEY CASCADE;
D. A PRIMARY KEY constraint CANNOT be removed if it has dependent constraints.
Ans: A
Question 3.
You need to add a NOT NULL constraint to the QUANTITY column in the PO_DETAIL table.
Which statement should you use to complete this task?
A. ALTER TABLE po_detail
MODIFY (quantity NOT NULL);
B. ALTER TABLE po_detail
MODIFY quantity CONSTRAINT NOT NULL;
C. ALTER TABLE po_detail
ADD CONSTRAINT quantity_nn NOT NULL(quantity);
D. ALTER TABLE po_detail
ADD CONSTRAINT NOT NULL (quantity);
Ans: A
Question 4.
Which clause could you use to limit the price values to 100.00 or less?
A. CONSTRAINT inventory_price_ck CHECK (price < 100.00)
B. CONSTRAINT CHECK inventory_price_ck (price < 100.00)
C. CONSTRAINT inventory_price_ck CHECK (price <= 100.00)
D. CONSTRAINT CHECK inventory_price_ck (price <= 100.00)
E. CONSTRAINT inventory_price_ck CHECK (price IN (100.00))
Ans: CExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 2 of 2
Question 5.
Evaluate this CREATE TABLE statement:
1. CREATE TABLE order*1 (
2. order# NUMBER(9),
3. cust_id NUMBER(9),
4. date_1 DATE DEFAULT SYSDATE);
Which line of this statement will cause an error?
A. 1
B. 2
C. 3
D. 4
Ans: A
Question 6.
Which privilege do you need for database access?
A. ALTER USER
B. CREATE USER
C. ALTER SYSTEM
D. ALTER SESSION
E. CREATE SESSION
Ans: E
Question 7.
Which system privilege may be granted to a role?
A. ALTER
B. EXECUTE
C. REFERENCES
D. BACKUP ANY TABLE
Ans: DExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 3 of 3
Question 8.
You need to create user Marcia and allow this user to create and drop tables in any schema. She
should be able to create procedures and sequences only in her schema.
Which script should you use to achieve these results?
A. CREATE USER marcia
IDENTIFIED BY sue123;
GRANT DROP ANY TABLE, CREATE SEQUENCE, CREATE PROCEDURE
TO marcia
/
B. CREATE USER marcia
IDENTIFIED BY sue123;
GRANT CREATE TABLE, DROP TABLE, CREATE SEQUENCE, CREATE
PROCEDURE
TO marcia
/
C. CREATE USER marcia
IDENTIFIED BY sue123;
GRANT CREATE SESSION, DROP ANY TABLE, CREATE SEQUENCE, CREATE
PROCEDURE
TO marcia
/
D. CREATE USER marcia
IDENTIFIED BY sue123;
GRANT CREATE SESSION, CREATE ANY TABLE, DROP ANY TABLE, CREATE
SEQUENCE, CREATE PROCEDURE
TO marcia
/
Ans: D
Question 9.
Evaluate this SQL script:
CREATE USER hr IDENTIFIED BY hr01;
CREATE ROLE hr_director;
GRANT hr_director TO hr;
GRANT SELECT ON teacher TO hr_director;
CREATE OR REPLACE ROLE hr_director
/
How many users are granted the HR_DIRECTOR role, and how many privileges are granted to
the HR_DIRECTOR role?
A. 1 user and 1 privilege
B. 1 user and no privileges
C. no users and 1 privilege
D. no users and no privileges
Ans: AExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 4 of 4
Question 10.
User Dave cannot remember his password and would like to create a new one: 'dave500'. He is
not currently connected to the Oracle Server. User Dave has only been granted the CREATE
SESSION system privilege.
Which statement should he execute?
A. ALTER USER dave
NEW PASSWORD dave500;
B. ALTER USER dave
IDENTIFIED BY dave500;
C. ALTER USER dave
IDENTIFIED BY PASSWORD dave500;
D. ALTER USER dave
IDENTIFIED BY NEW PASSWORD dave500;
E. User Dave CANNOT change his password.
Ans: E
Question 11.
The STUDENT table contains these columns:
ID NUMBER(9) Primary Key
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
ENROLL_DATE DATE
You created a report that displays the column headings and data results in this format:
Student Name Enrolled
------------------------- -----------------
Jones, Jennifer 12-SEP-01
Which two statements concerning this report's SELECT statement are true? (Choose two.)
A. The display lengths of the LAST_NAME and FIRST_NAME columns are increased.
B. Separate column aliases are used for the LAST_NAME, FIRST_NAME, and
ENROLL_DATE columns.
C. The LAST_NAME and FIRST_NAME columns are concatenated together.
D. One column alias is used for the LAST_NAME and FIRST_NAME columns.
E. A format mask must have been used on the ENROLL_DATE column.
Ans: C & DExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 5 of 5
Question 12.
The TEACHER table contains these columns:
ID NUMBER(9) Primary Key
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
SUBJECT_ID NUMBER(9)
Which query should you use to display only the full name of each teacher along with the
identification number of the subject they are responsible for teaching?
A. SELECT last_name, first_name, subject_id
FROM teacher;
B. SELECT last_name, first_name, id
FROM teacher;
C. SELECT *
FROM teacher;
D. SELECT last_name, subject_id
FROM teacher;
Ans: A
Question 13.
The STUDENT table contains these columns:
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
EMAIL VARCHAR2(50)
You are writing a SELECT statement to retrieve the names of students that do NOT have an
email address.
SELECT last_name||' , ' ||first_name "Student Name"
FROM student
Which WHERE clause should you use to complete this statement?
A. WHERE email = NULL;
B. WHERE email != NULL;
C. WHERE email IS NULL;
D. WHERE email IS NOT NULL;
Ans: CExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 6 of 6
Question 14.
You query the database with this SQL statement:
SELECT *
FROM transaction;
For which purpose was this statement created?
A. to view the data in the TRANSACTION table
B. to insert data into the TRANSACTION table
C. to review the structure of the TRANSACTION table
D. to delete selected data from the TRANSACTION table
Ans: A
Question 15.
Which statements pertaining to SQL and iSQL*Plus are true? (Choose all that apply.)
A. SQL and iSQL*Plus are command languages.
B. iSQL*Plus can format query results.
C. SQL includes a continuation character.
D. iSQL*Plus can send SQL statements to the server.
E. SQL runs on a browser.
Ans: B & D
Question 16.
Which Oracle product contains its own command language and can be used to create script files?
A. iSQL*Plus
B. SQL
C. PL/SQL
D. JAVA*SQL?
Ans: AExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 7 of 7
Question 17.
The EMPLOYEE_HIST table contains these columns:
EMPLOYEE_ID NUMBER Primary Key
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(3)
POSITION VARCHAR2(30)
MANAGER_ID NUMBER
SALARY NUMBER(6,2)
Evaluate this statement:
SELECT DISTINCT department_id, manager_id
FROM employee_hist;
Which statement is true?
A. The query will fail because the DISTINCT keyword may only be used in a single column
SELECT list.
B. A particular MANAGER_ID can be displayed more than once.
C. A particular DEPARTMENT_ID can only be displayed once.
D. A unique combination of MANAGER_ID and DEPARTMENT_ID may displayed more than
once.
Ans: B
Question 18.
The ACCOUNT table contains these columns:
ACCOUNT_ID NUMBER(12)
NEW_BALANCE NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
FINANCE_CHARGE NUMBER(7,2)
With the least amount of effort, you want to display all of the ACCOUNT table records. Which
query should you use?
A. SELECT *
FROM account;
B. SELECT account_id, new_balance, prev_balance, finance_charge
FROM account;
C. SELECT all
FROM account;
C. SELECT any
FROM account;
Ans: AExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 8 of 8
Question 19.
Examine the structure of the LINE_ITEM table as shown below:
LINE_ITEM_ID NUMBER(9) NOT NULL, Primary Key
ORDER_ID NUMBER(9) NOT NULL, Primary Key, Foreign Key to
ORDER_ID column of
the CURR_ORDER table
PRODUCT_ID NUMBER(9) NOT NULL, Foreign Key to PRODUCT_ID
column of the
PRODUCT table
QUANTITY NUMBER(9)
You query the database with this SQL statement:
SELECT order_id||'-' ||line_item_id||' ' ||product_id||' ' ||quantity "Purchase"
FROM line_item;
Which component of the SELECT statement is a literal?
A. -
B. ||
C. Purchase
D. quantity
Ans: A
Question 20.
The ACCOUNT table contains these columns:
ACCOUNT_ID NUMBER(12)
NEW_PURCHASES NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
FINANCE_CHARGE NUMBER(7,2)
PAYMENTS NUMBER(7,2)
You must print a report that contains the account number and the current balance for a particular
customer. The current balance consists of the sum of an account' s previous balance, new
purchases, and finance charge. You must calculate the finance charge based on a rate of .9
percent. Payments must be deducted from this amount. The customer' s account number is
543842. Which SELECT statement will you use?
A. SELECT account_id, new_purchases + (prev_balance * .009) - payments
FROM account
WHERE account_id = 543842;
B. SELECT new_balance + finance_charge - payments
FROM account
WHERE account_id = 543842;
C. SELECT account_id, new_purchases + prev_balance * 1.009 - payments
FROM account
WHERE account_id = 543842;
D. SELECT account_id, new_purchases + (prev_balance * 1.009) + finance_charge -
payments
FROM account
WHERE account_id = 543842;
Ans: CExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 9 of 9
Question 21.
The ACCOUNT table contains these columns:
ACCOUNT_ID NUMBER(12)
FINANCE_CHARGE NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
PAYMENTS NUMBER(7,2)
NEW_PURCHASES NUMBER(7,2)
You created the ACCOUNT_ID_SEQ sequence to generate sequential values for the
ACCOUNT_ID column.
You issue this statement:
ALTER TABLE account
MODIFY (finance_charge NUMBER(8,2));
Which statement about the ACCOUNT_ID_SEQ sequence is true?
A. The sequence is dropped.
B. The precision of the sequence is changed.
C. The sequence is reverted to its minimum value.
D. The sequence is unchanged.
Ans: D
Question 22.
Which SELECT statement will display the next value of the PARTS_ID_SEQ sequence by
actually retrieving the value from the sequence?
A. SELECT NEXTVAL(parts_id_seq)
FROM SYS.DUAL;
B. SELECT parts_id_seq.NEXTVAL
FROM inventory;
C. SELECT parts_id_seq.NEXTVAL
FROM SYS.DUAL;
D. SELECT NEXTVAL(parts_id_seq)
FROM inventory;
E. SELECT parts_id_seq NEXTVAL
FROM inventory;
Ans: CExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 10 of 10
Question 23.
You issue this statement:
CREATE PUBLIC SYNONYM part
FOR linda.product;
Which task was accomplished by this statement?
A. A new segment object was created.
B. A new object privilege was assigned.
C. A new system privilege was assigned.
D. The need to qualify an object name with its schema was eliminated.
Ans: D
Question 24.
User Mark wants to eliminate the need to type the full table name when querying the
TRANSACTION_HISTORY table existing in her schema. All other database users should use the
schema and full table name when referencing this table.
Which statement should user Marilyn execute?
A. CREATE PUBLIC SYNONYM trans_hist
FOR mark;
B. CREATE SYNONYM trans_hist
FOR transaction_history;
C. CREATE PRIVATE SYNONYM trans_hist
FOR mark.transaction_history;
D. CREATE PUBLIC trans_hist SYNONYM
FOR mark.transaction_history;
Ans: B
Question 25.
The TEACHER table in your schema contains these columns:
ID NUMBER(9) NOT NULL, Primary Key
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
SUBJECT_ID NUMBER(9)
You execute this statement:
CREATE INDEX teacher_name_idx
ON teacher(first_name, last_name);
Which statement is true?
A. The statement creates a composite non-unique index.
B. The statement creates a composite unique index.
C. You must have the CREATE ANY INDEX privilege for the statement to succeed.
D. The statement will fail because it contains a syntax error.
Ans: AExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 11 of 11
Question 26.
Examine the structure of the CURR_ORDER table as shown below:
CURR_ORDER Table
ORDER_ID NUMBER NOT NULL, Primary Key
CUSTOMER_ID NUMBER NOT NULL Foreign Key to CUSTOMER_ID column of the
CUSTOMERS table
EMP_ID NUMBER NOT NULL, Foreign Key to EMP_ID column of the
EMPLOYEES table
ORDER_DT DATE NOT NULL
ORDER_AMT NUMBER (7,2)
SHIP_METHOD VARCHAR2 (5)
You created the ORDER_V view selecting all rows and columns from the ORDER table where
the amount of the order was over $250 and the date of the order was after January 1, 2000. The
CREATE VIEW statement included the WITH CHECK OPTION clause.
Which statement will execute successfully?
A. INSERT INTO order_v (order_id, customer_id, emp_id, order_dt, order_amt)
VALUES (840, 292, 104, ' 10-OCT-2001' , 318);
B. INSERT INTO order_v (order_id, customer_id, emp_id, order_amt, ship_method)
VALUES (936, 292, 104, 256.3, ' UPXS' );
C. INSERT INTO order_v (order_id, customer_id, emp_id, order_dt, order_amt, ship_method)
VALUES (164, 292, 104, ' 10-MAY-2001' , 3.56, ' UPXS' );
D. INSERT INTO order_v (order_id, customer_id, emp_id, order_dt, order_amt, ship_method)
VALUES (203, 292, 104, ' 10-OCT-1999' , 298.4, ' UPXS' );
Ans: A
Question 27.
An inline view is a SELECT statement that is given an alias and is embedded in the ______
clause of another SELECT statement.
A. FROM
B. WHERE
C. SELECT
D. CASE
Ans: AExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 12 of 12
Question 28.
Examine the structures of the EMPLOYEE and CURR_ORDER tables as shown below:
EMPLOYEE Table
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
LAST_NAME VARCHAR2 (25)
FIRST NAME VARCHAR2 (25)
DEPT_ID NUMBER Foreign key to DEPT_ID column of the DEPARTMENTS table
JOB_ID NUMBER Foreign key to JOB_ID column of the JOBS table
MGR_ID NUMBER References EMPLOYEE_ID column
SALARY NUMBER (9,2)
COMMISSION NUMBER (7,2)
HIRE_DATE DATE
CURR_ORDER Table
ORDER_ID NUMBER NOT NULL, Primary Key
CUSTOMER_ID NUMBER Foreign key to CUSTOMER_ID column of the CUSTOMERS
table
EMPLOYEE_ID NUMBER Foreign key to EMP_ID column of the EMPLOYEES table
ORDER_DATE DATE
ORDER_AMT NUMBER (7,2)
SHIP_METHOD NUMBER (5)
You queried the database with this SQL statement:
SELECT a.last_name, a.first_name, a.job_id, NVL(a.commission, 0), b.avgcomm
FROM employee a, (SELECT job_id, AVG(commission) AVGCOMM
FROM employee
WHERE commission IS NOT NULL
GROUP BY job_id) b
WHERE a.job_id = b.job_id
AND a.commission < b.avgcomm;
Which is a result of this query?
A. The AVG function' s DISTINCT keyword must be used in the inner query or the statement
will fail when executed.
B. The employee information displayed will be for employees that have a commission that is
less than the average commission of all employees who have the same job.
C. The employee information displayed will be displayed in numeric order by the JOB_ID and
in alphabetical order by the LAST_NAME where the JOB_IDs are the same.
D. A self join CANNOT be used in an outer query when the inner query is an inline view. The
self join must be placed in the inner query for the statement to execute successfully.
Ans: BExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 13 of 13
Question 29.
Examine the structures of the EMPLOYEE and CURR_ORDER tables.
EMPLOYEE Table
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
LAST_NAME VARCHAR2 (25)
FIRST NAME VARCHAR2 (25)
DEPT_ID NUMBER Foreign key to DEPT_ID column of the DEPARTMENTS table
JOB_ID NUMBER \ Foreign key to JOB_ID column of the JOBS table
MGR_ID NUMBER References EMPLOYEE_ID column
SALARY NUMBER (9,2)
COMMISSION NUMBER (7,2)
HIRE_DATE DATE
CURR_ORDER Table
ORDER_ID NUMBER NOT NULL, Primary Key
CUSTOMER_ID NUMBER Foreign key to CUSTOMER_ID column of the CUSTOMERS
table
EMPLOYEE_ID NUMBER Foreign key to EMP_ID column of the EMPLOYEES table
ORDER_DATE DATE
ORDER_AMT NUMBER (7,2)
SHIP_METHOD NUMBER (5)
To keep your top sales representatives motivated, your company plans to increase the bonuses
of employees. You need to create a SELECT statement that returns the name, bonus, and
maximum order amount associated with each employee for all employees whose bonus is less
than 8 percent of their maximum order amount.
Which SELECT statement should you use?
A. SELECT e.last_name, e.first_name, e.commission, o.maxamt
FROM employee e, (SELECT employee_id, MAX(order_amt) MAXAMT
FROM curr_order
GROUP BY employee_id) o
WHERE e.employee_id = o.employee_id
AND e.commission < .08 * o.maxamt;
B. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amount) MAXAMT
FROM employee e, curr_order o
WHERE e.employee_id = o.employee_id
AND e.commission < .08 * o.maxamt
GROUP BY e.last_name, e.first_name, e.commission;
C. SELECT e.last_name, e.first_name, e.commission, MAX(o.order_amt)
FROM employee e, curr_order
WHERE e.employee_id = o.employee_id
AND e.commission < .08 * (SELECT employee_id, MAX(order_amt) MAXAMT
FROM curr_order
GROUP BY employee_id);
D. SELECT last_name, first_name, commission, max_amt
FROM employee, (SELECT employee_id, MAX(order_amt) MAXAMT
FROM curr_order
GROUP BY employee_id)
WHERE employee.employee_id = curr_order.employee_id
AND commission < .08 * maxamt;
Ans: AExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 14 of 14
Question 30.
Which statements concerning the creation of a view are true? (Choose all that apply.)
A. A constraint name must be provided when using the WITH CHECK OPTION clause or the
statement will fail.
B. View columns that are the result of derived values must be given a column alias.
C. When the view already exists, using the OR REPLACE option requires the re-granting of
the object privileges previously granted on the view.
D. A view may have column names that are different than the actual base table(s) column
names by using column aliases.
Ans: B & D
Question 31.
You need to create the CURRENTPR table. The table must meet these requirements:
1. The table must contain the EMPLOYEE_ID and HRS_WORKED columns for numeric data.
2. The table must contain the PAY_DATE column for date values.
3. The table must contain the PAY_RATE and PAY_AMT columns for numeric data with precision
and scale of 5,2 and 10,2 respectively.
4. The table must have a composite primary key on the EMPLOYEE_ID and PAY_RATE
columns.
Which CREATE TABLE statement will satisfy these requirements?
A. CREATE TABLE currentpr (
employee_id NUMBER
CONSTRAINT employee_id_pk PRIMARY KEY(employee_id),
pay_date DATE
CONSTRAINT pay_date_pk PRIMARY KEY(pay_date),
hrs_worked NUMBER,
pay_rate NUMBER(5,2),
pay_amt NUMBER(10,2));
B. CREATE TABLE currentpr (
employee_id NUMBER
CONSTRAINT currentpr_pk PRIMARY KEY(employee_id, pay_date),
pay_date DATE,
hrs_worked NUMBER,
pay_rate NUMBER(5,2),
pay_amt NUMBER(10,2));
C. CREATE TABLE currentpr (
employee_id NUMBER,
pay_date DATE,
hrs_worked NUMBER,
pay_rate NUMBER(5,2),
pay_amt NUMBER(10,2),
CONSTRAINT employee_id_pk PRIMARY KEY(employee_id),
CONSTRAINT pay_date_pk PRIMARY KEY(pay_date));
D. CREATE TABLE currentpr (
employee_id NUMBER,
pay_date DATE,
hrs_worked NUMBER,
pay_rate NUMBER(5,2),
pay_amt NUMBER(10,2),
CONSTRAINT currentpr_pk PRIMARY KEY(employee_id, pay_date));
Ans: DExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 15 of 15
Question 32.
For which two types of constraints will a unique index be automatically created? (Choose two.)
A. UNIQUE
B. PRIMARY KEY
C. NOT NULL
D. FOREIGN KEY
E. CHECK
Ans: A & B
Question 33.
You disabled the PRIMARY KEY constraint on the ID column in the INVENTORY table and
updated all the values in the INVENTORY table. You need to enable the constraint and verify that
the new ID column values do not violate the constraint. If any of the ID column values do not
conform to the constraint, an error message should be returned.
valuate this statement:
ALTER TABLE inventory
ENABLE CONSTRAINT inventory_id_pk;
Which statement is true?
A. The statement will achieve the desired results.
B. The statement will execute, but will NOT enable the PRIMARY KEY constraint.
C. The statement will execute, but will NOT verify that values in the ID column do NOT violate
the constraint.
D. The statement will return a syntax error.
Ans: AExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 16 of 16
Question 34.
Examine the structures of the PRODUCT and SUPPLIER tables.
PRODUCT Table
PRODUCT_ID NUMBER NOT NULL, Primary Key
PRODUCT_NAME NUMBER (25)
SUPPLIER_ID NUMBER Foreign key to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER
SUPPLIER Table
SUPPLIER_ID NUMBER NOT NULL, Primary Key
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
PEGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)
Evaluate this statement:
ALTER TABLE product
DISABLE CONSTRAINT supplier_id_fk;
For which task would you issue this statement?
A. to drop the FOREIGN KEY constraint on the PRODUCT table
B. to remove any constraint references on the SUPPLIER_ID column in the PRODUCT table
C. to disable any dependent integrity constraints on the SUPPLIER_ID column in the
PRODUCT table
D. to deactivate the FOREIGN KEY constraint on the PRODUCT table
Ans: D
Question 35.
Which four statements about Oracle constraints are true? (Choose four.)
A. A UNIQUE constraint specifies a column or combination of columns whose values must be
unique for all rows in a table.
B. A CHECK constraint specifies a condition that must be true.
C. A PRIMARY KEY constraint uniquely identifies each row of a table.
D. A NOT NULL constraint ensures that null values are NOT allowed in a column.
E. A UNIQUE constraint prohibits the input of nulls because nulls do NOT satisfy the
constraint conditions.
F. A PRIMARY KEY constraint allows null values in a column when the column is part of a set
of columns that uniquely identifies each row.
Ans: A, B, C & DExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 17 of 17
Question 36.
Which statement about a table is true?
A. A table can have up to 10,000 columns.
B. The size of a table does NOT need to be specified.
C. A table CANNOT be created while users are using the database.
D. The structure of a table CANNOT be modified while the table is online.
Ans: B
Question 37.
Examine the structure of the EMPLOYEE table.
EMPLOYEE Table
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_LNAME VARCHAR2 (25)
EMP_FNAME VARCHAR2 (25)
DEPT_ID NUMBER Foreign key to DEPT_ID column of the DEPARTMENT table
JOB_ID NUMBER Foreign key to JOB_ID column of the JOB table
MGR_ID NUMBER References EMPLOYEE_ID column
SALARY NUMBER (9,2)
HIRE_DATE DATE
DPB DATE
Which statement should you use to increase the EMP_LNAME column length to 25 if the column
currently contains 3000 records?
A. You CANNOT increase the width of the EMP_LNAME column.
B. ALTER TABLE employee
MODIFY emp_lname VARCHAR2(25);
C. ALTER TABLE employee
RENAME emp_lname VARCHAR2(25);
D. ALTER employee TABLE
MODIFY COLUMN emp_lname VARCHAR2(25);
E. ALTER employee TABLE
MODIFY COLUMN (emp_lname VARCHAR2(25));
Ans: BExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 18 of 18
Question 38.
Examine the structure of the PO_DETAIL table.
PO_DETAIL Table
PO-NUM PO_LINE IN-PRODUCT ID-PRODUCT UNIT_PRICE
10052 1 1 100 10.30
10052 2 2 100 10.00
10054 1 1 50 72.00
10054 2 2 10 10.00
10054 3 3 10 10.00
Which statement will permanently remove all the data in, the indexes on, and the structure of the
PO_DETAIL table?
A. DELETE TABLE po_detail;
B. TRUNCATE TABLE po_detail;
C. ALTER TABLE po_detail
SET UNUSED (po_num, po_line_id, product_id, quantity, unit_price);
D. DROP TABLE po_detail;
Ans: D
Question 39.
Examine the structure of the EMPLOYEE table.
EMPLOYEE Table
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_LNAME VARCHAR2 (25)
EMP_FNAME VARCHAR2 (25)
DEPT_ID NUMBER Foreign key to DEPT_ID column of the DEPARTMENT table
JOB_ID NUMBER Foreign key to JOB_ID column of the JOB table
MGR_ID NUMBER References EMPLOYEE_ID column
SALARY NUMBER (9,2)
HIRE_DATE DATE
DPB DATE
The MGR_ID column currently contains employee identification numbers, and you need to allow
users to include text characters in the identification values. Which statement should you use to
implement this?
A. ALTER employee
MODIFY (mgr_id VARCHAR2(15));
B. ALTER TABLE employee
MODIFY (mgr_id VARCHAR2(15));
C. ALTER employee TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));
D. ALTER TABLE employee
REPLACE (mgr_id VARCHAR2(15));
E. You CANNOT modify the data type of the MGR_ID column.
Ans: EExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 19 of 19
Question 40.
Which CREATE TABLE statements will fail? (Choose all that apply.)
A. CREATE TABLE time1 (time1 NUMBER(9));
B. CREATE TABLE date (time_id NUMBER(9));
C. CREATE TABLE time (time_id NUMBER(9));
D. CREATE TABLE time* (time_id NUMBER(9));
E. CREATE TABLE $time (time_id NUMBER(9));
F. CREATE TABLE datetime (time_id NUMBER(9));
Ans: B, D & E
Question 41.
Evaluate this statement:
DELETE FROM workorder;
What does this statement accomplish?
A. discards only the structure of the WORKORDER table
B. deletes the WORKORDER column
C. deletes all the rows from the WORKORDER table
D. deletes all the values in the columns that do NOT have NOT NULL constraints
E. generates an error because the FROM keyword should NOT be included
F. deletes all rows from the WORKORDER table and permanently discards the table' s structure
Ans: C
Question 42.
The PRODUCT table contains these columns:
PRODUCT_ID NUMBER PK
NAME VARCHAR2(30)
LIST_PRICE NUMBER(7,2)
COST NUMBER(7,2)
You logged on to the database to update the PRODUCT table. After your session began, you
issued these statements:
INSERT INTO product VALUES(4,' Ceiling Fan' ,59.99, 32.45);
INSERT INTO product VALUES(5,' Ceiling Fan' ,69.99,37.20);
SAVEPOINT A;
UPDATE product SET cost = 0;
SAVEPOINT B;
DELETE FROM product WHERE UPPER(name) = ' CEILING FAN' ;
ALTER TABLE product ADD qoh NUMBER DEFAULT 10;
ROLLBACK TO B;
UPDATE product SET name = ' CEILING FAN KIT' WHERE product_id = 4;
Then you exit iSQL*Plus. Which of the statements you issued were committed?
A. only the INSERT statements
B. only the INSERT statements and the first UPDATE statement
C. the INSERT statements, the first UPDATE statement, and the DELETE statement
D. all of the DML operations
E. none of the DML operations
Ans: DExam Name:
Exam Type:
Exam Code:
Introduction to Oracle9i: SQL
Oracle
1Z0-007 Total Questions: 178
Page 20 of 20
Question 43.
Which statement type would be used to remove transactions more than one year old from the
TRX table?
A. TCL
B. DCL
C. DDL
D. DML
E. DRL
Ans: D
Question 44.
Which two DML statements could you use to modify the contents of the PRODUCT_NAME
column of the existing PRODUCT table? (Choose two.)
A. INSERT
B. MODIFY
C. UPDATE
D. COMMIT
E. ALTER
F. MERGE
[Show More]
.png)
.png)
