Oracle SQL 9i MCQs (FREE Multiple Choice Questions)

1: The STUDENT_GRADES table has these columns:

STUDENT_ID		NUMBER (12)
SEMESTER_END		DATE
GPA		NUMBER (4, 3)

Which of the following statements finds the highest Grade Point Average (GPA) per semester?

A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;

D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;

E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

2: Which type of join will you write to perform an outer join of tables A and B that returns all rows from B-:

A. Any outer join

B. A left outer join

C. A cross join

D. A right outer join

E. An inner join

3: You want to list names of the employees who have been with the company for more than five years. Which of the following SQL statements will display the required results?

A. SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE>5

B. SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5

C. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5

D. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5

4: You have to calculate the value 12* salary* commission_pct for all the employees in the EMP table. Which of the following statements ensures that a value is displayed in the calculated column for all the employees?

A. SELECT last_name, 12 * salary* commission_pct FROM emp;

B. SELECT last_name, 12 * salary* (commission_pct,0) FROM emp;

C. SELECT last_name, 12 * salary* (nvl(commission_pct,0) )FROM emp;

D. SELECT last_name, 12 * salary* (decode(commission_pct,0)) FROM emp;

5: You want to display the titles of books that meet the following criteria:

1. Purchased before Feb 21, 2002
2. Price is less than $500 or greater than $900

You want to sort the result by the date of purchase, starting with the most recently bought book.

Which of the following statements should you use?

A. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < '21-FEB-2002' ORDER BY purchase_date;

B. SELECT book_title FROM books WHERE price IN (500, 900) AND purchase_date < '21-FEB-2002' ORDER BY purchase date ASC;

C. SELECT book_title FROM books WHERE price < 500 OR > 900 AND purchase_date DESC;

D. SELECT Book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < '21-FEB-2002' ORDER BY purchase_date DESC;

6: You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty.
Which of the following statements will accomplish this task?

A. ALTER TABLE students ADD PRIMARY KEY student_id;

B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);

C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;

D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

7: Which of the following data types stores data outside the Oracle database?

A. UROWID

B. BFILE

C. BLOB

D. NCLOB

E. EXTERNAL

8:

Which of the following constitute the correct guidelines for naming database tables?

A. Must begin with either a number or letter

B. Must be 1-30 characters long

C. Should not be an Oracle Server reserved word

D. Must contain only A-Z, a-z, 0-9, _,*, and #

E. Must contain only A-Z, a-z, 0-9, _, $, and #

F. Must begin with a letter

9:

An outer join is used when:

A. The tables being joined have NOT NULL columns

B. The tables being joined have only matched data.

C. The columns being joined have NULL values

D. The tables being joined have only unmatched data

E. The tables being joined have both matched and unmatched data

10:

Which of the following components are required to run iSQL*plus on PC?

A. SQL*PLUS installed on the PC

B. HTTP Server

C. Oracle net on PC

D. iSQL*PLUS Server

11:

Which of the following constitute the attributes of /SQL*Plus?

A. /SQL*Plus commands cannot be abbreviated

B. /SQL*Plus commands are accessed from a browser

C. /SQL*Plus commands are used to manipulate data in tables

D. /SQL*Plus commands manipulate table definitions in the database

12:

Which of the following statements is a complete transaction?

A. DELETE employees;

B. DESCRIBE employees;

C. ROLLBACK TO SAVEPOINT C;

D. GRANT SELECT ON employees TO SCOTT;

E. ALTER TABLE employees SET UNUSED COLUMN sal;

13:

What kinds of commands can you enter at the command prompt in 9i Sql Plus?

A. PL/SQL Blocks

B. SQL*Plus commands

C. Security commands

D. SQL commands

14:

Which of the following statements are correct with regard to WHERE and HAVING clauses?

A. A WHERE clause can be used to restrict both rows and groups

B. A WHERE clause can be used to restrict rows only

C. A HAVING clause can be used to restrict both rows and groups

D. A HAVING clause can be used to restrict groups only

E. A HAVING clause CANNOT be used in Subqueries

15:

Evaluate the following SQL statement:

SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id;

What will happen if all the parentheses are removed from the calculation?

A. The value displayed in the CALC_VALUE column will be lower

B. The value displayed in the CALC_VALUE column will be higher

C. There will be no difference in the value displayed in the CALC_VALUE column

D. An error will be reported

16:

Which of the following is an iSQL*Plus command?

A. INSERT

B. UPDATE

C. SELECT

D. DESCRIBE

E. DELETE

17:

Examine the code given below:

SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000

Which of the following statement is correct with regard to this code?

A. It returns employees who have 50% of the salary greater than $23,000:

B. It returns employees who have 50% commission rate or salary greater than $23,000:

C. It returns employees who have 50% of salary less than $23,000:

D. None of the above

18:

Examine the two SQL statements given below:

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC;

SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;

What is true about them?

A. The two statements produce identical results

B. The second statement returns a syntax error while first will be executed successfully

C. The second statement will return top 2 records having maximum salary, while first statement will return all records

D. There is no need to specify DESC because the results are sorted in descending order by default

19:

What will happen if you query the emp table shown below:

selectempno,DISTINCTename,Salary from emp;

A. EMPNO ,unique value of ENAME and then SALARY are Displayed.

B. EMPNO ,unique value of the two columns, ENAME and salary are displayed.

C. DISTINCT is not a valid keyword in SQL.

D. No values will be displayed because the statement will return an error

20:

How many join conditions should be there to avoid a Cartesion Join for joining three tables?

A. 1

B. 2

C. 3

D. None of the above

21:

Which operator will be evaluated first in the statement:

select (2+3*4/2-8) from dual:

A. +

B. -

C. /

D. *

22:

Which of the following SQL statements returns a numeric value?

A. SELECT ADD_MONTHS(MAX (hire_date), 6) FROM EMP;

B. SELECT ROUND(hire_date)FROM EMP;

C. SELECT sysdate-hire_date FROM EMP;

D. SELECT TO_NUMBER(hire_date + 7)FROM EMP;

23:

Which of the following substitution variables should be used to reuse the variable value without prompting the user each time?

A. &

B. ACCEPT

C. PROMPT

D. &&

24:

Which of the following SELECT statements will get the result 'elloworld' from the string 'HelloWorld'?

A. SELECT SUBSTR ('HelloWorld',1) FROM dual;

B. SELECT INITCAP(TRIM('HellowWorld', 1,1) FROM dual;

C. SELECT LOWER (SUBSTR ('HellowWorld', 2,1) FROM dual;

D. SELECT LOWER (TRIM ('H' FROM 'HelloWorld')) FROM dual;

25:

Which of the following views should a user query to display the columns associated with the constraints on a table owned by the user?

A. USER_CONSTRAINTS

B. USER_OBJECTS

C. ALL_CONSTRAINTS

D. USER_CONS_COLUMNS

E. USER_COLUMNS

26:

What does the TRUNCATE statement do?

A. Removes the table

B. Removes all rows from a table

C. Shortens the table to 10 rows

D. Removes all columns from a table

27:

Top N analysis requires _____ and _____.

A. The use of rowed &Only an inline view

B. a GROUP BY clause & Only an inline view

C. an ORDER BY clause & An inline view and an outer query

D. None of the above

28:

Which data dictionary view holds information about the column in a view?

A. USER_VIEWS

B. USER_VIEW_COLUMNS

C. USER_TAB_COLUMNS

D. USER_ALL_COLUMNS

29:

Which of the following SELECT statements should be used to extract the year from the system date to display it in the format "2001"?

A. SELECT TO_CHAR(SYSDATE, 'yyyy') FROM dual;

B. SELECT TO_DATE(SYSDATE, 'yyyy') FROM dual;

C. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;

D. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;

E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;

30:

Which of the following SQL statements accepts user input for the columns to be displayed, table name, and the WHERE condition?

A. SELECT &1, "&2"FROM &3 WHERE last_name = '&4';

B. SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4'';

C. SELECT &1, &2 FROM &3 WHERE last_name = '&4';

D. SELECT &1, '&2' FROM EMP WHERE last_name = '&4';

31:

Where is the GROUP BY clause statement placed in a SELECT statement that includes a WHERE clause?

A. Immediately after the SELECT clause

B. Before the WHERE clause

C. After the ORDER BY clause

D. After the WHERE clause

32:

Which of the following statements are correct with regard to NULL values?

A. Only =and !=operator can be used to search for NULL values in a column

B. In an ascending order sort, NULL values appear at the bottom of the result set

C. Both a and b

D. Neither a nor b

33:

Which of the following operations cannot be performed using the ALTER TABLE statement?

A. Rename table

B. Rename column

C. Drop column

D. Drop NOT NULL Constraint

34:

Which of the following constraints can be defined only at the column level?

A. UNIQUE

B. NOT NULL

C. CHECK

D. PRIMARY KEY

E. FOREIGN KEY

35:

Which of the following SQL statements defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?

A. CREATE TABLE EMP (empnoNUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) NOT NULL, CONSTRAINT emp_deptno_fk FOREIGN KEY deptno REFERENCES deptdeptno);

B. CREATE TABLE EMP (empnoNUMBER(4), ename VARCHAR2(35), deptno NUMBER(7,2) CONSTRAINT emp_deptno_fk REFERENCES dept (deptno));

C. CRETE TABLE EM (empnoNUMBER(4), ename VARCHAR2(35) deptno NUMBER (7,2) NOT NULL, CONSTRAINT em_deptno_fk REFERENCES dept (deptno) FOREIGN KEY (deptno));

D. CREATE TABLE EMP (empno NUMBER (4), enameVARCHAR2(35), deptno NUMBER(7,2) FOREIGN KEY CONSTRAINT empdeptnofk REFERENCES dept (deptno));

36:

Which of the following shows the correct use of the Trunc command on a date?

A. TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual;

B. Select TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual;

C. Date =TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;

D. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

37:

Which of the following SQL statements should be used to remove a view called EMP_DEPT_VU from the schema?

A. DROP emp_dept_vu;

B. DELETE emp_dept_vu;

C. REMOVE emp_dept_vu;

D. DROP VIEW emp_dept_vu;

E. DELETE VIEW emp_dept_vu;

F. REMOVE VIEW emp_dept_vu;

38:

_______ operator can be used with a multiple row subquery.

A. =

B. LIKE

C. BETWEEN

D. NOT IN

E. Is

39:

Evaluate the SQL statement given below:

SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual;

Which of the following values are displayed?

A. 46 and 45.93

B. 50 and 45.93

C. 50 and 45.9

D. 45 and 45.93

E. 45.95 and 45.93

40:

Which of the following tasks can be performed by using the TO_CHAR function?

A. Convert '10'to 10

B. Convert 10 to '10'

C. Convert 'TEN' to 10

D. Convert a date to a character expression

E. Convert a character expression to a date

41:

Examine the structure of the STUDENTS table given below:

STUDENT_ID	NUMBER	NOT NULL, Primary Key
STUDENT_NAME	VARCHAR2 (30)
COURSE_ID	VARCHAR2 (10)	NOT NULL
MARKS	NUMBER
START_DATE	DATE
FINISH_DATE	DATE

You need to create a report of ten students who achieved the highest ranking in the course INT_SQL and completed the course in the year 1999.
Which of the following SQL statements accomplishes this task?

A. SELECT student_id, marks, ROWNUM "Rank" FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL'ORDER BY marks DESC;

B. SELECT student_id, marks, ROWID "Rank" FROM students WHERE ROWID <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'AND course_id = 'INT_SQL'ORDER BY marks;

C. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC);

D. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students WHERE finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL' ORDER BY marks DESC)WHERE ROWNUM <= 10 ;

E. SELECT student_id, marks, ROWNUM "Rank" FROM (SELECT student_id, marks FROM students ORDER BY marks) WHERE ROWNUM <= 10 AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99' AND course_id = 'INT_SQL';

42:

Which component is a literal in the following select statement?
Select 'Emp name :'||ename from emp where deptno=20;

A. ename

B. 20

C. Emp name:

D. ||

43:

Examine the data in the EMPLOYEES table given below:

LAST_NAME	DEPARTMENT_ID	SALARY
ALLEN	10	3000
MILLER	20	1500
King	20	2200
Davis	30	5000

Which of the following Subqueries work?

A. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);

B. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

C. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

D. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

E. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));

Oracle SQL 9i MCQs

Oracle SQL 9i MCQs

List of Oracle SQL 9i MCQs Mul...

Related Oracle SQL 9i MCQs:

Available in:

Latest MCQs:

Popular MCQs: