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).
