1. Find out the
ODD statement ?
a.
Select
* from emp where deptno = 20;
b.
Select
* from emp where deptno in (20);
c.
Select
* from emp where deptno like ‘20’;
d.
Select
* from emp where deptno contains (20);
2.
Which two clauses of the SELECT statement facilitate selection and
projection?
a. Select, From
b. Order By, Where
c. Select, Where
d. Select, Order By
3.
Choose the query that extracts the LAST_NAME, JOB_ID, and SALARY
values from the
EMPLOYEES table for records having JOB_ID values of either SA_REP or
MK_MAN and
having SALARY values in the range of $1000 to $4000.
The
SELECT and FROM clauses are
SELECT
LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES:
a. WHERE JOB_ID IN
('SA_REP','MK_MAN')
AND SALARY > 1000 AND
SALARY < 4000;
b. WHERE JOB_ID IN
('SA_REP','MK_MAN')
AND SALARY BETWEEN 1000
AND 4000;
c. WHERE JOB_ID LIKE 'SA_REP%'
AND 'MK_MAN%'
AND SALARY > 1000 AND
SALARY < 4000;
d. WHERE JOB_ID = 'SA_REP'
AND SALARY BETWEEN 1000
AND 4000
OR
JOB_ID='MK_MAN';
4.
Which of the following WHERE clauses contains an error?
The SELECT and
FROM clauses are
SELECT
* FROM EMPLOYEES:
a. WHERE HIRE_DATE IN
('02-JUN-2004');
b. WHERE SALARY IN
('1000','4000','2000');
c. WHERE JOB_ID IN
(SA_REP,MK_MAN);
d.
WHERE
COMMISSION_PCT BETWEEN 0.1 AND 0.5;
5.
Choose the WHERE clause that extracts the DEPARTMENT_NAME values
containing the
character literal "er" from the DEPARTMENTS table.
The
SELECT and FROM clauses are
SELECT
DEPARTMENT_NAME FROM DEPARTMENTS;
a. WHERE DEPARTMENT_NAME IN
('%e%r');
b. WHERE
DEPARTMENT_NAME LIKE '%er%';
c. WHERE WHERE
DEPARTMENT_NAME BETWEEN 'e' AND 'r';
d. WHERE DEPARTMENT_NAME
CONTAINS 'e%r';
6. Which two of
the following conditions are equivalent to each other ?
a.
WHERE
COMMISSION_PCT IS NULL
b.
WHERE
COMMISSION_PCT = NULL
c.
WHERE
COMMISSION_PCT IN (NULL)
d.
WHERE
NOT(COMMISSION_PCT IS NOT NULL)
7. Which three of
the following conditions are equivalent to each other?
a. WHERE SALARY <=5000
AND SALARY >=2000
b. WHERE SALARY IN
(2000,3000,4000,5000)
c. WHERE SALARY BETWEEN
2000 AND 5000
d. WHERE SALARY > 1999
AND SALARY < 5001
e. WHERE SALARY >=2000
AND <=5000
8.
Choose one false statement about the ORDER BY clause.
a. When using the ORDER BY
clause, it always appears as the last clause in a SELECT statement.
b. The ORDER BY clause may
appear in a SELECT statement that does not contain a
WHERE
clause.
c. The ORDER BY clause
specifies one or more terms by which the retrieved rows are sorted. These terms
can only be column names.
d. Positional sorting is
accomplished by specifying the numeric position of a column as it appears in
the SELECT list, in the ORDER BY clause.
9.
The following query retrieves the LAST_NAME, SALARY, and
COMMISSION_PCT values for employees whose LAST_NAME begins with the letter R.
Based on the
following query, choose the ORDER BY clause that first sorts the results by the
COMMISSION_PCT column, listing highest commission earners first, and then sorts
the results in ascending order by the SALARY column.
Any records with NULL COMMISSION_PCT must appear last:
SELECT
LAST_NAME, SALARY, COMMISSION_PCT FROM EMPLOYEES
WHERE
LAST_NAME LIKE 'R%'
a. ORDER BY COMMISSION_PCT
DESC, 2;
b. ORDER BY 3 DESC, 2 ASC
NULLS LAST;
c. ORDER BY 3 DESC NULLS
LAST, 2 ASC;
d. ORDER BY COMMISSION_PCT
DESC, SALARY ASC;
10.
The DEFINE command explicitly
declares a session-persistent substitution variable with a specific value. How
is this variable referenced in an SQL statement?
Consider an
expression that calculates tax on an employee’s SALARY based on the current tax
rate. For the following session-persistent substitution variable, which
statement correctly references the TAX_RATE variable?
DEFINE
TAX_RATE=0.14
a. SELECT SALARY *
:TAX_RATE TAX FROM EMPLOYEES;
b. SELECT SALARY *
&TAX_RATE TAX FROM EMPLOYEES;
c. SELECT SALARY *
:&&TAX TAX FROM EMPLOYEES;
d. SELECT SALARY * TAX_RATE
TAX FROM EMPLOYEES;
11.
When using ampersand
substitution variables in the following query, how many times will you be
prompted to input a value for the variable called JOB the first time this query
is executed?
SELECT
FIRST_NAME, '&JOB' FROM EMPLOYEES
WHERE
JOB_ID LIKE '%'||&JOB||'%' AND '&&JOB' BETWEEN 'A' AND 'Z';
a. 0
b. 1
c. 2
d. 3
View Answers