SQL - A QuickView for Testers

>> 4 Categories
## DDL - Data Definition Language - Used to define format and type i.e. structure of objects.
-- CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
## DML - Data Manipulation Language - Used to manipulate data stored in tables.
-- SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
## DCL - Data Control Language - Used to define roles, permissions, referential integrities and other security features.
-- GRANT, REVOKE
## TCL - Transactional Control Language - Used to manage transactions happening in database.
-- COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION
 
>> Objects
>> General
>> Tables - Objects which will stay permanently in database. Contains columns and rows.

1. CREATE TABLE Emp1(ID int Primary Key, FirstName VARCHAR(255), LastName VARCHAR(255), Salary int)
2. CREATE TABLE Emp2(ID int Primary Key, Area VARCHAR(255), Track VARCHAR(255))

>> Views - Same as tables, but resides dynamically not permanently. But database will have query to make the view permanently.

1. CREATE VIEW V1
2. AS
3. SELECT * FROM EMP1
5. -- Access the View
6. SELECT * FROM V1

-- Synonyms - Just assigning some objects with a name so that we can use the SYNONYM defined instead of full object names while creating query.

1. CREATE SYNONYM MyEmp FOR [dbo].Emp1
3. -- Access the Synonym
4. SELECT * FROM MyEmp

>> Programmabilty
-- Functions - Used to define custom functions which will be used as part of SQL statements. Ex. SUM, DATEDIFF, LEN etc.

1. CREATE FUNCTION [dbo].ADD1(@A INT, @B INT)
2. RETURNS INT
3. AS
4. BEGIN
5. RETURN (SELECT SUM(@A + @B))
6. END
8. -- Access the Function
9. SELECT [dbo].ADD1(10, 20)

-- Stored Procedures - Combination of normal SQL statements which will do some business operation in database like insert, update, etc. It will take inputs also. Mostly application developers will call these SPs from code on particular GUI event to achieve some business updates. SPs will be in database permanently.

1. CREATE PROCEDURE SP1
2. AS
3. BEGIN
4. SELECT * FROM Emp2
5. END
7. -- Access the SP
8. EXEC SP1

-- Triggers - Set of SQL statements which will execute based on transactions on a object. Like before or after UPDATE or INSERT or DELETE on a table, some SQL statements will be fired and executed.

1. -- Trigger to insert an entry to history table when anything changes in Emp1 table
2. CREATE OR REPLACE TRIGGER EmpHistory
3. BEFORE UPDATE OF Salary
4. ON Emp1
5. FOR EACH ROW
6. BEGIN
7. INSERT INTO EmpHistory
8. VALUES
9. (:old.ID,
10.  :old.FirstName,
11.  :old.LastName,
12.  :old.Salary);
13. END;

-- Assemblies - Managed db objects like triggers, Stored Procedures will be compiled as a DLL and it can be used to access the triggers and SPs in any database by just registering the DLL as an ASSEMBLY in the database.
-- Rules - Specifying rules to a column in a table. i.e. Age column should be between 15 to 60.

1. --To validate data entry for Salary field
2. CREATE RULE SalaryRule
3. AS
4. @Salary >= 10000 AND @Salary < 20000;

>> Security
-- Users - Associated users to a DB.
-- Roles - Roles for a DB. roles can be assigned to schemas to achieve security
-- Schemas - We can say, Container of objects. Schemas will have associated user and role. But user will not be direct owner of objects.
>> Indexes - To speed up the performance of DB. 
-- Clustered Index - Comprises actual rows of data
-- Non-Clustered Index - Comprises pointers key columns data and pointers to actual row data.
>> Cursors - Temperary area of system memory which holds the result record set for the query. But at a time one row only can be processed from active set. Programmers use this active set to process each rows in their procedures.

>> Commands and Functions
## CREATE - Used to create objects
## ALTER - Used to alter the structure of objects

1. -- Adding an column
2. ALTER TABLE Emp1 Add Age int

## DROP - Used to delete the objects; Also can be rolled back.

1. DROP TABLE Emp1

## TRUNCATE - Used to delete all records in objects fast way; Also can be rolled back.

1. TRUNCATE TABLE Emp1

## COMMENT - Used to provide comments
## RENAME - Used to rename objects.

1. SP_RENAME 'Emp1', 'Emp111'

## SELECT - Used to get data from objects

1. SELECT * FROM Emp1

## INSERT - Used to add records in objects

1. INSERT INTO Emp1 VALUES(0001, 'Shanmuga', 'Vel', 10)
2. INSERT INTO Emp1(ID, LastName, Salary, FirstName) VALUES(0002, 'G', 20, 'Vijai')

## UPDATE - Used to update records in objects

1. UPDATE Emp1 SET Salary = 30
2. UPDATE Emp1 SET Salary = 30 WHERE ID = 0003

## DELETE - Used to delete records in objects; Also can be rolled back.

1. DELETE FROM Emp1
2. DELETE FROM Emp1 WHERE ID=0010

## MERGE - Used to merge ie. Sync 2 tables using conditions.
## CALL/EXEC- Used to call stored procedures
## EXPLAIN PLAN - Used to see execution plan while querying any objects
## LOCK TABLE - Used to lock a whole table. Programmers use this while a trasaction need to access many rows multiple times as whole table locking will reduce time.
## GRANT - Used to provide permissions for users, roles, schemas etc.
## REVOKE - Used to remove permissions for users, roles, schemas etc.
## COMMIT - To end the transaction; We can't ROLLBACK the transaction after COMMIT, as COMMIT ends the TRAN. Also COMMIT releases savepoints.
## ROLLBACK - Used to rolll back the previous transaction.

1. --********* ROLL BACK ********
2. -- 1. DELETE -----------------
3. BEGIN TRANSACTION
4. DELETE FROM Emp3
6. --Rollback the Delete
7. ROLLBACK TRANSACTION
9. -- 2. DROP -----------------
10. BEGIN TRANSACTION
11. DROP TABLE Emp3
13. --Rollback the DROP
14. ROLLBACK TRANSACTION
16. -- 3. TRUNCATE -----------------
17. BEGIN TRANSACTION
18. TRUNCATE TABLE Emp3
20. --Rollback the TRUNCATE
21. ROLLBACK TRANSACTION
23. -- But if you end/finish the txn using COMMIT, you can't rollback ----
24. BEGIN TRANSACTION
25. TRUNCATE TABLE Emp3
26. COMMIT TRANSACTION
28. -- RollBack Won't work now as I Commits the txn
29. ROLLBACK TRANSACTION

## SAVEPOINT - Used to save trasaction points so that we can rollback to particular point on a trasaction.

1. --SAVEPOINTS to RollBack
2. CREATE TABLE Emp3(ID int)
3. BEGIN TRANSACTION
4. INSERT INTO Emp3 VALUES(01)
5. SAVE TRANSACTION POINT1
6. INSERT INTO Emp3 VALUES(02)
7. SAVE TRANSACTION POINT2
9. --Rollback to the SAVEPOINT;
10. ROLLBACK TRAN POINT1

## Joins - Used to get records from multiple objects with relalationships
>> INNER JOIN

1. SELECT * FROM Emp1 JOIN Emp2 ON Emp1.ID = Emp2.ID
2. SELECT * FROM Emp1 INNER JOIN Emp2 ON Emp1.ID = Emp2.ID --Both are same
3. SELECT Emp1.[ID], FirstName, LastName, Emp2.Area FROM Emp1 INNER JOIN Emp2 ON Emp1.ID = Emp2.ID
4. --Using WHERE
5. SELECT Emp1.[ID], Emp1.FirstName, Emp1.LastName, Emp2.Area FROM Emp1, Emp2 WHERE Emp1.ID = Emp2.ID

>> OUTER JOIN
-- LEFT OUTER JOIN
-- RIGHT OUTER JOIN
-- FULL OUTER JOIN

1. SELECT * FROM Emp1 LEFT JOIN Emp2 ON Emp1.ID = Emp2.ID
2. SELECT * FROM Emp1 LEFT OUTER JOIN Emp2 ON Emp1.ID = Emp2.ID --Both are same
3. SELECT * FROM Emp1 RIGHT JOIN Emp2 ON Emp1.ID = Emp2.ID
4. SELECT * FROM Emp1 FULL JOIN Emp2 ON Emp1.ID = Emp2.ID --Full OUTER Join

>> CROSS JOIN

1. -- CROSS JOIN - Combines each row in 1st table with each row in 2nd table; So (1st TableRowsCount x 2nd TableRowsCount) will be result rows.
2. SELECT * FROM Emp1 CROSS JOIN Emp2

>> SELF JOIN or NATURAL JOIN

1. -- SELF JOIN or NATUAL JOIN - Joining same tabel with different aliases using INNER JOIN.
2. SELECT * FROM Emp1 AS E1 JOIN Emp2 AS E2 ON E1.ID = E2.ID

## UNION - Used to combine results of multiple SELECT quries with OR operation

1. -- UNIONS -- Combine 2 or more Select Statements with OR; Need same No. Of Columns, DataType and order for all Select Statements
2. -- UNION will return distinct rows only
3. SELECT [ID] FROM Emp1
4. UNION
5. SELECT [ID] FROM Emp2
7. -- UNION ALL will return all rows including duplicate
8. SELECT [ID] FROM Emp1
9. UNION ALL
10. SELECT [ID] FROM Emp2

## INTERSECT- Used to combine results of multiple SELECT quries with AND opeartion

1. -- INTERSECT -- Combine 2 or more Select Statements with AND; Need same No. Of Columns, DataType and order for all Select Statements
2. SELECT [ID] FROM Emp1
3. INTERSECT
4. SELECT [ID] FROM Emp2

## Constraints
-- NOT NULL
-- DEFAULT
-- UNIQUE
-- PRIMARY key
-- FOREIGN key
-- CHECK
-- INDEX

UNIQUE:
1) Unique Key is nothing but it is uniquely identified each roe in Table.
2) Unique Key Does not Allows Duplicate values but allows only one Null value.
3) Primary key is default Non- Clustered indexes
NOT NULL:
1) NOT NULL constraint  wont allow null values.
2) Any column can  be added this constraint.
PRIMARY KEY: = UNIQUE + NOT NULL
1) Primary key is nothing but it is uniquely identified each roe in Table.
2) Primary key Does not Allows Duplicate values and Null values.
3) Primary key is default Clustered indexes
4) One table can have only one Primary key.

-- Aggregate - SUM, AVERAGE, COUNT, MIN, MAX
-- Logical - AND, OR, NOT
-- Clauses/Operators - WHERE, GROUP BY, ORDER BY, HAVING, FROM, IN, LIKE, DISTINCT, BETWEEN, TOP
** HAVING - only for SELECT statements; Mostly used with GROUP BY; If used without GROUP BY, just act as WHERE
- HAVING is WHERE for Group of rows; WHERE is for individual rows

1. -- GROUP BY
2. SELECT SUM(Salary) AS "Total", FirstName FROM Emp1 GROUP BY FirstName
4. -- HAVING vs WHERE
5. SELECT Emp1.ID, AVG(Emp1.Salary)
6. FROM Emp1 INNER JOIN Emp2
7. ON Emp1.ID = Emp2.ID
8. WHERE Emp2.Area = 'QA'
9. GROUP BY Emp1.ID
10. HAVING AVG(Emp1.Salary) > 10
12. -- ORDER BY
13. SELECT * FROM Emp1 ORDER BY FirstName
15. -- LIKE
16. SELECT * FROM Emp1 WHERE FirstName LIKE 'Shan%'
18. -- IN
19. SELECT * FROM Emp1 WHERE FirstName IN ('Shanmuga', 'Vijai')
21. -- AND / OR
22. SELECT * FROM Emp1 WHERE ID IN(0001, 0002, 0005) AND FirstName LIKE 'Shan%'
23. SELECT * FROM Emp1 WHERE ID IN(0001, 0002, 0005) OR FirstName LIKE 'Shan%'
25. -- MIN / MAX / COUNT / SUM
26. SELECT MIN(Salary) AS "MIN SALARY" FROM Emp1
27. SELECT MAX(Salary) AS "MIN SALARY" FROM Emp1
28. SELECT COUNT(*) AS "No.Of Emps" FROM Emp1 -- Will count NULL values also
29. SELECT SUM(Salary) AS "Total" FROM Emp1
31. -- DISTINCT
32. SELECT DISTINCT [ID], FirstName, LastName FROM Emp1
34. -- BETWEEN
35. SELECT [ID], FirstName, LastName, Salary FROM Emp1 WHERE Salary BETWEEN 10 AND 40

-- WildCards - %, _, [charlist], [!charlist] or [^charlist]

1. SELECT * FROM Emp1 WHERE Emp1.FirstName LIKE 'Shan%'
3. SELECT * FROM Emp1 WHERE Emp1.LastName LIKE '_el' -- _indicate any single char
5. SELECT * FROM Emp1 WHERE Emp1.FirstName LIKE 'Shan[abm]%'
7. SELECT * FROM Emp1 WHERE Emp1.FirstName LIKE 'Shan[^ma]%'  -- OR ---
9. SELECT * FROM Emp1 WHERE Emp1.FirstName LIKE 'Shan![ma]%'

Most Interviewers will ask these questions.
1. How to identify duplicate rows?

1. -- ************* Find Duplicate Rows *****************
2. WITH TEMP AS
3. (
4. SELECT
5. ROW_NUMBER() OVER(PARTITION BY FirstName ORDER BY FirstName) AS RN,
6. FirstName,
7. LastName,
8. Salary
9. FROM Emp1
10. )
12. SELECT * FROM TEMP WHERE RN > 1
13. --PARTITION BY refers based on which column, SQL engine need to find duplicates
15. SELECT * FROM TEMP WHERE RN = 1  --Records without duplicates

2. How to find 2nd max salary from Emp table?

1. -----------------------------------  Method:1 -----------------------------------------
2. --1st Max Salary:
3. SELECT MAX(Salary) FROM Emp1
5. --2nd Max Salary:
6. SELECT MAX(Salary) FROM Emp1 WHERE Salary < (SELECT MAX(Salary) FROM Emp1)
8. --3rd Max Salary:
9. SELECT MAX(Salary) FROM Emp1 WHERE Salary < (SELECT MAX(Salary) FROM Emp1 WHERE Salary < (SELECT MAX(Salary) FROM Emp1))
11. --and so on...
12. -----------------------------------  Method:2 -----------------------------------------
13. --Form a dynamic sub table with row numbers after ordering by salary and select by row number.
14. --Nth Max Salary:
15. SELECT FirstName, LastName, Salary FROM
16. (SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RN, FirstName, LastName, Salary FROM Emp1) AS E
17. WHERE RN = N

Hope this article will give an idea to beginners and a quick refresh for others !!!