1.- Which of the following queries would return rows with an event_date earlier than May 4, 2019?
a) SELECT * FROM table WHERE event_date < ‘2019-05-04’
b) SELECT * FROM table WHERE event_date = ‘2019-05-4’
c) SELECT * FROM table WHERE event_date LIKE ‘2019-05%’
d) None of the above
2.- To combine data from two tables, the following clauses are valid:
a) LEFT JOIN
b) INNER JOIN
c) JOIN
d) UNION
e) All of the above
f) None of the above
3.-Given the two tables below and the following query:
SELECT city.user_id, [Link], city.city_name
FROM city
LEFT JOIN jobs on jobs.user_id = city.user_id
Table: City Table: Jobs
____________________ ___________________
user_id | city_name user_id | title
-------------------------- ---------------------------
matt | NY matt | data scientist
eric | CHI eric | data engineer
art | POR kristin | consultant
kristin | NY art | data engineer
susie | NY susie | consultant
anne | data scientist
adam | president
How many rows will your query produce?
4.- Given the two tables below, and the following query:
Confidential - Not for Public Consumption or Distribution
Select city.user_id, [Link], city.city_name from city left join jobs on jobs.user_id = city.user_id
Table: City Table: Jobs
____________________ ___________________
user_id | city_name user_id | title
-------------------------- ---------------------------
matt | NY matt | data scientist
eric | CHI eric | data engineer
art | POR kristin | consultant
kristin | NY art | data engineer
susie | NY susie | consultant
anne | data scientist
adam | president
How many rows will have an empty or null value for the column city_name?
5.- Given the two tables below, and the following query:
SELECT jobs.user_id, [Link], city.city_name from jobs left join city on jobs.user_id = city.user_id
Table: City Table: Jobs
____________________ ___________________
user_id | city_name user_id | title
-------------------------- ---------------------------
matt | NY matt | data scientist
eric | CHI eric | data engineer
art | POR kristin | consultant
kristin | NY art | data engineer
susie | NY susie | consultant
anne | data scientist
adam | president
How many rows will your query produce?
6.- Given the two tables below, and the following query:
select jobs.user_id, [Link], city.city_name from jobs left join city on jobs.user_id = city.user_id
Confidential - Not for Public Consumption or Distribution
Table: City Table: Jobs
____________________ ___________________
user_id | city_name user_id | title
-------------------------- ---------------------------
matt | NY matt | data scientist
eric | CHI eric | data engineer
art | POR kristin | consultant
kristin | NY art | data engineer
susie | NY susie | consultant
anne | data scientist
adam | president
How many rows will have an empty or null value for city ?
7.- Given the table below, which of the following queries will produce the result of 0.56?
Table: Programmatic_cost
user_id | campaign_id | event_date | cost
10001 | 763765 | 5/3/2019 | 0.09
10001 | 763765 | 5/3/2019 | 0.05
10001 | 763765 | 5/3/2019 | 0.11
10002 | 763765 | 5/3/2019 | 0.01
10002 | 763765 | 5/3/2019 | 0.04
10002 | 763765 | 5/4/2019 | 0.14
10002 | 763765 | 5/4/2019 | 0.12
a) select sum(cost) from programmatic_cost
b) select count(cost) from programmatic_cost
c) sum (cost) from programmatic_cost
d) select sum(cost) from programmatic_cost group by 1
e) select sum(cost) from programmatic_cost where event_date = ‘2019-05-4’
8.- Given the table programmatic_cost , which of the queries below will produce the following result?
user_id | event_date | cost
10001 | 5/3/2019 | 0.25
10002 | 5/3/2019 | 0.05
Confidential - Not for Public Consumption or Distribution
10002 | 5/4/2019 | 0.26
Table: programmatic_cost
user_id | campaign_id | event_date | cost
10001 | 763765 | 5/3/2019 | 0.09
10001 | 763765 | 5/3/2019 | 0.05
10001 | 763765 | 5/3/2019 | 0.11
10002 | 763765 | 5/3/2019 | 0.01
10002 | 763765 | 5/3/2019 | 0.04
10002 | 763765 | 5/4/2019 | 0.14
10002 | 763765 | 5/4/2019 | 0.12
a) select *, sum(cost) from programmatic_cost
b) select user_id, event_date, sum(cost) from programmatic_cost
c) select sum(cost) user_id, event_date, campaign_id from programmatic_cost group by user_id, event_date
d)select user_id, event_date, sum(cost) from programmatic_cost group by user_id, event_date
9.- Given the table adoptions, which query will return the names of dogs adopted before 2019?
Table: adoptions
pet_name | species | adoption_date | weight
roby | cat | 1/15/2015 | 12
grizz | cat | 11/20/2015 | 14
jake | dog | 3/14/2013 | 9
eddle | cat | 1/23/2019 | 7
chevvy | dog | 06/15/2017 | 18
a) select * from adoptions where adoption_date < ‘2019-01-01’
b) select pet_name, adoption_date from adoptions
c) select species, adoption_date from adoptions where adoption_date =<’2019-01-01’
d) select pet_name from adoptions where species = ´dog´ and adoption_date < ‘2019-01-01’
10.- Given the table adoptions, which query will return cats that weigh more than 10 lbs. along their
adoption date?
Table: adoptions
pet_name | species | adoption_date | weight
Confidential - Not for Public Consumption or Distribution
roby | cat | 1/15/2015 | 12
grizz | cat | 11/20/2015 | 14
jake | dog | 3/14/2013 | 9
eddle | cat | 1/23/2019 | 7
chevvy | dog | 06/15/2017 | 18
a) select * from adoptions where species = ‘cat’
b) select pet_name from adoptions where weight > 10
c) select pet_name, adoption_date from adoptions where weight > 10
d) select pet_name, adoption_date from adoptions where species = ‘cat’ and weight > 10
11.- Given the table adoptions, fill in the blank to create a query which will return the earliest
adoption_date in the table
Table: adoptions
pet_name | species | adoption_date | weight
roby | cat | 1/15/2015| 12
grizz | cat | 11/20/2015 | 14
jake | dog | 3/14/2013 | 9
eddle | cat | 1/23/2019 | 7
chevvy | dog | 06/15/2017 | 18
select (adoption_date) as earliest_adoption from adoptions
12.- Given the table apple_sales , which query below will properly calculate the total production cost
for each product in 2017? Total production cost is defined as the number of units multiplied by the unit
cost
Table: apple_sales
Sku | product_name | year | units | sale_price | unit_cost
10001 | iPhone 7S | 2017 | 1500 | 500 | 100
Confidential - Not for Public Consumption or Distribution
10002 | iPhone 8 | 2017 | 2500 | 600 | 110
10003 | iPad air | 2017 | 700 | 850 | 180
10004 | iPad pro | 2017 | 500 | 1000 | 200
10001 | iPhone 7S | 2018 | 1600 | 500 | 97
10002 | iPhone 8 | 2018 | 2600 | 600 | 105
10003 | iPad Air | 2018 | 725 | 850 | 170
10004 | iPad Pro | 2018 | 450 | 1000 | 190
a) select sku, product_name, units*unit_cost as total_production_cost from apple_sales where year = 2017
b) select product, multiply (units, unit_cost) as total_production_cost from apple_sales where year = 2017
c) select sku_product_name, units + unit_cost as total_production_cost from apple_sales where year = 2017
d) select sku, product_name, units x unit_cost as total_production_cost from apple_sales where year = 2017
13.- Net revenue is defined as units multiplied by sale_price minus total production cost.
Which query below will properly calculate net revenue for each product for 2017 and 2018?
Table: apple_sales
Sku | product_name | year | units | sale_price | unit_cost
10001 | iPhone 7S | 2017 | 1500 | 500 | 100
10002 | iPhone 8 | 2017 | 2500 | 600 | 110
10003 | iPad air | 2017 | 700 | 850 | 180
10004 | iPad pro | 2017 | 500 | 1000 | 200
10001 | iPhone 7S | 2018 | 1600 | 500 | 97
10002 | iPhone 8 | 2018 | 2600 | 600 | 105
10003 | iPad Air | 2018 | 725 | 850 | 170
10004 | iPad Pro | 2018 | 450 | 1000 | 190
a) select sku, p roduct_name, year, units * sale_price + units / unit_cost as total_revenue from apple_sales
b) select sku, product_name, year, (unit/unit_cost) / (sale_price * unit_cost) as total_revenue from apple_sales
c) select sku, product_name, year, (units * sale_price) / unit_cost as total_revenue from apple_sales
d) select sku, product_name, year (units * sale_price) – (units * unit_cost) as total_revenue from apple_sales
14.- Reach and frequency are important measures of advertising campaigns.
Given the table to impressions, which query below will help to answer how many unique user_ids were
reached on each site as a part of campaign 77654?
Table: impressions
user_id | site_id | campaign_id | placement_id | event_time
10001 |23424 | 77564 | 871014 | 1/23/2019 [Link] PM
Confidential - Not for Public Consumption or Distribution
10002 |56547 | 77564 | 871014 | 1/24/2019 [Link] PM
10001 |23478 | 77564 | 871014 | 1/25/2019 [Link] PM
10003 |23424 | 77564 | 871014 | 1/26/2019 [Link] PM
10004 |23424 | 77564 | 871014 | 1/27/2019 [Link] PM
a) select count(distinct(user_id)) as unique_reach, site_id from impressions where campaign_id = 77654 group by
site_id
b) select count(site_id) as unique_reach, site_id from impressions where campaign_id = 77654 group by site_id
c) select count(distinct(user_id)) as unique_reach, campaign_id from impressions where campaign_id = 77654
group by campaign_id
d) select count(user_id) as unique_reach, site_id, from impressions where campaign_id = 77654 group by site_id
15.- Given the table paid_search, the Ad ID column represents the three major search engines
( google, yahoo, bing)
We would like to create a new column which uses the name of the search engine in place of the ID number.
Fill one word into each blank in the statement below to create the new column.
(case)
(when) ad_id = 311528944 then ‘Google’
(when) ad_id = 297355014 then ‘Yahoo’
(when) ad_id = 297356392 then ‘Bing’
(else) ‘other’
(end) as search_engine
16 .- Reach and frequency are important measures of advertising campaigns.
Which query will calculate the number of impressions that each user _id saw during campaign 77654?
Table: impressions
user_id | site_id | campaign_id | placement_id | event_time
10001 |23424 | 77564 | 871014 | 1/23/2019 [Link] PM
Confidential - Not for Public Consumption or Distribution
10002 |56547 | 77564 | 871014 | 1/24/2019 [Link] PM
10001 |23478 | 77564 | 871014 | 1/25/2019 [Link] PM
10003 |23424 | 77564 | 871014 | 1/26/2019 [Link] PM
10004 |23424 | 77564 | 871014 | 1/27/2019 [Link] PM
a) select user_id, count(*) from impressions group by user_id
b) select count(distinct(user_id)) from impressions
c) select user_id, sum(*) from impressions
d) All of the above
e) None of the above
Confidential - Not for Public Consumption or Distribution
