Oracle PL/SQL Online Test

The EMPLOYEES table has these columns: LAST_NAME--VARCHAR2 (35) SALARY--NUMBER (8, 2) COMMISSION_PCT--NUMBER (5, 2) You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?
SELECT last_name, (salary*12)* commission_Pct FROM EMPLOYEES;
SELECT last_name, (salary*12)* IFNULL(commission_pct,0) FROM EMPLOYEES;
SELECT last_name, (salary*12)* NVL2(commission_pct,0) FROM EMPLOYEES;
SELECT last_name, (salary*12)* NVL(commission_pct,0) FROM EMPLOYEES;
Which is true about attributes of iSQL*Plus?
iSQL*Plus commands cannot be abbreviated.
iSQL*Plus commands are accesses from a browser.
iSQL*Plus commands are used to manipulate data in tables.
iSQL*Plus commands manipulate table definitions in the database.
Examine the structure of the EMPLOYEES and DEPARTMENTS tables: EMPLOYEES EMPLOYEE_ID NUMBER DEPARTMENT_ID NUMBER MANAGER_ID NUMBER LAST_NAME VARCHAR2(25) DEPARTMENTS DEPARTMENT_ID NUMBER MANAGER_ID NUMBER DEPARTMENT_NAME VARCHAR2(35) LOCATION_ID NUMBER You want to create a report displaying employee last names, department names, and locations. Which query should you use?
SELECT e.last_name, d. department_name, d.location_id FROM employees e NATURAL JOIN departments D USING department_id ;
SELECT last_name, department_name, location_id FROM employees NATURAL JOIN departments WHERE e.department_id =d.department_id;
SELECT e.last_name, d.department_name, d.location_id FROM employees e NATURAL JOIN departments d;
SELECT e.last_name, d. department_name, d.location_id FROM employees e NATURAL JOIN departments D
In which of the following case you would not use the USING clause?
You want to create a non equijoin.
The tables to be joined have multiple NULL columns.
The tables to be joined have columns with the same name and compatible data types.
You want to use a NATURAL join, but you want to restrict the number of columns in the join condition.
Which SQL statement displays the date March 19, 2001 in a format that appears as "Nineteenth of March 2001 12:00:00 AM"?
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth "of" Month YYYY fmHH:MI:SS AM') NEW_DATE FROM dual;
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Month YYYY HH:MI:SS AM') NEW_DATE FROM dual;
SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmtDdspth "of" Month YYYY fmtHH:MI:SS AM') NEW_DATE FROM dual;
Which statement is true regarding the default behavior of the ORDER BY clause?
Null values are displayed last for descending sequences.
Null values are left out of the sort.
Character values are displayed from Z to A.
Date values are displayed with the earliest value first.
What is true regarding sub queries?
The inner query always sorts the results of the outer query.
The outer query always sorts the results of the inner query.
The outer query must return a value to the inner query.
The inner query returns a value to the outer query.
The database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query? SELECT * FROM HR;
you obtain the results retrieved from the public synonym HR created by the database administrator
You obtain the results retrieved form the HR table that belongs to your schema.
you get an error message because you cannot retrieve from a table that has the same as a public synonym
You obtain the results retrieved from both the public synonym HR and the HR table that belongs to your schema, as a Cartesian product.
You need to give the MANAGER role the ability to select from insert into and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this?
GRANT select, insert, update ON student_grades TO manager;
GRANT select, insert, update ON student_grades TO ROLE manager
GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION;
GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION ;
Which syntax turns an existing constraint on?
ALTER TABLE table_name ENABLE constraint_name ;
ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constraint name ;
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name ;
ALTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name ;
DATETIME data types can not be used when specifying which of the following column definitions?
TIMESTAMP
INTERVAL MONTH TO DAY
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
USER_TAB_PRIVS_MADE
USER_TAB_PRIVS
USER_COL_PRIVS_MADE
USER_COL_PRIVS
Which / SQL* Plus feature can be used to replace values in the where clause?
Substitution variables
replacement variables
prompt variables
instead-of variables
For which constraint does the Oracle Server implicitly create a unique index?
NOT NULL
PRIMARY KEY
FOREIGN KEY
CHECK
In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?
before the WHERE clause
before the FROM clause
after the ORDER BY clause
after the WHERE clause
You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table some of the employees may not have a phone number available. Which data manipulation operation do you perform?
MERGE
INSERT
UPDATE
ADD
Which statement accurately describes a role?
a user can have access to a maximum of 10 roles
A role can have a maximum of 100 privileges contained in it.
Privileges are given to a role by using the CREATE ROLE statement.
A role is a named group of related privileges that can be granted to the user
Description:

The goal of this test is to do a self assessment of how comfortable you are with querying, adding, updating, and removing data from a database via SQL. It will also help you understand how to extract meaningful information across multiple tables.
Disclaimer: Content, such as images used in the questions (if any), have been picked up from various places for the sole purpose of Instruction.

Tags:

Oracle PL/SQL  DBA  OCA  Oracle  OCP

Discussion

Ihead Go

Found a site (http://www.iheadgo.com) that offers tons of practice exams for multiple subjects. They offer practice exams for almost anything possible. Check it out.
Checking out this site and looks interesting...a lot of different Exams TI.
http://www.iheadgo.com

78 days 13 hours 26 minutes ago

Supriyo

Q3. correct ans is option 3 not 4. Q4 ans is definitely NOT the option 4.

253 days 1 hours 41 minutes ago

Qiqa

useful indeed..

268 days 22 hours 21 minutes ago

Pavan

Good Practcice

318 days 3 hours 44 minutes ago

sajan varughese

Kunna

1122 days 14 hours 55 minutes ago

nehajain

in quetn no 6 more than 1 ans in correct
1st option is also correct.

1151 days 31 minutes ago

Jeffery

Very useful :)

1162 days 22 hours 35 minutes ago

asesh

Option D is correct for 3rd question because NVL2 should have three parameters whereas its passed with 2 parameters here..

1184 days 23 hours 31 minutes ago

ramesh

i need to attaind more test

1210 days 22 hours 48 minutes ago

NEER

3rd Qstn answer is C not D...

1275 days 14 hours 28 minutes ago

Your Facebook Friends on WizIQ

More Tests By Author

ASP Server Controls
10 Questions | 533 Attempts

ASP.NET, Namespaces ASP.NET
10 Questions | 1832 Attempts

An ASP.NET Application
10 Questions | 356 Attempts