Basics of SQL Part 7

 Triggers in SQL

A trigger is a special type of stored procedure that runs automatically when an event occurs in the database server. 

Types of triggers in SQL

- Data Manipulation trigger

- Data Definition trigger

- Logon trigger

How to use before insert trigger? 

Trigger Command: delimiter //

                               create trigger trigger_name()

                                before insert on table_name()

                                for each row

                                if new.marks < 0 then set new.marks =50;

                                 end if ; //

Sample: delimiter //

              create trigger marks_verify_st

              before insert on student

              for each row 

              if new mark < 0 then set new.marks = 50;

             end if ; //

How to drop the trigger?

Syntax : drop trigger trigger_name ;

Views in SQL

Views are actually virtual tables that do not store data from the rows but display data stored in other tables. views are created by joining one or more tables. 

Syntax: create view view_name as select column_name from table_name;

Sample: create view F as select customer_name, Phone, City from customer; 

How to fetch view ? 

Syntax: select * from view_name;

Sample: select * from F;

How to create a view by using join?

Syntax: create view view_name as select column_name from table1 as P

             inner join table2 as p2

             on p.coomonfiled=p2.commonfiled;

Sample: create view product_description

              as select product_name, quantityinstock, msrp, from products as p

             inner join product_line as pl

             on p.productline=pl.productline;

How to rename a view?

Syntax: rename table table name to new_table_name;

Sample: rename table product to vehicle;

How to display views?

Syntax: show full tables where table type ='view'

How to delete the view?

Syntax: drop view view_name;

Windows Function

Windows functions are used to solve analytical problems.

Syntax: select column_name over (partition by ) as column name from table_name;

Sample: select emp_name, age, dept, sum(salary) over (partition by dept) as total salary from emp;

#data #event #sql #day7 #database #windows #function 

Mail id: arpitmishra1629.ftp@gmail.com

Contact no: 8127430702

Linkedin: https://www.linkedin.com/in/arpit-mishra-9319b5131