SQL

1. Select the Top 3 rows from the table
SELECT TOP 3 * FROM Customers;
SELECT * FROM Persons LIMIT 3;
SELECT * FROM Persons WHERE ROWNUM <=5;

2. Selects the first 50% of the records from the "Customers" table
 SELECT TOP 50 PERCENT * FROM Customers;

3.Select distinct from the table

SELECT DISTINCT City FROM Customers;

4. Where condition
SELECT * FROM Customers WHERE CustomerID=1;

Operator    Description
=    Equal
<>    Not equal. Note: In some versions of SQL this operator may be written as !=
>    Greater than
<    Less than
>=    Greater than or equal
<=    Less than or equal
BETWEEN    Between an inclusive range
LIKE    Search for a pattern
IN    To specify multiple possible values for a column

5. AND OR Conditions.
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München');

6. Order By.
SELECT * FROM Customers ORDER BY Country ASC;
SELECT * FROM Customers ORDER BY Country DESC;

7. SQL lIKE operator.
SELECT * FROM Customers WHERE Country LIKE '%land%';
SELECT * FROM Customers WHERE City LIKE '_uresh';  o/p : suresh

SELECT * FROM Customers WHERE City LIKE '[bsp]%'; o/p : all city names started with any one character of these three(b or s or p)

SELECT * FROM Customers WHERE City LIKE '[a-c]%'; o/p :

all city names started with any one character of between a to c(a or b or c)

SELECT * FROM Customers WHERE City LIKE '[!bsp]%'; o/p : all city names except started with any one character of these three(b or s or p)

 8. IN operator.
SELECT * FROM Customers WHERE City IN ('Paris','London');

9. BETWEEN operator.
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products WHERE ProductName BETWEEN 'C' AND 'M';

10. ALIAS examples.
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

SELECT CustomerName, CONCAT(Address,', ',City,', ',PostalCode,', ',Country) AS Address FROM Customers;

SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID; 

11. Select TOP 10 Rows
SELECT TOP 10 Id, ProductName, UnitPrice, Package FROM Product
ORDER BY UnitPrice DESC;

12. ORDER BY OFFSET
SELECT column-names FROM table-name ORDER BY column-names OFFSET n ROWS;

SELECT Id, ProductName, UnitPrice, Package FROM Product ORDER BY UnitPrice DESC OFFSET 10 ROWS;
 
SELECT column-names FROM table-name ORDER BY column-names OFFSET n ROWS FETCH NEXT m ROWS ONLY;

SELECT Id, ProductName, UnitPrice, Package FROM Product ORDER BY UnitPrice DESC OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

13. Distinct
SELECT DISTINCT Country FROM Supplier ORDER BY COUNTRY  ;

14. Select Min and Max
SELECT MIN(UnitPrice) FROM Product;
SELECT MAX(UnitPrice) FROM Product;

15. COUNT, SUM and AVG
SELECT COUNT(Id) FROM Customer;
SELECT SUM(TotalAmount) FROM Order WHERE YEAR(OrderDate) = 2013;
SELECT AVG(TotalAmount) FROM Order;

16. AND OR NOT
SELECT Id, FirstName, LastName, City, Country FROM Customer WHERE FirstName = 'Thomas' AND LastName = 'Hardy'

 SELECT Id, FirstName, LastName, City, Country FROM Customer WHERE Country = 'Spain' OR Country = 'France'

 SELECT Id, FirstName, LastName, City, Country FROM Customer WHERE NOT Country = 'USA'

 SELECT Id, OrderDate, CustomerId, TotalAmount FROM [Order] WHERE NOT (TotalAmount >= 50 AND TotalAmount <= 15000) ORDER BY TotalAmount DESC