CMU15445homework1 (2022fall)_cmu 15-445 homework 1 2022-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_40878302/article/details/136005200

写作业用的软件是sqlite3,有同学用的是sqlstudio，感觉那个要更好点，我这个就是个黑框框，不能直接看数据库。每个问题对应三部分：要求；语句；最后运行的结果。大家可以比照着看，我也不知道我的答案对不对

Q2
request
Find the 10 Sci-Fi works with the longest runtimes.
Details: Print the title of the work, the premiere date, and the runtime. The column listing the runtime should be suffixed with the string " (mins)", for example, if the runtime_mins value is 12, you should output 12 (mins). Note a work is Sci-Fi even if it is categorized in multiple genres, as long as Sci-Fi is one of the genres.
Your first row should look like this: Cicak-Man 2: Planet Hitam|2008|999 (mins)

sentence
sqlite> SELECT primary_title,premiered, CONCAT(runtime_minutes,‘(mins)’) as runtime_minutes
…> FROM titles
…> WHERE genres like ‘%Sci-Fi%’
…> ORDER BY runtime_minutes DESC
…> LIMIT 10;

results:
Cicak-Man 2: Planet Hitam|2008|999(mins)
Son of Frankenstein|1939|99(mins)
The Asphyx|1972|99(mins)
Sssssss|1973|99(mins)
Cheech and Chong’s Next Movie|1980|99(mins)
Mutant|1984|99(mins)
D.A.R.Y.L.|1985|99(mins)
Curse of Snakes Valley|1988|99(mins)
Memoirs of an Invisible Man|1992|99(mins)
Cyborg 2: Glass Shadow|1993|99(mins)

Q3(case…when语句，过滤筛选)
request
Determine the oldest people in the dataset who were born in or after 1900. You should assume that a person without a known death year is still alive.
Details: Print the name and age of each person. People should be ordered by a compound value of their age and secondly their name in alphabetical order. Return the first 20 results.
Your output should have the format: NAME|AGE

sentence
sqlite> SELECT name,CASE(‘age’) WHEN died IS NOT NULL THEN died-born ELSE 2023-born END
…> FROM people
…> WHERE born>=1900
…> ORDER BY died-born DESC,name
…> LIMIT 20;

Q4(表间连接)
request
Find the people who appear most frequently as crew members.
Details: Print the names and number of appearances of the 20 people with the most crew appearances ordered by their number of appearances in a descending fashion.
Your output should look like this: NAME|NUM_APPEARANCES

sentence
sqlite> SELECT name,COUNT(*) AS num_appearances
…> FROM people INNER JOIN crew ON people.person_id = crew.person_id
…> GROUP BY name
…> ORDER BY num_appearances DESC
…> LIMIT 20;

Q5
request
Compute intersting statistics on the ratings of content on a per-decade basis.
Details: Get the average rating (rounded to two decimal places), top rating, min rating, and the number of releases in each decade. Exclude titles which have not been premiered (i.e. where premiered is NULL). Print the relevant decade in a fancier format by constructing a string that looks like this: 1990s. Order the decades first by their average rating in a descending fashion and secondly by the decade, ascending, to break ties.
Your output should have the format: DECADE|AVG_RATING|TOP_RATING|MIN_RATING|NUM_RELEASES

sentence
sqlite> SELECT
…> CONCAT(premiered/1010,‘s’) AS decade,
…> ROUND(AVG(rating),2) AS avg_rating,
…> MAX(rating) AS top_rating,
…> MIN(rating) AS min_rating,
…> COUNT() AS num_releases
…> FROM titles INNER JOIN ratings ON titles.title_id = ratings.title_id
…> WHERE premiered IS NOT NULL
…> GROUP BY decade
…> ORDER BY avg_rating DESC, decade ASC;

results
2020s|7.08|10.0|1.0|18825
2010s|7.05|10.0|1.0|73097
1960s|6.88|10.0|1.0|7423
2000s|6.85|10.0|1.0|39267
1990s|6.81|10.0|1.0|18177
1950s|6.77|10.0|1.0|4346
1970s|6.72|10.0|1.0|8929
1980s|6.72|10.0|1.0|11856
1940s|6.21|9.7|1.9|1872
1920s|6.04|8.9|1.0|915
1930s|6.0|9.4|1.1|1901
1910s|5.69|9.2|1.2|647
1870s|5.25|5.3|5.2|2
1880s|5.15|6.5|4.4|13
1900s|5.05|8.8|2.5|417
1890s|4.41|7.6|2.0|437

Q6(子查询 with as 语句)
request
Determine the most popular works with a person who has “Cruise” in their name and is born in 1962.
Details: Get the works with the most votes that have a person in the crew with “Cruise” in their name who was born in 1962. Return both the name of the work and the number of votes and only list the top 10 results in order from most to least votes. Make sure your output is formatted as follows: Top Gun|408389

sentence
sqlite> WITH movie_id AS(SELECT crew.title_id AS title_id FROM crew INNER JOIN people ON crew.person_id = people.person_id WHERE people.name like ‘%Cruise%’ AND people.born=1962)
…> SELECT titles.primary_title,ratings.votes
…> FROM movie_id INNER JOIN titles ON titles.title_id=movie_id.title_id
…> INNER JOIN ratings on ratings.title_id=movie_id.title_id
…> ORDER BY ratings.votes DESC
…> LIMIT 10;

Q7
request
List the number of works that premiered in the same year that “Army of Thieves” premiered.
Details: Print only the total number of works. The answer should include “Army of Thieves” itself. For this question, determine distinct works by their title_id, not their names.

sentencew
sqlite> WITH year AS(SELECT premiered AS year_id FROM titles WHERE primary_title= ‘Army of Thieves’)
…> SELECT COUNT(DISTINCT title_id)
…> FROM year INNER JOIN titles ON year.year_id=titles.premiered;

result
63843

Q8
request
List the all the different actors and actresses who have starred in a work with Nicole Kidman (born in 1967).
Details: Print only the names of the actors and actresses in alphabetical order. The answer should include Nicole Kidman herself. Each name should only appear once in the output.
Note: As mentioned in the schema, when considering the role of an individual on the crew, refer to the field category. The roles “actor” and “actress” are different and should be accounted for as such.

sentence
WITH nicole_movie AS (
SELECT DISTINCT (crew.title_id ) AS title_id
FROM crew INNER JOIN people ON crew.person_id = people.person_id
WHERE people.name = ‘Nicole Kidman’ AND people.born=1967),
nicole_colle AS(
SELECT DISTINCT (crew.person_id) AS person_id
FROM crew
WHERE crew.category=‘actor’ OR crew.category=‘actress’ AND crew.title_id IN nicole_movie)
SELECT name
FROM nicole_colle INNER JOIN people ON nicole_colle.person_id = people.person_id
ORDER BY name ASC;

result
好多，写不了一点

Q9 ( NTILE(10) OVER (ORDER BY avg_rating ASC) 语句)
request
For all people born in 1955, get their name and average rating on all movies they have been part of through their careers. Output the 9th decile of individuals as measured by their average career movie rating.
Details: Calculate average ratings for each individual born in 1955 across only the movies they have been part of. Compute the quantiles for each individual’s average rating using NTILE(10).
Make sure your output is formatted as follows (round average rating to the nearest hundredth, results should be ordered by a compound value of their ratings descending and secondly their name in alphabetical order): Stanley Nelson|7.13
Note: You should take quantiles after processing the average career movie rating of individuals. In other words, find the individuals who have an average career movie rating in the 9th decile of all individuals.

sentence
WITH movie_1955 AS ( SELECT people.person_id AS person_id , people.name,titles.title_id AS title_id
FROM people INNER JOIN crew ON people.person_id = crew.person_id
INNER JOIN titles ON crew.title_id = titles.title_id
WHERE people.born=1955 AND titles.type = ‘movie’),
actor_rating AS(SELECT name,ROUND(AVG(rating),2) AS avg_rating
FROM ratings INNER JOIN movie_1955 ON ratings.title_id = movie_1955. title_id
GROUP BY movie_1955.person_id),
quartiles AS (
SELECT *, NTILE(10) OVER (ORDER BY avg_rating ASC) AS rating_quar
FROM actor_rating
)
SELECT name,avg_rating
FROM quartiles
WHERE rating_quar=9
ORDER BY avg_rating DESC, name ASC;

Q10
request
Concatenate all the unique titles for the TV Series “House of the Dragon” as a string of comma-separated values in alphabetical order of the titles.
Details: Find all the unique dubbed titles for the new TV show “House of the Dragon” and order them alphabetically. Print a single string containing all these titles separated by commas.
Hint: You might find Recursive CTEs useful.
Note: Two titles are different even if they differ only in capitalization. Elements in the comma-separated value result should be separated with both a comma and a space, e.g. “foo, bar”.

sentence1(用现有函数，可变换程度差，GROUP_CONCAT,LTrim,CONCAT)
WITH special_movie AS (
SELECT titles.title_id AS title_id
FROM titles
WHERE primary_title=‘House of the Dragon’ AND type = ‘tvSeries’),
name_seq AS(
SELECT DISTINCT(CONCAT( ’ ’ , akas.title)) AS title
FROM akas INNER JOIN special_movie ON akas.title_id=special_movie.title_id
ORDER BY akas.title ASC)
SELECT LTrim(GROUP_CONCAT(title))
FROM name_seq;

sentence2（这个好）
WITH special_movie AS(
SELECT DISTINCT(akas.title) AS title, titles.primary_title AS name
FROM akas INNER JOIN titles ON akas.title_id =titles.title_id
WHERE titles.primary_title = ‘House of the Dragon’ AND titles.type = ‘tvSeries’
ORDER BY akas.title),–找到符合要求的所有名称，已去重，已排序
num AS(
SELECT row_number() OVER (ORDER BY special_movie.name ASC) AS number, special_movie.title as title
FROM special_movie ),–给每个名称编号
–number title
–1 A Casa do Dragão
–2 A Guerra dos Tronos: A Casa do Dragão
–3 Dom smoka…
function_cte AS(
SELECT number, title
FROM num
WHERE number=1
UNION
SELECT num.number,function_cte.title || ', ’ || num.title
FROM num JOIN function_cte ON num.number=function_cte.number+1)
SELECT title
FROM function_cte
ORDER BY number DESC
LIMIT 1;

result
A Casa do Dragão, A Guerra dos Tronos: A Casa do Dragão, Dom smoka, Game of Thrones: A Casa do Dragão, Gia Tộc Rồng, House of the Dragon, La Casa del Dragón, La casa del dragón, Rod draka, Ród smoka, Sárkányok háza, Zmajeva kuća, Дом Дракона, Домът на дракона, Дім Дракона, Кућа змаја, בית הדרקון, آل التنين, ハウス・オブ・ザ・ドラゴン, 龍族前傳, 하우스 오브 드래곤