1. Using SQL Plus, you create a user with the following command:
CREATE USER Joshua IDENTIFIED BY jyd205
What must you do to allow Joshua database access?
a. Use the ALTER USER command to assign default table space to Joshua.
b. Grant Joshua the CREATE SESSION privilege.
c. Use the ALTER USER command to assign Joshua a default profile.
d. Database access is granted by default.
2. A DBA has added privileges to Randall’s account to create tables and procedures on a database. Which of the following can Randall perform?
a. He can create tables, drop tables, and create procedures in any schema of the database.
b. He can create any table or procedure in his schemas only. He can drop any table from his schema only.
c. He can create a table in any schema of the database but can drop tables from and create procedures only within his own schemas.
d. He can create a table or procedure in any schema of a database and can also drop tables in any schema of the database.
3. Which data dictionary view contains the definition of a view?
a. MY_VIEWS
b. USER_VIEWS
c. SYSTEM_VIEWS
d. USER_TAB_VIEWS
4. You create the worker table using the following command:
CREATE VIEW division-salary-vu
AS SELECT division-no,salary,last-name
FROM worker
WHERE salary>45000
WITH CHECK OPTION;
Click on the EXHIBIT button and examine the worker table (exhibit not available). For which employee can you update the dept no column using this view?
a. Brown
b. South
c. Chizza
d. None
5. Click on the exhibit button and examine the table instance chart of the patient table
Column name Id_number last_name first_name birth_date physician_id
Key type PK
Nulls/Unique NN, UU NN NN
FK table PHYSICIAN
FK column ID_NUMBER
Data type NUM VARCHAR2 VARCHAR2 DATE NUM
Length 10 25 25 10
You create the patient_vu view based on the id number and last name columns from the patient table. How should you modify the view to contain only patients born in 1998?
a. Replace the view, adding a WHERE clause.
b. Use the ALTER command to add a WHERE clause to verify the date.
c. Drop the patient_vu, then create a new view with a WHERE clause.
d. Drop the patient_vu, then create a new view with a HAVING clause.
6. Which of the following statements regarding the use of a sub query in the FROM clause is true?
a. A sub query cannot be used in the FROM clause.
b. The need to create a new view or table is eliminated by placing a sub query in the FROM clause.
c. The need to grant SELECT privileges is eliminated by placing a sub query in the FROM clause.
d. Placing a sub query in the FROM clause defines a data source for future SELECT statements.
7. Examine the following chart:
Column name Id_number last_name first_name birth_date physician_id
Key type PK
Nulls/Unique NN, UU NN NN
FK table PHYSICIAN
FK column ID_NUMBER
Data type NUM VARCHAR2 VARCHAR2 DATE NUM
Length 10 25 25 10
You create the patient_id_seq sequence to be used with the patient table’s primary key column. The sequence begins at 1000 and has a maximum value of 99999999 and increments by 1. You must write a script to insert a row into the patient table and use the sequence you created. Which script should be used?
a. This cannot be done.
b. INSERT INTO patient(id_number, last_name, first_name, Birth_date)
VALUES(patient_id_seq, last_name, first_name, birth_date)
/
c. INSERT INTO patient(id_number, last_name, first_name, Birth_date)
VALUES(patient_id_seq.NEXTVALUE, &last_name,&first_name, & birth_date)
/
d. INSERT INTO patient(id_number, last_name, first_name, Birth_date)
VALUES(patient_id_seq.NEXTVAL, &last_name,&first_name, & birth_date)
/
8. The fruit table has ten columns. Because you query the table with conditions based on four or more columns, you create an index on all columns in the table. Which of the following will occur?
a. Updates on the table will be slower.
b. Inserts will be faster.
c. All queries will be faster.
d. The size of the fruit table will increase.
9. Examine the worker table:
WORKER
Column name ID_NO NAME SALARY DEPT_NO HIRE_DATE
Key type PK FK
Nulls/unique NN, UU NN
FK table DEPARMENT
FK column DEPT_NO
Data type NUM VARCHAR2 NUM NUM DATE
Length 9 25 8,2 3
You must display hire_date values in the following format:
16 of January 20001
Which SELECT statement must be used?
a. SELECT hire_date(fmDD “of”MONTH YYYY’) “Date Hired”
FROM worker;
b. SELECT hire_date(‘DD “of”MONTH YYYY’) “Date Hired”
FROM worker;
c. SELECT TO_CHAR (hire_date,’DDspth of MONTH YYYY’) “Date Hired”
FROM worker;
d. SELECT TO_CHAR(hire_date,’fmDD “of” MONTH YYYYY’)DATE HIRED
FROM worker;
10. Examine the worker table:
WORKER
Column name ID_NO NAME SALARY DEPT_NO HIRE_DATE
Key type PK FK
Nulls/unique NN, UU NN
FK table DEPARMENT
FK column DEPT_NO
Data type NUM VARCHAR2 NUM NUM DATE
Length 9 25 8,2 3
Which SQL statement will display employee hire date from earliest to latest?
a. SELECT hire_date.
FROM worker;
b. SELECT hire_date.
FROM worker
ORDER BY hire_date;
c. SELECT hire_date;
FROM worker
GROUP BY hire_date;
d. SELECT hire_date.
FROM worker
ORDER BY hire_date DESC;
