Class Fourteen; Text (Logical Operation or Operational operations and Join)

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 1^st 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; data 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)

 

LIKE OPERATORS (WILD CARD % %) A wild card is used to substitute one or more characters in a string. They are used with Like operator. The Like operator is used in a WHERE clause to search for a specific pattern in a column.

Wildcards are special characters that can stand in for unknown characters in a text value and are handy for locating multiple items with similar, but not identical data. Wildcards can also help with getting data based on a specified pattern match. For example, finding everyone named Peace on Park Street.

   SELECT *

    FROM [Park].[Street]

    WHERE Description LIKE’%Pea%’

   SELECT *

   FROM [Dimension].[City]

   WHERE City LIKE’%ton%’

NOT LIKE OPERATORS is direct opposite of LIKE Operators.

When you want to see the words or names that begin with A

We used the wild card at the front of letter A

Example; Select all Names that begin with letter A

SELECT *

FROM [Student_information]

WHERE Name LIKE ‘A%’

 

Similarly, we used wild card before letter B to see the word or names that end with B.

SELECT *

FROM [Student information]

WHERE Name LIKE ‘%B’

 

SELECT *

FROM [Student information]

WHERE Name LIKE ‘%d%’ (this is used when you want to see all names or words that contain letter D).

 

JOIN

A join clause in the Structured Query Language (SQL) combines columns from one or more tables into a new table.

A join stitches two tables and puts on the same row records with matching fields: It is a way of joining two or more tables together to fetch related data.

The SQL JOIN is a command clause that combines records from two or more tables in a database. It is a means of combining data in fields from two tables by using values common to each table. If you’re working with databases, at some point in your work you will likely need to use SQL JOINs.

TYPES OF JOINS

 INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS.

 

1). Join (Inner Join) returns rows that have matching values in both tables.

1). Join (Inner Join) returns rows that have matching values in both tables.

Example; 1)

SELECT *

FROM [Fact].[Order]

 JOIN [Fact].[Sale]

ON [Fact].[Sale].[City Key] =[Fact].[Order].[City Key]

 

2). SELECT *

FROM [dbo].[Orders]

JOIN [dbo].[Order_Product]

ON [dbo].[Order_Product].OrderID = [dbo].[Orders].OrderID

JOIN [dbo].[Product]

ON [dbo].[Product].CookieID=[dbo].[Order_Product].CookieID

JOIN [dbo].[Customers]

ON [dbo].[Customers].CustomerID = [dbo].[Orders].CustomerID

 

3). SELECT *

FROM [Fact].[Order]FO

JOIN [Fact].[Sale]FS

ON FS.[City Key] =FO.[City Key]

JOIN [Dimension].[City]DC

ON DC.[City Key]=FO.[City Key]

JOIN [Fact].[Purchase]FP

ON FP.[Date Key] = FS.[Delivery Date Key]

 

 

2). Left Outer join returns all rows from the left table with corresponding rows from the right table. If there is no matching row, NULLS are returned as value from the second table.

Example;

1).  SELECT *

 FROM [Dimension].[Customer]

 LEFT OUTER JOIN [Fact].[Stock Holding]

 ON [Fact].[Stock Holding].[Lineage Key]=[Dimension].[Customer].[Lineage Key]

 

3). Right outer join returns all rows from the right table with corresponding rows from the left table. If there is no matching row, NULLS are returned as value from left table.

Example;

SELECT *

 FROM [Dimension].[Customer]

 RIGHT OUTER JOIN [Fact].[Stock Holding]

 ON [Fact].[Stock Holding].[Lineage Key]=[Dimension].[Customer].[Lineage Key]

 

4). Full outer join returns all rows from both tables. If there is no matching row in the second table, NULL is returned.

Example;

SELECT *

 FROM [Dimension].[Customer]

LEFT OUTER JOIN [Fact].[Stock Holding]

 ON [Fact].[Stock Holding].[Lineage Key]=[Dimension].[Customer].[Lineage Key]

5). Cross join returns all possible combinations of rows from both tables.

Difference between Outer Join and Cross Join

 The main difference between “OUTER JOIN” and “CROSS JOIN” is in the number of rows that are returned. The “OUTER JOIN” command will return all rows from the first table, even if there are no matching rows in the second table. The “CROSS JOIN” command will return the Cartesian product of the two tables, which is every possible combination of rows from the two tables. So, the “OUTER JOIN” command only returns the rows that have matching values, while the “CROSS JOIN” command returns every possible combination of rows.

A “FULL OUTER JOIN” combines the “LEFT OUTER JOIN” and “RIGHT OUTER JOIN” commands. It returns all rows from both tables, regardless of whether there are any matches in the other table. So, all rows from the first table will be returned, with null values in the specified fields for any rows that have no match in the second table. And all rows from the second table will be returned, with null values in the specified fields for any rows that have no match in the first table.

In some cases, having null values in your dataset can make it more difficult to draw accurate conclusions from your analysis. For example, if you’re trying to calculate the average price of a product, but some of the product prices are missing, your average will be skewed. So, it’s important to consider whether missing values will affect the accuracy of your analysis. In other cases, you may be able to ignore the null values and still get useful results. It depends on the specific situation and the type of analysis you’re performing.

When you’re performing an analysis of aggregated data (like calculating an average, sum, or count), and you have some null values in your dataset, there are a few things you can do. One option is to exclude the rows that have null values from your analysis. This can help to ensure that your results are more accurate. Another option is to replace the null values with a placeholder value, like “NA” or “0”. This can allow you to include all of the rows in your analysis, but it’s important to remember that this will change the values of the data.

The “INNER JOIN” command is the most commonly used type of “JOIN” command. That’s because it’s the simplest and most straightforward way to join two tables together. It returns only the rows that have matching values in the specified fields, and it doesn’t create any new rows. In addition, “INNER JOIN” is the default type of “JOIN” command, so it doesn’t need to be specified explicitly. So, it’s the easiest to use and the most commonly used type of “JOIN.