SQL查询基础学习入门训练

1. List all year and "Events" (films released time, people births time, people deaths time) that occurred between 1930 and 1935

刚学了union all，老师也要求我们用union all解决这个问题~ 难度不算大hh

union all 就是把 若干个select的结果都列出来，并且不去除重复项

相比 union 效率高很多，因为不用去重。。

SELECT m.year_released AS year,
       m.title || '(' || c.country_name || ') was released' AS event
  FROM movies m
       JOIN
       countries c ON c.country_code = m.country
 WHERE m.year_released BETWEEN 1930 AND 1935
UNION ALL
SELECT p.born,
       trim(coalesce(p.first_name, '') || ' ' || surname || 'was born') 
  FROM people p
 WHERE p.born BETWEEN 1930 AND 1935
UNION ALL
SELECT p.died,
       trim(coalesce(p.first_name, '') || ' ' || surname || 'died') 
  FROM people p
 WHERE p.died BETWEEN 1930 AND 1935
 ORDER BY year

2. Same as question1, pushed into a subquery to add a sort key

SELECT year,
       event
  FROM (
           SELECT m.year_released AS year,
                  m.title || '(' || c.country_name || ') was released' AS event,
                  m.title AS sort_key
             FROM movies m
                  JOIN
                  countries c ON c.country_code = m.country
            WHERE m.year_released BETWEEN 1930 AND 1935
           UNION ALL
           SELECT born,
                  trim(coalesce(first_name, '') || ' ' || surname || 'was born'),
                  surname AS sort_key
             FROM people
            WHERE born BETWEEN 1930 AND 1935
           UNION ALL
           SELECT died,
                  trim(coalesce(first_name, '') || ' ' || surname || 'died'),
                  surname AS sort_key
             FROM people
            WHERE died BETWEEN 1930 AND 1935
       )
 ORDER BY year,
          sort_key

3. Events that happened the year when the earliest "Devdas" was released

WITH earliest_devdas AS (
    SELECT min(year_released) AS year
      FROM movies
     WHERE title = 'Devdas'
)
SELECT m.year_released AS year,
       m.title || '(' || c.country_name || ') was released' AS event
  FROM movies m
       JOIN
       countries c ON c.country_code = m.country
 WHERE m.year_released = (
                             SELECT year
                               FROM earliest_devdas
                         )
UNION ALL
SELECT born,
       trim(coalesce(first_name, '') || ' ' || surname || 'was born') 
  FROM people
 WHERE born = (
                  SELECT year
                    FROM earliest_devdas
              )
UNION ALL
SELECT died,
       trim(coalesce(first_name, '') || ' ' || surname || ' died') 
  FROM people
 WHERE died = (
                  SELECT year
                    FROM earliest_devdas
              )

5. Films where Qi Shu played without Ge You. Illustrates that "except" isn't really necessary

这里给出两个查询版本

版本1

SELECT m.title,
       m.country,
       m.year_released
  FROM (
           SELECT c.movieid
             FROM credits c
                  JOIN
                  people p ON p.peopleid = c.peopleid
            WHERE p.first_name = 'Qi' AND 
                  p.surname = 'Shu' AND 
                  c.credited_as = 'A' AND 
                  c.movieid NOT IN (
                      SELECT c.movieid
                        FROM credits c
                             JOIN
                             people p ON p.peopleid = c.peopleid
                       WHERE p.first_name = 'You' AND 
                             p.surname = 'Ge' AND 
                             c.credited_as = 'A'
                  )
       )
       F
       JOIN
       movies m ON m.movieid = F.movieid
 ORDER BY m.year_released

版本2

SELECT m.title,
       m.country,
       m.year_released
  FROM (
           SELECT c.movieid
             FROM credits c
                  JOIN
                  people p ON p.peopleid = c.peopleid
            WHERE p.first_name = 'Qi' AND 
                  p.surname = 'Shu' AND 
                  c.credited_as = 'A'
           EXCEPT
           SELECT c.movieid
             FROM credits c
                  JOIN
                  people p ON p.peopleid = c.peopleid
            WHERE p.first_name = 'You' AND 
                  p.surname = 'Ge' AND 
                  c.credited_as = 'A'
       )
       F
       JOIN
       movies m ON m.movieid = F.movieid
 ORDER BY m.year_released