SQL Objective Questions For OCP

m1.You are formulating a SQL statement to retrieve data from Oracle. Which of the following SQL statements is invalid?

A.            select NAME, JERSEY_NO where Jersey_No = 6;

B.            select NAME, JERSEY_NO from PLAYERS;

C.            select * from PLAYERS where JERSEY_NO = 6;

D.            select JERSEY_NO from PLAYERS;            

2.            You are processing some data changes in your SQL*Plus session as part of one transaction. Which of the following choices does not typically indicate the end of a transaction?

A.            Issuing an update statement

B.            Issuing a commit statement

C.            Issuing a rollback statement

D.            Ending your session       

3.            You have just removed 1,700 rows from a table. In order to save the changes you've made to the database, which of the following statements is used

A.            savepoint

B.            commit

C.            rollback

D.            set transaction 

4.            To identify the columns that are indexed exclusively as the result of their inclusion in a constraint, which of the following dictionary views is appropriate

A.            USER_INDEXES

B.            USER_TAB_COLUMNS

C.            USER_COLUMNS

D.            USER_CONS_COLUMNS              

5.            You are creating some tables in your database as part of the logical data model. Which of the following constraints can only be created as a columnconstraint (that is, not as a table constraint) either when you create or alter the table?

A.            Unique

B.            Foreign key

C.            Check

D.            Not NULL            

6.            You have a table with three associated indexes, two triggers, two references to that table from other tables, and a view. You issue the drop table cascade constraints statement. Which of the following objects will still remain after the statement is issued?

A.            The triggers

B.            The indexes

C.            The foreign keys in the other tables

D.            The view             

7.            You are using SQL operations in Oracle. All of the following DATE functions return a DATE datatype except one. Which one is it?

A.            NEW_TIME

B.            LAST_DAY

C.            ADD_MONTHS

D.            MONTHS_BETWEEN      

8.            You issue a select statement on the BANK_ACCT table containing the order by clause. Which of the following uses of the order by clause would produce an error?

A.            order by acctno DESC;

B.            order by 1;

C.            order by sqrt(1);

D.            order by acctno ASC;     

9.            You execute the query select 5 + 4 from DUAL. You have never inserted data into the DUAL table before. Which of the following statements best describes the DUAL table?

A.            Dictionary view containing two schema names

B.            Table with one column and one row used in various operations

C.            Dictionary view containing two index names

D.            Table with two columns and no rows used in various operations               

10.          You issue the following statement:

SELECT DECODE(ACCTNO, 123456, 'CLOSED', 654321, 'SEIZED', 590395, 'TRANSFER','ACTIVE') FROM BANK_ACCT;

If the value for ACCTNO is 503952, what information will this statement display?

A.            ACTIVE

B.            TRANSFER

C.            SEIZED

D.            CLOSED               

11.          You are entering several dozen rows of data into the BANK_ACCT table. Which of the following statements enables you to execute the same statement again and again, entering different values for variables at statement runtime?

A.            insert into BANK_ACCT (ACCTNO, NAME) VALUES (123456,'SMITH');

B.            insert into BANK_ACCT (ACCTNO, NAME) VALUES (VAR1, VAR2);

C.            insert into BANK_ACCT (ACCTNO, NAME) VALUES (&VAR1, '&VAR2');

D.            insert into BANK_ACCT (select ACCTNO, NAME from EMP_BANK_ACCTS);          

12.          You execute the following SQL statement: select ADD_MONTHS ('28-APR-97',120) from DUAL. What will Oracle return?

A.            28-APR-03

B.            28-APR-07

C.            28-APR-13

D.            28-APR-17          

13.          On Monday, June 26, 2037, at 10:30 P.M., you issue the following statement against an Oracle database:

ALTER SESSION SET NLS_DATE_FORMAT ='DAY MONTH DD, YYYY: HH:MIAM';

Then you issue the following statement:

SELECT SYSDATE FROM DUAL;

What will Oracle return?

A.            26-JUN-37

B.            June 26, 2037, 22:30

C.            26-JUN-2037

D.            MONDAY JUNE 26, 2037: 10:30PM           

14.          You want to join the data from two tables, A and B, into one result set and display that set in your session. Tables A and B have a common column, called C in both tables. Which of the following choices correctly displays the where clause you should use if you want to see the data in table A where the value in column C equals 5, even when no corresponding value appears in table B?

A.            where A.C = 5 AND A.C = B.C;

B.            where A.C = 5 AND A.C = B.C (+);

C.            where A.C = 5 AND A.C (+) = B.C(+);

D.            where A.C = 5; 

15.          Each of the following statements is true about associated columns and datatypes except one. Which of the following statements is not true?

A.            A column designed to hold data in a table must be declared with a datatype large enough to hold values for that column.

B.            When creating composite primary keys, the datatypes in all columns within the primary key must be the same datatype.

C.            When creating referential integrity constraints between two tables, the datatype of the referenced column in the parent table must be identical to the referencing column in the child.

D.            When creating record variables designed to hold a row's worth of data, each element's datatype in the record must be large enough to hold the associated column from the table. 

16.          You have a group of values from a column in a table, and you would like to perform a group operation on them. Each of the following functions operates on data from all rows as a group except for which of the following choices?

A.            avg( )

B.            sqrt( )

C.            count( )

D.            stddev( )             

17.          You have a situation where you need to use the nvl( ) function. All the following statements about the nvl( ) function are true except one. Which is it?

A.            nvl( ) returns the second value passed if the first value is NULL.

B.            nvl( ) handles values of many different datatypes.

C.            nvl( ) returns NULL if the first value is not equal to the second.

D.            Both the values passed for nvl( ) must be the same datatype.   

18.          You create a sequence with the following statement:

CREATE SEQUENCE MY_SEQ

START WITH 394

INCREMENT BY 12

NOMINVALUE

NOMAXVALUE

NOCACHE

NOCYCLE;

A user issues SQL statements to obtain NEXTVAL three times, and then issues SQL statements to obtain CURRVAL four times. What is the current value of the sequence?

A.            406

B.            418

C.            430

D.            442        

19.          Table EMP has 17,394,430 rows in it. You issue a delete from EMP statement, followed by a commit. Then you issue a select count(*) to find out how many rows are in the table. Several minutes later, Oracle returns zero. Why did it take so long for Oracle to obtain this information?

A.            The table was not empty.

B.            The high-water mark was not reset.

C.            Oracle always performs slowly after a commit is issued.

D.            The table data did not exist to be counted anymore.      

20.          After creating a view, you realize that several columns were left out. Which of the following statements should you issue in order to add some columns to your view?

A.            alter view

B.            create or replace view

C.            insert into view

D.            create view       

21.          You are testing several SQL statements for accuracy and usefulness. A SQL statement will result in a Cartesian product as the result of which of thefollowing items?

A.            A join statement without a where clause

B.            The result of the sum( ) operation

C.            select * from DUAL

D.            The result of the avg( ) operation            

22.          In order to set your SQL*Plus session so that your NLS_DATE_FORMAT information is altered in a specific way every time you log into Oracle, what method should you use?

A.            Setting preferences in the appropriate menu option

B.            Creating an appropriate login.sql file

C.            Issuing the alter user statement

D.            Issuing the alter table statement             

23.          The EMP_SALARY table has two columns: EMP_USER and SALARY. EMP_USER is set to be the same as the Oracle username. To allow user MARTHA, the salary administrator, to see her own salary only, you create a view with the following statement:

CREATE VIEW EMP_SAL_VW

AS SELECT EMP_USER, SALARY

FROM EMP_SALARY

WHERE EMP_USER = 'MARTHA';

Later, you decide to deploy this view to other users. Which of the following choices identifies a revision of this view that would prevent users from seeing any salary information other than their own?

A.            create or replace view emp_sal_vw as select emp_user,_salary from emp_salary where emp_user <> user;

B.            create or replace view emp_sal_vw as select emp_user,_salary from emp_salary where emp_user = user;

C.            create or replace view emp_sal_vw as select emp_user,_salary from emp_salary where emp_user <> 'MARTHA';

D.            create or replace view emp_sal_vw as select emp_user,_salary from emp_salary where emp_user in (select emp_user from emp_salary where emp_user <> 'MARTHA');   

24.          You are trying to store data in an Oracle table. All of the following scalar datatypes can be stored in an Oracle database except one. Which is it?

A.            CHAR

B.            RAW

C.            DATE

D.            INTEGER             

25.          You are performing some conversion operations in your SQL*Plus session. To convert a date value into a text string, you should use which of the following conversion functions?

A.            CONVERT

B.            TO_CHAR

C.            TO_NUMBER

D.            TO_DATE            

26.          Your attempt to read the trigger code stored in the Oracle data dictionary view ALL_TRIGGERS has encountered a problem. The contents of the TRIGGER_BODY column appear to be getting cut off at the end. In order to resolve this problem, which of the following measures is appropriate?

A.            Grant appropriate select privileges on ALL_TRIGGERS to yourself.

B.            Increase your memory allocation limit with the alter user statement.

C.            Use the set command to allow for larger LONG column values.

D.            Drop and recreate the ALL_TRIGGERS view.       

27.          You issue the following update statement against the Oracle database:

UPDATE BANK_ACCT SET NAME = 'SHAW';

Which records will be updated in that table?

A.            The first record only

B.            All records

C.            The last record only

D.            None of the records      

28.          You create a table but then subsequently realize you need a few new columns. To add those columns later, you should issue which of the following statements?

A.            create or replace table

B.            alter table

C.            create table

D.            truncate table  

29.          You are busy creating your tables based on a logical data model. Which of the following constraints requires the references privilege in order to be created?

A.            unique

B.            foreign key

C.            check

D.            not NULL            

30.          The INVENTORY table has three columns: UPC_CODE, UNITS, and DELIV_DATE. The primary key is UPC_CODE. You want to add new records daily through a view. The view will be created using the following code:

CREATE VIEW DAY_INVENTORY_VW

AS SELECT UPC_CODE, UNITS, DELIV_DATE

FROM INVENTORY

WHERE DELIV_DATE = SYSDATE

ORDER BY UPC_CODE;

What happens when you try to create the previous view?

A.            Oracle returns an error stating that the order by clause is not permitted on views.

B.            Oracle returns an error stating that the with check option clause is required for creating this view.

C.            Oracle returns an error stating that the select statement must be enclosed in parentheses.

D.            Oracle creates the view successfully.     

31.          You need to search for text data in a column, but you only remember part of the string. Which of the following SQL operations enables the use of wildcard comparisons?

A.            in

B.            exists

C.            between

D.            like        

32.          You have a script you plan to run using SQL*Plus that contains one SQL statement that inserts data into one table. Which of the following options is the easiest way for this script to enable you to specify values for variables once in the script in a way where no user interaction is required at the SQL*Plus prompt?

A.            Use define to capture values.

B.            Use accept to capture values for each run.

C.            Use & to specify values at runtime for the statement.

D.            Use hard-coded values in the statement.            

33.          You join data from two tables, EXPNS and EMP, into one result set and display that set in your session. The tables have a common column called EMPID. Which of the following choices correctly displays the where clause you would use if you wanted to see the data in table EMP where the value in column EMPID equals 39284, but only when a corresponding value appears in table EXPNS?

A.            where EMP.EMPID = 39284 AND EMP.EMPID = EXPNS.EMPID;

B.            where EMP.EMPID = 39284 (+) AND EMP.EMPID = EXPNS.EMPID;

C.            where EMP.EMPID = EXPNS.EMPID;

D.            where EMP.EMPID = 39284 AND EMP.EMPID = EXPNS.EMPID (+);           

34.          Review the following transcript of a SQL*Plus session:

INSERT INTO INVENTORY (UPC_CODE, PRODUCT)

VALUES (503949353,'HAZELNUT COFFEE');

INSERT INTO INVENTORY (UPC_CODE, PRODUCT)

VALUES (593923506,'SKIM MILK');

INSERT INTO INVENTORY (UPC_CODE, PRODUCT)

VALUES (402392340,'CANDY BAR');

SAVEPOINT INV1;

UPDATE INVENTORY SET UPC_CODE = 50393950

WHERE UPC_CODE = 402392340;

UPDATE INVENTORY SET UPC_CODE = 4104930504

WHERE UPC_CODE = 402392340;

COMMIT;

UPDATE INVENTORY SET PRODUCT = (

SELECT PRODUCT FROM INVENTORY

WHERE UPC_CODE = 50393950)

WHERE UPC_CODE = 593923506;

ROLLBACK;

Which of the following UPC codes will not have records in the INVENTORY table as a result of this series of operations?

A.            593923506

B.            503949353

C.            4104930504

D.            50393950            

35.          You are removing a table from the Oracle database. When you issue the drop table command to remove the table, what happens to any of the views that may have an object dependency on that table?

A.            The views are dropped automatically along with the table.

B.            Views in the same schema as the table are dropped automatically, but views outside that schema are not dropped.

C.            Views in the same database as the table are dropped automatically, but views that access the table via database link are not dropped.

D.            Views with object dependencies on the table being dropped are rendered invalid automatically, but are not dropped.            

36.          You want to join data from four tables into one result set and display that set in your session. Table A has a column in common with table B, table B with table C, and table C with table D. You want to further restrict data returned from the tables by only returning data where values in the common column shared by A and B equal 5. How many conditions should you have in the where clause of your select statement?

A.            Two

B.            Three

C.            Four

D.            Five       

37.          You are attempting to explain the Oracle security model for an Oracle database to the new security administrator. What are two components of the Oracle database security model?

A.            Password authentication and granting privileges

B.            Password authentication and creating database objects

C.            Creating database objects and creating users

D.            Creating users and password authentication      

38.          You have a script you plan to run using SQL*Plus that contains several SQL statements that manage milk inventory in several different tables based onvarious bits of information. You want the output to go into a file for review later. Which command should you use?

A.            prompt

B.            echo

C.            spool

D.            define  

39.          You have a table called TEST_SCORE that stores test results by student personal ID number, test location, and date the test was taken. Tests given in various locations throughout the country are stored in this table. A student is not allowed to take a test for 30 days after failing it the first time, and a check in the application prevents the student from taking a test twice in 30 days at the same location. Recently, it has come to everyone's attention that students are able to circumvent the 30-day rule by taking a test in a different location. Which of the following SQL statements would be useful for identifying the students who have done so?

A.            select A.STUDENT_ID, A.LOCATION, B.LOCATION from TEST_SCORE A, TEST_SCORE B where A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION = B.LOCATION AND trunc(A.TEST_DATE)+30 <= trunc(B.TEST_DATE) AND trunc(A.TEST_DATE)-30 >= trunc(B.TEST_DATE);

B.            select A.STUDENT_ID, A.LOCATION, B.LOCATION from TEST_SCORE A, TEST_SCORE B where A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION <> B.LOCATION AND trunc(A.TEST_DATE)+30 >= trunc(B.TEST_DATE) AND trunc(A.TEST_DATE)-30 <= trunc(B.TEST_DATE);

C.            select A.STUDENT_ID, A.LOCATION, B.LOCATION from TEST_SCORE A, TEST_SCORE B where A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION = B.LOCATION AND trunc(A.TEST_DATE)+30 >= trunc(B.TEST_DATE) AND trunc(A.TEST_DATE)-30 <= trunc(B.TEST_DATE);

D.            select A.STUDENT_ID, A.LOCATION, B.LOCATION from TEST_SCORE A, TEST_SCORE B where A.STUDENT_ID = B.STUDENT_ID AND A.LOCATION <> B.LOCATION AND trunc(A.TEST_DATE)+30 <= trunc(B.TEST_DATE) AND trunc(A.TEST_DATE)-30 >= trunc(B.TEST_DATE);              

40.          In an expense application, you are searching for employee information in the EMPLOYEE table corresponding to an invoice number you have. The INVOICE table contains EMPID, the primary key for EMPLOYEE. Which of the following options is appropriate for obtaining data from EMPLOYEE using your invoice number?

A.            select * from EMPLOYEE where empid = &empid;

B.            select * from EMPLOYEE where empid = 69494;

C.            select * from EMPLOYEE where empid = (select empid from invoice where invoice_no = 4399485);

D.            select * from EMPLOYEE;            

41.          Which of the following uses does not describe an appropriate use of the having clause?

A.            To put returned data into sorted order

B.            To exclude certain data groups based on known criteria

C.            To include certain data groups based on unknown criteria

D.            To include certain data groups based on known criteria 

42.          You are managing data access for an application with 163 tables and 10,000 users. Which of the following objects would assist in managing access in this application by grouping privileges into an object that can be granted to users at once?

A.            Sequences

B.            Tables

C.            Indexes

D.            Roles    

43.          After logging onto Oracle the first time to access table EMP, user SNOW is told to change his password. Which of the following statements enables him to do so?

A.            alter user

B.            alter table

C.            alter role

D.            alter index         

44.          User SNOW executes the following statement: select * from EMP.This statement executes successfully, and SNOW can see the output. User REED owns table EMP. What object is required in order for this scenario to happen?

A.            User SNOW needs the role to view table EMP.

B.            User SNOW needs the privileges to view table EMP.

C.            User SNOW needs a synonym for table EMP.

D.            User SNOW needs the password for table EMP.               

45.          You issue the following statement in Oracle:

SELECT * FROM EMP WHERE DEPT IN

(SELECT DEPT FROM VALID_DEPTS

WHERE DEPT_HEAD = 'SALLY'

ORDER BY DEPT);

Which of the following choices best indicates how Oracle will respond to this SQL statement?

A.            Oracle returns the data selected.

B.            Oracle returns data from EMP but not VALID_DEPTS.

C.            Oracle returns data from VALID_DEPTS but not EMP.

D.            Oracle returns an error.               

46.          You are coding SQL statements in SQL*Plus. Which of the following is a valid SQL statement?

A.            select nvl(sqrt(59483)) from dual;

B.            select to_char(nvl(sqrt(59483), 0)) from dual;

C.            select to_char(nvl(sqrt(59483), 'VALID')) from dual;

D.            select (to_char(nvl(sqrt(59483), '0')) from dual;

47.          The following output is from a SQL*Plus session:

select PLAY_NAME||', ' || AUTHOR play_table from PLAYS;

My Plays and Authors

-------------------------------------

Midsummer Night's Dream, SHAKESPEARE

Waiting For Godot, BECKETT

The Glass Menagerie, WILLIAMS

Which of the following SQL*Plus commands produced it?

A.            column PLAY_TABLE alias "My Plays and Authors"

B.            column PLAY_TABLE format a12

C.            column PLAY_TABLE heading "My Plays and Authors"

D.            column PLAY_TABLE as "My Plays and Authors"

48.          You create a view with the following statement:

CREATE VIEW BASEBALL_TEAM_VW

AS SELECT B.JERSEY_NUM, B.POSITION, B.NAME

FROM BASEBALL_TEAM B

WHERE B.NAME = (SELECT UNAME FROM MY_USERS);

The contents of the MY_USERS table are listed as follows:

UNAME

-----

JONES

SMITH

FRANK

JENNY

Which of the following players will not be listed when user JONES attempts to query the view?

A.            JONES

B.            SMITH

C.            BABS

D.            JENNY  

49.          Your attempt to read the view-creation code stored in the Oracle data dictionary has encountered a problem. The view code appears to be getting cut off at the end. In order to resolve this problem, which of the following measures is appropriate?

A.            Increase the size of the dictionary view.

B.            Increase your user view allotment with the alter user statement.

C.            Use the set long statement.

D.            Use the set NLS_DATE_FORMAT statement.     

50.          Inspect the following SQL statement:

SELECT FARM_NAME, COW_NAME,

COUNT(CARTON) AS NUMBER_OF_CARTONS

FROM COW_MILK

GROUP BY COW_NAME;

Which of the following choices contains the line with the error?

A.            select FARM_NAME, COW_NAME,

B.            count(CARTON) as NUMBER_OF_CARTONS

C.            from COW_MILK

D.            group by COW_NAME;

E.            This statement has no errors.    

51.          Inspect the following SQL statement:

SELECT COW_NAME,

  MOD(CARTON, FILL_STATUS)

FROM COW_MILK

GROUP BY COW_NAME;

Which of the following lines contains an error?

A.            select COW_NAME,

B.            mod(CARTON, FILL_STATUS)

C.            from COW_MILK

D.            group by COW_NAME;

E.            This statement has no errors.    

52.          You are writing queries against an Oracle database. Which of the following queries takes advantage of an inline view?

A.            select * from EMP_VW where EMPID = (select EMPID from INVOICE where INV_NUM = 5506934);

B.            select A.LASTNAME, B.DEPT_NO from EMP A, (select EMPID, DEPT_NO from DEPT) B where A.EMPID = B.EMPID;

C.            select * from EMP where EMPID IN (select EMPID from INVOICE where INV_NUM > 23);

D.            select 'select * from EMP_VW where EMPID is not NULL;' from USER_TABLES;  

53.          You have several indexes on a table that you want to remove. You want to avoid removing the indexes associated with constraints, however. Each of the following statements will remove the index associated with a constraint except one. Which choice will not remove the index associated with a constraint?

A.            drop index

B.            alter table drop primary key cascade

C.            alter table drop constraint

D.            drop table          

54.          You are managing constraints on a table in Oracle. Which of the following choices correctly identifies the limitations on primary key constraints?

A.            Every primary key column value must be unique.

B.            No primary key column value can be NULL.

C.            Every primary key column value must be unique and none can be NULL.

D.            Every primary key column must be the same datatype as other columns in the table.     

55.          Review the following statement:

CREATE TABLE FOOBAR

( MOO VARCHAR2(3),

 BOO NUMBER);

This table contains 60,000,000 rows. You issue the following statement:

SELECT MOO, BOO FROM FOOBAR WHERE MOO = 'ABC'

This value is unique in column MOO, yet the query takes several minutes to resolve. Which of the following explanations is the best reason why?

A.            Oracle didn't use the existing primary key index.

B.            select statements that do not use views take longer to resolve.

C.            Table FOOBAR has no primary key, and therefore has no index on MOO.

D.            The table had been dropped and recreated.      

56.          You have created a table called EMP with a primary key called EMP_PK_01. In order to identify any objects that may be associated with that table and primary key, what dictionary views and characteristics would you look for?

A.            USER_SEQUENCES, sequences created at the same time

B.            USER_TABLES, tables with the same number of columns

C.            USER_IND_COLUMNS, constraints with the same name as the table

D.            USER_INDEXES, indexes with the same name as the constraint 

57.          You are designing your database and attempting to determine the best method for indexing your tables. Which of the following is a main advantage of using bitmap indexes on a database?

A.            To improve performance on columns with many unique values

B.            To improve performance on columns with few unique values

C.            To improve performance on columns with all unique values

D.            To improve performance on sequences with all unique values  

58.          User HARRIS would like to change a row into the EMPLOYEE table that has three columns: EMPID, LASTNAME, and SALARY. The user would like to update salary data for employee number 59694. Which statement would work best?

A.            update employee set salary = 5000 where empid = 59694;

B.            update employee set empid = 45939 where empid = 59694;

C.            update employee set lastname = 'HARRIS' where empid = 59694;

D.            update employee set salary = 5000 where lastname = 'HARRIS';

59.          You want to grant user TIMOTHY the ability to update data in the EMP table as well as the ability to administer that access for others. Which of the following commands would you issue?

A.            grant update to timothy;

B.            grant update on emp to timothy;

C.            grant update on emp to timothy with grant option;

D.            grant update on emp to timothy with admin option;      

60.          User REED can administer the create session privilege. User REED grants the same create session privilege to MANN using the appropriate clause. MANN then grants the privilege to SNOW. REED discovers MANN issued the privilege to SNOW and revokes the privilege from MANN. Who can connect to Oracle?

A.            REED only

B.            SNOW and MANN only

C.            REED, MANN, and SNOW

D.            REED and SNOW only