Sql_Advance_参考答案

     目录

 

1.      第一章        2.      第二章       3.      第三章      4.      第四章        5.      第五章   

 

6.      第六章        7.      第七章        8.      第八章      9.      第九章       10.    第十章

 

---

1. 第一章

-- Q1
SELECT key,
       CASE WHEN CASE WHEN x < y THEN y ELSE x END < z THEN z
            ELSE CASE WHEN x < y THEN y ELSE x END
       END AS greatest
FROM Greatests;

SELECT key, MAX(col) AS greatest
FROM (SELECT key, x AS col FROM Greatests
      UNION ALL
      SELECT key, y AS col FROM Greatests
      UNION ALL
      SELECT key, z AS col FROM Greatests) TMP
GROUP BY key;

-- Q2
SELECT sex,
       SUM(population) AS total,
       SUM(CASE WHEN pref_name = '德岛' THEN population ELSE 0 END) AS col_1,
       SUM(CASE WHEN pref_name = '香川' THEN population ELSE 0 END) AS col_2,
       SUM(CASE WHEN pref_name = '爱媛' THEN population ELSE 0 END) AS col_3,
       SUM(CASE WHEN pref_name = '高知' THEN population ELSE 0 END) AS col_4,
       SUM(CASE WHEN pref_name IN ('德岛', '香川', '爱媛', '高知') THEN population ELSE 0 END) AS zaijie
From PopTbl2
GROUP BY sex;

-- Q3
SELECT key, 
       CASE key WHEN 'B' THEN 1,
                WHEN 'A' THEN 2,
                WHEN 'D' THEN 3,
                WHEN 'C' THEN 4
                ELSE NULL
       END AS sort_col
FROM Greatests
ORDER BY sort_col;

2.第二章

-- Q1
SELECT P1.name AS name_1,
       P2.name AS name_2
FROM Products P1, Products P2
WHERE P1.name >= P2.name;

-- Q2
SELECT district, name, price,
       RANK() OVER (PARTITION BY district ORDER BY price DESC) AS rank_1
FROM DistrictProducts;

SELECT P1.district, P1.name, P1.price,
       (SELECT COUNT(P2.price)
        FROM DistrictProducts P2
        WHERE P2.price > P1.price
              AND P1.district = P2.district) + 1 AS rank_1
FROM DistrictProducts P1;

SELECT P1.district, P1.name,
       MAX(P1.price) AS price,
       COUNT(P2.name) + 1 AS rank_1
FROM DistrictProducts P1
LEFT OUTER JOIN DistrictProducts P2 ON P1.price < P2.price AND P1.district = P2.district
GROUP BY P1.district, P1.name;

-- Q3
UPDATE DistrictProducts2 P1
SET ranking = (SELECT COUNT(P2.price) + 1
               FROM DistrictProducts2 P2
               WHERE P2.price > P1.price
                     AND P1.district = P2.district);

UPDATE DistrictProducts2
SET ranking = (SELECT P1.ranking
               FROM (SELECT district, name,
                            RANK() OVER (PARTITION BY district ORDER BY price DESC) AS ranking
                     FROM DistrictProducts2) P1
               WHERE P1.name = DistrictProducts2.name
                     AND P1.district = DistrictProducts2.district);

3.第三章

     略

4.第四章

-- Q1
SELECT CASE WHEN COUNT(*) <> MAX(seq) THEN '存在缺失的编号' ELSE '不存在缺失的编号' END AS gap
FROM SeqTbl;

-- Q2
SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date BETWEEN '2005-09-01' AND '2005-09-30' 
                           THEN 1 ELSE 0 END);

SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN EXTRACT(YEAR FROM sbmt_date) = 2005
                                AND BETWEEN EXTRACT(MONTH FROM sbmt_date) = 09 
                           THEN 1 ELSE 0 END);

-- Q3
SELECT SI.shop,
       COUNT(SI.item) AS my_item_cnt,
       (SELECT COUNT(item) FROM Items) - COUNT(SI.item) AS diff_cnt
FROM ShopItems SI, Items I
WHERE SI.item = I.item
GROUP BY SI.shop;

5.第五章

-- Q1
SELECT MASTER.age_class AS age_class,
       MASTER.sex_cd AS sex_cd,
       SUM(CASE WHEN pref_name IN ('秋田', '青森') THEN population ELSE NULL END) AS pop_tohoku,
       SUM(CASE WHEN pref_name IN ('东京', '千叶') THEN population ELSE NULL END) AS pop_kanto
FROM (SELECT age_class, sex_cd
      FROM TblAge CROSS JOIN TblSex) MASTER
LEFT OUTER JOIN TblPop DATA
     ON MASTER.age_class = DATA.age_class 
        AND MASTER.sex_cd = DATA.sex_cd
GROUP BY MASTER.age_class, MASTER.sex_cd;

-- Q2
CREATE VIEW Children(child)
AS 
SELECT child_1 FROM Personnel
UNION
SELECT child_2 FROM Personnel
UNION
SELECT child_3 FROM Personnel;

SELECT EMP.employee, 
       COUNT(CHILDREN.child) AS child_cnt
FROM Personnel EMP
LEFT OUTER JOIN Children ON CHILDREN.child IN (EMP.child_1, EMP.child_2, EMP.child_3)
GROUP BY EMP.employee;

-- Q3
MERGE INTO Class_A A
USING (SELECT * FROM Class_B) B ON (A.id = B.id)
WHEN MATCHED THEN 
     UPDATE SET A.name = B.name
WHEN NOT MATCHED THEN
     INSERT (id, name) INTO (B.id, B.name);

6.第六章

-- Q1
SELECT S1.year, S1.sale,
       CASE SIGN(sale - (SELECT sale 
                         FROM Sales S2
                         WHERE S2.year = S1.year - 1))
            WHEN 0 THEN '→'
            WHEN 1 THEN '↑'
            WHEN -1 THEN '↓'
       ELSE '-' END AS var
FROM Sales S1
ORDER BY year;

-- Q2
SELECT reserver, start_date, end_date
FROM Reservations R1
WHERE EXISTS (SELECT * 
              FROM Reservations R2
              WHERE R1.reserver <> R2.reserver
                    AND (R1.start_date, R1.end_date) OVERLAPS (R2.start_date, R2.end_date));

SELECT R1.reserver, R1.start_date, R1.end_date
FROM Reservations R1, Reservations R2
WHERE R1.reserver <> R2.reserver
      AND (R1.start_date, R1.end_date) OVERLAPS (R2.start_date, R2.end_date));

7.第七章

-- Q1
SELECT CASE WHEN COUNT(*) = (SELECT COUNT(*) FROM tbl_A)
                 AND COUNT(*) = (SELECT COUNT(*) FROM tbl_B)
            THEN '相等' ELSE '不相等' END AS result
FROM (SELECT * FROM tbl_A
      UNION
      SELECT * FROM tbl_B) TMP;

-- Q2
SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS (SELECT skill
                  FROM Skills
                  EXCEPT 
                  SELECT skill
                  FROM EmpSkills ES2
                  WHERE ES1.emp = ES2.emp)
      AND NOT EXISTS (SELECT skill
                      FROM EmpSkills ES3
                      WHERE ES1.emp = ES3.emp
                      EXCEPT 
                      SELECT skill
                      FROM Skills);

SELECT emp
FROM EmpSkills ES1
WHERE NOT EXISTS (SELECT skill
                  FROM Skills
                  EXCEPT 
                  SELECT skill
                  FROM EmpSkills ES2
                  WHERE ES1.emp = ES2.emp)
GROUP BY emp
HAVING COUNT(*) = (SELECT COUNT(*) FROM Skills);

8.第八章

-- Q1
SELECT DISTINCT key
FROM ArrayTbl2 A1
WHERE NOT EXISTS (SELECT * 
                  FROM ArrayTbl2 A2
                  WHERE A1.key = A2.key
                        AND (A2.val <> 1
                             OR A2.val IS NULL));

SELECT DISTINCT key
FROM ArrayTbl2 A1
WHERE 1 = ALL (SELECT val 
               FROM ArrayTbl2 A2
               WHERE A1.key = A2.key);

SELECT key
FROM ArrayTbl2
ORDER BY key
HAVING SUM(CASE WHEN val = 1 THEN 1 ELSE 0 END) = 10;

SELECT key
FROM ArrayTbl2
ORDER BY key
HAVING MIN(val) = 1
       AND MAX(val) = 1;

-- Q2
SELECT *
FROM Projects P1
WHERE 'O' = ALL (SELECT CASE WHEN step_nbr <= 1 AND status = '完成' THEN 'O'
                             WHEN step_nbr > 1 AND status = '等待' THEN 'O'
                             ELSE 'X' END
                 FROM Projects P2
                 WHERE P1.project_id = P2.project_id);

-- Q3
SELECT num AS prime
FROM Numbers Dividend
WHERE num > 1
      AND NOT EXISTS (SELECT * 
                      FROM Numbers Divisor
                      WHERE Dividend.num / 2 >= Divisor.num
                            AND Divisor.num <> 1
                            AND MOD(Dividend.num, Divisor.num) = 0)
ORDER BY prime;

9.第九章

-- Q1
SELECT seq
FROM Sequence N
WHERE seq BETWEEN 1 AND 12
      AND NOT EXISTS (SELECT * 
                      FROM SeqTbl S
                      WHERE S.seq = N.seq);

SELECT N.seq
FROM Sequence N
LEFT OUTER JOIN SeqTbl S ON N.seq = S.seq
WHERE N.seq BETWEEN 1 AND 12
      AND S.seq IS NULL;

-- Q2
SELECT S1.seat AS start_seat, '~', S2.seat AS end_seat
FROM Seats S1, Seats S2, Seats S3
WHERE S2.seat = S1.seat + (:head_cnt - 1)
      AND S3.seat BETWEEN S1.seat AND S2.seat
GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.status = '未预订' THEN 1 ELSE 0 END);

SELECT S1.seat AS start_seat, '~', S2.seat AS end_seat
FROM Seats2 S1, Seats2 S2, Seats2 S3
WHERE S2.seat = S1.seat + (:head_cnt - 1)
      AND S3.seat BETWEEN S1.seat AND S2.seat
GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.status = '未预订' AND S3.row_id = S1.row_id 
                           THEN 1 ELSE 0 END);

-- Q3
SELECT S1.seat AS start_seat,
       S2.seat AS end_seat,
       S2.seat - S1.seat + 1 AS seat_cnt
FROM Seats3 S1, Seats3 S2, Seats3 S3
WHERE S1.seat <= S2.seat
      AND S3.seat BETWEEN S1.seat - 1 AND S2.seat + 1
GROUP BY S1.seat, S2.seat
HAVING COUNT(*) = SUM(CASE WHEN S3.seat BETWEEN S1.seat AND S2.seat
                                AND S3.status = '未预订' THEN 1
                           WHEN S3.seat = S2.seat + 1 
                                AND S3.status <> '未预订' THEN 1
                           WHEN S3.seat = S1.seat - 1 
                                AND S3.status <> '未预订' THEN 1
                           ELSE 0
                      END);

10.第十章

-- Q1
SELECT center
FROM Materials2
GROUP BY center
HAVING COUNT(material || orgland) <> COUNT(DISTINCT material || orgland);

-- Q2
SELECT student_id
FROM TestScores
WHERE subject IN ('数学', '语文')
GROUP BY student_id
HAVING SUM(CASE WHEN subject = '数学' AND score >= 80 THEN 1 
                WHEN subject = '语文' AND score >= 50 THEN 1 
                ELSE 0 
           END) = 2;