15-445 homework#1 sql

最新推荐文章于 2021-08-20 15:06:52 发布

RedemptionC

最新推荐文章于 2021-08-20 15:06:52 发布

阅读量548

点赞数

本文链接：https://blog.csdn.net/RedemptionC/article/details/111138140

版权

这是一份关于SQL作业的概述，涉及使用SQLite3解决各种数据库查询问题。包括按工作类型排序的工作列表，查找每种类型中最长名字的工作，统计古典音乐艺术家最多的国家，列出最多配音艺术家，识别Coldplay的乙烯基唱片，分析各年代官方发行量，计算过去一年各月发行量占比，查找与Ariana Grande合作的艺术家总数，排名Dr. Dre和Eminem与其他多产二人组的合作次数，以及拼接The Beatles的配音名称。

摘要由CSDN通过智能技术生成

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;