Oracle

Chapter 7 - DQL (Data Query Language)

Logical Operators

Logical operators in Oracle DQL are used to combine multiple conditions in a query. They help in filtering records based on specific criteria, allowing for more complex queries. The most common logical operators are AND, OR, and NOT. Understanding these operators is crucial for crafting precise queries and retrieving the desired data from your database.

Common Logical Operators
- AND: Returns true if both conditions are true.
- OR: Returns true if at least one of the conditions is true.
- NOT: Reverses the result of a condition; true becomes false and vice versa.

Using Logical Operators in SQL Queries

To combine multiple conditions, you can use the logical operators in your SQL statements. Here are some examples using tables related to authors, books, library, membership, and rentals:

SELECT *
FROM books
WHERE author_id = 1 AND published_year > 2010;

SELECT *
FROM library
WHERE location = 'Downtown' OR location = 'Uptown';

SELECT *
FROM membership
WHERE NOT status = 'inactive';

Combining Logical Operators
- You can combine multiple logical operators for more complex conditions. Parentheses can be used to group conditions:
```
SELECT *
FROM rentals
WHERE (member_id = 2 AND book_id = 5) OR (member_id = 3 AND return_date IS NULL);
```
Best Practices
- Always use parentheses to clarify the order of evaluation in complex queries.
- Avoid using multiple logical operators without grouping, as it can lead to confusion and errors.
- Test your queries incrementally to ensure they return the expected results.

By mastering logical operators, you can effectively manipulate your data retrieval processes and enhance your SQL query capabilities.

Tansy SQL Course | Logical Operators | Chapter 7 | Lesson 34 - Video Thumbnail

TEST CODE

To choose male customers located in the state of New York, use the following query:

SELECT *
FROM org_client
WHERE gender = 'M'
AND state = 'NY';

To select customers either from the state of Pennsylvania or the city of Miami, use the following query:

SELECT *
FROM org_client
WHERE city = 'Miami'
OR state = 'PA';

To fetch customers who do not live in the state of New York, use the following query:

SELECT *
FROM org_client
WHERE state <> 'NY';

To find married clients whose credit limit is higher than the credit limit of every unmarried client, use the following query:

SELECT *
FROM org_client
WHERE credit_limit > ALL (SELECT credit_limit FROM org_client WHERE married_flag = 0)
AND married_flag = 1;

To find single female clients born in the same year as any single male clients, use the following query:

SELECT *
FROM org_client
WHERE birth_year = ANY (SELECT birth_year FROM org_client WHERE gender = 'M' AND married_flag = 0)
AND gender = 'F'
AND married_flag = 0;

To find all customers who have placed an order, use the following query:

SELECT *
FROM org_client
WHERE EXISTS (SELECT 1 FROM act_order WHERE org_client.client_id = act_order.client_id);

To fetch all rows from the clients table where the birth year column matches any of the specified values (1990, 1980), use the following query:

SELECT *
FROM org_client
WHERE birth_year IN (1990, 1980);

AND operator

SELECT *
FROM org_client
WHERE gender = 'M'
AND state = 'NY';

In the given image, the green hue indicates the data that has been chosen or meets the criteria specified in our query. When using the AND operator, both column values must satisfy the specified criteria.

OR operator

SELECT *
FROM org_client
WHERE city = 'Miami'
OR state = 'PA';

In the given image, the green hue indicates the data that has been chosen or meets the criteria specified in our query. In the case of the OR operator, at least one of the column values needs to meet the specified criteria.

SQL ALL Clause

SELECT * FROM org_client
WHERE credit_limit > ALL (SELECT credit_limit FROM org_client WHERE married_flag = 0)
AND married_flag = 1;

In this context, we are focused on identifying married couples whose credit limit is higher than the highest credit limit among bachelors. The bachelors and their respective credit limits are displayed on the right side, arranged in descending order with the top credit limit being 15,000. Therefore, for married clients to have a higher credit rating than all bachelors, they must exceed this top credit limit. Rows with a green background on the left represent the data of married couples that fulfill our criteria.

SQL ANY Clause

SELECT * FROM org_client
WHERE birth_year = ANY (SELECT birth_year FROM org_client WHERE gender = 'M' AND married_flag = 0)
AND gender = 'F'
AND married_flag = 0;

In this case, our goal is to find single female clients who share the same birth year as any of the single male clients. The data for single females, along with their birth years, is displayed on the left, while the information for single males is listed on the right. Single female clients whose data is highlighted with a green background are those whose birth year matches with that of a single male from the right side.

SQL EXISTS Clause

SELECT * FROM org_client
WHERE EXISTS (SELECT 1 FROM act_order WHERE org_client.client_id = act_order.client_id);

In this scenario, we aim to list clients who have made at least one order. Referring to the image, the clients from the 'clients' table, highlighted with a green background, meet our criteria, as they have placed one or more orders in the 'orders' table on the right. Conversely, clients marked with a red background are those who haven't placed any orders in the table on the right.

IN OPERATOR

SELECT *
FROM org_employee
WHERE designation IN ('Sales Manager', 'Financial Analyst', 'CFO');

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Previous Next

Comments(0 comments)

Comments Not Found