SQL FAQ

"DA-RUM" "DUM-DUM"
My FAQ's
---------
* SQL
1. http://www.java67.com/2013/04/10-frequently-asked-sql-query-interview-questions-answers-database.html

* Finding the 3 greates salary from the employe table:
Ex1 :
SELECT TOP 1 salary FROM (
   SELECT TOP 3 salary
   FROM employees
   ORDER BY salary DESC) AS emp
ORDER BY salary ASC

Ex2 :
SELECT salary from
(SELECT rownum ID, EmpSalary salary from
(SELECT DISTINCT EmpSalary from salary_table order by EmpSalary DESC)
where ID = nth)

    Select empno, empname, mdept  from emp_table where Mdept = 'IT"

Different SQL Joins
--------------------
Before we continue with examples, we will list the types of the different SQL JOINs you can use:

INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables

* Joins
-------
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

* Inner Join
------------
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match
between the columns. If there are rows in the "Customers" table that do not have matches
in "Orders", these customers will NOT be listed.

* Left Join
------------
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all the rows from the left table (Customers),
even if there are no matches in the right table (Orders).

* Right Join
-------------
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Sample:
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;

Note: The RIGHT JOIN keyword returns all the rows from the right table (Employees),
even if there are no matches in the left table (Orders).

* Full Outer Join
------------------

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Note: The FULL OUTER JOIN keyword returns all the rows from the left table (Customers),
and all the rows from the right table (Orders). If there are rows in "Customers" that do
not have matches in "Orders", or if there are rows in "Orders" that do not have matches
in "Customers", those rows will be listed as well.

* Wildcards
-----------

A wildcard character can be used to substitute for any other character(s) in a string.

Wildcard Description
% A substitute for zero or more characters

_ A substitute for a single character

[charlist] Sets and ranges of characters to match

[^charlist]
or
[!charlist] Matches only a character NOT specified within the brackets

1. SELECT * FROM Customers WHERE City LIKE 'ber%';
2. SELECT * FROM Customers WHERE City LIKE '%es%';

SELECT * FROM Customers WHERE City LIKE '_erlin';

City starting with "b", "s", or "p" - Example :
SELECT * FROM Customers WHERE City LIKE '[bsp]%';

* SQL PRIMARY KEY Constraint on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is
created:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

* SQL FOREIGN KEY Constraint on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "P_Id" column when the
"Orders" table is created:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

* SQL statement selects only the distinct values from the "City" columns from
the "Customers" table:

SELECT DISTINCT City FROM Customers;

* * CREATE TRIGGER <trigger name> 

  { BEFORE | AFTER } 

  { INSERT | UPDATE | DELETE } 

  ON <table name>  

  FOR EACH ROW 

  <triggered action>

-------------

** Stored Procedures
---------------------
Ex1:
----
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [DBO].[GetNewYorkEmployees]
AS

SELECT E.FirstName, E.LastName, L.City, L.[State]
  FROM Employee AS E
INNER JOIN Location as L
ON E.LocationID = L.LocationID
  WHERE L.[State] = 'NY'
GO

* EXEC GetNewYorkEmployees

Ex2:
----
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [DBO].[GetNONNewYorkEmployees]
AS

SELECT E.FirstName, E.LastName, L.City, L.[State]
  FROM Employee AS E
INNER JOIN Location as L
ON E.LocationID = L.LocationID
  WHERE L.[State] !!= 'NY'
GO

* EXEC GetNONNewYorkEmployees
-----------------