SQL (Structured Query Language)
- It is high level language which provide you capability to query data from any structured data source (Tables).
- High level language means language which is used by us to communicate (English).
- For learning, we will be using online SQL editor provided by W3C.
- http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all
SQL script:
SQL Queries:
Using Wild Card (*) :
Selecting all data
SELECT * FROM CUSTOMERS
Specific Columns:
SELECT CustomerName, ContactName,Address,
City FROM CUSTOMERS
Playing with data:
Joining 2 Columns:
Select CustomerName,ContactName,Address + ' - ' + City
FROM CUSTOMERS
Using Alias:
Select CustomerName,ContactName,
Address + ' - ' + City AS [Address]
FROM CUSTOMERS
Using WHERE Clause:
Select CustomerName,ContactName,
Address + ' - ' + City AS [Address]
FROM CUSTOMERS WHERE City='Berlin'
Playing with WHERE:
OR Clause:
Select CustomerName,ContactName,
Address + ' - ' + City AS [Address]
FROM CUSTOMERS WHERE City='Berlin' OR City='London'
AND Clause:
Select CustomerName,ContactName,
Address + ' - ' + City AS [Address]
FROM CUSTOMERS WHERE City='London' AND Postalcode='WA1 1DP'
Like Clause:
Select CustomerName,ContactName,
Address + ' - ' + City AS [Address],PostalCode
FROM CUSTOMERS WHERE Postalcode like 'W%'
IN Clause:
Select CustomerName,ContactName,
Address + ' - ' + City AS [Address],PostalCode
FROM CUSTOMERS WHERE City IN ('Berlin','London')
Precedence:
Select CustomerName,ContactName,
Address + ' - ' + City AS [Address],PostalCode
FROM CUSTOMERS
WHERE (City='London' AND Postalcode='WA1 1DP') OR City='Berlin'
Group By:
Select City,Count(*)
FROM CUSTOMERS GROUP BY City
Playing more:
Grouping by Country and City:
Select Country,City,Count(*)
FROM CUSTOMERS GROUP BY Country,City
Error Queries:
SELECT SupplierID,count(1) from Products
Query:
SELECT SupplierID,count(1) from Products GROUP BY SupplierID
Query:
SELECT SupplierID,CategoryID,count(1) from Products
group by SupplierID,CategoryID ORDER BY SupplierID
HAVING Clause:
SELECT SupplierID,count(1) from Products GROUP BY SupplierID HAVING Count(1)>2