Module 4: SQL
Basic Structure
Topics to be covered in this Module
❖ Case Study- Clique Bait
❖ DML Commands
➢ Insert
➢ Update
➢ Select
➢ Delete
❖ Basic Structure of SQL Query
➢ Select
➢ From
➢ Where
➢ And/Or/Not/Between/Like/Distinct/Count
Digital Marketing Case study-Clique Bait
Introduction - Clique Bait, an online seafood store. The founder and
CEO, Danny, want your help to leverage to analyse data in the seafood
industry. In this case study, your task is to analyze the dataset and devise
innovative solutions to calculate funnel fallout rates for the Clique Bait
online store.
Entity Relationship Diagram
Table info - Users
The "Users" table represents the customers who visit the Clique Bait website. Each customer is identified
and tracked using their unique cookie ID. The table consists of the following fields:
user_id: An identifier for each user.
cookie_id: The unique identifier assigned to each user's cookie.
start_date: The date when the user first visited the website, indicating the start of their interaction with
Clique Bait.
Table info - Events
The "Events" table is used to log customer visits to the Clique Bait website. Each visit is recorded
with a unique visit ID and associated with a specific cookie ID.
visit_id: It represents the unique identifier for each visit made by a customer.
cookie_id: It refers to the identifier associated with a specific customer, allowing tracking of their
activities across multiple visits.
page_id:It indicates the identifier of the page visited by the customer during a particular visit.
event_type: It represents the type of event captured during the visit, such as a page view, ad
impression, add to cart, etc.
sequence_number: It denotes the order or sequence in which the events occurred within a visit, helping to
maintain the chronological record.
event_time: It represents the timestamp or date and time when the event occurred during the visit.
Table info - Event Identifier
The event_identifier table shows the types of events which are captured by Clique Bait’s digital data systems.
Table info - Campaign Identifier
This table shows information for the campaigns that Clique Bait has ran on their website so far in 2020.
campaign_id - field in the table represents the unique identifier for a campaign.
Products - field indicates the range or specific products associated with the campaign.
Campaign_name - field stores the name or title of the campaign.
Start_date - field indicates the date when the campaign starts
End_date - field represents the date when the campaign ends.
Table info - Page Hierarchy
This table lists all of the pages on the Clique Bait website which are tagged and have data passing through
from user interaction events.
Example-
Basic Structure of SQL Query
A SQL query is used to retrieve data from one or more tables. The basic structure of a SQL query includes the
following elements:
Select … From
Select: The SELECT keyword is used to specify the columns you want to retrieve data from.
From: The FROM keyword is used to specify the table or tables you want to retrieve data from.
Ques - Fetch all the record from the user table .
Select * from users
Where
Where: The WHERE keyword is used to specify a condition that must be met for a row to be included in the
result set.
Where is used to put conditions on the Table
Ques - Select the record from user whose
cookie_id is "863329"
select * from users where cookie_id="863329"
Conditions can be inequality/comparisons
Select * from users
where date(start_date) (> ,< ,!=, =,>=,<=) “2022-03-01"
Multiple Conditions – and/or/not
<> - Not Equal To Operator
Ques - Fetch the list of details from events table whose Ques - Fetch the list of detail from events table where
event_type is 1 and event_time is after the data event_type is not "2"
"2020-01-18 "
select * from events where event_type !=2
select * from events where event_type=1 and
date(event_time) >"2020-01-18"
Ques fetch the list of detail from events table where Ques - Fetch the record between the date "2020-01-29"
page_id=2 or sequence _number can be 5 and "2020-02-14" from events table
select * from events where page_id=2 or select * from events where date(event_time) between
sequence_number=5 "2020-01-29" and "2020-02-14"
Like is used to find parts of string (wildcard
searches)
Ques - find the page_name which starts with the letter “S” Ques - find the page_name which ends with the letter “A”
Select * from Page_Hierarchy where page_name like “s%” Select * from Page_Hierarchy where page_name like “%A”
Finds any page_name that start with "S" and ends with "N"
Select * from Page_Hierarchy where page_name like “s%n”
Select ….Distinct
Distinct is a keyword used in SQL to eliminate duplicate rows from a result set.
Ques - List all unique users id?
Select distinct (user_id) from users;
Count
The COUNT function is a SQL aggregate function that returns the number of rows in a table.
Ques - Count the total no. of user_id from the
user_table
Select count(*) from users
Count distinct
The COUNT DISTINCT function is used to count the number of unique values in a column of a table.
Ques - How many unique users are there?
select count(distinct user_id) as user_count from
users;
Sum
The SUM function is a SQL aggregate function that returns the sum of all values in a column.
Ques - What is the sum of all sequence
numbers for events with an event type of "click"
in the Events table?
SELECT SUM(sequence_number) AS
click_sum
FROM events
WHERE event_type = 2
Max
The MAX function is a SQL aggregate function that returns the maximum value in a column.
Ques - What is the highest sequence number
among all the events in the Events table?
SELECT MAX(sequence_number) AS
highest_sequence_number
FROM events;
Min
The MIN function is a SQL aggregate function that returns the minimum value in a column.
Ques - What is the earliest start date for a
campaign in the Campaign Identifier table?
SELECT MIN(start_date) AS earliest_start_date
FROM campaign_identifier;