Multiple Choice Quiz Questions Of Oracle
- 1.
Which two statements are true about rebuilding an index? (Choose two.)
- A.
The resulting index may contain deleted entries.
- B.
A new index is built using an existing index as the data source.
- C.
Queries cannot use the existing index while the new index is being built.
- D.
During a rebuild, sufficient space is needed to accommodate both the old and the new index in their respective tablespaces.
Correct Answer(s)
B. A new index is built using an existing index as the data source.
D. During a rebuild, sufficient space is needed to accommodate both the old and the new index in their respective tablespaces.Explanation
During the rebuilding of an index, a new index is created using an existing index as the data source. This means that the existing index is used as a reference to build the new index. Additionally, during the rebuild process, enough space is required to accommodate both the old and the new index in their respective tablespaces. This is because the old index needs to be kept until the new index is fully built and ready to replace it.Rate this question:
-
- 2.
Consider this SQL statement: UPDATE employees SET first_name = 'John' WHERE emp_id = 1009; COMMIT; What happens when a user issues the COMMIT in the above SQL statement?
- A.
Dirty buffers in the database buffer cache are flushed.
- B.
The server process places the commit record in the redo log buffer.
- C.
Log Writer (LGWR) writes the redo log buffer entries to the redo log files and data files.
- D.
The user process notifies the server process that the transaction is complete
- E.
The user process notifies the server process that the resource locks can be released.
Correct Answer
E. The user process notifies the server process that the resource locks can be released.Explanation
When the user issues the COMMIT statement, the user process notifies the server process that the resource locks can be released. This means that any locks held by the user process on the affected data are released, allowing other processes to access and modify the data. This ensures data consistency and concurrency in a multi-user environment.Rate this question:
-
- 3.
Which type of file is part of the Oracle database?
- A.
Control file
- B.
password file
- C.
Parameter files
- D.
Archived log files
Correct Answer
A. Control fileExplanation
The control file is a type of file that is part of the Oracle database. It is a binary file that stores important metadata about the database, such as the database name, datafile locations, and log file information. The control file is crucial for the proper functioning of the database as it is used during database startup and recovery operations. It helps in maintaining the consistency and integrity of the database by keeping track of changes made to the database. Therefore, the control file is an essential component of the Oracle database.Rate this question:
-
- 4.
Examine this TRUNCATE TABLE command: TRUNCATE TABLE departments; Which four are true about the command? (Choose four.)
- A.
All extents are released.
- B.
All rows of the table are deleted.
- C.
Any associated indexes are truncated.
- D.
No undo data is generated for the table's rows.
- E.
It reduces the number of extents allocated to the DEPARTMENTS table to the original setting for MINEXTENTS.
Correct Answer(s)
B. All rows of the table are deleted.
C. Any associated indexes are truncated.
D. No undo data is generated for the table's rows.
E. It reduces the number of extents allocated to the DEPARTMENTS table to the original setting for MINEXTENTS.Explanation
The TRUNCATE TABLE command in this scenario has the following implications:
1. All rows of the table are deleted: This means that all data within the "departments" table will be removed.
2. Any associated indexes are truncated: Any indexes associated with the "departments" table will also be truncated, essentially removing them.
3. No undo data is generated for the table's rows: Unlike other deletion methods, such as DELETE, the TRUNCATE TABLE command does not generate any undo data for the deleted rows. This means that the action cannot be rolled back or undone.
4. It reduces the number of extents allocated to the DEPARTMENTS table to the original setting for MINEXTENTS: This implies that the TRUNCATE TABLE command will reduce the number of extents allocated to the "departments" table to its original setting, which is determined by the MINEXTENTS parameter.Rate this question:
-
- 5.
Which data dictionary view would you use to get a list of object privileges for all database users?
- A.
DBA_TAB_PRIVS
- B.
ALL_TAB_PRIVS
- C.
USER_TAB_PRIVS
- D.
ALL_TAB_PRIVS_MADE
Correct Answer
A. DBA_TAB_PRIVSExplanation
DBA_TAB_PRIVS is the correct answer because it is a data dictionary view that provides information about object privileges for all database users. This view contains columns such as GRANTEE (the user who has been granted the privilege), OWNER (the owner of the object), TABLE_NAME (the name of the object), and PRIVILEGE (the type of privilege granted). By querying this view, you can obtain a comprehensive list of object privileges for all users in the database.Rate this question:
-
- 6.
Your developers asked you to create an index on the PROD_ID column of the SALES_HISTORY table, which has 100 million rows. The table has approximately 2 million rows of new data loaded on the first day of every month. For the remainder of the month, the table is only queried. Most reports are generated according to the PROD_ID, which has 96 distinct values. Which type of index would be appropriate?
- A.
Bitmap
- B.
Reverse key
- C.
Unique B-Tree
- D.
Normal B-Tree
Correct Answer
A. BitmapExplanation
A bitmap index would be appropriate in this scenario. This is because the PROD_ID column has a relatively low cardinality (96 distinct values), and the table is mostly queried rather than being updated frequently. Bitmap indexes are efficient for columns with low cardinality and are especially useful for read-intensive operations. They use a bitmap for each distinct value in the column, making them suitable for queries that involve multiple values or combinations of values.Rate this question:
-
- 7.
Which privilege is required to create a database?
- A.
DBA
- B.
SYSDBA
- C.
SYSOPER
- D.
RESOURCE
Correct Answer
B. SYSDBAExplanation
To create a database, the privilege required is SYSDBA. SYSDBA is a powerful privilege that allows a user to perform administrative tasks, including creating and managing databases, as well as performing backup and recovery operations. This privilege is typically granted to database administrators who need full control over the database system. SYSDBA users have the highest level of access and authority within the database, making them responsible for managing critical operations and ensuring the overall stability and security of the database environment.Rate this question:
-
- 8.
User A issues this command: UPDATE emp SET id=200 WHERE id=1 Then user B issues this command: UPDATE emp SET id=300 WHERE id=1 User B informs you that the UPDATE statement seems to be hung. How can you resolve the problem so user B can continue working?
- A.
No action is required
- B.
Ask user B to abort the statement
- C.
Ask user A to commit the transaction
- D.
Ask user B to commit the transaction
Correct Answer
C. Ask user A to commit the transactionExplanation
If user B is experiencing a hung UPDATE statement, it means that user A's transaction is still in progress and has not been committed. By asking user A to commit the transaction, it will release the lock on the emp table and allow user B to continue working without any issues.Rate this question:
-
- 9.
The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) Which statement finds students who have a grade point average (GPA) greater than 3.0 for the calendar year 2001?
- A.
SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' OR gpa > 3.0;
- B.
SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa gt 3.0;
- C.
SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa > 3.0;
- D.
SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa >= 3.0;
- E.
SELECT student_id, gpa FROM student_grades WHERE semester_end > '01-JAN-2001' OR semester_end < '31-DEC-2001' AND gpa >= 3.0;
Correct Answer
C. SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa > 3.0;Explanation
The correct answer is "SELECT student_id, gpa FROM student_grades WHERE semester_end BETWEEN '01-JAN-2001' AND '31-DEC-2001' AND gpa > 3.0;". This statement selects the student_id and gpa columns from the student_grades table. It uses the BETWEEN operator to filter the records where the semester_end date falls between '01-JAN-2001' and '31-DEC-2001'. Additionally, it includes a condition to filter only those records where the gpa is greater than 3.0.Rate this question:
-
- 10.
Scott issues the SQL statements: to be done 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?
- A.
SELECT * FROM DEPT
- B.
SELECT * FROM SCOTT. DEPT
- C.
SELECT * FROM DBA.SCOTT.DEPT
- D.
SELECT * FROM ALL_USERS WHERE USER_NAME = ‘SCOTT’ AND TABLE NAME= 'DEPT'
Correct Answer
B. SELECT * FROM SCOTT. DEPTExplanation
Sue should use the command "SELECT * FROM SCOTT.DEPT" to select from Scott's DEPT table. This command specifies the schema (SCOTT) and table name (DEPT) that Sue wants to select from.Rate this question:
-
- 11.
The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL STREET_ADDRESS VARCHAR2(150) CITY_ADDRESS VARCHAR2(50) STATE_ADDRESS VARCHAR2(50) PROVINCE_ADDRESSVARCHAR2(50) COUNTRY_ADDRESS VARCHAR2(50) POSTE_CODE VARCHAR2(12) CUSTOMER_PHONE VARCHAR2(20) THE CUSTOMER_ID column is the primary key for the table which two statements find the number of customer? (Choose two.)
- A.
SELECT TOTAL (*) FROM customers;
- B.
SELECT COUNT (*) FROM customers;
- C.
SELECT TOTAL (customer_id) FROM customer;
- D.
SELECT COUNT(costomer_id) FROM customer;
- E.
SELECT COUNT(customers) FROM customers;
- F.
SELECT TOTAL (customer_name) FROM customers;
Correct Answer(s)
B. SELECT COUNT (*) FROM customers;
D. SELECT COUNT(costomer_id) FROM customer;Explanation
The first statement, "SELECT COUNT (*) FROM customers;", finds the number of customers by counting the total number of rows in the customers table.
The fourth statement, "SELECT COUNT(costomer_id) FROM customer;", also finds the number of customers by counting the total number of non-null values in the customer_id column of the customer table.Rate this question:
-
- 12.
In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?
- A.
Before the WHERE clause
- B.
Before the FROM clause
- C.
After the ORDER BY clause
- D.
After the WHERE clause
Correct Answer
D. After the WHERE clauseExplanation
The GROUP BY clause is placed after the WHERE clause in a SELECT statement. This allows for the grouping of rows based on a specified column or columns. The WHERE clause is used to filter the rows based on certain conditions before the grouping is applied. Placing the GROUP BY clause after the WHERE clause ensures that the filtering is done before the grouping is performed.Rate this question:
-
- 13.
Which two are true about aggregate functions? (Choose two)
- A.
You can use aggregate functions in any clause of a SELECT statement.
- B.
You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
- C.
You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns
- D.
You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
- E.
You can use aggregate functions on a table, only by grouping the whole table as one single group.
Correct Answer(s)
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.Explanation
Aggregate functions can be used only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement. This means that aggregate functions cannot be used in any other clause of a SELECT statement such as GROUP BY, HAVING, or ORDER BY. Additionally, aggregate functions allow for the passing of various parameters such as column names, expressions, constants, or functions. This allows for more flexibility in performing calculations on the data.Rate this question:
-
- 14.
. Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE You issue these statements: CREATE table new_emp (employee_id NUMBER, name VARCHAR2 (30)); INSERT INTO new_emp SELECT employee_id, last_name from employees; Savepoint s2; Delete from new_emp; Rolback to s2; Delete from new_emp where employee_id = 180; UPDATE new_emp set name = ‘James’; Rolback to s2; UPDATE new_emp set name = ‘James’ WHERE employee_id = 180; Rollback; At the end of this transaction, what is true?
- A.
You have no rows in the table.
- B.
You have an employee with the name of James
- C.
You cannot roll back to the same savepoint more than once.
- D.
Your last update fails to update any rows because employee ID 180 was already deleted.
Correct Answer
A. You have no rows in the table.Explanation
At the end of this transaction, the table "new_emp" will have no rows. This is because the transaction starts with creating the table "new_emp" and inserting data from the "employees" table into it. However, after saving a savepoint (s2) and deleting all rows from "new_emp", the rollback to s2 restores the table to its state before the delete statement, which means the rows are still deleted. The subsequent update statement does not affect any rows because the employee with ID 180 was already deleted. Therefore, the table will have no rows in the end.Rate this question:
-
- 15.
Which / SQL* Plus feature can be used to replace values in the where clause?
- A.
Substitution variables
- B.
Replacement variables
- C.
Prompt variables
- D.
Instead-of variables
Correct Answer
A. Substitution variablesExplanation
Substitution variables in SQL* Plus can be used to replace values in the where clause. They allow users to dynamically input values at runtime, making queries more flexible and interactive. By using the ampersand (&) symbol followed by a variable name, users can prompt for input and substitute the entered value into the query. This feature is particularly useful when the values in the where clause need to be changed frequently or when the user wants to filter the results based on specific criteria.Rate this question:
-
- 16.
.Evaluate the SQL statement: SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?
- A.
0
- B.
1
- C.
0.00
- D.
An error statement
Correct Answer
A. 0Explanation
The SQL statement is evaluating the expression ROUND(TRUNC(MOD(1600,10),-1),2).
First, the MOD(1600,10) calculates the remainder of 1600 divided by 10, which is 0.
Then, the TRUNC(0,-1) function truncates the value 0 to the nearest 10th, which is still 0.
Finally, the ROUND(0,2) function rounds the value 0 to 2 decimal places, resulting in 0.00.
Since the question asks for what will be displayed, the correct answer is 0.Rate this question:
-
- 17.
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) DEPARTMENT_ID NUMBER SALARY NUMBER
- A.
SELECT a last_name, a salary, a department_id, b.maxsal FROM employees a, (SELECT department_id, max(salary)maxsal FROM employees GROUP BY department_id)b WHERE a department_id = department-id AND a_salary
- B.
SELECT a. last name, a salary, a. department_id FROM employees a WHERE a. department_id IN (SELECT department_id FROM employees b GROUP BY department_id having salary = SELECT max(salary) from employees
- C.
SELECT a last_name, a salary, a.department_id FROM employees a WHERE a salary = SELECT max(salary) FROM employees b WHERE a department_id = department_id);
- D.
SELECT a last_name, a salary, a.department_id FROM employees a WHERE (a department_id, a salary) IN (SELECT department_id, a salary) IN (SELECT department_id max(salary) FROM employees b GROUP BY department_id ORDER BY department_id);
Correct Answer
A. SELECT a last_name, a salary, a department_id, b.maxsal FROM employees a, (SELECT department_id, max(salary)maxsal FROM employees GROUP BY department_id)b WHERE a department_id = department-id AND a_salaryExplanation
The given answer is a valid SQL query that retrieves the last name, salary, department ID, and the maximum salary for each department from the EMPLOYEES table. It achieves this by using a subquery to calculate the maximum salary for each department and then joining it with the EMPLOYEES table using the department ID. The result is a list of employees with their respective last name, salary, department ID, and the maximum salary for each department.Rate this question:
-
- 18.
Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER NOT NULL EMP_ID VARCHAR2(30) JOB_ID VARCHAR2(20) DEFAULT ‘SA_REP’ SAL NUMBER COMM_PCT NUMBER MGR_ID NUMBER DEPARTMENT_ID NUMBER you need to update the records of emloyees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below: JOB_ID Default value specified for this column definition SAL maximum salary earned for the_job ID SA_REP COMM_PCT Default value is specified for the column, the value should be NULL DEPARTMENT_ID: Supplied by the user during run time through substitution variable which UPDATE statement meets the requirements?
- A.
UPDATE employees SET job_id=DEFAULT AND Sal=(SELECT MAX(sal) FROM emoployees WHERE job_id='SA_REP' AND comm_pet=DEFALUT AND department_id =&did WHERE employee_id IN (103, 115),
- B.
UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal) AND comm_pct = DEFAULT OR NULL AND department _id = & did WHERE employee_id IN (103,115) AND ob_id = 'SA_REP'
- C.
UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX (sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT, department _id = &did WHERE employee_id IN (103,115)
- D.
UPDATE emplouees SET job_id = DEFAULT sal = MAX (sal) comm_pct = DEFAULT department_id = &did WHERE employee_id IN (103,115) AND job_id = 'SA_REP'
- E.
UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did WHEREemployee_id IN (103,115)
Correct Answer
C. UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX (sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT, department _id = &did WHERE employee_id IN (103,115)Explanation
The correct answer is the fourth option: UPDATE emplouees SET job_id = DEFAULT sal = MAX (sal) comm_pct = DEFAULT department_id = &did WHERE employee_id IN (103,115) AND job_id = 'SA_REP'. This statement updates the job_id to the default value, the sal to the maximum salary earned for the job_id 'SA_REP', the comm_pct to the default value, and the department_id to the value provided by the user through the substitution variable &did. It also specifies that the update should only be applied to the employee_id 103 and 115.Rate this question:
-
- 19.
Which three are DATETIME data types that can be used when specifying column definitions? (Choose three)
- A.
TIMESTAMP
- B.
INTERVAL MONTH TO DAY
- C.
INTERVAL DAY TO SECOND
- D.
INTERVAL YEAR TO MONTH
- E.
TIMESTAMP WITH DATABASE TIMEZONE
Correct Answer(s)
A. TIMESTAMP
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTHExplanation
The three DATETIME data types that can be used when specifying column definitions are TIMESTAMP, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH. These data types are commonly used in databases to store date and time information. TIMESTAMP stores both date and time values, INTERVAL DAY TO SECOND stores a duration in days, hours, minutes, and seconds, and INTERVAL YEAR TO MONTH stores a duration in years and months. These data types provide flexibility in storing and manipulating date and time data in a database.Rate this question:
-
- 20.
Examine the structure of the EMPLOYEES table: column name data type remarks EMPLOYEE_ID NUMBER NOT NULL, primary key LAST_NAME VARCHAR2(30) FIRST_NAME VARCHAR2(30) JOB_ID NUMBER SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER You need to create an index called NAME IDX on the first name and last name fields of the EMPLOYEES table. Which SQL statement would you use to perform this task?
- A.
CREATE INDEX NAME_IDX (first_name, last_name)
- B.
CREATE INDEX NAME_IDX (first_name AND last_name)
- C.
CREATE INDEX NAME_IDX ON (first_name, last_name)
- D.
CREATE INDEX NAME_IDX ON employees (first_name AND last_name)
- E.
CREATE INDEX NAME_IDX ON employees (first_name, last_name)
Correct Answer
E. CREATE INDEX NAME_IDX ON employees (first_name, last_name)Explanation
The correct answer is "CREATE INDEX NAME_IDX ON employees (first_name, last_name)". This statement is used to create an index called NAME_IDX on the first name and last name fields of the EMPLOYEES table. It specifies the table name (employees), the index name (NAME_IDX), and the columns to be included in the index (first_name and last_name).Rate this question:
-
- 21.
You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes 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 stuent_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)
Correct Answer
D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);Explanation
The correct answer is "ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);". This statement adds a constraint named "stud_id_pk" to the "students" table, specifying that the "student_id" column should be the primary key. This ensures that each value in the "student_id" column is unique and not null, effectively creating a primary key on that column.Rate this question:
-
- 22.
Which two statements about views are true? (Choose two)
- A.
A view can be created as read only
- B.
A view can be created as a join on two or more tables.
- C.
A view cannot have an ORDER BY clause in the SELECT statement.
- D.
A view cannot be created with a GROUP BY clause in the SELECT statement.
- E.
A view must have aliases defined for the column names in the SELECT statement.
Correct Answer(s)
A. A view can be created as read only
B. A view can be created as a join on two or more tables.Explanation
Views can be created as read-only, meaning that they can be used to retrieve data but not to modify it. Views can also be created as a join on two or more tables, allowing users to retrieve data from multiple tables as if it were a single table. However, views can have an ORDER BY clause in the SELECT statement, so the statement that says a view cannot have an ORDER BY clause is incorrect. Similarly, views can be created with a GROUP BY clause in the SELECT statement, so the statement that says a view cannot be created with a GROUP BY clause is also incorrect. Lastly, views do not necessarily require aliases for the column names in the SELECT statement, so the statement that says a view must have aliases defined for the column names is incorrect.Rate this question:
-
- 23.
Which is an /SQL * Plus command?
- A.
INSERT
- B.
DELETE
- C.
EXPAND
- D.
DESCRIBE
Correct Answer
D. DESCRIBEExplanation
The correct answer is DESCRIBE because it is a valid SQL*Plus command used to display the structure of a table, including the column names, datatypes, and constraints. It provides information about the table's structure and allows users to understand the layout of the table without querying the data itself.Rate this question:
-
- 24.
Which SELECT statement should you use to extract the year form the system date and display it in the format “1998”?
- 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 TO_CHAR (SUBSTR(SYSDATE, 8,2),’yyyy’) FROM dual
Correct Answer
A. SELECT TO_CHAR(SYSDATE, ‘yyyy’) FROM dualExplanation
The correct answer is SELECT TO_CHAR(SYSDATE, ‘yyyy’) FROM dual. This statement uses the TO_CHAR function to convert the system date (SYSDATE) into the specified format (‘yyyy’), which represents the year. The result will be displayed as "1998" or any other current year. The FROM dual clause is used to ensure that the query is executed properly.Rate this question:
-
- 25.
A subquery can be used to _________.
- A.
Create groups of data
- B.
Sort data in a specific order
- C.
Convert data to a different format
- D.
Retrieve data based on an unknown condition
Correct Answer
D. Retrieve data based on an unknown conditionExplanation
A subquery can be used to retrieve data based on an unknown condition. This means that the subquery can be used to fetch data from a table based on a condition that is not known or specified beforehand. The subquery can be used to dynamically retrieve data based on the result of another query or based on certain calculations or comparisons within the subquery itself. This allows for more flexible and dynamic data retrieval, as the condition can be determined at runtime.Rate this question:
-
- 26.
Which clause should you use to exclude group results?
- A.
WHERE
- B.
HAVING
- C.
RESTRICT
- D.
GROUP BY
Correct Answer
B. HAVINGExplanation
The HAVING clause should be used to exclude group results. Unlike the WHERE clause, which filters rows before grouping, the HAVING clause filters groups after they have been formed. It allows you to specify conditions that must be met by the groups, allowing you to exclude certain groups based on those conditions. This is useful when you want to filter the results based on aggregate functions such as COUNT, SUM, or AVG. The HAVING clause is typically used in conjunction with the GROUP BY clause to perform group-level filtering.Rate this question:
-
- 27.
You would like to display the system date in the format *Monday, 01 June, 2001* Which SELECT statement should you use?
- A.
SELECT TO_DATE (SYSDATE, ‘FMDAY, DD Month, YYYY’) FROM dual
- B.
SELECT TO_CHAR(SYSDATE, ‘FMDD, DY Month ‘YYY’) FROM dual
- C.
SELECT TO_CHAR(SYSDATE, ‘FMDay, DD Month YYYY’) FROM dual
- D.
SELECT TO_CHAR(SYSDATE, ‘FMDAY, DDD Month, YYYY’) FROM dual
- E.
SELECT TO_DATES(SYSDATE,’FMDY, DDD Month, YYYY’) FROM dual
Correct Answer
C. SELECT TO_CHAR(SYSDATE, ‘FMDay, DD Month YYYY’) FROM dualExplanation
The correct answer is SELECT TO_CHAR(SYSDATE, ‘FMDay, DD Month YYYY’) because the TO_CHAR function is used to convert the system date to a character string in the specified format. The 'FMDay' format specifier will display the full name of the day, 'DD' will display the day of the month, 'Month' will display the full name of the month, and 'YYYY' will display the year in four digits. This will result in the desired format of "Monday, 01 June, 2001".Rate this question:
-
- 28.
which three statements correctly describe the functions and use of constraints? (Choose three)
- A.
Constraints provide data independence
- B.
Constraint make complex queries easy
- C.
Constraints enforce rules at the view level
- D.
Constraints enforce rules at the table level
- E.
constraints prevent the deletion of a table if there are dependencies
- F.
Constraints prevent the deletion of an index if there are dependencies
Correct Answer(s)
A. Constraints provide data independence
C. Constraints enforce rules at the view level
D. Constraints enforce rules at the table levelExplanation
Constraints provide data independence by ensuring that the data remains consistent and accurate regardless of changes made to the database structure. Constraints enforce rules at both the view level and the table level to maintain data integrity and prevent invalid or inconsistent data from being entered. These rules can include enforcing unique values, referential integrity, and data type constraints. Constraints also prevent the deletion of a table or an index if there are dependencies, ensuring that data dependencies are maintained and preventing data loss or corruption.Rate this question:
-
- 29.
Which two are character manipulation functions? (Choose two)
- A.
TRIM
- B.
REPLACE
- C.
TRUNC
- D.
TO_DATE
- E.
MOD
Correct Answer(s)
A. TRIM
B. REPLACEExplanation
TRIM and REPLACE are character manipulation functions. TRIM is used to remove leading and trailing spaces from a string, while REPLACE is used to replace a specific substring within a string with another substring. These functions are commonly used in database queries or programming languages to manipulate and modify character data. TRUNC, TO_DATE, and MOD are not character manipulation functions; TRUNC is used for truncating a date or number, TO_DATE is used for converting a string to a date, and MOD is used for finding the remainder of a division operation.Rate this question:
-
- 30.
. The EMPLOYEES table contains these columns: LAST_NAME VARCHAR2(25) SALARY NUMBER(6,2) COMMISSION_PCT NUMBER(6) You need to write a query that will produce these results: 1. Display the salary multiplied by the commission_pct 2. Exclude employees with a zero commission_pct 3. Display a zero for employees with a null commission value Evaluate the SQL statement: SELECT LAST_NAME, SALARY * COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL; What does the statement provide?
- A.
All of the desired results
- B.
Two of the desired results
- C.
One of the desired results
- D.
An error statement
Correct Answer
C. One of the desired resultsExplanation
The given SQL statement provides one of the desired results, which is to display the salary multiplied by the commission_pct. It achieves this by selecting the LAST_NAME and multiplying the SALARY by COMMISSION_PCT from the EMPLOYEES table, and only including rows where the COMMISSION_PCT is not null. However, it does not address the other two desired results, which are to exclude employees with a zero commission_pct and to display a zero for employees with a null commission value.Rate this question:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 21, 2023Quiz Edited by
ProProfs Editorial Team -
Jun 07, 2011Quiz Created by
Sajeesh88
Back to top