T-SQL - Querying multiple tables with Joins

Note : The following scripts are the part of the course on edx titled as: Querying Data with Transact-SQL -
Querying Data with Transact-SQL
These queries works on AdventureWorks database and information regarding the same
can be accessed by visiting following link -
AdventureWorks Installation and configuration

/* Inner, Outer, Cross, Self Combining rows from multiple tables Primary Key and Foreign Key

Select ….. From Table 1 JOIN Table 2 ON COLUMN

Select …. From Table1, Table2 where

Inner Joins – Return rows where a match is found in both tables Join operator is = then it is also called equi join

Select emp.Name, ord.amount from employee as emp [INNER] JOIN sales as ord on emp.EmpID = ord.EmpID

By default it is Inner Join (Optional , default value of join)

-- Basic Inner Join (Inner is Optional)
select
  SalesLT.Product.Name As ProductName,
  SalesLT.ProductCategory.Name As Category
From
  SalesLT.Product  
  INNER JOIN SalesLT.ProductCategory ON SalesLT.Product.ProductCategoryID = SalesLT.ProductCategory.ProductCategoryID
	-- Table Aliases

  T-SQL - Introduction (Part 2)

Note : The following scripts are the part of the course on edx titled as: Querying Data with Transact-SQL -
Querying Data with Transact-SQL
These queries works on AdventureWorks database and information regarding the same
can be accessed by visiting following link -
AdventureWorks Installation and configuration

In the last post, we started with Introduction to T-SQL, where some of the forms of SELECT statement were discussed, along with examples regarding Data Type Conversion , Working with NULLs and CASE-END operations were shown.
You can follow the same - Introduction to Transact-SQL - Part 1

We’ll be discussing these in this post -

• Removing Duplicates
• Sorting Results
• Paging Sorted Results
• Filtering Data

Removing Duplicates

Distinct is used for getting results distinct values from the selected columns

• The following queries results in a column with distinct Color names with null replaced as None, creating a new category for such cases.

select
  distinct isnull(Color, 'None') as Color
from
  SalesLT.Product

Sorting Results

Order By is used to order the final output return by sql queries by the order of the column in ascending / descending manner. The default order is ascending.

• The following queries will give the distinct color names sorted alphabetically.
  

  T-SQL - Introduction (Part 1)

Note : The following scripts are the part of the course on edx titled as: Querying Data with Transact-SQL -
Querying Data with Transact-SQL
These queries works on AdventureWorks database and information regarding the same
can be accessed by visiting following link -
AdventureWorks Installation and configuration

SQL is widely used language to interact with databases. It is a declarative language and not procedural, meaning to say that we are more concerned on achieving the output rather than how it is going to be achieved.
T-SQL is Microsoft implementation of the SQL also called as Transact SQL.The following links can be accessed to have a detailed overview about the same.

• T-SQL
• Microsoft Documentation

Just to brief, in a database there are some relations which are also called as Tables / Entities , which have Records (Rows) and Domains (Columns).Within tables we have some keys and constrains which are Primary Keys and Foreign Keys.

In SQL, there are three type of operation can be performed and we have various statements to do the same -

• Data Manipulation - Select, Insert, Update, Delete
• Data Definition - Create, Alter, Drop
• Data Control - Grant, Revoke, Deny

We’ll focus on the data manipulation part which is mostly relates to data analysis and performing advance operations over the data, starting with following in this post -

• SELECT Statement
• Data Type Conversion
• Working With Null
• Case And End