Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

DAT 560G: Database Design and SQL

Fall 2022, Mini A

Practice Final Exam

A description of the database, sample questions, and answers

Instructions

This is an individual assignment. You may not discuss your approach to solving these questions with anyone, other than the instructor or TA. We will only answer clarification questions.

Please type your SID at the bottom of this page.

You are not permitted to use any online resources.

You are supposed to have only MySQL, Canvas, and Ms. Word open on your laptop. A browser is necessary at the last moments to submit your work on Canvas.

Submit the pdf file on Canvas page. Keep track of your time.

Don’t worry if you can’t complete this exam. Grades in the course will be curved.

GOOD LUCK

Background

Nowadays, many brands have been developing social marketing. Instagram is one of the digital channels.

Brand not only posts content by itself but also pays some social media influencers for promotion on Instagram. What’s more, there’re also some organic posts, which are naturally generated by influencers without paid partnership.

Database

Details of the database are described below. All the information is not real.

Each of these unique brands is categorized by industry, founded date, the number of employees, annual revenue in million dollars, the number of products, first login date on Instagram, the latest number of followers and the accumulative number of posts.  

Brand (Brand, Industry, FoundedDate, NumEmployees, AnnualRevenueInMillion, NumProducts, JoinDate, NumFollowers, NumPosts)

Each of these unique influencers is categorized by country, gender, the number of employees (if he/she has a team), annual revenue in dollars, first post date on Instagram, the latest number of followers, the accumulative number of posts, the average engagement of each post and the average number of monthly posts.

Influencer (Name, Country, Gender, NumEmployees, AnnualRevenue, FirstPost, NumFollowers, NumPosts, AvgEngagement, AvgMonthlyPost)

Each of these unique products is categorized by its brand, product type, unit price, first introduced date, all physical sales, all digital sales, all sales via Instagram and the average discount of all channels.

Product (ProductName, Brand, ProductType, Price, FirstIntroduced, AllPhysicalSales, AllDigitalSales, InsSales, AvgDiscount)

Some brands have established a long-term cooperation with some influencers. Brands would sponsor those influencers with a fixed monthly fee. Therefore, influencers would have a fixed monthly revenue from brands, apart from the additional advertising fee for certain products.

BrandSponser (Influencer, Brand, MonthlyRevenue)

Posts were posted in unique time. Each post is categorized by its poster names (this could be a brand or a influencer), the post type, which product it contained, the number of edits, the number of likes, the number of comments, the number of forwards and the advertising fee.

Post (PostTime, PosterName, PostType, ProductName, NumEdits, NumViews, NumLikes, NumComments, NumForwards, Advertisingfee)

Database

The E/R Diagram for the database is below.

Relations:

Brand (Brand, Industry, FoundedDate, NumEmployees, AnnualRevenueInMillion, NumProducts, JoinDate, NumFollowers, NumPosts)

Influencer (Name, Country, Gender, NumEmployees, AnnualRevenue, FirstPost, NumFollowers, NumPosts, AvgEngagement, AvgMonthlyPost)

Product (ProductName, Brand, ProductType, Price, FirstIntroduced, AllPhysicalSales, AllDigitalSales, InsSales, AvgDiscount)

BrandSponser (Influencer, Brand, MonthlyRevenue)

Post (PostTime, PosterName, PostType, ProductName, NumEdits, NumViews, NumLikes, NumComments, NumForwards, Advertisingfee)

 

For each question, submit your SQL code and a screen-shot of the results. If the results are too long, partial results are fine. Include the number of rows returned.

You may include a few additional attributes, but do not include many useless attributes. The attributes returned in the query must make it clear that the result is correct.

A. List products with all physical sales greater than 2,000. Include ProductName, Brand, ProductType, Price, AllPhysicalSales, AllDigitalSales and InsSales(sales via Instagram). Sort the results by ProductName.

SELECT ProductName, Brand, ProductType, Price, AllPhysicalSales, AllDigitalSales, InsSales

FROM Product

WHERE AllPhysicalSales > 2000

ORDER BY ProductName;

 /* 8 rows returned

 

B. Which influencers did not post for products this month? List Influencer Name, Country, and Gender. Sort the results in descending order of influencer name.

-- Solution 1

SELECT DISTINCT Name, Country, Gender

FROM Influencer LEFT JOIN Post

ON Influencer.Name = Post.PosterName

WHERE Post.PosterName IS NULL

ORDER BY NAME;

-- Solution2

SELECT DISTINCT Name, Country, Gender

FROM Influencer

WHERE NOT EXISTS (SELECT *

FROM Post

WHERE Influencer.Name = Post.PosterName)

ORDER BY NAME;

-- Solution3

SELECT DISTINCT Name, Country, Gender

FROM Influencer

WHERE Name NOT IN (SELECT PosterName

FROM Post)

ORDER BY NAME;  

 /* 4 rows returned

 

C. For those brand names containing 'i',which brand post for its products? Please list Brand Name, PostTime, NumViews (num of views), NumLikes (num of likes) and NumComments (number of comments). Sort the results by Brand Name.

SELECT PosterName, PostTime, NumViews, NumLikes,NumComments

FROM Post INNER JOIN Brand

ON Post.PosterName = Brand.Brand

WHERE PosterName like '%i%'

ORDER BY PosterName;

/* 9 rows returned

 

D. For Sportswear industry, identify its unique Brand, ProductName and total Advertisingfee for each brand. Sort the results by the increasing order of total advertising fee.

SELECT Brand.Brand, Product.ProductName, sum(Post.Advertisingfee)

FROM Brand LEFT JOIN Product using(Brand) LEFT JOIN Post using(ProductName)

WHERE Industry = 'Sportswear'

GROUP BY Brand.Brand

ORDER BY sum(Post.Advertisingfee);

/* 5 rows returned

 

E. For the brands that founded before 1960, how much money did each brand sponsor? Include unique Brand Name, FoundedDate, and total MonthlyRevenue they sponsored influencers. Only show brands sponsoring more than 10,000 MonthlyRevenue. Sort the results by decreasing order of total money.

SELECT DISTINCT Brandsponsor.Brand, FoundedDate, sum(MonthlyRevenue)

FROM Brandsponsor RIGHT JOIN Brand using (Brand)

WHERE FoundedDate < '1960-01-01'

GROUP BY Brandsponsor.Brand

HAVING sum(MonthlyRevenue) > 10000

ORDER BY sum(MonthlyRevenue) DESC;

/* 4 rows returned