PL/SQL AND Operator

The PL/SQL AND operator is used to combine multiple conditions in a WHERE clause of an SQL query. It allows you to refine your query by ensuring that all specified conditions are met.

AND queries which help in filtering data more precisely and can be crucial for retrieving accurate results from a database. In this article, we will explore the concept of PL/SQL AND Operator with its syntax, examples, and output.

PL/SQL AND Operator

The AND operator is used to connect two or more conditions in a WHERE clause. All conditions connected by the AND clause must be true for a record to be included in the result set.

This operator is essential for queries that need to satisfy multiple criteria simultaneously.

Syntax:

SELECT column1, column2, ...

FROM table_name

WHERE condition1 AND condition2;

Explanation :

• SELECT column1, column2, ...: Specifies the columns to be fetched from the table. You can list one or more column names separated by commas.
  
• FROM table_name: Indicates the table from where the data is retrieved. This is where the columns specified in the SELECT clause is located.
  
• WHERE condition1 AND condition2: Filters the rows based on specified conditions. Only rows that meet all the conditions will be included in the result set. The AND operator ensures that both conditions must be true for a row to be selected.

Multiple Conditions:

SELECT column1, column2, ...

FROM table_name

WHERE condition1 AND condition2 AND condition3;

Explanation :

• The SQL SELECT statement with multiple conditions allows you to filter data based on several criteria.
  
• Using AND between conditions means that each condition must be true for a row to be included in the result set.
  
• For example, WHERE condition1 AND condition2 AND condition3 ensures that only records meeting all specified conditions are selected.

Combining with Other Operators:

SELECT column1, column2, ...

FROM table_name WHERE

condition1 AND (condition2 OR condition3);

Explanation :

• In SQL, combining AND with OR allows for more nuanced filtering in queries.
  
• By using parentheses, we can group conditions to specify that a row must meet one condition while satisfying at least one of the other conditions.
  
• For example, WHERE condition1 AND (condition2 OR condition3) selects rows where condition1 is true and either condition2 or condition3 is true.
  

Example of PL/SQL AND Operator

In this PL/SQL example, two tables are created: products, which stores information about various products including their IDs, names, and prices, and sales, which records transactions with details like sale IDs, product IDs, sale dates, and quantities sold.

By using these tables, you can perform complex queries to analyze sales data and product information, demonstrating the application of the AND operator to filter records based on multiple conditions.

Table 1: products

The provided SQL commands create a table named products with columns for product_id, product_name, and price. The table is then populated with five rows, each representing a different product with its unique identifier, name, and price.

Query:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2)
);

INSERT INTO products (product_id, product_name, price) VALUES (1, 'Laptop', 800.00);
INSERT INTO products (product_id, product_name, price) VALUES (2, 'Smartphone', 500.00);
INSERT INTO products (product_id, product_name, price) VALUES (3, 'Tablet', 300.00);
INSERT INTO products (product_id, product_name, price) VALUES (4, 'Headphones', 100.00);
INSERT INTO products (product_id, product_name, price) VALUES (5, 'Monitor', 250.00);

Output:

Explanation:

• The table products now holds information about five different products, each with a unique ID, name, and price.
  
• The product_id serves as the primary key, ensuring each product's record is unique.
  
• The product_name provides descriptive names, while the price column specifies the cost of each product.

Table 2: sales

The provided SQL commands create a table named sales with columns for sale_id, product_id, sale_date, and quantity. The table is populated with five rows, each representing a sales transaction with its unique ID, associated product ID, sale date, and quantity sold.

Query:

CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    quantity INT
);

INSERT INTO sales (sale_id, product_id, sale_date, quantity) VALUES (1, 1, '2024-08-01', 10);
INSERT INTO sales (sale_id, product_id, sale_date, quantity) VALUES (2, 2, '2024-08-05', 5);
INSERT INTO sales (sale_id, product_id, sale_date, quantity) VALUES (3, 3, '2024-08-10', 15);
INSERT INTO sales (sale_id, product_id, sale_date, quantity) VALUES (4, 4, '2024-08-15', 20);
INSERT INTO sales (sale_id, product_id, sale_date, quantity) VALUES (5, 5, '2024-08-20', 8);

Output:

Explanation:

• The sales table captures transactions for various products, each uniquely identified by sale_id.
  
• The product_id column links each sale to a specific product, while sale_date records when the sale occurred.
  
• The quantity column indicates the number of units sold in each transaction.
  
• This table helps track sales data and correlate it with product information for analysis.

Example 1: Retrieve Products with Price Above 250 and Quantity Sold Greater Than 5

This query combines conditions to filter products that have a price greater than 250 and have been sold in quantities greater than 5. It joins two tables, products and sales, to ensure that both conditions are met before retrieving the results.

Query:

SELECT p.product_name, p.price
FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE p.price > 250 AND s.quantity > 5;

Output:

Explanation :

• The result includes products that are priced above 250 and have been sold in quantities greater than 5.
  
• This typically includes high-value items with significant sales.

Example 2: Find Sales on Specific Date with Quantity Sold Greater Than 10

This query filters sales records that occurred on a specific date (e.g., August 15, 2024) and where the quantity sold exceeds 10. It checks two conditions: the exact sale date and the quantity of items sold.

Query:

SELECT s.sale_id, s.product_id, s.sale_date, s.quantity
FROM sales s
WHERE s.sale_date = '2024-08-15' AND s.quantity > 10;

Output:

Explanation:

• The result shows sales records from a specific date where the quantity sold is more than 10.
  
• This output highlights sales transactions meeting both criteria.

Example 3: Get Products Sold Between a Range of Dates and Price Below 300

This query selects products sold within a specific date range (e.g., between August 1 and August 10, 2024) and with a price below 300. It combines conditions on the sale date and product price to refine the results.

Query:

SELECT p.product_name, p.price
FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE s.sale_date BETWEEN '2024-08-01' AND '2024-08-10' AND p.price < 300;

Output:

Explanation:

• The result includes products sold within the specified date range with a price below 300.
  
• This output is filtered to show products that fit both the time frame and price criteria.

Example 4: Find Sales with Quantity Greater Than 10 and Product Name Containing 'Phone'

This query retrieves sales records where the quantity sold is greater than 10 and the product name includes 'Phone'. It joins the sales and products tables and applies both conditions to filter the data.

Query:

SELECT s.sale_id, p.product_name, s.quantity
FROM sales s
JOIN products p ON s.product_id = p.product_id
WHERE s.quantity > 10 AND p.product_name LIKE '%Phone%';

Output:

Explanation :

• The result displays sales records where the quantity is greater than 10 and the product name contains 'Phone'.
  
• This output focuses on high-quantity sales of products with 'Phone' in their names.

Conclusion

The PL/SQL AND operator is essential for combining multiple conditions in SQL queries. It ensures that only records meeting all specified criteria are retrieved. This operator enhances query precision and helps in obtaining more accurate results from your database.