Test Your Oracle ROWs and COLUMNs Knowledge

Progress

True / False

In Oracle Database, rows in a table are also known as records.

True False

Rows in a table, also known as records, represent individual entries of data in the table.

True / False

Columns in an Oracle table define the data type and characteristics of the data that can be stored in each cell.

True False

Columns in a table specify the data type and constraints for the data stored in each cell of that column.

True / False

In Oracle, the SELECT statement can be used to retrieve specific columns from a table.

True False

The SELECT statement allows users to query and retrieve specific columns from a table.

True / False

In Oracle, you can update multiple rows in a table with a single UPDATE statement.

True False

The UPDATE statement can be used to modify data in multiple rows at once, based on a specified condition.

True / False

In Oracle, the INSERT statement can only add one row at a time to a table.

True False

The INSERT statement can add multiple rows at once using the INSERT ALL or INSERT INTO ... SELECT syntax.

True / False

In Oracle, each column in a table must have a unique name.

True False

Each column in a table must have a unique name to ensure that the data in each column can be correctly identified.

True / False

Oracle supports the use of virtual columns, which are columns that are not physically stored in the table but are derived from other columns.

True False

Virtual columns in Oracle are computed columns based on expressions or functions applied to other columns, and they are not physically stored in the table.

True / False

In Oracle, a single row can span multiple data blocks.

True False

A single row in Oracle cannot span multiple data blocks; it must fit within a single data block.

True / False

In Oracle, the ROWID pseudo-column uniquely identifies each row in a table.

True False

The ROWID pseudo-column uniquely identifies each row in a table by providing a unique identifier for each row.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes topics on efficiently managing rows and columns in large tables.

True False

The OCP certification exam covers a wide range of topics, including efficient management and optimization of rows and columns in large tables, reflecting the candidate's comprehensive knowledge and skills in Oracle Database administration.

Q11

Single Choice

In Oracle, which SQL statement is used to retrieve all columns from a table?

SELECT * FROM table_name; SELECT ALL FROM table_name; SELECT COLUMNS FROM table_name;SELECT table_name FROM *;

: The SELECT * statement retrieves all columns from the specified table.

Q12

Single Choice

Which Oracle SQL command is used to insert a new row into a table?

INSERT INTO table_name VALUES (...); ADD ROW table_name VALUES (...); INSERT ROW INTO table_name (...);CREATE ROW IN table_name VALUES (...);

The INSERT INTO statement is used to insert new rows into a table in Oracle.

Q13

Single Choice

How do you select specific columns from a table in Oracle?

SELECT column1, column2 FROM table_name; GET column1, column2 FROM table_name; FETCH column1, column2 FROM table_name;DISPLAY column1, column2 FROM table_name;

The SELECT statement followed by column names retrieves specific columns from a table in Oracle.

Q14

Single Choice

What will the following Oracle SQL query return?

SQL Code

SELECT first_name, last_name FROM employees WHERE department_id = 10;

All columns from the employees table. Only the first name and last name of employees in department 10. The number of employees in department 10.Only the department ID of employees in department 10.

The query selects only the first_name and last_name columns for rows where the department_id is 10.

Q15

Single Choice

In Oracle, what is the result of this SQL command?

SQL Code

UPDATE employees SET salary = salary * 1.10 WHERE department_id = 20;

Increases the salary of all employees by 10%. Sets the salary of all employees in department 20 to 1.10. Increases the salary of employees in department 20 by 10%.Increases the salary of employees in department 20 by $1.10.

The UPDATE statement multiplies the salary by 1.10 (a 10% increase) for employees in department 20.

Q16

Single Choice

Which Oracle SQL command is used to delete a specific row from a table?

DELETE FROM table_name WHERE condition; REMOVE FROM table_name WHERE condition; DROP FROM table_name WHERE condition;ERASE FROM table_name WHERE condition;

The DELETE FROM statement deletes specific rows from a table based on a condition.

Q17

Single Choice

Given the following SQL query in Oracle, what will be the outcome?

SQL Code

SELECT COUNT(*) FROM orders WHERE order_date > TO_DATE('2023-01-01', 'YYYY-MM-DD');

The total number of orders placed after January 1, 2023. The total number of orders placed before January 1, 2023. The sum of all orders after January 1, 2023.The maximum order amount after January 1, 2023.

The COUNT(*) function returns the number of rows that satisfy the condition specified in the WHERE clause.

Q18

Single Choice

What does the following Oracle SQL query do?

SQL Code

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

Lists all department IDs. Calculates the average salary across all departments. Lists each department ID and the average salary within that department.Lists all employees and their average salary.

The query groups the results by department_id and calculates the average salary for each group.

Q19

Single Choice

In Oracle, what does this SQL command accomplish?

SQL Code

ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

Adds a new column to the employees table. Ensures that the email column only contains unique values. Creates a new index on the email column.Changes the data type of the email column.

The ALTER TABLE statement with ADD CONSTRAINT UNIQUE ensures that all values in the email column are unique.

Q20

Single Choice

In Oracle, what is the purpose of using the ROWNUM pseudo-column in a SQL query?

To count the total number of rows in a table. To assign a unique number to each row in the result set. To retrieve a specific column from a table.To update rows in a table based on their order.

The ROWNUM pseudo-column is used to assign a unique, sequential integer to rows in the order they are retrieved from the database.

Q21

Multiple Choice

Identify the SQL scripts that correctly create and manipulate database tables and columns in Oracle.

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), salary NUMBER ); UPDATE employees SET salary = 5000 WHERE employee_id = 101; CREATE TABLE employees ( employee_id NUMBER, employee_name VARCHAR2(100), salary NUMBER ); UPDATE employees SET salary = 5000 WHERE employee_id = 101; CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), salary NUMBER ); UPDATE employees SET salary = 5000 WHERE employee_id = 101;CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, employee_name VARCHAR2(100), salary NUMBER ); UPDATE employees SET salary = 5000 WHERE employee_id = 101;

Creating tables and updating columns are fundamental operations in SQL. The correct scripts include both the CREATE TABLE statement and an UPDATE statement.

Q22

Multiple Choice

Select the SQL scripts that demonstrate correct use of the ALTER TABLE statement in Oracle.

ALTER TABLE employees ADD department_id NUMBER DEFAULT 10; ALTER TABLE employees DROP COLUMN bonus; ALTER TABLE employees ADD department_id NUMBER; ALTER TABLE employees DROP COLUMN bonus; ALTER TABLE employees ADD department_id NUMBER DEFAULT 10; ALTER TABLE employees DROP COLUMN bonus;ALTER TABLE employees ADD department_id NUMBER DEFAULT 10; ALTER TABLE employees DROP COLUMN bonus;

The ALTER TABLE statement is used to modify the structure of an existing table. The correct scripts will include the addition of a new column with a default value and the dropping of an existing column.

Q23

Multiple Choice

Determine the SQL scripts that correctly implement constraints on database tables in Oracle.

CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, order_status VARCHAR2(20) CHECK (order_status IN ('Pending', 'Shipped', 'Delivered')) ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, order_status VARCHAR2(20) DEFAULT 'Pending' ); CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, order_status VARCHAR2(20) CHECK (order_status IN ('Pending', 'Shipped', 'Delivered')) );CREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, order_status VARCHAR2(20) DEFAULT 'Pending' );

Constraints ensure data integrity by enforcing rules on table columns. The correct scripts will include primary key and check constraints.

Q24

Multiple Choice

Identify the SQL scripts that correctly use the DELETE and TRUNCATE statements in Oracle.

TRUNCATE TABLE logs; DELETE FROM employees WHERE department_id = 10; DELETE FROM logs; DELETE FROM employees WHERE department_id = 10; TRUNCATE TABLE logs; DELETE FROM employees WHERE department_id = 10;DELETE FROM logs; TRUNCATE TABLE employees WHERE department_id = 10;

DELETE and TRUNCATE are used to remove rows from a table. DELETE can remove specific rows, while TRUNCATE removes all rows without logging individual row deletions.

Q25

Multiple Choice

Choose the SQL scripts that demonstrate the correct use of JOINs in Oracle.

SELECT e.employee_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; SELECT e.employee_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; SELECT employee_name, department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;SELECT employee_name, department_name FROM employees e, departments d WHERE e.department_id = d.department_id;

JOINs are used to combine rows from two or more tables based on related columns. The correct scripts will demonstrate an INNER JOIN to retrieve the desired data.

Q26

Multiple Choice

Determine the SQL scripts that correctly implement subqueries in Oracle.

SELECT employee_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); SELECT employee_name FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees); SELECT employee_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);SELECT employee_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE salary = AVG(salary));

Subqueries allow for the execution of a query within another query. The correct scripts will demonstrate the use of a subquery to compare each employee's salary with the average salary.

Q27

Multiple Choice

Select the SQL scripts that correctly demonstrate the use of GROUP BY and HAVING clauses in Oracle.

SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) >= 5000; SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) >= 5000; SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) >= 5000;SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) >= 5000;

The GROUP BY clause is used to group rows that have the same values in specified columns. The HAVING clause is used to filter groups based on aggregate functions. The correct scripts will include both clauses.

Q28

Multiple Choice

Identify the SQL scripts that correctly use Oracle's analytic functions.

SELECT employee_name, hire_date, SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary FROM employees; SELECT employee_name, hire_date, SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary FROM employees; SELECT employee_name, hire_date, SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) AS cumulative_salary FROM employees;SELECT employee_name, hire_date, SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary FROM employees;

Analytic functions in Oracle perform calculations across a set of table rows and return a single result for each row. The correct scripts will demonstrate the use of the analytic function SUM() with the OVER() clause.

Q29

Multiple Choice

Determine the SQL scripts that correctly implement MERGE statements in Oracle.

MERGE INTO employees e USING employees_stage es ON (e.employee_id = es.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = es.salary WHEN NOT MATCHED THEN INSERT (employee_id, employee_name, salary) VALUES (es.employee_id, es.employee_name, es.salary); MERGE INTO employees e USING employees_stage es ON (e.employee_id = es.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = es.salary WHEN NOT MATCHED THEN INSERT (employee_id, employee_name, salary) VALUES (es.employee_id, es.employee_name, es.salary); MERGE INTO employees e USING employees_stage es ON (e.employee_id = es.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = es.salary WHEN NOT MATCHED THEN INSERT (employee_id, employee_name, salary) VALUES (es.employee_id, es.employee_name, es.salary);MERGE INTO employees e USING employees_stage es ON (e.employee_id = es.employee_id) WHEN MATCHED THEN UPDATE SET e.salary = es.salary WHEN NOT MATCHED THEN INSERT (employee_id, employee_name, salary) VALUES (es.employee_id, es.employee_name, es.salary);

The MERGE statement is used to combine insert and update operations into a single statement. The correct scripts will demonstrate the use of the MERGE statement with the WHEN MATCHED and WHEN NOT MATCHED clauses.

Q30

Multiple Choice

Select the SQL scripts that demonstrate the correct use of the INSERT ALL statement in Oracle.

INSERT ALL INTO employees (employee_id, employee_name, salary) VALUES (201, 'John Doe', 5000) INTO employees (employee_id, employee_name, salary) VALUES (202, 'Jane Smith', 6000) INTO employees (employee_id, employee_name, salary) VALUES (203, 'Jim Brown', 5500) SELECT * FROM dual; INSERT ALL INTO employees (employee_id, employee_name, salary) VALUES (201, 'John Doe', 5000) INTO employees (employee_id, employee_name, salary) VALUES (202, 'Jane Smith', 6000) INTO employees (employee_id, employee_name, salary) VALUES (203, 'Jim Brown', 5500) SELECT * FROM dual; INSERT ALL INTO employees (employee_id, employee_name, salary) VALUES (201, 'John Doe', 5000) INTO employees (employee_id, employee_name, salary) VALUES (202, 'Jane Smith', 6000) INTO employees (employee_id, employee_name, salary) VALUES (203, 'Jim Brown', 5500) SELECT * FROM dual;INSERT ALL INTO employees (employee_id, employee_name, salary) VALUES (201, 'John Doe', 5000) INTO employees (employee_id, employee_name, salary) VALUES (202, 'Jane Smith', 6000) INTO employees (employee_id, employee_name, salary) VALUES (203, 'Jim Brown', 5500) SELECT * FROM dual;

The INSERT ALL statement is used to insert multiple rows into a table in a single statement. The correct scripts will demonstrate the use of the INSERT ALL statement with multiple INTO clauses.

Oracle Database Quiz Questions