SQL Multiplication Operator

SQL Multiplication Operator

The SQL * (multiplication) operator is used to multiply numeric values. It is commonly used in SQL queries to calculate derived values such as total prices, salaries, or other computed metrics.

In this tutorial, we will explore the SQL multiplication operator, its syntax, and real-world applications with detailed examples.

---

Syntax of SQL Multiplication Operator

The basic syntax for using the multiplication operator in SQL is:

SELECT column1 * column2 AS result
FROM table_name;

Alternatively, it can be used directly in a calculation:

SELECT 10 * 5 AS result;

These queries return the product of the specified values or columns.

---

Step-by-Step Examples Using SQL Multiplication Operator

1. Calculating Total Price of Products

Let’s create a products table and use the multiplication operator to calculate total cost.

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    price DECIMAL(10,2),
    quantity INT
);

Insert sample records:

INSERT INTO products (name, price, quantity)
VALUES 
('Laptop', 750.00, 2),
('Mobile', 500.00, 3),
('Tablet', 300.00, 4);

Now, calculate the total price for each product:

SELECT name, price, quantity, (price * quantity) AS total_price
FROM products;

Explanation:

• price * quantity calculates the total price for each product.
• The result is displayed under the alias total_price.
• For example, for the laptop: 750.00 * 2 = 1500.00.

---

2. Calculating Employee Annual Salary

Let’s create an employees table to calculate the annual salary using the multiplication operator.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    monthly_salary DECIMAL(10,2)
);

Insert sample records with names like Arjun, Ram, John, and Roy:

INSERT INTO employees (name, monthly_salary)
VALUES 
('Arjun', 5000.00),
('Ram', 4500.00),
('John', 5200.00),
('Roy', 4800.00);

Now, calculate the annual salary (assuming 12 months of salary):

SELECT name, monthly_salary, (monthly_salary * 12) AS annual_salary
FROM employees;

Explanation:

• The multiplication operator * calculates the annual salary by multiplying the monthly salary by 12.
• For example, Arjun’s annual salary: 5000 * 12 = 60000.

---

Conclusion

In this tutorial for SQL multiplication operator, we covered:

1. How to use the Multiplication operator * in SQL queries.
2. Multiplication in calculations for pricing and salaries.
3. Step-by-step examples with product and employee data.