2022 FALL CMU15445：hw1

目的

​ 目的就是让你熟悉一下SQL查询的书写，大致了解查询的

• 语法
• 嵌套
• 执行优先顺序

题目

Q1

没什么好说的，就是样例

SELECT DISTINCT(language)
FROM akas
ORDER BY language
LIMIT 10;

Q2

||是用来append string的，然后就是基本的语句

SELECT PRIMARY_TITLE, PREMIERED, RUNTIME_MINUTES ||' (mins)'
FROM titles
WHERE genres like "%Sci-Fi%" 
ORDER BY runtime_minutes DESC
LIMIT 10; 

Q3

需要一个coalesce函数来设置default值，搜了一下才找到这个函数

SELECT NAME, (coalesce(died,2022) - born) as AGE 
FROM people
where born >= 1900
order by age desc, name asc
limit 20;

Q4

出现了第一次嵌套

select NAME, count(person_id) as NUM_APPEARANCES
From (SELECT *
FROM crew inner join people on crew.person_id = people.person_id)
group by name
order by num_appearances desc
LIMIT 20;

Q5

这个比较坑，因为计算后得到数字，再append string就失效了，所以找了半天终于找到了个能用的，原理是转换为4位的string，再append就行了

SELECT CAST(premiered / 10 * 10 AS CHAR(4)) || '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
LIMIT 1000;

Q6

普通联表

SELECT primary_title , votes
FROM titles inner join ratings 
on titles.title_id = ratings.title_id
where titles.title_id in (select title_id
from crew inner join people 
on crew.person_id = people.person_id
where name like '%Cruise%' and  born = 1962)
order by votes desc
LIMIT 10;

Q7

蛮简单的

SELECT count(title_id)
FROM titles 
where premiered = (
select premiered
from titles 
where primary_title == 'Army of Thieves'
);

Q8

两次嵌套，但是在纸上画一画就行，主要是抓住相同的key，逐步找到目标select就行

SELECT distinct name 
FROM crew inner join people 
on crew.person_id = people.person_id
where category in ('actor','actress') and title_id in(
	select distinct title_id 
	from crew 
	where person_id = (
		select person_id
		from people
		where name = 'Nicole Kidman' and born = 1967
		)
	)
order by name asc;

Q9

需要学习一下NTILE，意思就是按照规则等分表到bucket中，比如m行n等分：NTILE(n) over (order by avg(rating) asc)（默认是asc）。首先每bucket分配m/n，余数mod不够m行则前mod bucket分配+1，如果够m行则继续除法分配

这里由于约束较多，于是直接联表，也可以使用select得到的结果进行union，注意union是上下拼接（自动distinct），join是左右拼接

SELECT name , ROUND(avg_rating, 2)
FROM (
    SELECT name, AVG(rating) as avg_rating, NTILE(10) OVER (ORDER BY AVG(rating)) as decile
    FROM people
    INNER JOIN crew ON people.person_id = crew.person_id
    INNER JOIN ratings ON crew.title_id = ratings.title_id
    INNER JOIN titles ON titles.title_id = crew.title_id
    WHERE born = 1955 and type = 'movie'
    GROUP BY name
) t
WHERE decile = 9 
ORDER BY avg_rating DESC, name ASC 

Q10

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”.

这个说简单也简单，说复杂也有一点点复杂(一般可以使用GROUP_CONCAT函数直接完成)

• 目标：所有叫做”House of the Dragon”tv series的别名（各种语言）进行字符串拼接并输出
• 1.p表存放所有别名
• 2.c表将p表中的primary_title改为序号，方便下面比较
• 3.f表递归拼接union
  • 递归出口：第一个select
  • 递归函数：第二个select，将上下两行拼接，比如将两行拼接为
• 4.降序得到最终的拼接，即为答案

with p as (
      select titles.primary_title as name, akas.title as dubbed
      from titles
      inner join akas on titles.title_id = akas.title_id
      where titles.primary_title = "House of the Dragon" AND titles.type = 'tvSeries'
      group by titles.primary_title, akas.title
      order by akas.title
),
c as (
      select row_number() over (order by p.name asc) as seqnum, p.dubbed as dubbed
      from p
),
flattened as (
      select seqnum, dubbed
      from c
      where seqnum = 1 
      union all 
      select c.seqnum, f.dubbed || ', ' || c.dubbed
      from c join
            flattened f
            on c.seqnum = f.seqnum + 1 
)
select dubbed from flattened
order by seqnum desc limit 1;

跑分结果

Donate