Test Your Oracle SQL Knowledge

Progress

True / False

SQL stands for Structured Query Language.

True False

SQL stands for Structured Query Language, which is used to manage and manipulate relational databases.

True / False

SQL is only used in Oracle databases.

True False

SQL is a standard language used for managing and manipulating relational databases, and it is used in many database systems, including Oracle, MySQL, PostgreSQL, and SQL Server.

True / False

The SELECT statement in SQL is used to retrieve data from a database.

True False

The SELECT statement is used to query and retrieve data from one or more tables in a database.

True / False

In Oracle SQL, the WHERE clause is used to filter records based on specified conditions.

True False

The WHERE clause is used to specify conditions that filter the rows returned by a SELECT, UPDATE, or DELETE statement.

True / False

SQL commands in Oracle are case-sensitive.

True False

SQL commands in Oracle are not case-sensitive; however, data within the database (such as text values) can be case-sensitive depending on the collation settings.

True / False

The INSERT statement in SQL is used to modify existing records in a table.

True False

The INSERT statement is used to add new records to a table, whereas the UPDATE statement is used to modify existing records.

True / False

In Oracle SQL, subqueries can be used within a SELECT statement to perform nested queries.

True False

Subqueries, also known as nested queries, can be used within a SELECT statement to retrieve data based on the results of another query.

True / False

The GROUP BY clause in Oracle SQL is used to aggregate data and summarize it by one or more columns.

True False

The GROUP BY clause is used to group rows that have the same values in specified columns and allows aggregate functions like SUM, COUNT, and AVG to be applied to each group.

True / False

Oracle SQL supports both inner joins and outer joins to combine rows from two or more tables.

True False

Oracle SQL supports various types of joins, including inner joins and outer joins (left, right, and full outer joins), to combine rows from multiple tables based on related columns.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes topics on advanced SQL queries and performance tuning.

True False

The OCP certification exam covers a range of topics, including advanced SQL queries, optimization techniques, and performance tuning, reflecting the candidate's deep understanding of SQL in Oracle Database.

Q11

Single Choice

What does SQL stand for in Oracle?

Structured Query Language Standard Query Language Sequential Query LanguageSystem Query Language

SQL stands for Structured Query Language, which is used to communicate with and manipulate databases.

Q12

Single Choice

Which Oracle SQL statement is used to retrieve data from a database?

FETCH GET SELECTRETRIEVE

The SELECT statement is used to retrieve data from one or more tables in a database.

Q13

Single Choice

In Oracle SQL, which keyword is used to remove duplicate rows from the result set?

UNIQUE DISTINCT DIFFERENTSEPARATE

The DISTINCT keyword is used to remove duplicate rows from the result set.

Q14

Single Choice

Which of the following is a valid Oracle SQL statement to update data in a table?

SQL Code

UPDATE employees SET salary = salary + 500 WHERE employee_id = 101;

MODIFY employees SET salary = salary + 500 WHERE employee_id = 101; UPDATE employees SET salary = salary + 500 WHERE employee_id = 101; CHANGE employees SET salary = salary + 500 WHERE employee_id = 101;ALTER employees SET salary = salary + 500 WHERE employee_id = 101;

The UPDATE statement is used to modify existing data in a table, where specific rows are updated based on the condition provided.

Q15

Single Choice

In Oracle SQL, what is the purpose of the GROUP BY clause?

SQL Code

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

To filter rows based on a condition. To sort rows in ascending or descending order. To group rows that have the same values in specified columns.To join two tables on a common column.

The GROUP BY clause is used to group rows that share the same values in one or more columns, often used with aggregate functions like COUNT.

Q16

Single Choice

Which Oracle SQL clause is used to filter records in a SELECT query?

WHERE HAVING FILTERLIMIT

The WHERE clause is used to filter records based on a specified condition in a SELECT query.

Q17

Single Choice

What does the following Oracle SQL query do?

SQL Code

SELECT employee_id, first_name, last_name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 50);

Selects all employees with a salary greater than any employee in department 50. Selects all employees in department 50. Selects employees whose salary is greater than the highest salary in department 50.Selects employees whose salary is less than the salary of employees in department 50.

The ALL operator in the subquery compares the salary of each employee against all salaries in department 50, returning those with a salary greater than the highest in that department.

Q18

Single Choice

Which of the following SQL statements in Oracle uses a subquery to return employees with the same job as employee 101?

SQL Code

SELECT employee_id, first_name, last_name FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 101);

Returns all employees with the same job as employee 101. Returns only employee 101. Returns employees with a different job than employee 101.Returns all employees in the same department as employee 101.

The subquery selects the job ID of employee 101, and the main query returns all employees who have that same job ID.

Q19

Single Choice

What will be the result of the following Oracle SQL query?

SQL Code

SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;

Displays all departments where the maximum salary is exactly $10,000. Displays departments where the highest salary is more than $10,000. Displays the sum of salaries in each department.Displays employees earning more than $10,000 in each department.

The HAVING clause filters the groups created by GROUP BY, returning only those departments where the maximum salary exceeds $10,000.

Q20

Single Choice

Which Oracle SQL keyword is used to combine the results of two queries while removing duplicate rows?

UNION ALL INTERSECT UNIONJOIN

The UNION keyword combines the results of two queries and removes duplicate rows. UNION ALL would keep all duplicates.

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