Gain insights into SQL by exploring database creation, data manipulation, and advanced queries. Learn about practical applications and practice for SQL interviews to build confidence and readiness.

sql.tar.gz

The ability to work SQL is becoming an increasingly in-demand skill, both for software developers and people in less technical roles. If you’re interested in learning SQL and have no prior experience with it, then this course will be your light in a dark tunnel.

You’ll start by covering the basics of SQL such as how to create a database, how to insert, query, and update data. You’ll also learn fundamental concepts that developers and data scientists use everyday such as multi-table operations, nested queries, and how to set up views. 

Throughout, you’ll get to execute SQL queries in your browser and see results in real-time - you won’t need to worry about set-up.

At the end of this course, you’ll also get some hands-on practice with common SQL interview questions, so when the time comes, you’ll be ready and confident to answer any question that comes your way. Let’s get started!

An Introductory Guide to SQL

# Multiple Triggers
It is possible to create triggers on a table whose action time and event are the same. Such triggers are fired in a sequence that is specified at the time of creation of the triggers. The __FOLLOWS__ and __PRECEDES__ keywords are used to define the sequence in which triggers associated with a table having the same action time and event execute.

## Syntax

>CREATE TRIGGER **trigger_name** 
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
>
>ON **table_name**
>
>[FOLLOWS | PRECEDES] **existing_trigger_name**
>
>FOR EACH ROW
>
>**trigger_body**

>Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy-paste the command **./DataJek/Lessons/50lesson.sh** and wait for the mysql prompt to start-up.


-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 

-- Query 1
CREATE TABLE GenderSummary (
  TotalMales INT NOT NULL,
  TotalFemales INT NOT NULL
);
 
CREATE TABLE MaritalStatusSummary (
  TotalSingle INT NOT NULL,
  TotalMarried INT NOT NULL,
  TotalDivorced INT NOT NULL
);
 
CREATE TABLE ActorsTableLog (
  RowId INT AUTO_INCREMENT PRIMARY KEY,
  ActorId INT NOT NULL,
  Detail VARCHAR(100) NOT NULL,
  UpdatedOn TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Query 2
INSERT INTO GenderSummary (TotalMales, TotalFemales) 
Values ((SELECT COUNT(Gender) FROM Actors WHERE Gender = 'Male'),
        (SELECT COUNT(Gender) FROM Actors WHERE Gender = 'Female'));
 
SELECT * FROM GenderSummary;
 
INSERT INTO MaritalStatusSummary (TotalSingle, TotalMarried, TotalDivorced) 
Values ((SELECT COUNT(MaritalStatus) FROM Actors WHERE MaritalStatus = 'Single'),
        (SELECT COUNT(MaritalStatus) FROM Actors WHERE MaritalStatus = 'Married'),
        (SELECT COUNT(MaritalStatus) FROM Actors WHERE MaritalStatus = 'Divorced'));
 
SELECT * FROM MaritalStatusSummary;

-- Query 3
DELIMITER **
CREATE TRIGGER UpdateGenderSummary
AFTER INSERT
ON Actors 
FOR EACH ROW
BEGIN
DECLARE count INT;
IF NEW.Gender = 'Male' THEN
    UPDATE GenderSummary
   SET TotalMales = TotalMales+1;
   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalMales value of GenderSummary table changed.');
ELSE  
   UPDATE GenderSummary
   SET TotalFemales = TotalFemales+1;
   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalFemales value of GenderSummary table changed.');
END IF;
END  **
DELIMITER ;

-- Query 4
DELIMITER **
CREATE TRIGGER UpdateMaritalStatusSummary
AFTER INSERT
ON Actors 
FOR EACH ROW
FOLLOWS UpdateGenderSummary
BEGIN
DECLARE count INT;
IF NEW.MaritalStatus = 'Single' THEN
    UPDATE MaritalStatusSummary
   SET TotalSingle = TotalSingle+1;
   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalSingle value of MaritalStatusSummary table changed.');
ELSEIF  NEW.MaritalStatus = 'Married' THEN
    UPDATE MaritalStatusSummary
   SET TotalMarried = TotalMarried+1;
   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalMarried value of MaritalStatusSummary table changed.');
ELSE
   UPDATE MaritalStatusSummary
   SET TotalDivorced = TotalDivorced+1;
   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalDivorced value of MaritalStatusSummary table changed.');
END IF;
END  **
DELIMITER ;

-- Query 5
INSERT INTO Actors (FirstName, SecondName, DoB, Gender, MaritalStatus,  NetWorthInMillions) 
VALUES ('Tom', 'Hanks', '1956-07-09', 'Male', 'Married', 350);
SELECT * FROM ActorsTableLog;

-- Query 6
SHOW TRIGGERS;

-- Query 7
SELECT
    trigger_name,
    action_order
FROM
    information_schema.triggers
WHERE
    trigger_schema = 'MovieIndustry';


1. To demonstrate the order in which two triggers execute for the same event, we will create a simple example. Suppose that we want to perform two tasks when a new record is inserted in the __Actors__ table. First, based on the gender of the actor, we want to update the __GenderSummary__ table. Second, based on his/her marital status, we want to update the __MaritalStatusSummary__ table. We will log these actions in a separate table __ActorsTableLog__ to show the order of execution of triggers. To create these tables, execute the following queries:

   ```mysql
   CREATE TABLE GenderSummary (
   TotalMales INT NOT NULL,
   TotalFemales INT NOT NULL
   );

   CREATE TABLE MaritalStatusSummary (
   TotalSingle INT NOT NULL,
   TotalMarried INT NOT NULL,
   TotalDivorced INT NOT NULL
   );

   CREATE TABLE ActorsTableLog (
   RowId INT AUTO_INCREMENT PRIMARY KEY,
   ActorId INT NOT NULL,
   Detail VARCHAR(100) NOT NULL,
   UpdatedOn TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   );
   ```

   Now run the following queries to enter data in the tables:

   ```mysql
   INSERT INTO GenderSummary (TotalMales, TotalFemales) 
   Values ((SELECT COUNT(Gender) FROM Actors WHERE Gender = 'Male'),
        (SELECT COUNT(Gender) FROM Actors WHERE Gender = 'Female'));

   SELECT * FROM GenderSummary;

   INSERT INTO MaritalStatusSummary (TotalSingle, TotalMarried, TotalDivorced) 
   Values ((SELECT COUNT(MaritalStatus) FROM Actors WHERE MaritalStatus = 'Single'),
        (SELECT COUNT(MaritalStatus) FROM Actors WHERE MaritalStatus = 'Married'),
        (SELECT COUNT(MaritalStatus) FROM Actors WHERE MaritalStatus = 'Divorced'));

   SELECT * FROM MaritalStatusSummary;
   ```

2. Now we will create the first trigger that updates __GenderSummary__ table after a row is inserted in the __Actors__ table as follows:

   ```mysql
   DELIMITER **

   CREATE TRIGGER UpdateGenderSummary
   AFTER INSERT
   ON Actors 
   FOR EACH ROW
   BEGIN

   DECLARE count INT;

   IF NEW.Gender = 'Male' THEN
    UPDATE GenderSummary
   SET TotalMales = TotalMales+1;

   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalMales value of GenderSummary table changed.');

   ELSE  
   UPDATE GenderSummary
   SET TotalFemales = TotalFemales+1;
  
   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalFemales value of GenderSummary table changed.');

   END IF;
   END  **

   DELIMITER ;
   ```
   In this trigger, we first check the __Gender__ of the newly inserted actor and increment the __TotalMales__ or __TotalFemales__ value accordingly. Then a row is inserted in the __ActorsTableLog__ which describes which value in the __GenderSummary__ table was changed.


3. Next, we will create another trigger __UpdateMaritalStatusSummary__ that will execute after the __UpdateGenderSummary__ trigger as follows:

   ```mysql
   DELIMITER **

   CREATE TRIGGER UpdateMaritalStatusSummary
   AFTER INSERT
   ON Actors 
   FOR EACH ROW
   FOLLOWS UpdateGenderSummary

   BEGIN

   DECLARE count INT;

   IF NEW.MaritalStatus = 'Single' THEN
    UPDATE MaritalStatusSummary
   SET TotalSingle = TotalSingle+1;

   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalSingle value of MaritalStatusSummary table changed.');

   ELSEIF  NEW.MaritalStatus = 'Married' THEN
    UPDATE MaritalStatusSummary
   SET TotalMarried = TotalMarried+1;

   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalMarried value of MaritalStatusSummary table changed.');

   ELSE
   UPDATE MaritalStatusSummary
   SET TotalDivorced = TotalDivorced+1;
  
   INSERT INTO ActorsTableLog (ActorId, Detail) 
   VALUES (NEW.Id, 'TotalDivorced value of MaritalStatusSummary table changed.');

   END IF;
   END  **

   DELIMITER ;
   ```
   The __FOLLOWS__ keyword is used to define the order of execution of the trigger to be after the __UpdateGenderSummary__ trigger.

   In this trigger, __IF THEN ELSEIF ELSE__ statements are used to check the __MaritalStatus__ of the newly inserted actor and corresponding value in __MaritalStatusSummary__ table is updated. Then a row is inserted in the __ActorsTableLog__ which describes which value in the __MaritalStatusSummary__ table was changed.

4. Both triggers are associated with the same event __AFTER INSERT ON ACTORS__. To test if the triggers are executed in the order defined, we will insert a row in the __Actors__ table and then check the __ActorsTableLog__ table.

   ```mysql
   INSERT INTO Actors (FirstName, SecondName, DoB, Gender, MaritalStatus,  NetWorthInMillions) 
   VALUES ('Tom', 'Hanks', '1956-07-09', 'Male', 'Married', 350);

   SELECT * FROM ActorsTableLog;
   ```



In this lesson, we will see how to write multiple triggers for a table which  have the same action time and event.

Introduction

Basic SQL

Joins

Nested Queries

Multi Table Operations

Views

Stored Procedures

Triggers

Miscellaneous

Practice & Prep

Epilogue

Multiple Triggers

Multiple Triggers

Syntax