15-445 homework#1 sql

overview

主要是对如上结构的db，根据各种要求写sql

sqlite3

显示所有表格：.tables

显示表结构：.schema table_**

 

题目

Q1 [0 POINTS] (Q1_SAMPLE):

The purpose of this query is to make sure that the formatting of your output matches exactly the formatting of our auto-grading script.

Details: List all types of work ordered by type ascendingly.

本题已经给出了答案：

sqlite> select name from work_type order by name;
    Answer:
    Aria
    Audio drama
    Ballet
    Beijing opera
    Cantata
    Concerto
    Incidental music
    Madrigal
    Mass
    Motet
    Musical
    Opera
    Operetta
    Oratorio
    Overture
    Partita
    Play
    Poem
    Prose
    Quartet
    Sonata
    Song
    Song-cycle
    Soundtrack
    Suite
    Symphonic poem
    Symphony
    Zarzuela
    Etude
    

Q2 [5 POINTS] (Q2_LONG_NAME):

List works with longest name of each type.

Details: For each work type, find works that have the longest names. There might be cases where there is a tie for the longest names - in that case, return all of them. Display work names and corresponding type names, and order it according to work type (ascending) and use work name (ascending) as tie-breaker.

work的每种类型中，找出名字最长的，如果最长的有多个，那么都列出来，显示work的名字以及对应的类型名，并且按照类型排序，如果 类型（int）相同，那么按照work的名字排序

参考答案：

select work.name,
    work_type.name
from work
    inner join (
        select max(length(work.name)) as max_length,
            work.type as type
        from work
        group by work.type
    ) as newtable on newtable.max_length = length(work.name)
    and work.type = newtable.type
    inner join work_type on work.type = work_type.id
order by work.type asc,
    work.name asc;

Q3 [5 POINTS] (Q3_OLD_MUSIC_NATIONS):
List top 10 countries with the most classical music artists (born or started before 1850) along with the number of
associated artists.
Details: Print country and number of associated arists before 1850. For example, Russia|191 . Sort by number of a
in descending order.

select a2.name,
    count(*) as c
from artist a1
    inner join area a2 on a1.area = a2.id
where begin_date_year < 1850
group by a1.area
order by c desc
limit 10;

 

Q4 [10 POINTS] (Q4_DUBBED_SMASH):
List the top 10 dubbed artist names with the number of dubs.

the number of corresponding distinct dubbed artist names in the artist_alias  table. 

select a2.name,
    count(*) as c
from artist a1
    inner join area a2 on a1.area = a2.id
where begin_date_year < 1850
group by a1.area
order by c desc
limit 10;cs144@cs144vm:~/15-445/hw/hw1$ cat q4_dubbed_smash.sql 
Select artist.name,
    count(distinct artist_alias.name) as num
From artist
    inner join artist_alias on artist.id = artist_alias.artist
Where artist.begin_date_year > 1950
    and area = 221
Group by artist.id
Order by num desc
Limit 10;

Q5 [10 POINTS] (Q5_VINYL_LOVER):
List the distinct names of releases issued in vinyl format by the British band Coldplay.
Details: Vinyl format includes ALL vinyl dimensions excluding VinylDisc . Sort the release names by release date
ascendingly 

select distinct r1.name as rname
from artist_credit_name a1
    inner join artist_credit a2 on a1.artist_credit = a2.id
    inner join release r1 on a2.id = r1.artist_credit
    inner join release_info r2 on r1.id = r2.release
    inner join medium m1 on r1.id = m1.release
    inner join medium_format m2 on m1.format = m2.id
where a1.name = 'Coldplay'
    and m2.name like '%Vinyl'
order by date_year,
    date_month,
    date_day;

Q6 [10 POINTS] (Q6_OLD_IS_NOT_GOLD):
Which decades saw the most number of official releases? List the number of official releases in every decade since
1900. Like 1970s|57210 .
Details: Print all decades and the number of official releases. Releases with different issue dates or countries are
considered different releases. Print the relevant decade in a fancier format by constructing a string that looks like 
1970s . Sort the decades in decreasing order with respect to the number of official releases and use decade
(descending) as tie-breaker. Remember to exclude releases whose dates are NULL .

select distinct r1.name as rname
from artist_credit_name a1
    inner join artist_credit a2 on a1.artist_credit = a2.id
    inner join release r1 on a2.id = r1.artist_credit
    inner join release_info r2 on r1.id = r2.release
    inner join medium m1 on r1.id = m1.release
    inner join medium_format m2 on m1.format = m2.id
where a1.name = 'Coldplay'
    and m2.name like '%Vinyl'
order by date_year,
    date_month,
    date_day;cs144@cs144vm:~/15-445/hw/hw1$ cat q6_old_is_not_gold.sql 
Select decade,
    count(*) as cnt
from (
        select (CAST((date_year / 10) as int) * 10) || 's' as decade
        from release
            inner join release_info on release.id = release_info.release
        where release.status = 1
            and date_year >= 1900
    )
Group by decade
Order by cnt desc,
    decade desc;

Q7 [15 POINTS] (Q7_RELEASE_PERCENTAGE):
List the month and the percentage of all releases issued in the corresponding month all over the world in the pas
year. Display like 2020.01|5.95 .
Details: The percentage of releases for a month is the number of releases issued in that month devided by the to
releases in the past year from 07/2019 to 07/2020, both included. Releases with different issue dates or countries 
considered different releases. Round the percentage to two decimal places using ROUND() . Sort by dates in ascend
order.

with past_year_release (year, month) as (
    select date_year,
        date_month
    from release_info r1
        inner join release r2 on r1.release = r2.id
    where (
            (
                date_year = 2019
                and date_month >= 7
            )
            or (
                date_year = 2020
                and date_month <= 7
            )
        )
)
select cast(year as varchar) || '.' || (
        case
            when month < 10 then '0'
            else ''
        end
    ) || cast(month as varchar) as date,
    round(
        count(*) * 100.0 / (
            select count(*)
            from past_year_release
        ),
        2
    )
from past_year_release
group by date
order by date;

Q8 [15 POINTS] (Q8_COLLABORATE_ARTIST):
List the number of artists who have collaborated with Ariana Grande.
Details: Print only the total number of artists. An artist is considered a collaborator if they appear in the same
artist_credit with Ariana Grande. The answer should include Ariana Grande herself.

Select count(distinct artist)
From artist_credit_name
Where artist_credit in (
        select artist_credit
        from artist_credit_name
        where name = 'Ariana Grande'
    );

Q9 [15 POINTS] (Q9_DRE_AND_EMINEM):
List the rank, artist names, along with the number of collaborative releases of Dr. Dre and Eminem among other m
productive duos (as long as they appear in the same release) both started after 1960 (not included). Display like
[rank]|Dr. Dre|Eminem|[# of releases] .
Details: For example, if you see a release by A, B, and C, it will contribute to three pairs of duos: A|B|1 , A|C|1 , an
B|C|1 . You will first need to calculate a rank of these duos by number of collaborated releases (release with
artist_credit shared by both artists) sorted descendingly, and then find the rank of Dr. Dre  and Eminem . Only relea
in English are considered. Both artists should be solo artists. All pairs of names should have the alphabetically sma
one first. Use artist names (asc) as tie breaker.
Hint: Artist aliases may be used everywhere. When doing aggregation, using artist ids will ensure you get the cor
results. One example entry in the rank list is 9|Benj Pasek|Justin Paul|27

with duos_list (id1, id2, count) as (
    select a1.artist as id1,
        a2.artist as id2,
        count(*) as c
    from artist_credit_name a1
        inner join artist_credit_name a2 on a1.artist_credit = a2.artist_credit
        inner join release r on a2.artist_credit = r.artist_credit
        inner join artist a3 on a1.artist = a3.id
        inner join artist a4 on a2.artist = a4.id
        inner join artist_type a5 on a3.type = a5.id
        inner join artist_type a6 on a4.type = a6.id
        inner join language l on r.language = l.id
    where a3.name < a4.name
        and a5.name = "Person"
        and a6.name = "Person"
        and l.name = 'English'
        and a3.begin_date_year > 1960
        and a4.begin_date_year > 1960
    group by a1.artist,
        a2.artist
)
select *
from (
        select row_number () over (
                order by count desc,
                    a1.name,
                    a2.name
            ) as rank,
            a1.name as name1,
            a2.name as name2,
            count
        from duos_list d
            inner join artist a1 on d.id1 = a1.id
            inner join artist a2 on d.id2 = a2.id
    )
where name1 = 'Dr. Dre'
    and name2 = 'Eminem';

Q10 [15 POINTS] (Q10_AROUND_THE_WORLD):
Concat all dubbed names of The Beatles using comma-separated values(like " Beetles, fab four ").

with c as (
      select row_number() over (
                  order by c.id asc
            ) as seqnum,
            c.name as name
      from artist_alias c
            join artist on c.artist = artist.id
      where artist.name = 'The Beatles'
),
flattened as (
      select seqnum,
            name as name
      from c
      where seqnum = 1
      union all
      select c.seqnum,
            f.name || ', ' || c.name
      from c
            join flattened f on c.seqnum = f.seqnum + 1
)
select name
from flattened
order by seqnum desc
limit 1;