Test Your Oracle 'AND' and 'OR' Operators Knowledge

Progress

True / False

In Oracle, the AND operator is used to combine multiple conditions in a WHERE clause where all conditions must be true.

True False

The AND operator combines multiple conditions, and all conditions must be true for the row to be included in the result set.

True / False

The OR operator in Oracle is used to combine multiple conditions in a WHERE clause where at least one condition must be true.

True False

The OR operator combines multiple conditions, and at least one condition must be true for the row to be included in the result set.

True / False

In Oracle, the AND operator has higher precedence than the OR operator.

True False

The AND operator has higher precedence than the OR operator, meaning it is evaluated before the OR operator in a compound condition.

True / False

In Oracle, you can use parentheses to change the order of evaluation of AND and OR operators in a WHERE clause.

True False

Parentheses can be used to explicitly define the order of evaluation, overriding the default precedence of the AND and OR operators.

True / False

The AND operator can be used to combine conditions in both WHERE and HAVING clauses in Oracle.

True False

The AND operator can be used to combine conditions in the WHERE clause (for filtering rows) and the HAVING clause (for filtering groups).

True / False

In Oracle, combining AND and OR operators without parentheses can lead to ambiguous and unexpected results.

True False

Without parentheses, the default precedence may not match the intended logic, leading to ambiguous or unexpected query results.

True / False

In Oracle, the AND operator can be used within a JOIN condition to specify multiple join criteria.

True False

The AND operator can be used in JOIN conditions to specify multiple criteria that must all be met for the join to succeed.

True / False

The OR operator can be used in Oracle to check if a value is within a range of values without using BETWEEN.

True False

The OR operator can be used to manually check if a value falls within a range by specifying multiple conditions, though BETWEEN is usually more concise.

True / False

In Oracle, the AND and OR operators can be used in a CASE statement to define complex conditions.

True False

The AND and OR operators can be used in a CASE statement to create complex conditional logic within the CASE expression.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using the AND and OR operators efficiently and understanding their impact on query performance and logic.

True False

The OCP certification covers advanced topics, including the efficient use of the AND and OR operators and understanding their impact on query performance and logical outcomes in SQL queries.

Q11

Single Choice

What is the purpose of the AND operator in Oracle SQL?

To combine two strings To join two tables To ensure both conditions in a query are trueTo sort data in ascending order

The AND operator is used in Oracle SQL to combine two or more conditions, and it returns rows only when all conditions are true.

Q12

Single Choice

What is the purpose of the OR operator in Oracle SQL?

To ensure either of the conditions in a query is true To perform mathematical operations To filter rows based on a specific conditionTo compare two columns for equality

The OR operator in Oracle SQL returns rows when at least one of the specified conditions is true.

Q13

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

SELECT * FROM employees WHERE department_id = 10 AND salary > 5000;

Employees with a salary less than 5000 Employees in department 10 with a salary greater than 5000 Employees in any department with a salary greater than 5000Employees in department 10 with any salary

The query uses the AND operator to filter rows where both conditions—department_id = 10 and salary > 5000—are true.

Q14

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

SELECT * FROM orders WHERE order_date > '01-JAN-2023' OR order_status = 'Pending';

Orders placed after January 1, 2023, or orders with a status of 'Pending' Orders placed before January 1, 2023 Orders with a status other than 'Pending'Orders placed on January 1, 2023

The OR operator ensures that the query returns orders where either condition—order_date > '01-JAN-2023' or order_status = 'Pending'—is true.

Q15

Single Choice

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

SQL Code

SELECT * FROM products WHERE category_id = 1 AND (price < 100 OR stock > 50);

Products in category 1 with a price less than 100 and stock greater than 50 Products in category 1 with a price less than 100 or stock greater than 50 Products in any category with a price less than 100 and stock greater than 50Products in category 1 with a price greater than 100

The AND and OR operators are combined, so the query returns products in category_id = 1 where either price < 100 or stock > 50 is true.

Q16

Single Choice

Consider the following Oracle SQL query:How will this query behave if parentheses are not used?

SQL Code

SELECT * FROM customers WHERE city = 'New York' OR city = 'Los Angeles' AND orders > 10;

It returns customers from New York or Los Angeles with more than 10 orders It returns customers from New York with any number of orders or customers from Los Angeles with more than 10 orders It returns customers from New York or Los Angeles regardless of the number of ordersIt returns only customers with more than 10 orders

Without parentheses, AND has higher precedence than OR, so the query first evaluates city = 'Los Angeles' AND orders > 10, and then includes customers from New York.

Q17

Single Choice

What happens when both AND and OR operators are used in an Oracle SQL query without parentheses?

The query raises an error The AND condition is evaluated first The OR condition is evaluated firstThe query ignores the AND condition

In Oracle SQL, the AND operator has higher precedence than OR, so AND conditions are evaluated before OR unless parentheses are used to override this precedence.

Q18

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

SELECT * FROM employees WHERE (department_id = 20 AND salary > 6000) OR (department_id = 10 AND salary < 4000);

Employees in department 20 with a salary greater than 6000 or in department 10 with a salary less than 4000 Employees in department 10 with a salary greater than 6000 Employees in any department with a salary greater than 6000Employees in department 20 with a salary less than 4000

The query uses parentheses to combine conditions, returning employees who meet either the conditions for department 20 and a high salary or department 10 and a low salary.

Q19

Single Choice

How can the use of AND and OR operators affect query performance in Oracle?

It does not affect performance Combining many OR conditions can lead to slower queries compared to AND Combining many AND conditions always makes the query fasterThe performance is the same regardless of how AND and OR are used

Using multiple OR conditions can slow down queries because Oracle must evaluate each condition separately, whereas AND conditions can often be optimized more effectively.

Q20

Single Choice

In an Oracle certification exam, you encounter the following SQL statement:What does this query return?

SQL Code

SELECT product_id, product_name FROM products WHERE (category_id = 1 AND price > 50) OR (category_id = 2 AND price < 20);

Products in category 1 or 2 with a price greater than 50 Products in category 1 with a price greater than 50 or in category 2 with a price less than 20 Products in category 1 or 2 with a price less than 20Products in category 2 with a price greater than 50

This query returns products that meet either condition: products in category_id = 1 with a price greater than 50, or products in category_id = 2 with a price less than 20.

Q21

Multiple Choice

Which SQL code snippet correctly uses the AND and OR operators to filter employees where the department is 'HR' and the salary is above 50000, or the department is 'IT' in Oracle?

SQL Code

SELECT *
FROM employees
WHERE department = 'HR' AND salary > 50000
OR department = 'IT';

SELECT *
FROM employees
WHERE (department = 'HR' AND salary > 50000)
OR department = 'IT';

SELECT employee_id, department, salary
FROM employees
WHERE department = 'HR' AND (salary > 50000 OR department = 'IT');

SELECT * FROM employees WHERE department = 'HR' AND salary > 50000 OR department = 'IT'; SELECT * FROM employees WHERE (department = 'HR' AND salary > 50000) OR department = 'IT'; SELECT employee_id, department, salary FROM employees WHERE department = 'HR' AND (salary > 50000 OR department = 'IT');SELECT * FROM employees WHERE department = 'IT' AND salary > 50000 OR department = 'HR';

Options A and B correctly use the AND and OR operators to filter employees where the department is 'HR' and the salary is above 50000, or the department is 'IT'. Option C incorrectly uses parentheses.

Q22

Multiple Choice

Which SQL code snippet demonstrates how to use the AND and OR operators to filter orders where the status is 'Shipped' and the amount is above 1000, or the customer is 'VIP' in Oracle?

SQL Code

SELECT *
FROM orders
WHERE status = 'Shipped' AND amount > 1000
OR customer = 'VIP';

SELECT *
FROM orders
WHERE (status = 'Shipped' AND amount > 1000)
OR customer = 'VIP';

SELECT order_id, status, amount
FROM orders
WHERE status = 'Shipped' AND (amount > 1000 OR customer = 'VIP');

SELECT * FROM orders WHERE status = 'Shipped' AND amount > 1000 OR customer = 'VIP'; SELECT * FROM orders WHERE (status = 'Shipped' AND amount > 1000) OR customer = 'VIP'; SELECT order_id, status, amount FROM orders WHERE status = 'Shipped' AND (amount > 1000 OR customer = 'VIP');SELECT * FROM orders WHERE customer = 'VIP' AND status = 'Shipped' OR amount > 1000;

Options A and B correctly use the AND and OR operators to filter orders where the status is 'Shipped' and the amount is above 1000, or the customer is 'VIP'. Option C incorrectly uses parentheses.

Q23

Multiple Choice

Which SQL code snippet uses the AND and OR operators to filter products where the category is 'Electronics' and the price is between 100 and 500, or the stock is below 50 in Oracle?

SQL Code

SELECT *
FROM products
WHERE category = 'Electronics' AND price BETWEEN 100 AND 500
OR stock < 50;

SELECT *
FROM products
WHERE (category = 'Electronics' AND price BETWEEN 100 AND 500)
OR stock < 50;

SELECT product_id, category, price
FROM products
WHERE category = 'Electronics' AND (price BETWEEN 100 AND 500 OR stock < 50);

SELECT * FROM products WHERE category = 'Electronics' AND price BETWEEN 100 AND 500 OR stock < 50; SELECT * FROM products WHERE (category = 'Electronics' AND price BETWEEN 100 AND 500) OR stock < 50; SELECT product_id, category, price FROM products WHERE category = 'Electronics' AND (price BETWEEN 100 AND 500 OR stock < 50);SELECT * FROM products WHERE stock < 50 AND category = 'Electronics' OR price BETWEEN 100 AND 500;

Options A and B correctly use the AND and OR operators to filter products where the category is 'Electronics' and the price is between 100 and 500, or the stock is below 50. Option C incorrectly uses parentheses.

Q24

Multiple Choice

Which SQL code snippet demonstrates how to use the AND and OR operators to filter customers where the country is 'USA' and the age is above 30, or the membership is 'Gold' in Oracle?

SQL Code

SELECT *
FROM customers
WHERE country = 'USA' AND age > 30
OR membership = 'Gold';

SELECT *
FROM customers
WHERE (country = 'USA' AND age > 30)
OR membership = 'Gold';

SELECT customer_id, country, age
FROM customers
WHERE country = 'USA' AND (age > 30 OR membership = 'Gold');

SELECT * FROM customers WHERE country = 'USA' AND age > 30 OR membership = 'Gold'; SELECT * FROM customers WHERE (country = 'USA' AND age > 30) OR membership = 'Gold'; SELECT customer_id, country, age FROM customers WHERE country = 'USA' AND (age > 30 OR membership = 'Gold');SELECT * FROM customers WHERE membership = 'Gold' AND country = 'USA' OR age > 30;

Options A and B correctly use the AND and OR operators to filter customers where the country is 'USA' and the age is above 30, or the membership is 'Gold'. Option C incorrectly uses parentheses.

Q25

Multiple Choice

Which SQL code snippet uses the AND and OR operators to filter sales records where the region is 'East' and the sales amount is above 5000, or the salesperson is 'John' in Oracle?

SQL Code

SELECT *
FROM sales
WHERE region = 'East' AND sales_amount > 5000
OR salesperson = 'John';

SELECT *
FROM sales
WHERE (region = 'East' AND sales_amount > 5000)
OR salesperson = 'John';

SELECT sale_id, region, sales_amount
FROM sales
WHERE region = 'East' AND (sales_amount > 5000 OR salesperson = 'John');

SELECT * FROM sales WHERE region = 'East' AND sales_amount > 5000 OR salesperson = 'John'; SELECT * FROM sales WHERE (region = 'East' AND sales_amount > 5000) OR salesperson = 'John'; SELECT sale_id, region, sales_amount FROM sales WHERE region = 'East' AND (sales_amount > 5000 OR salesperson = 'John');SELECT * FROM sales WHERE salesperson = 'John' AND region = 'East' OR sales_amount > 5000;

Options A and B correctly use the AND and OR operators to filter sales records where the region is 'East' and the sales amount is above 5000, or the salesperson is 'John'. Option C incorrectly uses parentheses.

Q26

Multiple Choice

Which SQL code snippet demonstrates advanced use of the AND and OR operators to filter employees where the job title is 'Manager' and the department is 'Sales', or the hire date is before 2020 in Oracle?

SQL Code

SELECT *
FROM employees
WHERE job_title = 'Manager' AND department = 'Sales'
OR hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD');

SELECT *
FROM employees
WHERE (job_title = 'Manager' AND department = 'Sales')
OR hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD');

SELECT employee_id, job_title, department
FROM employees
WHERE job_title = 'Manager' AND (department = 'Sales' OR hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD'));

SELECT * FROM employees WHERE job_title = 'Manager' AND department = 'Sales' OR hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD'); SELECT * FROM employees WHERE (job_title = 'Manager' AND department = 'Sales') OR hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD'); SELECT employee_id, job_title, department FROM employees WHERE job_title = 'Manager' AND (department = 'Sales' OR hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD'));SELECT * FROM employees WHERE hire_date < TO_DATE('2020-01-01', 'YYYY-MM-DD') AND job_title = 'Manager' OR department = 'Sales';

Options A and B correctly use the AND and OR operators to filter employees where the job title is 'Manager' and the department is 'Sales', or the hire date is before 2020. Option C incorrectly uses parentheses.

Q27

Multiple Choice

Which SQL code snippet uses the AND and OR operators to filter orders where the order date is after 2021 and the status is 'Completed', or the total is above 10000 in Oracle?

SQL Code

SELECT *
FROM orders
WHERE order_date > TO_DATE('2021-01-01', 'YYYY-MM-DD') AND status = 'Completed'
OR total > 10000;

SELECT *
FROM orders
WHERE (order_date > TO_DATE('2021-01-01', 'YYYY-MM-DD') AND status = 'Completed')
OR total > 10000;

SELECT order_id, order_date, status
FROM orders
WHERE order_date > TO_DATE('2021-01-01', 'YYYY-MM-DD') AND (status = 'Completed' OR total > 10000);

SELECT * FROM orders WHERE order_date > TO_DATE('2021-01-01', 'YYYY-MM-DD') AND status = 'Completed' OR total > 10000; SELECT * FROM orders WHERE (order_date > TO_DATE('2021-01-01', 'YYYY-MM-DD') AND status = 'Completed') OR total > 10000; SELECT order_id, order_date, status FROM orders WHERE order_date > TO_DATE('2021-01-01', 'YYYY-MM-DD') AND (status = 'Completed' OR total > 10000);SELECT * FROM orders WHERE total > 10000 AND order_date > TO_DATE('2021-01-01', 'YYYY-MM-DD') OR status = 'Completed';

Options A and B correctly use the AND and OR operators to filter orders where the order date is after 2021 and the status is 'Completed', or the total is above 10000. Option C incorrectly uses parentheses.

Q28

Multiple Choice

Which SQL code snippet demonstrates advanced use of the AND and OR operators to filter customers where the city is 'New York' and the age is below 40, or the account type is 'Premium' in Oracle?

SQL Code

SELECT *
FROM customers
WHERE city = 'New York' AND age < 40
OR account_type = 'Premium';

SELECT *
FROM customers
WHERE (city = 'New York' AND age < 40)
OR account_type = 'Premium';

SELECT customer_id, city, age
FROM customers
WHERE city = 'New York' AND (age < 40 OR account_type = 'Premium');

SELECT * FROM customers WHERE city = 'New York' AND age < 40 OR account_type = 'Premium'; SELECT * FROM customers WHERE (city = 'New York' AND age < 40) OR account_type = 'Premium'; SELECT customer_id, city, age FROM customers WHERE city = 'New York' AND (age < 40 OR account_type = 'Premium');SELECT * FROM customers WHERE account_type = 'Premium' AND city = 'New York' OR age < 40;

Options A and B correctly use the AND and OR operators to filter customers where the city is 'New York' and the age is below 40, or the account type is 'Premium'. Option C incorrectly uses parentheses.

Q29

Multiple Choice

Which SQL code snippet uses the AND and OR operators to filter products where the brand is 'Apple' and the price is above 1000, or the warranty is '2 years' in Oracle?

SQL Code

SELECT *
FROM products
WHERE brand = 'Apple' AND price > 1000
OR warranty = '2 years';

SELECT *
FROM products
WHERE (brand = 'Apple' AND price > 1000)
OR warranty = '2 years';

SELECT product_id, brand, price
FROM products
WHERE brand = 'Apple' AND (price > 1000 OR warranty = '2 years');

SELECT * FROM products WHERE brand = 'Apple' AND price > 1000 OR warranty = '2 years'; SELECT * FROM products WHERE (brand = 'Apple' AND price > 1000) OR warranty = '2 years'; SELECT product_id, brand, price FROM products WHERE brand = 'Apple' AND (price > 1000 OR warranty = '2 years');SELECT * FROM products WHERE warranty = '2 years' AND brand = 'Apple' OR price > 1000;

Options A and B correctly use the AND and OR operators to filter products where the brand is 'Apple' and the price is above 1000, or the warranty is '2 years'. Option C incorrectly uses parentheses.

Q30

Multiple Choice

Which SQL code snippet demonstrates the certification-level use of the AND and OR operators to filter orders where the shipping date is before 2022 and the destination is 'California', or the payment status is 'Pending' in Oracle?

SQL Code

SELECT *
FROM orders
WHERE shipping_date < TO_DATE('2022-01-01', 'YYYY-MM-DD') AND destination = 'California'
OR payment_status = 'Pending';

SELECT *
FROM orders
WHERE (shipping_date < TO_DATE('2022-01-01', 'YYYY-MM-DD') AND destination = 'California')
OR payment_status = 'Pending';

SELECT order_id, shipping_date, destination
FROM orders
WHERE shipping_date < TO_DATE('2022-01-01', 'YYYY-MM-DD') AND (destination = 'California' OR payment_status = 'Pending');

SELECT * FROM orders WHERE shipping_date < TO_DATE('2022-01-01', 'YYYY-MM-DD') AND destination = 'California' OR payment_status = 'Pending'; SELECT * FROM orders WHERE (shipping_date < TO_DATE('2022-01-01', 'YYYY-MM-DD') AND destination = 'California') OR payment_status = 'Pending'; SELECT order_id, shipping_date, destination FROM orders WHERE shipping_date < TO_DATE('2022-01-01', 'YYYY-MM-DD') AND (destination = 'California' OR payment_status = 'Pending');SELECT * FROM orders WHERE payment_status = 'Pending' AND shipping_date < TO_DATE('2022-01-01', 'YYYY-MM-DD') OR destination = 'California';

Options A and B correctly use the AND and OR operators to filter orders where the shipping date is before 2022 and the destination is 'California', or the payment status is 'Pending'. Option C incorrectly uses parentheses.

Oracle Database Quiz Questions