Operators

 

 

Arithmetic Operators:

1. Basic Arithmetic Operations in SQL:

   - Addition (+), Subtraction (-), Multiplication (), Division (/):

     ```sql

     SELECT salary, salary  1.1 AS increased_salary FROM employees;

     ```

   - Modulus (%):

     ```sql

     SELECT employee_id, salary % 2 AS is_even FROM employees;

     ```

 

2. Use of Operators in SELECT Statements for Calculations:

   - Utilizing arithmetic operations to calculate derived values.

   ```sql

   SELECT product_name, (unit_price  quantity) AS total_price FROM order_items;

   ```

 

3. Examples Demonstrating Arithmetic Operators:

   - Scenario:

     - Calculating the area of a circle.

     ```sql

     SELECT radius, PI() * POW(radius, 2) AS area FROM circles;

     ```

 

Comparison Operators:

4. Overview of Comparison Operators:

   - Equality (=, <>):

     ```sql

     SELECT * FROM employees WHERE department_id = 101;

     ```

   - Inequality (<, >, <=, >=):

     ```sql

     SELECT * FROM products WHERE price >= 50;

     ```

 

5. Application of Comparison Operators in WHERE Clauses:

   - Filtering data based on specified conditions.

   ```sql

   SELECT * FROM orders WHERE order_date > '2024-01-01';

   ```

 

6. Understanding NULL Values in Comparisons:

   - Dealing with the nuances of NULL values in comparisons.

   ```sql

   SELECT * FROM customers WHERE last_purchase_date IS NULL;

   ```

 

Logical Operators:

7. AND, OR, and NOT Operators for Combining Conditions:

   - AND Operator:

     ```sql

     SELECT * FROM employees WHERE department_id = 101 AND salary > 50000;

     ```

   - OR Operator:

     ```sql

     SELECT * FROM employees WHERE department_id = 101 OR department_id = 102;

     ```

   - NOT Operator:

     ```sql

     SELECT * FROM employees WHERE NOT department_id = 101;

     ```

 

8. Crafting Complex Conditions Using Logical Operators:

   - Combining multiple conditions for precise data retrieval.

   ```sql

   SELECT * FROM products WHERE (category = 'Electronics' AND price > 100) OR (category = 'Clothing' AND stock_quantity > 50);

   ```

 

9. Truth Tables to Understand the Behavior of Logical Operators:

   - Breaking down the outcomes of logical combinations.

   ```sql

   -- Truth table for AND

   SELECT a, b, a AND b AS result FROM truth_table_data;

   ```

 

Bitwise Operators:

10. Introduction to Bitwise Operators in SQL:

    - Bitwise AND (&), Bitwise OR (|), Bitwise XOR (^):

      ```sql

      SELECT column1, column2, column1 & column2 AS bitwise_and_result FROM bitwise_data;

      ```

 

11. Application of Bitwise Operators in Data Manipulation:

    - Manipulating binary data using bitwise operators.

    ```sql

    UPDATE flags_table SET status = status | 4 WHERE user_id = 101;

    ```

 

12. Use Cases Where Bitwise Operators are Beneficial:

    - Scenario:

      - Storing and manipulating user permissions using bitwise operators.

 

Assignment Operators:

13. Explanation of Assignment Operators:

    - Simple Assignment (=):

      ```sql

      SET @counter = 1;

      ```

    - Compound Assignment (+=, -=, =, /=):

      ```sql

      UPDATE inventory SET quantity_available -= 10 WHERE product_id = 201;

      ```

 

14. Practical Examples Showcasing the Use of Assignment Operators:

    - Scenario:

      - Incrementing product ratings using a compound assignment operator.

      ```sql

      UPDATE product_ratings SET rating_count += 1 WHERE product_id = 301;

      ```

 

Key Takeaways:

- Precision in Data Manipulation: Learn to use arithmetic, comparison, logical, bitwise, and assignment operators for precise data manipulation.

- Efficient Data Retrieval: Master crafting complex conditions for efficient data retrieval.

- Real-world Application: Understand how operators are applied in real-world scenarios for enhanced database management.

 

Congratulations on completing our comprehensive SQL and MySQL course! You've gained the knowledge and skills to navigate the intricate world of databases and data manipulation. Keep practicing, keep querying, and may your SQL adventures continue to unlock new possibilities in your data-driven journey. Happy querying!