database management system: SQL operators

database management system: SQL operators

1. Arithmetic Operators Comparison Operators Logical Operators 5/14/2024 SQL Commands DML and DQL 1 Operators in SQL

2. Addition (+) Subtraction (-) Multiplication (*) Division ( / ) 5/14/2024 SQL Commands DML and DQL 2 Arithmetic Operators

3. Arithmetic Operators (+, -, *, / ) Arithmetic operators in SQL are used to perform mathematical operations on numerical values within SQL queries. These operators allow you to manipulate numeric data stored in the database tables. 5/14/2024 SQL Commands DML and DQL 3

4. Addition (+) Used to add the values of numeric Columns with in the select statement 5/14/2024 SQL Commands DML and DQL 4 SELECT Column1 + Column2 + Column3 From tblName Where Condition ;

5. Example: C alculate the total price by adding the list price and the tax amount for each product. Here the weight indicates the Tax Amount. 5/14/2024 SQL Commands DML and DQL 5

6. Subtraction ( - ) Used to subtract the values of numeric Columns with in the select statement Syntax: 5/14/2024 SQL Commands DML and DQL 6 SELECT Column1 - Column2 From tblName Where Condition ;

7. Subtraction ( - ) Example: To calculate the profit margin for each product by subtracting the cost from the selling price : Use Adventure Workers LT 2022 for this example 5/14/2024 SQL Commands DML and DQL 7 SELECT ProductID , Name AS ProductName , ListPrice AS SellingPrice , StandardCost AS Cost , ( ListPrice - StandardCost ) AS ProfitMargin FROM SalesLT . Product ;

8. Multiplication (*) It provides the multiplied result of two numeric columns each rows with in the select statement Syntax: 5/14/2024 SQL Commands DML and DQL 8 SELECT Column1 * Column2 From tblName Where Condition ;

9. Multiplication (*) Example: To calculate the total revenue by multiplying the unit price with the quantity sold for each order item: 5/14/2024 SQL Commands DML and DQL 9 SELECT SalesOrderDetailID , ProductID , OrderQty AS Quantity , UnitPrice , ( OrderQty * UnitPrice ) AS TotalRevenue FROM SalesLT . SalesOrderDetail ;

10. Multiplication Example: Calculate the Total Price of each Dispensed Gallons by using Price Column Values of Prices table and GallonDispensed column Values of Transaction Table. 5/14/2024 SQL Commands DML and DQL 10 USE GasStationDB ; SELECT t . GallonsDispensed * p . Price as 'Total Price' from Transactions as t , Prices as p Where p . FuelTypeID = t . FuelTypeID ;

11. Division ( / ) It is used to divide the numeric column value by another numeric column value in the select statement Syntax: 5/14/2024 SQL Commands DML and DQL 11 SELECT Column1 / Column2 From tblName Where Condition ;

12. Division (/) Example: Calculate the Unit Price per Gallon of each transactions in the Transactions table by dividing the AmountPaid by Gallon Dispensed Column. 5/14/2024 SQL Commands DML and DQL 12 SELECT t . AmountPaid / t . GallonsDispensed as 'Price Per Gallon' From Transactions as t ;

13. Greater Than (>) Less Than (<) Equal to (=) Not Equal (<> or != ) 5/14/2024 SQL Commands DML and DQL 13 Comparison Operators

14. Comparison Operators It is used in select statement with Where clause to specify certain Condition It returns a Boolean value (True or False) General Syntax: 5/14/2024 SQL Commands DML and DQL 14 SELECT * FROM tblName WHERE column1 [ > ] | [ < ] | [ >= ] | [ <= ] | [ = ] | [ != or <> ] value ;

15. Example : Display all transactions that has amount paid values of 49.20. 5/14/2024 SQL Commands DML and DQL 15 USE GasStationDB ; select * from Transactions where AmountPaid = 49.2 ;

16. AND OR BETWEEN NOT IN EXISTS ALL NOT IN LIKE 5/14/2024 SQL Commands DML and DQL 16 Logical Operators

17. AND Syntax 5/14/2024 SQL Commands DML and DQL 17 SELECT * FROM table_name WHERE condition1 AND condition2 ;

18. OR Syntax 5/14/2024 SQL Commands DML and DQL 18 SELECT * FROM table_name WHERE condition1 OR condition2 ;

19. BETWEEN Syntax 5/14/2024 SQL Commands DML and DQL 19 SELECT * FROM table_name WHERE Column1 BETWEEN Value1 AND Value2 ;

20. NOT Syntax 5/14/2024 SQL Commands DML and DQL 20 SELECT * FROM table_name WHERE NOT condition ;

21. LIKE Syntax 5/14/2024 SQL Commands DML and DQL 21 SELECT * FROM table_name WHERE Column1 LIKE 'Pattern' ;

22. EXISTS Syntax 5/14/2024 SQL Commands DML and DQL 22 SELECT * FROM table_name WHERE EXISTS(SELECT * FROM tblName2 WHERE Condition )

23. ALL Syntax 5/14/2024 SQL Commands DML and DQL 23 SELECT * FROM table_name WHERE Column1 >= ALL(SELECT Column2 FROM tblName2 )