Total Pageviews

February 21, 2015

2/21/2015 07:45:00 PM
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;