Oracle Mock Test 1

The correct answer is "SELECT last_name FROM EMP WHERE last_name LIKE '_A%;". This SQL statement uses the LIKE operator with a wildcard character (_) to match any single character and the letter "A" as the second character in the last name. This will retrieve the last names of employees who have the letter "A" as the second character in their names.

The function to_numbert() is not a valid number function. This can be inferred from the fact that the other options - sinh(), sqrt(), and round() - are commonly known mathematical functions that operate on numbers. However, to_numbert() does not follow the same pattern and is therefore not a number function.

The given question is asking to select the TCL (Transaction Control Language) statements. TCL statements are used to manage transactions in a database. The correct answers are COMMIT, ROLLBACK, and SAVEPOINT. COMMIT is used to permanently save the changes made in a transaction, ROLLBACK is used to undo the changes made in a transaction, and SAVEPOINT is used to set a point in a transaction from where the changes can be rolled back.

The SQL statement is using the functions ROUND, TRUNC, and MOD to perform calculations on the number 1600. The MOD function calculates the remainder when 1600 is divided by 10, which is 0. The TRUNC function then truncates this result to the nearest multiple of 10, which is also 0. Finally, the ROUND function rounds this result to 2 decimal places, which is still 0. Therefore, the output of the SQL statement will be 0.

The correct answer is the fourth option, "ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id)." This statement explicitly names a constraint by using the keyword "CONSTRAINT" followed by the name "student_id_fk." It also specifies the foreign key constraint by using the keyword "FOREIGN KEY" and references the table "students" and the column "student_id."

The NOT IN operator can be used with a multiple row subquery to exclude values that are present in the subquery result from the main query result. It returns all rows from the main query where the value is not found in the subquery result.

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 explicitly known or specified. The subquery can be used to dynamically determine the condition or filter criteria for retrieving the data, allowing for more flexibility in querying the database.

DML operations cannot be performed on a complex view. A complex view is a view that is created using multiple tables or subqueries, and it may involve joins, aggregations, or other complex operations. While DML operations like INSERT, UPDATE, and DELETE can be performed on simple views (views created from a single table), they are not allowed on complex views. Therefore, the statement is false.

BFILE is the correct answer because it is used to store large binary files outside of the database, such as images, audio, or video files. BLOB, CLOB, and LONG RAW are all types of data that can be stored internally within the database.

A FULL OUTER JOIN is used when you want to retrieve all unmatched data from both tables. This means that you want to include all records from both tables, even if they do not have a matching record in the other table. This is useful when you want to combine data from two tables and include all the information available, regardless of whether it has a match or not.

The SQL statement is using the ROUND and TRUNC functions on two numbers: 45.953 and 45.936. The ROUND function with a second argument of -1 rounds the first number to the nearest tens place, resulting in 50. The TRUNC function with a second argument of 2 truncates the second number to two decimal places, resulting in 45.93. Therefore, the values displayed are 50 and 45.93.

SQL is a relational database language that is not designed to support object-oriented programming concepts. Object-orientation allows for the encapsulation of data and behavior into objects, which SQL lacks. SQL is primarily focused on managing and manipulating structured data in a relational database management system. Therefore, it is not suitable for implementing object-oriented concepts like inheritance, polymorphism, and encapsulation.

The correct answer is the last option because it correctly includes the criteria of purchasing before January 21, 2001 and having a price less than $500 or greater than $900. The ORDER BY clause is used to sort the results by the purchase date in descending order, starting with the most recently bought book.

The CREATE VIEW privilege allows the user to create a view in their own schema. This means that they can create a view using tables and data that they have access to within their own schema. They do not have the ability to create a view in any other schema or create a view that is accessible by everyone. Additionally, the option of creating a table view is not mentioned as a possibility with the CREATE VIEW privilege.

The given SQL statement uses a subquery to find the maximum salary for each department and then joins it with the employees table. It selects the employee name, salary, department ID, and maximum salary for all employees whose salary is less than the maximum salary in their department. Therefore, the statement returns the 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.

The correct answer is "SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';". This query uses the LIKE operator with the pattern '%A\_B%', where the underscore (_) is a wildcard representing any single character. The ESCAPE '\' clause is used to escape the underscore character so that it is treated as a literal character instead of a wildcard. This query will search for employees with names that have the pattern 'A_B', where the underscore represents any single character.

The correct answer is "SELECT 'Dear Customer ' || customer_name || ',' FROM customers;". This statement concatenates the string 'Dear Customer ' with the values from the customer_name column in the CUSTOMERS table, followed by a comma. This will produce the desired output of "Dear Customer customer_name" for each row in the table.

To add a foreign key constraint on the dept_no column in the EMP table, the correct approach is to use the ALTER TABLE command with the ADD clause on the EMP table. This allows us to specify the foreign key constraint and reference the ID column in the DEPT table. By using the ADD clause on the EMP table, we can define the foreign key relationship between the two tables and ensure data integrity by enforcing referential integrity rules.

The TRUNCATE statement in Oracle is a Data Definition Language (DDL) command that is used to remove all data from a table. Unlike other DDL commands like DROP or ALTER, the TRUNCATE statement cannot be rolled back. Once the TRUNCATE statement is executed, the data is permanently deleted from the table and cannot be recovered. Therefore, it is important to use the TRUNCATE statement with caution as it cannot be undone.

Using the TO_CHAR function, you can convert a number (such as 10) to a character expression ('10') and you can also convert a date to a character expression.

The correct answer is to use the statement "Select last name, first name. FROM emp WHERE LOWER (last name)= 'smith';". This statement will list all the employees whose last name is "Smith" regardless of the case in which the last names are stored. By using the LOWER function, the last name will be converted to lowercase, allowing for a case-insensitive comparison with the string 'smith'.

The correct answer is "ALTER TABLE table_name DROP PRIMARY KEY CASCADE." This syntax is used to remove a PRIMARY KEY constraint and all its dependent constraints. The "DROP PRIMARY KEY" command removes the primary key constraint from the specified table, and the "CASCADE" keyword ensures that any dependent constraints are also removed.

The correct answer is "ALTER TABLE student MODIFY(name varcher2(25) NOT NULL)". This statement modifies the structure of the table by adding a constraint to the NAME column, specifying that it cannot contain NULL values.

The correct query to retrieve all employees' last names, along with their managers' last names and their department names, is the one that uses a LEFT OUTER JOIN to join the employees table with itself on the manager_id column, and then uses another LEFT OUTER JOIN to join the result with the departments table on the department_id column. This query ensures that all employees are included in the result, even if they do not have a manager or department.

The first statement is incorrect because it uses the wrong range for the order total (100 to 2000 instead of 100 to 200). The second statement is correct because it uses the correct range for the order total (100 to 200). The third statement is incorrect because it uses the wrong syntax for the comparison operators (missing the second operand for the second comparison). The fourth statement is incorrect because it uses the wrong syntax for the comparison operators (missing the second operand for the second comparison).

The SQL statement DROP TABLE DEPT will delete all data in the table and also delete the table structure. Additionally, all indexes based on the DEPT table will be dropped.

In a query with a GROUP BY clause, the columns in the SELECT clause must be aggregate functions only. This means that when using GROUP BY, the SELECT clause can only include aggregate functions such as SUM, COUNT, AVG, etc. It is not possible to include individual columns in the SELECT clause without applying an aggregate function to them. The purpose of the GROUP BY clause is to group the data based on certain columns, and the SELECT clause should only display the aggregated results of those groups.

The four types of functions available in SQL are character functions, date functions, conversion functions, and numeric functions. Character functions are used to manipulate and analyze character data, such as extracting substrings or changing the case of letters. Date functions are used to perform calculations and manipulations on date and time values. Conversion functions are used to convert data types from one format to another. Numeric functions are used to perform calculations and manipulations on numeric data, such as finding the square root or rounding numbers.

The first statement inserts a row into the table by providing values for all columns, including a NULL value for the EMPLOYEE_ID column. The second statement inserts a row by providing values for the FIRST_NAME and LAST_NAME columns. The third statement inserts a row by providing a value for the EMPLOYEE_ID column and NULL values for the FIRST_NAME and LAST_NAME columns.

The first subquery "SELECT last_name FROM employees Where salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id)" works because it returns the last names of employees whose salary is greater than the maximum salary in their respective departments.

The second subquery "SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id)" works because it returns the department IDs of departments where all employees have a salary greater than the average salary of their department.

The third subquery "SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id)" works because it returns the distinct department IDs where at least one employee has a salary greater than the average salary of their department.