SQL A QuickView for Testers

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. link download