[SQL] 基礎 T-SQL 練習

論壇問題

練習 T-SQL

-- 資料建立
DECLARE @t_user TABLE (
    f_id int
    , f_name char(10)
    , f_birthday date
    , f_phone char(11)
    , f_level tinyint ) 

INSERT INTO @t_user (f_id , f_name , f_birthday , f_phone , f_level) VALUES
    (1 , 'Peter' , '1990-01-01' , '0911-123456' , 2)
    , (2 , 'Amy' , '1990-11-26' , '0911-235685' , 5)
    , (3 , 'Robert' , '1992-03-06' , '0912-365754' , 4)
    , (4 , 'Anna' , '1990-05-05' , '0913-644112' , 4)
    , (5 , 'Tina' , '1986-11-30' , '0922-111111' , 3)

DECLARE @t_bonus TABLE (
    f_name char(10)
    , f_bonus money
    , f_date date )

INSERT INTO @t_bonus (f_name , f_bonus , f_date) VALUES
    ('Peter' , 5000 , '2016-09-10')
    , ('Robert' , 1650 , '2016-09-10')
    , ('Anna',2500,'2016-08-10')
    , ('Peter',1000,'2016-07-10')
    , ('Anna',1500,'2016-10-10')

-- 1-1 查詢 1990 年出生的會員所有資料，按照生日排序 (由大至小)
SELECT * 
FROM @t_user 
WHERE f_birthday BETWEEN '1990-01-01' AND '1990-12-31'
ORDER BY f_birthday

-- 1-2 查詢名稱包含 er 字串的會員所有資料，按照 f_level 排序 (由小到大)
SELECT * 
FROM @t_user
WHERE CHARINDEX('er' , f_name) <> 0
ORDER BY f_level

-- 1-3 查詢 t_user 中有獎金 (記錄在 t_bonus) 的會員所有資料 (使用 IN)
SELECT *
FROM @t_user
WHERE f_name IN (SELECT f_name FROM @t_bonus)

-- 1-4 查詢 2016 年下半年是否有員工領到獎金 (使用 EXISTS)
SELECT T1.*
FROM @t_user AS T1
WHERE EXISTS
    (
        SELECT 1
        FROM @t_bonus AS T2
        WHERE T2.f_date BETWEEN '2016-07-01' AND '2016-12-31'
            AND T1.f_name = T2.f_name
    )

-- 1-5 查出表 A 有領過獎金的員工所有資料 (使用 DISTINC)
SELECT T1.*
FROM @t_user AS T1
    JOIN 
      (
          SELECT DISTINCT f_name
          FROM @t_bonus
      ) AS T2 ON T1.f_name = T2.f_name
  
-- 2.1 查出 2016 年 9 月領最大獎金的員工名稱
SELECT 
    TOP 1 
    f_name ,
    MAX(f_bonus) AS MaxBonus
FROM @t_bonus 
WHERE f_date >='2016/09/01' and f_date < '2016/10/01'
GROUP BY f_name 
ORDER BY MaxBonus DESC

-- 2.2 查出 2016 年每個員工的總共領取的獎金金額
SELECT
    f_name
    , SUM(f_bonus) AS TotalBonus
FROM @t_bonus
WHERE f_date BETWEEN '2016-01-01' AND '2016-12-31'
GROUP BY f_name

-- 2.3 查出 2016 年總共領取獎金超過 2000 的員工名稱、總獎金金額 (使用 GROUP BY & HAVING)
SELECT
    f_name
    , SUM(f_bonus) AS TotalBonus
FROM @t_bonus 
WHERE f_date BETWEEN '2016-01-01' AND '2016-12-31'
GROUP BY f_name
HAVING SUM(f_bonus) > 2000