MatthewDapcic_HW2
.sql
keyboard_arrow_up
School
University of Miami *
*We aren’t endorsed by this school
Course
628
Subject
Computer Science
Date
Dec 6, 2023
Type
sql
Pages
7
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
Related Questions
example inn 100 sqlmstatement
arrow_forward
SQL
arrow_forward
Database design
Objectives:
To understand and be able to write simple SQL queries
Instruction:
Refer to HR schema to answer the questions
Include screenshot of your code and output of each question
1. Write SQL query that displays the last name and salary of employees earning more than $12,000.
2. Write SQL query that displays the last name and department number for employee number 176.
3. Write SQL query to display the last name and salary for all employees whose salary is not in the range $5,000 through $12,000.
4. Write SQL query to display the last name, job ID, and hire date for employees with the last names of Matos and Taylor. Order the query in ascending order by hire date.
5. Display the last name and department ID of all employees in departments 20 or 50 in ascending alphabetical order by name.
6. Write a query that displays the last names of all employees who have both an “a” and an “e” in their last name.
7. Write a query to display the employee…
arrow_forward
Don't want copy paste answer
arrow_forward
SQL question help
arrow_forward
PATHS is a table that contains information about paths on a number line. The
structure of PATHS is as follows, where x1 and x2 represent that there is a path
from Coordinate x1 to Coordinate x2 (You can't move from Coordinate x2 to
Coordinate x1 ).
NAME
ΤΥΡE
NULLABLE
X1
INT
FALSE
X2
INT
FALSE
Problem
Please write an SQL statement that returns the beginning and end point of each
path in PATHS . Sort them by the value of the beginning point in ascending order.
Constraints
• In the case where a direct path from Coordinate a to Coordinate b is
available, no path from Coordinate b to Coordinate a will be given.
|x1-x2| = 1
• No path will overlap with one another.
Example
Suppose that PATHS is as follows:
x1
x2
1
2
2
3
4
7
7
6
This table can be visualized as follows:
START
END
END
START
7
8.
9
Therefore, your SQL statement must return the following:
start
end
1
4
8
6
arrow_forward
SQL query Knowledge
arrow_forward
( Please answer this question immediately I do not have time )
Use the following table to answer the questions below:
Table name: Books
Column Name
Role
Data Type
Length
Constraint
bookID
Stores the ID of a book
Number
5
Primary key
bookTitle
Stores the title of a book
Varchar2
50
-
bookISBN
Stores the ISBN of a book
Number
13
Cannot be null and duplicated
publicationDate
Store the publication date of a book
Date
-
Cannot be null
bookPrice
Stores the price of a book
Number
2,2
Cannot be null
a. Write in SQL a command that creates the table Books according to the above description
b. Write in SQL a statement that adds a new column named publisherID of type Char(10). This column should be defined as a foreign key that relates the table Books to the table Publisher
c. Write in SQL a command that deletes the books that are published before 20-Aug-2010
d. Write a SQL query that displays the number of books that are published in the year 2018
e. Write in SQL a…
arrow_forward
Q1. Write a SELECT statement that returns these columns from the Invoices table: The invoice
_total column A column that uses the ROUND function to return the invoice_total column with
1 decimal digit A column that uses the ROUND function to return the invoice_total column
with no decimal digits
Q1. Write a SELECT statement that returns these columns from the Invoices table:
• The invoice_total column
• A column that uses the ROUND function to return the invoice_total column with 1
decimal digit
• A column that uses the ROUND function to return the invoice_total column with no
decimal digits
invoice_total one digit
3813.33
3813.3
zero_digits
3813
arrow_forward
SEE MORE QUESTIONS
Recommended textbooks for you
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
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
Related Questions
- example inn 100 sqlmstatementarrow_forwardSQLarrow_forwardDatabase design Objectives: To understand and be able to write simple SQL queries Instruction: Refer to HR schema to answer the questions Include screenshot of your code and output of each question 1. Write SQL query that displays the last name and salary of employees earning more than $12,000. 2. Write SQL query that displays the last name and department number for employee number 176. 3. Write SQL query to display the last name and salary for all employees whose salary is not in the range $5,000 through $12,000. 4. Write SQL query to display the last name, job ID, and hire date for employees with the last names of Matos and Taylor. Order the query in ascending order by hire date. 5. Display the last name and department ID of all employees in departments 20 or 50 in ascending alphabetical order by name. 6. Write a query that displays the last names of all employees who have both an “a” and an “e” in their last name. 7. Write a query to display the employee…arrow_forward
- Don't want copy paste answerarrow_forwardSQL question helparrow_forwardPATHS is a table that contains information about paths on a number line. The structure of PATHS is as follows, where x1 and x2 represent that there is a path from Coordinate x1 to Coordinate x2 (You can't move from Coordinate x2 to Coordinate x1 ). NAME ΤΥΡE NULLABLE X1 INT FALSE X2 INT FALSE Problem Please write an SQL statement that returns the beginning and end point of each path in PATHS . Sort them by the value of the beginning point in ascending order. Constraints • In the case where a direct path from Coordinate a to Coordinate b is available, no path from Coordinate b to Coordinate a will be given. |x1-x2| = 1 • No path will overlap with one another. Example Suppose that PATHS is as follows: x1 x2 1 2 2 3 4 7 7 6 This table can be visualized as follows: START END END START 7 8. 9 Therefore, your SQL statement must return the following: start end 1 4 8 6arrow_forward
- SQL query Knowledgearrow_forward( Please answer this question immediately I do not have time ) Use the following table to answer the questions below: Table name: Books Column Name Role Data Type Length Constraint bookID Stores the ID of a book Number 5 Primary key bookTitle Stores the title of a book Varchar2 50 - bookISBN Stores the ISBN of a book Number 13 Cannot be null and duplicated publicationDate Store the publication date of a book Date - Cannot be null bookPrice Stores the price of a book Number 2,2 Cannot be null a. Write in SQL a command that creates the table Books according to the above description b. Write in SQL a statement that adds a new column named publisherID of type Char(10). This column should be defined as a foreign key that relates the table Books to the table Publisher c. Write in SQL a command that deletes the books that are published before 20-Aug-2010 d. Write a SQL query that displays the number of books that are published in the year 2018 e. Write in SQL a…arrow_forwardQ1. Write a SELECT statement that returns these columns from the Invoices table: The invoice _total column A column that uses the ROUND function to return the invoice_total column with 1 decimal digit A column that uses the ROUND function to return the invoice_total column with no decimal digits Q1. Write a SELECT statement that returns these columns from the Invoices table: • The invoice_total column • A column that uses the ROUND function to return the invoice_total column with 1 decimal digit • A column that uses the ROUND function to return the invoice_total column with no decimal digits invoice_total one digit 3813.33 3813.3 zero_digits 3813arrow_forward
arrow_back_ios
arrow_forward_ios
Recommended textbooks for you
- Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:CengageProgramming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage LearningCOMPREHENSIVE MICROSOFT OFFICE 365 EXCEComputer ScienceISBN:9780357392676Author:FREUND, StevenPublisher:CENGAGE L
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
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