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



Comments

Post a Comment

Popular posts from this blog

Self Discipline Benefits and Importance

-
Image
                                       Self Discipline Benefits and Importance Welcome to my blog and thank you for this much support. So let's start. Today's blog is based on " Self Discipline Benefits and Importance ". Self-discipline is one of the most important and useful skills everyone should possess. This skill is essential in every area of life, and though most people acknowledge its importance, very few do something to strengthen it. Contrary to common belief, self-discipline does not mean being harsh toward yourself or living a limited, restrictive lifestyle. Self-discipline means self-control, which is a sign of inner strength and control of yourself, your actions, and your reactions. Self-discipline gives you the power to stick to your decisions and follow them through, without changing your mind, and is, therefore, one of the important requirements for achieving goals. The possession of this skill enables you to persevere with your d
Read more

The curriculum of our current education system

-
Image
                                        The curriculum of our current education system Today's blog is based on " The curriculum of our current education system ". I wholeheartedly agree that the tight curriculum of our current education system leaves no room for imagination and creativity. The academic pressure is so much on them, they have forgotten their innate creativity and imagination. They are always under the pressure of studies. This pressure is put on them from the beginning of the session. Formative assessment activities of all the subjects, studies and their assignments of all the subjects, unit tests, co-curricular activities, homework, classwork, projects, practical exams, summative examination, date sheet, result, again the new term, etc. So much of pressure on the young growing children! Are we educating them or training them in the art of being stressful throughout their childhood? Even while studying inside the class, they feel so burdene
Read more

Blog 1.2

-
Image
                                                      Journey..... Welcome to my blog and thank you for this much of support. so let's start. I was in the first year when I joined NCC as a cadet. I was not aware of what is NCC? but I knew one thing that for NCC I have to wear a uniform. I was excited. After a few months, people started noticing that there is a cadet name Arpit Mishra he is very punctual and he can do anything. Major Neeti Prakash was NCC Officer. She worked in PSIT as a registrar. She called me one day and asked me, do you want to work with me in an event? I said yes ma'am why not. I managed my first event as a coorganizer and that event was TedX. She was very happy while seeing my hard work and dedication to my work. We organize many events together. After a few months I got my first NCC Rank and that was Under Officer. I can't forget that day. after a year I got my second and higher rank in NCC and that was Senior Under Officer. I trained many jun
Read more