Facebook

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

Leave a Reply

Your email address will not be published. Required fields are marked *