General Oracle SQL 9i MCQs

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?

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

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?

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?

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?

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?

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

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

9. An outer join is used when:

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

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

12. Which of the following statements is a complete transaction?

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

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

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?

16. Which of the following is an iSQL*Plus command?

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?

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?

19. What will happen if you query the emp table shown below: selectempno,DISTINCTename,Salary from emp;

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

21. Which operator will be evaluated first in the statement: select (2+3*4/2-8) from dual:

22. Which of the following SQL statements returns a numeric value?

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

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

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?

26. What does the TRUNCATE statement do?

27. Top N analysis requires _____ and _____.

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

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'?

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

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

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

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

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

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

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

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

38. _______ operator can be used with a multiple row subquery.

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?

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

41. 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?

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

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?