SELECT SalesOrderID

,   ShipDate
FROM Sales.SalesOrderHeader
WHERE ShipDate BETWEEN '2005-07-23T00:00:00'
                       AND '2005-07-24T23:59:59'

IS NULL

IS NOT NULL

SELECT ProductID,
   Name,
   ISNULL(Weight,0) AS Weight
FROM Production.Product
WHERE Weight IS NULL;

SELECT ProductID,
   Name,
   Weight
FROM Production.Product
WHERE Weight IS NOT NULL;

IN

WHERE Color IN ('Silver', 'Black', 'Red')
WHERE Color = 'Silver' OR Color = 'Black' OR Color = 'Red'

WHERE Description LIKE '%/%%' ESCAPE '/

Wildcards for the LIKE predicate

Wildcard
Usage

%
The percent sign. Represents a string of zero or more characters
_
The underscore. Represents a single character
[…]
A list of characters enclosed within square brackets. Represents a single character from among any in the list. You may use the hyphen as a shorthand to translate a range into a list. For example, [ABCDEF]-flatcan be written more succinctly as[A-F]-flat.You can also mix and match single characters and ranges. The expressions[A-CD-F]-flat, [A-DEF]-flat, and [ABC-F]-flat all mean the same thing and ultimately resolve to [ABCDEF]-flat.
[^…]
A list of characters enclosed within square brackets and preceeded by a caret. Represents a single character from among any not in the list.

ORDER BY

ORDER BY p.Name DESC,
   h.EndDate DESC

ORDER BY p.Name ASC,
   h.EndDate ASC;

ORDER BY p.Name ASC,
   h.EndDate DESC;


One issue when ordering by unselected columns is that ORDER BY items must appear in the SELECT list if SELECT DISTINCT is specified. That’s because the grouping operation used internally to eliminate duplicate rows from the result set has the effect of disassociating rows in the result set from their original underlying rows in the table. That behavior makes perfect sense when you think about it. A deduplicated row in a result set would come from what originally were two or more table rows. And which of those rows would you go to for the excluded column? There is no answer to that question, and hence the caveat.

SELECT p.ProductID,
   p.Name,
   p.Color
FROM Production.Product AS p
WHERE p.Color IS NOT NULL
ORDER BY 
CASE  LOWER(p.Color)
    WHEN 'red' THEN ' '
    ELSE LOWER(p.Color)
END;


SELECT ProductID, Name
FROM Production.Product
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY






DECLARE @AddressLine2 nvarchar(60)
DECLARE @AddressLine3 nvarchar(60)
SELECT @AddressLine3=ISNULL(@AddressLine2,'10, lake view')
SELECT @AddressLine3




DECLARE @AddressLine1 nvarchar(60) = '101 E. Varnum'
DECLARE @AddressLine2 nvarchar(60) = 'Ambulance Desk'
SELECT @AddressLine1 = AddressLine1, @AddressLine2 = AddressLine2
FROM Person.Address
WHERE AddressID = 49862;
IF @@ROWCOUNT = 1
  SELECT @AddressLine1, @AddressLine2
ELSE
  SELECT 'Either no rows or too many rows found.';

SELECT * FROM table_name

SELECT * FROM HumanResources.Employee

Never use: SELECT * FROM - in production code.

Reason:

1. COLUMNS can vary. Addition or removal of columns affect client application using them.
2. Network traffic - No need to select all the information, if you need only very few.

Operator	Description
!=	Tests two expressions not being equal to each other.
!>	Tests whether the left condition is less than or equal to (i.e., not greater than) the condition on the right.
!<	Tests whether the left condition is greater than or equal to (i.e., not less than) the condition on the right.
<	Tests the left condition as less than the right condition.
<=	Tests the left condition as less than or equal to the right condition.
<>	Tests two expressions not being equal to each other.
=	Tests equality between two expressions.
>	Tests the left condition being greater than the expression to the right.
>=	Tests the left condition being greater than or equal to the expression to the right.

SELECT * FROM HumanResources.Employee

WHERE JobTitle='SALES Representative'

WHERE - can be combined with AND and OR 

SELECT 

FirstName

,LastName AS [Surname]

FROM

Person.Person

COLUMN ALIAS

SELECT BusinessEntityID AS EmployeeID

       ,VacationHours + SickLeaveHours AS [TotalHolidaysAvailable]
FROM HumanResources.Employee;

TABLE ALIAS:

SELECT E.BirthDate FROM HumanResources.Employee AS E

T-SQL Notes

SELECT list_of_columns_required
FROM list_of_tables_or_views_that_act_as_source_of_data
WHERE list_of_conditions_or_filter
ORDER BY sort_order_columns;

http://msftdbprodsamples.codeplex.com/ --- sample database download for SQL Server products

To connect to specific database in an SQL Server Instance:

USE [AdventureWorks2012]

GO

One SQL Server Instance, can have many databases used by one or more different applications.

select * from [AdventureWorks2012].[HumanResources].[Employee]
select * from AdventureWorks2012.HumanResources.Employee

We can do:

USE [AdventureWorks2012]
GO
SELECT * from HumanResources.Employee




The advantage with high-lighted approach: The code can be run against a different database, as long as the schema exists.


Say we can have [MYSTARTUP] database which has HumanResources schema and the schema contains Employee table.


SELECT
 BusinessEntityID
 ,NationalIDNumber
 ,LoginID
FROM
[HumanResources].[Employee]




In the above HumanResources is Schema. A schema contains the object, and that schema is then owned by a user. Because users own a schema, and the schema contains the object, you can change the owner of the schema without having to modify object ownership.