Database used Northwind sample Database
-- USE for changing Database.
-----------------------------
USE Northwind
------------------
-- SELECT STATEMENT
------------------
-- SQL SELECT Statement to selecting all coloumns.
-------------------------------------------------
SELECT * FROM [Suppliers]
-- SQL SELECT Statement to select specific coloumns.
----------------------------------------------------
SELECT [CITY], [COUNTRY] FROM [Suppliers]
-----------------
-- SELECT DISTINCT
-----------------
-- SQL DISTINCT statement is used to return only distinct (different) values.
-----------------------------------------------------------------------------
SELECT DISTINCT [Country], [City] FROM [Suppliers]
SELECT DISTINCT [ContactName] FROM [Suppliers]
---------------
-- WHERE CLAUSE
---------------
-- SQL WHERE Clause is used to filter records.
----------------------------------------------
SELECT * FROM [Suppliers] WHERE [Country]='USA'
SELECT [ContactName], [CompanyName] FROM [Suppliers] WHERE [Country]='USA'
-- Single quotes are not necessary in nemuric fields.
-----------------------------------------------------
SELECT [ContactName], [CompanyName] FROM [Suppliers] WHERE [SupplierID]='12'
---------------------------------
-- Operators in The WHERE Clause.
---------------------------------
SELECT * from suppliers
-- Equal-To = Operator.
-----------------------
SELECT [ContactName] FROM [Suppliers] WHERE [Phone]='555-8787'
-- Not Equal != Operator.
-------------------------
SELECT [ContactName], [FAX] FROM [Suppliers] WHERE [FAX] != 'NULL'
-- IS Operator for retriving Null values from database.
-------------------------------------------------------
SELECT [ContactName], [FAX] FROM [Suppliers] WHERE [FAX] IS NULL
-- Greater than, Less than, Greater than or equal, Less than or equal.
----------------------------------------------------------------------
SELECT [ContactName] FROM [Suppliers] WHERE [SupplierID] < '5'
------------------
-- BETWEEN Operator
------------------
-- BETWEEN Operator selects values within a range. The values can be numbers, text, or dates.
---------------------------------------------------------------------------------------------
SELECT [ContactName], [SupplierID] From [Suppliers] WHERE [SupplierID] BETWEEN '10' AND '15'
SELECT [ContactName], [SupplierID] From [Suppliers] WHERE [ContactName] BETWEEN 'D' AND 'R'
SELECT [CustomerID], [OrderID], [OrderDate] From [Orders] WHERE [OrderDate] BETWEEN '1996-05-04' AND '1997-04-09'
-- NOT BETWEEN use to display the values outside the range.
-----------------------------------------------------------
SELECT [ContactName], [SupplierID] From [Suppliers] WHERE [SupplierID] NOT BETWEEN '2' AND '5'
----------------
-- LIKE Operator
----------------
-- LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
-----------------------------------------------------------------------------------------
-- The following SQL statement selects all ContactName with a City ending with the letter "d".
-- The "%" sign is used to define wildcards (missing letters) both before and after the pattern.
SELECT [SupplierID], [ContactName], [CompanyName], [City] FROM [Suppliers] WHERE [City] LIKE '%d'
-- The following SQL statement selects all CompanyName with a City containing the pattern "don".
------------------------------------------------------------------------------------------------
SELECT [CompanyName], [City] FROM [Suppliers] WHERE [City] LIKE '%don%'
-- The following SQL statement selects all CompanyName with a City starting with the letter "a".
------------------------------------------------------------------------------------------------
SELECT [CompanyName], [City] FROM [Suppliers] WHERE [City] LIKE 'b%'
-- NOT keyword allows you to select records that does NOT match the pattern.
----------------------------------------------------------------------------
SELECT [CompanyName], [City] FROM [Suppliers] WHERE [City] NOT LIKE '%don%'
--------------
-- IN Operator
--------------
-- IN SQL statement selects all CompanyName with a City of "London" or "Paris".
--------------------------------------------------------------------------------
SELECT [CompanyName], [City] FROM [Suppliers] WHERE [City] IN ('London', 'Paris')
-------------------------
-- SQL AND & OR Operators
-------------------------
-- The AND operator displays a record if both the first condition AND the second condition are true.
----------------------------------------------------------------------------------------------------
SELECT [CompanyName], [ContactName] FROM [Suppliers] WHERE [City]='Tokyo' AND [Country]='Japan'
-- The OR operator displays a record if either the first condition OR the second condition is true.
---------------------------------------------------------------------------------------------------
SELECT [CompanyName], [ContactName], [City], [Country] FROM [Suppliers] WHERE [City]='Tokyo' OR [Country]='UK'
-- Combination of AND & OR Operator
------------------------------------
SELECT [CompanyName], [ContactName], [City], [Country] FROM [Suppliers] WHERE [City]='Tokyo' AND ([Country]='UK' OR [Country]='Japan')
-------------------
-- ORDER BY Keyword
-------------------
-- The ORDER BY keyword sorts the records in ascending order by default.
------------------------------------------------------------------------
SELECT [SupplierID], [ContactName] FROM [Suppliers] WHERE [Country]='UK' ORDER BY [SupplierID]
-- ORDER BY DESC
----------------
SELECT [SupplierID], [ContactName] FROM [Suppliers] WHERE [Country]='Germany' ORDER BY [SupplierID] DESC
-- ORDER BY Several Columns
---------------------------
SELECT [SupplierID], [ContactName] FROM [Suppliers] ORDER BY [CompanyName], [City], [Country]
-------------------------
-- SQL Wildcard Character
-------------------------
-- A wildcard character can be used to substitute for any other character(s) in a string.
-----------------------------------------------------------------------------------------
-- SQL Wildcard characters are used with the SQL LIKE operator.
-- SQL _ Wildcard a substitute for a single character.
------------------------------------------------------
SELECT [SupplierId], [CompanyName], [City] FROM [Suppliers] WHERE [City] LIKE '_okyo'
SELECT [SupplierID], [CompanyName], [City] FROM [Suppliers] WHERE [City] LIKE 'L_n_o_'
-- SQL % Wildcard
-----------------
SELECT [SupplierID], [CompanyName], [City] FROM [Suppliers] WHERE [City] LIKE '%don%'
-- SQL [charlist] Wildcard use to find between ranges of characters to match.
-----------------------------------------------------------------------------
SELECT * FROM [Suppliers] WHERE [City] LIKE '[bsp]%'
SELECT * FROM [Suppliers] WHERE [City] LIKE '[a-b]%'
-- The following SQL statement selects all customers with a City NOT starting with "b", "s", or "p".
----------------------------------------------------------------------------------------------------
SELECT * FROM [Suppliers] WHERE [City] LIKE '[!bsp]%'