Using SQL SELECT statement with Transact SQL or TSQL

SELECT sql statement returns the data from SQL tables. Does not make any changes to the underlying data. It is the most commonly used SQL statement. This statement can use a sub-select (sub query). Here is the syntax

SELECT fields FROM tables WHERE condition ORDERBY fields

Examples from the Northwind database
If you would like to download a copy of this Microsoft sample database here is the link:

Download Northwind sample database

Returning all data from a table

• SELECT * FROM CUSTOMERS returns all the customer records

Using a SQL WHERE clause

• SELECT * FROM CUSTOMERS WHERE COUNTRY='USA' return customers in USA

Using SQL ORDER BY clause

• SELECT * FROM CUSTOMERS ORDER BY COMPANYNAME sorts by Company Name in Ascending Order

Using a DISTINCT clause to eliminate duplicates

• SELECT DISTINCT(COUNTRY) FROM CUSTOMERS will return all the unique values for Country field

Using OR in WHERE clause

• SELECT * FROM CUSTOMERS WHERE COUNTRY='USA' OR COUNTRY='UK' returns customers in USA or UK

Using the SQL IN clause (similar to OR clause)

• SELECT * FROM CUSTOMERS WHERE COUNTRY IN ('USA','UK') same as above

Using LIKE clause to look for patterns

• SELECT * FROM CUSTOMERS WHERE CONTACTNAME LIKE 'JO%' returns Customers with name beginning with the pattern ‘JO___’ e.g. JOHN would be returned

Using my fav IS NULL clause

• SELECT * FROM CUSTOMERS WHERE REGION IS NULL will give all the customer where the Region is missing or not known

Searching for numeric value

• SELECT CUSTOMERID FROM ORDERS WHERE EMPLOYEEID=5 returns the Customer ID from the Orders table where EmployeeID is 5

Working with Dates in Transact SQL

• SELECT * FROM ORDERS WHERE ORDERDATE=’ 1996-07-04’ checks for the all the orders for 7/4/1996

Using SQL COUNT clause

• SELECT COUNT(*) FROM CUSTOMERS returns the number of records in Customer table. will return number

Using SQL MIN function

• SELECT MIN(UNITPRICE) FROM PRODUCTS; will return the lowest priced product (a number)

Using SQL MAX function

• SELECT MAX(UNITPRICE) FROM PRODUCTS; will return the highest priced product (a number)

Using SQL AVG clause to compute Average

• SELECT AVG(UNITPRICE) FROM PRODUCTS; will return the average Unit Price (a number)

Using SQL SUM function

• SELECT SUM(Quantity) FROM [ORDER DETAILS] WHERE ORDERID=10258 will sum all the items for this order

Using GROUP BY HAVING clause, mainly used for grouping data

• SELECT ProductID, SUM(Quantity) AS Total_Quantity FROM [ORDER DETAILS] GROUP BY ProductID HAVING SUM(Quantity) >1000

will return two things: one the product id and second the sum of quantity gouped by Product ID where number of products ordered is greater than 1000