PostgreSQL

In PostgreSQL, Logical Operators are used to combine multiple conditions in a query, allowing for more complex and refined data retrieval. These operators are crucial for filtering data based on multiple criteria, making them an essential part of writing effective queries.

Here's a brief overview of logical operators and how they can be used with SQL queries:

1. AND Operator
   • The AND operator is used to combine multiple conditions in a WHERE clause. All conditions connected by AND must be true for a row to be included in the result set.
   • Example: Retrieve customers with a balance greater than $5000 and who have an active account.

     SELECT * FROM customers
     WHERE customer_id IN (
         SELECT customer_id FROM accounts
         WHERE balance > 5000
     ) AND EXISTS (
         SELECT 1 FROM accounts
         WHERE customer_id = customers.customer_id
         AND status = 'active'
     );
     

2. OR Operator
   • The OR operator is used to connect multiple conditions where at least one of them must be true for a row to be included.
   • Example: Find customers who either have a balance greater than $5000 or have made a transaction in the last month.

     SELECT * FROM customers
     WHERE customer_id IN (
         SELECT customer_id FROM accounts
         WHERE balance > 5000
     ) OR customer_id IN (
         SELECT customer_id FROM transactions
         WHERE transaction_date >= CURRENT_DATE - INTERVAL '1 month'
     );
     

3. NOT Operator
   • The NOT operator negates a condition, returning rows where the condition is false.
   • Example: Retrieve customers who do not have any transactions in the past 6 months.

     SELECT * FROM customers
     WHERE customer_id NOT IN (
         SELECT customer_id FROM transactions
         WHERE transaction_date >= CURRENT_DATE - INTERVAL '6 months'
     );
     

4. Combining Logical Operators
   • Logical operators can be combined to form more complex conditions. Use parentheses to ensure the correct order of operations.
   • Example: Find customers who have a balance greater than $5000 and either have an active account or made a transaction in the last 3 months.

     SELECT * FROM customers
     WHERE balance > 5000
     AND (
         EXISTS (
             SELECT 1 FROM accounts
             WHERE customer_id = customers.customer_id
             AND status = 'active'
         ) OR customer_id IN (
             SELECT customer_id FROM transactions
             WHERE transaction_date >= CURRENT_DATE - INTERVAL '3 months'
         )
     );
     

   These examples demonstrate how logical operators can be used to refine and combine conditions in SQL queries, allowing for more tailored data retrieval.

5. To gain complete access, login with gmail or outlook, no need of signup. click here

   

   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';

   Try it now

   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';

   Try it now

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

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

   Try it now

   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;

   Try it now

   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;

   Try it now

   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);

   Try it now

   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);

   Try it now