SQL



Trigger

A trigger is a SQL block which is fired automatically when a DML statement like Insert, Delete, and Update is executed on a database table.

Trigger Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition) 
BEGIN
--- SQL STATEMENTS 
END;

Consider the below example to understand triggers better.

Create RATING_HISTORY Table

CREATE TABLE RATING_HISTORY (
PRODUCT_NUMBER INT,
PRODUCT_DESC  VARCHAR(255),
PRODUCT_RATING INT
);

Create PRODUCT_DETAILS Table


CREATE TABLE PRODUCT_DETAILS
PRODUCT_NUMBER INT,
PRODUCT_DESC  VARCHAR(255),
PRODUCT_RATING INT
);

Write the Trigger RATING_TRIGGER 

CREATE or REPLACE TRIGGER RATING_TRIGGER  
BEFORE UPDATE OF PRODUCT_RATING
ON PRODUCT_DETAILS
FOR EACH ROW
BEGIN
INSERT INTO RATING_HISTORY
VALUES (
:old.PRODUCT_NUMBER,
:old. PRODUCT_DESC  ,
:old. PRODUCT_RATING);
END;

So when we run the below query a Row will be created in the RATING_HISTORY table.UPDATE PRODUCT_DEATILS SET PRODUCT_RATING = 4 WHERE PRODUCT_NUMBER = 1 ;

Primary Keys vs Unique Keys
Primary key does not allow null value but unique key allows null value.We can declare only one primary key in a table but a table can have multiple unique key. A PRIMARY KEY is both a unique constraint and it also implies that the column is NOT NULL.Unique keyes will make sure that the columns in which no two rows are similar

Indexed Coulmn
This way, it's easier to retrieve the table row value when the query is queried on that indexed table column. The disadvantage is that if you do many updating/deleting/create, SQL has to manage the indexing tables and that will be a performance bottleneck.



SQL joins

SQL join is  used to  combine  records  from two more  tables .There are mainly four types of Join as listed below.

1)INNER JOIN OR Simple  JOIN
2)LEFT JOIN or LEFT OUTER JOIN 
3)RIGHT JOIN OR RIGHT OUTER JOIN 
4)FULL OUTER JOIN


1) INNER JOIN or Simple JOIN :  This  join returns rows when there is a match in both tables.

Sample Query using INNER JOIN 

SELECT  COLUM_NAME(S)  FROM  TABLE1 INNER JOIN TABLE2 ON  TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME .

SELECT  USERS.USERS_ID  FROM  USERS INNER JOIN USERREG  ON   USERS.USERS_ID = USERREG.USERS_ID;

Sample Query using Simple JOIN 

SELECT  COLUM_NAME(S)  FROM  TABLE1 JOIN TABLE2 ON  TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME .

SELECT  USERS.USERS_ID  FROM  USERS JOIN USERREG  ON   USERS.USERS_ID = USERREG.USERS_ID;

2)LEFT JOIN: This join returns all rows from the left table, and the matched rows from the right table.

Sample Query using LEFT JOIN 

SELECT  COLUM_NAME(S)  FROM  TABLE1 LEFT  JOIN TABLE2 ON  TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME

SELECT  USERS.USERS_ID  FROM  USERS LEFT JOIN USERREG  ON   USERS.USERS_ID = USERREG.USERS_ID;

Sample Query using LEFT OUTER JOIN 

SELECT  COLUM_NAME(S)  FROM  TABLE1  LFET OUTER JOIN TABLE2 ON  TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME

SELECT  USERS.USERS_ID  FROM  USERS LEFT OUTER JOIN USERREG  ON   USERS.USERS_ID = USERREG.USERS_ID;

3)RIGHT JOIN: This Join Return all rows from the right table, and the matched rows from the left table.

Sample Query using RIGHT JOIN 

SELECT  COLUM_NAME(S) FROM  TABLE1 RIGHT  JOIN TABLE2 ON  TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME

SELECT  USERS.USERS_ID  FROM  USERS RIGHT  JOIN USERREG  ON   USERS.USERS_ID = USERREG.USERS_ID;

Sample Query using RIGHT OUTER JOIN 

SELECT  COLUM_NAME(S)  FROM TABLE1 RIGHT OUTER  JOIN TABLE2 ON  TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME

SELECT  USERS.USERS_ID  FROM  USERS RIGHT OUTER JOIN USERREG  ON   USERS.USERS_ID = USERREG.USERS_ID;


4) FULL OUTER JOIN: This Join returns rows when there is a match in one of the tables. The FULL OUTER JOIN keyword returns all the rows from the left table , and all the rows from the right table.

Sample Query using FULL OUTER JOIN 

SELECT  COLUM_NAME(S)  FROM TABLE1 FULL OUTER  JOIN TABLE2 ON  TABLE1.COLUMN_NAME = TABLE2.COLUMN_NAME

SELECT  USERS.USERS_ID  FROM  USERS  FULL OUTER JOIN USERREG  ON   USERS.USERS_ID = USERREG.USERS_ID;


No comments:

Post a Comment