Fundamentals

Question 1

Which two statements are true regarding working with dates?

Accepted Answer

The default internal storage of dates is in the numeric format

Accepted Answer

The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required

Answer

The default internal storage of dates is in the character format

Answer

The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century

Question 2

promo_begin_date is stored in the default date format. Produce a report that provides name, cost, and start date of all promos in the post category that were launched before jan 1, 2000.

Accepted Answer

select promo_name, promo_cost, promo_begin_date
from promotions
where promo_category like '%post%' and promo_begin_date <'1-JAN-00';

Answer

select promo_name, promo_cost, promo_begin_date
from promotions
where promo_category='post' and promo_begin_date <'01-01-00';

Answer

select promo_name, promo_cost, promo_begin_date
from promotions
where promo_cost like 'post%' and promo_begin_date <'01-01-2000';

Answer

select promo_name, promo_cost, promo_begin_date
from promotions
where promo_category like 'p%' and promo_begin_date <'1-JANUARY-2000';

Question 3

Employees:
Employee_id number(4)
Last_name varchar2 (25)
Job_ID varchar2 (10)

Search for strings 'SA_' in job_ID.

Accepted Answer

select employee_id, last_name, job_id from employees where job_id like '%SA\_'ESCAPE'\';

Answer

select employee_id, last_name, job_id from employees where job_id like '%SA_';

Answer

select employee_id, last_name, job_id from employees where job_id like '%SA_'ESCAPE"\";

Answer

select employee_id, last_name, job_id from employees where job_id ='%SA_';

Question 4

Which two are valid?

Accepted Answer

select promo_id, decode(nvl(promo_cost, 0), promo_cost, promo_cost * 0.25, 100) "Discount"
from promotions;

Accepted Answer

select promo_id, decode(promo_cost, 10000, decode(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost"
from promotions;

Answer

select promo_id, decode(nullif(promo_cost, 10000), null, promo_cost *.25,'N/A') "Catcost"
from promotions;

Answer

select promo_id, decode(promo_cost, >10000, 'High',<10000, 'Low') "Range"
from promotions;

Question 5

Which two are true regarding dates?

Accepted Answer

Default internal storage of dates is in the numeric format

Accepted Answer

The RR date format automatically calculates the century from sysdate function but allows the user to enter century if required

Answer

Default internal storage of dates is in the character format

Answer

The RR date format automatically calculates the century from sysdate function and does not allow the user to enter century if required

Question 6

Which two are true regarding using and on clauses in table joins?

Accepted Answer

On clause can be used to join tables on columns that have different names but compatible data types

Accepted Answer

Where clause can be used to apply additional conditions in the select statement containing the on or the using clause

Answer

Maximum one pair of columns can be joined between two tables using the on clause

Answer

Both using and on clause can be used for equijoins and nonequijoins

Question 7

In which two cases would one use an outer join?

Accepted Answer

Columns being joined have null values

Accepted Answer

Tables being joined have both matched and unmatched data

Answer

Tables being joined have not null columns

Answer

Tables being joined have only matched data

Answer

Tables being joined have only unmatched data

Question 8

Which two are true regarding constraints?

Accepted Answer

Column with unique constraint can contain null values

Accepted Answer

Constraint can be disabled even if constraint column contains data

Answer

FK cannot contain null values

Answer

Constraint is enforced only for insert operation on table

Question 9

select lpad (salary, 10, '*')
from emp
where emp_id=1001;

If employee 1001 has a salary of 17000, what is the result?

Accepted Answer

**17000.00

Answer

17000.00

Answer

17000*****

Answer

****170.00

Answer

error

Question 10

Create synonym emp for hr.employees;

What happens?

Accepted Answer

Alternative name for employees table in HR schema in your schema is created

Answer

Error

Answer

Two identical tables in HR schema with different names

Answer

A table called employees in HR schema based on EMP table is created

Question 11

Display date 13-Oct-2008 in words: 'Thirteenth of October, Two Thousand Eight.'

Accepted Answer

select to_char(to_date('13-oct-2008'), 'fmDdthsp "of" Month, Year') from dual;

Answer

select to_char('13-oct-2008', 'fmDdspth "of" Month, Year') from dual;

Answer

select to_char(to_date('13-oct-2008'), 'fmDdspth of month, year') from dual;

Answer

select to_date(to_char('13-oct-2008', 'fmDdspth "of" Month, Year')) from dual

Question 12

Name three tasks that can be performed using SQL functions built into oracle?

Accepted Answer

Displaying date in non-default format

Accepted Answer

Finding number of characters in an expression

Accepted Answer

Substituting character string in a test expression with a specified string

Answer

Combining more than two columns/expressions into single column in the output

Question 13

Which union operator statement is true?

Accepted Answer

Number of columns selected in all select statements need to be the same

Answer

All column names must be identical across all select statements

Answer

Null values are not ignored during duplicate checking

Answer

Output is not sorted by default

Question 14

The following fails:

select e.emp_name, d.dept_name
from employees e
join departments d
using (department_id)
where d.department_id not in (10,40)
order by dept_name;

Find fix.

Accepted Answer

Remove table alias prefix from where clause

Answer

Remove table alias from select clause

Answer

Remove order by clause

Answer

Prefix the column in the using clause with the table alias

Answer

Prefix the column in the order by clause with table alias

Question 15

Select two true statements regarding where and having clauses in select statements

Accepted Answer

Having clause can be used with aggregate functions in subqueries

Accepted Answer

Where clause can be used to exclude rows before dividing them into groups

Answer

Where clause can be used to exclude rows after dividing them into groups

Answer

Aggregate functions and columns used in the having clause must be specified in the select list of the query

Question 16

A sequence called EMP_ID_SEQ is created in order to populate values for the employee_id column of the employees table. Select two true statements.

Accepted Answer

EMP_ID_SEQ not affected by modifications to employees table

Accepted Answer

Any other column of number type in your schema can use EMP_ID_SEQ

Answer

Cannot use the EMP_ID_SEQ to populate job_id column

Answer

EMP_ID_SEQ is invalidated when employee_id is modified

Answer

EMP_ID_SEQ is dropped when employees table is dropped

Question 17

Select three true statements regarding views.

Accepted Answer

Views can be created from tables and other views

Accepted Answer

Both simple/complex views can use indexes existing on underlying tables

Accepted Answer

Complex views can be created on multiple tables that exist in the same/different schemas

Answer

Views can be created only from tables

Answer

Only simple views can use indexes existing on underlying tables

Answer

Complex views can only be created on multiple tables that exist in the same schema

Question 18

Select three true statements regarding sub-queries

Accepted Answer

Main/Sub query can get data from different tables

Accepted Answer

Sub queries can contain group by and order by clauses

Accepted Answer

Multiple columns/expressions can be compared between main/sub query

Answer

Sub-queries can contain order by but not group by clause

Answer

Main/Sub query must get data from same tables

Answer

Only one column/expression can be compared between main/sub query

Question 19

Select two correct statements that can be executed on a Job_detail table

Accepted Answer

One cannot add a new column to the table with long as the data type

Accepted Answer

Job_image column cannot be included in the order by clause (because it can be null)

Answer

Job_image column can be included in the group by clause

Answer

One can alter the table to include not null constraint on the job_image column

Question 20

Select two true statements regarding single row functions

Accepted Answer

TRUNC: can be used with number and date values

Accepted Answer

SYSDATE: returns database server current date/time

Answer

MOD: returns quotient of division

Answer

CONCAT: can be used with number/date values

Answer

INSTR: can be used to find only the first occurrence of character in a string

Answer

TRIM: can be used to remove all the occurrences of a character from a string

Question 21

Display the first names of all customers from the customers table that contain 'e' and have 'a' in the second last position. Select the correct answer.

Accepted Answer

select cust_first_name
from customers
where instr(cust_first_name, 'e')<>0 and
substr(cust_first_name, -2, 1)='a'

Answer

select cust_first_name
from customers
where instr(cust_first_name, 'e')<>" and
substr(cust_first_name, -2, 1)='a'

Answer

select cust_first_name
from customers
where instr(cust_first_name, 'e') is not null and
substr(cust_first_name, 1, -2)='a'

Answer

select cust_first_name
from customers
where instr(cust_first_name, 'e')<>0 and
substr(cust_first_name, length(cust_first_name), -2)='a'

Question 22

Which statement is true regarding the outcome of the following query?
Select p.prod_id, prod_name, prod_list_price, quantity_sold, cust_last_name
From products p Natural Join sales s Natural Join customers c
Where prod_id=148;

Accepted Answer

It produces an error because a column used in the Natural Join cannot have a qualifier

Answer

It produces an error because the Natural Join can be used only with two tables

Answer

It produces an error because all columns used in the Natural Join should have a qualifier

Answer

It executes successfully

Question 23

Which is a valid Create Table statement?

Accepted Answer

Create Table emp9$# (emp_no NUMBER(4));

Answer

Create Table 9emp$# (emp_no NUMBER(4));

Answer

Create Table emp*123 (emp_no NUMBER(4));

Answer

Create Table emp9$# (emp_no NUMBER(4), date DATE);

Fundamentals

Questions in this quiz (23)

Information

Feedback

What do you think of the new look?