There are three types of operators in SQL: Arithmetic Operators, String Operators and Logical Operators.

Operators are used in SQL to perform specific mathematical, logical or comparison operations on the records in a database.

 

1.Comparison operations (Greater than”>”, Less than “<”, Equal to “=”, Not equal to “!=” “<>”)

2. Arithmetic operations (Additions, subtractions, division and multiplications)

3. Logical operation or operational operations (And, Or, Between, In, Like, Not like, Is)

Comparison operations

Using the where clause to filter data

SELECT *

FROM [Product]

WHERE Cookies = 2

Highlight and execute

NOTE; Can use Where cookies (>, <, !=, etc)

EXAMPLES

       SELECT *

FROM [Fact].[Order]

(1). SELECT [city key],[Order Date Key] Quantity, [Unit Price], [Tax Amount]

FROM [Fact].[Order]

WHERE [Unit Price] > 200

(2). SELECT [Salesperson Key], Description, Quantity, [Unit Price],Profit

      FROM [Fact].[Sale]

      WHERE Profit < 500

(3). SELECT [Salesperson Key], Description, Quantity, [Unit Price],Profit,  [Delivery        Date Key]

     FROM [Fact].[Sale]

     WHERE [Delivery Date Key] != ‘1/1/2013’

 

USING ORDERBY TO SORT RECORDS

SELECT [City Key],[Order Date Key], Quantity, [Unit Price], [Tax Amount]

FROM [Fact].[Order]

ORDER BY [Tax Amount]

 

USING TOP 10 TO VIEW THE SELECTED NUMBERS OF FIRST 10 RECORDS IN A TABLE

SELECT TOP 10 *

FROM [Fact].[Order]

 

SELECT *

FROM [Dimension].[City]

SELECT [City Key],[State Province],City,Continent

FROM [Dimension].[City]

WHERE City <>’ Califomia’

 

Questions; using fact order table, returns items that were order only on the 31/5/2016

SELECT *

FROM [Fact].[Order]

 

SELECT *

FROM [Fact].[Order]

WHERE [Order Date Key]= ‘5/31/2016’

 

Questions; using fact order table, returns items only that were not order on the 1/1/2013

                                             

SELECT *

FROM [Fact].[Order]

 

SELECT [Order Key], [Customer Key], [Order Date Key], Description

FROM [Fact].[Order]

WHERE [Order Date Key] <> ‘1/1/2013’

 

Question; Fetch from [Fact].[order] table, the tax amount greater than 200

SELECT *

FROM [Fact].[order]

WHERE Tax amount > 200

ORDER BY Tax amount

 

ARITHMETIC OPERATIONS

Using distinct to remove duplicate

SELECT DISTINCT *

FROM [ fact].[order]   Highlight and execute

 

Using Sum or Average you need to specify the column to be sum

SELECT SUM ([Tax Amount]) AS TOTAL_TAX

FROM [Fact].[Order]

 

SELECT AVG([Tax Amount]) AS AVERAGE_TAX

FROM [Fact].[Order]

 

SELECT  Count (*)

FROM Fact.Sale

Question; What is the maximum Tax Amount in the Fact. Order Table

SELECT Max([Tax Amount]) AS MAXIMUM_TAX

FROM [Fact].[Order]                                                                                                     

 

Questions; Use Fact. Order Table to get Revenue for the order.

SELECT [WWI Backorder ID], Description, Quantity,

[Unit Price],([Quantity] * [unit price])AS Revenue

FROM [Fact].[Order]

 

Question; Add Tax amount and Total excluding tax as Total

SELECT Quantity, [Unit Price],[Tax Rate], [Tax Amount],[Total Excluding Tax],

([Total Excluding Tax]+[Tax Amount]) AS Total

FROM [Fact].[Order]

 

Logical operation or operational operations

 (And, Or, Between, In, Like, Not like, Is)

And operators, (condition 1 and condition 2 must be settle)

True result –both statements must be settled

False result—condition not satisfied

True + False = False

False + True = False

False + False = False

Question involving And Operators

(1). Fetch all records from fact.order table where salesperson key 19 and WWI order ID is 4

SELECT *

FROM [Fact].[Order]

WHERE [Salesperson Key] = 19

AND [WWI Order ID] = 4

 

 

(2). Fetch a records from fact.order table where Quantity less than or equal 10 and unit price = 32 from January 1st 2013.

SELECT *

FROM [Fact].[Order]

WHERE Quantity <=10

AND [Unit Price] = 32

AND [Order Date Key] = ‘1/1/2013’

 

OR Operators (One of the conditions must be settle)

SELECT *

FROM [Fact].[Sale]

WHERE Profit = 1000

OR Quantity =10

 

Between Operators it is used to select range of a date

Example; date and numerical data

SELECT *

FROM [Fact].[Sale]

WHERE [Delivery Date Key] Between ‘1/02/2013’ and ‘1/11/2013’

 

IN Operators (It allows to used only in and get the results of the items listed)

SELECT *

FROM [Fact].[Sale]

WHERE Quantity IN (5,8, 9,6)

 

NOT IN Operators is the direct opposite of IN Operators’

SELECT *

FROM [Fact].[Sale]

 

WHERE Quantity NOT IN (5,8, 9,6)