DAT 560G: Database Design and SQL Fall 2022
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
2022-10-29