Most Frequently Asked Oracle Multiple Choice Questions and Answers pdf
1. Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?
2. To write a query that performs an outer join of tables A and B and returns all rows from B, You need to write
3. Which of the following correctly shows the correct use of the TRUNC command on a date?
4. Which of the following has been achieved by the following SQL codes? SELECT employee_id FROM employees WHERE commission_pct = .5 OR salary > 23000;
5. Which of the following has been achieved by the following SQL codes? SELECT * FROM employees WHERE hire_date < TO_DATE ('01-JAN-1999', 'DD-MON-YYYY') AND salary > 3500;
6. Which of the following SQL statements can calculate and return the absolute value of -33?
7. Evaluate this 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 WHERE e.employee_id = s.emp_id;What will happen if you remove all the parentheses from the calculation?
8.The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10) Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?
9.You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?
10. What is true about joining tables through an Equi join?
11. View the image below to examine the structures of the EMPLOYEES and TAX tables. You need to find the percentage tax applicable for each employee. Which SQL statement would you use?
12.Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
13. Which is an iSQL*Plus command?
14.Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?
15.Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?
16. Evaluate this SQL statement:SELECT ename, sal, 12*sal+100 FROM EMP;The SAL column stores the monthly salary of the employee. Which change must be madeto the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?
17.You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table. Which set of statements accomplishes this?
18. Which describes the default behavior when you create a table?
19. Which statement creates a new user?
20. Evaluate these two SQL statements: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?
21. Examine the structure of the EMPLOYEES table:EMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2 (25)LAST_NAME VARCHAR2 (25)HIRE_DATE DATEWhich UPDATE statement is valid?
22. What is true about sequences?
23. Which statement describes the ROWID data type?
24. Which object privileges can be granted on a view?
25. What is true of using group functions on columns that contain NULL values?
26. Examine this statement:SELECT student_id, GPA FROM student_grades WHERE GPA > &&value;You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?
27. Which SQL statement returns a numeric value?
28. What is true about updates through a view?
29. You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. Which statement accomplishes this task?
30. Mark for review Evaluate the SQL statement:SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual;Which values are displayed?
31. Mark for review what does the FORCE option for creating a view do?
32. Mark for review a data manipulation language statement _____.
33. Mark for review The DBA issues this SQL command: CREATE USER Scott IDENTIFIED by tiger; what privileges do the user Scott has at this point?
34. Mark for review View the image below and examine the data in the EMPLOYEES table.Examine the subquery:SELECT last_name FROM employees WHERE salary IN (SELECT MAX (salary) FROMemployees GROUP BY department_id);Which statement is true?
35. Mark for review which best describes an inline view?
36 Mark for review. In which case would you use a FULL OUTER JOIN?
37. Mark for review. Which constraint can be defined only at the column level?
38. Mark for review Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
39. Mark for review. The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?
40. Mark for review. Which view should a user query to display the columns associated with the constraints on a table owned by the user?
41. Mark for review Examine these statements:CREATE ROLE registrar;GRANT UPDATE ON student_grades TO registrar;GRANT registrar to user1, user2, user3;What does this set of SQL statements do?
42. Mark for review. You need to create a view EMP_VU. The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20. Which SQL statement would you use to create the view EMP_VU?
43. Mark for review Examine the statement:Create synonym EMP for hr.employees;What happens when you issue the statement?
44. You need to change the definition of an existing table. The COMMERCIALS table needs its DESCRIPTION column changed to hold varying length characters up to 2000 bytes. The column can currently hold 1000 bytes per value. The table contains 20000 rows. Which statement is valid?
45. Mark for review Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition?
46. Mark for review. Which is a valid CREATE TABLE statement?
47. Mark for review A SELECT statement can be used to perform these three functions: - Choose rows from a table. - Choose columns from a table. - Bring together data that is stored in different tables by creating a link between them. Which set of keywords describes these capabilities?
48. Evaluate this SQL statement:SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed?
49. Mark for review View the image below and examine the data in the EMPLOYEES and DEPARTMENTS tables.You want to retrieve all employees' last names, along with their managers' last names and their department names. Which query would you use?
50. What is necessary for your query on an existing view to execute successfully?
51. Which SELECT statement will get the result 'elloworld' from the string 'HelloWorld'?
52. Management has asked you to calculate the value 12* salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns:LAST NAME VARCHAR2 (35) NOT NULLSALARY NUMBER (9, 2) NOT NULLCOMMISSION_PCT NUMBER (4, 2)Which statement ensures that a value is displayed in the calculated column for all employees?
53. The EMPLOYEES table has these columns:LAST_NAME VARCHAR2 (35)SALARY NUMBER (8, 2)COMMISSION_PCT NUMBER (5, 2)You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?
54. Evaluate the SQL statement:SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX (sal) maxsal 4 FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal<b.maxsal;What is the result of the statement?
55. Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott on this view. Which option enables Scott to eliminate the need to qualify the view with the name MARY.EMP_DEPT_LOC_VU each time the view is referenced?
56. Which SQL statement defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?
57. Evaluate the set of SQL statements:CREATE TABLE dept (dept_id NUMBER (2) dname VARCHAR2 (14), Loc VARCHAR2 (13));ROLLBACK;DESCRIBE DEPTWhat is true about the set?
58. Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
59. In which scenario would an index be most useful?
60. Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?
61. You want to display the titles of books that meet these criteria:1. Purchased before January 21, 20012. Price is less than $ 500 or greater than $ 900You want to sort the result by their date of purchase, starting with the most recently bought book. Which statement should you use?
62. Which operator can be used with a multiple row subquery?
63. You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was created). How do you obtain the definition of the view?
64. Which statement explicitly names a constraint?
65. You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results?
66.You are granted the CREATE VIEW privilege. What does this allow you to do?
67. What is true about the WITH GRANT OPTION clause?
68. In which scenario would Top N analysis be the best solution?
69. What does the TRUNCATE statement do?
70. A subquery can be used to _________.
71. Which clause should you use to exclude group results?
72. Scott issues the SQL statements:CREATE TABLE dept (deptno number (2) dname VARCHAR2 (14) loc VARCHAR2 (13));GRANT SELECT ON DEPT TO SUE;If Sue needs to select from Scott's DEPT table, which command should she use?
73. You need to calculate the total of all salaries in the accounting department. Which group function should you use?
74. You would like to display the system date in the format *Monday, 01 June, 2001* Which SELECT statement should you use?
75. You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator"=" What happens when the main query is executed?
76. You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table some of the employees may not have a phone number available. Which data manipulation operation do you perform?
77. in a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?
78. Which / SQL* Plus feature can be used to replace values in the where clause?
79. Evaluate the SQL statement:SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual;What will be displayed?
80. Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
81. Which syntax turns an existing constraint on?
82. the database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query?SELECT * FROM HR;
83. You need to give the MANAGER role the ability to select from insert into and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this?
84. Which two statements about subqueries are true? (Choose two.)
85. Which two statements about views are true? (Choose two)
86. Which three are DATETIME data types that can be used when specifying column definitions? (Choose three)
87. For which two constrains does the Oracle Server implicitly create a unique index?(Choose two)
88. Which two statements accurately describe a role? (Choose two)
89. Which two are character manipulation functions? (Choose two)
90. Which three statements correctly describe the functions and use of constraints? (Choose three)
91. Which two statements about creating constraints are true? (Choose two)
92. Which two statements about sequences are true? (Choose two)
93. Which four are correct guidelines for naming database tables? (Choose four)
94. When should you create a role? (Choose two)
95. Which three statements about Subqueries are true? (Choose three).
96. Which two are attributes of /SQL*Plus? (Choose two)
97. Which two statements is true regarding the ORDER BY clause? (Choose two)
98. Which four statements correctly describe functions that are available in SQL? (Choose four)
99. From SQL*Plus, you issue this SELECT statement:SELECT * FROM orders;You use this statement to retrieve data from a database table for ____________.
100. Mark for review. Which two are true about aggregate functions? (Choose two.)
1. Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?
2. To write a query that performs an outer join of tables A and B and returns all rows from B, You need to write
3. Which of the following correctly shows the correct use of the TRUNC command on a date?
4. Which of the following has been achieved by the following SQL codes? SELECT employee_id FROM employees WHERE commission_pct = .5 OR salary > 23000;
5. Which of the following has been achieved by the following SQL codes? SELECT * FROM employees WHERE hire_date < TO_DATE ('01-JAN-1999', 'DD-MON-YYYY') AND salary > 3500;
6. Which of the following SQL statements can calculate and return the absolute value of -33?
7. Evaluate this 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 WHERE e.employee_id = s.emp_id;What will happen if you remove all the parentheses from the calculation?
8.The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) ENAME VARCHAR2 (25) JOB_ID VARCHAR2(10) Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?
9.You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?
10. What is true about joining tables through an Equi join?
11. View the image below to examine the structures of the EMPLOYEES and TAX tables. You need to find the percentage tax applicable for each employee. Which SQL statement would you use?
12.Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?
13. Which is an iSQL*Plus command?
14.Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?
15.Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?
16. Evaluate this SQL statement:SELECT ename, sal, 12*sal+100 FROM EMP;The SAL column stores the monthly salary of the employee. Which change must be madeto the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?
17.You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table. Which set of statements accomplishes this?
18. Which describes the default behavior when you create a table?
19. Which statement creates a new user?
20. Evaluate these two SQL statements: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?
21. Examine the structure of the EMPLOYEES table:EMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2 (25)LAST_NAME VARCHAR2 (25)HIRE_DATE DATEWhich UPDATE statement is valid?
22. What is true about sequences?
23. Which statement describes the ROWID data type?
24. Which object privileges can be granted on a view?
25. What is true of using group functions on columns that contain NULL values?
26. Examine this statement:SELECT student_id, GPA FROM student_grades WHERE GPA > &&value;You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?
27. Which SQL statement returns a numeric value?
28. What is true about updates through a view?
29. You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. Which statement accomplishes this task?
30. Mark for review Evaluate the SQL statement:SELECT ROUND (45.953, -1), TRUNC (45.936, 2) FROM dual;Which values are displayed?
31. Mark for review what does the FORCE option for creating a view do?
32. Mark for review a data manipulation language statement _____.
33. Mark for review The DBA issues this SQL command: CREATE USER Scott IDENTIFIED by tiger; what privileges do the user Scott has at this point?
34. Mark for review View the image below and examine the data in the EMPLOYEES table.Examine the subquery:SELECT last_name FROM employees WHERE salary IN (SELECT MAX (salary) FROMemployees GROUP BY department_id);Which statement is true?
35. Mark for review which best describes an inline view?
36 Mark for review. In which case would you use a FULL OUTER JOIN?
37. Mark for review. Which constraint can be defined only at the column level?
38. Mark for review Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
39. Mark for review. The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?
40. Mark for review. Which view should a user query to display the columns associated with the constraints on a table owned by the user?
41. Mark for review Examine these statements:CREATE ROLE registrar;GRANT UPDATE ON student_grades TO registrar;GRANT registrar to user1, user2, user3;What does this set of SQL statements do?
42. Mark for review. You need to create a view EMP_VU. The view should allow the users to manipulate the records of only the employees that are working for departments 10 or 20. Which SQL statement would you use to create the view EMP_VU?
43. Mark for review Examine the statement:Create synonym EMP for hr.employees;What happens when you issue the statement?
44. You need to change the definition of an existing table. The COMMERCIALS table needs its DESCRIPTION column changed to hold varying length characters up to 2000 bytes. The column can currently hold 1000 bytes per value. The table contains 20000 rows. Which statement is valid?
45. Mark for review Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition?
46. Mark for review. Which is a valid CREATE TABLE statement?
47. Mark for review A SELECT statement can be used to perform these three functions: - Choose rows from a table. - Choose columns from a table. - Bring together data that is stored in different tables by creating a link between them. Which set of keywords describes these capabilities?
48. Evaluate this SQL statement:SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed?
49. Mark for review View the image below and examine the data in the EMPLOYEES and DEPARTMENTS tables.You want to retrieve all employees' last names, along with their managers' last names and their department names. Which query would you use?
50. What is necessary for your query on an existing view to execute successfully?
51. Which SELECT statement will get the result 'elloworld' from the string 'HelloWorld'?
52. Management has asked you to calculate the value 12* salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns:LAST NAME VARCHAR2 (35) NOT NULLSALARY NUMBER (9, 2) NOT NULLCOMMISSION_PCT NUMBER (4, 2)Which statement ensures that a value is displayed in the calculated column for all employees?
53. The EMPLOYEES table has these columns:LAST_NAME VARCHAR2 (35)SALARY NUMBER (8, 2)COMMISSION_PCT NUMBER (5, 2)You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?
54. Evaluate the SQL statement:SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX (sal) maxsal 4 FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal<b.maxsal;What is the result of the statement?
55. Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She granted SELECT privilege to Scott on this view. Which option enables Scott to eliminate the need to qualify the view with the name MARY.EMP_DEPT_LOC_VU each time the view is referenced?
56. Which SQL statement defines a FOREIGN KEY constraint on the DEPT NO column of the EMP table?
57. Evaluate the set of SQL statements:CREATE TABLE dept (dept_id NUMBER (2) dname VARCHAR2 (14), Loc VARCHAR2 (13));ROLLBACK;DESCRIBE DEPTWhat is true about the set?
58. Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
59. In which scenario would an index be most useful?
60. Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?
61. You want to display the titles of books that meet these criteria:1. Purchased before January 21, 20012. Price is less than $ 500 or greater than $ 900You want to sort the result by their date of purchase, starting with the most recently bought book. Which statement should you use?
62. Which operator can be used with a multiple row subquery?
63. You need to perform certain data manipulation operations through a view called EMP_DEPT_VU, which you previously created. You want to look at the definition of the view (the SELECT statement on which the view was created). How do you obtain the definition of the view?
64. Which statement explicitly names a constraint?
65. You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results?
66.You are granted the CREATE VIEW privilege. What does this allow you to do?
67. What is true about the WITH GRANT OPTION clause?
68. In which scenario would Top N analysis be the best solution?
69. What does the TRUNCATE statement do?
70. A subquery can be used to _________.
71. Which clause should you use to exclude group results?
72. Scott issues the SQL statements:CREATE TABLE dept (deptno number (2) dname VARCHAR2 (14) loc VARCHAR2 (13));GRANT SELECT ON DEPT TO SUE;If Sue needs to select from Scott's DEPT table, which command should she use?
73. You need to calculate the total of all salaries in the accounting department. Which group function should you use?
74. You would like to display the system date in the format *Monday, 01 June, 2001* Which SELECT statement should you use?
75. You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator"=" What happens when the main query is executed?
76. You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table some of the employees may not have a phone number available. Which data manipulation operation do you perform?
77. in a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?
78. Which / SQL* Plus feature can be used to replace values in the where clause?
79. Evaluate the SQL statement:SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual;What will be displayed?
80. Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
81. Which syntax turns an existing constraint on?
82. the database administrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequently use this table. As a user of the database, you created a table called HR in your schema. What happens when you execute this query?SELECT * FROM HR;
83. You need to give the MANAGER role the ability to select from insert into and modify existing rows in the STUDENT_GRADES table. Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this?
84. Which two statements about subqueries are true? (Choose two.)
85. Which two statements about views are true? (Choose two)
86. Which three are DATETIME data types that can be used when specifying column definitions? (Choose three)
87. For which two constrains does the Oracle Server implicitly create a unique index?(Choose two)
88. Which two statements accurately describe a role? (Choose two)
89. Which two are character manipulation functions? (Choose two)
90. Which three statements correctly describe the functions and use of constraints? (Choose three)
91. Which two statements about creating constraints are true? (Choose two)
92. Which two statements about sequences are true? (Choose two)
93. Which four are correct guidelines for naming database tables? (Choose four)
94. When should you create a role? (Choose two)
95. Which three statements about Subqueries are true? (Choose three).
96. Which two are attributes of /SQL*Plus? (Choose two)
97. Which two statements is true regarding the ORDER BY clause? (Choose two)
98. Which four statements correctly describe functions that are available in SQL? (Choose four)
99. From SQL*Plus, you issue this SELECT statement:SELECT * FROM orders;You use this statement to retrieve data from a database table for ____________.
100. Mark for review. Which two are true about aggregate functions? (Choose two.)
No comments:
Post a Comment