MatthewDapcic_HW2

sql

School

University of Miami *

*We aren’t endorsed by this school

Course

628

Subject

Computer Science

Date

Dec 6, 2023

Type

sql

Pages

Uploaded by CountGorillaMaster665

/* INSTRUCTIONS: 1 Write a query to answer each question. 2 You do not have to additionally write an answer or comment, the query output is sufficient. 3 If you spot a typo or a question is worded ambiguously, just solve it however you understand it (leave a comment if you're nervous about it) 4 Alias column headers appropriately - no "code" in column headers 5 Round or format all numbers appropriately - Currencies should be formatted as currencies - Percentages should be formatted as percentages, rounded to two decimals - Large numbers should include the thousands separator SUBMISSIONS: * 5 pts Rename this file with your name (e.g. DougLehmann_HW1.sql) * 5 pts Submit the correct SQL file to Blackboard * Please refer to syllabus for grading policy * YOU GET ONE SUBMISSION (So stay organized, and know what you have where / what you're submitting the first time) * Late submission -20% per day / we're all busy, just submit what you have by the deadline and move on. */ -- Answer 1-13 the following using the films database USE films; SELECT * FROM films LIMIT 5; SELECT * FROM people LIMIT 5; SELECT * FROM reviews LIMIT 5; SELECT * FROM roles LIMIT 5; -- 1. Which 5 countries have the most films represented in the database? SELECT country AS 'Country', FORMAT(COUNT(title), 0) AS 'Number of Films' FROM films GROUP BY 1 ORDER BY COUNT(title) + 0 DESC LIMIT 5 ; -- 2. Which 5 films had the highest net profit (gross - budget)? SELECT title AS 'Film', CONCAT('$',FORMAT(gross - budget, 2)) AS 'Net Profit' FROM films ORDER BY (gross - budget) DESC LIMIT 5; -- 3. Return the year and number of films per year, and sort from most recent to oldest release year. Only return years that had fewer than 25 films released. SELECT

release_year AS 'Year', COUNT(title) AS 'Number of Films' FROM films GROUP BY 1 HAVING COUNT(title) < 25 ORDER BY 1 DESC; -- 4. Return the title and imdb score for the 3 films with the highest IMDB score. SELECT f.title AS 'Title', FORMAT(r.imdb_score,2) AS 'IMDB Score' FROM films f JOIN reviews r ON f.film_id = r.film_id ORDER BY 2 DESC LIMIT 3; -- 5. Obtain the total number of facebook likes by release year, order from most to least recent SELECT f.release_year AS 'Release Year', FORMAT(SUM(r.facebook_likes),0) AS 'Number of Facebook Likes' FROM films f JOIN reviews r ON f.film_id = r.film_id GROUP BY 1 ORDER BY 1 DESC; -- 6. What is the only film with more than one million facebook likes? SOLVE THIS WITH A JOIN SELECT f.title AS 'Film', FORMAT(SUM(r.facebook_likes),0) AS 'Number of Facebook Likes' FROM films f JOIN reviews r ON f.film_id = r.film_id GROUP BY 1 HAVING SUM(r.facebook_likes) > 1000000; -- 7. What is the only film with more than one million facebook likes? SOLVE THIS USING A SUBQUERY IN A WHERE STATEMENT SELECT title FROM films WHERE film_id IN( SELECT film_id FROM reviews WHERE facebook_Likes >1000000 ); -- 8. Return a table that provides the language and average number of facebook likes per film for Spanish, French, and Portuguese films. Round the number of likes to an integer, and order the table high to low SELECT f.title AS 'Film', f.language AS 'Language', FORMAT(AVG(r.facebook_likes), 0) AS 'Average Number of Facebook Likes' FROM films f JOIN reviews r ON f.film_id = r.film_id

WHERE Language = 'Spanish' OR Language = 'French' OR Language = 'Portuguese' GROUP BY 1 ORDER BY AVG(r.facebook_likes) DESC; -- 9. Return a table that provides the language and average IMDB score. Only include languages with an average IMDB score between 7 and 8. Order the results high to low based on average IMDB score. SELECT f.language, FORMAT(AVG(r.imdb_score), 2) AS 'Average IMDB Score' FROM films f JOIN reviews r ON f.film_id = r.film_id GROUP BY f.language HAVING AVG(r.imdb_score) BETWEEN 7 AND 8 ORDER BY AVG(r.imdb_score) DESC; -- 10. Which rating receives the most Facebook likes, on average, and what is that average number of likes? Round your answer to an integer. SELECT f.rating, FORMAT(AVG(r.facebook_likes), 0) AS 'Average Facebook Likes' FROM films f JOIN reviews r ON f.film_id = r.film_id GROUP BY 1 ORDER BY AVG(r.facebook_likes) DESC LIMIT 1; /* ecom Please use the 5 tables below in the ecom database for questions 11-17 The table contain the following: order_items - and itemized breakdown of each order orders - orders and order totals pageviews - tracks each page that a visitor views products - a list of our company's products visits - tracks website visitors, with information on where they came from or how they arrived to our website */ USE ecom; SELECT * FROM order_items LIMIT 5; SELECT * FROM orders LIMIT 5; SELECT * FROM pageviews LIMIT 5; SELECT * FROM products LIMIT 5; SELECT * FROM visits LIMIT 5; -- 11. What percent of website visits come from each source? Return a table that lists the Soure, Total Visits from that source, and % of Website Traffic that represents SELECT v.source AS 'Source', FORMAT(COUNT(p.page),0) AS 'Total Visits', CONCAT(ROUND(100*COUNT(p.page)/(SELECT COUNT(page) FROM pageviews),2),'%') AS 'Percent of Website Traffic'

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

FROM pageviews p JOIN visits v ON p.visit_id = v.visit_id GROUP BY 1; -- 12. Confirm that the numbers you calculated in 1) sum to 100% by querying that table and summing the percent of website traffic column. SELECT v.source AS 'Source', FORMAT(COUNT(p.page),0) AS 'Total Visits', CONCAT(ROUND(100*COUNT(p.page)/(SELECT COUNT(page) FROM pageviews),2),'%') AS 'Percent of Website Traffic', Total AS 'Total Website Traffic' FROM pageviews p JOIN visits v ON p.visit_id = v.visit_id JOIN ( SELECT CONCAT(ROUND(100*COUNT(page)/(SELECT COUNT(page) FROM pageviews),2),'%') AS 'Total' FROM pageviews )a GROUP BY 1; -- 13. Confirm that the numbers you calculated in 1) sum to 100% by treating your query in 1) as a common table expression and summing the percent of website traffic column. WITH s AS ( SELECT source FROM visits ), p AS ( SELECT COUNT(page) FROM pageviews ) SELECT source, FORMAT(COUNT(page),0) AS 'Total Visits', CONCAT(ROUND(100*COUNT(page)/(SELECT COUNT(page) FROM pageviews),2),'%') AS 'Percent of Website Traffic', Total FROM visits LEFT JOIN pageviews USING(visit_id) JOIN ( SELECT CONCAT(ROUND(100*COUNT(page)/(SELECT COUNT(page) FROM pageviews),2),'%') AS 'Total' FROM pageviews )d GROUP BY 1; -- 14. Calculate the conversion rate for each advertisement (defined as # orders / # visits). Include individuals that were not shown an advertisement in a "None" category. SELECT IFNULL(v.advertisement, 'None') AS 'Advertisement', CONCAT(ROUND(100*COUNT(od.order_date)/(SELECT COUNT(visit_date) FROM visits),2),'%') AS 'Conversion Rate' FROM visits v JOIN orders od ON v.visit_id = od.visit_id GROUP BY 1;

-- 15. Return the average conversion rate, using your subquery in 4 as a subquery. SELECT aa AS 'Advertisement', dd AS 'Conversion Rate', CONCAT(ROUND(100*(COUNT(od.order_date)/(SELECT COUNT(visit_date) FROM visits)/7),2),'%') AS 'Average Conversion Rate' FROM visits v JOIN orders od ON v.visit_id = od.visit_id JOIN (SELECT IFNULL(v.advertisement, 'None') AS 'aa', CONCAT(ROUND(100*COUNT(od.order_date)/(SELECT COUNT(visit_date) FROM visits),2),'%') AS 'dd' FROM visits v JOIN orders od ON v.visit_id = od.visit_id GROUP BY 1)c GROUP BY 1; -- 16. Return the average conversion rate, using your subquery in 4 as a common table expression. WITH ad AS ( SELECT advertisement FROM visits ), cr AS ( SELECT order_date FROM orders ) SELECT IFNULL(advertisement, 'None') AS 'Advertisement', CONCAT(ROUND(100*COUNT(order_date)/(SELECT COUNT(visit_date) FROM visits),2),'%') AS 'Conversion Rate', Avg AS 'Average Conversion Rate' FROM visits LEFT JOIN orders USING(visit_id) JOIN ( SELECT CONCAT(ROUND(100*(COUNT(order_date)/(SELECT COUNT(visit_date) FROM visits)/7),2),'%') AS 'Avg' FROM orders)r GROUP BY 1 ; -- 17. What is the average number of pages viewed per visit? SELECT FORMAT(COUNT(page)/(SELECT COUNT(visit_date) FROM visits),2) AS ' Average Number of Pages viewed per Visit' FROM pageviews ; /* miami Using the purchase orders data tables in the miami database (po_jan_22 through po_jul_22), return a table that includes the total spend and % of total spend for each month.

Diplsay month by name (e.g. January, February, etc) and order chronologically */ USE miami; -- 18. Solve using a common table expression - all_po - that brings all po data into one table WITH jan AS ( SELECT po_id, po_amount AS jan_spend FROM po_jan_22 GROUP BY 1 ), feb AS ( SELECT po_id, po_amount AS feb_spend FROM po_feb_22 GROUP BY 1 ), mar AS ( SELECT po_id, po_amount AS mar_spend FROM po_mar_22 GROUP BY 1 ), apr AS ( SELECT po_id, po_amount AS apr_spend FROM po_apr_22 GROUP BY 1 ), may AS ( SELECT po_id, po_amount AS may_spend FROM po_may_22 GROUP BY 1 ), jun AS ( SELECT po_id, po_amount AS jun_spend FROM po_jun_22 GROUP BY 1 ), jul AS ( SELECT po_id, po_amount AS jul_spend FROM po_jul_22 GROUP BY 1 ) SELECT po_id AS 'ID', CONCAT('$', FORMAT(jan_spend, 2)) AS 'January', CONCAT('$', FORMAT(feb_spend, 2)) AS 'February',

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

CONCAT('$', FORMAT(mar_spend, 2)) AS 'March', CONCAT('$', FORMAT(apr_spend, 2)) AS 'April', CONCAT('$', FORMAT(may_spend, 2)) AS 'May', CONCAT('$', FORMAT(jun_spend, 2))AS 'June', CONCAT('$', FORMAT(jul_spend, 2)) AS 'July', CONCAT('$', FORMAT(jan_spend + feb_spend + mar_spend + apr_spend + may_spend + jun_spend + jul_spend, 2)) AS 'Total' FROM jan LEFT JOIN feb USING (po_id) LEFT JOIN mar USING (po_id) LEFT JOIN apr USING (po_id) LEFT JOIN may USING (po_id) LEFT JOIN jun USING (po_id) LEFT JOIN jul USING (po_id) ; -- Do not understand why I am getting NULL for months other than January-- -- NOT GRADED. Try to solve this using a subquery / no CTE. HINT: The solution is long and not that nice, unless I'm missing some shortcut...

Related Documents

Untitled document.docx

2-more_exercises.pdf

hw5-F23.pdf

cs 251 a5.pdf

Emergency Preparedness & Response.docx

MatthewDapcic_HW3.sql

Minor Homework Flowcharting (1).docx

Assignemnt 3.docx.pdf

cis-120-008-2022 1 quiz 2.docx

discovering computers quiz 1.docx

Assignment 3.docx

Homework2_HiteshNarra.pdf

Recommended textbooks for you

Programming with Microsoft Visual Basic 2017

Computer Science

ISBN:9781337102124

Author:Diane Zak

Publisher:Cengage Learning

COMPREHENSIVE MICROSOFT OFFICE 365 EXCE

Computer Science

ISBN:9780357392676

Author:FREUND, Steven

Publisher:CENGAGE L

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:9781337508841

Author:Carey

Publisher:Cengage

Programming Logic & Design Comprehensive

Computer Science

ISBN:9781337669405

Author:FARRELL

Publisher:Cengage

Enhanced Discovering Computers 2017 (Shelly Cashm...

Computer Science

ISBN:9781305657458

Author:Misty E. Vermaat, Susan L. Sebok, Steven M. Freund, Mark Frydenberg, Jennifer T. Campbell

Publisher:Cengage Learning

CMPTR

Computer Science

ISBN:9781337681872

Author:PINARD

Publisher:Cengage

SEE MORE TEXTBOOKS

Recommended textbooks for you

Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Programming Logic & Design Comprehensive
Computer Science
ISBN:9781337669405
Author:FARRELL
Publisher:Cengage
Enhanced Discovering Computers 2017 (Shelly Cashm...
Computer Science
ISBN:9781305657458
Author:Misty E. Vermaat, Susan L. Sebok, Steven M. Freund, Mark Frydenberg, Jennifer T. Campbell
Publisher:Cengage Learning
CMPTR
Computer Science
ISBN:9781337681872
Author:PINARD
Publisher:Cengage