Oracle Exam Online MCQs Short Question SQL Query and Joins Sample Question
1.
Select ename ||' : '||'1'||
'Monthly sal *12= '||sal Monthly (30)
From EMP;
How the Result will be displayed:
A.
MONTHLY
------------------------------------------------------------------------
SMITH : 1Monthly sal *12= 800
B.
ENAME||':'||'1'||'MONTHLY'||SAL*12 SAL
-------------------------------------------------------------------
----------
SMITH : 1
Monthly 9600
800
C.
ENAME MONTHLY sal Monthly
-------------------------------------------------------------------
----------
SMITH : 1
Monthly 9600
800
D.
Statement will not execute
successfully.
2.
Evaluate these select
statements
i.
Select ename||123,empno from
emp;
ii.
Select ename||'123',empno from
emp;
A.
Statement 1 will not execute
and statement 2 will execute successfully.
B.
Statement 2 will not execute
and statement 1 will execute successfully.
C.
Both statements will not
execute successfully.
D.
Both Statements will execute
but the result will be different.
E.
Both Statements will execute
successfully and result also will be the same.
3.
To_Char(Price, ‘$999990.99’)
If price=0.25
Then the display will be
a) 0.25 b)
$.25 c) $0.25 d)
$000000.25
4.
Which single row function can be used on a
varchar2 column?
a) NVL b)
TRUNC c) ROUND
d) SYSDATE
5.
Which command would cause a
pending transaction to end?
A) ALTER B)
DELETE C) INSERT
D) UPDATE E)
SELECT
6.
1.Create table 1234;
2.Create table A1234;
A) Statement 1 will execute successfully.
B) Statement 2 will execute successfully.
C) Both will not execute.
D)
Both will
execute successfully.
7.
Operators of the same priority
are evaluated from ……………. To…………………
8.
Eliminate Duplicate rows by
using the ………….. Keyword in the ………… clause.
9.
What will be result of these
values?
Select ROUND (99.923,2), ROUND (99.923,0),
ROUND (99.923,-1), TRUNC(99.923,2), TRUNC(99.923,0), TRUNC(99.923,-1) FROUM
DUAL;
10. 1.SelectDistinct from emp;
2.Select job,Distinct deptno, ename from
emp;
A. Statement 1 will execute successfully.
B. Statement 2 will execute successfully.
C. Both will not execute.
D.
Both will
execute successfully.
11. The PERSONNEL table contains these columns:
ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
MANAGER_ID NUMBER(9)
For this example,
department managers are personnel.
Evaluate these two
SQL statements:
SELECT p.last_name, p.first_name, m.last_name,
m.first_name
FROM personnel p, personnel m
WHERE m.id = p.manager_id;
SELECT p.last_name, p.first_name, m.last_name,
m.first_name
FROM personnel p, personnel m
WHERE m.manager_id = p.id;
How do the two SQL
statements differ?
A.
One of the statements will not
execute.
B.
One of the statements is not a
self-join.
C.
The results of the statements
will be the same, but the format will be different.
D.
The results of the statements
will be different; but the display will be the same.
12. Which of the functions only use with numeric data.
A) AVG B)
COUNT C) MAX D) MIN
E) STDDEV F) SUM G)
VARIANCE
13. You query the database with this command:
SELECT manufacturer_id
FROM inventory
WHERE manufacturer_id LIKE '%N\%P\%O%' ESCAPE %;
For which character pattern is the LIKE operator searching?
A). NPO B).
N\P\O C). N%P%O D). N\%P/%O
15. Which SQL statement creates the PARTS_456874_VU view that contains
the ID_NUMBER, DESCRIPTION, and QUANTITY columns for MANUFACTURER_ID 456874
from the INVENTORY table and does not allow the manufacturer values to be
changed through the view?
- CREATE VIEW parts_456874_vu
AS SELECT id_number, description, quantity
FROM inventory
WITH CHECK
CONSTRAINT;
- CREATE VIEW parts_456874_vu
AS SELECT id_number, description, quantity
FROM inventory
HAVING manufacturer_id = 456874
WITH READ ONLY;
- CREATE VIEW parts_456874_vu
AS SELECT id_number, description, quantity
FROM inventory
WHERE manufacturer_id = 456874
WITH READ ONLY;
- CREATE VIEW parts_456874_vu
AS SELECT id_number, description, quantity
FROM inventory
WHERE manufacturer_id = 456874
WITH CHECK OPTION;
16.
Which function would you use to
display date value in “mm/yy” format?
A). To_Char B).
To_Date C). To_Number D). None
17.
The STUDENT table contains
these columns:
ID NUMBER(9) PK
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
SUBJECT_ID NUMBER(9)
Compare these two
SQL statements:
1. SELECT
DISTINCT subject_id, last_name, first_name
FROM student
ORDER BY
1;
2. SELECT
id, last_name, first_name, subject_id
FROM student
ORDER BY
subject_id;
How will the
results differ?
A.
Statement 1 will be sorted
alphabetically; statement 2 will not.
B.
Statement 1 will limit
duplicate subject ids; statement 2 will not.
C.
Statement 1 will not eliminate
duplicate rows from the output; statement 2 will.
D.
Statement 2 will display
distinct combinations of the values in the STUDENT table; statement 1 will not.
18.
You attempt to query the
database with this command:
SELECT i.id_number, m.id_number
FROM inventory i, manufacturer m
WHERE i.manufacturer_id = m.id_number
ORDER BY inventory.description;
Which clause
causes an error?
A.
ORDER BY inventory.description;
B.
FROM inventory i, manufacturer
m
C.
WHERE i.manufacturer_id =
m.id_number
D.
SELECT i.id_number,
m.manufacturer_name
19.
The STUDENT table contains
these columns:
ID NUMBER(9)
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
Evaluate this SQL statement:
SELECT
*
FROM student
WHERE
id =
(SELECT id
FROM student
WHERE UPPER(last_name) = 'SHEHZAD')
AND UPPER(first_name) = 'IRFAN');
What would cause this statement to fail?
- There are no students named IRFAN SHEHZAD.
- There is more than one student named SHEHZAD.
- There is more than one student named IRFAN SHEHZAD.
- The FIRST_NAME and LAST_NAME values in the database are in lowercase.
20.
Evaluate this command:
SELECT id_number, description, SUM(price)
FROM
inventory
WHERE
price > 6.00
GROUP BY
id_number
ORDER BY
manufacturer_id;
Why will this
command cause an error?
A.
The PRICE column must be
included in the GROUP BY clause.
B.
The ORDER BY clause should
immediately follow the WHERE clause.
C.
The MANUFACTURER_ID column is
not included in the SELECT clause.
D.
The ORDER BY clause cannot be
used in a SELECT statement with a GROUP BY clause.
E.
The DESCRIPTION and
MANUFACTURER_ID columns are not included in the GROUP BY clause.
21.
Which of the function include
Null values:
A)
AVG B) COUNT C)
MAX D) MIN E) STDDEV F) SUM G)
VARIANCE
22.
How Column headings are
displayed:
A.
Date left justified
B.
Character left justified
C.
Number right justified
D.
All of the above
E.
None of the above
23.
For which two types of constraints
is an index automatically created?
A) CHECK B) UNIQUE C) NOT
NULL D) FOREIGN KEY E)
PRIMARY KEY
24.
Which characteristic relates to
a single row function?
- Act on each row returned in the query.
- Return one result per row.
- May return a data value of a different type than that referenced.
- May expect one or more argument.
- All of the above.
- None of the above.
25.
Character functions Accept
character input and can return:
A.
Only character data.
B.
Both character and number data.
C.
Only number data.
D.
None of the above.
26.
The values in inventory table
are follows:
ID_Number ORDER_DATE
25025 10-jun-97
25027 11-oct-96
25023 19-apr-97
32096 25-jul-97
25026 11-sep-97
45025 15-sep-97
Delete from
inventory
Where
order_date>to_date(’25.07.1997’,’DD.MM.YYYY’);
which id_number
value would be deleted
A) 32096 B)
25026 C) 32081
D) No value will be deleted
27.
In order to Truncate a table you must
have:
A. Create
Table Privileges.
B. Delete
Table Privileges.
C. Select
Privileges.
D. Both
Create and Delete Privileges.
E. DBA
Privileges.
28.
what is the default length value of a
CHAR column?
A) 1 B) 9 C)
38 D) A column length must be specified
for a CHAR column
29.
What would happen if you created an
index on all six columns in the student table:
A. The
speed of updates would be increased.
B. Queries
with a where clause would be slower.
C. The
deletes operations on the table would be slower.
D. All
the select statements issued on the table would be faster.
E. Non
of the above.
30.
Which two privileges can only be granted
to a user and not to a Role.
A) ALTER B)
INDEX C) DELETE
D) INSERT
E) EXECUTE F)
REFERENCES
(Part II) (10)
With Reference to your ORACLE project What type of queries
you have developed to facilitate user provide some examples. How can you
integrate your modules and use Constraints to implement a RDBMS. Draw a
Prototype Model of your project (not ERD&DFD).
0 comments:
Post a Comment